« J2EE (FAQs)
An Beautiful Love Story… »

Jsp – Oracle Connectivity

Introduction

This document deals with the JSP connectivity with Oracle.This also exemplifies adding,updating, deleting and retrieving records from database(Oracle) through JSP.

Prerequisites

  • Tomcat Server.
  • Oracle Client.
  • Oracle Server.

Target Readers : All

Getting Started

Copy a jar file class12.jar from Oralce_root->jdbc->lib->class12.jar
to Tomcat Server in Tomcat_root->common->lib.

Database Connectivity

<%
try{
Class.forName(”oracle.jdbc.driver.OracleDriver”).newInstance();

//Creating the connection object
Connection con = DriverManager.getConnection(”jdbc:oracle:thin:” + “@172.24.205.62:1521:infosys”,”Uname”,”Pwd”);

con.close();
}
catch (SQLException sqle){
throw new SQLException();
}
%>

Note:

172.24.205.62 -> IP Address of Oracle Server.
1521 -> Port Number.
infosys -> Host String.
Uname -> UserName.
Pwd -> Password.

Adding a Record

<%
try{
Class.forName(”oracle.jdbc.driver.OracleDriver”).newInstance();

//Creating the connection object
Connection con = DriverManager.getConnection(”jdbc:oracle:thin:” + “@172.24.205.62:1521:infosys”,”Uname”,”Pwd”);

PreparedStatement stmt = con.prepareStatement(”insert into Table_Name values(?,?,?)”);
stmt.setString(1,column1);
stmt.setInt(2,column2);
stmt.setString(3,column3);

stmt.executeUpdate();

con.close();
}
catch (SQLException sqle){
throw new SQLException();
}
%>

Where column1,column2,column3 can be static values or can be taken from the previous page by establishing session.

Deleting a Record

<%
try{
Class.forName(”oracle.jdbc.driver.OracleDriver”).newInstance();

//Creating the connection object
Connection con = DriverManager.getConnection(”jdbc:oracle:thin:” + “@172.24.205.62:1521:infosys”,”Uname”,”Pwd”);

PreparedStatement stmt = con.prepareStatement(”delete from Table_Name where EmpName=’XYZ’”);
stmt.executeUpdate();

con.close();
}
catch (SQLException sqle){
throw new SQLException();
}
%>

Updating a Record

<%
try{
Class.forName(”oracle.jdbc.driver.OracleDriver”).newInstance();

//Creating the connection object
Connection con = DriverManager.getConnection(”jdbc:oracle:thin:” + “@172.24.205.62:1521:infosys”,”Uname”,”Pwd”);

PreparedStatement stmt = con.prepareStatement(”update Table_Name set Designation=’Software Engineer’ where EmpName=’XYZ’”);
stmt.executeUpdate();

con.close();
}
catch (SQLException sqle){
throw new SQLException();
}
%>

Retrieving a Record

<%
String empName=”";
int empNumber=0;
String empDesignation=”";

try{
Class.forName(”oracle.jdbc.driver.OracleDriver”).newInstance();

//Creating the connection object
Connection con = DriverManager.getConnection(”jdbc:oracle:thin:” + “@172.24.205.62:1521:infosys”,”Uname”,”Pwd”);

PreparedStatement stmt = con.prepareStatement(”select * from Table_Name”);
ResultSet rs = stmt.executeQuery();

while(rs.next())
{
empName = rs.getString(”EMPNAME”);
empNumber = rs.getInt(”EMPNO”);
empDesignation = rs.getString(”DESIGNATION”);

out.println(empName);
out.println(empNumber);
out.println(empDesignation);
}

con.close();
}
catch (SQLException sqle){
throw new SQLException();
}
%>

Tags: , , , , ,

This entry was posted on Monday, March 23rd, 2009 at 8:29 PM and is filed under Technical. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply