Inserting and reading a string array in Java

Question

I'm using prepared statements to insert an array into my database

PreparedStatement pstmt = conn.prepareStatement("INSERT INTO TABLE(stringArray) VALUES (?)");
String[] arr = { "a", " b", "c" };
pstmt.setObject(1, arr);

I'm using setObject following an example, but setArray won't resolve and setBlob doesnt work either.

Now when i come to read it, I'm not sure what to do with my object to get the array values back!

PreparedStatement pstmt = conn.prepareStatement("SELECT stringArray FROM TABLE WHERE id = 1");
ResultSet rs = pstmt.executeQuery();
rs.next();
Object object = rs.getObject(1);
System.out.println(object.toString());

Just outputs

[B@45d0e784

I know i'm doing object.toString and i want an array, but how would i do that? do i need to set it as a byte or something when inserting?

Thanks for your help

This is not a good idea. Every element should get its own column in a relational database. — Gilbert Le Blanc, Jan 28 '13 at 18:20
im only storing the array temporarily so that i can get it back out and play with it again in java later on — FredoAF, Jan 29 '13 at 10:42

score 2 · Answer 1 · edited Jan 29 '13 at 19:16

2

PreparedStatement#setArray method takes java.sql.Array as a argument. Try -

PreparedStatement pstmt = conn.prepareStatement("INSERT INTO TABLE(stringArray) VALUES (?)");
String[] arr = { "a", " b", "c" };
Array a = conn.createArrayOf("marksArray", arr);
pstmt.setArray(1, arr);

Note that -

The JDBC driver is responsible for mapping the elements Object array to the default JDBC SQL type defined in java.sql.Types for the given class of Object. The default mapping is specified in Appendix B of the JDBC specification. If the resulting JDBC type is not the appropriate type for the given typeName then it is implementation defined whether an SQLException is thrown or the driver supports the resulting conversion.

edited Jan 29 '13 at 19:16

Bohemian

412,405
93
575
722

answered Jan 28 '13 at 18:09

Subhrajyoti Majumder

40,646
13
77
103

I think stringArray is single place holder and id is another field of that table and it is right that insert statement might not be 100% right. – Subhrajyoti Majumder Jan 29 '13 at 19:04
+1 Sorry - didn't read the question properly - you code should work :) – Bohemian Jan 29 '13 at 19:16

score 0 · Answer 2 · answered Jan 29 '13 at 17:49

I couldn't get the setArray function to work, it came up with an error each time. I therefore decided to implement my own serialization.

I used a for loop to create a string that contained my with comma separation. Then when I retrieve the line from the database I split by comma and my array is re-formed.

I guess that the serialization used in prepared statements is a lot more useful for user-defined objects, but for my string array it wasn't worth the bother.

Inserting and reading a string array in Java

2 Answers2