如何为列表构建SQL SELECT语句？

Question

private int year
private String make
private String model
private String color

Car car1 = new Car(2015, "Toyota", "Camry", "Blue")
Car car2 = new Car(2017, "Honda", "Corolla", "White")
Car car3 = new Car(2011, "Honda", "Civic", "Red")
 

select * from cars_table where year in (2015,2011,2017) and make in (“Toyota”,”Honda”) and model in (“Camry”,”Corolla”,”Civic”)

我相信这个sql语句可以工作，但我不知道如何用Java构建这个语句。我的算法技能不是最好的，我试着用一个for循环来处理列表，但我不能拼凑出第二个get引用。太感谢你们了！

Answer 1

您的查询将返回太多的行，因为它将返回2011丰田花冠（以及许多其他不需要的组合）。

若要仅用这些值的组合返回值，需要查询：

SELECT *
FROM   cars_table
WHERE  ( year, make, model ) IN (
         ( 2015, 'Toyota', 'Camry' ),
         ( 2017, 'Honda', 'Corolla' ),
         ( 2011, 'Honda', 'Civic' )
       )

db<>在这里摆弄

StringBuilder query = new StringBuilder();
query.append( "SELECT * FROM cars_table WHERE (year, make, model) IN (" );
boolean first = true;
for ( final Car car: carList )
{
  if ( first )
  {
    first = false;
  } else {
    query.append(',');
  }
  query.append('(');
  query.append(car.getYear());
  query.append(",'");
  query.append(car.getMake());
  query.append("','");
  query.append(car.getModel());
  query.append("')");
}
query.append(')');
System.out.println(query);