Java Database Connectivity (JDBC) remains the standard foundation for interacting with relational databases in Java applications. One of its key components is the PreparedStatement interface, which simplifies the execution of parameterized SQL queries while enhancing security and performance. Prepared statements help prevent SQL injection and reduce query parsing overhead by allowing reuse with different parameter values.
This article explores the correct way to reuse a PreparedStatement and shows how minor adjustments can greatly enhance performance.
1. Why Should You Reuse a PreparedStatement?
Each time an SQL statement is prepared, the database must parse the query, compile it, and generate an execution plan. However, when you reuse a PreparedStatement without closing it between executions, the database can bypass these steps by reusing the previously compiled plan, significantly reducing computational overhead and improving performance. The benefits of reusing a PreparedStatement include:
- Improved performance: Avoids the cost of SQL compilation for every execution.
- Better security: Shields against SQL injection via parameterization.
- Reduced database load: Fewer round-trips and lighter parsing tasks.
- Cleaner code: Reduces duplication of boilerplate code.
2. Inefficient Usage: Preparing Inside a Loop
Let’s begin by examining a poor practice: creating a new PreparedStatement within a loop. In this scenario, we are working with a table named BOOKS.
CREATE TABLE BOOKS (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255),
author VARCHAR(255)
);
Here is inefficient Java code inserting multiple book records:
public class InefficientPreparedStatement {
private static final Logger logger = Logger.getLogger(InefficientPreparedStatement.class.getName());
public static void main(String[] args) throws Exception {
Connection connection = DriverManager.getConnection("jdbc:h2:mem:testdb", "sa", "password");
// Create table
try (Statement stmt = connection.createStatement()) {
stmt.execute("CREATE TABLE BOOKS (id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255), author VARCHAR(255))");
}
// Inefficient: preparing and closing the statement in each loop iteration
for (int i = 1; i <= 5; i++) {
try (PreparedStatement pstmt = connection.prepareStatement("INSERT INTO books (title, author) VALUES (?, ?)")) {
pstmt.setString(1, "Book " + i);
pstmt.setString(2, "Author " + i);
pstmt.executeUpdate();
logger.log(Level.INFO, "Inserted Book {0} by Author {1}", new Object[]{i, i});
}
}
// Verify insertions
try (Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM books")) {
rs.next();
logger.log(Level.INFO, "Books inserted: {0}", rs.getInt(1));
}
connection.close();
}
}
What’s wrong with this approach is that it creates a new PreparedStatement during each loop iteration, which forces the database to parse and compile the SQL statement every time. This prevents the JDBC driver and database from reusing the execution plan, thereby defeating the entire purpose of using a PreparedStatement. As the volume of data increases, this inefficiency can lead to significantly slower performance and higher resource consumption.
3. Efficient Methods to Reuse a PreparedStatement
Next let’s look at how to do it right. Below are some patterns for reusing PreparedStatement properly.
3.1 Reuse Within a Loop Without Batch
Let’s improve the previous code by creating the PreparedStatement once and reusing it in the loop.
public class ReusedPreparedStatement {
private static final Logger logger = Logger.getLogger(ReusedPreparedStatement.class.getName());
public static void main(String[] args) throws Exception {
Connection connection = DriverManager.getConnection("jdbc:h2:mem:testdb", "sa", "password");
try (Statement stmt = connection.createStatement()) {
stmt.execute("CREATE TABLE BOOKS (id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255), author VARCHAR(255))");
}
String query = "INSERT INTO BOOKS (title, author) VALUES (?, ?)";
try (PreparedStatement pstmt = connection.prepareStatement(query)) {
for (int i = 1; i <= 5; i++) {
pstmt.setString(1, "Efficient Book " + i);
pstmt.setString(2, "Efficient Author " + i);
pstmt.executeUpdate();
logger.log(Level.INFO, "Inserted Efficient Book {0} by Efficient Author {1}", new Object[]{i, i});
}
}
// Verify insertions
try (Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM books")) {
rs.next();
logger.log(Level.INFO, "Books inserted: {0}", rs.getInt(1));
}
connection.close();
}
}
A better approach here was to prepare the PreparedStatement once and reuse it across multiple executions. This allows the database to reuse the execution plan, improving performance and reducing overhead. Additionally, this method leads to cleaner, faster, and more maintainable code.
3.2 Batch Insert with PreparedStatement
Batch processing is the best method when inserting or updating a large number of records.
public class BatchedPreparedStatement {
private static final Logger logger = Logger.getLogger(BatchedPreparedStatement.class.getName());
public static void main(String[] args) throws Exception {
Connection connection = DriverManager.getConnection("jdbc:h2:mem:testdb", "sa", "password");
try (Statement stmt = connection.createStatement()) {
stmt.execute("CREATE TABLE BOOKS (id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255), author VARCHAR(255))");
}
String query = "INSERT INTO BOOKS (title, author) VALUES (?, ?)";
try (PreparedStatement pstmt = connection.prepareStatement(query)) {
for (int i = 1; i <= 15; i++) {
pstmt.setString(1, "Batched Book " + i);
pstmt.setString(2, "Batched Author " + i);
pstmt.addBatch();
}
int[] results = pstmt.executeBatch();
logger.log(Level.INFO, "Batch inserted {0} books", results.length);
}
// Verify insertions
try (Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM books")) {
rs.next();
logger.log(Level.INFO, "Books inserted: {0}", rs.getInt(1));
}
connection.close();
}
}
Output:
This approach is better because all SQL commands are sent to the database in a single batch, which reduces network traffic and takes advantage of the database engine’s batch processing capabilities. It is highly efficient for handling large volumes of data and significantly lowers JDBC overhead, resulting in improved overall throughput.
4. Conclusion
In this article, we explored the concept of reusing PreparedStatement objects in Java’s JDBC API to improve both performance and code quality. We began by examining an inefficient pattern by creating a new PreparedStatement inside a loop, which leads to unnecessary parsing and compilation of SQL statements, increased overhead, and degraded performance. We then demonstrated better approaches such as preparing the statement once and reusing it with different parameters, as well as leveraging JDBC batch updates to send multiple statements in a single execution.
In conclusion, reusing a PreparedStatement in Java is an effective way to improve performance and write cleaner code. Depending on your use case, choose the right reuse strategy. For bulk inserts, use batch processing with executeBatch() and addBatch().
5. Download the Source Code
This article explored how to reuse PreparedStatement in Java SQL for efficient and optimized database operations.
You can download the full source code of this example here: java sql reuse preparedstatement
