Spring——JdbcTemplate

1、JdbcTemplate概念及其准备工作

（1）什么是JdbcTemplate

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

（2）maven依赖项

<!-- springmvc中包含spring核心包 -->
<dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-webmvc</artifactId>
   <version>5.1.10.RELEASE</version>
</dependency>

<!-- spring对jdbc的封装 -->
<dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-jdbc</artifactId>
   <version>4.2.5.RELEASE</version>
</dependency>

<!-- 使用德鲁伊的数据源 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.3</version>
</dependency>

<!-- mysql数据库连接 -->
<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>5.1.35</version>
</dependency>
<!-- 单元测试 -->
<dependency>
   <groupId>junit</groupId>
   <artifactId>junit</artifactId>
   <version>4.10</version>
</dependency>

（3）数据库搭建

（4）在bean.xml中配置JdbcTemplate对象，并注入数据库连接池

    <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="mysql" />
        
        <property name="driverClassName" 
        value="com.mysql.jdbc.Driver" />
    </bean>

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

（5）开启组件扫描
将com.glp包下的所有带注解的组件注入到spring中

<context:component-scan base-package="com.glp"></context:component-scan>

（6）创建service层和dao层

2、JdbcTemplate操作

源码参考：码云地址

（1）BookDao接口

public interface BookDao {
	//增加
    void add(Book book);
    
    //修改
    void updateBook(Book book);
	
	//删除
    void deleteBook(String id);

	//查询数据量
    void selectCount();

	//查询对象
    void findBookInfo(String id);

	//查询对象集合
    List<Book> findBookList();

	//批量增加
    void batchAddBook(List<Object[]> list);

	//批量删除
    void batchDeleteBook(List<Object[]> list);
}

（2）BookDaoImpl实现类

@Repository
public class BookDaoImpl implements BookDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void add(Book book) {
        //1 创建sql语句
        String sql = "insert into book values(?,?,?)";
        //2 调用方法实现
        Object[] args = {book.getUserId(), book.getUsername(), book.getUstatus()};
        int update = jdbcTemplate.update(sql,args);
        System.out.println("插入成功："+update);
    }

    public void updateBook(Book book) {
        String sql = "update book set username=?,ustatus=? where user_id=?";
        Object[] args = { book.getUsername(), book.getUstatus(),book.getUserId()};
        int update = jdbcTemplate.update(sql,args);

        System.out.println("修改成功："+update);
    }


    public void deleteBook(String id) {
        String sql = "delete from book where user_id = ?";
        int update = jdbcTemplate.update(sql, id);
        System.out.println("删除成功："+update);
    }

    public void selectCount() {
        String sql = "select count(*) from book";
        Integer integer = jdbcTemplate.queryForObject(sql, Integer.class);

        System.out.println("数据量："+ integer);

    }

    public void findBookInfo(String id) {
        String sql = "select * from book where user_id=?";
        Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
        System.out.println(book);
    }

    public List<Book> findBookList() {
        String sql = "select * from book";
        List<Book> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));

        System.out.println(query);
        return query;
    }

    public void batchAddBook(List<Object[]> list) {
        String sql = "insert into book values(?,?,?)";
        int[] ints = jdbcTemplate.batchUpdate(sql, list);
        System.out.println(Arrays.toString(ints));
    }

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

注意：

• 插入、修改、删除都使用update方法：
  update(String sql, Object... args)
  ，其中第一个参数是sql语句，第二个参数为可变参数，我们可以利用数组，传入多个参数值。

• 查询数据量，使用queryForObject方法：
  queryForObject(String sql, Class<T> requiredType)，其中第一个参数是sql语句，第二个参数为返回值类型class.

• 查询对象，也使用queryForObject方法，只不过参数不同：
  queryForObject(String sql, RowMapper<T> rowMapper, Object... args)，其中第一个参数为sql语句，第二个参数RowMapper 是接口，针对返回不同类型数据，使用这个接口里面实现类完成数据封装，第三个为sql的参数。

• 返回查询的集合，使用query方法：
  query(String sql, RowMapper<T> rowMapper)，第二个参数和queryForObject方法的相同我们使用它的实现类来完成：
  new BeanPropertyRowMapper<Book>(Book.class)

• 批量添加操作，batchUpdate方法：
  batchUpdate(String sql, List<Object[]> 第一个参数：sql语句， 第二个参数：List集合，添加多条记录数据

• 批量删除操作也使用batchUpdate方法

（3）BookService

@Service
public class BookService {

    @Autowired
    private BookDao bookDao;

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

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

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

    public void selectCount(){
        bookDao.selectCount();
    }

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

    public List<Book> findBookList(){
        return bookDao.findBookList();
    }

    public void batchAddBook(List<Object[]> list){
        bookDao.batchAddBook(list);
    }

    public void batchDeleteBook(List<Object[]> list){
        bookDao.batchDeleteBook(list);
    }

}

（4）测试

public class Jdbctest {


    @Test
    public void test(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = applicationContext.getBean("bookService", BookService.class);
        Book book = new Book();
        book.setUserId("4");
        book.setUsername("java");
        book.setUstatus("a");
        bookService.addBook(book);
    }

    @Test
    public void test1(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = applicationContext.getBean("bookService", BookService.class);
        Book book = new Book();
        book.setUserId("1");
        book.setUsername("javaAndC++");
        book.setUstatus("b");
        bookService.updateBook(book);
    }

    @Test
    public void test2(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = applicationContext.getBean("bookService", BookService.class);
        bookService.deleteBook("1");
    }


    @Test
    public void test3(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = applicationContext.getBean("bookService", BookService.class);
        bookService.selectCount();
    }


    @Test
    public void test4(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = applicationContext.getBean("bookService", BookService.class);
        bookService.findBookInfo("1");
    }

    @Test
    public void test5(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = applicationContext.getBean("bookService", BookService.class);
        bookService.findBookList();
    }

    @Test
    public void test6(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = applicationContext.getBean("bookService", BookService.class);
        List<Object[]> list = new ArrayList<>();
        Object[] o1 = {"7","glp","hello"};
        Object[] o2 = {"8","cbj","hello"};
        list.add(o1);
        list.add(o2);
        bookService.batchAddBook(list);
    }

    @Test
    public void test7(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = applicationContext.getBean("bookService", BookService.class);
        List<Object[]> list = new ArrayList<>();
        Object[] o1 = {"1"};
        Object[] o2 = {"2"};
        list.add(o1);
        list.add(o2);
        bookService.batchDeleteBook(list);
    }
}