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
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.
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();
|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|
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);
ResultSet result = pStat.executeQuery();