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

Nishtha Thakur

Updated on 30-Jul-2019 22:30:26

• Related Questions & Answers
• 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 where Clause in Android sqlite?
• Passing an array to a query using WHERE clause in MySQL?
• How to update multiple rows using single WHERE clause in MySQL?
• Get all results using WHERE clause in MySQL?
• Using the entire expression in MySQL WHERE clause?
• How to add a where clause in a MySQL Insert statement?
• What are the advantages and limitations of JDBC PreparedStatement?
• How to insert Timestamp value in a database using JDBC program?
• How to use MySQL VIEW with WHERE clause?
• MySQL to Implementing OR operator in a WHERE clause?
• Conditional WHERE clause in MySQL stored procedure to set a custom value for NULL values