JDBC statements
There are 3 types of jdbc statement object
- Simple Statement Object
o Limitation of simple statement
§ framing sql query for simple statement object based on variable as input values is quite complex
§ when simple statement object is used to send and execute same sql query for multiple number of times, all the times
§ some query goes to database software from java application, multiple times same query will be parsed
§ multiple time same query will be executed with same or different values and multiple times same query output comes to java application
§ in these operation sending same query for multiple times to database software and parsing same query for multiple times are unnecessary operation
§ but we can’t avoid them while working with simple statement object
- Prepared Statement Object
o Precompiled query:
§ To overcome above statement i.e., simple statement object use precompiled queries represented by prepared statement object.
§ Simple statement object deals with static sql query where as prepared statement object deals with precompiled queries
§ The sql query that comes to database software from client application without values, becomes parsed query, resides in database software and allows client application to set values. To execute query fro multiple number of times is called precompiled query
§ Even though precompiled query is executed for multiple number of times with same different values the query comes to db s/w only once and query will be parsed only for one time
§ The prepared statement object java jdbc application represents precompiled query
§ In db s/w and allows the programmer to set values to query and to execute the query for multiple number of times
o Procedure to work with prepared statement object
§ In order to execute given sql query in db s/w only for one time use simple statement object.
§ In order to execute same sql query for multiple number of times with same or different values use precompiled query represented by prepared statement
§ Write a jdbc application to insert multiple records in the db table
//PstTest.java
import java.sql.*;
import java.io.*;
public class PstTest {
public static void main(String[] args) {
BufferedReader br=null;
Connection con=null;
Statement st=null;
PreparedStatement ps=null;
try
{
br=new BufferedReader(new InputStreamReader(System.in));
int n=0;
if(br!=null)
{
System.out.println("enter number of dept values to enter : ");
String deptno=br.readLine().trim();
n=Integer.parseInt(deptno);
}
Class.forName("oracle.jdbc.driver.OracleDriver");
con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:dee","scott","tiger");
ps=con.prepareStatement("insert into dept values(?,?,?)");
//read mulitple department details from keyboard
for(int i=1;i<=n;++i)
{
System.out.println("enter"+i+"department details");
System.out.println("enter department number");
int no=Integer.parseInt(br.readLine().trim());
System.out.println("enter department name");
String name=br.readLine().trim();
System.out.println("enter location");
String add=br.readLine().trim();
//set values to place holder of the query
ps.setInt(1,no);
ps.setString(2, name);
ps.setString(3, add);
//execute the query
int res=ps.executeUpdatea();
if(res==0)
System.out.println(i+"record insertion failed");
else
System.out.println(i+"record insertion successful");
}
}
catch(ClassNotFoundException cnf)
{
cnf.printStackTrace();
}
catch(SQLException se)
{
se.printStackTrace();
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
try
{
if(br!=null)
br.close();
}
catch(IOException ioe)
{
ioe.printStackTrace();
}
try
{
if(ps!=null)
ps.close();
}
catch(SQLException ioe)
{
ioe.printStackTrace();
}
try
{
if(con!=null)
con.close();
}
catch(SQLException ioe)
{
ioe.printStackTrace();
}
}
}
}
Required to check before execution
· Class path should be check once whether classes12.jar is exist
· Check Sql plus
Output
enter number of dept values to enter : 1
enter1department details
enter department number
50
enter department name
SOFTWARE
enter location
1record insertion successful
Database verify
Precompiled query Used in Program
· Prepare query with place holders (?)
Note: a place holder in the query represents values in that position will be set afterwards
Ex: String qry=”insert into student values (?,?,?)”;
Note: the ’?’ in the above query will represents that values to query will be set afterwards
· Make query as precompiled query and represent that query with jdbc prepared statement object
PreparedStatement ps=con.PreparedStatement (qry);
The method con.PreparedStatement() takes given query and sends that query to db s/w then makes that query as precompiled query in db s/w
· The ‘ps’ object is PreparedStatement object and this object represents precompiled query available in the db s/w
· Set values for place holders by using setxxx() symbols
Ps.setint(1,675) where 675 is place holder values
Ps.setString(2,’raja’);
Ps.setString(3,’hyd’);
· Execute the query
Int res= ps.executeupdate();
Summary
· When the above application is executed to insert three records into the table, the insert query goes to db s/w only once and that query will be parsed by db engine only once, but query extraction takes place for multiple number of times and the output will be gathered multiple number of times
· We can avoid unnecessary operations while executing same query for multiple number of times
Place holder Problems:
· we cannot work with placeholder parameter representing table name, column name and sql keyword
· these placeholder parameter must always represent the query condition values and input values
Eg:
i. Select * from student where sno >=? (Valid)
ii. Select * from? Student where sno>=? (Invalid)
iii. Select * from? Where?>=? (Invalid)
· Always count positional parameter in the order
· they are available in the query (left to right ) not in the order the columns represented by place holder parameter
Eg:
Update student st sname=?, sadd=? Where sno=?
Select * from student where sno>=? And sno<=?
· All DML,DRL,DDL queries can be executed as precompiled queries by using prepared statement object
JDBC Application:
· Jdbc application are two tier application because they contain two layers
· In each application layer 1 / tier 1 is java application and layer 2 /tier 2 is db s/w
· In java jdbc application are called client application and Db s/w is called server s/w
· The logic that is given to develop user interface like reading input values of the application and writing result back to monitor is called presentation logic
· The main logic of the application that generate result based on input values is called business logic
· The data of the application that is stored in the files or db table is called application data
· The application that we have developed so far by using simple statement object and prepared statement object comes under fat client –thin server application
· The logic writing in jdbc application to send and execute sql query in db s/w is called business logic
- Callable Statements Object
o In order to develop thin client-fat server mode based jdbc application we need to keep business logic(sql quires of the appl ) in db s/w
o For this we can take the support of pl/sql procedures and function because they will be developed executed and will be residing in db s/w
o PL/SQL procedures , functions can have prams having data types and mode
o They are in mode(default, outmode in out mode
Ex: y=x+x; // where y is out and x+x is in
X=x+x; //it is inout
o PL / SQL programming is db dependent programming so the sent of the programming will based on db s/w we use
o In PL / SQL procedure and function any number of prams can be there having any data types/move
o Write PL/SQL procedure in oracle db s/w
Open sql plus and
To open editor type:
In that editor, type the following code
Go to file and save
Go to file and exit
After that type ‘/’ to create procedure
To see the error
o Procedure to call PL/SQL procedure from java app using callable statement object
§ Create query in java app calling pl/sql procedure
§ String qry=”{call my_procedure(?,?,?)}”;
§ Create callable statement object representing pl/sql procedure of db s/w
§ CallableStatement cs=con.prepareCall(qry);
§ Here cs is object represents a pl/sql procedure available in db s/w
§ Register out parameters of pl/sql procedure with sql types/jdbc types
