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

一、什么是JdbcTemplate

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

1、导入jar包

 2、配置文件

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

}

public interface BookDao {
}

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

<?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 http://www.springframework.org/schema/context/spring-context.xsd">

    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3307/demo"></property>
        <property name="username" value="root"></property>
        <property name="password" value="123456"></property>
    </bean>
    <!--JdbcTemplate对象-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <!--注入dataSource-->
        <property name="dataSource" ref="dataSource"></property>
    </bean>
    <!-- 组件扫描 -->
    <context:component-scan base-package="com.cjc"></context:component-scan>
</beans>

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<Book> selectAllBook();

    void insertAllBook(List<Object[]> bookList);

    void updateAllBook(List<Object[]> bookList);

    void deleteAllBook(List<Object[]> 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<Book> selectAllBook() {
        String sql = "select * from t_book ";
        List<Book> bookList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Book.class));
    return bookList;
    }

    @Override
    public void insertAllBook(List<Object[]> 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<Object[]> 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<Object[]> 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;

    /**
     * 添加book
     * @param book
     */
    public void addBook(Book book){
        bookDao.addBook(book);
    }

    /**
     * 修改book
     * @param book
     */
    public void updateBook(Book book){
        bookDao.updateBook(book);
    }

    /**
     * 删除book
     * @param id
     */
    public void deleteBook(String id){
        bookDao.deleteBook(id);
    }

    /**
     * 查询记录数
     * @return
     */
    public int selectCount(){
        int i = bookDao.selectBook();
        return i;
    }

    /**
     * 查询单条数据
     * @param id
     * @return
     */
    public Book selectBook(String id){
        return bookDao.findBookInfo(id);
    }

    /**
     * 批量查询
     * @return
     */
    public List<Book> selectAllBook(){
        return bookDao.selectAllBook();
    }

    /**
     * 批量添加
     * @param bookList
     */
    public void insertAllBook(List<Object[]> bookList){
        bookDao.insertAllBook(bookList);
    }

    /**
     * 批量修改
     * @param bookList
     */
    public void updateAllBook(List<Object[]> bookList){
        bookDao.updateAllBook(bookList);
    }

    /**
     * 批量删除
     * @param bookList
     */
    public void deleteAllBook(List<Object[]> 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<Book> bookList = bookService.selectAllBook();
//        System.out.println(bookList);
        //批量添加
//        List<Object[]> 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<Object[]> list = new ArrayList<>();
//        Object[] arr1 = {"语文","无","4"};
//        Object[] arr2 = {"数学","无","5"};
//        list.add(arr1);
//        list.add(arr2);
//        bookService.updateAllBook(list);
        //批量删除
        List<Object[]> list = new ArrayList<>();
        Object[] arr1 = {"4"};
        Object[] arr2 = {"5"};
        list.add(arr1);
        list.add(arr2);
        bookService.deleteAllBook(list);
    }
}