Posts Tagged ‘resultset’

Json + JqueryThere is no direct way to iterate over a Java List with jQuery, see the following case study : Spring controller @RequestMapping(value = “/”, method = RequestMethod.GET) public ModelAndView getPages() { List list = new ArrayList(); list.add(“List A”); list.add(“List B”); list.add(“List C”); list.add(“List D”); list.add(“List E”); ModelAndView model = new ModelAndView(“somepage”); model.addObject(“list”, list); return model; } JSP page, you can…

 

The Greek lowercase omega (ω) character is use...

Here’s a guide to show you how to integrate JSF 2.0 with database via JDBC. In this example, we are using MySQL database and Tomcat web container.

Directory structure of this example

1. Table Structure

Create a “customer” table and insert five dummy records. Later, display it via JSF h:dataTable.

SQL commands

DROP TABLE IF EXISTS `mkyongdb`.`customer`;
CREATE TABLE  `mkyongdb`.`customer` (
  `CUSTOMER_ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `NAME` VARCHAR(45) NOT NULL,
  `ADDRESS` VARCHAR(255) NOT NULL,
  `CREATED_DATE` datetime NOT NULL,
  PRIMARY KEY (`CUSTOMER_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

INSERT INTO mkyongdb.customer(customer_id, name, address, created_date) 
VALUES(1, 'mkyong1', 'address1', now());
INSERT INTO mkyongdb.customer(customer_id, name, address, created_date) 
VALUES(2, 'mkyong2', 'address2', now());
INSERT INTO mkyongdb.customer(customer_id, name, address, created_date) 
VALUES(3, 'mkyong3', 'address3', now());
INSERT INTO mkyongdb.customer(customer_id, name, address, created_date) 
VALUES(4, 'mkyong4', 'address4', now());
INSERT INTO mkyongdb.customer(customer_id, name, address, created_date) 
VALUES(5, 'mkyong5', 'address5', now());

2. MySQL DataSource

Configure a MySQL datasource named “jdbc/mkyongdb“, follow this article – How to configure MySQL DataSource in Tomcat 6

3. Model Class

Create a “Customer” model class to store the table records.

File : Customer.java

package com.mkyong.customer.model;

import java.util.Date;

public class Customer{

	public long customerID;
	public String name;
	public String address;
	public Date created_date;

	//getter and setter methods 
}

4. JDBC Example

A JSF 2.0 managed bean, inject datasource “jdbc/mkyongdb” via @Resource, and uses normal JDBC API to retrieve all the customer records from database and store it into a List.

File : CustomerBean.java

package com.mkyong;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import com.mkyong.customer.model.Customer;

@ManagedBean(name="customer")
@SessionScoped
public class CustomerBean implements Serializable{

	//resource injection
	@Resource(name="jdbc/mkyongdb")
	private DataSource ds;

	//if resource injection is not support, you still can get it manually.
	/*public CustomerBean(){
		try {
			Context ctx = new InitialContext();
			ds = (DataSource)ctx.lookup("java:comp/env/jdbc/mkyongdb");
		} catch (NamingException e) {
			e.printStackTrace();
		}

	}*/

	//connect to DB and get customer list
	public List getCustomerList() throws SQLException{

		if(ds==null)
			throw new SQLException("Can't get data source");

		//get database connection
		Connection con = ds.getConnection();

		if(con==null)
			throw new SQLException("Can't get database connection");

		PreparedStatement ps 
			= con.prepareStatement(
			   "select customer_id, name, address, created_date from customer"); 

		//get customer data from database
		ResultSet result =  ps.executeQuery();

		List list = new ArrayList();

		while(result.next()){
			Customer cust = new Customer();

			cust.setCustomerID(result.getLong("customer_id"));
			cust.setName(result.getString("name"));
			cust.setAddress(result.getString("address"));
			cust.setCreated_date(result.getDate("created_date"));

			//store all data into a List
			list.add(cust);
		}

		return list;
	}
}

5. JSF Page dataTable

A JSF 2.0 xhtml page, uses h:dataTable to display all the customer records in table layout format.

 

 <?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"
      >
    <h:head>
    	<h:outputStylesheet library="css" name="table-style.css"  />
    </h:head>
 
    <h:body>
 
    	<h1>JSF 2.0 + JDBC Example</h1>
 
 		<h:dataTable value="#{customer.getCustomerList()}" var="c"
    			styleClass="order-table"
    			headerClass="order-table-header"
    			rowClasses="order-table-odd-row,order-table-even-row"
    		>
 
    		<h:column>
    			<f:facet name="header">
    				Customer ID
    			</f:facet>
    				#{c.customerID}
    		</h:column>
 
    		<h:column>
    			<f:facet name="header">
    				Name
				</f:facet>
    				#{c.name}
    		</h:column>
 
 			<h:column>
    			<f:facet name="header">
    				Address
				</f:facet>
    				#{c.address}
    		</h:column>
 
    		<h:column>
    			<f:facet name="header">
    				Created Date
				</f:facet>
    				#{c.created_date}
    		</h:column>
 
    	</h:dataTable>
 
    </h:body>
 
</html>
 

6. Demo

Run it, see output

Download Source Code

Download It – JSF-2-JDBC-Integration-Example.zip (12KB)

International Bibliography of Periodical Liter...

executeQuery()—for getting the data from database
executeUpdate()—for insert,update,delete
execute()—any kind of operations

executeQuery() This is used generally for reading the content of the database. The output will be in the form of ResultSet. Generally SELECT statement is used.

executeUpdate() This is generally used for altering the databases. Generally DROP TABLE or DATABASE, INSERT into TABLE, UPDATE TABLE, DELETE from TABLE statements will be used in this. The output will be in the form of int. This int value denotes the number of rows affected by the query.

execute() If you dont know which method to be used for executing SQL statements, this method can be used. This will return a boolean. TRUE indicates the result is a ResultSet and FALSE indicates it has the int value which denotes number of rows affected by the query.

International Bibliography of Periodical Liter...

There are major differences between iBatis and Hibernate but both the solutions work well, given their specific domain. Personally I would suggest you should use iBATIS if:

  • You want to create your own SQL‘s and are willing to maintain them.
  • your environment is driven by relational data model.
  • you have to work existing and complex schema’s.

And simply use Hibernate if:

  • Your environment is driven by object model and wants generates SQL automatically.

To count there are few differences:

  • iBATIS is:
    • Simpler
    • Faster development time
    • Flixable
    • Much smaller in package size
  • Hibernate:
    • Generates SQL for you which means you don’t spend time on SQL
    • Provides much more advance cache
    • Highly scalable

Other difference is that iBATIS makes use of SQL which could be database dependent where as Hibernate makes use of HQL which is relatively independent of databases and it is easier to change db in Hibernate.

Hibernate maps your Java POJO objects to the Database tables where as iBatis maps the ResultSet from JDBC API to your POJO Objets.

If you are using stored procedures, well you can do it in Hibernate but it is little difficult in comparision of iBATIS. As an alternative solution iBATIS maps results sets to objects, so no need to care about table structures. This works very well for stored procedures, works very well for reporting applications, etc

Finally, Hibernate and iBATIS both are open source Object Relational Mapping(ORM) tools available in the industry. Use of each of these tools depends on the context you are using them. Hibernate and iBatis both also have good support from SPRING framework so it should not be a problem to chose one of them.

  

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

public class Main {

  public static void main(String[] argsthrows Exception {

    Connection conn = getOracleConnection();

    // Step-2: identify the stored procedure

    String proc3StoredProcedure = “{ call proc3(?, ?, ?) }”;

    // Step-3: prepare the callable statement

    CallableStatement cs = conn.prepareCall(proc3StoredProcedure);

    // Step-4: set input parameters …

    // first input argument

    cs.setString(1“abcd”);

    // third input argument

    cs.setInt(310);

    // Step-5: register output parameters …

    cs.registerOutParameter(2, java.sql.Types.VARCHAR);

    cs.registerOutParameter(3, java.sql.Types.INTEGER);

    // Step-6: execute the stored procedures: proc3

    cs.execute();

    // Step-7: extract the output parameters

    // get parameter 2 as output

    String param2 = cs.getString(2);

    // get parameter 3 as output

    int param3 = cs.getInt(3);

    System.out.println(“param2=” + param2);

    System.out.println(“param3=” + param3);

    conn.close();

  }

  private static Connection getHSQLConnection() throws Exception {

    Class.forName(“org.hsqldb.jdbcDriver”);

    System.out.println(“Driver Loaded.”);

    String url = “jdbc:hsqldb:data/tutorial”;

    return DriverManager.getConnection(url, “sa”“”);

  }

  public static Connection getMySqlConnection() throws Exception {

    String driver = “org.gjt.mm.mysql.Driver”;

    String url = “jdbc:mysql://localhost/demo2s”;

    String username = “oost”;

    String password = “oost”;

    Class.forName(driver);

    Connection conn = DriverManager.getConnection(url, username, password);

    return conn;

  }

  public static Connection getOracleConnection() throws Exception {

    String driver = “oracle.jdbc.driver.OracleDriver”;

    String url = “jdbc:oracle:thin:@localhost:1521:caspian”;

    String username = “mp”;

    String password = “mp2”;

    Class.forName(driver)// load Oracle driver

    Connection conn = DriverManager.getConnection(url, username, password);

    return conn;

  }

}

           

         

    

  

CallStoresProcedureInOracleAndPassInOutParameters.zip( 3,849 k)