04-Spring-JDBCTemplate

1.JdbcTemplate

1. 什么是 JdbcTemplate

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

2. 准备工作

   1. 引入jar包

   2. 配置数据源

   3. 配置JdbcTemplate

   4. 后续可通过 JdbcTemplate 对象进行增删改操作

      1.jar包
      spring-core、spring-beans、spring-aop、spring-context、
      spring-jdbc、spring-tx、spring-orm、
      druid、mysql-connector-java
      
      
      

      <!--    组件扫描-->
      <context:component-scan base-package="com.potato"/>
      
      <!--    2.配置数据源-->
      <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
          <property name="driverClassName" value="com.driver.jdbc.mysql"/>
          <property name="url" value="jdbc:mysql://127.0.0.1:3306/spring5"/>
          <property name="username" value="root"/>
          <property name="password" value="root"/>
      </bean>
      
      <!--    3.配置JdbcTemplate 对象-->
      <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
          <constructor-arg ref="dataSource"/>
      </bean>

2.添加、修改、删除、查询

@Repository
public class UserDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     *
     * 1.增删改  都用update 方法
     * <p>
     * int update(String sql, @Nullable Object... args)
     * sql：sql语句
     * args：sql语句中占位符对应的值
     */
    public Integer add(User user) {
        String sql = "insert into t_user values(?,?,?,?)";
        int result = jdbcTemplate
            .update(sql, user.getId(), user.getUsername(), user.getSex(), user.getAge());
        return result;
    }
    public Integer remove(Integer id) {
        String sql = "delete from t_user where id =?";
        int result = jdbcTemplate.update(sql, id);
        return result;
    }
    public Integer update(User user) {
        String sql = "update t_user set username=? , sex=? , age =? where id=?";
        int result = jdbcTemplate
            .update(sql, user.getUsername(), user.getSex(), user.getAge(), user.getId());
        return result;
    }

    /**
     * 2.查询返回单个对象
     * <p>
     * <T> T queryForObject(String sql, Class<T> requiredType)
     * sql:sql语句
     * requiredType 查询结果类的class
     */
    public User findUserById(Integer id) {
        String sql = "select * from t_user where id=?";
        User user = jdbcTemplate
            .queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), id);
        return user;
    }

    public Integer findCount() {
        String sql = "select count(*) from t_user";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
        return count;
    }


    /**
     * 3.查询返回集合
     * <p>
     * List<T> query(String sql, RowMapper<T> rowMapper)
     * sql:sql语句
     * rowMapper：把数据库中的列字段和java bean中属性对应上
     */
    public List<User> findList(String username) {
        String sql = "select * from t_user where username like concat('%',username,'%')";
        List<User> users = jdbcTemplate
            .query(sql, new BeanPropertyRowMapper<User>(User.class));
        return users;
    }
}

3.批量操作

@Repository
public class UserDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 4.批量增删改
     * <p>
     * int[] batchUpdate(String sql, List<Object[]> batchArgs)
     * sql：sql语句
     * Object[]：对象数组，表示一条记录,和sql语句的占位符对应
     * batchArgs：需要进行批量的全部记录
     */
    public int[] batchAdd(List<Object[]> users) {
//        users：[{"王老三","男",18},{"麻子哥","男",28},{"李大娘","女",46},]
        String sql = "insert into t_user(username,sex,age) values(?,?,?)";
		List<Object[]> batchArgs = new ArrayList<>();
        Object[] o1 = {"王老三", "男", 18};
        Object[] o2 = {"麻子哥", "男", 28};
        batchArgs.add(o1);
        batchArgs.add(o2);
       
        int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
        return ints;
    }

    public int[] batchRemove(List<Object[]> users) {
        String sql = "delete from t_user where id =?";
        List<Object[]> batchArgs = new ArrayList<>();
        Object[] o1 = {10};
        Object[] o2 = {11};
        batchArgs.add(o1);
        batchArgs.add(o2);
        
        int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
        return ints;
    }

    public int[] batchUpdate(List<Object[]> users) {
        String sql = "update t_user set username=? , sex=? , age =? where id=?";
        List<Object[]> batchArgs = new ArrayList<>();
        Object[] o1 = {"王老三", "男", 28, 10};
        Object[] o2 = {"麻子哥", "男", 28, 11};
        batchArgs.add(o1);
        batchArgs.add(o2);
        
        int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
        return ints;
    }
}