JSF 2.0 + JDBC integration example

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<Customer> 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<Customer> list = new ArrayList<Customer>();
 
		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)

Reference

  1. JDBC example in Java
  2. JNDI datasource examples in Tomcat 6
  3. Configurate MySQL datasource in Tomcat 6
  4. JSF 2 dataTable example
Tags :

About the Author

mkyong
Founder of Mkyong.com and HostingCompass.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

  • Giancarlo

    Always clear and students oriented. Great!

  • Leonardo

    Thank you so much! :D

  • manuel

    Records no found… why?

  • Ifti

    I have used your example.
    And I’m getting the following error if i access the jsf page frequently.

    java.sql.SQLException: Listener refused the connection with the following error:
    ORA-12519, TNS:no appropriate service handler found
    The Connection descriptor used by the client was:
    127.0.0.1:1521:XE

    Suppose I’m calling the bean using parameters from the jsf page. If i call the bean frequently then the error occurred. But if I take some time between two consecutive call then no error happened.

    • jakub

      it looks like you where trying to connect to oracle database, example is for mysql, maybe this is the cause

  • Ifti

    Thanks for the example.
    But don’t we need to close the connection by our self?

    After using the connection we should close it.

    • jakub

      I also have such impression, even in finally clause

  • shila

    hi….i use your codes in intellij IDEA jet brain…it works but do not show me my information that i ensert to my sql….what do i do? i need help…pelease

  • Dickens

    I get the following error:

    /default.xhtml @20,8 value=”#{customer.getCustomerList()}”: The class ‘com.mkyong.CustomerBean’ does not have the property ‘getCustomerList’.

    • Arun

      I get similar error..
      /default.xhtml @20,8 value=”#{customer.getCustomerList()}” Error Parsing: #{customer.getCustomerList()}

  • Pingback: JSF 2.0 Tutorial()

  • abbaq

    hi mkyong,
    the tutorial doesn’t print the list in my browser,
    help me ;)))

  • abbaq

    Cannot create JDBC driver of class ” for connect URL ‘null’

    i got an error plz help me out

  • Saravanan K

    Hi Mr.Yong,

    The above example works fine when I use eclipse configured with tomcat, but when I deploy the project in server, @Resource is not working, what do I miss, please help!

    Thanks for your help in advance.

  • rdk

    hi mkyong,
    the tutorial doesn’t print the list in my browser, but only the thead component…
    help me ;)))

    • rdk

      Now it’s ok… TNX!

  • mammoth

    Hi Yong,
    It works , great tutorial . Thanks a lot …

  • Asia

    hi,
    Helpfull tutorial.If we want to retreive any 1 record based on the date how to do it.I tried your tutorial and its working fine,but i am facing problem when i want to retreive record based on a particular date.

  • Asia

    Helpfull tutorial.If we want to retreive any 1 record based on the date how to do it.I tried your tutorial and its working fine,but i am facing problem when i want to retreive record based on a particular date.

  • Harshita Srivastava

    Hi,,

    I followed the steps in the tutorial above but I am not able to deploy the project on Websphere and getting the error below:

    Module named ‘JavaServerFaces’ failed to deploy. See Error Log view for more detail.
    weblogic.application.ModuleException: Failed to load webapp: ‘JavaServerFaces’
    Exception received from deployment driver. See Error Log view for more detail.

  • Asaf

    Thank you very much, this tutorial was very helpful.

    -Asaf

  • Oscar B.

    Hi Mkyong, I find your website such a holly grail for those noobs (like me of course) trying to dive into JSF 2.0 – There are plenty of non-friendly-for-noobs web tutorials around the web :S

    I have just one curiosity; why does @Resource annotation is not working in my Tomcat 7.0.1? It just works perfect on my GlassFish 3 instance.

    Any clue?

    Contrats.

  • http://www.maboc.nl maboc

    Nice demonstration.

    May I suggest that you close your connection to the database when you are done with it. Your connection pool (if you are using one) will quickly run out of free connections, if you don’t close the connections your are done with.

    Best Regards

    Martijn

    • http://null- Lino

      Where close my connection ??

  • dexter

    how to establish a JDBC connection using Oracle database?
    I tried to do it the way it is shown here using mysql,but it does not work.
    I am able to establish JDBC connection in JSP though by passing Pagecontext to the bean .

  • Femi

    For more info, here is my customer bean source code:

    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.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 {
     
    	/**
    	 * 
    	 */
    	private static final long serialVersionUID = -5063419797864907761L;
    	//resource injection
    	@Resource(name="jdbc/inventorymgt")
    	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/inventorymgt");
    		} 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 productid, productname, description, addeddate from products"); 
     
    		//get customer data from database
    		ResultSet result =  ps.executeQuery();
     
    		List list = new ArrayList();
     
    		while(result.next()){
    			Customer cust = new Customer();
     
    			cust.setCustomerId(result.getString("productid"));
    			cust.setName(result.getString("productname"));
    			cust.setDescription(result.getString("description"));
    			cust.setAddedDate(result.getDate("addeddate"));
     
    			//store all data into a List
    			list.add(cust);
    		}
     
    		return list;
    	}
    }
  • Femi

    Hi Mykong,

    Great job you have done and I’m trying to use it.
    I kinda twist the codes a bit and they are fine without errors on the page. I’m using ms sql server.

    I created a data source which was ok, connection successful, but when I run it using eclipse galileo:

    first it gives a prompt as if I want to download or save a page, then when I click find on the prompt,

    I get “File Association: Windows has the following information about this MIME type. This page will help you find software needed to open your file”

    on another opened web page and then on Eclipse browser I get “Navigation to the Web Page Cancelled.”
    Please help.

    • http://www.mkyong.com mkyong

      What URL you trying to access? What’s your app server? Look like your server doesn’t understand your file extension.

      • Femi

        my app server is tomcat, I’m sure sure of the url, the inventorymgr is a data source nut my db is ms sql server based. pls help!

  • http://luchogb.com Lucho

    Hey budy, this web app doesnt work , check this error :

    /default.xhtml @20,8 value=”#{customer.getCustomerList()}” Error Parsing: #{customer.getCustomerList()}

    • http://www.mkyong.com mkyong

      may be you got the wrong jsf dependency? Are you compile this project with Maven?

  • tommcatt28

    Found interface com.mysql.jdbc.Connection, but class was expected now is the error

    • http://www.mkyong.com mkyong

      Sorry, don’t get you.

  • tommcatt28

    My server says /ICEfacesPage1.xhtml @22,28 value=”#{leausuario.Userlist}”: Propiedad ‘Userlist’ no hallada en el tipo clases.Leausuario

    WHERE leausuario is the bean and Userlist is Customerlist()

    PLEASE HELP ME I DON KNOW WHAT DO!!!!

  • Mango

    Can u kindly help me in updating the data base(MySQL or SQL Server) with user entered data in JSF 2.0 without using spring and hibernate

    • http://www.mkyong.com mkyong

      Then just use JDBC like this article.

  • Tutu

    Hi Mkyong,

    When I run the example, I have this message: com.sun.faces.context.FacesFileNotFoundException

    What I can do?

    Thanks.

    • http://www.mkyong.com mkyong

      What’s your last error stack? caused by…

  • Pingback: JDBC Tutorials()

  • Emmanuel

    How I can insert data in database using jsf 2.0

  • Olaf

    Hi Yong,

    thanks for your tutorial!

    When starting the default.xhtml, I got the following error:

    /default.xhtml @26,63 value=”#{customer.getCustomerList()}” Error Parsing: #{customer.getCustomerList()}

    could you help me please?

    Regards
    Olaf

    • http://www.mkyong.com mkyong

      are you using JSF 2.0? Method expression is supported in JSF 2.0 only.

      • Olaf

        Yes, I think so.

        I copied your examples, using tomcat and mysql (and configured them how you described).

        • http://www.mkyong.com mkyong

          Check this example – http://www.mkyong.com/jsf2/jsf-2-0-hello-world-example/

          Make sure you have all the required library for JSF 2.0 development.

          And also, check some common errors below :
          http://www.mkyong.com/tutorials/jsf-2-0-tutorials/

          • Bharathi

            Hi mkyong,

            How r u. i am getting the same error what Olaf got. I you provide a solution for this.Whta I have to i am using JSF 2.1.6 jar files.

            Another thing when I click on ctrl+spacebar It dosnt show the suggestion in default.xhtml.

            When I create a Dynamic web project without maven then is shows the suggestion .Can you help me.

            I like the way u contributed with these much of examples.Keep the way you are posting the sample examples.

        • Raju Ilayaraja

          Hi mkyong,Olaf

          I am also getting this error:
          When starting the default.xhtml, I got the following error:

          /default.xhtml @26,63 value=”#{customer.getCustomerList()}” Error Parsing: #{customer.getCustomerList()}

          Can you please help!!!

  • http://www.jdbctutorial.net Tousif Khan

    Hi Yong.

    Please write a tutorial on JSF Pagination using MySQL.
    Thanks