Spring Boot JDBCTemplate Upsert Example (batch insert or update if exists)


JdbcTemplate using Spring Boot provides update(String sql, Object... args) method which supports upsert (nsert or update if exists) operation. You can use this method to perform an upsert operation in the database.

Code Snippet:
String sql = "INSERT INTO employee (employeeId, employeeName, employeeDateOfBirth, employeeDepartment, employeeJoiningDate, employeeSalary) " +
            "VALUES (:id, :name, :dob, :department, :joiningDate, :salary) " +
            "ON DUPLICATE KEY UPDATE " +
            "employeeName=VALUES(employeeName), " +
            "employeeDateOfBirth=VALUES(employeeDateOfBirth), " +
            "employeeDepartment=VALUES(employeeDepartment), " +
            "employeeJoiningDate=VALUES(employeeJoiningDate), " +
            "employeeSalary=VALUES(employeeSalary)";

MapSqlParameterSource[] batchParams = employees.stream()
            .map(employee -> new MapSqlParameterSource()
                    .addValue("id", employee.getEmployeeId())
                    .addValue("name", employee.getEmployeeName())
                    .addValue("dob", Date.valueOf(employee.getEmployeeDateOfBirth()))
                    .addValue("department", employee.getEmployeeDepartment())
                    .addValue("joiningDate", Date.valueOf(employee.getEmployeeJoiningDate()))
                    .addValue("salary", employee.getEmployeeSalary()))
            .toArray(MapSqlParameterSource[]::new);

namedParameterJdbcTemplate.batchUpdate(sql, batchParams);

Related Examples:

  1. Spring Boot: NamedParameterJdbcTemplate batch insert example
  2. JdbcTemplate Batch Insert Example using Spring Boot
  3. Spring Boot: JDBCTemplate BatchUpdate Update Query Example
  4. Spring Boot: JdbcTemplate Update Query With Parameters Example
  5. JDBCTemplate Querying Examples with Spring Boot 3

Facing issues? Have Questions? Post them here! I am happy to answer!







Author Info:

Rakesh (He/Him) has a Masters Degree in Computer Science with over 15+ years of experience in Web and Application development. He is the author of insightful How-To articles for Code2care.

Follow him on: X

You can also reach out to him via e-mail: rakesh@code2care.org

Copyright Code2care© 2024 | Privacy Policy | About Us | Contact Us | Search