Showing posts with label database. Show all posts
Showing posts with label database. Show all posts

Thursday 26 September 2013

How to display datatable row number in JSF

In , , // siddhu vydyabhushana // 4 comments
JSF dataTable does not contains any method to display the currently selected row numbers. However, you can hack it with javax.faces.model.DataModel class, which has a getRowIndex() method to return the currently selected row number.

JSF + DataModel

Here’s a JSF 2.0 example to show you how to use DataModel to return the currently selected row number.

1. Managed Bean

A managed bean named “person”, and show the use of DataModel to hold a list of the person object.
package com.mkyong;
 
import java.io.Serializable;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
import javax.faces.model.ArrayDataModel;
import javax.faces.model.DataModel;
 
@ManagedBean(name="person")
@SessionScoped
public class PersonBean implements Serializable{
 
	private static final long serialVersionUID = 1L;
 
	private static final Person[] personList = new Person[]{
 
		new Person("Person", "A", 10),
		new Person("Person", "B", 20),
		new Person("Person", "C", 30),
		new Person("Person", "D", 40),
		new Person("Person", "E", 50)
 
	};
 
	/* To get the row numbers, use dataModel instead
	public Person[] getPersonList() {
 
		return personList;
 
	}
	*/
 
	private DataModel<Person> person = new ArrayDataModel<Person>(personList);
 
	public DataModel<Person> getPersonList() {
 
		return person;
 
	}
 
	public static class Person{
 
		String firstName;
		String lastName;
		int age;
 
		public Person(String firstName, String lastName, int age) {
			super();
			this.firstName = firstName;
			this.lastName = lastName;
			this.age = age;
		}
 
		//getter and setter methods 
	}
}

2. JSF Page

JSP page to show the use of DataModel “rowIndex” to return 0-indexed of the currently selected row number.
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"   
      xmlns:h="http://java.sun.com/jsf/html"
      xmlns:f="http://java.sun.com/jsf/core"
      xmlns:ui="http://java.sun.com/jsf/facelets"
      >
    <h:head>
    	<h:outputStylesheet library="css" name="table-style.css"  />
    </h:head>
    <h:body>
 
    	<h1>Display dataTable row numbers in JSF</h1>
 
    	   <h:dataTable value="#{person.personList}" var="p"
    		styleClass="person-table"
    		headerClass="person-table-header"
    		rowClasses="person-table-odd-row,person-table-even-row"
    	   >
 
		<h:column>
 
    			<!-- display currently selected row number -->
    			<f:facet name="header">No</f:facet>
    			#{person.personList.rowIndex + 1}
 
    		</h:column>
 
    		<h:column>
 
    			<f:facet name="header">First Name</f:facet>
    			#{p.firstName}
 
    		</h:column>
 
    		<h:column>
 
    			<f:facet name="header">Last Name</f:facet>
    			#{p.lastName}
 
    		</h:column>
 
    		<h:column>
 
    			<f:facet name="header">Age</f:facet>
    			#{p.age}
 
    		</h:column>
 
    	   </h:dataTable>
 
    </h:body>
</html>

3. Demo

jsf2-dataTable-RowNumbers-Example

Download Source Code

Download It – JSF-2-DataTable-RowNumbers-Example.zip (10KB)
 
Read More

How to delete row in JSF Datatable

In , , // siddhu vydyabhushana // 16 comments
This example is enhancing the previous JSF 2 dataTable example, by adding a “delete” function to delete the row in dataTable.

Delete Concept

The overall concept is quite simple :
1. Assign a “Delete” link to the end of each row.
//...
<h:dataTable value="#{order.orderList}" var="o">
 
<h:column>
 
    <f:facet name="header">Action</f:facet>
 
    <h:commandLink value="Delete" action="#{order.deleteAction(o)}" />
 
</h:column>
2. If “Delete” link is clicked, pass the current row object into the deleteAction(). In deleteAction() method, just removes the current row object from the “list” and return back to the current page.
public String deleteAction(Order order) {
 
	orderList.remove(order);
	return null;
}

Example

A JSF 2.0 example to implement the above concept to delete row in dataTable.

1. Managed Bean

A managed bean named “order”, self-explanatory.
package com.mkyong;
 
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Arrays;
 
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
 
@ManagedBean(name="order")
@SessionScoped
public class OrderBean implements Serializable{
 
	private static final long serialVersionUID = 1L;
 
	private static final ArrayList<Order> orderList = 
		new ArrayList<Order>(Arrays.asList(
 
		new Order("A0001", "Intel CPU", 
				new BigDecimal("700.00"), 1),
		new Order("A0002", "Harddisk 10TB", 
				new BigDecimal("500.00"), 2),
		new Order("A0003", "Dell Laptop", 
				new BigDecimal("11600.00"), 8),
		new Order("A0004", "Samsung LCD", 
				new BigDecimal("5200.00"), 3),
		new Order("A0005", "A4Tech Mouse", 
				new BigDecimal("100.00"), 10)
	));
 
	public ArrayList<Order> getOrderList() {
 
		return orderList;
 
	}
 
	public String deleteAction(Order order) {
 
		orderList.remove(order);
		return null;
	}
 
	public static class Order{
 
		String orderNo;
		String productName;
		BigDecimal price;
		int qty;
 
		public Order(String orderNo, String productName, 
				BigDecimal price, int qty) {
			this.orderNo = orderNo;
			this.productName = productName;
			this.price = price;
			this.qty = qty;
		}
 
		//getter and setter methods
	}
}

2. JSF page

JSF page to display the data with dataTable tag, and create a “delete” link to delete the row record.
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"   
      xmlns:h="http://java.sun.com/jsf/html"
      xmlns:f="http://java.sun.com/jsf/core"
      xmlns:ui="http://java.sun.com/jsf/facelets"
      >
    <h:head>
    	<h:outputStylesheet library="css" name="table-style.css"  />
    </h:head>
    <h:body>
 
    	<h1>JSF 2 dataTable example</h1>
    	<h:form>
    		<h:dataTable value="#{order.orderList}" var="o"
    			styleClass="order-table"
    			headerClass="order-table-header"
    			rowClasses="order-table-odd-row,order-table-even-row"
    		>
 
    		<h:column>
 
    			<f:facet name="header">Order No</f:facet>
    			#{o.orderNo}
 
    		</h:column>
 
    		<h:column>
 
    			<f:facet name="header">Product Name</f:facet>
    			#{o.productName}
 
    		</h:column>
 
    		<h:column>
 
    			<f:facet name="header">Price</f:facet>
    			#{o.price}
 
    		</h:column>
 
    		<h:column>
 
    			<f:facet name="header">Quantity</f:facet>
    			#{o.qty}
 
    		</h:column>
 
    		<h:column>
 
    			<f:facet name="header">Action</f:facet>
 
    			<h:commandLink value="Delete" 
                                action="#{order.deleteAction(o)}" />
 
    		</h:column>
 
    		</h:dataTable>
 
    	</h:form>
    </h:body>
</html>

3. Demo

From top to bottom, shows a row record being deleted.
jsf2-dataTable-Delete-Example-1
jsf2-dataTable-Delete-Example-2

Download Source Code

Download It – JSF-2-DataTable-Delete-Example.zip (10KB)
Read More

How to add row in JSF Table

In , , // siddhu vydyabhushana // 3 comments
This example is enhancing previous delete dataTable row example, by adding a “add” function to add a row in dataTable.
Here’s a JSF 2.0 example to show you how to add a row in dataTable.

1. Managed Bean

A managed bean named “order”, self-explanatory.
package com.mkyong;
 
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Arrays;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
 
@ManagedBean(name="order")
@SessionScoped
public class OrderBean implements Serializable{
 
	private static final long serialVersionUID = 1L;
 
	String orderNo;
	String productName;
	BigDecimal price;
	int qty;
 
	//getter and setter methods
 
	private static final ArrayList<Order> orderList = 
		new ArrayList<Order>(Arrays.asList(
 
		new Order("A0001", "Intel CPU", 
				new BigDecimal("700.00"), 1),
		new Order("A0002", "Harddisk 10TB", 
				new BigDecimal("500.00"), 2),
		new Order("A0003", "Dell Laptop", 
				new BigDecimal("11600.00"), 8),
		new Order("A0004", "Samsung LCD", 
				new BigDecimal("5200.00"), 3),
		new Order("A0005", "A4Tech Mouse", 
				new BigDecimal("100.00"), 10)
	));
 
	public ArrayList<Order> getOrderList() {
 
		return orderList;
 
	}
 
	public String addAction() {
 
		Order order = new Order(this.orderNo, this.productName, 
			this.price, this.qty);
 
		orderList.add(order);
		return null;
	}
 
	public String deleteAction(Order order) {
 
		orderList.remove(order);
		return null;
	}
 
	public static class Order{
 
		String orderNo;
		String productName;
		BigDecimal price;
		int qty;
 
		public Order(String orderNo, String productName, 
				BigDecimal price, int qty) {
			this.orderNo = orderNo;
			this.productName = productName;
			this.price = price;
			this.qty = qty;
		}
 
		//getter and setter methods
	}
}

2. JSF page

JSF page to display the data with dataTable tag, and a entry form to key in the order data.
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"   
      xmlns:h="http://java.sun.com/jsf/html"
      xmlns:f="http://java.sun.com/jsf/core"
      xmlns:ui="http://java.sun.com/jsf/facelets"
      >
    <h:head>
    	<h:outputStylesheet library="css" name="table-style.css"  />
    </h:head>
    <h:body>
 
    	<h1>JSF 2 dataTable example</h1>
    	<h:form>
    		<h:dataTable value="#{order.orderList}" var="o"
    			styleClass="order-table"
    			headerClass="order-table-header"
    			rowClasses="order-table-odd-row,order-table-even-row"
    		>
 
    		<h:column>
 
    			<f:facet name="header">Order No</f:facet>
    			#{o.orderNo}
 
    		</h:column>
 
    		<h:column>
 
    			<f:facet name="header">Product Name</f:facet>
    			#{o.productName}
 
    		</h:column>
 
    		<h:column>
 
    			<f:facet name="header">Price</f:facet>
    			#{o.price}
 
    		</h:column>
 
    		<h:column>
 
    			<f:facet name="header">Quantity</f:facet>
    			#{o.qty}
 
    		</h:column>
 
    		<h:column>
 
    			<f:facet name="header">Action</f:facet>
 
    			<h:commandLink value="Delete" 
                                   action="#{order.deleteAction(o)}" />
 
    		</h:column>
 
    		</h:dataTable>
 
    		<h3>Enter Order</h3>
    		<table>
    		<tr>
    			<td>Order No :</td>
    			<td><h:inputText size="10" value="#{order.orderNo}" /></td>
    		</tr>
    		<tr>
    			<td>Product Name :</td>
    			<td><h:inputText size="20" value="#{order.productName}" /></td>
    		</tr>
    		<tr>
    			<td>Quantity :</td>
    			<td><h:inputText size="5" value="#{order.price}" /></td>
    		</tr>
    		<tr>
    			<td>Price :</td>
    			<td><h:inputText size="10" value="#{order.qty}" /></td>
    		</tr>
    		</table>
 
    		<h:commandButton value="Add" action="#{order.addAction}" />
 
    	</h:form>
    </h:body>
</html>

3. Demo

From top to bottom, shows a row record being added.
jsf2-dataTable-Add-Example-1
jsf2-dataTable-Add-Example-2
jsf2-dataTable-Add-Example-3

Download Source Code

Download It – JSF-2-DataTable-Add-Example.zip (10KB)
 
Read More

Wednesday 3 July 2013

JDBC PreparedStatement Example – Select List Of The Records

In , // siddhu vydyabhushana // 8 comments
Here’s an example to show you how to select records from table via JDBC PreparedStatement, and display the records via a ResultSet object. To issue a select query, calls the PreparedStatement.executeQuery() method like this :
String selectSQL = "SELECT USER_ID, USERNAME FROM DBUSER WHERE USER_ID = ?";
PreparedStatement preparedStatement = dbConnection.prepareStatement(selectSQL);
preparedStatement.setInt(1, 1001);
ResultSet rs = preparedStatement.executeQuery(selectSQL );
while (rs.next()) {
	String userid = rs.getString("USER_ID");
	String username = rs.getString("USERNAME");	
}
Full example…
package com.mkyong.jdbc;
 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
public class JDBCPreparedStatementSelectExample {
 
	private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
	private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:MKYONG";
	private static final String DB_USER = "user";
	private static final String DB_PASSWORD = "password";
 
	public static void main(String[] argv) {
 
		try {
 
			selectRecordsFromTable();
 
		} catch (SQLException e) {
 
			System.out.println(e.getMessage());
 
		}
 
	}
 
	private static void selectRecordsFromTable() throws SQLException {
 
		Connection dbConnection = null;
		PreparedStatement preparedStatement = null;
 
		String selectSQL = "SELECT USER_ID, USERNAME FROM DBUSER WHERE USER_ID = ?";
 
		try {
			dbConnection = getDBConnection();
			preparedStatement = dbConnection.prepareStatement(selectSQL);
			preparedStatement.setInt(1, 1001);
 
			// execute select SQL stetement
			ResultSet rs = preparedStatement.executeQuery();
 
			while (rs.next()) {
 
				String userid = rs.getString("USER_ID");
				String username = rs.getString("USERNAME");
 
				System.out.println("userid : " + userid);
				System.out.println("username : " + username);
 
			}
 
		} catch (SQLException e) {
 
			System.out.println(e.getMessage());
 
		} finally {
 
			if (preparedStatement != null) {
				preparedStatement.close();
			}
 
			if (dbConnection != null) {
				dbConnection.close();
			}
 
		}
 
	}
 
	private static Connection getDBConnection() {
 
		Connection dbConnection = null;
 
		try {
 
			Class.forName(DB_DRIVER);
 
		} catch (ClassNotFoundException e) {
 
			System.out.println(e.getMessage());
 
		}
 
		try {
 
			dbConnection = DriverManager.getConnection(
                             DB_CONNECTION, DB_USER,DB_PASSWORD);
			return dbConnection;
 
		} catch (SQLException e) {
 
			System.out.println(e.getMessage());
 
		}
 
		return dbConnection;
 
	}
 
}

Result

List of the records with “user_id = 1001″ are retrieved from table “DBUSER” and displayed.
Read More

JDBC Statement Example – Batch Update

In , // siddhu vydyabhushana // 6 comments
Here’s an example to show you how to insert few records in batch process, via JDBC Statement.
dbConnection.setAutoCommit(false);
 
statement = dbConnection.createStatement();
statement.addBatch(insertTableSQL1);
statement.addBatch(insertTableSQL2);
statement.addBatch(insertTableSQL3);
 
statement.executeBatch();
 
dbConnection.commit();
Note
Batch Update is not limit to Insert statement, it’s apply for Update and Delete statement as well.
See full JDBC batch update example …
package com.mkyong.jdbc;
 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
 
public class JDBCBatchUpdateExample {
 
	private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
	private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:MKYONG";
	private static final String DB_USER = "user";
	private static final String DB_PASSWORD = "password";
	private static final DateFormat dateFormat = new SimpleDateFormat(
			"yyyy/MM/dd HH:mm:ss");
 
	public static void main(String[] argv) {
 
		try {
 
			batchInsertRecordsIntoTable();
 
		} catch (SQLException e) {
 
			System.out.println(e.getMessage());
 
		}
 
	}
 
	private static void batchInsertRecordsIntoTable() throws SQLException {
 
		Connection dbConnection = null;
		Statement statement = null;
 
		String insertTableSQL1 = "INSERT INTO DBUSER"
				+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) " + "VALUES"
				+ "(101,'mkyong','system', " + "to_date('"
				+ getCurrentTimeStamp() + "', 'yyyy/mm/dd hh24:mi:ss'))";
 
		String insertTableSQL2 = "INSERT INTO DBUSER"
				+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) " + "VALUES"
				+ "(102,'mkyong','system', " + "to_date('"
				+ getCurrentTimeStamp() + "', 'yyyy/mm/dd hh24:mi:ss'))";
 
		String insertTableSQL3 = "INSERT INTO DBUSER"
				+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) " + "VALUES"
				+ "(103,'mkyong','system', " + "to_date('"
				+ getCurrentTimeStamp() + "', 'yyyy/mm/dd hh24:mi:ss'))";
 
		try {
			dbConnection = getDBConnection();
			statement = dbConnection.createStatement();
 
			dbConnection.setAutoCommit(false);
 
			statement.addBatch(insertTableSQL1);
			statement.addBatch(insertTableSQL2);
			statement.addBatch(insertTableSQL3);
 
			statement.executeBatch();
 
			dbConnection.commit();
 
			System.out.println("Records are inserted into DBUSER table!");
 
		} catch (SQLException e) {
 
			System.out.println(e.getMessage());
 
		} finally {
 
			if (statement != null) {
				statement.close();
			}
 
			if (dbConnection != null) {
				dbConnection.close();
			}
 
		}
 
	}
 
	private static Connection getDBConnection() {
 
		Connection dbConnection = null;
 
		try {
 
			Class.forName(DB_DRIVER);
 
		} catch (ClassNotFoundException e) {
 
			System.out.println(e.getMessage());
 
		}
 
		try {
 
			dbConnection = DriverManager.getConnection(
				DB_CONNECTION, DB_USER,DB_PASSWORD);
			return dbConnection;
 
		} catch (SQLException e) {
 
			System.out.println(e.getMessage());
 
		}
 
		return dbConnection;
 
	}
 
	private static String getCurrentTimeStamp() {
 
		java.util.Date today = new java.util.Date();
		return dateFormat.format(today.getTime());
 
	}
 
}

Result

3 records are inserted into database via batch update process.

Why need to use Batch Update?

The above batch update is same with normal executeUpdate() method like this :
statement.executeUpdate(insertTableSQL1);
statement.executeUpdate(insertTableSQL2);
statement.executeUpdate(insertTableSQL3);
But batch update has performance benefit if you want to insert many records, because executeBatch() reduces the number of JDBC calls to database.
Read More

JDBC Statement Example – Delete A Record

In , // siddhu vydyabhushana // 3 comments
Here is an example to show you how to delete a record from a table via JDBC statement. To issue a delete statement, calls the Statement.executeUpdate() method like this :
Statement statement = dbConnection.createStatement();
// execute the delete SQL stetement
statement.executeUpdate(deleteTableSQL);
Full example…
package com.mkyong.jdbc;
 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
 
public class JDBCStatementDeleteExample {
 
 private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
 private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:MKYONG";
 private static final String DB_USER = "user";
 private static final String DB_PASSWORD = "password";
 
 public static void main(String[] argv) {
 
  try {
 
   deleteRecordFromDbUserTable();
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  }
 
 }
 
 private static void deleteRecordFromDbUserTable() throws SQLException {
 
  Connection dbConnection = null;
  Statement statement = null;
 
  String deleteTableSQL = "DELETE DBUSER WHERE USER_ID = 1";
 
  try {
   dbConnection = getDBConnection();
   statement = dbConnection.createStatement();
 
   System.out.println(deleteTableSQL);
 
   // execute delete SQL stetement
   statement.execute(deleteTableSQL);
 
   System.out.println("Record is deleted from DBUSER table!");
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  } finally {
 
   if (statement != null) {
    statement.close();
   }
 
   if (dbConnection != null) {
    dbConnection.close();
   }
 
  }
 
 }
 
 private static Connection getDBConnection() {
 
  Connection dbConnection = null;
 
  try {
 
   Class.forName(DB_DRIVER);
 
  } catch (ClassNotFoundException e) {
 
   System.out.println(e.getMessage());
 
  }
 
  try {
 
   dbConnection = DriverManager.getConnection(
                             DB_CONNECTION, DB_USER,DB_PASSWORD);
   return dbConnection;
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  }
 
  return dbConnection;
 
 }
 
}

Result

A record with “user_id=1″ is deleted from table.
DELETE DBUSER WHERE USER_ID = 1
Record IS deleted FROM DBUSER TABLE!
Read More

Designed By Blogtipsntricks. In Association With Tozilnutpam and Praverb Dot Net.

© 2012 Java,Android,j2ee and jsp tutorials blog