Programming Tutorials

JSP Example to connect to MS SQL database and retrieve records

By: Abinaya Printer Friendly Format    

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>



Most Viewed Articles (in JSP )

Latest Articles (in JSP)

Comment on this tutorial

Subscribe to Tutorials

Related Tutorials

Archived Comments

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

2. In good form, don't forget to add column headers a
View Tutorial          By: Jake at 2009-07-13 14:20:16

3. i'm a student and new to all these HTML and JSP.
View Tutorial          By: sillee at 2009-08-13 03:22:14

4. The above codes is working for me~
yeah, wh
View Tutorial          By: rejectee at 2009-10-23 22:14:25

5. what if i am running on localhost?
what shd
View Tutorial          By: harish at 2010-02-03 03:47:08

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

7. i m student ,can any one help me in above code str
View Tutorial          By: devendra kumar at 2010-03-09 04:34:10

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

9. if done all the steps but i have found out this er
View Tutorial          By: hisham at 2010-11-24 11:01:54

10. hi i am new to JSP wht is class.forname in the cod
View Tutorial          By: chanti at 2011-05-07 03:02:26

11. i've already tried to run the jsp but i got no dat
View Tutorial          By: karla at 2011-09-21 20:55:48

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

13. Dear Friends, I get the following exception...

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

14. Did you check if you are using the correct usernam
View Tutorial          By: Murugan at 2011-11-18 09:28:47

15. Here is my issue. The sql which comes thru the JTD
View Tutorial          By: Kumar at 2012-02-21 02:41:33

16. Here is my issue. The sql which comes thru the JTD
View Tutorial          By: Kumar at 2012-02-21 02:42:08

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

18. My Page URL- http://localhost:8080/WebApplication1
View Tutorial          By: aMIT sURI at 2012-05-12 10:43:21

19. PLZ HELP ME WITH THIS.. WHEN I RUN THE FILE ON APA
View Tutorial          By: Rahul at 2012-08-18 12:26:39

20. hi guys,
its working in a good manner impre
View Tutorial          By: saran at 2012-12-18 13:15:12

21. I am getting a blank page. Closed the table tag an
View Tutorial          By: Harshal at 2013-02-19 14:56:35

22. (i'm a senior PHP programmer, but a newbie in JSP,
View Tutorial          By: LOL at 2013-03-22 08:57:21

23. I have already establish connection with mysql in
View Tutorial          By: shaishav at 2013-06-20 04:27:44

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

25. When run this code then I am getting a blank page.
View Tutorial          By: Paritosh Chandra Dey at 2013-08-14 08:55:42

26. I tried to use excute this code to retreive record
View Tutorial          By: PL at 2013-08-27 02:58:55

27. some of the errors above e.g the jasper exception
View Tutorial          By: Robert at 2013-09-14 23:24:57

28. I had a problem, which is the password when window
View Tutorial          By: jua at 2013-09-16 23:16:27

29. Most Our friends ask About error occurred during t
View Tutorial          By: Chala G. at 2015-01-13 16:52:10

30. Thanks Man , You are Genius i have been looking fo
View Tutorial          By: hamza Ouni at 2015-02-22 13:38:02

31. i am trying to run the above example in eclipse bu
View Tutorial          By: Prathyusha at 2015-03-10 11:01:06

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

33. Here is a good example about how to establish MySQ
View Tutorial          By: Anil Das at 2016-12-05 10:20:11

34. so....
i find this guide very very very ver
View Tutorial          By: Pierpalle Scrotini (P.S.) at 2017-02-21 07:47:48

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

36. magnificent points altogether, you simply received
View Tutorial          By: Dedra at 2017-04-29 06:58:39

37. I see your page needs some unique content. Writing
View Tutorial          By: FVREmoryxgb at 2017-06-11 10:56:11

38. I savor, lead to I discovered exactly what I was h
View Tutorial          By: www.instalatiigplprahova.ro at 2017-07-27 08:34:34

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

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

© 2019 Java-samples.com

Tutorial Archive: Data Science  Android  AJAX  ASP.net  C  C++  C#  Cocoa  Cloud Computing  EJB  Java  Certification  Interview  iPhone  Javascript  JSF  JSP  Java Beans  J2ME  JDBC  Linux  Mac OS X  MySQL  Perl  PHP  Python  Ruby  SAP  VB.net  EJB  Struts  Trends  WebServices  XML  Office 365  Hibernate

Latest Tutorials on: Data Science  Android  AJAX  ASP.net  C  Cocoa  C++  C#  EJB  Java  Certification  Interview  iPhone  Javascript  JSF  JSP  Java Beans  J2ME  JDBC  Linux  Mac OS X  MySQL  Perl  PHP  Python  Ruby  SAP  VB.net  EJB  Struts  Cloud Computing  WebServices  XML  Office 365  Hibernate