Storing a HashMap in an SQL database

Question

How do you store a HashMap inside of an SQL database? Also, how would you load that HashMap from a SQL database back into an instance of a HashMap?

Okay, this is what I'm doing. I have a database to store the player data for my game. It has a table containing their usernames and passwords. Each player has a HashMap that stores their properties. I need to store that HashMap in the database along with it's respective user.

The key type is String, however the value type is Object (for generic usage), but I'm pretty sure the value (even though it's type is Object) will always be able to be converted into a String for storage. — , Apr 25 '11 at 07:54

score 18 · Accepted Answer · answered Apr 25 '11 at 08:40

18

You need a 3 column table

user,

key,

value

Would then look like

"user 1", "property1", "value1"

"user 1", "property2", "value2"

"user 2", "property1", "value1"

"user 3", "property1", "value1"

"user 3", "property2", "value2"

You would then just read the data back in and look round the user field to rebuild each has table.

answered Apr 25 '11 at 08:40

pgbsl

222
1
3

I was originally thinking this would be the way I'd have to do it, but I figured there was probably a better way. – Apr 25 '11 at 08:44
I think that this is a nice, simple solution. Simple and easy to read trumps *clever* solutions every time in my opinion. Nice and easy to maintain. – pgbsl Apr 25 '11 at 09:18
I have no idea how to implement this idea into code with the SQL framework in Java. – Apr 25 '11 at 09:31
Get an entry set from the hashmap, then iterate across it. See @Jigar s comment for link to sql tutorials. There is plenty of info out there already on how to do this, that will be tailored to the database that you are using. – pgbsl Apr 25 '11 at 16:33
My problem isn't with SQL's syntax, it's with the Java SQL framework. I spent an hour last night trying to figure out how to iterate through the entry set of the HashMap and then making an UPDATE query to the database for each entry. It wasn't working no matter what I was doing. – Apr 25 '11 at 20:31
I would but I recently gave up and went back to XML. I still want to learn how to use the Java SQL framework effectively. If anyone could point me in the direction of a good tutorial covering all the basic parts of using it. – Apr 26 '11 at 06:24

score 6 · Answer 2 · answered Apr 25 '11 at 07:52

If you want to stick key-value you can, however, you've to make sure the keys and values are strings and can fit in the column definition. Here is an example:

mysql> create table hashmap (k varchar(200), v varchar(200));
Query OK, 0 rows affected (0.24 sec)

mysql> insert into hashmap values ('key','value');
Query OK, 1 row affected (0.02 sec)

mysql> select * from hashmap;
+------+-------+
| k    | v     |
+------+-------+
| key  | value |
+------+-------+
1 row in set (0.00 sec)

Additionally, you can unique index the column k which holds the keys so your lookups are constant just like a hashmap, and you can replace the old value by using ON DUPLICATE KEY UPDATE v = new_value.. I am assuming you're using MySQL for the ON DUPLICATE KEY part.

How is it not related to your question? You wanted to stick the data inside a hashmap in a table, and the schema I outlined does just that, except that key and value are strings. Can you add more details and possibly give an example, as from the comments its fairly obvious that no one really understands what you want answered. — lobster1234, Apr 25 '11 at 07:59

jmj · Answer 3 · 2011-04-25T08:18:32.140

2

HashMap is data structure, You can store data residing in it.

Create a table emulating key value pair. iterate through keys of map and store it in DB.

edited Apr 25 '11 at 08:18

answered Apr 25 '11 at 07:47

jmj

237,923
42
401
438

2

@Gnarly - His point is that SQL databases are about storing data, not data structures. – Oded Apr 25 '11 at 07:48
Yes, but I want to store the data inside of the HashMap inside of the database. – Apr 25 '11 at 07:51
You just need to execute insert query using JDBC – jmj Apr 25 '11 at 08:33
I don't know anything about SQL databases, thus why I am asking how to on SO. – Apr 25 '11 at 08:43
1

quick googling turned into [this](http://beginner-sql-tutorial.com/sql-insert-statement.htm) – jmj Apr 25 '11 at 08:45

score 2 · Answer 4 · answered Apr 25 '11 at 07:53

Each entry in the HashMap is like a row in your table. You would end up having a separate table for each HashMap (probably).

Your K parameter in your map should be enforced as a unique key on your table, and indexed.

Keep in mind that storing these objects may require more than one column each. For example, you may have a Point object as your key, so you'd need a column x and column y, and an index on uniqueness for x and y pairs.

Storing a HashMap in an SQL database

4 Answers4

Linked