JDBC Prepared Statements example

In this tutorial you will learn about the JDBC Prepared statements, the Java JDBC Prepared statements are pre-compiled SQL statements. Precompiled SQL is useful where same SQL has to be executed more than one time or repeatedly.

For example you need to insert thousand of data in one table, for that you have to make thousands of INSERT SQL statement with data, here you can note that the structure of INSERT will be same for all the INSERT SQL statements except data values. In this situation you can make use of Java JDBC Prepared statements.

The Prepared statements in java JDBC only save your time if you have to execute the same SQL again and again. Every java SQL Prepared statement is compiled at some point.

To use the Prepared Statements first you have to get the PreparedStatement object by Connection object as below code.

The java JDBC PreparedStatements are very useful especially where you can use a for loop or while loop to set a parameter to a succession of values. If you want to execute a Statement object many times, it normally reduces execution time to use a PreparedStatement object inmentstead.

// Prepare the INSERT STATEMENTS for PERSON table, in this example we are using the PERSON as table name and it has
// Three columns as PERSON_ID, NAME and AGE
String INSERT_SQL = "INSERT INTO PERSON VALUES(?, ?, ?)";
// Get the Prepared statement object
statement = connection.prepareStatement(INSERT_SQL);

In the above example we have the set the SQL query and get the prepared statement object based on INSER SQL statement.

The syntax is straightforward you have to just insert question marks for any parameters that you will be substituting before you send the INSERT SQL to the database.

You must supply values to be used in place of the question mark placeholders before sending it to execute the SQL. You can set the values by calling the one of setXXX method, where XXX is the data type of that column of table used in SQL. There is a setXXX method for each primitive type declared in the Java programming language.

Below is the complete code of JDBC Prepared statement example.

package com.java.connect.jdbc;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
 
public class JDBCPreparedStatementExample {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement statement = null;
 
        try {
            // Register the mysql driver
            Class.forName("com.mysql.jdbc.Driver"); // Or any other driver which database you want to connect.
            // Get the connection object
            connection = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "");
            // Prepare the INSERT STATEMENTS for PERSON table, in this example we are using 
                    // the PERSON as table name and it has
            // Three columns as PERSON_ID, NAME and AGE
            String INSERT_SQL = "INSERT INTO PERSON VALUES(?, ?, ?)";
            // Get the Prepared statement object
            statement = connection.prepareStatement(INSERT_SQL);
            statement.setString(1, "1");
            statement.setString(2, "Mahendra");
            statement.setString(3, "29");
            statement.executeUpdate();
            statement.setString(1, "2");
            statement.setString(2, "Rajendra");
            statement.setString(3, "33");
            statement.executeUpdate();
            statement.setString(1, "3");
            statement.setString(2, "Surendra");
            statement.setString(3, "22");
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            // Finally we have close all the JDBC resources
            try {
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
 
}