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

By: Baski Emailed: 1757 times Printed: 2444 times    

Latest comments
By: rohit kumar - how this program is work
By: Kirti - Hi..thx for the hadoop in
By: Spijker - I have altered the code a
By: ali mohammed - why we use the java in ne
By: ali mohammed - why we use the java in ne
By: mizhelle - when I exported the data
By: raul - no output as well, i'm ge
By: Rajesh - thanx very much...
By: Suindu De - Suppose we are executing

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>


JSP Home | All JSP Tutorials | Latest JSP Tutorials

Sponsored Links

If this tutorial doesn't answer your question, or you have a specific question, just ask an expert here. Post your question to get a direct answer.



Bookmark and Share

Comments(5)


1. View Comment

Seems to be the best tutorial on this topic. At least as far as I have searched. Thanks for saving my time. Just followed the steps and I could make it work like a gem. Thx Baski.

View Tutorial          By: Bob Nickson at 2008-08-26 02:50:29
2. View Comment

thank u. it is very useful for users

View Tutorial          By: c.balasubramani at 2008-09-24 03:55:47
3. View Comment

good Article!! you can add context settings in application's in META-INF/context.xml instead of server.xml (if u dont want to restart the server) like following :
<Resource name="jdbc/urDB" auth="Container"
type="javax.sql.DataSource" username="username" password="pass"
driverClassName="com.mysql.jdbc.Driver" url="your URL"
maxActive="8" maxIdle="4"/>


View Tutorial          By: Amit Nigam at 2009-06-10 07:35:20
4. View Comment

this is a site site for our problem to be sorted out in a second
thanks
atique


View Tutorial          By: Atique at 2009-11-02 00:15:18
5. View Comment

its good

View Tutorial          By: sumanth at 2010-04-10 20:10:00

Your name (required):


Your email(required, will not be shown to the public):


Your sites URL (optional):


Your comments:



More Tutorials by Baski
Compiling multiple source files and specifying classpath using javac
The equals() Method example in Java
Default Values for Data Types in Java
JSP Example to connect to MS SQL database using Tomcat Connection Pool
<convertNumber> and <convertDateTime> in JSF
Enable/Disable Scripting Elements in JSP
Using calloc() Function in C
lseek() sample program in C
Arrays of Structures example program in C
The Basic Syntax Expression Language in JSP
Initialization of Pointer Arrays in C
Functions returning non-integer values in C
Increment and Decrement Operators in C
Getting Started with C
What is JSF (JavaServer Faces)?

More Tutorials in JSP
LifecycleException: service.getName(): "Catalina"; Protocol handler start failed: `java.net.BindException: Permission denied <null>:80
JSP Alert Example
JSP CheckBox Example
Uploading an Image to a Database using JSP
Uploading a file to a server using JSP
A JSP page that gets properties from a bean
The page Directive in JSP
The taglib, tag, include, attribute and the variable Directive in JSP
Declarations in JSP
Scriptlets and Expressions in JSP
Tag Libraries in JSP
The Request Object in JSP
The Response Object in JSP
The Out Object in JSP
The Session Object in JSP

More Latest News
Most Viewed Articles (in JSP )
JSP Example to connect to MS SQL database and retrieve records
What are the different scopes in JSP?
JSP Alert Example
Comparison operators in JSP
JSP Program for display Date
JSP CheckBox Example
Sending Email using JSP
Disabling Scriptlets in JSP using web.xml
Uploading an Image to a Database using JSP
Enable/Disable Scripting Elements in JSP
Embedding java codes in jsp sciptlets
Arithmetic Evaluation Using the Expression Language in JSP
JSP Example to connect to MS SQL database using Tomcat Connection Pool
Writing your first JSP page
Uploading a file to a server using JSP
Most Emailed Articles (in JSP)
The taglib, tag, include, attribute and the variable Directive in JSP
What is JSP?
Cookies using JSP or Java Bean
Click to Activate and Use this control
The JSP Program running first Time.
Scriptlets
Server Side Programming
Syntax For JSP Declaratives
Embedding java codes in jsp sciptlets
Tags using in jsp
JSP pages in servlet
Combining Scriptlets with HTML
JSP Directives
Form processing in JSP
Declaring variable in JSP