Java Oracle: Storing a TreeMap as BLOB?

Question

I have the following code:

  public void StoreMapInDB(TreeMap<DateTime, Integer> map) throws
        IOException, FileNotFoundException{
    try {
  PreparedStatement insertMap = null;
  String insertString = "INSERT INTO TESTMAP(ID, NAME) VALUES (1, ?)";
  Connection con=null;
  con.setAutoCommit(false);
  Class.forName("oracle.jdbc.driver.OracleDriver");
  con=DriverManager.getConnection(
    "jdbc:oracle:thin:@XXXXX",
    "XXX",
    "XXX");
    //This line is incorrect for sure 
    //insertMap.setBlob(1, map.);
    } catch(Exception e){e.printStackTrace();}
}

The connection works and all with database. This time i am trying to insert the map i.e. the treemap i created into a column in the table with type BLOB. How can I do that? is there any other better datatypes that I should look into?

Thanks,

WeMakeSoftware · Answer 1 · 2011-12-06T16:15:56.727

5

If you want to put your object into BLOB datatype, You could do something like this:

// Serialize to a byte array
ByteArrayOutputStream bos = new ByteArrayOutputStream() ;
out = new ObjectOutputStream(bos) ;
out.writeObject(object);
out.close();

// Get the bytes of the serialized object
byte[] buf = bos.toByteArray();
PreparedStatement prepareStatement = connection.prepareStatement("insert into tableName values(?,?)");
prepareStatement.setLong(1, id);
prepareStatement.setBinaryStream(2, new ByteArrayInputStream(buf), buf.length);

edited Dec 06 '11 at 16:15

answered Dec 06 '11 at 13:08

WeMakeSoftware

9,039
5
34
52

hmmm...could u elaborate your last line that is "use buf to set Blob datatype? I tried something but doubt it worked (not tested yet) that is Blob var = (Blob)map; – sys_debug Dec 06 '11 at 15:12
oh just tried it, but the database didn't get any update in the fields. there are no errors but nothing is inserted. Any idea? – sys_debug Dec 07 '11 at 08:59
did you commit the transaction or is autocommit turned on? If it were a database problem, most likely you would get an Exception back. – WeMakeSoftware Dec 07 '11 at 10:04
yeh i do the following con.commit(); so it should because I set autocommit false at the beginning – sys_debug Dec 07 '11 at 10:08
do you call prepareStatement.execute()? – WeMakeSoftware Dec 07 '11 at 10:11
now i did so it works :) thanks so much for the help! it is greatly appreciated. Anywhere you could direct me to read about converting the blob object i stored as byte stream into back (vice versa of what I did here)? – sys_debug Dec 07 '11 at 11:40
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/5658/discussion-between-funtik-and-sys-debug) – WeMakeSoftware Dec 07 '11 at 11:53
will do :) away from office now. – sys_debug Dec 08 '11 at 06:54

score 1 · Answer 2 · edited May 23 '17 at 11:53

There are two ways you can go about this...

You can take advantage of the fact that the collections are serializable and output them to a byte array, as shown in the answer to this question.
You could change the type of your table so that instead of using a binary field to store a specific implementation of a TreeMap, you store the keys and values as individual rows that can then be extracted and used to rebuild the map later. This option is more future-proof because you won't rely on TreeMap and its serialization format forever. Instead of using the columns (id, data), you would use (id, key, value). To save to the DB, you iterate over the Map's entrySet() and insert each key/value pair using the same row ID.

Thanks mate but there will be other issues to tackle if I decide to do it your way. the thing is the value and key accessed from map are critical to some methods. So Maybe I just keep it this way and serialize :) thanks though for suggesting the reference post :) — sys_debug, Dec 06 '11 at 15:10

Java Oracle: Storing a TreeMap as BLOB?

2 Answers2