To overcome the above limitation, use precompiled SQL query with the help of PreparedStatement object. The JDBC PreparedStatement object deals with precompiled SQL query. The query that comes to database software from Java application without values only once becomes parsed query in database software only once and allows client application(Java application) to set the values for query for the multiple times, with same or different values to gather the results for multiple times. This is called precompiled SQL query.
To execute SQL query once in database software, use simple statement object. Similarly, to execute same SQL query multiple times in database software with different or same values, use JDBC PreparedStatement object.
Procedure to work with JDBC PreparedStatement object
1. Prepare SQL query with parameters(place holder())
String query="insert into student values(,,)";
2. Send SQL query to database software as pre-compiled query and represent that query with PreparedStatement object.
PreparedStatement ps=cn.PreparedStatement ( query);
ps = this object represent precompiled query of database software being from java application
3. preparedStatement(query) = this method send to database software and makes that queries in database software.
Set values to parameters of precompiled query by using setxxx() methods.
For int value:ps.setInt(); For String value:ps.setString(); For double value:ps.setDouble(); ps.setInt(1,2); ps.setString(2," sql"); ps.set Double(3 "bbsr");
Note: The parameter of SQL query will have one base index here BBSR is parameter value.
4. Execute the query in database software
int result = ps.executeUpadate();
5. Process the results.
if(result==0) {
System.out.println("record insertion is failed")
}
else {
System.out.printIn("record insertion is successful")
}
6. To execute same SQL query multiple times with same or different values repeat step-3 to step-5 multiple times.
7. Close the preparedStatement object.
ps.close();
• While creating PreparedStatement mark symbol only to allow replace the values to command, but not for table name, but not for column name.
1. Create table(Sno.number(), sname varchar2(10)); //Invalid because create is a DDL command. 2. Select from emp; //Invalid because cannot be used for column name. 3. Select * from emp where empno.= / / Valid 4. Update setSql = Where empno=; / /Invalid cannot be used for table name.
I. The executeQuery() Method
This method retrieves data from a table using a SELECT statement with parameter markers. This method returns a result table in a ResultSet object. After you obtain the result table, you need to use ResultSet methods to move through the result table and obtain the individual column values from each row.
To retrieve rows from a table using a SELECT statement with parameter markers, you need to perform these steps:
1. Invoke the Connection.prepareStatement method to create a PreparedStatement object.
2. Invoke PreparedStatement.setXXX methods to pass values to the input parameters.
3. Invoke the PreparedStatement.executeQuery method to obtain the result table
from the SELECT statement in a ResultSet object.
4. In a loop, position the cursor using the ResultSet.next method, and retrieve data from each column of the current row of the ResultSet object using getXXX methods.
5. Invoke the ResultSet. Close method to close the ResultSet object.
6. Invoke the PreparedStatement. Close method to close the PreparedStatement object when you have finished using that object.
II. The executeUpdate()Method
The Statement.executeUpdate method works if you update tables with constant values. However, updates often need to involve passing values in variables to DB2 tables. To do that, you use the PreparedStatement. ExecuteUpdate method.
e.g.,
Update empployee set phoneno = where empno
Variables of this form are called parameter markers. To execute an SQL statement with parameter markers, you need to perform these steps:
1. Invoke theConnection.prepareStatement method to create aPreparedStatement object.
2. Invoke the PreparedStatement. setXXXmethod to pass values to the variables.
3. Invoke the PreparedStatement. executeUpdate method to update the table with the variable values.
4. Invoke the PreparedStatement. close method to close the PreparedStatement object when you have finished using that object.
III. The execute() Method
(i) to update data
• First you need to create a database connection.
• With a database connection, you must write the SQL update statement and let this statement be the parameter to the below specified prepareStatement(String SQL) method.
• If you have any parameter in your SQL statement, then it is time to set these using the preparedStatement setxxx() methods.
• Now you can create a preparedStatement object with the method
• With the returned preparedStatement object, you can execute the following method to perform the SQL update statement.
(ii) to delete or insert
• First you need to create a database connection.
• With a database connection, you must write the SQL DELETE or the SQL INSERT statement and let this statement be the parameter to the below specified prepareStatement(String sql) method.
• If you have any parameter in your SQL statement, then it is time to set these using the preparedStatement setxxx() methods.
• Now you can create a preparedStatement object with the method.
• With the returned PreparedStatement object, you can execute the following method to perform the SQL Update statement
(iii) to execute DDL statements
• First you need to create a Database Connection.
• With a DataBase connection, you must write the SQL DDL statement and let this statement be the parameter to the below specified prepareStatement() method.
• If you have any parameter in your SQL statement, then it is time to set these using the PreparedStatement setXXX()methods.
• Now you can create a PreparedStatement object with the method:
Example:
Using PreparedStatement Insert value into database table
Step 1: Create table first
Syntax: Create table student(sid number (7), snamevarchar2(20),smark number(5));
Step 2: Write Java code
Example:
import java.io.*;
import java.sql.*;
import java.util.* ;
public class PreparedInsert {
public static void main(String args[ ]) throws Exception {
//load jdbc driver into jvm
Class.forName("oracle.jdbc.driver.oracleDriver");
//get Connection object
Connection con=DriverManager.getConnection("jdbc:oracle:thin@localhost:1521:XE","System","manager");
//create PrepatedStatement
PreparedStatement ps=con.prepareStatement ("insert into Student values (,,}") ;
try{
InputStreamReader rs=new InputStreamReader (System.in) ;
BufferedReader br=new BufferedReader(rs);
System.out.println("no of record is inserted") ;
int n=Integer.parseInt (br.readLine()) ;
for(int i=0;i<n;i++) {
System.out.println("Enter student name");
String name=br.readLine();
System.out.println("Enter student Id");
int sid=Integer.parseInt(br.readLine());
System.out.println("Enter marks");
int marks=Integer.parseInt(br.readLine());
// set the value
ps.setInt (1,sid);
ps.setString(2,name);
ps.setInt(3,marks);
int k=ps.executeUpdate () ;
System.out.println(k+"row is inserted");
}
}
catch(Exception ee){}
//step-6 close connection
ps.close() ;
con.close();
System.out.println("connection is closed");
}//End of main method
}//End of class
Using PreparedStatement Insert Picture into Database
It is easier to insert or update SQL data type such as Blob, Clob or object with the help of setxxx() method. Insert a picture is nothing but storing binary data (bytes) of picture into a database table.
In JDBC, we need PreparedStatement interface to transfer binary data from Java program to database. If we want to insert a picture into a table, the table column should be blob (binary logical object).
The following are the steps required for inserting a picture:
1. Create java.io.File object for the picture.
2. Create java.io.FileInputStream object for reading binary data from the file.
3. Find the size of picture.
4. Call the setBinaryStream()method for inserting binary data of the picture to an sql statement.
Picturelnsert.java
import java.sql.* ;
import java.io.* ;
import java.util.*;
public class PictureInsert {
public static void main(String args []) throws Exception {
//load the type-4 driver
Class.forName("oracle.jdbc.driver.OracleDriver");
//get connection
Connection cn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","system" , "manager") ;
//create preaparedStatement object
PreparedStatement ps=cn.PreparedStatement("insert into employee_info values(,,)");
Scanner sc=new Scanner(System.in) ;
System.out.println("Enter the employee id");
int eid=sc.nextInt();
System.out.println("enter the employee name");
String ename=sc.next();
//set id and name
ps.setInt (1,eid);
ps.setString(2 , ename);
//step-1(for insertion an image;
File file=new File(path) ;
//step-2
FileInputStream fis=new FilelnputStream (file);
//step-3
int size=(int)file.length();
//step-4
ps.setBinaryStream(3, fis, size);
//step-5
int k=ps.executeUpdate();
System.out.println(k+"row is inserted");
ps.close();
cn.close();
}
}
• Before we run the application we need to create table called employee_info in
database.
• Syntax
Create table employee_info(emp_id number(5),emp_name varchar2(20),emp_photo blob);
• Compile the application.
javac Pictureinsert.java
• Run the application
java Pictureinsert
Enter the employee id 7788
Enter employee name Dinesh
Enter photo path C://photos/ duke.gif
1 row inserted
Using Prepared Statement Select from Database
In JDBC we need for selecting a picture from a database. While selecting a picture we will get bytes (binary data). Bytes from picture are stored in database. The binary data is read and then converted to picture .
• Following are the steps required for reading a picture from database:
Step-1: Execute select operation obtain the binary data into ResultSet object.
Step-2: Read binary data from ResultSet by calling getBinaryStream() and store the byte into InputStream object.
Step-3: Create FileOutputStream to create a new file.
Step-4: Read the byte from InputStream and write that into file.
PictureInserl.java
import java.sql.*;
import java.io.*;
import java.util.*;
public class PictureSelect {
public static void main(String args[ ])throws Exception {
//load the type-4 driver
Class.forName("oracle.jdbc.driver.OracleDriver");
//get connection
Connection cn=DriverManager.getConnection ("jdbc:oracle: thin:@localhost :1521:XE", "system" , "manager");
//create PreparedStatement object
PreparedStatement ps =cn.PreparedStatement( "select emp_photo from employee_info where empid=");
Scanner sc=new Scanner (System.in);
System.out.println("Enter the employee id");
int eid=sc.nextInt( ) ;
//set id
ps.setInt (1, eid) ;
//step-l execute the select operation, obtain binary data using ResultSet
ResultSet rs=ps.executeQuery();
rs.next() ;
//step-2 Read binary data
InputStream is=rs.getBinaryStream (1) ;
//step-3 CreateFileoutputStream object
FileOutputStream fos=new FileOutputStream( "d:/photos/duke.gif");
//step-4 read the data
int k=0;
while((k=is.read())!=-1) {
fos.write(b);
}
System.out.println("image recieved open it from d;/photo");
rs.close();
ps.close();
cn.close();
}
}
Output:
Image received open it from d:/photo
Dinesh Thakur holds an B.C.A, MCDBA, MCSD certifications. Dinesh authors the hugely popular