Spring学习笔记-JdbcTemplate

1. 什么是jdbcTemplate

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

2. 准备工作

(1)引入依赖

连接数据库mysql-connector-java-8.0.13,jar，spring-jdbc-5.2.6.RELEASE.jar，事务spring-tx-5.2.6.RELEASE.jar，整合其他框架spring-orm-5.2.6.RELEASE。jar

(2)在Spring配置文件中配置数据库连接池
经过几次报错，++MySQL8.0版++本应填写如下：

<!--数据库连接池-->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
        <property name="url" value="jdbc:mysql://127.0.0.1:3306/user_db?serverTimezone=UTC"/>
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
    </bean>

(3)配置JdbcTemplate对象，注入DataSourse

<!--JdbcTemplate对象-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <!--注入dataSourse,JdbcTemplate类里是通过set方法注入-->
        <property name="dataSource" ref="dataSource"></property>
    </bean>

(4)创建service类，创建dao类，在dao里注入jdbcTemplate对象

• 配置文件开启组件扫描

<!--开启组件扫描-->
    <context:component-scan base-package="com.company"></context:component-scan>

• Service类,注入dao

@Service //注解创建对象
public class BookService {
    //注入dao
    @Autowired
    private BookDao bookDao;
}

• Dao类，注入jdbcTemplate

@Repository //注解创建对象
public class BookDaoImpl implements BookDao{
    //注入jdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;
}

3. 使用jdbcTemplate进行操作

3.1 添加

(1)对应数据库表创建实体类

public class Book {
    private String userId;
    private String username;
    private String ustatus;

    public String getUserId() {
        return userId;
    }

    public String getUsername() {
        return username;
    }

    public String getUstatus() {
        return ustatus;
    }

    public void setUserId(String userId) {
        this.userId = userId;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public void setUstatus(String ustatus) {
        this.ustatus = ustatus;
    }
}

(2)编写service和dao

• 在dao里进行数据库添加操作，调用JdbcTemplate对象里update()方法实现,该方法可以实现增删改

update(String sql,Object... args):第一个参数为sql语句，第二个参数为可变参数，设置sql语句值

BookDaoImpl：

@Repository
public class BookDaoImpl implements BookDao{
    //注入jdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;
    //添加的方法
    @Override
    public void add(Book book) {
        //创建sql语句
        String sql="insert into t_book values(?,?,?)";
        //调用方法实现
        Object[] args={book.getUserId(),book.getUsername(),book.getUstatus()};
        int update=jdbcTemplate.update(sql,args);
        System.out.println(update);
    }
}

BookService:

@Service
public class BookService {
    //注入dao
    @Autowired
    private BookDao bookDao;

    //添加的方法
    public void addBook(Book book){
        bookDao.add(book);
    }
}

(3)测试

public class TestBook {
    @Test
    public void testJdbcTemplate(){
        ApplicationContext context=new ClassPathXmlApplicationContext("bean1.xml");
        BookService bookService=context.getBean("bookService", BookService.class);
        Book book=new Book();
        book.setUserId("1");
        book.setUsername("Java");
        book.setUstatus("a");
        bookService.addBook(book);
    }
}

3.2 修改和删除

依旧调用jdbcTemplate对象的update()方法：

• BookDaoImpl:

    //修改
    @Override
    public void updateBook(Book book) {
        String sql="update t_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);
    }

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

• BookService:

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

    //删除
    public void deleteBook(String id){
        bookDao.delete(id);
    }

• 测试:

    @Test
    public void testupadte(){
        ApplicationContext context=new ClassPathXmlApplicationContext("bean1.xml");
        BookService bookService=context.getBean("bookService", BookService.class);
        Book book=new Book();
        book.setUserId("1");
        book.setUsername("c plus plus");
        book.setUstatus("b");
        bookService.updateBook(book);
    }

    @Test
    public void testdelete(){
        ApplicationContext context=new ClassPathXmlApplicationContext("bean1.xml");
        BookService bookService=context.getBean("bookService", BookService.class);
        String id="1";
        bookService.deleteBook(id);
    }

3.3 查询

• 查询返回某个值

查询里面有多少条记录：

使用queryForObject(String sql,Class<T> requeiredType)方法,第一个参数为sql语句，第二个参数为返回类型Class.
BookDaoImpl:

    //查询表中记录数
    @Override
    public int selectCount() {
        String sql="select count(*) from t_book";
        Integer count=jdbcTemplate.queryForObject(sql,Integer.class);
        return count;
    }

• 查询返回对象
  使用queryForObject(String sql,RowMapper<T> rowMapper,Object... args)方法，第一个参数为sql语句；第二个参数是接口，返回不同类型的数据，使用该接口里的实现类可实现数据封装；第三个参数为sql语句值。
  BookDaoImpl:

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

• 查询返回集合
  使用query(String sql,RowMapper<T> rowMapper,Object... args)方法
  BookDaoImpl:

    //查询返回集合
    @Override
    public List<Book> findAllBook() {
        String sql="select * from t_book";
        List<Book> bookList=jdbcTemplate.query(sql,new BeanPropertyRowMapper<Book>(Book.class));
        return bookList;
    }

3.4 批量操作

批量操作：操作表里面的多记录

使用JdbcTemplate对象的batchUpdate(String sql,List<Object[]> batchArgs)实现批量操作。

• 批量添加：
  BookDaoImpl:

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

BookService:

//批量添加
    public void batchAdd(List<Object[]> batchArgs){
        bookDao.batchAddBook(batchArgs);
    }

测试：

    @Test
    public void testBatchAdd(){
        ApplicationContext context=new ClassPathXmlApplicationContext("bean1.xml");
        BookService bookService=context.getBean("bookService", BookService.class);
        List<Object[]> batchArgs=new ArrayList<>();
        Object[] o1={"4","python","d"};
        Object[] o2={"5","php","e"};
        Object[] o3={"6","mysql","f"};
        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);
        bookService.batchAdd(batchArgs);
    }

• 批量修改和删除
  BookDaoImpl:

    //批量修改
    @Override
    public void batchUpdateBook(List<Object[]> batchArgs) {
        String sql="update t_book set username=?,ustatus=? where user_id=?";
        int[] ints=jdbcTemplate.batchUpdate(sql,batchArgs);
        System.out.println(Arrays.toString(ints));
    }

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

BookService:

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

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

测试：

    @Test
    public void testBatchUpdate(){
        ApplicationContext context=new ClassPathXmlApplicationContext("bean1.xml");
        BookService bookService=context.getBean("bookService", BookService.class);
        List<Object[]> batchArgs=new ArrayList<>();
        Object[] o1={"html","asas","4"};
        Object[] o2={"css","sss","5"};
        Object[] o3={"js","sd","6"};
        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);
        bookService.batchupadte(batchArgs);
    }

    @Test
    public void testBatchDelete(){
        ApplicationContext context=new ClassPathXmlApplicationContext("bean1.xml");
        BookService bookService=context.getBean("bookService", BookService.class);
        List<Object[]> batchArgs=new ArrayList<>();
        Object[] o1={"4"};
        Object[] o2={"5"};
        batchArgs.add(o1);
        batchArgs.add(o2);
        bookService.batchdelete(batchArgs);
    }