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