Spring---JdbcTemplate操作数据库

1、什么是JdbcTemplate

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

2、使用前的配置

（1）导入相应的jar包

（2）、配置xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       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">
    <!--开启注解扫描-->
    <context:component-scan base-package="com.gongda.Spring5.JDBCTemplate"></context:component-scan>

    <!--数据库连接池-->
    <!--destroy-method="close"，当数据库连接不使用的时候,就把该连接重新放到数据池中,方便下次使用调用.-->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
        <property name="url" value="jdbc:mysql:///test"></property>
        <property name="username" value="root"></property>
        <property name="password" value="password"></property>
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
</bean>
    <!--配置JdbcTemplate，注入DataSource-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
</beans>

（3）创建Book、Service、Dao等类并添加相应注解



（3）、然后就是编写代码
BookService

@Service
public class BookService {
    //注入dao
    @Autowired
    private BookDao bookDao;
    //添加
    public void addBook(Book book){
        bookDao.add(book);
    }
    //修改
    public void updateBook(Book book){
        bookDao.update(book);
    }
    //删除
    public void deleteBook(int book_id){
        bookDao.delete(book_id);
    }
    //返回所有记录数
    public void findCount(){
        bookDao.selectCount();
    }

    //返回某一记录
    public void findOne(int book_id) {
        Book bookInfo = bookDao.findBookInfo(book_id);
        System.out.println(bookInfo);
    }

    //返回所有记录
    public void findAll() {
        List<Book> allBook = bookDao.findAllBook();
        System.out.println(allBook);
    }

    //批量添加记录
    public void batchAddBooks(List<Object[]> books){
        bookDao.batchAllBook(books);
    }

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

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

BookDao

public interface BookDao {
    //添加
    public void add(Book book);

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

    //删除
    public void delete(int book_id);

    public void selectCount();

    Book findBookInfo(int book_id);

    List<Book> findAllBook();

    void batchAllBook(List<Object[]> books);

    void batchUpdateBook(List<Object[]> books);

    void batchDeleteBook(List<Object[]> book_id);
}

BookDapImpl

@Repository
public class BookDaoImpl implements BookDao {

    //注入JdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void add(Book book) {
        //添加
        String sql="insert into book values(?,?,?)";
        Object args[]={book.getBook_id(),book.getBook_name(),book.getBook_value()};
        int update = jdbcTemplate.update(sql, args);
        System.out.println(update);
    }
    public void update(Book book){
        //修改
        String sql="update book set book_name=?,book_value=? where book_id=?";
        Object args[]={book.getBook_name(),book.getBook_value(),book.getBook_id()};
        int update = jdbcTemplate.update(sql, args);
        System.out.println(update);
    }

    @Override
    public void delete(int book_id) {
        //删除
        String sql="delete from book where book_id=?";
        Object args[]={book_id};
        int update = jdbcTemplate.update(sql, args);
        System.out.println(update);
    }

    @Override
    public void selectCount() {
        //查询所有记录数
        String sql="select count(*) from book";
        //弟一个参数: sql语句
        //第二个参数:返回类型Class
        Integer integer = jdbcTemplate.queryForObject(sql, Integer.class);
        System.out.println(integer);
    }

    @Override
    public Book findBookInfo(int book_id) {
        //查询返回某一对象
        String sql="select * from book where book_id=? ";
        Object args[]={book_id};
        //第一个参数:sql语句
        //第二个参数:RowMapper，是接口，针对返回不同类型数据，使用这个接口里面实现类完成数据封装
        //第三个参数: sql语句值
        Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), args);
        return book;
    }

    @Override
    public List<Book> findAllBook() {
        //查询所有记录
        String sql="select * from book";
        //第一个参数:sql语句
        //第二个参数:RowMapper，是接口，针对返回不同类型数据，使用这个接口里面实现类完成数据封装
        //第三个参数: sql语句值
        List<Book> books = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
        return books;
    }

    @Override
    public void batchAllBook(List<Object[]> books) {
        //批量添加记录
        String sql="insert into book values(?,?,?)";
        int[] ints = jdbcTemplate.batchUpdate(sql, books);
        System.out.println(Arrays.toString(ints));
    }

    @Override
    public void batchUpdateBook(List<Object[]> books) {
        //批量修改记录
        String sql="update book set book_name=?,book_value=? where book_id=?";
        int[] ints = jdbcTemplate.batchUpdate(sql, books);
        System.out.println(Arrays.toString(ints));
    }

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

}

TestJdbcTemplate

public class TestJdbcTemplate {
    @Test
    public void testJdbcTemplate(){
        ApplicationContext context=new ClassPathXmlApplicationContext("bean2.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        //添加
       /* Book book=new Book();
        book.setBook_id(1);
        book.setBook_name("java框架");
        book.setBook_value(20);
        bookService.addBook(book);*/

        //修改
        /*Book book=new Book();
        book.setBook_id(1);
        book.setBook_name("java框架");
        book.setBook_value(30);
        bookService.updateBook(book);*/

        //删除
        /*bookService.deleteBook(1);*/

        //查询所有记录数
        /*bookService.findCount();*/

        //查询返回对象
        /*bookService.findOne(2);*/

        //查询返回所有对象
        /*bookService.findAll();*/

        //批量插入多条数据
       /* List<Object[]> books=new ArrayList<>();
        Object[] o1={3,"ccc", 33};
        Object[] o2={4,"ddd",44};
        Object[] o3={5,"eee",55};
        books.add(o1);
        books.add(o2);
        books.add(o3);
        bookService.batchAddBooks(books);*/

        //批量修改多条记录
        /*List<Object[]> book_list=new ArrayList<>();
        Object[] o1={"ccc1", 33,3};
        Object[] o2={"ddd2",44,4};
        Object[] o3={"eee3",55,5};
        book_list.add(o1);
        book_list.add(o2);
        book_list.add(o3);
        bookService.batchUpdate(book_list);*/

        //批量删除多条记录
        List<Object[]> list=new ArrayList<>();
        Object[] o1={3};
        Object[] o2={4};
        list.add(o1);
        list.add(o2);
        bookService.batchDelete(list);
    }
}