数据库校验用户准备工作

数据库创建用户表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for sys_user
-- ----------------------------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user`  (
  `id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'NULL' COMMENT '用户名',
  `nick_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'NULL' COMMENT '呢称',
  `password` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'NULL' COMMENT '密码',
  `status` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '0' COMMENT '账号状态(0正常1停用)',
  `email` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '邮箱',
  `phone_number` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '手机号',
  `sex` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户性别(0男，1女，2未知)',
  `avatar` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '头像',
  `user_type` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '1' COMMENT '用户类型(0管理员，1普通用户)',
  `create_by` bigint(0) NULL DEFAULT NULL COMMENT '创建人的用户id',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `update_by` bigint(0) NULL DEFAULT NULL COMMENT '更新人',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
  `delete_flag` int(0) NULL DEFAULT 0 COMMENT '删除标志(0代表未删除，1代表已删除)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sys_user
-- ----------------------------
INSERT INTO `sys_user` VALUES (2, 'admin', '哔哩哔哩', '123456789', '0', '123@qq.com', '13518526352', '0', NULL, '0', NULL, NULL, NULL, NULL, 0);

SET FOREIGN_KEY_CHECKS = 1;

定义mapper接口

public interface UserMapper extends BaseMapper<User> {
}

这里使用的是mybatis-plus。

修改实体类

类名上加@TableName("sys_user")，id字段上加@TableId。

@AllArgsConstructor
@NoArgsConstructor
@Data
@TableName("sys_user")
public class User implements Serializable {

    private static final long serialVersionUID = 1L;

    /**
     * 主键
     */
    @TableId
    private Long id;

    /**
     * 用户名
     */
    private String userName;

    /**
     * 昵称
     */
    private String nickName;

    /**
     * 密码
     */
    private String password;

    /**
     * 账号状态: 0正常 1停用
     */
    private String status;

    /**
     * 邮箱
     */
    private String email;

    /**
     * 手机号
     */
    private String phoneNumber;

    /**
     * 用户性别(0男，1女，2未知)
     */
    private String sex;

    /**
     * 头像
     */
    private String avatar;

    /**
     * 用户类型:0 管理员; 1 普通用户
     */
    private String userType;

    /**
     * 创建人的用户ID
     */
    private Long createBy;

    /**
     * 创建时间
     */
    private Date createTime;

    /**
     * 创建人的用户ID
     */
    private Long updateBy;

    /**
     * 更新时间
     */
    private Date updateTime;

    /**
     * 删除标志: 0未删除 1已删除
     */
    private Integer deleteFlag;

}

配置mapper扫描

主启动类上添加@MapperScan

@SpringBootApplication
@MapperScan("com.sanfen.mapper") // mapper扫描
public class SpringsecurityReviewApplication {

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

}

测试类进行mybatis-plus测试

@SpringBootTest
class SpringsecurityReviewApplicationTests {

    @Autowired
    private UserMapper userMapper;

    @Test
    void contextLoads() {
        List<User> userList = userMapper.selectList(null);
        System.out.println(userList);
    }

}

上面都没有问题的话，会查出结果：

2022-03-27 17:08:25.988  INFO 26600 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2022-03-27 17:08:26.509  INFO 26600 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
[User(id=2, userName=admin, nickName=哔哩哔哩, password=$2a$10$n4kVYFOw0NFS.QoCUY0QT.uDF.V23yrPGHJCKsGlxFl8HBpriDQBG, status=0, email=123@qq.com, phoneNumber=13518526352, sex=0, avatar=null, userType=0, createBy=null, createTime=null, updateBy=null, updateTime=null, deleteFlag=0)]