java.sql.PreparedStatement – Prepared Statement Java Example

Aldo ZiflajOctober 8th, 2014Last Updated: January 4th, 2021

0 205 6 minutes read

In this example, we will discuss the Prepared Statement interface and its usage in Java using examples. This interface creates an object that represents a precompiled SQL statement. This object can then be used to efficiently execute this statement multiple times.

The PreparedStatement interface extends the Statement interface, which is used for executing a static SQL statement and returning the results it produces.

The PreparedStatement interface exists since JDK1.2, with methods added in both JDK1.4 and JDK1.6.

1. Why using prepared statements?

Prepared statements examples have two main advantages above static statements. Firstly, they have a better performance than static statements. This because they are compiled only once from the database engine, and not everytime they get executed.

Secondly, prepared statements are the best protection against SQL injections. This because of the fact that the values that will be inserted into a SQL query are sent to the SQL server after the actual query is sent to the server. In other words, the data input by a potential hacker is sent separately from the prepared query statement. This means that there is absolutely no way that the data input by a hacker can be interpreted as SQL, and there’s no way that the hacker could run his own SQL on your application.

2. Selecting with Prepared Statement in Java

To see how PreparedStatement is used, firstly create a database, a table and put some data in it by executing this SQL:

CREATE DATABASE db_test;
CREATE TABLE IF NOT EXISTS db_test.tbl (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    surname VARCHAR(20) NOT NULL,
    age INT NOT NULL
);
 
INSERT INTO db_test.tbl VALUES (1, 'Bruce', 'Scott', 65);
INSERT INTO db_test.tbl VALUES (2, 'John', 'Doe', 40);
INSERT INTO db_test.tbl VALUES (3, 'Jane', 'Doe', 35);

Now that the database is ready and has some data, we can continue with some Java code. Create a class called PreparedSelectExample with the following source code:

PreparedSelectExample.java

package com.javacodegeeks.examples;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
public class PreparedSelectExample {
    private static final String HOST = "jdbc:mysql://localhost/";
    private static final String DB = "db_test";
    private static final String USER = "root";
    private static final String PASSWORD = "bitnami";
     
    public static void main(String[] args) {
        String sqlQuery = "SELECT * FROM tbl WHERE surname = ?";
         
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection(HOST+DB,USER,PASSWORD);
             
            PreparedStatement preparedSelect = conn.prepareStatement(sqlQuery);
            preparedSelect.setString(1, "Doe");
            ResultSet rs = preparedSelect.executeQuery();
             
            while (rs.next()) {
                int id = rs.getInt(1);
                String name = rs.getString(2);
                String surname = rs.getString(3);
                int age = rs.getInt(4);
                System.out.printf("%d\t%s\t%s\t%d\n",id,name,surname,age);
            }
             
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
 
    }
 
}

In this example, I firstly set some global constants that hold information about the database name, username and password (you would want to change the username/password). Then, I create a prepared SQL statement that would select every person from the table tbl that have the same surname. This surname is unknown initially; it can be set later in the program (as I do), it can be entered by the user as an input, etc.

After the connection with the database is done successfully (don’t forget to add the drivers), the prepared SQL statement gets compiled (line 22), and the first question mark on the prepared statement is replaced by “Doe” (line 23). Then, the statement gets executed and the result is printed.

Take a look at line 23, where the question mark of the prepared statement is replaced by “Doe”. PreparedStatement has plenty of these setter methods for different kind of data types, like setArray(), setBigDecimal(), setInt(), setDouble() and many more. The first argument of the setter methods is the index of the question mark that will be replaced by the object, starting by 1.

After executing the above code, the output will be this:

2   John    Doe 40
3   Jane    Doe 35

3. Changing data with PreparedStatement

In the above example the data stored in the database didn’t change, it was just printed in the screen. In that example, we used executeQuery() method to select the data.

When the data is changed, by an insert, update or delete query, the method that should be used is executeUpdate(). The following example will show how to use the method when inserting data, but the case is the same even when deleting or updating.

Create a class called PreparedInsertExample and put the following source code in it:

PreparedInsertExample.java

package com.javacodegeeks.examples;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
 
public class PreparedInsertExample {
    private static final String HOST = "jdbc:mysql://localhost/";
    private static final String DB = "db_test";
    private static final String USER = "root";
    private static final String PASSWORD = "bitnami";
     
    public static void main(String[] args) {
        String sqlQuery = "INSERT INTO `tbl` (`name`, `surname`, `age`) VALUES (?,?,?)";
         
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection(HOST+DB,USER,PASSWORD);
             
            PreparedStatement preparedInsert = conn.prepareStatement(sqlQuery);
             
            String myName = "Aldo";
            String mySurname = "Ziflaj";
            int myAge = 19;
             
            preparedInsert.setInt(3, myAge);
            preparedInsert.setString(1, myName);
            preparedInsert.setString(2, mySurname);
             
            int status = preparedInsert.executeUpdate();
            System.out.println(status);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
         
    }
 
}

Just as in the first example, I created the main structure of the SQL query and then went on adding my data (name, surname and age) at the query. Then, on line 31, the query is executed by calling the executeUpdate() method, which returns either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing.

4. Inserting multiple records with one PreparedStatement

If you were going to insert more than one record on the database, you don’t have to create another prepared statement; the same PreparedStatement instance can be used multiple times.

The following code shows just this, adding a list of people. Firstly, create a Person class:

Person.java

package com.javacodegeeks.examples.multiple_insert;
 
public class Person {
    private String name;
    private String surname;
    private int age;
     
    protected Person() {}
     
    public Person(String name, String surname, int age) {
        this.name = name;
        this.surname = surname;
        this.age = age;
    }
     
    public String getName() {
        return this.name;
    }
     
    public String getSurname() {
        return this.surname;
    }
     
    public int getAge() {
        return this.age;
    }
 
}

This is a very simple class that will hold the information for a single person that would be inserted in the database.

Now create an interface called DbContract and put this code in it:

DbContract.java

package com.javacodegeeks.examples.multiple_insert;
 
public interface DbContract {
    public static final String HOST = "jdbc:mysql://localhost/";
    public static final String DB = "db_test";
    public static final String USER = "root";
    public static final String PASSWORD = "bitnami";
}

You may want to put your data instead of keeping my data here.

In a normal application or software, this interface would store more data, about the tables name, their columns, and so on. This is done to keep the code DRY (Don’t Repeat Yourself).

Now create another class called MultipleInsertExample:

MultipleInsertExample.java

package com.javacodegeeks.examples.multiple_insert;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
 
public class MultipleInsertExample {
     
    public static void main(String[] args) {
        String sqlQuery = "INSERT INTO `tbl` (`name`, `surname`, `age`) VALUES (?,?,?)";
         
        List<Person> coolGuys = new ArrayList<>();
         
        coolGuys.add(new Person("Larry", "Ellison", 70));
        coolGuys.add(new Person("Sergey", "Brin", 41));
        coolGuys.add(new Person("Stephan", "Wozniak", 64));
         
        try {
            Class.forName("com.mysql.jdbc.Driver");
             
            Connection conn = DriverManager.getConnection(
                                        DbContract.HOST+DbContract.DB,
                                        DbContract.USER,
                                        DbContract.PASSWORD);
             
            PreparedStatement preparedInsert = conn.prepareStatement(sqlQuery);
             
            int insertions = 0;
            for (Person guy : coolGuys) {
                preparedInsert.setString(1,guy.getName());
                preparedInsert.setString(2, guy.getSurname());
                preparedInsert.setInt(3, guy.getAge());
                 
                insertions += preparedInsert.executeUpdate();
            }
             
            System.out.println(insertions+" records added");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
         
 
    }
 
}

After going through the two first examples, the code for this is supposed to be kinda obvious. The only difference here is that the same PreparedStatement is used more than once.

After executing this you should see this output:

1	`3 records added`

5. Download the source code

Download
You can download the full source code of this example here: java.sql.PreparedStatement – Prepared Statement Java Example

Last updated on May 20th, 2020