Java SQL what is the best way to save class with HashMap attribute in MySQL database

Question

I don't know what's the best way to save a HashMap in the database (the HashMap can be very big) so I my first idea is to save the HashMap as a blob (in a column), an other idea was to save every value in the HashMap corresponding to the key but this would make it to large I thought.

My example class:

public class MyObject {
    private int id;
    private HashMap<Integer, CustomHash> hashes;
    // getters and setters
}

I also heard of serializing the class and save it in the database. Any suggestions?

Also no use of ORM only plain JDBC

EDIT: how to save hashmap into clob?

CREATE TABLE IF NOT EXISTS myobjects (
   id int GENERATED BY DEFAULT AS IDENTITY(START WITH 1) PRIMARY KEY,
   hashes clob NOT NULL
);

My statement:

PreparedStatement ps = null;
try {
    ps = this.connection.prepareStatement("INSERT INTO myobjects (hashes) VALUES (?)");
    ps.setClob(1, (Clob) myObject.getHashes());
    ps.executeUpdate();
} catch(SQLException sqlEx) {
    sqlEx.printStackTrace();
} finally {
    DAOUtility.close(this.connection, ps);
}

The error: HashMap cannot be cast to Clob

yup - http://stackoverflow.com/questions/3393649/storing-a-mapstring-string-using-jpa — Leo, Apr 08 '14 at 10:57
You want a table with three columns - an integer for the `id` field, an integer for the maps' keys and a varchar (or possibly some kind of CLOB/text column) for the maps' values. — Dawood ibn Kareem, Apr 08 '14 at 10:57
I would rather not store the keys and values of the map separately. — t0tec, Apr 08 '14 at 11:03
Putting them in different columns in the same row of a database table is hardly "separately". Doing anything else just means you'll have to parse them apart somehow later. — Dawood ibn Kareem, Apr 08 '14 at 11:46
@T0tec Do you need to merely persist the map or do you want to 'map' the map to a rdbms? If you're happy with your map stored in a blob, just serialize it. Otherwise David Wallace's suggestion is perfectly fine. — VH-NZZ, Apr 08 '14 at 11:49
So it's not possible to store the HashMap directly to one column in the database? Can I use JSON to serialize the HashMap into the blob column and deserialize it back to the HashMap? — t0tec, Apr 08 '14 at 11:51
@T0tec yes, it is. You suggested one option (though with JSON you may want to store it as clob and not blob). — VH-NZZ, Apr 08 '14 at 11:54
Sigh, I'm kinda lost, I'll just try David Wallace's method, so save it in a separate table? — t0tec, Apr 08 '14 at 12:37

VH-NZZ · Accepted Answer · 2014-04-08T15:16:06.680

Disclaimer: this is not the best way, but one way among many to save an instance of a Map in a rdbms.

CLOB is for large character objects (up to 2 or 4 GB usually). I suggested it above because you mentioned using JSON to serialize your map. Okay, here's a straightforward possibility (using a BLOB):

CREATE TABLE IF NOT EXISTS myobjects (
   id int GENERATED BY DEFAULT AS IDENTITY(START WITH 1) PRIMARY KEY,
   hashes blob(32K) NOT NULL
);

and using your snippet for insertion:

PreparedStatement ps = null;
try {
    // serialize your map
    ByteArrayOutputStream b = new ByteArrayOutputStream();
    ObjectOutputStream output = new ObjectOutputStream(b);
    o.writeObject(myObject().getHashes());

    ps = this.connection.prepareStatement("INSERT INTO myobjects (hashes) VALUES (?)");
    ps.setBinaryStream(1, new ByteArrayInputStream(b.toByteArray()));
    ps.executeUpdate();
} catch(IOException ioEx) {
    ioEx.printStackTrace();
} catch(SQLException sqlEx) {
    sqlEx.printStackTrace();
} finally {
    DAOUtility.close(this.connection, ps);
}

Note that your values will need to be serializable too.

This would merely allow you to use your persisted map on the JVM though. If you intend to access the map directly in the database, I'd strongly suggest 'mapping the map' to three columns (id, key, value). That would be more transparent but would require more work (ie. more SQL queries) to store/retrieve the map. HTH

@T0tec: see my point? Also, for the other solutions, have a look at, eg. http://stackoverflow.com/questions/5776331/storing-a-hashmap-in-an-sql-database?rq=1 — VH-NZZ, Apr 08 '14 at 20:04
Yes, I know it's not a good way as described but I'll take your answer. — t0tec, Apr 09 '14 at 09:43
Also, do note that there are other libraries out there that will to a String instead of a byte array. That may be useful for a number of reasons. Also, if you use Java's straightforward serialization, make sure that your `CustomHash` class defines a `private static final long serialVersionUID = some random long number;` Otherwise you'll have trouble deserializing if your class definition changes even slightly. Do refer to chap. 11 of *Effective Java* by J. Bloch for good practices. HTH — VH-NZZ, Apr 09 '14 at 12:39

Java SQL what is the best way to save class with HashMap attribute in MySQL database

1 Answers1