This is an step by step guide to create Database Pooling for java web based application which is recommended for preformance.
Ingredients:
Container used : Tomcat v7
Databse used : MS SQL Server 2008
IDE used : Eclipse
Get latest Microsoft JDBC Driver 4.0 for SQL server from here
Use sqljdbc4.jar for Java6.
Step 1:
Create dynamic web project in Eclipse say DBPoolingSample
.
Step 2:
Create XML file in WebContentMETA-INFcontext.xml
<?xml version="1.0" encoding="UTF-8"?> <Context> <!-- Specify a JDBC datasource --> <Resource name="jdbc/testdb" auth="Container" type="javax.sql.DataSource" username="<username>" password="<password>" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" url="jdbc:sqlserver://localhost:1434;DatabaseName=connectpool_db" maxActive="10" maxIdle="4" /> </Context>
Note: Check IP for SQL server instance.
Note: Change username password.
Step 3:
Create a java class to retrieve Connection object.
package db.;
package db; import java.sql.Connection; import java.sql.SQLException; import javax.naming.Context; import javax.naming.InitialContext; import javax.naming.NamingException; import javax.sql.DataSource; /** * * @author abhishek.anne * */ public class DBConnect { private DataSource dataSource; private Connection connection; private static DBConnect moDbConnect; private DBConnect() { try { // Get DataSource Context initContext = new InitialContext(); Context envContext = (Context) initContext.lookup("java:/comp/env"); dataSource = (DataSource) envContext.lookup("jdbc/testdb"); } catch (NamingException e) { e.printStackTrace(); } } public static Connection getConnection() throws NullPointerException{ if (moDbConnect != null) { try { moDbConnect.connection = moDbConnect.dataSource.getConnection(); } catch (SQLException sqle) { sqle.printStackTrace(); } return moDbConnect.connection; } else { moDbConnect = new DBConnect(); try { moDbConnect.connection = moDbConnect.dataSource.getConnection(); } catch (SQLException sqle) { sqle.printStackTrace(); } return moDbConnect.connection; } } }
Step 4:
Create anyservlet to use connection obejct to perform db operation.
package servlet; 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.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import db.DBConnect; public class DBServlet extends HttpServlet { public void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { Connection connection=null; Statement statement=null; ResultSet resultSet = null; try { connection = DBConnect.getConnection(); statement = connection.createStatement(); String query = "SELECT * FROM [coonectpool_db].[dbo].[user]"; resultSet = statement.executeQuery(query); PrintWriter out=resp.getWriter(); while (resultSet.next()) { out.println(resultSet.getInt(1) + resultSet.getString(2)); } } catch (SQLException e) { e.printStackTrace(); }finally { try { if (null != resultSet) resultSet.close(); } catch (SQLException e) { e.printStackTrace(); } try { if (null != statement) statement.close(); } catch (SQLException e) { e.printStackTrace(); } try { if (null != connection) connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
Step 5:
Sample web.xml file
<?xml version="1.0" encoding="UTF-8"?> <web-app id="WebApp_ID" version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"> <display-name>TomcatConnectionPooling</display-name> <welcome-file-list> <welcome-file>index.jsp</welcome-file> </welcome-file-list> <servlet> <servlet-name>TestServlet</servlet-name> <servlet-class> servlet.DBServlet </servlet-class> </servlet> <servlet-mapping> <servlet-name>TestServlet</servlet-name> <url-pattern>/servlet/test</url-pattern> </servlet-mapping> </web-app>
Step 6:
Deploy Application and browse
http://localhost:<port>/DBPoolingSample/servlet/test
By making driver change you can connect to respective Database server
like:
MY SQL:com.mysql.jdbc.Driver
Oracle :oracle.jdbc.driver.OracleDriver