SpringBoot之实训-----完成数据库的增删改查和分页查询

依赖：

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.xxx</groupId>
  <artifactId>Spring_MYbaties</artifactId>
  <version>1.0-SNAPSHOT</version>

  <name>Spring_MYbaties</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
  </properties>

  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.2.2.RELEASE</version>
  </parent>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
      <scope>test</scope>
    </dependency>
    <!--web启动器-->
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <!-- mybatis 集成 -->
    <dependency>
      <groupId>org.mybatis.spring.boot</groupId>
      <artifactId>mybatis-spring-boot-starter</artifactId>
      <version>2.1.1</version>
    </dependency>
    <!-- springboot 分页插件 -->
    <dependency>
      <groupId>com.github.pagehelper</groupId>
      <artifactId>pagehelper-spring-boot-starter</artifactId>
      <version>1.2.13</version>
    </dependency>
    <!-- mysql 驱动 -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <!-- c3p0 数据源 -->
    <dependency>
      <groupId>com.mchange</groupId>
      <artifactId>c3p0</artifactId>
      <version>0.9.5.5</version>
    </dependency>

    <dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-lang3</artifactId>
  </dependency>
    <!-- DevTools 的坐标 -->
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-devtools</artifactId>
      <!--当前这个项目被继承之后，这个不向下传递-->
      <optional>true</optional>
    </dependency>
  </dependencies>

  <build>
    <finalName>springboot</finalName>



    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
        <configuration>
          <fork>true</fork><!-- 如果没有该配置，热部署的devtools不生效 -->
        </configuration>
      </plugin>
    </plugins>
  </build>
</project>

application.yml

server:
  port:
  servlet:
    context-path:

spring:
  datasource:
    ##type: com.mchange.v2.c3p0.ComboPooledDataSource
    type: com.mchange.v2.c3p0.ComboPooledDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/exam?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8
    username: root
    password: cjd
  ## 热部署配置
  devtools:
    restart:
      enabled: true
      # 设置重启的目录，添加目录的文件需要restart
      additional-paths: src/main/java
      # 解决项目自动重新编译后接口报404的问题
      poll-interval: 3000
      quiet-period: 1000

mybatis:
  type-aliases-package: com.xxx.po
  mapper-locations: classpath:/mappers/*.xml
  configuration:
    map-underscore-to-camel-case: true

pagehelper:
  helper-dialect: mysql

logging:
  level:
    com:
      yjxxt:
        mapper: debug

 UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.xxx.dao.UserMapper">
    <select id="queryUserByUserName" parameterType="string" resultType="com.xxx.po.User">
            select userid,username,userpwd from tbb_user where username=#{userName}
            </select>


    <select id="queryById" parameterType="int" resultType="com.xxx.po.User">
        select * from tbb_user
        where userid = #{id,jdbcType=INTEGER}
    </select>

    <insert id="save" parameterType="com.xxx.po.User">
    insert into tbb_user (username,userpwd) values (#{userName},#{userPwd})
    </insert>

    <update id="update" parameterType="com.xxx.po.User">
    update tbb_user set username =#{userName},userpwd=#{userPwd} where userid= #{userId}
    </update>

    <delete id="deleteUserById" parameterType="int">
       delete from tbb_user where userid=#{id}
       </delete>

    <select id="selectByParams" parameterType="com.xxx.query.UserQuery" resultType="com.xxx.po.User">
        select* from tbb_user
        <where>
            <if test="null != userName and userName !=''">
                and username like concat('%',#{userName},'%')
            </if>
        </where>
    </select>
</mapper>

启动类：

package com.xxx;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
 * @Author 陈平安
 * @Date 2022/6/30 9:51
 * @PackageName:com.xxx
 * @ClassName: Start01
 * @Description: TODO
 * @Version 1.0
 */
@SpringBootApplication
@MapperScan("com.xxx.dao")
public class Start01 {

    public static void main(String[] args) {
        SpringApplication.run(Start01.class);

    }
}

 工具类

package com.xxx.Utis;

import com.xxx.Excelption.ParamsException;

/**
 * @Author 陈平安
 * @Date 2022/6/30 10:11
 * @PackageName:com.xxx.Utis
 * @ClassName: Utils01
 * @Description: TODO
 * @Version 1.0
 */
public class AssertUtil {
    public static void isTrue(Boolean flag, String msg) {
        if (flag) {
            throw new ParamsException(msg);
        }
    }
}

Service层：

package com.xxx.service;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.xxx.Utis.AssertUtil;
import com.xxx.dao.UserMapper;
import com.xxx.po.User;
import com.xxx.query.UserQuery;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.apache.commons.lang3.StringUtils;

import javax.annotation.Resource;

/**
 * @Author 陈平安
 * @Date 2022/6/30 9:48
 * @PackageName:com.xxx.service
 * @ClassName: UserService
 * @Description: TODO
 * @Version 1.0
 */
@Service
public class UserService {
    @Resource
    private UserMapper userMapper;

    public User queryUserByUserName(String userName) {
        return userMapper.queryUserByUserName(userName);
    }

    public User queryById(Integer userId) {
        return userMapper.queryById(userId);
    }

    public void saveUser(User user) {

        AssertUtil.isTrue(StringUtils.isBlank(user.getUserName()), "用户名不能为 空!");
        AssertUtil.isTrue(StringUtils.isBlank(user.getUserPwd()), "用户密码不能为 空!");
        User temp = userMapper.queryUserByUserName(user.getUserName());
        AssertUtil.isTrue(null != temp, "该用户已存在!");
        AssertUtil.isTrue(userMapper.save(user) < 1, "用户记录添加失败!");
    }

    /*** 修改用户 * @param user */
    public void updateUser(User user) {
        AssertUtil.isTrue(StringUtils.isBlank(user.getUserName()), "用户名不能为 空!");
        AssertUtil.isTrue(StringUtils.isBlank(user.getUserPwd()), "用户密码不能为 空!");
        User temp = userMapper.queryUserByUserName(user.getUserName());
        AssertUtil.isTrue(null != temp && !(temp.getUserId().equals(user.getUserId())), "该用户已存在!");
        AssertUtil.isTrue(userMapper.update(user) < 1, "用户记录添加失败!");
    }


    /*** 删除用户 * @param id */
    public void deleteUser(Integer id) {
        AssertUtil.isTrue(null == id || null == userMapper.queryById(id), "待删除记 录不存在!");
        AssertUtil.isTrue(userMapper.deleteUserById(id) < 1, "用户删除失败!");
    }
    /*** 通过指定参数，分页查询用户列表 * @param userQuery * @return */
    public PageInfo<User> queryUserByParams(UserQuery userQuery) {
        PageHelper.startPage(userQuery.getPageNum(), userQuery.getPageSize());
        return new PageInfo<User>(userMapper.selectByParams(userQuery));
    }
}

分页查询相关实体类：

package com.xxx.query;

public class UserQuery {

    private Integer pageNum = 1; // 当前页
    private Integer pageSize = 3; // 每页显示的数量
    private String userName; // 查询条件：用户名

    public UserQuery() {
    }

    public Integer getPageNum() {
        return pageNum;
    }

    public void setPageNum(Integer pageNum) {
        this.pageNum = pageNum;
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }

    public String getUserName() {
        return userName;
    }

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

    @Override
    public String toString() {
        return "UserQuery{" +
                "pageNum=" + pageNum +
                ", pageSize=" + pageSize +
                ", userName='" + userName + '\'' +
                '}';
    }
}

User

package com.xxx.query;

public class UserQuery {

    private Integer pageNum = 1; // 当前页
    private Integer pageSize = 3; // 每页显示的数量
    private String userName; // 查询条件：用户名

    public UserQuery() {
    }

    public Integer getPageNum() {
        return pageNum;
    }

    public void setPageNum(Integer pageNum) {
        this.pageNum = pageNum;
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }

    public String getUserName() {
        return userName;
    }

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

    @Override
    public String toString() {
        return "UserQuery{" +
                "pageNum=" + pageNum +
                ", pageSize=" + pageSize +
                ", userName='" + userName + '\'' +
                '}';
    }
}

 

package com.xxx.Excelption;

public class ParamsException extends RuntimeException{
    private Integer code=300;
    private String msg="参数异常";

    public ParamsException() {
        super("参数异常");
    }

    public ParamsException(Integer code) {
        super("参数异常");
        this.code=code;
    }

    public ParamsException(String msg) {
        super(msg);
        this.msg=msg;
    }

    public ParamsException(Integer code,String msg) {
        super(msg);
        this.msg=msg;
        this.code=code;
    }

    public Integer getCode() {
        return code;
    }

    public void setCode(Integer code) {
        this.code = code;
    }

    public String getMsg() {
        return msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }
}

接口类：

package com.xxx.dao;

import com.xxx.po.User;
import com.xxx.query.UserQuery;

import java.util.List;

public interface UserMapper {
    // 根据用户名查询用户记录
    User queryUserByUserName(String userName);

    User queryById(Integer userId);

    // 添加用户
    public int save(User user);

    // 修改用户
    public int update(User user);

    // 删除用户
    public int deleteUserById(Integer userId);

    // 通过条件，分页查询用户列表
    List<User> selectByParams(UserQuery userQuery);
}

controller；

package com.xxx.controller;

import com.github.pagehelper.PageInfo;
import com.xxx.Excelption.ParamsException;
import com.xxx.po.User;
import com.xxx.query.UserQuery;
import com.xxx.service.UserService;
import com.xxx.vo.ResultInfo;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;

/**
 * @Author 陈平安
 * @Date 2022/6/30 9:49
 * @PackageName:com.xxx.controller
 * @ClassName: UserController
 * @Description: TODO
 * @Version 1.0
 */
@RestController
public class UserController {

    @Resource
    private UserService userService;
    /*** 用户查询用户 * @param user * @return */
    @GetMapping("/user/{userName}")
    public User queryUserByUserName(@PathVariable String userName) {
        System.out.println("***********" + userName);
        System.out.println("+++++++++++" + userName + "-------");
        return userService.queryUserByUserName(userName);
    }
    /*** 查询用户 * @param user * @return */
    @GetMapping("/user01/{id}")
    public User queryById(@PathVariable Integer id) {
        return userService.queryById(id
        );
    }

    /*** 添加用户 * @param user * @return */


    @PutMapping("/user")
    public ResultInfo saveUser(@RequestBody User user) {
        ResultInfo resultInfo = new ResultInfo();
        try {
            userService.saveUser(user);
        } catch (ParamsException e) {
            e.printStackTrace();
            resultInfo.setCode(e.getCode());
            resultInfo.setMsg(e.getMsg());
        } catch (Exception e) {
            e.printStackTrace();
            resultInfo.setCode(300);
            resultInfo.setMsg("记录添加失败!");
        }
        return resultInfo;
    }

    /*** 修改用户 * @param user * @return */
    @PostMapping("/user")
    public ResultInfo updateUser(@RequestBody User user) {
        ResultInfo resultInfo = new ResultInfo();
        try {
            userService.updateUser(user);
        } catch (ParamsException e) {
            e.printStackTrace();
            resultInfo.setCode(e.getCode());
            resultInfo.setMsg(e.getMsg());
        } catch (Exception e) {
            e.printStackTrace();
            resultInfo.setCode(300);
            resultInfo.setMsg("记录更新失败!");
        }
        return resultInfo;
    }

    /*** 删除用户 * @param userId * @return */
    @DeleteMapping("/user/{userId}")
    public ResultInfo deleteUser(@PathVariable Integer userId) {
        ResultInfo resultInfo = new ResultInfo();
        try {
            userService.deleteUser(userId);
        } catch (ParamsException e) {
            e.printStackTrace();
            resultInfo.setCode(e.getCode());
            resultInfo.setMsg(e.getMsg());
//            2.2 .5.分页条件查询操作
//            2.2 .5 .1.UserQuery
        } catch (Exception e) {
            e.printStackTrace();
            resultInfo.setCode(300);
            resultInfo.setMsg("记录删除失败!");
        }
        return resultInfo;
    }

    /*** 通过指定参数，分页查询用户列表 * @param userQuery * @return */
    @GetMapping("user/list")
    public PageInfo<User> list(UserQuery userQuery) {
        return userService.queryUserByParams(userQuery);
    }
}

目录结构：

 

在企业 web 应用开发中，对服务器端接口进行测试，通常借助接口测试工具，这里使用 Postman 接

口测试工具来对后台 restful 接口进行测试。

Postman 工具下载地址 ： https://www.getpostman.com/apps ，选中对应平台下载即可。

下载安装后，启动 Postman 根据后台接口地址发送响应请求即可对接口进行测试

 

安装包如上：