Program for Arraylist as a Prepared Statement Parameter
Below is the solution for how to use an arraylist as a prepared statement parameter:
// 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. |
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.
Contact Us