Spring Data Access Technologies

Every task needs a different level of abstraction from its data access layer, which makes the choice a trade-off between control and productivity.

The repetition JDBC leaves behind

Java Database Connectivity (JDBC) is the bottom layer of data access. Acquiring a connection, running SQL, mapping results, releasing resources and handling exceptions all sit right there in the code. As a result, every repository class repeats a similar try-finally skeleton.

That repetition raises the risk of omission. Miss a single resource release and the connection pool drains slowly. What the higher-level technologies hide only becomes clear once you have seen this floor.

// Using JDBC directly - the skeleton repeated in every method
Connection con = dataSource.getConnection();
PreparedStatement pstmt = con.prepareStatement(sql);
try {
    pstmt.setLong(1, id);
    ResultSet rs = pstmt.executeQuery();
    // map the result ...
} catch (SQLException e) {
    throw new MyDbException(e);
} finally {
    pstmt.close();   // this can throw SQLException as well
    con.close();
}

In the code above the actual logic is only the SQL and the mapping, yet resource release and exception translation take up more lines. As repositories multiply, this skeleton is copied verbatim.

What JdbcTemplate takes away

JdbcTemplate ships with the spring-jdbc library and is usable with no extra configuration. Using the template callback pattern, it handles connection acquisition and release, statement and result set closing, transaction synchronization, and exception translation. The developer writes the SQL, defines the parameters, and maps the result.

Control over the SQL still belongs to the developer, though. Since the SQL string and the RowMapper are written by hand, complex queries make the SQL and mapping code verbose in a hurry.

private final JdbcTemplate template;
 
public JdbcItemRepository(DataSource dataSource) {
    this.template = new JdbcTemplate(dataSource);
}
 
public Optional<Item> findById(Long id) {
    String sql = "select id, item_name, price, quantity from item where id = ?";
    try {
        Item item = template.queryForObject(sql, itemRowMapper(), id);
        return Optional.of(item);
    } catch (EmptyResultDataAccessException e) {
        return Optional.empty();
    }
}
 
private RowMapper<Item> itemRowMapper() {
    return (rs, rowNum) -> {
        Item item = new Item();
        item.setId(rs.getLong("id"));
        item.setItemName(rs.getString("item_name"));
        item.setPrice(rs.getInt("price"));
        item.setQuantity(rs.getInt("quantity"));
        return item;
    };
}

Positional ? binding silently breaks when the column order changes. Spring therefore recommends NamedParameterJdbcTemplate, which binds by name.

private final NamedParameterJdbcTemplate template;
 
public Item save(Item item) {
    String sql = "insert into item (item_name, price, quantity) " +
                 "values (:itemName, :price, :quantity)";
    SqlParameterSource param = new BeanPropertySqlParameterSource(item);
    KeyHolder keyHolder = new GeneratedKeyHolder();
    template.update(sql, param, keyHolder);
    item.setId(keyHolder.getKey().longValue());
    return item;
}

BeanPropertySqlParameterSource matches the object's property names directly to the binding names. For a plain INSERT, SimpleJdbcInsert removes the SQL string entirely.

Exception abstraction and translation

SQLException is a checked exception tied to JDBC. Letting it bubble up to the service layer binds the upper code to storage technology details. Switching later from JDBC to JPA then ripples through the upper layers because of the exception type.

Spring solves this with exception translation. A SQLExceptionTranslator converts technology-specific exceptions into the DataAccessException hierarchy. JdbcTemplate performs that conversion internally and automatically.

DataAccessException is a runtime exception, so there is no need to declare throws on every method. Switching the implementation to JPA keeps the change small, because Spring translates JPA exceptions into the same hierarchy. The trade is a dependency on Spring itself.

The abstraction does not erase the cause. Lumping every error into one common exception makes root causes hard to trace, so it is better to catch constraint violations and syntax errors separately.

Data sources and transaction boundaries

Creating a connection from scratch every time is expensive, because it involves a TCP connection and database authentication. That is why a connection pool reuses connections that were created ahead of time. Spring Boot reads properties such as spring.datasource.url and registers a HikariDataSource as the default pool.

spring:
  datasource:
    url: jdbc:h2:tcp://localhost/~/test
    username: sa
    password:

Pool size is tuned to production load. The default maximum pool size in HikariCP is 10 (per the maximumPoolSize entry in the official HikariCP documentation).

Above the connection sits the transaction boundary. Spring Boot inspects the registered technology and registers a transaction manager automatically. For JDBC-family setups it picks DataSourceTransactionManager, and for JPA it picks JpaTransactionManager. The bean is registered under the name transactionManager.

The transaction boundary usually sits in the service layer, because one business operation groups several repository calls into a unit. @Transactional is a proxy-based declarative tool that wraps a method call in a transaction.

Propagation is the rule that decides whether to join an existing transaction or start a new one. The default, REQUIRED, joins an existing transaction if there is one and starts a new one otherwise. REQUIRES_NEW is used sparingly, only where an independent commit is genuinely required, such as an audit log.

Being proxy-based brings two things to watch. Calling one of your own methods from inside the same object bypasses the proxy, so no transaction is applied (self-invocation). And if an exception is raised inside a participating inner call, catching it on the outside can still produce an UnexpectedRollbackException at commit time.

Choosing a technology

In practice the options are JDBC plus the abstractions above it: MyBatis, JPA, Spring Data JPA and Querydsl. The table below compares where each one fits and what to watch for.

The criterion narrows to the trade-off between control and productivity. When SQL needs fine-grained control, MyBatis or JdbcTemplate has the advantage. When the domain model and dirty checking are what you want to exploit, JPA has the advantage.

A common combination splits simple CRUD onto Spring Data JPA and complex dynamic queries onto Querydsl. When the read model and the write model have different characteristics, mixing technologies is a perfectly normal choice.

The last criterion is performance. For a bottleneck, reviewing SQL and indexes comes before swapping technologies. Even with an ORM, you still have to understand connections, transactions and execution plans to diagnose an incident correctly.

Summary

Spring data access technologies are a stack of abstractions that progressively hide the repetition and exception handling JDBC exposes. JdbcTemplate consolidates resource release and exception translation but leaves SQL control with the developer. Exception abstraction translates technology-specific exceptions into the DataAccessException hierarchy and loosens the coupling of the upper layers. Understanding the data source, the transaction manager and the propagation rules is what lets you catch connection exhaustion and rollback problems in time. Choosing a technology means picking the level of abstraction that matches the work, and mixing several when the work calls for it.