spring学习记录-JdbcTemplate基础

JdbcTemplate
1、什么是 JdbcTemplate
（1）Spring 框架对 JDBC 进行封装，使用 JdbcTemplate 方便实现对数据库操作
2.1.准备依赖包：


2.2在spring配置文件里配置数据库连接池：

<!-- 数据库连接池 --> <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" 
 destroy-method="close">
 <property name="url" value="jdbc:mysql:///user_db" />
 <property name="username" value="root" />
 <property name="password" value="root" />
 <property name="driverClassName" value="com.mysql.jdbc.Driver" />
</bean>

2.3配置 JdbcTemplate 对象，注入 DataSource

<!-- JdbcTemplate 对象 --> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
 <!--注入 dataSource-->
 <property name="dataSource" ref="dataSource"></property>
</bean>

2.4 创建 service 类，创建 dao 类，在 dao 注入 jdbcTemplate 对象

* 配置文件
<!-- 组件扫描 --> <context:component-scan base-package="com.atguigu"></context:component-scan> 

service 类:

@Service(value = "bookService")//注入对象
public class BookService {
    //注入dao
    @Autowired//注入属性
    BookDao bookDao;

    //添加方法
    public void addBook(Book book){
        bookDao.add(book);//调用BookDaoImpl重写的add方法
    }
    public void update(Book book){
        bookDao.update(book);
    }
    public void delete(Integer id){
        bookDao.delete(id);
    }
    //查询记录数
    public int findCount(){
        return    bookDao.selectCount();
    }
    //查询表记录信息
    public  Book findOne(Integer id){
       return   bookDao.findBookInfo(id);
    }
    //查询全部表记录
    public List<Book> findAll(){
        return bookDao.findAll();
    }
    //批量添加
    public void batchAdd(List<Object []> batch){
          bookDao.batchAddBook(batch);
    }
    //批量修改
    public  void  batchUpdateBook(List<Object []> batch){
        bookDao.batchUpdateBook(batch);
    }
    //批量删除
    public  void  batchDeleteBook(List<Object []> batch){
        bookDao.batchDeleteBook(batch);
    }
}

BookDao类

public interface BookDao {
    public  void  add(Book book);
    public  void  update(Book book);
    public void delete(Integer id);
    public int selectCount();
    public Book findBookInfo(Integer id);
    public List<Book> findAll();
    public void batchAddBook(List<Object []> batch);
    public void batchUpdateBook(List<Object []> batch);
    public  void  batchDeleteBook(List<Object []> batch);

}

BookDaoImpl类

@Repository
public class BookDaoImpl implements BookDao{
        //注入jdbctemplate
    @Autowired
    JdbcTemplate jdbcTemplate;

    @Override
    public void add(Book book) {
        String sql="insert into book values(?,?,?)";
        int update = jdbcTemplate.update(sql, book.getId(), book.getName(), book.getAuthor());
        System.out.println(update);
    }
    @Override
    public void update(Book book) {
        String sql="update  book  set id=?,name=?,author=? where id=?";
        int update = jdbcTemplate.update(sql, book.getId(), book.getName(), book.getAuthor(),book.getId());
        System.out.println(update);
    }
    @Override
    public void delete(Integer id) {
        String sql="delete from book where id=?";
        int update = jdbcTemplate.update(sql,id);
        System.out.println(update);
    }
    @Override
    public int selectCount() {
        String sql="select count(*) from book";
        int update = jdbcTemplate.queryForObject(sql,Integer.class);
        return update;
    }
    @Override
    public Book findBookInfo(Integer id) {
       String sql="select * from book where id=?";
       Book book=jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<Book>(Book.class),id);
       return book;
    }
    @Override
    public List<Book> findAll() {
        String sql="select *from book";
        List<Book> book = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
        return book;
    }
    @Override
    public void batchAddBook(List<Object[]> batch) {
        String sql="insert into book values(?,?,?)";
        int[] ints = jdbcTemplate.batchUpdate(sql, batch);
        System.out.println(Arrays.asList(ints));
    }
    //批量修改
    @Override
    public void batchUpdateBook(List<Object[]> batch) {
        String sql="update book set name=?,author=?  where id=?";
        int[] ints = jdbcTemplate.batchUpdate(sql, batch);
        System.out.println(Arrays.asList(ints));
    }
    @Override
    public void batchDeleteBook(List<Object[]> batch) {
        String sql="delete from  book where id=?";
        int[] ints = jdbcTemplate.batchUpdate(sql, batch);
        System.out.println(Arrays.asList(ints));
    }
}

测试类：

@Test
    public void testJdbcTemplate(){
            ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");//xm开启扫描需要bean.xml中的
//                                                <!--<context:component-scan base-package="com.lbl"></context:component-scan>-->
//            AnnotationConfigApplicationContext context = new AnnotationConfigApplicationContext(BookConfig.class);//注解开启扫描，需要BookConfig里的
//                                                                                                                          @Configuration
//                                                                                                                          @ComponentScan(basePackages = "com.lbl")//组件扫描
            bookService.addBook(new Book(3,"三体","刘慈欣"));
            bookService.update(new Book(1,"朝花夕拾","鲁迅"));
            bookService.delete(1);
            int count = bookService.findCount();
            System.out.println(count);
            Book one = bookService.findOne(2);
            System.out.println(one);
            List<Book> all = bookService.findAll();
            System.out.println(all);
                    List<Object[]> batch= new ArrayList<>();
                  Object o[]=  {4};
                  Object p[]=  {5};
                  Object q[]=  {6};
                  Object r[]=  {7};
                  batch.add(o);
                  batch.add(p);
                  batch.add(q);
                  batch.add(r);
//                 bookService.batchUpdateBook(batch);
                bookService.batchDeleteBook(batch);

        }
        }