Introduction to Spring JDBC Template

Spring JDBC Template, part of the spring-jdbc module, simplifies database operations by handling repetitive JDBC tasks. It provides a higher-level abstraction over raw JDBC while maintaining performance advantages over more complex ORM frameworks.

SQL Parameter Passing Methods

Spring JDBC Template offers flexible parameter passing approaches beyond basic JDBC placeholder syntax.

Parameter Representation Styles

• Positional parameters using question marks (?)
• Named parameters using colon-prefixed identifiers (:paramName)

Parameter Passing Techniques

• Arrays and collections for positional parameters
• Maps and Java beans for named parameters

Key method signaturees from Spring JDBC Template:

JdbcTemplate.batchUpdate(String, Collection<t>, int, ParameterizedPreparedStatementSetter<t>)
JdbcTemplate.batchUpdate(String, List<object>)
JdbcTemplate.query(String, Object[], int[], ResultSetExtractor<t>)
JdbcTemplate.update(String, Object...)

NamedParameterJdbcTemplate.update(String, Map<string>)
NamedParameterJdbcTemplate.queryForObject(String, SqlParameterSource, Class<t>)
</t></string></t></object></t></t>

SqlParameterSource Implementation

The SqlParameterSource interface provides structured parameter handling with three primary implementations:

• BeanPropertySqlParameterSource - extracts parameters from Java bean properties
• MapSqlParameterSource - uses Map-based parameter storage
• EmptySqlParameterSource - provides empty parameter source

Example using BeanPropertySqlParameterSource:

@Transactional
public int insertFamilyRecord(String familyName) {
    String sql = "INSERT INTO family(name) VALUES(:name)";
    Family family = new Family(familyName);
    KeyHolder keyHolder = new GeneratedKeyHolder();
    SqlParameterSource params = new BeanPropertySqlParameterSource(family);
    int affectedRows = namedJdbcTemplate.update(sql, params, keyHolder);
    return keyHolder.getKey().intValue();
}

Batch Processing Operations

Batch operations are suitable for data import and collection scenarios where moderate data volumes are processed.

@Transactional
public String executeBatchInsert() {
    String insertSQL = "INSERT INTO family(name, batch_id) VALUES(?,?)";
    List<object> parameterList = new ArrayList<>();
    String batchIdentifier = UUID.randomUUID().toString();
    
    for (int i = 0; i < 2; i++) {
        Object[] params = new Object[2];
        params[0] = UUID.randomUUID().toString();
        params[1] = batchIdentifier;
        parameterList.add(params);
    }
    
    // Method 1: Using ParameterizedPreparedStatementSetter
    jdbcTemplate.batchUpdate(insertSQL, parameterList, 4, 
        (PreparedStatement stmt, Object[] arguments) -> {
            stmt.setObject(1, arguments[0]);
            stmt.setObject(2, arguments[1]);
        });
    
    // Method 2: Using BatchPreparedStatementSetter
    BatchPreparedStatementSetter batchSetter = new BatchPreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement stmt, int index) throws SQLException {
            stmt.setObject(1, parameterList.get(index)[0]);
            stmt.setObject(2, parameterList.get(index)[1]);
        }
        
        @Override
        public int getBatchSize() {
            return parameterList.size();
        }
    };
    
    int[] resultCounts = jdbcTemplate.batchUpdate(insertSQL, batchSetter);
    int totalRows = Arrays.stream(resultCounts).sum();
    System.out.println("Total rows affected: " + totalRows);
    return batchIdentifier;
}
</object>

Retrieving Auto-generated Keys

public int createFamilyRecord(String familyName) {
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update((Connection conn) -> {
        String sql = "INSERT INTO family(name) VALUES(?)";
        PreparedStatement stmt = conn.prepareStatement(sql, new String[]{"id"});
        stmt.setInt(1, Integer.parseInt(familyName));
        return stmt;
    }, keyHolder);
    return keyHolder.getKey().intValue();
}

Query Results Mapping to Objects

public ThirdService getServiceByName(String serviceName) {
    String querySQL = "SELECT service_name, service_name_cn, instance_service_name, "
                    + "service_desc, status_flag, add_time, last_optime "
                    + "FROM third_services WHERE service_name = ? OR service_name_cn = ? "
                    + "LIMIT 1";
    
    RowMapper<thirdservice> mapper = new BeanPropertyRowMapper<>(ThirdService.class);
    ThirdService service = jdbcTemplate.queryForObject(querySQL, mapper, serviceName);
    return service;
}
</thirdservice>

Performance Considerasions

Performance hierarchy typically follows: raw JDBC > JDBC Template > MyBatis. The trade-off involves balancing execution speed against development efficiency and maintainability.

Optimization strategies include minimizing reflection operations and data transformations. For maximum performance, consider returning results as List<Map<String, Object>> or similar structures.

Exception Handling

Spring provides specialized exceptions for JDBC operations:

• BadSqlGrammarException - SQL syntax errors
• CannotGetJdbcConnectionException - connection acquisition failures
• IncorrectResultSetColumnCountException - column count mismatches
• InvalidResultSetAccessException - result set access issues
• JdbcUpdateAffectedIncorrectNumberOfRowsException - unexpected row modification counts
• LobRetrievalFailureException - large object retrieval failures
• SQLWarningException - SQL warnings
• UncategorizedSQLException - unclassified SQL exceptions

Utility Classes

Key utility classes include DataSourceUtils and JdbcUtils. DataSourceUtils ensures proper connection management within transactional contexts.

@Component
public class SpringContextHelper implements BeanFactoryAware, ApplicationContextAware {
    
    private static BeanFactory beanFactory;
    private static ApplicationContext applicationContext;
    
    @Override
    public void setBeanFactory(BeanFactory factory) throws BeansException {
        SpringContextHelper.beanFactory = factory;
    }
    
    @Override
    public void setApplicationContext(ApplicationContext context) throws BeansException {
        applicationContext = context;
        DataSource dataSource = context.getBean(DataSource.class);
        System.out.println("Current data source: " + dataSource.getClass().getName());
    }
    
    public static DataSource getCurrentDataSource() {
        return applicationContext.getBean(DataSource.class);
    }
}

Use Case Scenarios

• Performance-critical applications
• Complex SQL requirements not easily expressed in ORM frameworks
• Minimal dependency requirements for core components