how to insert/update multiple rows into database without firing insert/ update query multiple times?

Question

if (!knowledge.isEmpty()) {
                Iterator<Entry<String, String>> classnmValsItrKW = knowledge
                        .entrySet().iterator();
                while (classnmValsItrKW.hasNext()) {
                    Entry<String, String> p1 = classnmValsItrKW.next();
                    String nm = p1.getKey();
                    String val = p1.getValue();
                    String query3 = "insert into namevalue(seqid, name, value) values("
                            + seqId + ",'" + nm + "','" + val + "')";

                    //System.out.println("Insert query: " + query3);
                    st = connect.createStatement();
                    st.executeUpdate(query3);
                }
            }

"knowledge" is the hashtable where i have stored name value pairs which i want to insert into database. but for every iteration of while loop insert query is getting fired. which i think is inefficient. how do i insert multiple rows by firing insert query only once?

Take a look at PreparedStatement#addBatch and Statement#executeBatch — MadProgrammer
– MadProgrammer, Commented Aug 18, 2014 at 7:09

Scary Wombat · Accepted Answer · 2014-08-18 07:09:44Z

Found this example at http://www.mkyong.com/jdbc/jdbc-preparedstatement-example-batch-update/

dbConnection.setAutoCommit(false);//commit trasaction manually

String insertTableSQL = "INSERT INTO DBUSER"
            + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
            + "(?,?,?,?)";              
PreparedStatement = dbConnection.prepareStatement(insertTableSQL);

preparedStatement.setInt(1, 101);
preparedStatement.setString(2, "mkyong101");
preparedStatement.setString(3, "system");
preparedStatement.setTimestamp(4, getCurrentTimeStamp());
preparedStatement.addBatch();

preparedStatement.setInt(1, 102);
preparedStatement.setString(2, "mkyong102");
preparedStatement.setString(3, "system");
preparedStatement.setTimestamp(4, getCurrentTimeStamp());
preparedStatement.addBatch();
preparedStatement.executeBatch();

dbConnection.commit();

Note Batch Update is not limited to Insert statement, it’s valid for Update and Delete statement as well.

bitkot · Accepted Answer · 2014-08-18 07:57:52Z

If you don't have to worry about SQL Injection means you are not getting any user data then you can try this,

if (!knowledge.isEmpty()) {
    Iterator<Entry<String, String>> classnmValsItrKW = knowledge
            .entrySet().iterator();
    StringBuilder query3 = new StringBuilder("insert into namevalue(seqid, name, value) values ");
    while (classnmValsItrKW.hasNext()) {
        Entry<String, String> p1 = classnmValsItrKW.next();
        String nm = p1.getKey();
        String val = p1.getValue();
        query3.append("("+ seqId + ",'" + nm + "','" + val + "'),");
        //System.out.println("Insert query: " + query3);
        st = connect.createStatement();
        st.executeUpdate(query3);
    }
    query3.setLength(query3.length() - 1);
}

This will generate insert query with multiple values i.e

insert into some_table(a, b) values (a1, b1), (a2, b2)...

Note : Here query3 is a StringBuilder so do query3.toString() while executing.

Ninad Pingale · Accepted Answer · 2014-08-18 07:10:02Z

0

You need to use Batch Update.

In which, multiple queries are held in memory and fired once.

here is one example -

http://viralpatel.net/blogs/batch-insert-in-java-jdbc/

answered Aug 18, 2014 at 7:10

Ninad Pingale

7,1095 gold badges36 silver badges58 bronze badges

Collectives™ on Stack Overflow

how to insert/update multiple rows into database without firing insert/ update query multiple times?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related