Spring：（四）JDBCTemplate

4.1 JDBCTemplate简介

Spring 框架对 JDBC 进行封装，使用 JdbcTemplate 方便实现对数据库操作。

4.2 使用JDBCTemplate案例

准备工作：

1. 引入相关 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>
   

2. 在 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&amp;useSSL=false&amp;serverTimezone=UTC&amp;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>
   

3. 创建 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);
    }
}