HowToDoInJava

  • Python
  • Java
  • Spring Boot
  • Dark Mode
Home / Java / JDBC / Java JDBC PreparedStatement Example

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

Share this:

  • Twitter
  • Facebook
  • LinkedIn
  • Reddit

About Lokesh Gupta

A family guy with fun loving nature. Love computers, programming and solving everyday problems. Find me on Facebook and Twitter.

Feedback, Discussion and Comments

  1. tanakah

    July 26, 2015

    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

    • Lokesh Gupta

      July 27, 2015

      Is there any problem in passing the values though some method parameters?? Probably I do not understand your problem correctly.

  2. Samir Badr

    November 27, 2013

    nice session

  3. Tony

    November 24, 2013

    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.

  4. Shawn Irwin

    November 24, 2013

    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.

    • Lokesh Gupta

      November 24, 2013

      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 on this article!

Search Tutorials

JDBC Tutorial

  • JDBC – Introduction
  • JDBC – MySQL Connection
  • JDBC – Types of Driver
  • JDBC – SQL SELECT Query
  • JDBC – SQL INSERT Query
  • JDBC – SQL DELETE Query
  • JDBC – PreparedStatement
  • JDBC – Optimization Tips

Java Tutorial

  • Java Introduction
  • Java Keywords
  • Java Flow Control
  • Java OOP
  • Java Inner Class
  • Java String
  • Java Enum
  • Java Collections
  • Java ArrayList
  • Java HashMap
  • Java Array
  • Java Sort
  • Java Clone
  • Java Date Time
  • Java Concurrency
  • Java Generics
  • Java Serialization
  • Java Input Output
  • Java New I/O
  • Java Exceptions
  • Java Annotations
  • Java Reflection
  • Java Garbage collection
  • Java JDBC
  • Java Security
  • Java Regex
  • Java Servlets
  • Java XML
  • Java Puzzles
  • Java Examples
  • Java Libraries
  • Java Resources
  • Java 14
  • Java 12
  • Java 11
  • Java 10
  • Java 9
  • Java 8
  • Java 7

Meta Links

  • About Me
  • Contact Us
  • Privacy policy
  • Advertise
  • Guest and Sponsored Posts

Recommended Reading

  • 10 Life Lessons
  • Secure Hash Algorithms
  • How Web Servers work?
  • How Java I/O Works Internally?
  • Best Way to Learn Java
  • Java Best Practices Guide
  • Microservices Tutorial
  • REST API Tutorial
  • How to Start New Blog

Copyright © 2020 · HowToDoInjava.com · All Rights Reserved. | Sitemap

  • Java 15 New Features
  • Sealed Classes and Interfaces
  • EdDSA (Ed25519 / Ed448)