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

Have Questions? Post them here!