Java PreparedStatement

Filed Under: Java
Java PreparedStatement Important Points
  • Java PreparedStatement is just like a Statement and it’s part of the Java JDBC Framework.
  • It is used to perform crud operations with Database. 
  • PreparedStatement extends the Statement interface.
  • PreparedStatement is considered as more secure as it supports parameterized queries. PreparedStatement also prevents SQL Injection attacks.
  • We can obtain an instance of PreparedStatement by calling the prepareStatement(String query) method of Connection as shown below.
// Method :

public PreparedStatement prepareStatement(String query)throws SQLException{}

// Usage :

Connection con = DriverManager.getConnection ("jdbc:mysql://localhost:3306/customerdb", "root", "root");

PreparedStatement ps = con.prepareStatement("select id, firstname, lastname, email, birthdate from tblcustomer");

PreparedStatement Advantages

  • PreparedStatement can be used for both parameterized query and normal query.
  • Query Performance of PreparedStatement is better than that of the Statement.
  • An instance of PreparedStatement can be reused to execute the same query with different parameters.
  • PreparedStatement saves application from SQL Injection attacks.
Java PreparedStatement Hierarchy
Java PreparedStatement Hierarchy

PreparedStatement Methods

We can divide the methods into different categories.

1. Executing Query

  • ResultSet executeQuery(): This method is used perform read operation using PreparedStatement object. It returns an instance of ResultSet is used to get data.
  • int executeUpdate(): This method is used to execute insert, delete and update queries. It will return an integer value indicating numbers database row affected by the query.

2. Passing Parameter values to Query

All of the below methods have 2 arguments. 1st argument is Parameter Index and 2nd argument is the Value of Parameter.

  1. void setInt(int parameterIndex, int value): This method sets the Integer value to the specified parameter index.
  2. void setShort(int parameterIndex, short value): This method sets the short value to the specified parameter index.
  3. void setLong(int parameterIndex, long value): This method sets the Long value to the specified parameter index.
  4. void setFloat(int parameterIndex, float value): This method sets the Float value to the specified parameter index.
  5. void setDouble(int parameterIndex, double value): This method sets the Double value to the specified parameter index.
  6. void setBigDecimal(int parameterIndex, BigDecimal value): This method sets the BigDecimal value to the specified parameter index.
  7. void setString(int parameterIndex, String value): This method sets the String value to the specified parameter index.
  8. void setDate(int parameterIndex, Date value): This method sets the Date value to the specified parameter index.

Note: The parameterIndex value starts from 1 and all of these methods throw SQLException.

Java PreparedStatement Example

We will be using the MySQL database to demonstrate the usage of PreparedSatement. Use below DB scripts to create database, tables and sample data.

create database customerdb;

use customerdb;

create table tblcustomer(
    id integer AUTO_INCREMENT primary key,
    firstname varchar(32),
    lastname varchar(32),
    email varchar(32),
    birthdate datetime
);

insert into tblcustomer (id,firstname,lastname,email,birthdate) values(1,'Ricky','Smith','ricky@google.com','2001-12-10');

Database Connection Information:

Name of MySql Database: customerdb
IP: localhost
Port: 3306
username: root
password: root

Maven Dependency:

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.48</version>
    </dependency>
</dependencies>

1. Get data using PreparedStatement

In this case, we will fetch the row having specified id from tblcustomer. The Query will return a single row.

package com.journaldev.examples;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class PreparedStatementDemo {
    public static void main(String[] args) throws Exception {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int customerId = 1;
        String query = "select id, firstname, lastname, email,     birthdate from tblcustomer where id = ?";
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/customerdb", "root", "root");
            ps = con.prepareStatement(query);
            ps.setInt(1, customerId);
            rs = ps.executeQuery();
            while (rs.next()) {
                System.out.println("Id:" + rs.getInt(1));
                System.out.println("First Name:" + rs.getString(2));
                System.out.println("Last Name:" + rs.getString("lastname"));
                System.out.println("Email:" + rs.getString("email"));
                System.out.println("BirthDate:" + rs.getDate("birthdate"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            rs.close();
            ps.close();
            con.close();
        }
    }
}

Understanding the execution steps:

Step 1: Loading JDBC Driver.

Class.forName(“com.mysql.jdbc.Driver”) loads jdbc driver into memory.

Step 2: Now we need to obtain the Connection object. The following line will do it.

DriverManager.getConnection(“<<JDBC Url>>”, “<<Db username>>”, “<<db password>>”);

Step 3: We can obtain instance of PreparedStatement from Connection object. We need to also specify query that we want to execute. e.g.

PreparedSatement ps = con.prepareStatement(<<Query>>);

PreparedStatement also supports parameterized query.

‘?’ is the parameter in the query. The value of this parameter needs to be provided before executing the Query.

Step 4: Providing Values of query Parameters. There is only one parameter in the above example i.e. id of type integer.

int customerId = 1;
ps.setInt(1, customerId);
setInt(<<Parameter Number>>,<<Parameter Value>) method has 2 argument.
In the above example, ‘1’ is parameter number and variable customerId is the value of Parameter.

Step 5: Executing Query.

executeQuery() method of PreparedStatement is used to execute the select query. It will return the instance of ResultSet. If your query if for insert, update or delete purpose then you can use executeUpdate().

Step 6: Iterating ResultSet. next() method of ResultSet is used to obtain query output.

Step 7: Closing Resources: It’s one of the important steps. Many developers forget to close resources like ResultSet, PreparedStatement, and Connection. It will result in Resource leakage which can bring down your application.

Output of Program:

Id:1
First Name:Ricky
Last Name:Smith
Email:ricky@google.com
BirthDate:2001-12-1

2. Insert Operation using  PreparedStatement

In this example, we will use PreparedStatement to perform insert operation in tblcustomer table.

package com.journaldev.examples;

import java.sql.*;
import java.text.SimpleDateFormat;

public class PrepareStatementInsertDemo {
    public static void main(String[] args) throws Exception {
        {
            Connection con = null;
            PreparedStatement ps = null;
            ResultSet rs = null;
            String firstname = "matthew";
            String lastname = "wade";
            String email = "matthew@java.com";
            Date birthdate = new Date(new SimpleDateFormat("YYYY-MM-DD").parse("2000-12-12").getTime());
            String query = "insert into tblcustomer (id,firstname,lastname,email,birthdate) values(default,?,?,?,?)";
            try {
                Class.forName("com.mysql.jdbc.Driver");
                con = DriverManager.getConnection("jdbc:mysql://localhost:3306/customerdb", "root", "root");
                ps = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, firstname);
                ps.setString(2, lastname);
                ps.setString(3, email);
                ps.setDate(4, birthdate);
                int row = ps.executeUpdate();
                System.out.println("No. of Rows inserted:" + row);
                rs = ps.getGeneratedKeys();
                if (rs.next()) {
                    System.out.println("Id of new Customer:" + rs.getInt(1));
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                rs.close();
                ps.close();
                con.close();
            }
        }
    }
}

In this example, while creating an instance of PreparedStatement, we have passed 2 arguments. 1st is the query itself and 2nd is “Statement.RETURN_GENERATED_KEYS“, which will help us to get the primary key value of the new row.

The below code is used to provide parameters for Insert Query.

ps.setString(1, firstname);
ps.setString(2, lastname);
ps.setString(3, email);
ps.setDate(4, birthdate);

As stated in the previous program, executeUpdate() method is used to perform the insert operation. It will return the number of rows affected by our query.

Output of Program:

No. of Rows inserted:1
Id of new Customer:2

If you go to DB and execute a select query then you will see the below result.

mysql> use customerdb;

Database changed

mysql> select * from tblcustomer;
+----+-----------+----------+------------------+---------------------+
| id | firstname | lastname | email            | birthdate           |
+----+-----------+----------+------------------+---------------------+
|  1 | Ricky     | Smith    | ricky@google.com | 2001-12-10 00:00:00 |
|  2 | matthew   | wade     | matthew@java.com | 1999-12-26 00:00:00 |
+----+-----------+----------+------------------+---------------------+
2 rows in set (0.00 sec)

3. Update Operation using PreparedStatement

Now we will perform the update operation. We will update the first name and last name of the customer having email “matthew@java.com”. This row was inserted in the previous example.

package com.journaldev.examples;

import java.sql.*;

public class PrepareStatementUpdateDemo {
    public static void main(String[] args) throws Exception {
        {
            Connection con = null;
            PreparedStatement ps = null;
            String email = "matthew@java.com";
            String newFirstname = "john";
            String newLastname = "smith";
            String query = "update tblcustomer set firstname = ?,lastname =? where email = ?";
            try {
                Class.forName("com.mysql.jdbc.Driver");
                con = DriverManager.getConnection("jdbc:mysql://localhost:3306/customerdb", "root", "root");
                ps = con.prepareStatement(query);
                ps.setString(1, newFirstname);
                ps.setString(2, newLastname);
                ps.setString(3, email);
                int row = ps.executeUpdate();
                System.out.println("No. of Rows Updated:" + row);
                if (row == 1) {
                    String selectQuery = "select id,firstname,lastname,email,birthdate from tblcustomer where email=?";
                    try (PreparedStatement selStatement = con.prepareStatement(selectQuery);
                    ) {
                        selStatement.setString(1, email);
                        ResultSet rs = selStatement.executeQuery();
                        if (rs.next()) {
                            System.out.println("Id:" + rs.getInt(1));
                            System.out.println("First Name:" + rs.getString(2));
                            System.out.println("Last Name:" + rs.getString("lastname"));
                            System.out.println("Email:" + rs.getString("email"));
                            System.out.println("BirthDate:" + rs.getDate("birthdate"));
                        }
                        rs.close();
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                ps.close();
                con.close();
            }
        }
    }
}

Understanding the program:

In the above example, we have 3 parameters in the query. 1st is the new first name, 2nd is new last name and 3rd is the email of the customer.

The below line of code provides value of this parameter to PreparedStatement.

ps.setString(1, newFirstname);
ps.setString(2, newLastname);
ps.setString(3, email);

executeUpdate() method is used to execute update query. It will return the number of rows updated by the query.

Output of Program:

No. of Rows Updated:1
Id:2
First Name:john
Last Name:smith
Email:matthew@java.com
BirthDate:1999-12-26

You can check the update in the database using the SQL query.

mysql> select * from tblcustomer;
+----+-----------+----------+------------------+---------------------+
| id | firstname | lastname | email            | birthdate           |
+----+-----------+----------+------------------+---------------------+
|  1 | Ricky     | Smith    | ricky@google.com | 2001-12-10 00:00:00 |
|  2 | john      | smith    | matthew@java.com | 1999-12-26 00:00:00 |
+----+-----------+----------+------------------+---------------------+
2 rows in set (0.00 sec)

4. Delete Operation using PreparedStatement

Now we will delete customer record having email “matthew@java.com”.

package com.journaldev.examples;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class PrepareStatementDeleteDemo {
    public static void main(String[] args) throws Exception {
        {
            Connection con = null;
            PreparedStatement ps = null;
            String email = "matthew@java.com";
            String query = "delete from tblcustomer where email = ?";
            try {
                Class.forName("com.mysql.jdbc.Driver");
                con = DriverManager.getConnection("jdbc:mysql://localhost:3306/customerdb", "root", "root");
                ps = con.prepareStatement(query);
                ps.setString(1, email);
                int row = ps.executeUpdate();
                System.out.println("No. of Rows Deleted:" + row);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                ps.close();
                con.close();
            }
        }
    }
}

Batch Methods in PreparedStatement

  • void addBatch(): This method used to adds sets of the parameter to this PreparedStatement object’s batch to update multiple rows.
  • int[] executeBatch(): This method executes all the SQL queries from PreparedStatement object’s batch and returns the array of update counts. This method throws a BatchUpdateException if it fails to execute and JDBC driver may or may not continue to process the remaining batch.

Batch/Bulk Operation using PreparedStatement

package com.journaldev.examples;
import java.sql.*;
import java.text.SimpleDateFormat;

public class PrepareStatementBatchDemo {
    public static void main(String[] args) throws Exception {
        {
            Connection con = null;
            PreparedStatement ps = null;
            ResultSet rs = null;
            SimpleDateFormat sdf = new SimpleDateFormat("YYYY-MM-DD");
            String query = "insert into tblcustomer (id,firstname,lastname,email,birthdate) values(default,?,?,?,?)";
            try {
                Class.forName("com.mysql.jdbc.Driver");
                con = DriverManager.getConnection("jdbc:mysql://localhost:3306/customerdb", "root", "root");
                ps = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);

                // 1st Insert
                ps.setString(1, "Ross");
                ps.setString(2, "Southee");
                ps.setString(3, "ross@java.com");
                ps.setDate(4, new Date(sdf.parse("2000-12-12").getTime()));
                ps.addBatch();

                // 2nd Insert
                ps.setString(1, "Mayank");
                ps.setString(2, "Kohli");
                ps.setString(3, "mayank@java.com");
                ps.setDate(4, new Date(sdf.parse("2005-12-12").getTime()));
                ps.addBatch();

                // 3rd Insert
                ps.setString(1, "Tom");
                ps.setString(2, "Patel");
                ps.setString(3, "tom@java.com");
                ps.setDate(4, new Date(sdf.parse("1995-12-12").getTime()));
                ps.addBatch();

                // Execution
                int[] rows = ps.executeBatch();

                for (int row : rows) {
                    System.out.println("No. of Rows inserted:" + row);
                }
                rs = ps.getGeneratedKeys();
                while (rs.next()) {
                    System.out.println("Id of new Customer:" + rs.getInt(1));
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                rs.close();
                ps.close();
                con.close();
            }
        }
    }
}

In the above example, we have inserted 3 records of customers in one batch. It is more effective to insert multiple rows in batch instead of single-row. The addBatch() method adds data in a batch. The executeBatch() executes all the queries in the batch.

Output:

No. of Rows inserted:1
No. of Rows inserted:1
No. of Rows inserted:1
Id of new Customer:10
Id of new Customer:11
Id of new Customer:12
You can download the complete Java project from this link.

Reference: Java doc

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages