JSP Example to connect to MS SQL database and retrieve records

By: Abinaya  

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>



Archived Comments

1. onethujpad
View Tutorial          By: onethujpad at 2017-09-21 21:02:39

2. IvanQgs
View Tutorial          By: IvanOgd at 2017-09-19 23:20:16

3. I savor, lead to I discovered exactly what I was having a look for.
You have ended my 4 day l

View Tutorial          By: www.instalatiigplprahova.ro at 2017-07-27 08:34:34

4. I see your page needs some unique content. Writing manually is time consuming, but there is solution
View Tutorial          By: FVREmoryxgb at 2017-06-11 10:56:11

5. magnificent points altogether, you simply received emblem reader.
What would yoou recommend a

View Tutorial          By: Dedra at 2017-04-29 06:58:39

6. JasonNix
View Tutorial          By: JasonNix at 2017-04-24 20:49:47

7. so....
i find this guide very very very very very very.....
shish....
berlin wa

View Tutorial          By: Pierpalle Scrotini (P.S.) at 2017-02-21 07:47:48

8. Here is a good example about how to establish MySQL database connection with JSP -- http://www.mitra
View Tutorial          By: Anil Das at 2016-12-05 10:20:11

9. I have altered the code a
View Tutorial          By: Hardik Dobariya at 2016-03-15 08:40:16

10. i am trying to run the above example in eclipse but i am not getting the run option it was showing t
View Tutorial          By: Prathyusha at 2015-03-10 11:01:06

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

12. Most Our friends ask About error occurred during the run the Apache tomcat server ......... before
View Tutorial          By: Chala G. at 2015-01-13 16:52:10

13. I had a problem, which is the password when windows haven't? I have windows authentication in sql-se
View Tutorial          By: jua at 2013-09-16 23:16:27

14. some of the errors above e.g the jasper exception might come from the sqlserver Agent not beeing sta
View Tutorial          By: Robert at 2013-09-14 23:24:57

15. I tried to use excute this code to retreive records from my Database, however, it gave me a user log
View Tutorial          By: PL at 2013-08-27 02:58:55

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

17. it is not working
View Tutorial          By: shaishav at 2013-06-20 08:03:06

18. I have already establish connection with mysql in servlet file.so i have to use that connection in j
View Tutorial          By: shaishav at 2013-06-20 04:27:44

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

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

20. I am getting a blank page. Closed the table tag and added the headers. Below is the modified code. P
View Tutorial          By: Harshal at 2013-02-19 14:56:35

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

22. PLZ HELP ME WITH THIS.. WHEN I RUN THE FILE ON APACHE SERVER, AN EXCEPTION IS GENERATED. THE EXCEPTI
View Tutorial          By: Rahul at 2012-08-18 12:26:39

23. My Page URL- http://localhost:8080/WebApplication1/index.jsp
Whenever i run this program, it'

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

24. can you send me the java&jsp pdf files to ma mail..
View Tutorial          By: cyberian at 2012-04-04 09:45:32

25. Here is my issue. The sql which comes thru the JTDS driver are not parameterized in sql server. This
View Tutorial          By: Kumar at 2012-02-21 02:42:08

26. Here is my issue. The sql which comes thru the JTDS driver are not parameterized in sql server. This
View Tutorial          By: Kumar at 2012-02-21 02:41:33

27. Did you check if you are using the correct username and password? also in your url String, add the d
View Tutorial          By: Murugan at 2011-11-18 09:28:47

28. Dear Friends, I get the following exception...
java.sql.SQLException: Network error IOExcepti

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

29. Wonderful and thanks for this post !! It helped me a lot
View Tutorial          By: Pritam Preet at 2011-10-03 08:31:17

30. i've already tried to run the jsp but i got no data y no error message, i put the jtds-1.2.5.jar fil
View Tutorial          By: karla at 2011-09-21 20:55:48

31. hi i am new to JSP wht is class.forname in the code
when i run program the code executes upto

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

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

exception

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

33. Can u tell it can also use for connecting to oracle.......
View Tutorial          By: Ankur Jain at 2010-08-06 05:31:08

34. i m student ,can any one help me in above code string url is what means which address specify it.
View Tutorial          By: devendra kumar at 2010-03-09 04:34:10

35. nice one its very useful
View Tutorial          By: jhejhe at 2010-02-21 05:03:50

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

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

37. The above codes is working for me~
yeah, what if i am running on localhost?
what shd t

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

38. i'm a student and new to all these HTML and JSP.
i want to learn to do this Tomcat with Ms SQ

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

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

40. thanks f0r this p0st... it heLped me much... keep it up!
View Tutorial          By: martz at 2009-04-29 02:27:19


Most Viewed Articles (in JSP )

Latest Articles (in JSP)

Comment on this tutorial