Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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
Updated on: 30-Jul-2019
3K+ Views
Advertisements
To Continue Learning Please Login