Updated April 18, 2023
Introduction to JDBC PreparedStatement
JDBC PreparedStatement is used to send the SQL statement to the database which we have used or connected, it’s a statement of a special type that was derived from a number of general classes. The main use of PreparedStatement is if we want to execute the object of statement multiple times, PreparedStatement will reduce the execution time while using the object of PreparedStatement. The feature of PreparedStatement is it will give the SQL statement when the PreparedStatement is created. We can also say that PreparedStatement is a pre-compiled statement of SQL.
Syntax:
Below is the syntax which is as follows:
1) Create database connection for JDBC PreparedStatement –
Connection con_object = DriverManager.getConnection (db_path, db_username, db_user_password)2) Create JDBC PreparedStatement –
PreparedStatement object_PrepStmt
object_PrepStmt = con_object.PreparedStatement (SQL query);3) Set parameter values for PreparedStatement –
object_PrepStmt.method_PrepStmt (value, value);
object_PrepStmt.method_PrepStmt (value, value);4) Execute the SQL query using PreparedStatement –
ResultSet Object_RS = object_PrepStmt.executeQuery();Parameter description syntax of JDBC PreparedStatement.
1) Connection and connection object – This parameter is defined as we need to create a database connection using connection method also we need to create connection method object.
2) getConnection – This is a method used to create a connection with the database.
3) Database username – This is the name of the database user which was used to create the connectivity with the database using PreparedStatement.
4) Database user password – This is the password of the database user which was used to create the connectivity with the database using PreparedStatement.
5) PreparedStatement and object of PreparedStatement – We have using JDBC prepared statement to execute the SQL query by using PreparedStatement. For using PreparedStatement we also need to create the class of PreparedStatement.
6) SQL query – This is nothing but any SQL query which was we have used with PreparedStatement. We can use any statement like select, update, insert and delete with PreparedStatement.
7) ResultSet and object of resultset – This is used to extract the result from a statement by each line. After using ResultSet in PreparedStatement we need to create the object of ResultSet.
8) executeQuery – This is the method used in executing a query using PreparedStatement. We can use this method to execute any SQL PreparedStatement.
How JDBC PreparedStatement work?
- All symbols are represented by the “?” sign. This is also known as the parameter marker.
- As per comparison with the statement object PreparedStatement object have some additional features.
- Instead of writing the hard code queries, PreparedStatement is providing the feature to execute the parameterized query.
- At the time of creating PreparedStatement using SQL query, we have to pass the parameter of PreparedStatement. The same query is defined as the pre-compiled query. So at the time of executing PreparedStatement is will not compile the query again, the database will only execute the query instead of compiling it again.
- We can use the single PreparedStatement with different parameters at the time of SQL query execution.
- The main advantage and importance while using PreparedStatement is it will avoid the attack of SQL injection.
- We can also close our PreparedStatement by calling the call method. At the time of closing connection, object close method will automatically be closing the object of PreparedStatement.
- PreparedStatement is very useful using the complex object CLOB and BLOB. Using PreparedStatement we can also improve the performance of the application.
- To execute the PreparedStatement we require the following steps.
1) Create the database connection.
2) Create JDBC PreparedStatement.
3) Set the parameter values for PreparedStatement.
4) Execute the SQL query using PreparedStatement.
Below is the method of PreparedStatement which we have used at the time of creating it.
PreparedStatement method –
1) setInt(int_value, int_value)
2) setString(int_value, string_value)
3) setFloat(int_value, float_value)
4) setDouble(int_value, double_value)
5) executeUpdate()
6) executeQuery()
• The setInt method is used to set the integer type of value at the specified index parameter in PreparedStatement.
• The setInt method is used to set the string type of value at the specified index parameter.
• The setInt method is used to set the float type of value at the specified index parameter in PreparedStatement.
• The setInt method is used to set the double type of value at the specified index parameter.
• The executeUpdate method is used to execute the statement like insert, update, drop and delete. This method will return the int type value.
• The executeQuery method is nothing but the ResultSet instance when we have executed the select query with PreparedStatement.
Examples
The below example shows JDBC PreparedStatement are as follows.
1) JDBC PreparedStatement using insert query
The below example shows PreparedStatement using insert query.
Code:
public class Hello {
public static void main /*(main method)*/ (String[] args) throws Exception {
final String DB_CON = "jdbc:postgresql://localhost:5432/pre_stmt";
final String USER_NAME = "postgres";
final String PASSWORD = "postgres";
final String INSERT_QUERY = "insert into jdbc_prst values (1, 10)";
try(
Connection conn = DriverManager.getConnection(DB_CON, USER_NAME, PASSWORD);
PreparedStatement prst = conn.prepareStatement(INSERT_QUERY);
) {
prst.setInt(1, 5);
prst.setInt(2, 6);
int rows = prst.executeUpdate();
System.out.println("Rows inserted : " + rows );
ResultSet prs = prst.executeQuery(INSERT_QUERY);
prs.close(); }
catch (SQLException e) {
e.printStackTrace(); } } }
2) JDBC PreparedStatement using update query
The below example shows PreparedStatement using an update query.
Code:
public class Hello {
public static void main /*(main method)*/ (String[] args) throws Exception {
final String DB_CON = "jdbc:postgresql://localhost:5432/pre_stmt";
final String USER_NAME = "postgres";
final String PASSWORD = "postgres";
final String UPDATE_QUERY = "update jdbc_prst set age = 25 where id =1";
try(Connection conn = DriverManager.getConnection(DB_CON, USER_NAME, PASSWORD);
PreparedStatement prst = conn.prepareStatement(UPDATE_QUERY);
) {
prst.setInt(1, 5);
prst.setInt(2, 6);
int rows = prst.executeUpdate();
System.out.println("Rows updated : " + rows );
ResultSet prs = prst.executeQuery(UPDATE_QUERY);
prs.close();
} catch (SQLException e) {
e.printStackTrace();
} } }
3) JDBC PreparedStatement using delete query
The below example shows PreparedStatement using delete query.
Code:
public class Hello {
public static void main /*(main method)*/ (String[] args) throws Exception {
final String DB_CON = "jdbc:postgresql://localhost:5432/pre_stmt";
final String USER_NAME = "postgres";
final String PASSWORD = "postgres";
final String DELETE_QUERY = "delete from jdbc_prst where id =1";
try(Connection conn = DriverManager.getConnection(DB_CON, USER_NAME, PASSWORD);
PreparedStatement prst = conn.prepareStatement(DELETE _QUERY);
) {
prst.setInt(1, 5);
prst.setInt(2, 6);
int rows = prst.executeUpdate();
System.out.println("Rows deleted : " + rows );
ResultSet prs = prst.executeQuery(DELETE_QUERY);
prs.close();
} catch (SQLException e) {
e.printStackTrace();
} } }
Conclusion
JDBC PreparedStatement is called a SQL pre-compiled statement, PreparedStatement is the sub-interface of the statement. There are six types of PreparedStatement used in JDBC i.e. setInt, setString, setFloat, setDouble, executeUpdate and executeQuery. Using JDBC PreparedStatement we can prevent SQL injection attacks.
Recommended Articles
This is a guide to JDBC PreparedStatement. Here we discuss Definition, syntax, parameters, How JDBC PreparedStatement works? examples with code implementation respectively. You may also have a look at the following articles to learn more –
