Spring(六)-JdbcTemplate

一、JdbcTemplate使用

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

1.1、准备工作

1）引入相关jar包

2）配置数据库连接信息

prop.driverClass=com.mysql.jdbc.Driver
prop.url=jdbc:mysql://localhost:3306/mybatis
prop.userName=root
prop.password=123456

3）spring配置文件

<!--引入外部资源-->
<context:property-placeholder location="classpath:jdbc.properties"></context:property-placeholder>
<!--配置数据库连接池-->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
    <property name="url" value="${prop.url}" />
    <property name="username" value="${prop.userName}" />
    <property name="password" value="${prop.password}" />
    <property name="driverClassName" value="${prop.driverClass}" />
</bean>
<!--配置JdbcTemplate对象并注入datasource-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <!--注入dataSource-->
    <property name="dataSource" ref="dataSource"></property>
</bean>

4）测试数据库连接

@Test
public void testConnection() throws SQLException {
    ApplicationContext context = new ClassPathXmlApplicationContext("bean3.xml");
    DruidDataSource dataSource = context.getBean("dataSource", DruidDataSource.class);
    System.out.println(dataSource.getConnection());
}

1.2、创建相关类

1）创建数据库表

CREATE TABLE IF NOT EXISTS `book`(
   `id` INT  AUTO_INCREMENT,
   `name` VARCHAR(100) NOT NULL,
   `status` VARCHAR(100) NOT NULL,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

2）创建实体类

package com.dianchou.spring.jdbc;

/**
 * @author lawrence
 * @create 2020-07-08 17:02
 */
public class Book {
    private Integer id;
    private String name;
    private String status;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.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 + '\'' +
                '}';
    }
}

3）创建dao及service

BookDao：

package com.dianchou.spring.jdbc;

import java.util.List;

/**
 * @author lawrence
 * @create 2020-07-08 18:28
 */
public interface BookDao {
    public void addBook(Book book);

    public void deleteById(Integer id);

    public Integer getCount();

    public Book findBookById(Integer id);

    public List<Book> findAllBooks();

    public void batchAdd(List<Object[]> batchArgs);

    public void batchUpdate(List<Object[]> batchArgs);

    public void batchDelete(List<Object[]> batchArgs);

}

BookDaoImpl：

package com.dianchou.spring.jdbc;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.Arrays;
import java.util.List;

/**
 * @author lawrence
 * @create 2020-07-08 18:29
 */

@Repository
public class BookDaoImpl implements BookDao{

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void addBook(Book book) {
        String sql = "insert into book(name,status) values(?,?)";
        Object[] args = {book.getName(),book.getStatus()};
        int updateCount = jdbcTemplate.update(sql, args);
        System.out.println(updateCount);

    }

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

    }

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

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

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

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

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

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

BookService：

package com.dianchou.spring.jdbc;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @author lawrence
 * @create 2020-07-08 18:29
 */

@Service
public class BookService {

    @Autowired
    private BookDao bookDao;

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

    public void deleteBook(Integer id){
        bookDao.deleteById(id);
    }

    public void getCount(){
        Integer count = bookDao.getCount();
        System.out.println(count);
    }

    public void findBookById(){
        Book book = bookDao.findBookById(1);
        System.out.println(book);
    }

    public void findAllBooks(){
        List<Book> books = bookDao.findAllBooks();
        System.out.println(books);
    }

    public void batchAdd(List<Object[]> batchArgs){
        bookDao.batchAdd(batchArgs);
    }
    public void batchUpdate(List<Object[]> batchArgs){
        bookDao.batchUpdate(batchArgs);
    }
    public void batchDelete(List<Object[]> batchArgs){
        bookDao.batchDelete(batchArgs);
    }
}

4）测试

package com.dianchou.spring.jdbc;

import com.alibaba.druid.pool.DruidDataSource;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author lawrence
 * @create 2020-07-08 16:12
 */
public class JdbcTemplateTest {

    @Test
    public void testConnection() throws SQLException {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean3.xml");
        DruidDataSource dataSource = context.getBean("dataSource", DruidDataSource.class);
        System.out.println(dataSource.getConnection());
    }

    @Test
    public void testAdd(){
        ApplicationContext context = new ClassPathXmlApplicationContext("bean3.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        Book book = new Book();
//        book.setId(4);
        book.setName("水浒传");
        book.setStatus("OK");
        bookService.addBook(book);

    }

    @Test
    public void testDelete(){
        ApplicationContext context = new ClassPathXmlApplicationContext("bean3.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        bookService.deleteBook(3);
    }

    @Test
    public void testCount(){
        ApplicationContext context = new ClassPathXmlApplicationContext("bean3.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        bookService.getCount();
    }

    @Test
    public void testFindBookById(){
        ApplicationContext context = new ClassPathXmlApplicationContext("bean3.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        bookService.findBookById();
    }

    @Test
    public void testFindAllBooks(){
        ApplicationContext context = new ClassPathXmlApplicationContext("bean3.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        bookService.findAllBooks();
    }

    @Test
    public void testBatchAdd(){
        ApplicationContext context = new ClassPathXmlApplicationContext("bean3.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        List<Object[]> batchArgs = new ArrayList<>();
        Object[] o1 = {"Java","OK"};
        Object[] o2 = {"Python","OK"};
        Object[] o3 = {"C++","OK"};
        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);
        bookService.batchAdd(batchArgs);
    }
    @Test
    public void testBatchUpdate(){
        ApplicationContext context = new ClassPathXmlApplicationContext("bean3.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        List<Object[]> batchArgs = new ArrayList<>();
        Object[] o1 = {"Java","OK",1};
        Object[] o2 = {"Python","OK",2};
        Object[] o3 = {"C++","OK",3};
        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);
        bookService.batchUpdate(batchArgs);
    }
    @Test
    public void testBatchDelete(){
        ApplicationContext context = new ClassPathXmlApplicationContext("bean3.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        List<Object[]> batchArgs = new ArrayList<>();
        Object[] o1 = {1};
        Object[] o2 = {2};
        Object[] o3 = {3};
        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);
        bookService.batchDelete(batchArgs);
    }

}