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

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
– Dawood ibn Kareem, Commented Apr 8, 2014 at 10:57
I would rather not store the keys and values of the map separately. — t0tec
– t0tec, Commented Apr 8, 2014 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
– Dawood ibn Kareem, Commented Apr 8, 2014 at 11:46

VH-NZZ · Accepted Answer · 2014-04-08 15:16:06Z

1

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

edited Apr 8, 2014 at 15:16

answered Apr 8, 2014 at 15:09

VH-NZZ

5,4684 gold badges35 silver badges47 bronze badges

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

3 Comments

VH-NZZ Over a year ago

@T0tec: see my point? Also, for the other solutions, have a look at, eg. stackoverflow.com/questions/5776331/…

t0tec Over a year ago

Yes, I know it's not a good way as described but I'll take your answer.

VH-NZZ Over a year ago

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

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related