[springboot——02]JDBCTemplate操作MySQL

1. 配置基础依赖

mysql 驱动版本看情况【我的mysql版本为8.0.26】

<!--JDBCTemplate 依赖-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- MySql 依赖-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.26</version>
</dependency>

配置数据库的连接信息，在 application.yaml 配置文件中配置

官方配置：Common Application Properties (spring.io)

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/[数据库表]

2.新建实体类

新建实体类 User ,实现 RowMapper 接口，该接口重写的 mapRow 方法为了实体字段和数据表字段映射（对应）。

package com.example.demo.entity;

import lombok.*;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author: fly
 * @Date: 2023-03-19 20:14
 * @Description: 用户实体类
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
@ToString
public class User implements RowMapper<User> {

    private int id;
    private String username;
    private String password;

    @Override
    public User mapRow(ResultSet rs, int rowNum) throws SQLException {
        User user = new User();
        user.setId(rs.getInt("id"));
        user.setUsername(rs.getString("username"));
        user.setPassword(rs.getString("password"));
        return user;
    }
}

3.操作数据

JDBCTemplate 提供3个操作数据的方法：

execute 直接执行 sql 语句
update 进行新增、修改、删除操作
query 查询操作

创建数据表

CREATE TABLE `t_user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `username` varchar(100) DEFAULT NULL,
  `password` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表'

插入一条数据

INSERT INTO t_user VALUES(NULL,"admin","123456");

添加控制器 UserController 【均在浏览器测试通过！】

添加用户 /user/save
查询用户 /user/get/1
查询所有用户 /user/get 或者 /user/getAllUsers
修改用户 /user/update/1
删除用户 /user/delete/1

package com.fly.demo.controller;

import com.fly.demo.entity.User;
import lombok.extern.log4j.Log4j2;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
 * @author: fly
 * @Date: 2023-03-20 17:05
 * @Description: 用户控制器
 */
@RestController
@RequestMapping("/user")
@Log4j2
public class UserController {

    private JdbcTemplate jdbcTemplate;

    @Autowired
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * 添加用户
     */
    @GetMapping("/save")
    public void saveUser() {
        String sql = "INSERT INTO t_user VALUES(NULL,?,?)";
        User user = new User(null,"209110407","fengluyu");
        int row = jdbcTemplate.update(sql,user.getUsername(),user.getPassword());
        log.info("保存用户成功!保存个数: " + row);
    }

    /**
     * 获取单个用户
     * @param id 用户id
     * @return 返回用户
     */
    @GetMapping("/get/{id}")
    public User getUser(@PathVariable int id) {
        String sql = "SELECT id,username,password FROM t_user WHERE id = ?";
        List<User> userList = jdbcTemplate.query(sql, new User(), id);
        User user = null;
        if (userList.size() > 0) {
            user = userList.get(0);
        }
        return user;
    }

    /**
     * 返回所有用户
     * @return 所有用户列表
     */
    @GetMapping("/get")
    public List<User> getUsers() {
        String sql = "SELECT id,username,password FROM t_user";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
    }

    /**
     * 获取所有用户
     * @return 所有用户列表
     */
    @GetMapping("/getAllUsers")
    public List<User> getAllUser() {
        String sql = "SELECT id,username,password FROM t_user";
        return jdbcTemplate.query(sql,new User());
    }

    @GetMapping("/update/{id}")
    public void updateUser(@PathVariable int id) {
        String sql = "UPDATE t_user SET password = ? WHERE id = ?";
        int row = jdbcTemplate.update(sql,"87am23",id);
        log.info("修改用户(id:" +id + ") 操作: " + row);
    }

    @GetMapping("/delete/{id}")
    public void deleteUser(@PathVariable int id) {
        String sql = "DELETE FROM t_user WHERE id = ?";
        int row = jdbcTemplate.update(sql,id);
        log.info("删除用户(id: " + id + ") 操作: " + row);
    }
}