Connection Pool in JSF with @Resource Injection





Hey guys in this post, we will discuss setting up connection pooling in JavaServer faces application with full code example

Complete example


We will create this example step by step, follow this tutorial till the end

Read More:

Create Dynamic Web Project


To create a dynamic web project, you can read the following tutorial

Add the JDBC driver Jar file


Download and add the Jar file to the WEB-INF/lib folder of the JSF application.

Download: http://dev.mysql.com/downloads

Create database


Run the following queries in MySQL database

CREATE DATABASE jsfwebapp;

USE jsfwebapp;

CREATE TABLE tbl_employees
(
	id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    department VARCHAR(255) NOT NULL
);

INSERT INTO tbl_employees (name, email, department)
VALUES ("Sharath", "[email protected]", "IT");
INSERT INTO tbl_employees (name, email, department)
VALUES ("Praveen", "[email protected]", "Sales");
INSERT INTO tbl_employees (name, email, department)
VALUES ("Ramesh", "[email protected]", "Marketing");

Define the connection pool


Create META_INF/context.xml file and add the following content

<Context>
	<Resource 
		name="jdbc/employee_tracker"
		auth="Container"
		type="javax.sql.DataSource"
		maxActive="20"
		maxIdle="5"
		maxWait="10000"
		username="root"
		password="root"
		driverClassName="com.mysql.jdbc.Driver"
		url="jdbc:mysql://localhost:3306/jsfwebapp"
	/>
</Context>

name="jdbc/employee_tracker", this is just a name of the connection pool. Later we will use the same name in the configuration file to reference the datasource.

Make sure to the change the database username and password as per your installation

Configure the reference


Open web.xml file and add the following content

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1">
  <display-name>JSF-hello-world</display-name>
  <servlet>
    <servlet-name>Faces Servlet</servlet-name>
    <servlet-class>javax.faces.webapp.FacesServlet</servlet-class>
    <load-on-startup>1</load-on-startup>
  </servlet>
  <servlet-mapping>
    <servlet-name>Faces Servlet</servlet-name>
    <url-pattern>/faces/*</url-pattern>
  </servlet-mapping>
  <resource-ref>
  	<description>Employee Tracker</description>
  	<res-ref-name>jdbc/employee_tracker</res-ref-name>
  	<res-type>javax.sql.DataSource</res-type>
  	<res-auth>Container</res-auth>
  </resource-ref>
</web-app>

Here <res-ref-name> is the same name that we given in creating the connection pool

Create a Servlet


Create TestConnection.java file inside the in.bushansirgur.jsf.util package and add the following content

package in.bushansirgur.jsf.util;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.annotation.Resource;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

@WebServlet("/TestConnection")
public class TestConnection extends HttpServlet {
	
	private static final long serialVersionUID = 1L;
     
	@Resource(name="jdbc/employee_tracker")
	private DataSource dataSource;
	
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		PrintWriter out = response.getWriter();
		response.setContentType("text/plain");
		
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		
		try {
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery("select * from tbl_employees");
			while(resultSet.next()) {
				String name = resultSet.getString("name");
				System.out.println("Name: "+name);
			}
			
		}
		catch (SQLException ex) {
			ex.printStackTrace();
		}
	}
}

Run the app


To run the Servlet, right click on the Servlet, select Run As -> choose Run on Server

Aug 02, 2021 4:28:37 PM org.apache.catalina.startup.HostConfig deployDirectory
INFO: Deployment of web application directory [C:\Softwares\apache-tomcat-9.0.50\apache-tomcat-9.0.50\webapps\ROOT] has finished in [23] ms
Aug 02, 2021 4:28:37 PM org.apache.coyote.AbstractProtocol start
INFO: Starting ProtocolHandler ["http-nio-8080"]
Aug 02, 2021 4:28:37 PM org.apache.catalina.startup.Catalina start
INFO: Server startup in [2992] milliseconds
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
Name: Sharath
Name: Praveen
Name: Ramesh

That’s it for this post. If you like this post, then please share this with your friends and collogues. Also share this post on your social media profiles as well.



Bushan Sirgur

Hey guys, I am Bushan Sirgur from Banglore, India. Currently, I am working as an Associate project in an IT company.

Leave a Reply