spring boot mysql_SpringBoot、Mybatis、mysql 实现用户的增删改查

本文主要是搭建了一个SpringBoot 的框架，集成Mybatis、MySql、Pagehelper来实现用户表的增删(单条、多条)改查(单条、列表、分页)等功能

一、环境配置、导入依赖

1、新建一个SpringBoot项目，写入依赖

mysql

mysql-connector-java

mysql

mysql-connector-java

org.mybatis.spring.boot

mybatis-spring-boot-starter

2.1.3

com.github.pagehelper

pagehelper-spring-boot-starter

1.2.12

2、写 application.properties配置文件

application.properties 文件配置

#指定当前环境

spring.profiles.active=dev

#mysql 驱动

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

#mybatis映射配置

#mapper接口对应的xml文件(sql语句)位置

mybatis.mapper-locations=classpath:mapper/*.xml

#对应的实体类的位置

mybatis.type-aliases-package=com.example.userstartercore.system.dto

mybatis.configuration.map-underscore-to-camel-case=true

#分页插件配置

pagehelper.helper-dialect=mysql

#当该参数设置为true时，pageNum<=0 时会查询第一页，pageNum>pages(超过总数时)，会查询最后一页

pagehelper.reasonable=true

pagehelper.support-methods-arguments=true

pagehelper.params=count=countSql

application-dev.properties 文件配置

#tomcat

server.port=8080

#数据源配置

spring.datasource.url=jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC

spring.datasource.username=username

spring.datasource.password=password

#分页插件配置

pagehelper.helper-dialect=mysql

pagehelper.params=count=countSql

pagehelper.reasonable=true

pagehelper.support-methods-arguments=true

3、数据库建表

CREATE TABLE `user` (

`USER_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '表ID，主键，供其他表做外键',

`USERNAME`varchar(30) NOT NULL COMMENT '用户名',

`PASSWORD`varchar(100) NOT NULL COMMENT '密码',

`NICKNAME`varchar(30) NOT NULL COMMENT '用户名称',

`BIRTHDAY` dateDEFAULT NULL COMMENT '生日',

`SEX`int(1) DEFAULT NULL COMMENT '性别：1-男；0-女',PRIMARY KEY (`USER_ID`),UNIQUE KEY`USERNAME` (`USERNAME`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='用户';

二、功能实现

1、实现添加一个用户和多个用户

先贴一张具体项目结构图

(1)先建一张和数据库表对应的实体类 User1

@JsonInclude(JsonInclude.Include.NON_NULL)

@Table(name= "USER")public class User1 extendsBsaeDTO{/*** 用户ID*/@Id

@GeneratedValue(strategy=GenerationType.IDENTITY)

@OrderBy("DESC")privateLong userId;/*** 用户名*/

privateString username;/*** 密码*/

privateString password;/*** 昵称*/

privateString nickname;/*** 生日*/@JsonFormat(pattern=Dates.Pattern.DATE)privateDate birthday;/*** 性别：1-男/0-女*/

private Integer sex;

getter 和 setter 方法

(2) 写操作数据库的mapper接口

packagecom.example.userstartercore.system.dao;importcom.example.userstartercore.system.dto.User1;importorg.apache.ibatis.annotations.Mapper;importorg.springframework.stereotype.Repository;importjava.util.List;/***@authorchenhong

* @create 2021/1/11

* @desc 用户持久层接口*/@Mapper

@Repositorypublic interfaceUserMapper {//添加一个用户

User1 addUser (User1 user);//添加多个用户

int addUser1List (Listuser1);

}

(3) 写对应的 xml文件，sql 语句去操作数据库

insert into springboot.user (username,password,nickname,birthday,sex)

values (#{username},#{password},#{nickname},#{birthday},#{sex})

insert into springboot.user

(username, password,nickname,birthday,sex )

values(#{user.username},#{user.password}, #{user.nickname}, #{user.birthday}, #{user.sex})

(4)在UserService 接口中 定义 添加一个用户和多个用户的方法，，，以及在impl中实现接口中定义的方法

public interfaceUserService {/*** 添加一个用户

*@paramuser

*@returnUser1*/User1 addUser (User1 user);/*** 添加多个用户

*@paramuser1

*@returnList*/

int addUser1List (Listuser1);

}

@Servicepublic class UserServiceImpl implementsUserService{

@AutowiredprivateUserMapper userMapper;//添加一个用户

@OverridepublicUser1 addUser(User1 user) {returnuserMapper.addUser(user);

}//添加多个用户

@Overridepublic int addUser1List(Listuser1) {returnuserMapper.addUser1List(user1);

}

}

(5)写controller 接收前端的访问

@RequestMapping

@RestControllerpublic classUserController {

@AutowiredprivateUserService userService;/*** 添加一个用户，，，自己写的SQL*/@PostMapping("/sys/user/addOne")publicResult addUser(@Valid @RequestBody User1 user) {

user=userService.addUser(user);

System.out.println(user);return null;

}/*** 添加多个用户*/@PostMapping("/sys/user/addList")public Result addUsers(@Valid @RequestBody Listuser) {

System.out.println("打印出来了嘛"+user);

userService.addUser1List(user);

System.out.println(user);return null;

}

}

(6) 用PostMan 进行接口测试

数据插入成功

2、修改用户密码

用户需要往后端传 userId 、之前的密码oldpassword 、新设置的密码newpassword1、确认的密码newpassword 2，这个时候我们的cotroller 需要有对象来接收这四个数据，用User1 表去继承一个父类实体 BaseDTO

在Base中添加扩展字段 Attribute ,具体代码如下

/***@authorchenhong

* @create 2021/1/7

* @desc 基础实体类*/

public class BsaeDTO implementsSerializable {/*** 操作类型，add/update/delete 参考*/@TransientprivateString _operate;/*** 数据版本号,每发生update则自增,用于实现乐观锁.*/

privateLong versionNumber;/*** 创建人用户名*/@JsonInclude(JsonInclude.Include.NON_NULL)privateLong createBy;/*** 创建人名称*/@JsonInclude(JsonInclude.Include.NON_NULL)

@TransientprivateString creator;/*** 创建时间*/@JsonInclude(JsonInclude.Include.NON_NULL)

@JsonFormat(pattern=Dates.DEFAULT_PATTERN)privateDate createDate;/*** 更新人用户名*/@JsonInclude(JsonInclude.Include.NON_NULL)privateLong updateBy;/*** 更新人名称*/@JsonInclude(JsonInclude.Include.NON_NULL)

@TransientprivateString updater;/*** 更新时间*/@JsonInclude(JsonInclude.Include.NON_NULL)

@JsonFormat(pattern=Dates.DEFAULT_PATTERN)privateDate updateDate;/*** 其它属性*/@JsonIgnore

@Transientprotected Map innerMap = new HashMap<>();//

//下面是扩展属性字段//----------------------------------------------------------------------------------------------------

@JsonInclude(JsonInclude.Include.NON_NULL)privateString attribute1;

@JsonInclude(JsonInclude.Include.NON_NULL)privateString attribute2;

@JsonInclude(JsonInclude.Include.NON_NULL)privateString attribute3;

@JsonInclude(JsonInclude.Include.NON_NULL)privateString attribute4;

@JsonInclude(JsonInclude.Include.NON_NULL)privateString attribute5;

@JsonInclude(JsonInclude.Include.NON_NULL)privateString attribute6;

@JsonInclude(JsonInclude.Include.NON_NULL)privateString attribute7;

@JsonInclude(JsonInclude.Include.NON_NULL)privateString attribute8;

@JsonInclude(JsonInclude.Include.NON_NULL)privateString attribute9;

@JsonInclude(JsonInclude.Include.NON_NULL)private String attribute10;

getter 和 setter 方法

controller 写法：

@PostMapping("/sys/user/updateUserPassword")public intupdateUserPassword (@Valid @RequestBody User1 user) {returnuserService.updateUserPassword(user.getUserId(),user.getPassword(),user.getAttribute1(),user.getAttribute2());

}

service 和 serviceimpl

/*** 修改用户密码*/

int updateUserPassword(Long userId, String oldpassword, String password1, String password2);

//修改用户密码

@Overridepublic intupdateUserPassword(Long userId, String oldpassword, String password1, String password2) {if(password1.equals(password2)) {returnuserMapper.updateUserPassword(userId,oldpassword,password1);

}else return 0;

}

对应的 UserMapper 接口

//改修改用户密码

int updateUserPassword (Long userId, String oldpassword, String password1);

对应的xml 文件

update springboot.user set password = #{password1}

where user_Id = #{userId} and password = #{oldpassword}

PostMan 测试

3、分页查询

Contrller,网上教程很多

@GetMapping("/sys/user/queryAll")public Result queryAll(Model model, @RequestParam(defaultValue = "1",value="pageNum") Integer pageNum) {

PageHelper.startPage(pageNum,5);

List user =userService.findAllUser();

PageInfo pageInfo = new PageInfo(user);

model.addAttribute("pageInfo",pageInfo);return null;

}

新手入门的博客，，，请多多指教