Store String Array in MySql through java?

Question

I have a String Array in a java program. I am using jdbc driver to store data in MySql.

There is a reference variable "pstmt" that refers to a prepared statement

now I want to call the following method:

String [] items = {pen, ball, speaker, mic};  
pstmt.setArray(1, ....);

where "items" refer to String array but the method setArray() takes java.sql.Array instead of String array.

So how can I convert the string array "items" to a java.sql.Array so that I can pass an argument in the above method call.

Answer 1

As far as I know, setArray(int i, Array x) and java.sql.Array which is an interface for retrieving and materializing an SQL3 ARRAY data type can't be used in the fashion you want to use it.

By the way, implementing Array is optional according to the JDBC specification and I don't think MySQL's driver provide an implementation.

Answer 2

Generally when you try to store an array in a RDBM it's an indication that you need further normalization of your data model. I.e. you could set up another table with a foreign-key relationship to your current table and then store each element of the array in the new table.

Answer 3

You do it like this,

String [] items = {pen, ball, speaker, mic};  
pstmt.setArray(1, pstmt.getConnection().createArrayOf("String", items));