JSP Example to connect to MS SQL database and retrieve records

By: Abinaya in JSP Tutorials on 2007-10-12

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>Connect 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(); 
        } %>
    </table>
</body>

</html>

Previous
<< The TryCatchFinally Interface in JSP

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