如何使用JDBC在PreparedStatement的where子句中传递值？
使用PreparedStatement使用Where子句执行语句。通过将子句中的值替换为占位符“？”来准备查询并将此查询作为参数传递给prepareStatement()方法。
String query = "SELECT * FROM mobile_sales WHERE unit_sale >= ?"; //创建PreparedStatement对象 PreparedStatement pstmt = con.prepareStatement(query);
稍后，使用PreparedStatement接口的setXXX()方法将值设置为占位符。
pstmt.setInt(1, 4000); ResultSet rs = pstmt.executeQuery();
示例
让我们使用CREATE语句在MySQL数据库中创建一个名称为mobile_sales的表，如下所示-
CREATE TABLE mobile_sales ( mobile_brand VARCHAR(255), unit_sale INT );
现在，我们将使用INSERT语句在mobile_sales表中插入11条记录-
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);以下JDBC程序从mobile_sales表检索记录，其中单位销售值大于或等于4000。
在此示例中，我们使用PreparedStatement执行SELECT查询，并使用setter方法在WHERE子句中设置值。
示例
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 {
//注册驱动程序
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//获得连接
String mysqlUrl = "jdbc:mysql://localhost/testDB";
Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
System.out.println("Connection established......");
//查询以获取单位销售额大于（或等于）4000的移动品牌
String query = "SELECT * FROM mobile_sales WHERE unit_sale >= ?";
//创建PreparedStatement对象
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();
}
}
}输出结果
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