Error with mysql JDBC driver for method createArrayOf

Question

I'm a begginer java programmer. In my dummy projects I'm using the mysql JDBC 5.17 driver for connect to my database

Today I have a simply query

SELECT * FROM books WHERE idb IN (?)

I have this Array of int:

int[2] idBooks = new int{1,2};

and I would to select the books with the id in idBooks

When I use the API "createArrayOf" the JVM throws on the console this error:

  java.sql.SQLFeatureNotSupportedException

I know that my driver not support the "createArrayOf" method, there are other solution for do that?

I hope my question is not trivial :)

Thanks a lot in advance, sorry for my english Thanks to all!!

score 2 · Answer 1 · answered Feb 27 '14 at 12:25

One possible solution:

Java:

// str_idBooks will be [1, 2]
String str_idBooks = Arrays.toString( idBooks );

You have to replace '[', ' '(spaces), ']' from the string to pass to MySQL query.

MySQL:

If you did not replace the characters as said above, use this statement:

SELECT * FROM books
 WHERE FIND_IN_SET( idb, 
                    replace(replace(replace(?,'[',''),']',''),' ','')
                  ) > 0

If you have replaced the characters as said above, use this statement:

SELECT * FROM books
 WHERE FIND_IN_SET( idb, ? ) > 0

Use the Prepared Statement to set the parameter as :

pst.setString( 1, str_idBooks );

score 1 · Answer 2 · edited May 23 '17 at 12:20

1

It seems that MySQL doesn't have array variables.May U can try temporary tables instead of array variables

CREATE TEMPORARY TABLE IF NOT EXISTS my_temp_table
SELECT * FROM people WHERE last_name = 'John';

Look this.

edited May 23 '17 at 12:20

Community

answered Feb 27 '14 at 11:26

Ami

How will be my query in my case with some ids? – Andrea Catania Feb 27 '14 at 11:59
If you are using id you try 'IN'. Refer this you should get some idea.http://stackoverflow.com/questions/9476146/mysql-php-select-where-id-array – Ami Feb 27 '14 at 12:22
@AndreaCatania here is an example using temp table for keeping ids to other queries. https://stackoverflow.com/a/71799005/1864883 – wviana Apr 08 '22 at 14:49

2 Answers2