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: docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html — JB Nizet
– JB Nizet, Commented Nov 22, 2013 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
– RazorHead, Commented Nov 22, 2013 at 17:31
I will consider using jOOQ or SpringJDBC for this purpose. It is slightly convoluted. — KKKCoder
– KKKCoder, Commented Nov 22, 2013 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
– JB Nizet, Commented Nov 22, 2013 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
– RazorHead, Commented Nov 22, 2013 at 17:34

BalusC · Accepted Answer · 2013-11-22 18:01:30Z

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.

Collectives™ on Stack Overflow

Insert a HashMap into any database table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related