2

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!

IHaveAQuestion
  • 143
  • 2
  • 12
  • Oh I know how to use jdbc i meant i’m struggling building the actual select statement using the List – IHaveAQuestion May 02 '21 at 17:47
  • So what did you try? The SQL statement is a string. So your first attempt should create such a string out of your list values. This should be easy enough. The challenge is to make it all safe to prevent you from SQL injection. If you don't want to use something heavy weight such Hibernate you could use [MyBatis](https://mybatis.org/mybatis-3/dynamic-sql.html). Look at the foreach section. – vanje May 02 '21 at 17:53
  • @abra Yes there is an existing sql statement i posted above but i can’t figure out a way to build that in java – IHaveAQuestion May 02 '21 at 17:55

1 Answers1

1

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.

MT0
  • 143,790
  • 11
  • 59
  • 117
  • Could you explain what your Note: means in a more simpler term? – stackerstack May 02 '21 at 20:07
  • @stackerstack When the user tells you that they want a model of car named `"camry')) OR EXISTS( select 1 FROM passwords WHERE username = 'Admin' AND password_hash = 'A34F64DC') OR (year, make, model) IN ("` then you need to sanitise your input data or you are going to have issues. The second half of the note is an [alternate method of solving the problem](https://stackoverflow.com/a/54347047/1509264) by directly passing a Java array of classes to an Oracle collection of objects. – MT0 May 02 '21 at 20:16
  • And what's the point of the boolean first? – stackerstack May 02 '21 at 20:38
  • @stackerstack It tells you whether you are processing the first element of the list or not. – MT0 May 02 '21 at 20:39
  • So for your select statement compared to the original posters, I can see how his returns back multiple different rows. If you were to implement an update statement to multiple entries just like how you selected multiple entries how would it look like? – stackerstack May 03 '21 at 13:47
  • @stackerstack If you have a question that is beyond the scope of this question then please [ask a new question](https://stackoverflow.com/questions/ask) and make sure you include a [MRE]. Your question is impossible to answer in comments as to update something you need to update it to something and you have not provided nearly enough information on: the table structure; your sample data; what rows you want to update; or how you want to update them. – MT0 May 03 '21 at 14:55
  • So I'm looking again, and I still don't get the purpose of the boolean first. In what way would that tell you if it's the first element or not? To me, you are just manually setting it to true and then writing an if statement to check its trueness, in which case it'll always be true since theres nothing ever modifying it before the if statement – stackerstack May 06 '21 at 13:23
  • @stackerstack It will only be true on the first iteration; after that it will be false. Just add a `System.out.println( first );` in at the start of the for loop (before the `if` statement) and see what happens. – MT0 May 06 '21 at 13:46