Announcement Announcement Module
No announcement yet.
The best practice for mysql and "upsert" with Spring ? Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • The best practice for mysql and "upsert" with Spring ?

    I would need to update an existing row in a table in MySQL database, or create it if it is not existing.
    I am using com.mysql.jdbc.Driver.
    What is Spring way to do that, with preferably with PreparedStatement?
    I am trying to do something like this, bar1 is the key:

    String SQL_CMND= 
    "INSERT INTO foo(bar1, bar2, bar3) values (?, ?, ?)" +
    "VALUES (1, 2, 3)" +
    "ON DUPLICATE KEY UPDATE  bar1= ?, bar2=?, bar3=?";
    jdbcTemplate.update(SQL_CMND, new PreparedStatementSetter() {
        public void setValues(PreparedStatement ps) throws SQLException {	
        ps.setInt(1, 1); 
        ps.setInt(2, 2);
        ps.setInt(3, 2);
    Last edited by bartpved; Sep 18th, 2012, 01:52 AM.

  • #2
    For starters you have 6 placeholders not 3 and you don't need a PreparedStatementSetter simply pass the objects in using the varargs update method ... Basically it should simply work (you could try and use the NamedParameterJdbcTemplate that way you can use named parameters instead of ? which can make life a little easier when duplicated parameters are needed).


    • #3
      Thanks, I think NamedParameterJdbcTemplate is exactly what I need.