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 pass a value in where clause on PreparedStatement using JDBC?
To execute a statement with Where clause using PreparedStatement. Prepare the query by replacing the value in the clause with place holder “?” and, pass this query as a parameter to the prepareStatement() method.
String query = "SELECT * FROM mobile_sales WHERE unit_sale >= ?"; //Creating the PreparedStatement object PreparedStatement pstmt = con.prepareStatement(query);
Later, set the value to the place holder using the setXXX() method of the PreparedStatement interface.
pstmt.setInt(1, 4000); ResultSet rs = pstmt.executeQuery();
Example
Let us create a table with name mobile_sales in MySQL database using CREATE statement as shown below −
CREATE TABLE mobile_sales ( mobile_brand VARCHAR(255), unit_sale INT );
Now, we will insert 11 records in mobile_sales table using INSERT statements −
insert into mobile_sales values('Iphone', 3000);
insert into mobile_sales values('Samsung', 4000);
insert into mobile_sales values('Nokia', 5000);
insert into mobile_sales values('Vivo', 1500);
insert into mobile_sales values('Oppo', 900);
insert into mobile_sales values('MI', 6400);
insert into mobile_sales values('MotoG', 4360);
insert into mobile_sales values('Lenovo', 4100);
insert into mobile_sales values('RedMI', 4000);
insert into mobile_sales values('MotoG', 4360);
insert into mobile_sales values('OnePlus', 6334);Following JDBC program retrieves the records from mobile_sales table where unit sale value is greater than or equal to 4000.
In this example we use the PreparedStatement to execute the SELECT query and set the value in the WHERE clause using the setter method.
Example
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class PreparedStatement_WhereClause {
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/testDB";
Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
System.out.println("Connection established......");
//Query to retrieve the mobile brand with unit sale greater than (or, equal to) 4000
String query = "SELECT * FROM mobile_sales WHERE unit_sale >= ?";
//Creating the PreparedStatement object
PreparedStatement pstmt = con.prepareStatement(query);
pstmt.setInt(1, 4000);
ResultSet rs = pstmt.executeQuery();
System.out.println("Mobile brands with unit sale greater or equal to 4000: ");
while(rs.next()) {
System.out.print("Name: "+rs.getString("mobile_brand")+", ");
System.out.print("Customer Name: "+rs.getString("unit_sale"));
System.out.println();
}
}
}Output
Connection established...... Mobile brands with unit sale greater or equal to 4000: Name: Samsung, Customer Name: 4000 Name: Nokia, Customer Name: 5000 Name: MI, Customer Name: 6400 Name: MotoG, Customer Name: 4360 Name: Lenovo, Customer Name: 4100 Name: RedMi, Customer Name: 4000 Name: MotoG, Customer Name: 4360 Name: OnePlus, Customer Name: 6334
3K+ Views
To Continue Learning Please Login