Serializing a java Map to a db

Question

i'm using jdbc to work with my db. I want to store a HashMap directly in the db, oracle. i figure i need to serialize the Map, and its contents, which are only String. Map already implements Serializeable, but my question is how do i put the Map in the SQL using jdbc? for instance, i have a jdbcTemplate.update("insert into ....", new Object[]{....}, new int[]{....}) do i just put the Map in the Object array? Thanks

Answer 1

The way I have done it in the past is to create one column in a table to store your serialized objects. The datatype should be "blob" or equivalent in Oracle.

It can then be written/retrieved using ResultSet.setBytes() and ResultSet.getBytes()

Answer 2

You need a table with key/value columns, and if you're storing multiple maps, an identity column (or a foreign key to another table holding data that the hashmap is a member of).

Then in JDBC, create a prepared statement (insert into myhashmap (id, foreign_id, key, val) values (?, ?, ?, ?)) ONCE, and then loop over every element in the hashmap, setting the parameters on the statement and calling execute on the query.

(sorry, not near any code to post code snippets and don't want to type in buggy examples).

This is also trivially extendible to maps of String -> Object, where you wish to store every field in Object in the DB. I.e., it's basically like a standard table in a database, and a 'key' column. Lists are similar, but with a 'rank' column to indicate ordering.