How to call SQL database with JSP?

**Dumshell** · 03-08-2009

Hi,

How to call SQL database with JSP?
How is it possible to call SQL stored procedure with JSP?

Thanks
Dumshell

**gvBlake** · 03-08-2009

Database access in JSPs

Database access is very common in JSPs. Most database access these days is done using SQL. Therefore, if you do not know SQL, the first step is to learn SQL. In Java, SQL access is provided via JDBC (the java.sql.* package.) One approach to database access in JSP is simply to use JDBC, by putting JDBC calls in Java scriptlets.

Once you have your ODBC connection configured, add the following lines to your web configuration (web.ini) file:

dataSource.name: myDataSource
dataSource.myDataSource.odbc: jspsql

This tells the server to use the ODBC connection named "jspsql".

I hope this helps you. Start going through the tutorial. All the best.

**Rock Villa** · 03-08-2009

JSP Example to connect to MS SQL database and retrieve records

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.

Code:

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

**ComPaCt** · 03-08-2009

Updating a database using the sql:update tag

Code:

//web.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
  "http://java.sun.com/dtd/web-app_2_3.dtd">
<web-app>
  <resource-ref>
    <res-ref-name>jdbc/address</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
  </resource-ref>
</web-app>


<%@ taglib prefix="sql" uri="http://java.sun.com/jstl/sql" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core" %>
<html>
<head>
<title>Updating a database using the sql:update tag</title>
<sql:setDataSource
  var="conn"
  dataSource="jdbc/address"
/>
</head>
<body>
<h1>Modify Address List</h1>
<sql:update dataSource="${conn}" var="addresses">
    INSERT INTO AddressList (name, street, city, country, telephone) VALUES (?, ?, ?, ?, ?)
    <sql:param value='${param["name"]}'/>
    <sql:param value='${param["street"]}'/>
    <sql:param value='${param["city"]}'/>
    <sql:param value='${param["country"]}'/>
    <sql:param value='${param["tel"]}'/>
</sql:update>
</body>
</html>