Insert a HashMap into any database table

Question

I have a HashMap with the following definition:

  myMap = new HashMap<String,String>();

this map consists of the field names as keys and field values as of course values. I am trying to make a method that takes the HashMap and a table name as arguments. My query has to have the following format because I do not insert to all the columns in my table:

INSERT INTO $tableName (?,?,?,?)
VALUES (?,?,?,?)

The number of columns of course depends on the size of the HashMap. How can I achieve this through iterating through the HashMap. Here is what I have come up so far using a different approach but I do not think it will work properly:

public void insertData(HashMap<String, String> dataMap, String tableName) {

    int size=dataMap.size();
    String sql = "INSERT INTO " + tableName;
    Iterator<Entry<String, String>> it = dataMap.entrySet().iterator();
    int counter = 1;
    while (it.hasNext()) {
        Map.Entry pairs = (Map.Entry)it.next();
        sql += pairs.getKey()+"="+pairs.getValue();
        if(size > counter )
            sql += ", ";
        counter++;
    }
    sql += ";";
}

Learn about prepared statements: http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html — JB Nizet, Nov 22 '13 at 17:29
I do know about them, I just don't know how to implement them in this case. Because the number of columns to be inserted differs for different tables. — RazorHead, Nov 22 '13 at 17:31
I will consider using jOOQ or SpringJDBC for this purpose. It is slightly convoluted. — KKKCoder, Nov 22 '13 at 17:31
You'll have to generate a query like `insert into tablename (col1, col2) values (?, ?)`. col1 and col2 are the keys of the map. Then you'll iterate through the values (in the same order), and call setString() on the prepared statement for each value. — JB Nizet, Nov 22 '13 at 17:33
Unfortunately using any of those is not an option for this project. I guess my question is less about java and more about the algorithm needed to be used in order to solve this issue — RazorHead, Nov 22 '13 at 17:34
I would have to believe that there is some Java DB API somewhere that handles this, since it's a common and natural way to represent a row in the DB. In terms of "roll your own", it's a fairly simple matter to construct the insert/update statement based on the Map, if you're sure the Map is consistent with the DB. — Hot Licks, Nov 22 '13 at 17:57

score 12 · Accepted Answer · answered Nov 22 '13 at 18:01

You'd need to generate the prepared statement SQL string with column names and placeholders yourself. Here's a kickoff example:

StringBuilder sql = new StringBuilder("INSERT INTO ").append(tableName).append(" (");
StringBuilder placeholders = new StringBuilder();

for (Iterator<String> iter = dataMap.keySet().iterator(); iter.hasNext();) {
    sql.append(iter.next());
    placeholders.append("?");

    if (iter.hasNext()) {
        sql.append(",");
        placeholders.append(",");
    }
}

sql.append(") VALUES (").append(placeholders).append(")");
preparedStatement = connection.prepareStatement(sql.toString());
int i = 0;

for (String value : dataMap.values()) {
    preparedStatement.setObject(i++, value);
}

int affectedRows = prepatedStatement.executeUpdate();
// ...

This has the additional advantage that you can use Map<String, Object> where Object can also be a Number (Integer, Long, etc), Date, byte[], and so on, at least those types for which the PreparedStatement already has setter methods.

Keep in mind that you've a serious SQL injection attack hole if the tableName and map keys are controlled by the enduser.

Insert a HashMap into any database table

1 Answers1

Linked