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
- Data Science
- Android
- AJAX
- ASP.net
- C
- C++
- C#
- Cocoa
- Cloud Computing
- HTML5
- Java
- Javascript
- JSF
- JSP
- J2ME
- Java Beans
- EJB
- JDBC
- Linux
- Mac OS X
- iPhone
- MySQL
- Office 365
- Perl
- PHP
- Python
- Ruby
- VB.net
- Hibernate
- Struts
- SAP
- Trends
- Tech Reviews
- WebServices
- XML
- Certification
- Interview
categories
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