Spring Architecture Series-6.Implementing JDBC Module in Spring
Table of Contents
Implementing JDBC Module in Spring
Introduce
JDBC(Java Database Connectivity) is the standard API for database access in Java.However,working with JDBC can be tedious and error-prone.In this article,i"ll explore how to implement a JDBC module that simplifies database operations, based on my miniSpring project’s implementation.
Core Components
The JDBC module consists of several key components:
src/com/yaruyng/jdbc/
├── core/
│ ├── JdbcTemplate.java
│ ├── RowMapper.java
│ ├── ResultSetExtractor.java
│ ├── StatementCallBack.java
│ ├── PreparedStatementCallBack.java
│ └── ArgumentPreparedStatementSetter.java
├── datasource/
└── pool/
JdbcTemplate:The Core Class
The JdbcTemplate class is the central component that simplifies JDBC operations:
public class JdbcTemplate {
private DataSource dataSource;
public Object query(StatementCallBack stmtCallBack) {
Connection con = null;
Statement stmt = null;
try {
con = dataSource.getConnection();
stmt = con.createStatement();
return stmtCallBack.doInStatement(stmt);
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
stmt.close();
con.close();
} catch (SQLException e) {
}
}
return null;
}
public Object query(String sql, Object[] args,
PreparedStatementCallBack pstmtCallBack) {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = dataSource.getConnection();
pstmt = con.prepareStatement(sql);
ArgumentPreparedStatementSetter setter =
new ArgumentPreparedStatementSetter(args);
setter.setValues(pstmt);
return pstmtCallBack.doInPreparedStatement(pstmt);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
try {
pstmt.close();
con.close();
} catch (SQLException e) {
}
}
}
}
Key features:
- Resource management
- Exception handing
- Connection pooling support
- Prepared statement support
Row Mapping
The RowMapper interface provides a flexible way to map database rows to objects:
public interface RowMapper<T> {
T mapRow(ResultSet rs, int rowNum) throws SQLException;
}
Usage example
public class UserRowMapper implements RowMapper<User> {
@Override
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
User user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setEmail(rs.getString("email"));
return user;
}
}
Parameter Handling
The ArgumentPreparedStatementSetter class handles parameter binding:
public class ArgumentPreparedStatementSetter {
private final Object[] args;
public void setValues(PreparedStatement pstmt) throws SQLException {
if (this.args != null) {
for (int i = 0; i < this.args.length; i++) {
Object arg = this.args[i];
doSetValue(pstmt, i+1, arg);
}
}
}
private void doSetValue(PreparedStatement pstmt,
int parameterPosition, Object argValue) throws SQLException {
if (argValue instanceof String) {
pstmt.setString(parameterPosition, (String)argValue);
}
else if (argValue instanceof Integer) {
pstmt.setInt(parameterPosition, (int)argValue);
}
else if (argValue instanceof java.util.Date) {
pstmt.setDate(parameterPosition,
new java.sql.Date(((java.util.Date)argValue).getTime()));
}
}
}
Features:
- Type-safe parameter binding
- Support for common data types
- Extensible design
Result Set Extraction
The ResultSetExtractor interface provides a way to process result sets:
public interface ResultSetExtractor<T> {
T extractData(ResultSet rs) throws SQLException;
}
Implementation example:
public class RowMapperResultSetExtractor<T> implements ResultSetExtractor<List<T>> {
private final RowMapper<T> rowMapper;
public RowMapperResultSetExtractor(RowMapper<T> rowMapper) {
this.rowMapper = rowMapper;
}
@Override
public List<T> extractData(ResultSet rs) throws SQLException {
List<T> results = new ArrayList<>();
int rowNum = 0;
while (rs.next()) {
results.add(rowMapper.mapRow(rs, ++rowNum));
}
return results;
}
}
Usage Example
1. Simple Query
List<User> users = jdbcTemplate.query(
"SELECT * FROM users WHERE age > ?",
new Object[]{18},
new UserRowMapper()
);
2. Custom Statement Processing
Object result = jdbcTemplate.query(new StatementCallBack() {
@Override
public Object doInStatement(Statement stmt) throws SQLException {
ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM users");
if (rs.next()) {
return rs.getInt(1);
}
return 0;
}
});
3. Prepared Statement with Callback
Object result = jdbcTemplate.query(
"UPDATE users SET name = ? WHERE id = ?",
new Object[]{"John", 1},
new PreparedStatementCallBack() {
@Override
public Object doInPreparedStatement(PreparedStatement pstmt)
throws SQLException {
return pstmt.executeUpdate();
}
}
);
Key Features
1. Resource Management
- Automatic connection handling
- Statement cleanup
- Exception handling
2. Type Safety
- Generic row mapping
- Type-safe parameter binding
- Result type conversion
3. Flexibility
- Custom statement processing
- Extensible row mapping
- Configurable result extraction
4. Error Handling
- SQL exception wrapping
- Resource cleanup
- Transaction support
Best Practices
1. Connection Management
- Use connection pooling
- Proper resource cleanup
- Transaction boundaries
2. Exception Handling
- Custom exception types
- Proper error propagation
- Resource cleanup in finally blocks
3. Performance Optimization
- Statement caching
- Batch processing
- Connection pooling
Common Challenges and Solutions
1. Resource Leaks
- Use try-with-resources
- Proper cleanup in finally blocks
- Connection pooling
2. Type Conversion
- Implement type handlers
- Use prepared statements
- Handle null values
3. Transaction Management
- Spring transaction integration
- Proper isolation levels
- Rollback handling
Conclusion
Implementing a JDBC module provides:
- Simplified database access
- Type-safe operations
- Resource management
- Error handling Key takeaways:
- Understanding JDBC fundamentals
- Resource management patterns
- Type safety in database operations
- Performance optimization techniques
This implementation demonstrates how to create a robust database access layer while maintaining simplicity and flexibility.