Spring Boot整合JDBC实现对数据库的增删改查

• 本人阶段性学习的记录
• 本人小白，有错误请指出，有更好的解决方法，请在评论区评论，一起进步
  

环境配置准备

添加pom依赖

1. 使用idea脚手架创建项目，添加web启动器、JDBC以及thymeleaf
2. 根据自己的数据库版本添加不同的数据库启动坐标(我的数据库版本是8.0.32，查询地址)
3. 然后设置druid数据源为阿里的

    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
    </dependency>
    <!--druid数据源依赖-->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.1.18</version>
    </dependency>
    <!--      启动热部署-->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-devtools</artifactId>
        <optional>true</optional> <!-- 这个需要为 true 热部署才有效 -->
        <scope>true</scope>
    </dependency>
    <!--添加数据校验-->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-validation</artifactId>
    </dependency>

配置核心配置文件

将配置文件名后缀改为yml(感觉yml更加简洁，看起来更加舒服)

#配置数据库相关设置
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: 123456
    url: jdbc:mysql://localhost:3306/shoppingdb?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT
    hikari:
      auto-commit: true
  #设置页面缓存为false
  thymeleaf:
    cache: false

数据校验配置提示配置

1. 文件名必须是ValidationMessages.properties
2. 提示使用中文需要转换，可以使用jdk自带的工具native2ascii.exe进行转换

#数据校验不符合提示信息
userName.notnull=\u8bf7\u8f93\u51652\u52306\u4f4d\u7528\u6237\u540d
userPwd.notnull=\u7528\u6237\u5bc6\u7801\u4e0d\u80fd\u4e3a\u7a7a
stateId.notnull=\u7528\u6237\u7f16\u53f7\u4e0d\u80fd\u4e3a\u7a7a

创建config层

1. 创建configuration层
2. 新建属性配置类 JdbcProperties和JdbcConfiguration类

配置JDBC的属性配置类

package com.example.myspringboot.config;
    /*
    * Jdbc配置属性类
    * */
public class JdbcProperties {
    private String driverClassName;
    private String username;
    private String password;
    private String url;

    public JdbcProperties() {
    }
    public JdbcProperties(String driverClassName, String username, String password, String url) {
        this.driverClassName = driverClassName;
        this.username = username;
        this.password = password;
        this.url = url;
    }

    public String getDriverClassName() {
        return driverClassName;
    }

    public void setDriverClassName(String driverClassName) {
        this.driverClassName = driverClassName;
    }

    public String getUsername() {
        return username;
    }

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

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }
}

数据源JDBC配置信息

package com.example.myspringboot.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

/*
    * 数据源的Jdbc信息
    * */
@Configuration
public class JdbcConfiguration {
    @Bean
    //匹配以spring.datasource开头的配置信息
    //有多种方法，可自己去探索
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource getDataSource(){
        return new DruidDataSource();
    }

}

创建pojo层

编写实体类

1. 添加数据校验

package com.example.myspringboot.pojo;
    /*
    *编写实体类
    */
import org.hibernate.validator.constraints.Length;

import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;

public class Users {
    private Integer userId;
    //设置用户名称在指定的长度范围内
    @Length(min = 2,max = 6,message = "{userName.notnull}")
    private String userName;
    //校验数据不能为空
    @NotBlank(message = "{userPwd.notnull}")
    private String userPwd;
    @NotNull(message = "{stateId.notnull}")
    private Integer stateId;
    public Users() {
    }

    public Users(Integer userId, String userName, String userPwd, Integer stateId) {
        this.userId = userId;
        this.userName = userName;
        this.userPwd = userPwd;
        this.stateId = stateId;
    }

    public Integer getUserId() {
        return userId;
    }

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

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getUserPwd() {
        return userPwd;
    }

    public void setUserPwd(String userPwd) {
        this.userPwd = userPwd;
    }
}

创建控制层

1. 创建controller层
2. 创建页面跳转类PageController和用户控制类UsersController

跳转页面

1. 添加数据校验所需注解@ModelAttribute(“user”) Users users

package com.example.myspringboot.controller;
import com.example.myspringboot.pojo.Users;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;

/*
    * 跳转页面
    * */
@Controller
public class PageController {
    @RequestMapping("/{page}")
    public String showPage(@PathVariable String page ,@ModelAttribute("user") Users users){
        return page;
    }

}

用户控制

package com.example.myspringboot.controller;

import com.example.myspringboot.pojo.Users;
import com.example.myspringboot.service.UsersService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.validation.BindingResult;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import java.util.List;

/*
    * 用户管理
    * */
@Controller
@RequestMapping("/user")
public class UsersController {
    @Autowired
    private UsersService usersService;
    /*
    * 添加用户
    * */
    @PostMapping("/addUsers")
    public String addUsers (@ModelAttribute("user") @Validated Users users, BindingResult bindingResult){
        try{
            if (bindingResult.hasErrors()){
                return "addUsers";
            }
            this.usersService.addUsers(users);
        }catch (Exception e){
            e.printStackTrace();
            return "error";
        }
        return "redirect:/addOK";
    }
    /*
    * 查询所有用户
    * */
    @GetMapping("/findUserAll")
    public String findUserAll(Model model){
        List<Users> list=null;
        try{
            list=this.usersService.findUsersAll();
            model.addAttribute("list",list);
        }catch (Exception e){
            e.printStackTrace();
            return "error";
        }
        return "showUsersAll";
    }
    /*
    * 查询单个用户
    * */
    @PostMapping("/findUser")
    public String findUser(String userName,Model model){
        Users user=null;
        try{
            System.out.println(userName);
            user = this.usersService.findUser(userName);
            //System.out.println(user.getUserId());
            model.addAttribute("user",user);
        }catch (Exception e){
            e.printStackTrace();
            return "error";
        }
        return "showUser";
    }
    /*
    * 更新用户预更新查询
    * */
    @GetMapping("/preUpdateUser")
    public String perUpdateUser(Integer id,Model model){
        try{
            Users user=this.usersService.findUserById(id);
            model.addAttribute("user",user);

        }catch (Exception e){
            e.printStackTrace();
            return "error";
        }
        return "updateUser";
    }
    /*
    *更新用户
    * */
    @PostMapping("/updateUser")
    public String updateUser(Users users){
        try{
            System.out.println(users.getUserId());
            System.out.println(users.getUserName());
            this.usersService.updateUser(users);
        }catch (Exception e){
            e.printStackTrace();
            return "error";
        }
        return "redirect:/upOK";
    }
    /*
    * 删除用户
    * */
    @GetMapping("/deleteUsers")
    public String deleteUsers(Integer id){
        try{
            this.usersService.dropUsers(id);
        }catch (Exception e){
            e.printStackTrace();
            return "error";
        }
        return "redirect:/delOK";
    }

}

创建业务层

1. 创建service包
2. 创建UsersService接口以及Iml包下的接口实现类UsersServiceIml

UsersService接口

package com.example.myspringboot.service;

import com.example.myspringboot.pojo.Users;

import java.util.List;

public interface UsersService {
    public void addUsers(Users users);
    List<Users> findUsersAll();
    Users findUser(String userName);
    Users findUserById(Integer userid);
    public void updateUser(Users users);
    void dropUsers(Integer id);
}

业务层接口实现类

1. 注意添加@Service注解，以及DDL语句事务管理注解

package com.example.myspringboot.service.Iml;

import com.example.myspringboot.dao.UsersDao;
import com.example.myspringboot.pojo.Users;
import com.example.myspringboot.service.UsersService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/*
    * 业务层
    * */
@Service
public class UsersServiceIml implements UsersService {

    @Autowired
    private UsersDao usersDao;
    /*
     * 添加用户
     * */
    @Override
    //DDL语句添加事务
    @Transactional
    public void addUsers(Users users) {
        this.usersDao.installUsers(users);
    }
    /*
     * 查询所有用户
     * */
    @Override
    public List<Users> findUsersAll() {
        return usersDao.selectUsersAll();
    }
    /*
    * 按userName查询
    * */
    @Override
    public Users findUser(String userName) {
        return this.usersDao.selectUser(userName);
    }
    /*
    * 更新用户预查询
    * */
    @Override
    public Users findUserById(Integer userid) {
        return this.usersDao.selectByUsers(userid);
    }
    /*
    * 更新用户
    * */

    @Override
    @Transactional
    public void updateUser(Users users) {
        this.usersDao.updateUser(users);
    }
    /*
    * 删除用户
    * */

    @Override
    @Transactional
    public void dropUsers(Integer id) {
        this.usersDao.deleteUsers(id);
    }
}

dao层

1. 创建dao层
2. 创建UsersDao接口以及Iml包下的UserDaoIml实现类

UsersDao接口

package com.example.myspringboot.dao;
import com.example.myspringboot.pojo.Users;

import java.util.List;

public interface UsersDao {
    public void installUsers(Users users);
    List<Users> selectUsersAll();
    Users selectUser(String userName);
    Users selectByUsers(Integer userid);
    void updateUser(Users users);
    void deleteUsers(Integer id);
}

UsersDao实现类

package com.example.myspringboot.dao.Iml;

import com.example.myspringboot.dao.UsersDao;
import com.example.myspringboot.pojo.Users;
import org.apache.catalina.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/*
*
* 持久层
* */
@Repository
public class UsersDaoIml implements UsersDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    /*
    * 添加用户
    * */
    @Override
    public void installUsers(Users users) {
        String sql="insert into users(UserId,UserName,UserPwd,StateId) values (?,?,?,?)";
        this.jdbcTemplate.update(sql,users.getUserId(),users.getUserName(),users.getUserPwd(),users.getStateId());
    }
    /*
    * 查询所有用户
    * */

    @Override
    public List<Users> selectUsersAll() {
        String sql="select * from users";
        return jdbcTemplate.query(sql, new RowMapper<Users>() {
            @Override
            public Users mapRow(ResultSet rs, int rowNum) throws SQLException {
               Users users=new Users();
               users.setUserId(rs.getInt("userId"));
               users.setUserName(rs.getString("userName"));
               users.setUserPwd(rs.getString("userPwd"));
               users.setStateId(rs.getInt("stateId"));
               return users;
            }
        });
    }
    /*
    * 查询单个用户
    * */

    @Override
    public Users selectUser(String userName) {
        String sql="select * from users where UserName = ?";
        Object[] arr=new Object[]{userName};
        Users user=new Users();
        this.jdbcTemplate.query(sql, arr, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {

                user.setUserName(rs.getString("userName"));
                user.setUserId(rs.getInt("userId"));
                user.setUserPwd(rs.getString("userPwd"));
                user.setStateId(rs.getInt("stateId"));
            }
        });
          return user;
    }

    @Override
    public Users selectByUsers(Integer userid) {
        String sql="select * from users where UserId=?";
        Object[]arr=new Object[]{userid};
        Users users=new Users();
        this.jdbcTemplate.query(sql, arr, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                users.setUserId(rs.getInt("userId"));
                users.setUserName(rs.getString("userName"));
                users.setUserPwd(rs.getString("userPwd"));
                users.setStateId(rs.getInt("stateId"));
            }
        });
        return users;
    }
    /*
    * 更新用户
    * */
    @Override
    public void updateUser(Users users) {
        String sql="update users set UserName=?,UserPwd=?,StateId=? where UserId=?";
        this.jdbcTemplate.update(sql,users.getUserName(),users.getUserPwd(),users.getStateId(),users.getUserId());
    }
    /*
    * 删除用户
    * */

    @Override
    public void deleteUsers(Integer id) {
        String sql="delete from users where UserId=?";
        this.jdbcTemplate.update(sql,id);
    }
}

页面的编写

1. 需要在templates里面创建HTML文件
2. 页面名称必须与UsersController对应方法返回的值相同，不然会报模板错误，找不到对应的模板
3. 成功的页面可以用一个，可以不用多个页面
4. <link rel="shortcut icon" href="../resources/favicon.ico" th:href="@{/static/favicon.ico}"/>
   可以防止报错500，不加也不会影响到运行
5. xmlns:th="http://www.thymeleaf.org"不加会影响thymeleaf提示

添加页面

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
    <head>
        <meta charset="UTF-8">
        <title>添加用户</title>
        <link rel="shortcut icon" href="../resources/favicon.ico" th:href="@{/static/favicon.ico}"/>
    </head>
    <body>
        <form th:action="@{user/addUsers}" method="post">
            <label>
                姓名:<input type="text" name="userName" /><span th:errors="${user.userName}" style="color: red"></span><br/>
                密码:<input type="text" name="userPwd"/><span th:errors="${user.userPwd}" style="color: red"></span><br/>
                编号:<input type="text" name="stateId"/><span th:errors="${user.stateId}" style="color: red"></span> <br/>
                <input type="submit" value="OK"/>
            </label>
        </form>
        <a th:href="@{/user/findUserAll}">
            <input type="submit"  th:value="查询全部用户">
        </a>
        <form th:action="@{/user/findUser}" method="post">
            <label>
                姓名或者编号:<input type="text" name="userName" /><br/>
                <input type="submit"  th:value="查询单个用户">
            </label>
        </form>
    </body>
</html>

添加成功页面

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
    <head>
        <meta charset="UTF-8">
        <title>添加成功</title>
        <link rel="shortcut icon" href="../resources/favicon.ico" th:href="@{/static/favicon.ico}"/>
    </head>
    <body>
        <h1 style="color: green">添加成功</h1>
        <a th:href="@{/addUsers}">返回</a>
    </body>
</html>

所有用户查询页面

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
    <head>
        <meta charset="UTF-8">
        <title>查询所有用户</title>
        <link rel="shortcut icon" href="../resources/favicon.ico" th:href="@{/static/favicon.ico}"/>
        <style>
            table {
                border-collapse: collapse;
                margin: auto;
            }

            table, th, td {
                border: 1px solid black;
            }
        </style>
    </head>
    <body>
        <table >
            <tr>
                <th>用户id</th>
                <th>用户姓名</th>
                <th>用户密码</th>
                <th>用户编号</th>
                <th>操作</th>
            </tr>
            <tr th:each="u:${list}">
                <td th:text="${u.userId}"></td>
                <td th:text="${u.userName}"></td>
                <td th:text="${u.userPwd}"></td>
                <td th:text="${u.stateId}"></td>
                <td>
                    <a th:href="@{/user/preUpdateUser(id=${u.userId})}">修改</a>
                    <a th:href="@{/user/deleteUsers(id=${u.userId})}">删除</a>
                </td>
            </tr>
        </table>
    </body>
</html>

更新页面

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
    <head>
        <meta charset="UTF-8">
        <title>更新用户</title>
        <link rel="shortcut icon" href="../resources/favicon.ico" th:href="@{/static/favicon.ico}"/>
    </head>
    <body>
      <form th:action="@{/user/updateUser}" method="post">
        <label>
            <input type="hidden" name="userId" th:value="${user.userId}">
          姓名:<input type="text" name="userName" th:value="${user.userName}"/><br/>
          密码:<input type="text" name="userPwd" th:value="${user.userPwd}"/><br/>
          编号:<input type="text" name="stateId" th:value="${user.stateId}"/> <br/>
          <input type="submit" value="OK"/>
        </label>
      </form>
    </body>
</html>

更新成功

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
    <head>
        <meta charset="UTF-8">
        <title>Title</title>
      <link rel="shortcut icon" href="../resources/favicon.ico" th:href="@{/static/favicon.ico}"/>
    </head>
    <body>
      <h1 style="color: green">修改成功</h1>
      <a th:href="@{/user/findUserAll}">返回</a>
    </body>
</html>

单个用户查询

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
    <head>
        <meta charset="UTF-8">
        <title>查询用户</title>
      <link rel="shortcut icon" href="../resources/favicon.ico" th:href="@{/static/favicon.ico}"/>
        <style>
            table {
                border-collapse: collapse;
                margin: auto;
            }

            table, th, td {
                border: 1px solid black;
            }
        </style>
    </head>
    <body>
        <table >
            <tr>
                <th>用户id</th>
                <th>用户姓名</th>
                <th>用户密码</th>
                <th>用户编号</th>
            </tr>
            <tr>
                <td th:text="${user.userId}"></td>
                <td th:text="${user.userName}"></td>
                <td th:text="${user.userPwd}"></td>
                <td th:text="${user.stateId}"></td>
            </tr>
        </table>
    </body>
</html>

删除成功

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
    <head>
        <meta charset="UTF-8">
        <title>删除成功</title>
      <link rel="shortcut icon" href="../resources/favicon.ico" th:href="@{/static/favicon.ico}"/>
    </head>
    <body>
      <h1 style="color: green">删除成功</h1>
      <a th:href="@{/user/findUserAll}">返回</a>
    </body>
</html>

报错页面

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
    <head>
        <meta charset="UTF-8">
        <title>错误</title>
        <link rel="shortcut icon" href="../resources/favicon.ico" th:href="@{/static/favicon.ico}"/>
    </head>
    <body>
        <h1 style="color: red">出错了，请与管理员联系</h1>
    </body>
</html>

报错一般解决方法

1. thymeleaf类的错误一般都是页面名称也对应的方法不对应，检查修改对应的页面，或者页面中的thymeleaf语法出现错误，链接的地址不正确
2. 启动类报错可以检查配置文件是否正确，以及有没有出现明显的报错，没有发现
3. jdbc类的报错一般是sql语句没有书写正确，数据库密码，用户名不正确，sql语句中的字段名与数据库中的字段名不对应，输入的数据长度大于数据库中规定的长度
4. 更新时的预查询时出现空的情况可以检查以下有没有把查询页面中加入id的input表单，没有这个表单会出现不报错但是页面不显示东西