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
– Gilbert Le Blanc, Commented Jan 28, 2013 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
– FredoAF, Commented Jan 29, 2013 at 10:42

Bohemian · Accepted Answer · 2013-01-29 19:16:00Z

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, 2013 at 19:16

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

answered Jan 28, 2013 at 18:09

Subhrajyoti Majumder

41.3k13 gold badges84 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Subhrajyoti Majumder Over a year ago

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.

Bohemian Over a year ago

+1 Sorry - didn't read the question properly - you code should work :)

FredoAF · Accepted Answer · 2013-01-29 17:49:32Z

0

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.

answered Jan 29, 2013 at 17:49

FredoAF

5042 gold badges9 silver badges22 bronze badges

Collectives™ on Stack Overflow

Inserting and reading a string array in Java

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related