Adv Java - [JDBC: Prepared Statement]

♠ Posted by Unknown in Adv Java at 01:34

JDBC Prepared Statement

JDBC prepared statements address the following requirements:

1. Creating parametrerized statements such that data for parameters can be substituted dynamically.

1. Creating statements involving data values that cannot always be represented as character strings.

1. Pre-compiling SQL statements to avoid repeated compiling of the same SQL statements.

In most cases, you may not have the complete information to construct a WHERE clause in SQL. For instance, to write a SQL SELECT statement to select the data of a user, while writing the JDBC code, you need to know the primary key value to construct the SQL. In most cases, such information is available only at runtime. Prepared statements address this problem by providing for parameters in SQL.

Instead of using values, you may use “?” qualifiers in SQL. So instead of creating a statement with the SQL string:

SELECT <select_fields> FROM <table_name> WHERE USER_ID = <value>

You can use a prepared statement with the SQL string:

SELECT <select_fields> FROM <table_name> WHERE USER_ID = ?

You can substitute values using methods on the java.sql.PreparedStatement interface.

The same applies to SQL involving complex data types such as long text data, binary data, or even timestamp data. such data types cannot be expressed as such plain string. For instance, how would we create a SQL statement to update image data? since prepared statements are parameterized, instead of expressing such data type sin the SQL statement directly, you can set the data using various methods on the java.sql.PreparedStatement interface.

In addition, the same SQL statement can be executed many times with different parameters and the database can compile such statements just once, this improving performance. A PreparedStatement object can hold precompiled SQL statements. The following methods on the java.sql.Connection interface let us create PreparedStatement objects:

PreparedStatement prepareStatement(String sql) throws SQLExceptionPreparedStatement prepareStatement(String sql, int resultSetType,int resultSetConcurrency) throws SQLException

Example:

Write an application program to perform all the database driven operations such as insertion, Deletion, Updation and Selection using the concept of Prepared Statement.

import java.sql.*;

import java.io.*;

class EmpExample

{

          String dbUrl = "jdbc:odbc:Employee";

          String user = "";

          String password = "";

          Connection c;

          PreparedStatement ps;

          Statement s;

          ResultSet r;

          EmpExample() throws SQLException, ClassNotFoundException

          {

                   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

                   c = DriverManager.getConnection(dbUrl, user,password);

                   s = c.createStatement();

          }

          public void InsertEmployee() throws SQLException, IOException

          {

                   ps = c.prepareStatement("Insert Into Emp_Mst Values(?, ?, ?, ?, ?)");

                   BufferedReader br = new BufferedReader(new InputStreamReader(System.in));

                   System.out.print("Enter Employee No. : ");

                   int eno = Integer.parseInt(br.readLine());

                   System.out.print("Enter Employee Name : ");

                   String ename = br.readLine();

                   System.out.print("Enter Employee Designation : ");

                   String edesig = br.readLine();

                   System.out.print("Enter Employee Salary : ");

                   int esal = Integer.parseInt(br.readLine());

                   System.out.print("Enter Employee Join Date : ");;

                   String edate = br.readLine();

                  

                   ps.clearParameters();

                   ps.setInt(1,eno);

                   ps.setString(2, ename);

                   ps.setString(3, edesig);

                   ps.setInt(4, esal);

                   ps.setString(5, edate);

                   ps.executeUpdate();

          }

          public void DeleteEmployee() throws SQLException, IOException

          {

                   BufferedReader br = new BufferedReader(new InputStreamReader(System.in));

                   System.out.print("Enter Employee No. TO DELETE Record : ");

                   int eno = Integer.parseInt(br.readLine());

                   String DelQuery = "Delete From Emp_Mst Where EmpId = " + eno;

                   ps.executeUpdate(DelQuery);

          }

          public void DisplayEmployee() throws SQLException, ClassNotFoundException

          {

                   r = s.executeQuery("Select * From Emp_Mst");

                   while(r.next())

                   {

                             System.out.println(r.getString("EmpId") + " " + r.getString("EmpName"));

                   }

          }

}

public class PSExample

{

          public static void main(String [] args) throws SQLException, ClassNotFoundException, IOException

          {

                   EmpExample EObj = new EmpExample();

                   EObj.DisplayEmployee();

                   EObj.InsertEmployee();

                   EObj.DisplayEmployee();

          }

}