Utilizing MappingSqlQuery for Object Mapping

While basic JDBC templates often return results as generic maps, the MappingSqlQuery class provides a more structured way to convert database rows directly into Java objects. By extending this abstract class and implementing the mapRow method, you avoid manual iteration over result sets.

package com.example.dao;

import com.example.model.Account;
import org.springframework.jdbc.object.MappingSqlQuery;
import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;

public class AccountQuery extends MappingSqlQuery<Account> {

    public AccountQuery(DataSource ds, String sql) {
        super(ds, sql);
    }

    @Override
    protected Account mapRow(ResultSet rs, int rowNum) throws SQLException {
        Account account = new Account();
        account.setAccountId(rs.getInt("id"));
        account.setOwnerName(rs.getString("name"));
        account.setBalance(rs.getDouble("balance"));
        return account;
    }
}

To use this in a DAO implemantation, you initialize the query object, define parameters, and execute it:

public List<Account> findAccountsByName(String name) {
    String sql = "SELECT id, name, balance FROM accounts WHERE name = ?";
    AccountQuery query = new AccountQuery(getDataSource(), sql);
    query.declareParameter(new SqlParameter(java.sql.Types.VARCHAR));
    query.compile();
    return query.execute(new Object[]{name});
}

Updating Records with SqlUpdate

The SqlUpdate class encapsulates reusable update operations. Its particularly useful when you have fixed SQL statements that need to be executed multiple times with different parameters.

package com.example.dao;

import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.SqlUpdate;
import javax.sql.DataSource;
import java.sql.Types;

public class BalanceUpdater extends SqlUpdate {

    public BalanceUpdater(DataSource ds) {
        setDataSource(ds);
        setSql("UPDATE accounts SET balance = balance + ? WHERE id = ?");
        declareParameter(new SqlParameter(Types.DOUBLE));
        declareParameter(new SqlParameter(Types.INTEGER));
        compile();
    }

    public int modifyBalance(int id, double amount) {
        return update(new Object[]{amount, id});
    }
}

In your Spring configuration, you would wire the DataSource into this component:

<bean id="balanceUpdater" class="com.example.dao.BalanceUpdater">
    <constructor-arg ref="dataSource" />
</bean>

Aggregate Operations with SqlFunction

For queries that return a single value, such as COUNT(*) or SUM(column), SqlFunction is a lightweight choice. It simplifies the retrieval of scalar values.

public int getTotalAccountCount() {
    SqlFunction<Integer> countFunction = new SqlFunction<>(getDataSource(), "SELECT COUNT(*) FROM accounts");
    countFunction.compile();
    return countFunction.run();
}

Declarative Transaction Management

Spring provides the DataSourceTransactionManager to handle JDBC transactions. A common approach is using TransactionProxyFactoryBean to wrap a DAO and inject transactional behavior without polluting business logic with commit or rollback calls.

Consider a fund transfer method that must be atomic:

public void transferFunds(int sourceId, int targetId, double amount) {
    updateBalance(sourceId, -amount);
    // Simulate an error to test rollback
    if (amount > 1000) {
        throw new RuntimeException("Limit exceeded, triggering rollback");
    }
    updateBalance(targetId, amount);
}

The configuration for the transaction manager and the proxy looks like this:

<!-- Transaction Manager -->
<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource"/>
</bean>

<!-- Transactional Proxy -->
<bean id="accountDaoProxy" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
    <property name="target" ref="accountDao"/>
    <property name="transactionManager" ref="txManager"/>
    <property name="transactionAttributes">
        <props>
            <prop key="transfer*">PROPAGATION_REQUIRED</prop>
            <prop key="find*">PROPAGATION_SUPPORTS,readOnly</prop>
        </props>
    </property>
</bean>

The propagation settings define how transactions behave:

• PROPAGATION_REQUIRED: Joins an existing transaction or creates a new one.
• PROPAGATION_SUPPORTS: Executes within a transaction if one exists, otherwise executes non-transactionally.
• Rollback Behavior: By default, Spring rolls back on RuntimeException and Error, but not on checked exceptions unless configured via -ExceptionName.

To ensure transactions are active, you must retrieve the bean via the proxy ID (accountDaoProxy) rather than the raw target bean ID. This ensures that the caller interacts with the AOP proxy that manages the database connection lifecycle.