文章目录
4.1 JDBCTemplate简介
Spring 框架对 JDBC 进行封装,使用 JdbcTemplate 方便实现对数据库操作。
4.2 使用JDBCTemplate案例
准备工作:
-
引入相关 jar 包
<dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>5.3.20</version> </dependency> <!-- https://mvnrepository.com/artifact/org.springframework/spring-orm --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-orm</artifactId> <version>5.3.20</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-tx</artifactId> <version>5.3.20</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.11</version> </dependency>
-
在 spring 配置文件配置数据库连接池
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd"> <!--配置Druid配置--> <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close"> <property name="url" value="jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true" /> <property name="username" value="root" /> <property name="password" value="960614sd" /> <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" /> </bean> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <!--注入 dataSource--> <property name="dataSource" ref="dataSource"></property> </bean> <!--设置组件扫描--> <context:component-scan base-package="atguigu"></context:component-scan> </beans>
-
创建 dao 类,在 dao 注入 jdbcTemplate 对象
@Component(value = "userDAO") public class UserDAOImpl implements UserDAO { private JdbcTemplate jdbcTemplate; public UserDAOImpl() { } @Autowired private UserDAOImpl(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } @Override public void updateUser(User user) { jdbcTemplate.update("update user set name = ?, password = ?, address = ?, phone = ? where id = ?", user.getName(), user.getPassword(), user.getAddress(), user.getPhone(), user.getId()); } }
4.3 JDBCTemplate相关操作
4.3.1 增删改操作
@Component(value = "userDAO")
public class UserDAOImpl implements UserDAO {
private JdbcTemplate jdbcTemplate;
public UserDAOImpl() {
}
@Autowired
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
@Override
public void updateUser(User user) {
jdbcTemplate.update("update user set name = ?, password = ?, address = ?, phone = ? where id = ?", user.getName(), user.getPassword(), user.getAddress(), user.getPhone(), user.getId());
}
@Override
public void addUser(User user) {
jdbcTemplate.update("insert into user values(0,?,?,?,?)", user.getName(), user.getPassword(), user.getAddress(), user.getPhone());
}
@Override
public void deleteUser(Integer id) {
jdbcTemplate.update("delete from user where id = ?", id);
}
}
4.3.2 查询返回对象
@Component(value = "userDAO")
public class UserDAOImpl implements UserDAO {
...
@Override
public User getUserById(Integer id) {
String sql = "select * from user where id = ?";
return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), id);
}
}
其中queryForObject(String sql, RowMapper<T> rowMapper, Object... args
将查询的结果封装成具体Java Bean。有三个参数:
- 第一个参数:sql 语句
- 第二个参数:RowMapper 是接口,针对返回不同类型数据,使用这个接口里面实现类完成 数据封装
- 第三个参数:sql 参数值
4.3.3 查询返回集合
@Component(value = "userDAO")
public class UserDAOImpl implements UserDAO {
...
@Override
public List<User> getAll() {
String sql = "select * from user";
return jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class));
}
}
其中query(String sql, RowMapper<T> rowMapper, Object... args
将查询的结果封装成一个集合。有三个参数:
- 第一个参数:sql 语句
- 第二个参数:RowMapper 是接口,针对返回不同类型数据,使用这个接口里面实现类完成 数据封装
- 第三个参数:sql 参数值
4.3.4 批量操作
@Component(value = "userDAO")
public class UserDAOImpl implements UserDAO {
...
// 批量添加
@Override
public void batchAddUser(List<Object[]> batchArgs) {
String sql = "insert into user values(0,?,?,?,?)";
jdbcTemplate.batchUpdate(sql, batchArgs);
}
// 批量修改
@Override
public void batchUpdateBook(List<Object[]> batchArgs) {
String sql = "update user set name = ?, password = ?, address = ?, phone = ? where id = ?";
jdbcTemplate.batchUpdate(sql, batchArgs);
}
// 批量删除
@Override
public void batchDeleteBook(List<Object[]> batchArgs) {
String sql = "delete from user where id = ?";
jdbcTemplate.batchUpdate(sql, batchArgs);
}
}