CODEWITHSUNDEEP
javamysqlspringjdbctemplate
Explain Down Vote
Explain Down Vote

Reputation: 161

how to overwrite using spring jdbctemplate batchupdate while inserting records?

Need to insert records using spring jdbctemplate batch update. while inserting if duplicate record is found, the record needs to be updated else inserted. how do i do that? Below is my code.

Note:have not included exception handling.

 result = jdbcTemplate.batchUpdate(
            "insert ignore xxx set yy = ?, zz = ? where aa = ?",
            new BatchPreparedStatementSetter() {
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    ps.setDouble(1, Double.parseDouble(new JSONObject(jsonArray.get(i).toString()).get("aa").toString()));
                    ps.setDouble(2, Double.parseDouble(new JSONObject(jsonArray.get(i).toString()).get("bb").toString()));
                    ps.setString(3, new JSONObject(jsonArray.get(i).toString()).get("cc").toString());
                }

                public int getBatchSize() {
                    return jsonArray.length();
                }
            } );

    }

Upvotes: 1

Views: 3405

Answers (2)

HaMi
HaMi

Reputation: 539

You could use one of the following choices:

  1. as @fbokovikov said you can use merge sql command.
  2. you can check if the record is exist or not. In case of performance you can first get all the keys from db and then generate correct insert and update queries using those keys. In this scenario you should be aware of poor performance if you have big data in your table.
  3. you can delete records first and then insert them all. This is very bad in performance. :-)

Hope that helps

Upvotes: 1

yanefedor
yanefedor

Reputation: 2262

Need to insert records using spring jdbctemplate batch update. while inserting if duplicate record is found, the record needs to be updated else inserted

For this purpose you should use standard SQL-2003 command merge.

In MySQL this command has following syntax:

INSERT...ON DUPLICATE KEY UPDATE

Upvotes: 0

Related Questions