JdbcTemplate(概念和准备)

什么是JdbcTemplate

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

准备工作

引入相关的Jar包

因为druid的包之前引入过,所以引入下面的四个就可以了

新建数据库

CREATE DATABASE `user_db` DEFAULT CHARACTER SET utf8mb4

新建Spring配置文件

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:aop="http://www.springframework.org/schema/aop"
       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
       http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd
">
        <!--  开启注解扫描  -->
    <context:component-scan base-package="com.dance.spring.learn.jdbc"/>
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://localhost:3306/user_db" />
        <property name="username" value="root" />
        <property name="password" value="123456" />
    </bean>
      <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource" />
    </bean>
</beans>

新建BookDao接口

package com.dance.spring.learn.jdbc.dao;

public interface BookDao {
}

新建BookDao接口实现类

package com.dance.spring.learn.jdbc.dao.impl;

import com.dance.spring.learn.jdbc.dao.BookDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class BookDaoImpl implements BookDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

}

新建Service类

package com.dance.spring.learn.jdbc.service;

import com.dance.spring.learn.jdbc.dao.BookDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class BookService {

    @Autowired
    private BookDao bookDao;

}

JdbcTemplate操作数据库(添加)

新建表

create table user (
    user_id varchar(20) PRIMARY key,
    username varchar(100) not null,
    ustatus varchar(50) not null
)

新建实体类

package com.dance.spring.learn.jdbc.entity;

public class User {

    private String userId;
    private String userName;
    private String ustatus;

    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 getUstatus() {
        return ustatus;
    }

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

编写BookDao

接口新增方法

void add(User user);

实现类新增实现

@Override
public void add(User user) {
    int update = jdbcTemplate.update("insert into user values(?,?,?)", user.getUserId(), user.getUserName(), user.getUstatus());
    if(update > 0){
        System.out.println("新增成功");
    }else{
        System.out.println("新增失败");
    }
}

编写BookService

public void add(User user){
    bookDao.add(user);
}

编写测试类

@Test
public void testAdd(){
    ClassPathXmlApplicationContext classPathXmlApplicationContext = new ClassPathXmlApplicationContext("Spring-jdbc.xml");
    BookService bookService = classPathXmlApplicationContext.getBean("bookService", BookService.class);
    User user = new User();
    user.setUserId("1");
    user.setUserName("flower");
    user.setUstatus("1");
    bookService.add(user);
}

执行结果

十二月 11, 2021 4:47:14 下午 com.alibaba.druid.support.logging.JakartaCommonsLoggingImpl info
信息: {dataSource-1} inited
新增成功

查看数据库

JdbcTemplate操作数据库(修改和删除)

编写BookDao

增加接口

void update(User user);

void delete(User user);

实现接口

@Override
public void update(User user) {
    int update = jdbcTemplate.update("update user set username = ?,ustatus = ? where user_id = ?", user.getUserName(), user.getUstatus(), user.getUserId());
    if(update > 0){
        System.out.println("修改成功");
    }else{
        System.out.println("修改失败");
    }
}

@Override
public void delete(User user) {
    int update = jdbcTemplate.update("delete from user where user_id = ?", user.getUserId());
    if(update > 0){
        System.out.println("删除成功");
    }else{
        System.out.println("删除失败");
    }
}

编写BookService

public void update(User user){
    bookDao.update(user);
}
public void delete(User user){
    bookDao.delete(user);
}

编写测试类

@Test
public void testUpdate(){
    BookService bookService = classPathXmlApplicationContext.getBean("bookService", BookService.class);
    User user = new User();
    user.setUserId("1");
    user.setUserName("dance");
    user.setUstatus("2");
    bookService.update(user);
}

@Test
public void testDelete(){
    BookService bookService = classPathXmlApplicationContext.getBean("bookService", BookService.class);
    User user = new User();
    user.setUserId("1");
    bookService.delete(user);
}

执行结果

修改

十二月 11, 2021 6:13:12 下午 com.alibaba.druid.support.logging.JakartaCommonsLoggingImpl info
信息: {dataSource-1} inited
修改成功

删除

十二月 11, 2021 6:13:40 下午 com.alibaba.druid.support.logging.JakartaCommonsLoggingImpl info
信息: {dataSource-1} inited
删除成功

JdbcTemplate操作数据库(查询)

查询返回某个值

编写BookDao

新增接口

int selectCount();

实现接口

@Override
public int selectCount() {
    return jdbcTemplate.queryForObject("select count(1) from user",Integer.class);
}

编写BookService

public int selectCount(){
    return bookDao.selectCount();
}

编写测试类

@Test
public void testSelectCount(){
    BookService bookService = classPathXmlApplicationContext.getBean("bookService", BookService.class);
    int i = bookService.selectCount();
    System.out.println("总用户数为:"+i);
}

执行结果

执行之前先执行一下add否则没有数据就是0

十二月 11, 2021 6:30:05 下午 com.alibaba.druid.support.logging.JakartaCommonsLoggingImpl info
信息: {dataSource-1} inited
总用户数为:1

查询返回对象

编写BookDao

新增接口

User selectUserById(int id);

实现接口

@Override
public User selectUserById(int id) {
    return jdbcTemplate.queryForObject("select * from user where user_id = ?", new BeanPropertyRowMapper<>(User.class),id);
}

编写BookService

public User selectUserById(int id) {
    return bookDao.selectUserById(id);
}

编写测试类

@Test
public void testSelectUserById(){
    BookService bookService = classPathXmlApplicationContext.getBean("bookService", BookService.class);
    User user = bookService.selectUserById(1);
    System.out.println(user);
}

user类新增toString方法

@Override
public String toString() {
    return "User{" +
        "userId='" + userId + '\'' +
        ", userName='" + userName + '\'' +
        ", ustatus='" + ustatus + '\'' +
        '}';
}

执行结果

十二月 11, 2021 6:37:39 下午 com.alibaba.druid.support.logging.JakartaCommonsLoggingImpl info
信息: {dataSource-1} inited
User{userId='1', userName='flower', ustatus='1'}

查询返回集合

编写BookDao

新增接口

List<User> selectUserList();

实现接口

@Override
public List<User> selectUserList() {
    return jdbcTemplate.query("select * from user", new BeanPropertyRowMapper<>(User.class));
}

编写BookService

public List<User> selectUserList() {
    return bookDao.selectUserList();
}

编写测试类

@Test
public void testSelectUserList(){
    BookService bookService = classPathXmlApplicationContext.getBean("bookService", BookService.class);
    List<User> users = bookService.selectUserList();
    System.out.println(users);
}

执行结果

十二月 11, 2021 6:54:41 下午 com.alibaba.druid.support.logging.JakartaCommonsLoggingImpl info
信息: {dataSource-1} inited
[User{userId='1', userName='flower', ustatus='1'}, User{userId='2', userName='dance', ustatus='1'}]

JdbcTemplate操作数据库(批量操作)

批量新增

编写BookDao

新增接口

void batchAdd(List<User> userList);

实现接口

@Override
public void batchAdd(List<User> userList) {
    List<Object[]> collect = userList.stream().map(x -> new Object[]{x.getUserId(), x.getUserName(), x.getUstatus()}).collect(Collectors.toList());
    int[] ints = jdbcTemplate.batchUpdate("insert into user values(?,?,?)", collect);
    System.out.println(Arrays.toString(ints));
}

编写BookService

public void batchAdd(List<User> userList) {
    bookDao.batchAdd(userList);
}

编写测试类

user类增加全参数构造和无参数构造

public User(String userId, String userName, String ustatus) {
    this.userId = userId;
    this.userName = userName;
    this.ustatus = ustatus;
}

public User() {
}

测试类

@Test
public void testBatchAdd(){
    BookService bookService = classPathXmlApplicationContext.getBean("bookService", BookService.class);
    List<User> userList = Arrays.asList(
        new User("3","张三","1"),
        new User("4","李四","2"),
        new User("5","王五","3")
    );
    bookService.batchAdd(userList);
}

执行结果

[1, 1, 1]

但是这里出现了一个小问题,中文乱码了
去数据库删除数据
修改Spring配置文件

jdbc:mysql://localhost:3306/user_db?useSSL=false&amp;characterEncoding=utf-8&amp;autoReconnect=true

URL后面增加字符编码设置
再次测试

OK了

批量编辑

编写BookDao

新增接口

void batchUpdate(List<User> userList);

实现接口

@Override
public void batchUpdate(List<User> userList) {
    List<Object[]> collect = userList.stream().map(x -> new Object[]{x.getUserName(), x.getUstatus(), x.getUserId()}).collect(Collectors.toList());
    int[] ints = jdbcTemplate.batchUpdate("update user set username=?,ustatus=? where user_id=?", collect);
    System.out.println(Arrays.toString(ints));
}

编写BookService

public void batchUpdate(List<User> userList) {
    bookDao.batchUpdate(userList);
}

编写测试类

@Test
public void testBatchUpdate(){
    BookService bookService = classPathXmlApplicationContext.getBean("bookService", BookService.class);
    List<User> userList = Arrays.asList(
        new User("3","张三1","11"),
        new User("4","李四2","22"),
        new User("5","王五3","33")
    );
    bookService.batchUpdate(userList);
}

执行结果

[1, 1, 1]

批量删除

编写BookDao

新增接口

void batchDelete(List<User> userList);

实现接口

@Override
public void batchDelete(List<User> userList) {
    List<Object[]> collect = userList.stream().map(x -> new Object[]{x.getUserId()}).collect(Collectors.toList());
    int[] ints = jdbcTemplate.batchUpdate("delete from user where user_id=?", collect);
    System.out.println(Arrays.toString(ints));
}

编写BookService

public void batchDelete(List<User> userList) {
    bookDao.batchDelete(userList);
}

编写测试类

@Test
public void testBatchDelete(){
    BookService bookService = classPathXmlApplicationContext.getBean("bookService", BookService.class);
    List<User> userList = Arrays.asList(
        new User("3","张三1","11"),
        new User("4","李四2","22"),
        new User("5","王五3","33")
    );
    bookService.batchDelete(userList);
}

执行结果

[1, 1, 1]

完结撒花花

04-Spring5 JdbcTemplate

JdbcTemplate(概念和准备)

什么是JdbcTemplate

准备工作

引入相关的Jar包

新建数据库

新建Spring配置文件

新建BookDao接口

新建BookDao接口实现类

新建Service类

JdbcTemplate操作数据库(添加)

新建表

新建实体类

编写BookDao

编写BookService

编写测试类

执行结果

JdbcTemplate操作数据库(修改和删除)

编写BookDao

编写BookService

编写测试类

执行结果

JdbcTemplate操作数据库(查询)

查询返回某个值

编写BookDao

编写BookService

编写测试类

执行结果

查询返回对象

编写BookDao

编写BookService

编写测试类

执行结果

查询返回集合

编写BookDao

编写BookService

编写测试类

执行结果

JdbcTemplate操作数据库(批量操作)

批量新增

编写BookDao

编写BookService

编写测试类

执行结果

批量编辑

编写BookDao

编写BookService

编写测试类

执行结果

批量删除

编写BookDao

编写BookService

编写测试类

执行结果