Spring5学习笔记(3):JdbcTemplate

1 JDBCTemple概念

Spring框架对JDBC进行了一个封装，JDBCTemple可以更方便对数据库进行操作

2 JDBCTemple的相关准备

1.引入相关jar包

2.在spring的xml配置文件中配置数据库连接池

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

3.配置JdbcTemplate对象，注入DataSource

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

4.创建service类、dao类，在dao中创建jdbcTemplate对象
开启组件扫描

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

Service：

package com.wzc.spring.service;

import com.wzc.spring.dao.BookDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

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

Dao：

package com.wzc.spring.dao;

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

@Repository
public class BookDaoImpl implements BookDao{

    //注入JDbctemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;
}

3 JDBCTemple实现添加功能

创建表：

create table t_book(
	user_id bigint(20) primary key,
	username varchar(100) not null,
	ustates varchar(50) not null
);

1.对应数据库中的表创建实体类

package com.wzc.spring.entity;

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

    public String getUserId() {
        return userId;
    }

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

    public String getUsername() {
        return username;
    }

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

    public String getUstate() {
        return ustate;
    }

    public void setUstate(String ustate) {
        this.ustate = ustate;
    }
}

2.编写service和dao
（1）dao中实现向数据库中进行添加的操作
（2）调用jdbcTemplate中的update实现添加操作

package com.wzc.spring.dao;

import com.wzc.spring.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class BookDaoImpl implements BookDao{

    //注入JDbctemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;

    //添加的方法
    @Override
    public void add(Book book) {
        //1.创建sql语句
        String sql = "insert into t_book values(?,?,?)";
        //2.调用实现方法
        Object[] args = {book.getUserId(), book.getUsername(), book.getUstate()};
        int update = jdbcTemplate.update(sql, args);
        System.out.println(update);
    }
}

3.测试类

    @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.setUstate("a");

        bookService.addBook(book);
    }

查看数据库中的结果

4 JDBCTemple实现修改和删除功能

和上面的添加操作类似，都用到JdbcTemplate 中的update函数
BookDaoImpl.java:

    //修改
    @Override
    public void updateB(Book book) {
        String sql = "update t_book set username=?, ustates=? where user_id=?";
        Object[] args = {book.getUsername(), book.getUstate(), book.getUserId()};
        int update = jdbcTemplate.update(sql, args);
        System.out.println(update + "修改");
    }

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

5 JDBCTemple实现查询功能

5.1 查询返回的某个值

实现功能：查询表中一共有多少记录
使用jdbcTemplate中的querForObject方法实现操作
BookDaoImpl.java:

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

5.2 查询返回表中某一对象

BookDaoImpl.java:

    //查询返回对象
    @Override
    public Book findBookInfo(String id) {
        String sql = "select user_id userId,username,ustates as ustate from t_book where user_id=?";
        //调用方法
        Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
        return book;
    }

5.3 查询返回表中全部内容的集合

BookDaoImpl.java:

    @Override
    public List<Book> findAllBook() {
        String sql = "select user_id userId,username,ustates as ustate from t_book";
        List<Book> booklist = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
        return booklist;
    }

6 JDBCTemple实现批量操作

6.1 批量插入操作

BookDaoImpl.java:

    //批量添加
    @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));
    }

测试类：

        //批量添加
        List<Object[]> batchArgs = new ArrayList<>();
        Object[] o1 = {"9","java","a"};
        Object[] o2 = {"10","c++","c"};
        Object[] o3 = {"11","mysql","b"};
        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);
        //调用批量添加
        bookService.batchAdd(batchArgs);

6.2 批量修改操作

BookDaoImpl.java:

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

测试类

        //批量修改
        List<Object[]> batchArgs = new ArrayList<>();
        Object[] o1 = {"1","11","1"};
        Object[] o2 = {"2","22","2"};
        Object[] o3 = {"3","3","3"};
        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);
        //调用批量添加
        bookService.batchUpdate(batchArgs);

6.3 批量删除操作

BookDaoImpl.java:

    //批量删除
    @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));
    }

测试类：

        //批量删除
        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);