Recent Pos

The JDBC PreparedStatement Object

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmail

⇒ The PreparedStatement object represents the pre-compiled SQL statement.

⇒ This allows us to execute the pre-compiled SQL statements into the database server.

⇒ The java.sql.PreparedStatement interface is a subtype of java.sql.Statement.

⇒ When working with Statement object we find it will request the DB server for executing the given SQL statement.

⇒ When an SQL Statement is submitted to the DB server for execution, the following main operations are performed:

1. Compile the given SQL statement.

2. Prepare the execution plan for the statement.

3. Execute the plan and return the result.

4. The following diagram shows this process:
 
JDBC Prepared Statement object
 
⇒ The compiling and preparing the execution plan is time taking and costlier job.

⇒ It is found that in many cases the applications wants to execute the similar SQL statement for multiple times. In such cases compiling and preparing the execution plan for the same SQL for multiple times is found unnecessary.

⇒ To avoid this and try to improve the performance we have a concept of pre-compiled SQL statement.

⇒ In this case the database server can be requested to compile the given SQL and store the execution plan with a unique id so that the client application can request the database server for executing the plan, possibly for multiple times. This can save the unnecessary repeated compilation and execution plan creation for the same SQL.

⇒ In JDBC the PreparedStatement object supports the Java application to execute pre-compiled statement.
 

Working with PreparedStatement:

The following three steps are involved in working with PreparedStatement:

Step 1: Prepare the PreparedStatement object

Step 2: Set the values to the parameters

Step 3: Execute the Statement
 
⇒ The following method of Connection object is used to create the PreparedStatement object:

 
⇒ This method will take the given SQL to the database server for compilation and store the execution plan, if all successful, it returns the PreparedStatement object representing the compiled Statement of the given SQL.
 
Example:

 
⇒ In the above sample the SQL query is a simple and complete SQL statement. There is no any parameter to set.

⇒ It is allowed to have a SQL statement with some placeholder to set values later after the compilation. These placeholders are referred as parameters.

⇒ Parameters are the blanks or variables in the SQL statement to compile and represent by the PreparedStatement.

⇒ The parameter can be positional or named(Note: JDBC doesn’t support named parameter).

⇒ The positional parameter implemented by ‘?’ (question mark) character to describe the parameter in the SQL statement.
 

Rules for describing the parameters:

The following are the rules for describing the parameters in the SQL:

1. The SQL keywords cannot be a parameters.

2. Table or column names cannot be a parameter.

3. The SQL operators and functions cannot be a parameter.

4. We can have zero to more number of parameters.
 
⇒ Parameters are recognized by the index which are automatically assigned as per the position of the parameter in the SQL. From left to right the first parameter is given with an index 1 and following with 2,3.. so on.
 
Example:

 
⇒ After creating the PreparedStatement object we need to set the values to the parameters.

To do this we need to use the following method of PreparedStatement object:

 

Executing the statement:

⇒ We use any of the following methods of PreparedStatement for executing the statement:

int executeUpdate(): This is to execute the non-select statement.
 
ResultSet executeQuery(): This will execute the select statement.
 
Executing Prepared Statement
 
Example:
 
Inserting record into database..

 
 
Fetching the records from database and printing..

 

Advantages/Benefits of PreparedStatement:

⇒ This can improve the performance when we have a same SQL statement to execute for multiple times compared to Statement. As the compilation is done only for one time.

⇒ Easy to write the queries with multiple dynamic values makes the query more descriptive avoiding concatenation.

⇒ It is convenient to use PreparedStatement working with complex datatypes.

⇒ This can solve some of the SQL Injection problems.
 

Limitation and drawbacks of PreparedStatement:

⇒ PreparedStatement object can represent only 1 SQL statement to execute for any number of times.

⇒ PreparedStatement object can have only one SQL statement with different set of values.
 

Compare Statement and PreparedStatement:

StatementPreparedStatement
1. Single object can execute multiple SQL1. Only one SQL
2. Every execution of SQL needs compilation2. Only one compilation for multiple execution of SQL
3. Difficult to include multiple concatanation3. Easy writing SQL because of positional parameters
4. Needs DB specific format4. Easy working with compiler types
5. SQL injection attacks possibility is more5. Limited SQL injection attacks

 
 
<-Previous: The JDBC Statement execute() method | | Next->Working with java.sql.Date type using JDBC

No Comments

Post a Comment