Querying Data using the PreparedStatement Object

Consider a scenario where a New Publishers, a pubhlishing company maintains information about the books and the authors in a database. The company wants an application using which they cab access the information about authors based on different criteria. For Example, it should retain all authors living in a city specified at runtime. In this scenario, we cannot use Statement object to retrieve information because the value needs to be specified at the runtime. We need to use the PreparedStatement object as it can accept runtime parameters.

The PreparedStatement interface is derived from Statement interface and  is available in the java.sql package. 

The PreparedStatement objects are compiled and prepared only once by JDBC.

Methods of the PreparedStatement Interface

The PreparedStatement interface inherits the following methods from the Statement interface :

* ResultSet executeQuery() : Executes a SELECT statement and returns the result in a ResultSet object.

* int executeUpdate() : Executes an SQL Statement, INSERT, UPDATE or DELETE and returns the count of the rows affected.

* boolean execute() : Executes an SQL statment and returns a boolean value.

Now Lets Understand with an Example

Consider we have to retrieve author details by passing the author id at runtime. The SQL statement to write a parameterized query is :

SELECT * FROM authors WHERE author_id = ?

To submit such statement to the database we need to create PreparedStatement object and not Statement object. The PreparedStatement object can be created by using the preparedStatement() method of the Connection object. We need to use the following method to call to prepare an SQL statement :

stat = con.preparedStatement("SELECT * FROM authors WHERE author_id = ?");

The ? is called the PLACEHOLDER, which can be replaced by input parameters at runtime. It has an important part to play in PreparedStatement.
We can even use multiple placeholders.

Ex. 

Before executing the SQL statement, we must set the value of each '?' parameter. This is done by calling an appropriate setXYZ() method, where XYZ is the data type of the parameter. For Example :

state.setString(1,"101"); //This is how we need to put values for placeholders
ResultSet result = stat.executeQuery();

Methods	Description
void setInt(int index, int val)	Sets the Java int type value for the parameter corresponding to the index passed as parameter
void setString(int index, String val)	Sets the Java String type value for the parameter corresponding to the index passed as parameter
void setDouble(int index, Double val)	Sets the Java Double type value for the parameter corresponding to the index passed as parameter
void setLong(int index, long val)	Sets the Java long type value for the parameter corresponding to the index passed as parameter
void setFloat(int index,  float  val)	Sets the Java Float type value for the parameter corresponding to the index passed as parameter
void setBoolean(int index,  Boolean  val)	Sets the Java Boolean type value for the parameter corresponding to the index passed as parameter
void setShort(int index,  Short  val)	Sets the Java  Short  type value for the parameter corresponding to the index passed as parameter

Retrieving Rows

You can use the following code snippet to retrieve the books written by an author from the titles table using PreparedStatement object :

String str = "SELECT * FROM titles WHERE author_id = ?";

PreparedStatement pStat = con.preparedStatement(str);

ps.setString(1,"101");

ResultSet result = pStat.executeQuery();