spring5入门（十五）：JdbcTemplate使用，crud操作

• 持久层接口

public interface BookDao {

    public void add(Book book);  //添加图书

    public void update(Book book);  //修改图书

    public void delete(int id);  //删除图书

    public int queryCount();   //查询数量

    public Book queryBookById(int id);  //查询某本书

    public List<Book> queryBooks();   //查询所有书

    public void batchAddBook(List<Object[]> books);  //批量添加图书

    public void batchUpdateBook(List<Object[]> books);  //批量修改图书

    public void batchDeleteBook(List<Object[]> args);  //批量删除图书

}

• 持久层实现

@Repository
public class BookDaoImpl implements BookDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void add(Book book) {
        String sql = "insert into t_book set name=?,price=?";
        Object[] args = {book.getBookName(),book.getBookPrice()};
        int update = jdbcTemplate.update(sql, args);
        System.out.println(update);
    }

    @Override
    public void update(Book book) {
        String sql = "update t_book set name=?,price=? where id=?";
        Object[] args = {book.getBookName(),book.getBookPrice(),book.getBookId()};
        int update = jdbcTemplate.update(sql, args);
        System.out.println(update);
    }

    @Override
    public void delete(int id) {
        String sql = "delete from t_book where id=?";
        int update = jdbcTemplate.update(sql, id);
        System.out.println(update);
    }

    @Override
    public int queryCount() {
        String sql = "select count(*) from t_book";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
        return count;
    }

    @Override
    public Book queryBookById(int id) {
        String sql = "select id bookId,name bookName,price bookPrice from t_book where id=?";
        Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
        return book;
    }

    @Override
    public List<Book> queryBooks() {
        String sql = "select id bookId,name bookName,price bookPrice from t_book";
        List<Book> bookList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
        return bookList;
    }

    @Override
    public void batchAddBook(List<Object[]> books) {
        String sql = "insert into t_book set id=?,name=?,price=?";
        int[] ints = jdbcTemplate.batchUpdate(sql, books);
        System.out.println(ints);
    }

    @Override
    public void batchUpdateBook(List<Object[]> books) {
        String sql = "update t_book set name=?,price=? where id=?";
        int[] ints = jdbcTemplate.batchUpdate(sql, books);
        System.out.println(ints);
    }

    @Override
    public void batchDeleteBook(List<Object[]> args) {
        String sql = "delete from t_book where id=?";
        int[] ints = jdbcTemplate.batchUpdate(sql, args);
        System.out.println(ints);
    }

}

• 业务层

@Service
public class BookService {

    @Autowired
    private BookDao bookDao = new BookDaoImpl();

    //添加图书
    public void add(Book book){
        bookDao.add(book);
    }

    //修改图书
    public void update(Book book){
        bookDao.update(book);
    }

    //删除图书
    public void delete(Integer id){
        bookDao.delete(id);
    }

    //查询数量
    public int queryCount(){
        return bookDao.queryCount();
    }

    //查询图书
    public Book queryBookById(Integer id){
        return bookDao.queryBookById(id);
    }

    //查询所有图书
    public List<Book> queryBooks(){
        return bookDao.queryBooks();
    }

    //批量添加图书
    public void batchAddBook(List<Object[]> books){
        bookDao.batchAddBook(books);
    }

    //批量修改图书
    public void batchUpdateBook(List<Object[]> books){
        bookDao.batchUpdateBook(books);
    }

    //批量删除图书
    public void batchDeleteBook(List<Object[]> args){
        bookDao.batchDeleteBook(args);
    }

}