How to use an ArrayList as a Prepared Statement Parameter?

ArrayList is part of the Java Collection Framework and In this article, we will learn how to use an ArrayList as a prepared statement parameter.

Prepared Statement

It is a subinterface of the Statement interface. It is used to execute SQL parameterized queries and enhance performance and security. Here is an example of SQL parameterized queries.

String s="insert into emp values(?,?,?)";  

In the above example, we are passing the parameter ‘?’ for the values. Their values are set when we call the setter method of the PreparedStatement.

Prerequisites:

• Basics of JDBC
• SQL
• Basics of ArrayList

JDBC Prepared Statements and ArrayList

Prepared Statements in JDBC are used for the compilation of SQL queries, enhancing performance by reducing overhead. Parameters that are used in Prepared Statements are represented by placeholders, which are denoted by ‘?’ in the String of SQL query. When ArrayLists are used as parameters, dynamic values can be passed to the SQL query without the need for explicit parameters.

To implement this:

• Construct the String of SQL query with placeholders (‘?’) corresponding to the number of elements present in the ArrayList.
• Iterate over the ArrayList to set parameters in the Prepared Statement using appropriate setter methods.
• Execute the query and process the ResultSet to obtain the desired output.

Program for Arraylist as a Prepared Statement Parameter

Below is the solution for how to use an arraylist as a prepared statement parameter:

Java

// Java Program to use ArrayList as
// Prepared Statement Parameter
package gfg;

import java.sql.*;
import java.util.ArrayList;

public class PreparedStatementExample {
    public static void main(String[] args) {
        // Connection parameters
        String url = "jdbc:oracle:thin:@localhost:1521:orcl";
        String username = "vinay";
        String password = "1234";

        try {
            // Load Oracle JDBC driver
            Class.forName("oracle.jdbc.driver.OracleDriver");

            // Create connection
            Connection connection = DriverManager.getConnection(url, username, password);

            ArrayList<Integer> empIds = new ArrayList<>();
            empIds.add(1);
            empIds.add(2);
            empIds.add(3);

            // Construct SQL query with placeholders
            StringBuilder sql = new StringBuilder("SELECT * FROM emp WHERE empno IN (");
            for (int i = 0; i < empIds.size(); i++) {
                sql.append(i == 0 ? "?" : ", ?");
            }
            sql.append(")");

            // Prepare the statement
            PreparedStatement statement = connection.prepareStatement(sql.toString());

            // Set parameters using ArrayList values
            for (int i = 0; i < empIds.size(); i++) {
                statement.setInt(i + 1, empIds.get(i));
            }

            // Execute query
            ResultSet resultSet = statement.executeQuery();

            // Process the result set
            while (resultSet.next()) {
                System.out.println("Employee ID: " + resultSet.getInt("empno") +
                        ", Name: " + resultSet.getString("empname") +
                        ", Salary: " + resultSet.getDouble("sal") +
                        ", Department Number: " + resultSet.getInt("deptno"));
            }

            // Close resources
            resultSet.close();
            statement.close();
            connection.close();

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
}

Output:

Explanation of the above example:

First, We have to Connect JDBC with the five simple Steps:

• Register the Driver class: using forName() method
• Create connection: getConnection() method is used to create connection
• Create statement: createStatement() is used for statement(here, we use PreparedStatement)
• Execute queries: executeQuery() is used to execute the Query.
• Close Connection: close() is used to close the connection.

For more information refer to the table mentioned below:

Connection Parameters	“url”, “username”, and “password” are Strings and used for accessing the Oracle database. These details include the JDBC URL (url), username (username), and password (password).
Database Connection	we have to create a connection inside try block because it is mandatory to declare inside try block. Class.forName(“oracle.jdbc.driver.OracleDriver”). A connection is established using DriverManager.getConnection(url, username, password), which returns a Connection object.
ArrayList of Employee IDs	An ArrayList named “empIds” to store employee IDs. These IDs are used as parameters in the SQL query to fetch specific employee records from Oracle Database
Construct SQL Query	A StringBuilder named “sql” is used to construct string of the SQL query. The query selects all columns from the “emp” table where the “empno” column matches any of the employee IDs stored in the empIds ArrayList.
Prepare Statement and Set Parameters	The prepareStatement() method is called on the Connection object to create a PreparedStatement object. The dynamically constructed SQL query string is passed as an argument to this method. Parameters in the SQL query are set using the setInt() method of the PreparedStatement object. The employee IDs stored in the empIds ArrayList are set as parameters in the prepared statement.
Execute Query and Process Result Set	The executeQuery() call on the PreparedStatement to execute the SQL query. ResultSet object contains the all the employee records, which are iterated over using a loop. Inside thewhile loop, all of the employee details such as ID, name, salary, and department number are get from the ResultSet using getter methods (getInt(), getString(), getDouble()), and print the output to the console.
Close Resources	Once the result set are processed, resources such as the “ResultSet”, “PreparedStatement”, and “Connection” objects are closed using the close() method to release database and save memory leaks.
Exception Handling	“ClassNotFoundException” and “SQLException” are caught by try-catch block, and their stack traces are printed using the printStackTrace() method which helps in debugging.

Conclusion

Including ArrayLists as parameters in JDBC API Prepared Statements extends the database flexibility and versatility in Java applications. This is one of the way which allows for dynamic query execution, accommodating varying lengths of input data without compromising security or performance. Understanding and leveraging this capability empowers developers to build robust and scalable database-driven applications effectively.