Posts Tagged ‘jdbc’

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)

Separation of concerns

Spring could potentially be a one-stop shop for all your enterprise applications, however, Spring is modular, allowing you to pick and choose which modules are applicable to you, without having to bring in the rest. Following section gives detail about all the modules available in Spring Framework.

The Spring Framework provides about 20 modules which can be used based on an application requirement.

Spring Framework Architecture

Core Container:

The Core Container consists of the Core, Beans, Context, and Expression Language modules whose detail is as follows:

  • The Core module provides the fundamental parts of the framework, including the IoC and Dependency Injection features.
  • The Bean module provides BeanFactory which is a sophisticated implementation of the factory pattern.
  • The Context module builds on the solid base provided by the Core and Beans modules and it is a medium to access any objects defined and configured. The ApplicationContext interface is the focal point of the Context module.
  • The Expression Language module provides a powerful expression language for querying and manipulating an object graph at runtime.

Data Access/Integration:

The Data Access/Integration layer consists of the JDBC, ORM, OXM, JMS and Transaction modules whose detail is as follows:

  • The JDBC module provides a JDBC-abstraction layer that removes the need to do tedious JDBC related coding.
  • The ORM module provides integration layers for popular object-relational mapping APIs, including JPA, JDO, Hibernate, and iBatis.
  • The OXM module provides an abstraction layer that supports Object/XML mapping implementations for JAXB, Castor, XMLBeans, JiBX and XStream.
  • The Java Messaging Service JMS module contains features for producing and consuming messages.
  • The Transaction module supports programmatic and declarative transaction management for classes that implement special interfaces and for all your POJOs.

Web:

The Web layer consists of the Web, Web-Servlet, Web-Struts, and Web-Portlet modules whose detail is as follows:

  • The Web module provides basic web-oriented integration features such as multipart file-upload functionality and the initialization of the IoC container using servlet listeners and a web-oriented application context.
  • The Web-Servlet module contains Spring’s model-view-controller (MVC) implementation for web applications.
  • The Web-Struts module contains the support classes for integrating a classic Struts web tier within a Spring application.
  • The Web-Portlet module provides the MVC implementation to be used in a portlet environment and mirrors the functionality of Web-Servlet module.

Miscellaneous:

There are few other important modules like AOP, Aspects, Instrumentation, Web and Test modules whose detail is as follows:

  • The AOP module provides aspect-oriented programming implementation allowing you to define method-interceptors and pointcuts to cleanly decouple code that implements functionality that should be separated.
  • The Aspects module provides integration with AspectJ which is again a powerful and mature aspect oriented programming (AOP) framework.
  • The Instrumentation module provides class instrumentation support and class loader implementations to be used in certain application servers.
  • The Test module supports the testing of Spring components with JUnit or TestNG frameworks

  

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)