Spring JDBC
一、概述
Spring JDBC vs JDBC
┌─────────────────────────────────────────────────────────────────┐
│ JDBC vs Spring JDBC │
├─────────────────────────────────────────────────────────────────┤
│ │
│ 传统 JDBC： │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ Connection conn = null; │ │
│ │ PreparedStatement pstmt = null; │ │
│ │ ResultSet rs = null; │ │
│ │ try { │ │
│ │ conn = ds.getConnection(); │ │
│ │ pstmt = conn.prepareStatement(SQL); │ │
│ │ pstmt.setString(1, name); │ │
│ │ rs = pstmt.executeQuery(); │ │
│ │ while (rs.next()) { ... } │ │
│ │ } finally { │ │
│ │ close(rs, pstmt, conn); // 容易遗漏/出错 │ │
│ │ } │ │
│ └─────────────────────────────────────────────────────────┘ │
│ │
│ Spring JDBC： │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ jdbcTemplate.queryForObject(SQL, new Object[]{name}, │ │
│ │ (rs, rowNum) -> { ... }); │ │
│ │ // 自动管理连接、异常转换 │ │
│ └─────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────┘二、快速开始
Maven 依赖
xml
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
</dependency>配置
yaml
spring:
datasource:
url: jdbc:mysql://localhost:3306/demo
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver三、JdbcTemplate
查询
java
@Service
public class UserService {
@Autowired
private JdbcTemplate jdbcTemplate;
// 查询单个对象
public User findById(Long id) {
String sql = "SELECT * FROM users WHERE id = ?";
return jdbcTemplate.queryForObject(sql, (rs, rowNum) -> {
User user = new User();
user.setId(rs.getLong("id"));
user.setName(rs.getString("name"));
user.setEmail(rs.getString("email"));
return user;
}, id);
}
// 查询列表
public List<User> findAll() {
String sql = "SELECT * FROM users";
return jdbcTemplate.query(sql, (rs, rowNum) -> {
User user = new User();
user.setId(rs.getLong("id"));
user.setName(rs.getString("name"));
return user;
});
}
// 查询基本类型
public int count() {
String sql = "SELECT COUNT(*) FROM users";
return jdbcTemplate.queryForObject(sql, Integer.class);
}
// 查询 Map
public Map<String, Object> findAsMap(Long id) {
String sql = "SELECT * FROM users WHERE id = ?";
return jdbcTemplate.queryForMap(sql, id);
}
// 查询 List<Map>
public List<Map<String, Object>> findAllAsMap() {
String sql = "SELECT * FROM users";
return jdbcTemplate.queryForList(sql);
}
}更新
java
@Service
public class UserService {
@Autowired
private JdbcTemplate jdbcTemplate;
// 插入
public int insert(User user) {
String sql = "INSERT INTO users(name, email) VALUES(?, ?)";
return jdbcTemplate.update(sql, user.getName(), user.getEmail());
}
// 更新
public int update(User user) {
String sql = "UPDATE users SET name = ?, email = ? WHERE id = ?";
return jdbcTemplate.update(sql, user.getName(), user.getEmail(), user.getId());
}
// 删除
public int delete(Long id) {
String sql = "DELETE FROM users WHERE id = ?";
return jdbcTemplate.update(sql, id);
}
// 批量插入
public int batchInsert(List<User> users) {
String sql = "INSERT INTO users(name, email) VALUES(?, ?)";
List<Object[]> batch = users.stream()
.map(u -> new Object[]{u.getName(), u.getEmail()})
.collect(Collectors.toList());
int[] results = jdbcTemplate.batchUpdate(sql, batch);
return Arrays.stream(results).sum();
}
}命名参数
java
@Service
public class UserService {
@Autowired
private NamedParameterJdbcTemplate namedJdbcTemplate;
public User findByName(String name) {
String sql = "SELECT * FROM users WHERE name = :name";
Map<String, Object> params = Map.of("name", name);
return namedJdbcTemplate.queryForObject(sql, params, (rs, rowNum) -> {
User user = new User();
user.setId(rs.getLong("id"));
user.setName(rs.getString("name"));
return user;
});
}
// 使用 SqlParameterSource
public int insert(User user) {
String sql = "INSERT INTO users(name, email) VALUES(:name, :email)";
SqlParameterSource params = new BeanPropertySqlParameterSource(user);
return namedJdbcTemplate.update(sql, params);
}
}四、RowMapper
自定义 RowMapper
java
public class UserRowMapper implements RowMapper<User> {
@Override
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
User user = new User();
user.setId(rs.getLong("id"));
user.setName(rs.getString("name"));
user.setEmail(rs.getString("email"));
user.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
return user;
}
}
// 使用
public User findById(Long id) {
String sql = "SELECT * FROM users WHERE id = ?";
return jdbcTemplate.queryForObject(sql, new UserRowMapper(), id);
}BeanPropertyRowMapper
java
public User findById(Long id) {
String sql = "SELECT * FROM users WHERE id = ?";
return jdbcTemplate.queryForObject(sql,
new BeanPropertyRowMapper<>(User.class), id);
}五、事务管理
@Transactional
java
@Service
public class TransferService {
@Autowired
private JdbcTemplate jdbcTemplate;
@Transactional
public void transfer(Long fromId, Long toId, BigDecimal amount) {
// 扣款
String sql1 = "UPDATE accounts SET balance = balance - ? WHERE id = ?";
jdbcTemplate.update(sql1, amount, fromId);
// 存款
String sql2 = "UPDATE accounts SET balance = balance + ? WHERE id = ?";
jdbcTemplate.update(sql2, amount, toId);
// 如果异常，事务回滚
}
}六、SQL 结果映射
java
// 查询单个值
String name = jdbcTemplate.queryForObject("SELECT name FROM users WHERE id = ?", String.class, id);
// 查询计数
int count = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM users", Integer.class);
// 查询是否存在
boolean exists = jdbcTemplate.queryForObject(
"SELECT COUNT(*) > 0 FROM users WHERE id = ?", Boolean.class, id);
// IN 查询
List<Object> ids = Arrays.asList(1, 2, 3);
String sql = "SELECT * FROM users WHERE id IN (" +
ids.stream().map(id -> "?").collect(Collectors.joining(",")) + ")";
jdbcTemplate.query(sql, new UserRowMapper(), ids.toArray());七、面试高频问题
Q1: JdbcTemplate 如何管理连接？
通过 DataSourceUtils 从连接池获取连接，使用后自动归还
Q2: 命名参数有什么用？
使用 :name 代替 ?，避免参数顺序错误
Q3: RowMapper 和 ResultSetExtractor 区别？
- RowMapper：每行映射一个对象
- ResultSetExtractor：整个结果集映射一个对象
八、下一章预告
下一章我们将学习 JWT 实现登录：
- JWT 结构与原理
- Spring Security + JWT
- Token 刷新与黑名单