- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
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
- Related Articles
- How to set values to list of parameters of IN clause on PreparedStatement using JDBC?
- How to retrieve auto-incremented value generated by PreparedStatement using JDBC?
- How to store decimal values in a table using PreparedStatement in JDBC?
- What is PreparedStatement in JDBC?
- How to update multiple rows using single WHERE clause in MySQL?
- How to where Clause in Android sqlite?
- How to filter data using where Clause and “IN” in Android sqlite?
- Passing an array to a query using WHERE clause in MySQL?
- How to filter data using where Clause and “GLOB” in Android sqlite?
- How to filter data using where Clause and “LIKE” in Android sqlite?
- How to filter data using where Clause and “AND” in Android sqlite?
- How to insert Timestamp value in a database using JDBC program?
- How to filter data using where Clause and “NOT IN” in Android sqlite?
- What are the advantages and limitations of JDBC PreparedStatement?
- Get all results using WHERE clause in MySQL?
