The Prepared Statement is a slightly more powerful version of a Statement, and should always be at least as quick and easy to handle as a Statement.
The Prepared Statement may be parametrized.
Most relational databases handles a JDBC / SQL query in four steps:
- Parse the incoming SQL query
- Compile the SQL query
- Plan/optimize the data acquisition path
- Execute the optimized query / acquire and return data
A Statement will always proceed through the four steps above for each SQL query sent to the database. Learn more skills from Java Online Course
Thus, when creating a Prepared Statement some pre-optimization is performed immediately. The effect is to lessen the load on the database engine at execution time
Sometimes it is more convenient to use a PreparedStatement object for sending SQL statements to the database. This special type of statement is derived from the more general class, Statement that you already know.
If you want to execute a Statement object many times, it usually reduces execution time to use a PreparedStatement object instead.
The main feature of a PreparedStatement object is that, unlike a Statement object, it is given a SQL statement when it is created. The advantage to this is that in most cases, this SQL statement is sent to the DBMS right away, where it is compiled.
As a result, the PreparedStatement object contains not just a SQL statement, but a SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement SQL statement without having to compile it first. For more additional skills Java Online Training
Although PreparedStatement objects can be used for SQL statements with no parameters, you probably use them most often for SQL statements that take parameters.
The advantage of using SQL statements that take parameters is that you can use the same statement and supply it with different values each time you execute it. Examples of this are in the following sections.
Creating a PreparedStatement
Before you can use a PreparedStatement you must first create it. You do so using the Connection.prepareStatement(), like this:
String sql = "select * from people where id=?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
The PreparedStatement is now ready to have parameters inserted.
Inserting Parameters into a PreparedStatement
Everywhere you need to insert a parameter into your SQL, you write a question mark (?). For instance:
String sql = "select * from people where id=?";
Once a PreparedStatement is created (prepared) for the above SQL statement, you can insert parameters at the location of the question mark. This is done using the many setXXX() methods. Here is an example:
preparedStatement.setLong(1, 123);
The first number (1) is the index of the parameter to insert the value for. The second number (123) is the value to insert into the SQL statement.
Here is the same example with a bit more details:
String sql = "select * from people where id=?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setLong(123);
You can have more than one parameter in an SQL statement. Just insert more than one question mark. Here is a simple example:
To get in-depth knowledge, enroll for live free demo on Java Certification Course
String sql = "select * from people where firstname=? and lastname=?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "John");
preparedStatement.setString(2, "Smith");
Executing the PreparedStatement
Executing the PreparedStatement looks like executing a regular Statement. To execute a query, call the executeQuery() or executeUpdate method. Here is an executeQuery() example:
String sql = "select * from people where firstname=? and lastname=?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "John");
preparedStatement.setString(2, "Smith");
ResultSet result = preparedStatement.executeQuery();
As you can see, the executeQuery() method returns a ResultSet. Iterating the ResultSet is described in the Query the Database text.
Here is an executeUpdate() example:
String sql = "update people set firstname=? , lastname=? where id=?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "Gary");
preparedStatement.setString(2, "Larson");
preparedStatement.setLong (3, 123);
int rowsAffected = preparedStatement.executeUpdate();
The executeUpdate() method is used when updating the database. It returns an int which tells how many records in the database were affected by the update.
Mulesoft Online Learning 