04-Spring5 JdbcTemplate

JdbcTemplate(概念和准备)

什么是JdbcTemplate

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

准备工作

引入相关的Jar包

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

新建数据库

代码语言：javascript复制

CREATE DATABASE `user_db` DEFAULT CHARACTER SET utf8mb4

新建Spring配置文件

代码语言：javascript复制

<?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接口

代码语言：javascript复制

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

public interface BookDao {
}

新建BookDao接口实现类

代码语言：javascript复制

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类

代码语言：javascript复制

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操作数据库(添加)

新建表

代码语言：javascript复制

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

新建实体类

代码语言：javascript复制

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

接口新增方法

代码语言：javascript复制

void add(User user);

实现类新增实现

代码语言：javascript复制

@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

代码语言：javascript复制

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

编写测试类

代码语言：javascript复制

@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);
}

执行结果

代码语言：javascript复制

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

查看数据库

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

编写BookDao

增加接口

代码语言：javascript复制

void update(User user);

void delete(User user);

实现接口

代码语言：javascript复制

@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

代码语言：javascript复制

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

编写测试类

代码语言：javascript复制

@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);
}

执行结果

修改

代码语言：javascript复制

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

删除

代码语言：javascript复制

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

JdbcTemplate操作数据库(查询)

查询返回某个值

编写BookDao

新增接口

代码语言：javascript复制

int selectCount();

实现接口

代码语言：javascript复制

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

编写BookService

代码语言：javascript复制

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

编写测试类

代码语言：javascript复制

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

执行结果

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

代码语言：javascript复制

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

查询返回对象

编写BookDao

新增接口

代码语言：javascript复制

User selectUserById(int id);

实现接口

代码语言：javascript复制

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

编写BookService

代码语言：javascript复制

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

编写测试类

代码语言：javascript复制

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

user类新增toString方法

代码语言：javascript复制

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

执行结果

代码语言：javascript复制

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

查询返回集合

编写BookDao

新增接口

代码语言：javascript复制

List selectUserList();

实现接口

代码语言：javascript复制

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

编写BookService

代码语言：javascript复制

public List selectUserList() {
    return bookDao.selectUserList();
}

编写测试类

代码语言：javascript复制

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

执行结果

代码语言：javascript复制

十二月 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

新增接口

代码语言：javascript复制

void batchAdd(List userList);

实现接口

代码语言：javascript复制

@Override
public void batchAdd(List 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

代码语言：javascript复制

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

编写测试类

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

代码语言：javascript复制

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

public User() {
}

测试类

代码语言：javascript复制

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

执行结果

代码语言：javascript复制

[1, 1, 1]

但是这里出现了一个小问题,中文乱码了

去数据库删除数据

修改Spring配置文件

代码语言：javascript复制

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

URL后面增加字符编码设置

再次测试

OK了

批量编辑

编写BookDao

新增接口

代码语言：javascript复制

void batchUpdate(List userList);

实现接口

代码语言：javascript复制

@Override
public void batchUpdate(List 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

代码语言：javascript复制

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

编写测试类

代码语言：javascript复制

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

执行结果

代码语言：javascript复制

[1, 1, 1]

批量删除

编写BookDao

新增接口

代码语言：javascript复制

void batchDelete(List userList);

实现接口

代码语言：javascript复制

@Override
public void batchDelete(List 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

代码语言：javascript复制

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

编写测试类

代码语言：javascript复制

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

执行结果

代码语言：javascript复制

[1, 1, 1]

数据库 sql 腾讯云测试服务 spring jdbc

0 人点赞