JDBC
· First program of Java DataBase Connectivity.
-Write a jdbc connectivity program to create table into address database.
Step 1.create Database into Ms Access.
name=address.mdb
Step 2.create DSN(Data Source Name) for Database
-start->control panel->administrative tool
-select datasource odbc
-select system tab
-click on add button
-select "Microsoft Access Driver(*.mdb)
-give name of dsn & select database
name=addressDSN
select=address.mdb
-click on ok button.
Step 3.go to classes folder.
Step 4. create one java file that is createtable.java
Step 5. write following code :
/*program of create table into address database*/
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;
public class hs1 extends HttpServlet
{
public void doGet(HttpServletRequest req,HttpServletResponse res)
{
try
{
Connection con;
Statement stmt;
String s;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:addressDSN");
stmt=con.createStatement();
s="create table info(no number,name char(30),city char(15))";
int c=stmt.executeUpdate(s);
if(c>=1)
{
System.out.println("Table is created.");
}
else
{
System.out.println("Table is not created.");
}
con.commit();
con.close();
}
catch(Exception e){}
}
}
Step 6. compile javac createtable.java
Step 7 run the servlet.
-this statement is used to execute normal sql statement as well as can execute sql statement with parameter.
-PreparedStatement create dynamic sql by receiving parameter in form of (?) sign.
-To create object of preparestatement:
ex:
PreparedStatement ps;
-To initialize object of preparestatement:
ex:
ps=con.prepareStatement(String str);
-To set value into preparestatement:
ex:
ps.setInt(1,101);
ps.setString(2,args[1]);
-To execute prepare statement :
ex:
ps.executeUpdate();
or
ps.executeQuery();
-The below display example of preparedStatement with jsp.
Std.html
<html>
<body>
<form action=std.jsp method=post>
<pre>
<center>
Enter No <input type=text name=txt1><br>
Enter Name <input type=text name=txt2><br>
Enter City <input type=text name=txt3><br>
<input type=submit value=save>
</pre>
</form>
</body>
</html>
Std.jsp
<%@ page import="java.sql.*"%>
<%
try
{
int no=Integer.parseInt(request.getParameter("txt1"));
String nm=request.getParameter("txt2");
String city=request.getParameter("txt3");
Connection con;
PreparedStatement ps;
String s;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:StudentDSN");
s="insert into info values(?,?,?)";
ps=con.prepareStatement(s);
ps.setInt(1,no);
ps.setString(2,nm);
ps.setString(3,city);
int c=ps.executeUpdate();
if(c==1)
{
out.println("Record is saved.");
}
else
{
out.println("Record is not saved.");
}
con.close();
}
catch(Exception e){}
%>
This statement is used to call stored procedure generated in PL/SQL or transact or C or any other programming language.
-Any procedure in oracle may have some parameter.
-parameter can be following mode:
1)IN
indicate input value can only read by procedure.(default)
2)OUT
This is used to indicate that you can only write data to this type of parameter,you can not read it.
3)INOUT
This is used to indicate that you can performe both read & write data to parameter.
-To create object of callable statement:
CallableStatement cs;
-To intialize the object of callable statement:
cs=con.prepareCall(String str)
-To get & set Parameter of object
cs.setInt(int pos,int value)
int cs.getInt(int pos)
-To register output parameter
cs.registerOutParameter(int pos,int type)
-The below display example of callable statement :
Step 1 start Oracle
Step 2 create procedure into oracle
SQL>ed p1.sql
create procedure cube(n in number,ans out number)as
begin
ans:=n*n*n;
end;
Step 3 compile & run procedure
SQL>@ p1.sql
/
Step 4 run-> create one jsp file.
Step 5 Write following coding to jsp file.
<%@ page import="java.sql.*"%>
<%
try
{
Connection con;
CallableStatement cs;
String s;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:oraDSN","scott","tiger");
s="{CALL cube(?,?)}";
cs=con.prepareCall(s);
cs.setInt(1,5);
cs.registerOutParameter(2,Types.INTEGER);
cs.executeUpdate();
int c=cs.getInt(2);
System.out.println("\nCube\t=\t"+c);
con.close();
}
catch(Exception e){}
%>
Step 6 run : jsp file
Note: Create oracle DSN for callable statement.
Step 1 start->control panel->admistrative tools
Step 2 select data source (ODBC)
Step 3 click on ADD button
Step 4 select oracle driver
"Microsoft ODBC for oracle"
Step 5 click on FINISH button
Step 6 Give following information:
DSN=oraDSN
User=scott
Step 7 click on ok button
· JDBC Program to Perform INSERT , UPDATE , DELETE & DISPLAY ALL RECORD using JSP.
Main.html
<center>
<form method=post action=insert.html>
<input type=submit value=”Insert” >
</form><Br><br>
<form method=post action=update.html>
<input type=submit value=”Update” >
</form><Br><br>
<form method=post action=delete.html>
<input type=submit value=”Delete” >
</form><Br><br>
<form method=post action=display.jsp>
<input type=submit value=”Update” >
</form>
</center>
insert.html
<html>
<body>
<form action=insert_code.jsp method=post>
<pre>
<center>
Enter No <input type=text name=txt1><br>
Enter Name <input type=text name=txt2><br>
Enter City <input type=text name=txt3><br>
<input type=submit value=save>
</pre>
</form>
</body>
</html>
Insert_code.jsp
<%@ page import="java.sql.*"%>
<%
try
{
int no=Integer.parseInt(request.getParameter("txt1"));
String nm=request.getParameter("txt2");
String city=request.getParameter("txt3");
Connection con;
PreparedStatement ps;
String s;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:StudentDSN");
s="insert into info values(?,?,?)";
ps=con.prepareStatement(s);
ps.setInt(1,no);
ps.setString(2,nm);
ps.setString(3,city);
int c=ps.executeUpdate();
if(c==1)
{
out.println("Record is saved.");
}
else
{
out.println("Record is not saved.");
}
con.close();
}
catch(Exception e){}
%>
Update.jsp
<html>
<body>
<form action=update_code.jsp method=post>
<pre>
<center>
Enter No <input type=text name=txt1><br>
Enter Name <input type=text name=txt2><br>
Enter City <input type=text name=txt3><br>
<input type=submit value=update>
</pre>
</form>
</body>
</html>
Update_code.jsp
<%@ page import="java.sql.*" %>
<%
try
{
int no=Integer.parseInt(request.getParameter("txt1"));
String nm=request.getParameter("txt2");
String ct=request.getParameter("txt3");
Connection con;
PreparedStatement ps;
String s;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:StudentDSN");
s="update info set info.name=?,info.city=? where info.no=?";
ps=con.prepareStatement(s);
ps.setString(1,nm);
ps.setString(2,ct);
ps.setInt(3,no);
int c=ps.executeUpdate();
if(c==1)
{
out.println("Record is updated.");
}
else
{
out.println("Record is not updated.");
}
con.close();
}
catch(Exception e){}
%>
Delete.html
<html>
<body>
<form action=delete_code.jsp method=post>
<pre>
<center>
Enter No <input type=text name=txt1><br>
<input type=submit value=delete>
</pre>
</form>
</body>
</html>
Delete_code.html
<%@ page import="java.sql.*" %>
<%
try
{
int no=Integer.parseInt(request.getParameter("txt1"));
Connection con;
PreparedStatement ps;
String s;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:StudentDSN");
s="delete from info where info.no=?";
ps=con.prepareStatement(s);
ps.setInt(1,no);
int c=ps.executeUpdate();
if(c==1)
{
out.println("Record is deleted.");
}
else
{
out.println("Record is not deleted.");
}
}
catch(Exception e){}
%>
Display.jsp
<%@ page import="java.sql.*" %>
<%
try
{
Connection con;
Statement stmt;
ResultSet rs;
String s,nm,ct;
int no;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:StudentDSN");
stmt=con.createStatement();
s="select * from info";
rs=stmt.executeQuery(s);
out.println("<center><h1>Student List<hr>");
out.println("<table border=1>");
out.println("<tr><th>No<th>Name<th>City</tr>");
while(rs.next())
{
no=rs.getInt("no");
nm=rs.getString("name");
ct=rs.getString("city");
out.println("<tr>");
out.println("<td><h1>"+no);
out.println("<td><h1>"+nm);
out.println("<td><h1>"+ct);
}
out.println("</table>");
con.close();
}
catch(Exception e){}
%>
