Java学习 --- spring5使用JdbcTemplate实现增删改查

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

@Service
public class BookService {
    @Autowired
    private BookDao bookDao;

}

public interface BookDao {
}

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

public class Book {
    private String Id;
    private String name;
    private String status;

    public String getId() {
        return Id;
    }

    public void setId(String id) {
        Id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    @Override
    public String toString() {
        return "Book{" +
                "Id='" + Id + ''' +
                ", name='" + name + ''' +
                ", status='" + status + ''' +
                '}';
    }
}

public interface BookDao {

    void addBook(Book book);

    void updateBook(Book book);

    void deleteBook(String id);

    int selectBook();

    Book findBookInfo(String id);

    List selectAllBook();

    void insertAllBook(List bookList);

    void updateAllBook(List bookList);

    void deleteAllBook(List bookList);
}

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

    @Override
    public void addBook(Book book) {
        //创建sql语句
        String sql = "insert into t_book values(?,?,?)";
        int update = jdbcTemplate.update(sql, book.getId(), book.getName(), book.getStatus());
        System.out.println(update);
    }

    @Override
    public void updateBook(Book book) {
        String sql = "update t_book set name=?,status=? where id=?";
        int update = jdbcTemplate.update(sql, book.getName(), book.getStatus(), book.getId());
        System.out.println(update);
    }

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

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

    @Override
    public Book findBookInfo(String id) {
        String sql = "select * from t_book where id=?";
        Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Book.class), id);
        return book;
    }

    @Override
    public List selectAllBook() {
        String sql = "select * from t_book ";
        List bookList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Book.class));
    return bookList;
    }

    @Override
    public void insertAllBook(List bookList) {
        String sql = "insert into t_book values(?,?,?)";
        int[] ints = jdbcTemplate.batchUpdate(sql, bookList);
        System.out.println(Arrays.toString(ints));
    }

    @Override
    public void updateAllBook(List bookList) {
        String sql = "update t_book set name=?,status=? where id=?";
        int[] ints = jdbcTemplate.batchUpdate(sql, bookList);
        System.out.println(Arrays.toString(ints));
    }

    @Override
    public void deleteAllBook(List bookList) {
        String sql = "delete from t_book where id=?";
        int[] ints = jdbcTemplate.batchUpdate(sql, bookList);
        System.out.println(Arrays.toString(ints));
    }
}

@Service
public class BookService {
    @Autowired
    private BookDao bookDao;

    
    public void addBook(Book book){
        bookDao.addBook(book);
    }

    
    public void updateBook(Book book){
        bookDao.updateBook(book);
    }

    
    public void deleteBook(String id){
        bookDao.deleteBook(id);
    }

    
    public int selectCount(){
        int i = bookDao.selectBook();
        return i;
    }

    
    public Book selectBook(String id){
        return bookDao.findBookInfo(id);
    }

    
    public List selectAllBook(){
        return bookDao.selectAllBook();
    }

    
    public void insertAllBook(List bookList){
        bookDao.insertAllBook(bookList);
    }

    
    public void updateAllBook(List bookList){
        bookDao.updateAllBook(bookList);
    }

    
    public void deleteAllBook(List bookList){
        bookDao.deleteAllBook(bookList);
    }
}

public class JdbcTest {

    @Test
    public void Test01(){
        ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("bean4.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        Book book = new Book();
        //添加
//        book.setId("2");
//        book.setName("红楼梦");
//        book.setStatus("有");
//        bookService.addBook(book);
          //修改
//        book.setStatus("无");
//        book.setName("西游记");
//        book.setId("1");
//        bookService.updateBook(book);
       //删除
       // bookService.deleteBook("1");
        //查询
//        int i = bookService.selectCount();
//        System.out.println(i);
       // Book book1 = bookService.selectBook("1");
//        List bookList = bookService.selectAllBook();
//        System.out.println(bookList);
        //批量添加
//        List list = new ArrayList<>();
//        Object[] arr1 = {"4","语文","有"};
//        Object[] arr2 = {"5","数学","有"};
//        Object[] arr3 = {"6","英语","有"};
//        list.add(arr1);
//        list.add(arr2);
//        list.add(arr3);
//        bookService.insertAllBook(list);
        //批量修改
//        List list = new ArrayList<>();
//        Object[] arr1 = {"语文","无","4"};
//        Object[] arr2 = {"数学","无","5"};
//        list.add(arr1);
//        list.add(arr2);
//        bookService.updateAllBook(list);
        //批量删除
        List list = new ArrayList<>();
        Object[] arr1 = {"4"};
        Object[] arr2 = {"5"};
        list.add(arr1);
        list.add(arr2);
        bookService.deleteAllBook(list);
    }
}