Turn Desktop View Off
by Dinesh Thakur

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 EMPLOYEE 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 l: 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

javaPictureinsert

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