JSP Example to connect to MS SQL database and retrieve records

By: Abinaya Emailed: 1607 times Printed: 2079 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. This example JSP program shows how to connect to a MSSQL database from your JSP program. 

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).

The database server can be residing anywhere in the network. You just need to get the IP address or the domain name of the server together with the database name, username and password. Just remember to construct the right url. This sample JSP page 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 con;
java.sql.Statement s;
java.sql.ResultSet rs;
java.sql.PreparedStatement pst;

con=null;
s=null;
pst=null;
rs=null;

// Remember to change the next line with your own environment
String url= 
"jdbc:jtds:sqlserver://nameofyourdatabaseserver.or.ipaddress/yourdatabasename";
String id= "username";
String pass = "password";
try{

Class.forName("net.sourceforge.jtds.jdbc.Driver");
con = java.sql.DriverManager.getConnection(url, id, pass);

}catch(ClassNotFoundException cnfex){
cnfex.printStackTrace();

}
String sql = "select top 10 * from tbl_sys_user";
try{
s = con.createStatement();
rs = s.executeQuery(sql);
%>

<%
while( rs.next() ){
%><tr>
<td><%= rs.getString("cust_id") %></td>
<td><%= rs.getString("rdate") %></td>
<td><%= rs.getString("email") %></td>
</tr>
<%
}
%>

<%

}
catch(Exception e){e.printStackTrace();}
finally{
if(rs!=null) rs.close();
if(s!=null) s.close();
if(con!=null) con.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(27)


1. View Comment

thanks f0r this p0st... it heLped me much... keep it up!

View Tutorial          By: martz at 2009-04-29 02:27:19
2. View Comment

In good form, don't forget to add column headers and to close the table tag at the bottom....

View Tutorial          By: Jake at 2009-07-13 14:20:16
3. View Comment

i'm a student and new to all these HTML and JSP.
i want to learn to do this Tomcat with Ms SQL where my Uni only provide us these resources. i've tried to copy the above codes and past to my Tomcat and tried to run, i can only get the junk text as below:
May i know what are the installation needed?
can any1 suggest on how to start from installing all the application to connecting them.
I'm using Windows based OS,
i got Ms SQL Server 2005 Studio Express installer,
i got Apache Tomcat 5.5.27 Server installer.
what are the necessary configuration needed to run these server application on my PC (i'm testing with my PC as server at the moment).

can any1 pls help~

<%@ page import="java.util.*" %> <%@ page import="javax.sql.*;" %> <% java.sql.Connection con; java.sql.Statement s; java.sql.ResultSet rs; java.sql.PreparedStatement pst; con=null; s=null; pst=null; rs=null; // Remember to change the next line with your own environment String url= "jdbc:jtds:sqlserver://research.utar.edu.my/ResearchPortal"; String id= "username"; String pass = "password"; try{ Class.forName("net.sourceforge.jtds.jdbc.Driver"); con = java.sql.DriverManager.getConnection(url, id, pass); }catch(ClassNotFoundException cnfex){ cnfex.printStackTrace(); } String sql = "select top 10 * from tbl_sys_user"; try{ s = con.createStatement(); rs = s.executeQuery(sql); %> <% while( rs.next() ){ %><% } %> <% } catch(Exception e){e.printStackTrace();} finally{ if(rs!=null) rs.close(); if(s!=null) s.close(); if(con!=null) con.close(); } %>
<%= rs.getString("cust_id") %> <%= rs.getString("rdate") %> <%= rs.getString("email") %>


View Tutorial          By: sillee at 2009-08-13 03:22:14
4. View Comment

The above codes is working for me~
yeah, what if i am running on localhost?
what shd the code be?


View Tutorial          By: rejectee at 2009-10-23 22:14:25
5. View Comment

what if i am running on localhost?
what shd the code be?
pl explain all de steps in detail


View Tutorial          By: harish at 2010-02-03 03:47:08
6. View Comment

nice one its very useful

View Tutorial          By: jhejhe at 2010-02-21 05:03:50
7. View Comment

i m student ,can any one help me in above code string url is what means which address specify it.
bcoz i m new in this one
thnk u


View Tutorial          By: devendra kumar at 2010-03-09 04:34:10
8. View Comment

Can u tell it can also use for connecting to oracle.......

View Tutorial          By: Ankur Jain at 2010-08-06 05:31:08
9. View Comment

if done all the steps but i have found out this error after i run ,

exception

org.apache.jasper.JasperException: An exception occurred processing JSP page /test1.jsp at line 27

24: try{
25:
26: Class.forName("net.sourceforge.jtds.jdbc.Driver");
27: con = java.sql.DriverManager.getConnection(url, id, pass);
28:
29: }catch(ClassNotFoundException cnfex){
30: //cnfex.printStackTrace();

please i need to know why i got this error i have followed the same coding tec. and i got this error , i need someone to tell me where is the problem in that .


View Tutorial          By: hisham at 2010-11-24 11:01:54
10. View Comment

hi i am new to JSP wht is class.forname in the code
when i run program the code executes upto Class.forName and stops.


help on this
thanks in advance


View Tutorial          By: chanti at 2011-05-07 03:02:26
11. View Comment

Dear Friends, I get the following exception...
java.sql.SQLException: Network error IOException: Connection refused: connect
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:410)..

My Code is....
String driverName = "net.sourceforge.jtds.jdbc.Driver";
String url = "jdbc:jtds:sqlserver://localhost:1433";
Class.forName(driverName);
DriverManager.getConnection(url);
System.out.println("Connected Successfully");

Please give the solution for that one...
Thanks...
at net.sourceforge.jtds.jdbc.ConnectionJDBC3.<init>(ConnectionJDBC3.java:50)
at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at com.thanas.test.DBSCONNECTION.getConnection(DBSCONNECTION.java:20)
at com.thanas.test.DBSCONNECTION.main(DBSCONNECTION.java:10)
Caused by: java.net.ConnectException: Connection refused: connect
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.PlainSocketImpl.doConnect(Unknown Source)
at java.net.PlainSocketImpl.connectToAddress(Unknown Source)
at java.net.PlainSocketImpl.connect(Unknown Source)
at java.net.SocksSocketImpl.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at net.sourceforge.jtds.jdbc.SharedSocket.createSocketForJDBC3(SharedSocket.java:307)
at net.sourceforge.jtds.jdbc.SharedSocket.<init>(SharedSocket.java:257)
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:311)
... 6 more


View Tutorial          By: NT.Jay at 2011-10-06 05:33:09
12. View Comment

Did you check if you are using the correct username and password? also in your url String, add the database name that you are connecting to at the end /dbname

View Tutorial          By: Murugan at 2011-11-18 09:28:47
13. View Comment

Here is my issue. The sql which comes thru the JTDS driver are not parameterized in sql server. This causes the cache to grow at a rapid speed and causing issues to the memory.
My question is how to force parameterization with JTDS driver.
Please let me know


View Tutorial          By: Kumar at 2012-02-21 02:42:08
14. View Comment

My Page URL- http://localhost:8080/WebApplication1/index.jsp
Whenever i run this program, it's not showing me any record but i have inserted records into my Online Database...and i am not getting any error or exception...


View Tutorial          By: aMIT sURI at 2012-05-12 10:43:21
15. View Comment

PLZ HELP ME WITH THIS.. WHEN I RUN THE FILE ON APACHE SERVER, AN EXCEPTION IS GENERATED. THE EXCEPTION IS:
Exception: [Microsoft][ODBC Driver Manager] Invalid string or buffer length
PLEASE., PLEASE DO HELP ME...

<%@ page import="java.sql.*" %>
<%
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:SystemDsn;user=sa;password=.");
Statement ps=con.createStatement();

ResultSet rs=ps.executeQuery("select * from link_sql");
out.println("<table>");

while(rs.next())
{
out.println("<tr><td>");
out.println("<a href='"+rs.getString("link")+"'>"+rs.getString("anchor")+"</a>");
out.println("</td></tr>");
}
out.println("</table>");
}
catch(Exception e)
{
out.println("Exception: "+e.getMessage());
}

%>


View Tutorial          By: Rahul at 2012-08-18 12:26:39
16. View Comment

hi guys,
its working in a good manner impressive .........happy about it guys keep roll..:)


View Tutorial          By: saran at 2012-12-18 13:15:12
17. View Comment

I am getting a blank page. Closed the table tag and added the headers. Below is the modified code. Please let me know what is the issue with this?

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

java.sql.Connection con;
java.sql.Statement s;
java.sql.ResultSet rs;
java.sql.PreparedStatement pst;

con=null;
s=null;
pst=null;
rs=null;

// Remember to change the next line with your own environment
String url=
"jdbc:jtds:sqlserver://harshal:1433/jasptest";
String id= "sa";
String pass= "password";
try{

Class.forName("net.sourceforge.jtds.jdbc.Driver");
con = java.sql.DriverManager.getConnection(url, id, pass);

}catch(ClassNotFoundException cnfex){
cnfex.printStackTrace();

}
//String sql = "select * from tbl_sys_user";
String sql = "select top 10 * from tbl_sys_user";
try{
s = con.createStatement();
rs = s.executeQuery(sql);
%>

<%
while( rs.next() ){
%>


<tr>
<th>Header 1
<td><%= rs.getString("cust_id") %></td>
</th>
</tr>

<tr>
<th> Header 2
<td><%= rs.getString("rdate") %></td>
<th>
</tr>

<tr>
<th>
<td><%= rs.getString("email") %></td>
</th>
</tr>
<%
}
%>

<%

}
catch(Exception e){e.printStackTrace();}
finally{
if(rs!=null) rs.close();
if(s!=null) s.close();
if(con!=null) con.close();
}

%>
</table>
</body>
</html>


View Tutorial          By: Harshal at 2013-02-19 14:56:35
18. View Comment

(i'm a senior PHP programmer, but a newbie in JSP, so sorry in advance if is a stupid question)

for what we need variable "pst" if not used? o.O


View Tutorial          By: LOL at 2013-03-22 08:57:21
19. View Comment

I have already establish connection with mysql in servlet file.so i have to use that connection in jsp file.how it is possible.i'm using
eclipse.


View Tutorial          By: shaishav at 2013-06-20 04:27:44
20. View Comment

it is not working

View Tutorial          By: shaishav at 2013-06-20 08:03:06
21. View Comment

When run this code then I am getting a blank page.
pls help me.


View Tutorial          By: Paritosh Chandra Dey at 2013-08-14 08:55:42
22. View Comment

I tried to use excute this code to retreive records from my Database, however, it gave me a user login error, with the stacktrace below. Could anyone help?

Stacktrace:
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:491)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:395)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:308)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:259)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)


root cause

javax.servlet.ServletException: Login failed for user 'foo'.
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:841)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:774)
org.apache.jsp.port.pf0001_jsp._jspService(pf0001_jsp.java:123)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:371)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:308)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:259)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)


View Tutorial          By: PL at 2013-08-27 02:58:55
23. View Comment

some of the errors above e.g the jasper exception might come from the sqlserver Agent not beeing started or sqlserver beeing setup (default nowadays) not to listen to 1433 and tcp ip.
try opening the sqlserver configuration manager to edit the settings
I got this example working using jtds-1.3.1.jar in the directory
C:\Program Files\Apache Software Foundation\Tomcat 6.0\lib
so thanks a lot :)


View Tutorial          By: Robert at 2013-09-14 23:24:57
24. View Comment

I had a problem, which is the password when windows haven't? I have windows authentication in sql-server

View Tutorial          By: jua at 2013-09-16 23:16:27
25. View Comment

Most Our friends ask About error occurred during the run the Apache tomcat server ......... before that you must have define tour connection manager and URL path including MySQL server username , password and root (like local-host) and specially port number of your MySQL in order to communicate with Ur DB !!!

View Tutorial          By: Chala G. at 2015-01-13 16:52:10
26. View Comment

Thanks Man , You are Genius i have been looking for this code more than 3 days !! thanks again !

View Tutorial          By: hamza Ouni at 2015-02-22 13:38:02
27. View Comment

i am trying to run the above example in eclipse but i am not getting the run option it was showing the run configurations. how can i solve it.

View Tutorial          By: Prathyusha at 2015-03-10 11:01:06

Your name (required):


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


Your sites URL (optional):


Your comments:



More Tutorials by Abinaya
How to compile a Java program - javac
The clone() Method in Java
Data Types in Java
JSP Example to connect to MS SQL database and retrieve records
faces-config.xml to DirectTraffic in the JSF Application
Enabling Expression Language Evaluation in JSP
Using malloc() Function in C
ActionErrors and ActionError in Struts
Open, Creat, Close, Unlink system calls sample program in C
Structures and Functions in C
Introduction to JSP expression language
Multi-dimensional Arrays in C (Explained using date conversion program)
A C program similar to grep command in UNIX
Type Conversions in C (String to Integer, isdigit() etc)
Basics of C

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
JSP Alert Example
What are the different scopes in JSP?
JSP CheckBox Example
JSP Program for display Date
Uploading a file to a server using JSP
Sending Email using JSP
Embedding java codes in jsp sciptlets
Arithmetic Evaluation Using the Expression Language in JSP
Writing your first JSP page
Packaging Servlets
The Advantages of JSP
Automatically Refreshing a JSP
The page Directive in JSP
What is JSP?
Most Emailed Articles (in JSP)
Embedding java codes in jsp sciptlets
Packaging Servlets
What are the different scopes in JSP?
A JSP page that gets properties from a bean
The Advantages of JSP
The taglib, tag, include, attribute and the variable Directive in JSP
Sessions in JSP
Retrieving a Portion of a String
Server-side plug-Ins
The Advantages of Servlets Over “Traditional” CGI
Enable/Disable Scripting Elements in JSP
Scriptlets and Expressions in JSP
The Response Object in JSP
Uploading a file to a server using JSP
JSP Alert Example