SpringBoot+Mybatis+PostMan(一):搭建spring boot项目并实现对数据库的增删改查操作。
Springboot+Mybatis+redis+postman项目实战总目录*
SpringBoot+Mybatis+PostMan(二):SpringBoot + Mybatis分页查询实现
SpringBoot+Mybatis+PostMan(三):学习过滤器
SpringBoot+Mybatis+PostMan(四):学习拦截器
SpringBoot+Mybatis+PostMan(五):token登陆认证过程一(token生成与认证)
SpringBoot+Mybatis+PostMan(五):token登陆认证过程二(redis缓存引入)
SpringBoot+Mybatis+PostMan(六):token登陆认证过程三(redis封装与干掉原来session,避免用户重复登陆)
番外篇:SpringBoot 用户注册时经MD5加密存入数据库
第二篇章:用户角色权限访问控制
SpringBoot+Mybatis+PostMan(七):用户角色权限访问控制入门(数据模拟实现,不带数据库)
SpringBoot+Mybatis+PostMan(八):用户角色权限访问控制一(数据库用户角色表查询组合)
SpringBoot+Mybatis+PostMan(九):用户角色权限访问控制二(加入资源表和资源角色对应表)
SpringBoot+Mybatis+PostMan(十):用户角色权限访问控制三(禁用session、启用token并集成redis)
环境:
idea 2019
版本 2.4.0
数据库:dbeaver、mysql
前端实现:postman
一、首先配置一下基本环境。
1. 新建项目,引入相关依赖:
<dependency> <groupId>tk.mybatis</groupId> <artifactId>mapper-spring-boot-starter</artifactId> <version>2.1.3</version> </dependency> <!-- mybatis配置--> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.3</version> </dependency> <!--jdbc--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jdbc</artifactId> </dependency> <!--mysql jdbc驱动--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <!--web驱动--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!--test驱动--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <!--valid注解依赖--> <dependency> <groupId>javax.validation</groupId> <artifactId>validation-api</artifactId> <version>1.1.0.Final</version> </dependency>
<!--charset引入--> <dependency> <groupId>commons-io</groupId> <artifactId>commons-io</artifactId> <version>2.4</version> </dependency> <dependency> <groupId>commons-fileupload</groupId> <artifactId>commons-fileupload</artifactId> <version>1.3.1</version> <exclusions> <exclusion> <groupId>commons-io</groupId> <artifactId>commons-io</artifactId> </exclusion> </exclusions> </dependency> <dependency> <groupId>commons-collections</groupId> <artifactId>commons-collections</artifactId> <version>3.2</version> </dependency> <dependency> <groupId>commons-beanutils</groupId> <artifactId>commons-beanutils</artifactId> <version>1.9.2</version> <exclusions> <exclusion> <groupId>commons-collections</groupId> <artifactId>commons-collections</artifactId> </exclusion> </exclusions> </dependency> <dependency> <groupId>commons-codec</groupId> <artifactId>commons-codec</artifactId> <version>1.10</version> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> <version>3.6</version> </dependency>
2. Mybatis配置:
@Configuration public class MyBatisConfig { /** * Mapper扫描配置. 自动扫描将Mapper接口生成代理注入到Spring. */ @Bean public static MapperScannerConfigurer mapperScannerConfigurer() { MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer(); // 注意这里的扫描路径: 1.不要扫描到自定义的Mapper; 2.定义的路径不要扫描到tk.mybatis.mapper(如定义**.mapper). // 两个做法都会导致扫描到tk.mybatis的Mapper,就会产生重复定义的报错. mapperScannerConfigurer.setBasePackage("*.example.**.mapper"); return mapperScannerConfigurer; } }
3. application.properties中数据源配置、mapper包映射设置:
#数据源配置 spring.datasource.url=jdbc:mysql://47.100.59.91:3306/testdemo spring.datasource.username=root spring.datasource.password= Aa12345&_ #mybatis映射配置 mybatis.mapper-locations=classpath:mapper/*.xml mybatis.type-aliases-package=testdemo.system.dto mybatis.configuration.map-underscore-to-camel-case=true
4. 项目结构如下:
5. 前端代码承接部分要实现两个类,分别是Result和Results类。
package testdemo.util; import testdemo.base.Result; /** * Result生成工具类 */ public class Results { protected Results() {} public static Result newResult() { return new Result(); } public static Result newResult(boolean success) { return new Result(success); } // // 业务调用成功 // ---------------------------------------------------------------------------------------------------- public static Result success() { return new Result(); } public static Result success(String msg) { return new Result(true, null, msg); } public static Result success(String code, String msg) { return new Result(true, code, msg); } public static Result successWithStatus(Integer status) { return new Result(true, status); } public static Result successWithStatus(Integer status, String msg) { return new Result(true, status, null, msg); } public static Result successWithData(Object data) { return new Result(true, null, null, data); } public static Result successWithData(Object data, String msg) { return new Result(true, null, msg, data); } public static Result successWithData(Object data, String code, String msg) { return new Result(true, code, msg, data); } // // 业务调用失败 // ---------------------------------------------------------------------------------------------------- public static Result failure() { return new Result(false); } public static Result failure(String msg) { return new Result(false, null, msg); } public static Result failure(String code, String msg) { return new Result(false, code, msg); } public static Result failureWithStatus(Integer status) { return new Result(false, status); } public static Result failureWithStatus(Integer status, String msg) { return new Result(false, status, null, msg); } public static Result failureWithData(Object data) { return new Result(false, null, null, data); } public static Result failureWithData(Object data, String msg) { return new Result(false, null, msg, data); } public static Result failureWithData(Object data, String code, String msg) { return new Result(false, code, msg, data); } }
package testdemo.base; import com.fasterxml.jackson.annotation.JsonInclude; import java.io.Serializable; /** * 前端返回对象 */ public class Result implements Serializable { private static final long serialVersionUID = 1430633339880116031L; /** * 成功与否标志 */ private boolean success = true; /** * 返回状态码,为空则默认200.前端需要拦截一些常见的状态码如403、404、500等 */ @JsonInclude(JsonInclude.Include.NON_NULL) private Integer status; /** * 编码,可用于前端处理多语言,不需要则不用返回编码 */ @JsonInclude(JsonInclude.Include.NON_NULL) private String code; /** * 相关消息 */ @JsonInclude(JsonInclude.Include.NON_NULL) private String msg; /** * 相关数据 */ @JsonInclude(JsonInclude.Include.NON_NULL) private Object data; public Result() {} public Result(boolean success) { this.success = success; } public Result(boolean success, Integer status) { this.success = success; this.status = status; } public Result(boolean success, String code, String msg){ this(success); this.code = code; this.msg = msg; } public Result(boolean success, Integer status, String code, String msg) { this.success = success; this.status = status; this.code = code; this.msg = msg; } public Result(boolean success, String code, String msg, Object data){ this(success); this.code = code; this.msg = msg; this.data = data; } public boolean isSuccess() { return success; } public void setSuccess(boolean success) { this.success = success; } public Integer getStatus() { return status; } public void setStatus(Integer status) { this.status = status; } public String getCode() { return code; } public void setCode(String code) { this.code = code; } public String getMsg() { return msg; } public void setMsg(String msg) { this.msg = msg; } public Object getData() { return data; } public void setData(Object data) { this.data = data; } }
6. 实现枚举类BaseEnum和BaseEnums。
package testdemo.constants; import testdemo.base.BaseEnum; import java.util.HashMap; import java.util.Map; /** * 基础枚举值 */ public enum BaseEnums implements BaseEnum<String, String> { SUCCESS("request.success", "请求成功"), FAILURE("request.failure", "请求失败"), OPERATION_SUCCESS("operation.success", "操作成功"), OPERATION_FAILURE("operation.failure", "操作失败"), ERROR("system.error", "系统异常"), NOT_FOUND("not_found", "请求资源不存在"), FORBIDDEN("forbidden", "无权限访问"), VERSION_NOT_MATCH("record_not_exists_or_version_not_match", "记录版本不存在或不匹配"), PARAMETER_NOT_NULL("parameter_not_be_null", "参数不能为空"); private String code; private String desc; private static Map<String, String> allMap = new HashMap<>(); BaseEnums(String code, String desc) { this.code = code; this.desc = desc; } static { for(BaseEnums enums : BaseEnums.values()){ allMap.put(enums.code, enums.desc); } } @Override public String code() { return code; } @Override public String desc() { return desc; } public String desc(String code) { return allMap.get(code); } }
package testdemo.base; /** * 基础枚举接口 */ public interface BaseEnum<K, V> { /** * 获取编码 * * @return 编码 */ K code(); /** * 获取描述 * * @return 描述 */ V desc(); }
7. 还有常量设置constants。
package testdemo.constants; import com.google.common.base.Charsets; import java.nio.charset.Charset; /** * 系统级常量类 */ public class Constants { public static final String APP_NAME = "spring"; /** * 系统编码 */ public static final Charset CHARSET = Charsets.UTF_8; /** * 标识:是/否、启用/禁用等 */ public interface Flag { Integer YES = 1; Integer NO = 0; } /** * 操作类型 */ public interface Operation { /** * 添加 */ String ADD = "add"; /** * 更新 */ String UPDATE = "update"; /** * 删除 */ String DELETE = "delete"; } /** * 性别 */ public interface Sex { /** * 男 */ Integer MALE = 1; /** * 女 */ Integer FEMALE = 0; } }
8. 接下来就是数据库设计。
数据库名字是testdemo,数据库表名叫sys-user。
先创建一个数据库,然后创建用户表。
USE testdemo;
CREATE TABLE `testdemo`.`user` (
`id` bigint NOT NULL AUTO_INCREMENT,
`username` varchar(255) NULL,
`passwprd` varchar(255) NULL,
PRIMARY KEY (`id`)
);
插入几条数据:
基本准备做好了,接下来就进入代码编写了。
二、实现数据库表数据单条查询和多条查询。
1. UserController.java中代码如下:
@Autowired UserService userService ; /* * 查询所有条数据 * */ @GetMapping("/select") public Result select(){ List<User> userList = userService.select(); return Results.successWithData(userList, BaseEnums.SUCCESS.code(),BaseEnums.SUCCESS.desc()); } /* * 查询某条数据 * */ @GetMapping("/selectOne") public Result selectOne(@RequestParam("id") String id){ return Results.successWithData(userService.selectOne(id), BaseEnums.SUCCESS.code(),BaseEnums.SUCCESS.desc()); }
2. UserService接口中声明方法。
/* * 通过id查询用户 * */ public List<User> select(); /* * 查询某一条数据 * */ public User selectOne(String id);
3. UserServiceImp类继承UserService,并完成数据传递。
@Service public class UserServiceImpl implements UserService { @Autowired UserMapper userMapper ; @Override public User selectOne(String id){ return userMapper.selectOne(id) ; } @Override public List<User> select(){ return userMapper.select(); } }
4. UserMapper中声明方法,继续传递数据。
@Repository @Mapper public interface UserMapper { public List<User> select(); public User selectOne(String id); }
5. UserMapper.xml中代码如下:
<mapper namespace="testdemo.system.dao.UserMapper"> <resultMap id="userMap" type="testdemo.system.dto.User"> <result property="id" column="id"/> <result property="userName" column="username"/> <result property="password" column="password"/> </resultMap> <select id="select" resultMap="userMap"> select * from sys_user </select> <select id="selectOne" resultMap="userMap"> select * from sys_user where id = #{id} </select> </mapper>
这样一来,后端查询代码就完成了,现在利用postman完成前端数据查询。
6、前端数据查询
(1). 查询所有数据
(2). 查询一条数据
三、插入一条或多条数据。
在上面代码基础上添加代码。
1. UserController.java中:
/* * 插入一条数据 * */ @PostMapping("/InsertOne") public Result InsertOne(@RequestBody User user){ return Results.successWithData(userService.insertOne(user), BaseEnums.SUCCESS.code(),BaseEnums.SUCCESS.desc()); } /* * 插入多条数据 * */ @PostMapping("/InsertMany") public Result InsertMany(@RequestBody List<User> userlist){ return Results.successWithData(userService.insertMany(userlist), BaseEnums.SUCCESS.code(),BaseEnums.SUCCESS.desc()); }
2. UserService接口中添加方法:
/* * 新增一个用户 * */ public Integer insertOne(User user); /* * 新增多个用户 * */ public Integer insertMany(List<User> userList);
3. UserServiceImpl实现数据传递:
@Override public Integer insertOne(User user){ return userMapper.insertOne(user); } @Override public Integer insertMany(List<User> userList){ return userMapper.insertMany(userList) ; }
4. UserMapper.java中:
public Integer insertOne(User user); public Integer insertMany(List<User> userList);
5. UserMapper.xml中:
<insert id="insertOne"> insert into sys_user(id,username,password) values(#{id},#{userName},#{password}) </insert> <insert id="insertMany"> insert into sys_user(id,username,password) values <foreach collection="list" item="user" index="index" separator=","> (#{user.id} , #{user.userName},#{user.password}) </foreach> </insert>
上面xml中的user指的是当前对象,因为前面是list,说明user对象有多个,这样就需要带上user,表示都是list中哪个user的属性。这里的user可以随意命名。
collection="list"中的list和前面UserMapper.java中参数统一,如果没统一有时候也不会出错,但是如果想要不统一也能执行不出错的话,需要进入如下设置:
public Integer insertMany(@Param("list") List<User> userList);
这样以来两者也能对应上了。
6. 前端实现:
(1)插入一条数据:
(2)插入多条数据(先把数据表中清空,不然会出现重复插入情况)
四、删除某条数据
1. UserController.java中:
/* * 删除某条数据 * */ @PostMapping("/DeleteOne") public Result DeleteOne(@RequestParam("id") String id){ return Results.successWithData(userService.deleteOne(id), BaseEnums.SUCCESS.code(),BaseEnums.SUCCESS.desc()); }
2. UserService.java中:
/* * 删除某一条数据 * */ public Integer deleteOne(String id);
3. UserServiceImpl.java中:
@Override public Integer deleteOne(String id){ return userMapper.deleteOne(id) ; }
4. UserMapper.java中:
public Integer deleteOne(String id);
5. UserMapper.xml中:
<delete id="deleteOne"> delete from sys_user where id = #{id} </delete>
6.前端实现:
五、更新数据(根据id更新数据)
1. UserController.java
/* *按照id更新数据 * */ @PostMapping("/UpdateById") public Result UpdateById(@RequestBody User user){ User user1 = userService.selectOne(user.getId()) ; System.out.println(user1); if(user1!=null){ return Results.successWithData(userService.updateById(user), BaseEnums.SUCCESS.code(),BaseEnums.SUCCESS.desc()); }else{ return Results.failure() ; } }
2. UserService.java中:
/* * 更新数据 * */ public Integer updateById(User user) ;
3. UserServiceImpl.java中:
public Integer updateById(User user){ return userMapper.updateById(user ) ; }
4. UserMapper.java中:
public Integer updateById(User user) ;
5. UserMapper.xml中:
<update id="updateById" parameterType="User"> update sys_user set username = #{userName}, password = #{password} where id = #{id} </update>
6.前端postman传参如下:
数据库更新成功:
以上就是springboot框架+mybatis中前后端完整的数据库增删改查实现。
至此,结束。