How to set values to list of parameters of IN clause on PreparedStatement using JDBC?

The IN clause in MYSQL database is used to specify the list of parameters in a query.

For example, you need to retrieve contents of a table using specific IDs you can do so using the SELECT statement along with the IN clause as −

mysql> SELECT * from sales where ID IN (1001, 1003, 1005);
+------+-------------+--------------+--------------+--------------+-------+------------+
| ID   | ProductName | CustomerName | DispatchDate | DeliveryTime | Price | Location   |
+------+-------------+--------------+--------------+--------------+-------+------------+
| 1001 | Key-Board   | Raja         | 2019-09-01   | 11:00:00     | 8500  | Hyderabad  |
| 1003 | Mouse       | Puja         | 2019-03-01   | 10:59:59     | 4500  | Vijayawada |
| 1005 | Headset     | Jalaja       | 2019-04-06   | 11:08:59     | 7500  | Goa        |
+------+-------------+--------------+--------------+--------------+-------+------------+
3 rows in set (0.03 sec)

When you use the IN clause in prepared statement you can use bind variables for the parameters list (one for each) and set values for those later using the setter methods of the PreparedStatement interface and, after setting values to all the bind variables in the statement you can execute that particular statement using the execute() method.

String query = "UPDATE sales SET price = price+1500 WHERE ProductName IN (?, ?, ? )";
PreparedStatement pstmt = con.prepareStatement(query);
pstmt.setString(1, "Key-Board");
pstmt.setString(2, "Mouse");
pstmt.setString(3, "Headset");
pstmt.execute();

Example

Let us create a table with name sales in MySQL database, with one of the columns as auto-incremented, using CREATE statement as shown below −

CREATE TABLE Sales(
   ID INT PRIMARY KEY AUTO_INCREMENT,
   ProductName VARCHAR (20),
   CustomerName VARCHAR (20),
   DispatchDate date,
   DeliveryTime time,
   Price INT,
   Location VARCHAR(20)
);

Now, we will insert 5 records in sales table using INSERT statements −

insert into sales (ProductName, CustomerName, DispatchDate, DeliveryTime, Price, Location) values('Key-Board', 'Raja', DATE('2019-09-01'), TIME('11:00:00'), 7000, 'India');
insert into sales (ProductName, CustomerName, DispatchDate, DeliveryTime, Price, Location) values('Earphones', 'Roja', DATE('2019-05-01'), TIME('11:00:00'), 2000, 'Vishakhapatnam');
insert into sales (ProductName, CustomerName, DispatchDate, DeliveryTime, Price, Location) values('Mouse', 'Puja', DATE('2019-03-01'), TIME('10:59:59'), 3000, 'Vijayawada');
insert into sales (ProductName, CustomerName, DispatchDate, DeliveryTime, Price, Location) values('Mobile', 'Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 9000, 'Chennai');
insert into sales (ProductName, CustomerName, DispatchDate, DeliveryTime, Price, Location) values('Headset', 'Jalaja', DATE('2019-04-06'), TIME('11:08:59'), 6000, 'Goa');

Following JDBC program establishes connection with the database and increases the price value of the products key-board, mouse and, Headset by 1500 each, using the IN clause.

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class PreparedStatement_IN_clause {
   public static void main(String args[]) throws SQLException {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/sample_database";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Inserting values to a table
      String query = "UPDATE sales SET price = price+1500 WHERE ProductName IN (?, ?, ? )";
      PreparedStatement pstmt = con.prepareStatement(query);
      pstmt.setString(1, "Key-Board");
      pstmt.setString(2, "Mouse");
      pstmt.setString(3, "Headset");
      pstmt.execute();
      System.out.println("Price values updated ......");
      System.out.println("Contents of the Sales table after the update: ");
      //Retrieving data
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery("select * from sales");
      while(rs.next()) {
         System.out.print("Name: "+rs.getString("ProductName")+", ");
         System.out.print("Customer Name: "+rs.getString("CustomerName")+", ");
         System.out.print("Dispatch Date: "+rs.getDate("DispatchDate")+", ");
         System.out.print("Delivery Time: "+rs.getTime("DeliveryTime")+", ");
         System.out.print("Price: "+rs.getInt("Price")+", ");
         System.out.print("Location: "+rs.getString("Location"));
         System.out.println();
      }
   }
}

Output

Connection established......
Price values updated ......
Contents of the Sales table after the update:
Name: Key-Board, Customer Name: Raja, Dispatch Date: 2019-09-01, Delivery Time: 11:00:00, Price: 8500, Location: Hyderabad
Name: Earphones, Customer Name: Roja, Dispatch Date: 2019-05-01, Delivery Time: 11:00:00, Price: 2000, Location: Vishakhapatnam
Name: Mouse, Customer Name: Puja, Dispatch Date: 2019-03-01, Delivery Time: 10:59:59, Price: 4500, Location: Vijayawada
Name: Mobile, Customer Name: Vanaja, Dispatch Date: 2019-03-01, Delivery Time: 10:10:52, Price: 9000, Location: Chennai
Name: Headset, Customer Name: Jalaja, Dispatch Date: 2019-04-06, Delivery Time: 11:08:59, Price: 7500, Location: Goa