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

By: Baski Viewed: 171 times  Printer Friendly Format    


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>



Most Viewed Articles (in JSP )

Latest Articles (in JSP)

Comment on this tutorial

Subscribe to Tutorials

Related Tutorials

Archived Comments

1. Seems to be the best tutorial on this topic. At le
View Tutorial          By: Bob Nickson at 2008-08-26 02:50:29

2. thank u. it is very useful for users
View Tutorial          By: c.balasubramani at 2008-09-24 03:55:47

3. good Article!! you can add context settings in app
View Tutorial          By: Amit Nigam at 2009-06-10 07:35:20

4. this is a site site for our problem to be sorted o
View Tutorial          By: Atique at 2009-11-02 00:15:18

5. its good
View Tutorial          By: sumanth at 2010-04-10 20:10:00

6. its good
View Tutorial          By: sumanth at 2010-04-10 20:10:05

7. hii site may be gud bt hasn't solve my problem....
View Tutorial          By: billo rani at 2010-07-05 04:14:03

8. Wow, what a shit example!
View Tutorial          By: Wow at 2012-08-23 04:34:45