Skip to main content
Advertisement

9.3 JdbcTemplate and High-Volume Batch Processing

Even in the modern age dominated by ORMs like Spring Data JPA, JdbcTemplate remains overwhelmingly superior and remarkably resilient when deliberately orchestrating raw RDBMS queries or pumping millions of mass batch insertions per second.

1. Fundamentals of JdbcTemplate​

JdbcTemplate is essentially a graceful Spring utility class heavily capitalizing on the Template Callback Design Pattern. It actively absorbs all the excruciating JDBC boilerplates such as aggressively acquiring network Connections, building PreparedStatements, and meticulously releasing resources securely within try-catch-finally blocks.

@Repository
@RequiredArgsConstructor
public class UserRepositoryJdbc {

    private final JdbcTemplate jdbcTemplate;

    // 1. Querying a Single Record (QueryForObject)
    public User findById(Long id) {
        String sql = "SELECT * FROM users WHERE id = ?";
        return jdbcTemplate.queryForObject(sql, userRowMapper(), id);
    }

    // 2. Querying Multiple Records (Query)
    public List<User> findAll() {
        String sql = "SELECT * FROM users";
        return jdbcTemplate.query(sql, userRowMapper());
    }

    // 3. Mutating DML via Insert/Update/Delete (Update)
    public int updateName(Long id, String newName) {
        String sql = "UPDATE users SET name = ? WHERE id = ?";
        return jdbcTemplate.update(sql, newName, id);
    }

    // Custom RowMapper explicitly translating DB result sets to Java Objects
    private RowMapper<User> userRowMapper() {
        return (rs, rowNum) -> {
            User user = new User();
            user.setId(rs.getLong("id"));
            user.setName(rs.getString("name"));
            return user;
        };
    }
}

2. Supersonic Bulk Operations leveraging batchUpdate​

Sequentially streaming tens of thousands of parsed Excel rows directly to a database via JPA Persistence Contexts sequentially generates monumental catastrophic bottlenecks (triggering aggressive Slowdowns or deadly Out of Memory errors). Comparatively, executing JdbcTemplate's batchUpdate mechanism natively unlocks raw DB infrastructure Bulk operators culminating securely in 100x performance margins.

@Repository
@RequiredArgsConstructor
public class BulkUserRepository {

    private final JdbcTemplate jdbcTemplate;

    @Transactional
    public void saveAllBatch(List<User> userList) {
        String sql = "INSERT INTO users (name, age) VALUES (?, ?)";

        // Grouping payloads into strict chunks of 1000 items sequentially emitting to the DB
        jdbcTemplate.batchUpdate(sql, userList, 1000,
            (PreparedStatement ps, User user) -> {
                ps.setString(1, user.getName());
                ps.setInt(2, user.getAge());
            }
        );
    }
}
tip

When handling colossal pipelines utilizing MySQL/MariaDB infrastructures, you MUST append rewriteBatchedStatements=true manually onto your connection URL (or inside application.yml). If this driver parameter is omitted, your batchUpdate mechanism silently decays backwards, humiliatingly communicating linearly over the network exactly 1 record at a time.

Advertisement
Advertisement