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.

A typical template would look like this: “INSERT INTO EMPLOYEE (ID, NAME) VALUES (?, ?);”
Here values are set in runtime at placeholders represented by “?”.

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.

JDBC-Icon

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 !!

Was this post helpful?

Join 7000+ Fellow Programmers

Subscribe to get new post notifications, industry updates, best practices, and much more. Directly into your inbox, for free.

6 thoughts on “Java JDBC PreparedStatement Example”

  1. Hie Lokesh?
    Well I got this very helpful but then how do I use the prepsre statement using values eneterd from keyboard by the user? Im making a mysql database that holds informstion about administrators lecturers and students. And I want an admin to register a lecturer by inputing their data through netbeans then updating automatically updating their table in the database.
    Thank you in advance

  2. Hi Lokesh

    Thanks for a nice walk through session on PreaparedStatement.But the suggestion, in which scenario developer will go for Statement/PreapedStatement if you will include that will be very nice bcoz now a days that is very common question.

  3. Hey Lokesh, I subscribed to your newsletter, and for some reason, I am getting repeated emails specifically of this article, How to execute PreparedStatement using JDBC. I suspect that there is some issue with your server or whatever it is you are using to send these articles out. I’m hoping to remain subscribed, so hopefully you can find the problem . . . . I’m probably not the only person having this issue. Thanks.

    • I am sorry for the trouble. I am using wordpress’s native email subscription which works automatically and I never interfere in this thing. I will however try to find out root cause.

Comments are closed.

HowToDoInJava

A blog about Java and its related technologies, the best practices, algorithms, interview questions, scripting languages, and Python.