Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 03-08-2009
Member
 
Join Date: May 2008
Posts: 15
How to call SQL database with JSP?

Hi,

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

Thanks
Dumshell
Reply With Quote
  #2  
Old 03-08-2009
Member
 
Join Date: Apr 2008
Posts: 49
Re: How to call SQL database with JSP?

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.
Reply With Quote
  #3  
Old 03-08-2009
Member
 
Join Date: Oct 2008
Posts: 65
Re: How to call SQL database with JSP?

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>
Reply With Quote
  #4  
Old 03-08-2009
Member
 
Join Date: Oct 2008
Posts: 101
Re: How to call SQL database with JSP?

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>
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to call SQL database with JSP?"
Thread Thread Starter Forum Replies Last Post
Call entry stuck in Nokia C3-00 call log and stopped call logging Elijah*Blue Portable Devices 14 09-04-2012 02:49 PM
Creating Database link in database Lachlann Software Development 3 28-01-2010 01:17 PM
How JSP call JDBC database connection Swetlano Software Development 3 28-02-2009 01:46 PM
convert filemaker pro database to access database Czack MS Office Support 3 15-04-2007 01:06 AM


All times are GMT +5.5. The time now is 11:15 AM.