Query results differ between MySQL Workbench and in Java (through JDBC)

Question

I am using a MySQL database, a Java application and JDBC for communication.

The goal here is to select "Recipes" that match some criteria, which is achieved by not selecting (removing) certain recipes based on ID's

I am not especially strong in SQL, but the code below works just as intended when used in MySQL Workbench:

SELECT id FROM recipes WHERE(global_access = 1 OR owner = 1)
AND id NOT IN (1,2,3) 
AND id NOT IN ( 
# Remove for brevity. Returns ID's to be excluded. 
) );

Specifically, the part:

AND NOT IN (1,2,3)

should ensure that no recipes with ID 1,2 or 3 is returned. It coming as a String from Java in a PreparedStatement.

String sql = "SELECT id FROM recipes WHERE(global_access = 1 OR owner = ?) 
AND id NOT IN ( ? ) " .... (rest removed)

When I run this SQL in MySQL workbench, it correctly filters out the recipes (1,2,3). However, when I run it from Java, it does not.

I have checked that the parameter passed is indeed 1,2,3.

It does not give any errors, but seems to ignore the "AND NOT IN (1,2,3)" part completely.

Any idea how this can happen?

No, it is "setString" and the string consists of the ID's. I guess I know why now. The setString will automacally add ' , because it is a string? — Martin Dreymann, Aug 30 '18 at 12:42
yup, you need to set it as list/array. with string it would be treated as single value "1,2,3". — Ankur, Aug 30 '18 at 12:46
Don't add things like solved to the title. As the linked duplicate already provides solutions, it is also not necessary to edit your solution in. If your question hadn't been closed, the proper way would have been to post your own answer, not to edit the solution into your question. — Mark Rotteveel, Aug 30 '18 at 13:10

Joop Eggen · Answer 1 · 2018-08-30T14:25:49.403

Not every java database driver can do it, but often one can use a java.sql.Array:

AND id NOT IN(?)

Array a = connection.createArrayOf("INT", new Object[] {1, 2, 3});

preparedStatement.setArray(..., a);

Already commented, that setString would yield '1, 2, 3'.

The Array has to know the SQL type of every element, and for general usage is passed an Object array.

@MarkRotteveel informed this does not work in MySQL.

Then that part of the SQL string must be created dynamically.

" id NOT IN(" + idsList + ")"

Where idsList either is "?, ?, ... ?" needing setInt(++columnIndex, ids[i++]) or immediately "1, 2, 3".

IIRC this doesn't work for MySQL. – Mark Rotteveel Aug 30 '18 at 13:01 — Mark Rotteveel, Aug 30 '18 at 13:01

Query results differ between MySQL Workbench and in Java (through JDBC)

1 Answers1