Java How to build SQL SELECT statement for List<Car>?

user_12352530 0
Viewed 137

Say we have a Car class with attributes that represents the fields in the oracle database that only allows unique cars with primary keys year, make, and model:

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

Then our example List<Car> could be:

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

Assuming there are multiple entries in the oracle database, if I want a select statement that gets the attributes from a List<Car> to select the same cars with the 3 primary keys in the database, I would want something like:

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

I believe that this sql statement works but I have no idea how to build this statement in Java. My algorithm skills aren’t the best and I’ve tried using a for loop for the List but I can’t piece together a second get reference. Thank you so much!

oracle sql java
1 Answers

Your query will bring back too many rows as it would bring back a 2011 Toyota Corolla (among many other unwanted combinations).

To get back values with only those combinations of values, you want the query:

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

db<>fiddle here

In Java:

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);

Note: this assumes that no SQL injection will take place; if you are taking user input then you should make sure that you sanitise the input before you build a query from strings or formulate the query so that you can pass the array via a bind variable which represents an SQL collection of objects.

user_15092640
Related