Example : Database Pooling With Tomcat7 and SQLServer


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

 

Advertisements

JDBC Connection using MDB file

/* Program:

* Setup database driver manager to understand and use ODBC MS-ACCESS data source.

*

* Compile as: javac dbAccess.java

*/
/**
*
* @author abhishek.anne@gmail.com
* @publish @ playjava.wordpress.com
*
*/

import java.sql.*;

public class dbAccess

{

public static void main(String[] args)

{

try

{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

String database =

"jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=myDB.mdb;";

Connection conn = DriverManager.getConnection(database, "", "");

Statement s = conn.createStatement();

// create a table

String tableName = "myTable" + String.valueOf((int)(Math.random() * 1000.0));

String createTable = "CREATE TABLE " + tableName +

" (id Integer, name Text(32))";

s.execute(createTable);

// enter value into table

for(int i=0; i&lt;25; i++)

{

String addRow = "INSERT INTO " + tableName + " VALUES ( " +

String.valueOf((int) (Math.random() * 32767)) + ", 'Text Value " +

String.valueOf(Math.random()) + "')";

s.execute(addRow);

}

// Fetch table

String selTable = "SELECT * FROM " + tableName;

s.execute(selTable);

ResultSet rs = s.getResultSet();

while((rs!=null) &amp;&amp; (rs.next()))

{

System.out.println(rs.getString(1) + " : " + rs.getString(2));

}

// drop the table

String dropTable = "DROP TABLE " + tableName;

s.execute(dropTable);

// close and cleanup

s.close();

conn.close();

}

catch(Exception ex)

{

ex.printStackTrace();

}

}

}