JSP Example to connect to MS SQL database using Tomcat Connection Pool

By: Baski in JSP Tutorials on 2007-10-12

This is a simple JSP program to connect to MSSQL database using the connection pool in Tomcat by creating JNDI name for the DataSource.

You also need to download the appropriate driver to connect to MSSQL server from your JSP page. In this tutorial we are using the JTDS driver which can be downloaded from http://jtds.sourceforge.net/ Once you have downloaded the jar file you will have to copy it to your common lib folder in your tomcat (or any other servlet container you are using).

For using the JNDI in Tomcat, you will have to edit your context setting in server.xml

A sample context setting in Tomcat 4.1.29 is given below:

<Context path="/Payment" docBase="C:\Applications\Payment">
    <Logger className="org.apache.catalina.logger.FileLogger" prefix="payment_log." suffix=".txt"
        timestamp="true" />
    <Resource auth="Container" name="jdbc/paymentDB" scope="Shareable" type="javax.sql.DataSource" />
    <ResourceParams name="jdbc/paymentDB">
        <parameter>
            <name>factory</name>
            <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
        </parameter>
        <parameter>
            <name>url</name>
            <value></value>
        </parameter>
        <parameter>
            <name>password</name>
            <value>yourdbpassword</value>
        </parameter>
        <parameter>
            <name>maxWait</name>
            <value>10000</value>
        </parameter>
        <parameter>
            <name>maxActive</name>
            <value>100</value>
        </parameter>
        <parameter>
            <name>driverClassName</name>
            <value>net.sourceforge.jtds.jdbc.Driver</value>
        </parameter>
        <parameter>
            <name>username</name>
            <value>yourdbusername</value>
        </parameter>
        <parameter>
            <name>maxIdle</name>
            <value>30</value>
        </parameter>
    </ResourceParams>
</Context>

As you can see in the context example for connection pooling above, you are creating a JNDI named jdbc/paymentDB with the url that connects to your database server and the username and password. Once you have done that you can use the sample JSP page given below to get the context of this JNDI and use this to connect to the database. This sample program assumes that there is a table named tbl_sys_user in your database and it has fields with names, cust_id, rdate and email. In your case, you will have to change the names according to your requirement.

<html>
<head><title>Enter to database</title></head>
<body>
<table>
<%@ page import="java.util.*" %>
<%@ page import="javax.sql.*;" %>

<% 

java.sql.Connection c1;
java.sql.Statement s1;
java.sql.ResultSet rs1;
java.sql.PreparedStatement pst1;
DataSource paymentDB;

c1=null;
s1=null;
pst1=null;
rs1=null;

javax.naming.Context initCtx = new javax.naming.InitialContext();
javax.naming.Context envCtx = (javax.naming.Context) initCtx.lookup("java:comp/env");
paymentDB = (DataSource) envCtx.lookup("jdbc/paymentDB");

try{
if(paymentDB == null) {
javax.naming.Context initCtx1 = new javax.naming.InitialContext();
javax.naming.Context envCtx1 = (javax.naming.Context) initCtx1.lookup("java:comp/env");
paymentDB = (DataSource) envCtx1.lookup("jdbc/paymentDB");
}
}
catch(Exception e){
System.out.println("inside the context exception");
e.printStackTrace();
}


c1 = paymentDB.getConnection();
String sq1= "select top 10 * from tbl_sys_user";
pst1 = c1.prepareStatement(sq1);
rs1 = pst1.executeQuery();
while( rs1.next() ){
%>

<tr>
<td><%= rs1.getString("cust_id") %></td>
<td><%= rs1.getString("rdate") %></td>
<td><%= rs1.getString("email") %></td>
</tr>
<%
}


if(pst1!=null) pst1.close();
if(rs1!=null) rs1.close();
if(c1!=null) c1.close();
%>


</body>
</html>

Previous
<< JSP Example to connect to MS SQL database and retrieve records

Next
Calling JSP from a batch file >>