Java JDBC PreparedStatement Example

In database management systems, a prepared statement or parameterized statement is a feature used to execute the same or similar database statements repeatedly with high efficiency. Typically used with SQL statements such as queries or updates, the prepared statement takes the form of a template into which certain constant values are substituted during each execution.

How prepared statement works?

Most relational databases handles a JDBC / SQL query in four steps:

1. Parse the incoming SQL query
2. Compile the SQL query
3. Plan/optimize the data acquisition path
4. 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. A Prepared Statement pre-executes steps (1) – (3) in the execution process above. 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.

Advantages of using prepared statement over simple JDBC statement

• Pre-compilation and DB-side caching of the SQL statement leads to overall faster execution and the ability to reuse the same SQL statement in batches.
• Automatic prevention of SQL injection attacks by builtin escaping of quotes and other special characters. Note that this requires that you use any of the PreparedStatement setXxx() methods to set the values and not use inline the values in the SQL string by string-concatenating.
• Apart from above two main usage, prepared statements makes it easy to work with complex objects like BLOBs and CLOBs.

If you have missed, in previous posts, we have learned about types of JDBC drivers and some basic operations like making database connection using JDBC and then how to execute SELECT Query, and then INSET Query example.

Execution of prepared statements requires following steps:

1) Make a database connection
2) Set values and execute prepared statement

Pre-requisites include setting up a database schema and creating a table at least.

CREATE SCHEMA 'JDBCDemo' ;

CREATE TABLE 'JDBCDemo'.'EMPLOYEE'
(
	'ID' INT NOT NULL DEFAULT 0 ,
	'FIRST_NAME' VARCHAR(100) NOT NULL ,
	'LAST_NAME' VARCHAR(100) NULL ,
	'STAT_CD' TINYINT NOT NULL DEFAULT 0
);

Let’s write above steps in code:

1) Make JDBC database connection

Though we have already learned about it in making JDBC connection, lets recap with this simple code snippet.

Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager
	.getConnection("jdbc:mysql://localhost:3306/JDBCDemo", "root", "password");

2) Set values and execute PreparedStatement

This is the main step and core part in the post. It requires creating a Statement object and then using it’s executeQuery() method.

PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, 87);
pstmt.setString(2, "Lokesh");
pstmt.setString(3, "Gupta");
pstmt.setInt(4, 5);

int affectedRows = pstmt.executeUpdate();

Let’s see the whole code in working.

Complete JDBC PreparedStatement Example

package com.howtodoinjava.jdbc.demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class PreparedStatementDemo 
{
	public static void main(String[] args) 
	{
		Connection connection = null;
		PreparedStatement pstmt = null;
		String sql = "INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME,STAT_CD) VALUES (?,?,?,?)";
		try 
		{
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/JDBCDemo", "root", "password");
			
			pstmt = connection.prepareStatement(sql);
			pstmt.setInt(1, 87);
			pstmt.setString(2, "Lokesh");
			pstmt.setString(3, "Gupta");
			pstmt.setInt(4, 5);
			int affectedRows = pstmt.executeUpdate();
			System.out.println(affectedRows + " row(s) affected !!");
		} 
		catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				pstmt.close();
				connection.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
}

Output:

1 row(s) affected !!

That’s all in this post. Drop me a comment if something needs explanation.

Happy Leaning !!