一 新建项目
SpringBoot提供了项目初始化的功能,只要在页面上点选就可以生成一个初始空白项目
Spring Initializr 地址:https://start.spring.io/
生成并下载springboot压缩包
解压后目录结构:
IDE会自动构建project
二 配置环境
打开pom.xml,增加项目中需要的Maven依赖文件
<dependencies>
<!-- json -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>${fastjson.version}</version>
</dependency>
<!--mybatis数据库操作-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.1</version>
</dependency>
<!-- mybatis分页插件 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.13</version>
</dependency>
<!--sqlserver数据库-->
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
</dependency>
<!-- 单元测试 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
点击标题栏 File > Project Structure
在 Project选项点击后出现下拉框,再点击 Add SDK 在弹出的选项中点击JDK
弹出JDK安装路径的选择对话框,选择已经安装完成的JDK路径后,点击OK 完成JDK环境的配置
点击IDEA标题栏 File > Settings…选项
在设置界面中选择Build、Execution、Deployment > Build Tools > Maven,分别设置本地Maven的解压文件路径和勾选Settins.xml和本地仓库的路径,点击Apply,项目会自动下载依赖文件
三 后端编写
1 controller层 注册api路由和控制器
新建一个controller文件夹,用来放控制器,新建UserController,实现基本的增删改查
@Slf4j //log日志
@RestController //restful规范的控制器
public class UserController {
@Autowired
private SbUserService sbUserService;
@GetMapping("/users")
@ResponseBody //返回数据为json格式
Result getUsers(@RequestParam(value = "pageNumber", defaultValue = "1") Integer pageNumber, //url中的参数
@RequestParam(value = "pageSize", defaultValue = "10") Integer pageSize){
log.info("查询User,第{}页,每页{}条", pageNumber, pageSize);
List<SbUser> sbUsers = sbUserService.selectByPage(pageNumber, pageSize);
return Result.success(sbUsers);
}
@GetMapping("/users/{userId}")
@ResponseBody
Result getUser(@PathVariable Integer userId){// 从请求路径中获取参数,对应uri中的同名占位符
log.info("查询User,id:{}", userId);
UserInfo userInfo = sbUserService.selectUserInfo(userId);
return Result.success(userInfo);
}
@PostMapping("/users")
@ResponseBody
Result addUser(@RequestBody AUUserReq req){// json格式的请求参数( 请求参数的Content-Type 是 application/json)
log.info("新增User:{}", req);
Integer userId = sbUserService.addUser(req);
return Result.success(userId);
}
@DeleteMapping("/users/{userId}")
@ResponseBody
Result deleteUser(@PathVariable Integer userId){// 从请求路径中获取参数,对应uri中的同名占位符
log.info("删除User,id:{}", userId);
return Result.success();
}
@PutMapping("/users/{userId}")
@ResponseBody
Result updateUser(@PathVariable Integer userId, @RequestBody AUUserReq req){
log.info("更新User,id:{},信息:{}", userId, req);
return Result.success();
}
}
2 dto层 参数和返回
请求参数:
@Data
public class AUUserReq {
private String name;
private Integer age;
private String email;
}
定义统一返回结果:
@Data
public class Result<T> {
public static String SUCCESS_STATUS = "0";
public static String FAIL_STATUS = "1";
@JSONField(ordinal = 1) //规定字段的顺序
private String code;
@JSONField(ordinal = 2)
private String message;
@JSONField(ordinal = 3)
private T result;
private Result(String code, String message, T result) {
this.code = code;
this.message = message;
this.result = result;
}
public static Result success() {
return new Result<>(SUCCESS_STATUS, "成功", null);
}
public static <T> Result<T> success(T result) {
return new Result<>(SUCCESS_STATUS, "成功", result);
}
@Deprecated
public static Result fail() {
return new Result<>(FAIL_STATUS, "失败", "");
}
@Deprecated
public static Result fail(String code, String message) {
return new Result<>(code, message, null);
}
}
3 entity层 表结构
建立两个表,sb_user和user_detail,用user_id关联
sb_user主表结构:
@Data
public class SbUser implements Serializable {
private Integer id;
private String name;
private Integer age;
private String email;
private static final long serialVersionUID = 1L;
}
user_detail副表结构:
@Data
public class UserDetail implements Serializable {
private Integer id;
private Integer userId;
private String avatar;
private String hobby;
private String city;
private String job;
private String company;
private LocalDateTime createdTime;
private static final long serialVersionUID = 1L;
}
查询字段:
@Data
public class UserInfo extends SbUser {
private Integer id;
private String name;
private String city;
private String job;
}
4 mapper层 数据库映射
定义sb_user映射:
public interface SbUserMapper {
UserInfo selectUserInfo(@Param("id") Integer id);
List<SbUser> selectAll();
int deleteByPrimaryKey(Integer id);
int insert(SbUser record);
int insertSelective(SbUser record);
SbUser selectByPrimaryKey(Integer id);
int updateByPrimaryKeySelective(SbUser record);
int updateByPrimaryKey(SbUser record);
}
定义user_detail映射:
public interface UserDetailMapper {
int deleteByPrimaryKey(Integer id);
int insert(UserDetail record);
int insertSelective(UserDetail record);
UserDetail selectByPrimaryKey(Integer id);
int updateByPrimaryKeySelective(UserDetail record);
int updateByPrimaryKey(UserDetail record);
}
5 service层 逻辑处理
@Service
public class SbUserService{
@Resource
private SbUserMapper sbUserMapper;
@Autowired
private UserDetailService userDetailService;
public UserInfo selectUserInfo(Integer id){
return sbUserMapper.selectUserInfo(id);
}
@Transactional(rollbackFor = Exception.class) // 为方法添加事务,保证两个表同步
public Integer addUser(AUUserReq req) {
// 新增用户基本信息
SbUser sbUser = new SbUser();
sbUser.setName(req.getName());
sbUser.setAge(req.getAge());
sbUser.setEmail(req.getEmail());
this.insertSelective(sbUser); // 插入成功id会自动回填到sbUser中
// 新增用户详细信息
UserDetail userDetail = new UserDetail();
userDetail.setUserId(sbUser.getId());
userDetail.setJob(req.getJob());
userDetail.setHobby(req.getHobby());
userDetail.setCompany(req.getCompany());
userDetailService.insertSelective(userDetail);
return sbUser.getId();
}
public List<SbUser> selectByPage(Integer pageNumber, Integer pageSize){
PageHelper.startPage(pageNumber, pageSize);
return sbUserMapper.selectAll();
}
public int deleteByPrimaryKey(Integer id) {
return sbUserMapper.deleteByPrimaryKey(id);
}
public int insert(SbUser record) {
return sbUserMapper.insert(record);
}
public int insertSelective(SbUser record) {
return sbUserMapper.insertSelective(record);
}
public SbUser selectByPrimaryKey(Integer id) {
return sbUserMapper.selectByPrimaryKey(id);
}
public int updateByPrimaryKeySelective(SbUser record) {
return sbUserMapper.updateByPrimaryKeySelective(record);
}
public int updateByPrimaryKey(SbUser record) {
return sbUserMapper.updateByPrimaryKey(record);
}
}
@Service
public class UserDetailService{
@Resource
private UserDetailMapper userDetailMapper;
public int deleteByPrimaryKey(Integer id) {
return userDetailMapper.deleteByPrimaryKey(id);
}
public int insert(UserDetail record) {
return userDetailMapper.insert(record);
}
public int insertSelective(UserDetail record) {
return userDetailMapper.insertSelective(record);
}
public UserDetail selectByPrimaryKey(Integer id) {
return userDetailMapper.selectByPrimaryKey(id);
}
public int updateByPrimaryKeySelective(UserDetail record) {
return userDetailMapper.updateByPrimaryKeySelective(record);
}
public int updateByPrimaryKey(UserDetail record) {
return userDetailMapper.updateByPrimaryKey(record);
}
}
6 resource mapper 放置SQL
定义主表查询字段和返回格式:
<resultMap id="BaseResultMap" type="com.jch.entity.SbUser">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="name" jdbcType="VARCHAR" property="name" />
<result column="age" jdbcType="INTEGER" property="age" />
<result column="email" jdbcType="VARCHAR" property="email" />
</resultMap>
<sql id="Base_Column_List">
id, [name], age, email
</sql>
构造主表SQL语句:
<select id="selectUserInfo" parameterType="java.lang.Integer" resultType="com.jch.entity.UserInfo">
select su.id, su.name, ud.hobby, ud.company, ud.job, ud.city
from sb_user as su
left join user_detail ud on su.id = ud.user_id
where su.id = #{id}
</select>
<select id="selectAll" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from sb_user
</select>
<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from sb_user
where id = #{id,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
delete from sb_user
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.jch.entity.SbUser" useGeneratedKeys="true">
insert into sb_user ([name], age, email
)
values (#{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER}, #{email,jdbcType=VARCHAR}
)
</insert>
<insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.jch.entity.SbUser" useGeneratedKeys="true">
insert into sb_user
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="name != null and name != ''">
[name],
</if>
<if test="age != null">
age,
</if>
<if test="email != null and email != ''">
email,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="name != null and name != ''">
#{name,jdbcType=VARCHAR},
</if>
<if test="age != null">
#{age,jdbcType=INTEGER},
</if>
<if test="email != null and email != ''">
#{email,jdbcType=VARCHAR},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.jch.entity.SbUser">
update sb_user
<set>
<if test="name != null and name != ''">
[name] = #{name,jdbcType=VARCHAR},
</if>
<if test="age != null">
age = #{age,jdbcType=INTEGER},
</if>
<if test="email != null and email != ''">
email = #{email,jdbcType=VARCHAR},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.jch.entity.SbUser">
update sb_user
set [name] = #{name,jdbcType=VARCHAR},
age = #{age,jdbcType=INTEGER},
email = #{email,jdbcType=VARCHAR}
where id = #{id,jdbcType=INTEGER}
</update>
定义副表查询字段和返回格式:
<resultMap id="BaseResultMap" type="com.jch.entity.UserDetail">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="user_id" jdbcType="INTEGER" property="userId" />
<result column="avatar" jdbcType="VARCHAR" property="avatar" />
<result column="hobby" jdbcType="VARCHAR" property="hobby" />
<result column="city" jdbcType="VARCHAR" property="city" />
<result column="job" jdbcType="VARCHAR" property="job" />
<result column="company" jdbcType="VARCHAR" property="company" />
<result column="created_time" jdbcType="TIMESTAMP" property="createdTime" />
</resultMap>
<sql id="Base_Column_List">
id, user_id, avatar, hobby, city, job, company, created_time
</sql>
构造副表SQL语句:
<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from user_detail
where id = #{id,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
delete from user_detail
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.jch.entity.UserDetail" useGeneratedKeys="true">
<!--@mbg.generated-->
insert into user_detail (user_id, avatar, hobby,
city, job, company,
created_time)
values (#{userId,jdbcType=INTEGER}, #{avatar,jdbcType=VARCHAR}, #{hobby,jdbcType=VARCHAR},
#{city,jdbcType=VARCHAR}, #{job,jdbcType=VARCHAR}, #{company,jdbcType=VARCHAR},
#{createdTime,jdbcType=TIMESTAMP})
</insert>
<insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.jch.entity.UserDetail" useGeneratedKeys="true">
insert into user_detail
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="userId != null">
user_id,
</if>
<if test="avatar != null and avatar != ''">
avatar,
</if>
<if test="hobby != null and hobby != ''">
hobby,
</if>
<if test="city != null and city != ''">
city,
</if>
<if test="job != null and job != ''">
job,
</if>
<if test="company != null and company != ''">
company,
</if>
<if test="createdTime != null">
created_time,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="userId != null">
#{userId,jdbcType=INTEGER},
</if>
<if test="avatar != null and avatar != ''">
#{avatar,jdbcType=VARCHAR},
</if>
<if test="hobby != null and hobby != ''">
#{hobby,jdbcType=VARCHAR},
</if>
<if test="city != null and city != ''">
#{city,jdbcType=VARCHAR},
</if>
<if test="job != null and job != ''">
#{job,jdbcType=VARCHAR},
</if>
<if test="company != null and company != ''">
#{company,jdbcType=VARCHAR},
</if>
<if test="createdTime != null">
#{createdTime,jdbcType=TIMESTAMP},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.jch.entity.UserDetail">
update user_detail
<set>
<if test="userId != null">
user_id = #{userId,jdbcType=INTEGER},
</if>
<if test="avatar != null and avatar != ''">
avatar = #{avatar,jdbcType=VARCHAR},
</if>
<if test="hobby != null and hobby != ''">
hobby = #{hobby,jdbcType=VARCHAR},
</if>
<if test="city != null and city != ''">
city = #{city,jdbcType=VARCHAR},
</if>
<if test="job != null and job != ''">
job = #{job,jdbcType=VARCHAR},
</if>
<if test="company != null and company != ''">
company = #{company,jdbcType=VARCHAR},
</if>
<if test="createdTime != null">
created_time = #{createdTime,jdbcType=TIMESTAMP},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.jch.entity.UserDetail">
update user_detail
set user_id = #{userId,jdbcType=INTEGER},
avatar = #{avatar,jdbcType=VARCHAR},
hobby = #{hobby,jdbcType=VARCHAR},
city = #{city,jdbcType=VARCHAR},
job = #{job,jdbcType=VARCHAR},
company = #{company,jdbcType=VARCHAR},
created_time = #{createdTime,jdbcType=TIMESTAMP}
where id = #{id,jdbcType=INTEGER}
</update>
最终目录结构如下:
三 调试
application.yml配置文件添加数据库:
## 数据源配置
spring:
datasource:
type: com.zaxxer.hikari.HikariDataSource
url: jdbc:sqlserver://10.100.51.148:14330;database=test
username: jch
password: 123456
# Hikari 连接池配置
# 最小空闲连接数量
hikari:
minimum-idle: 5
# 空闲连接存活最大时间,默认600000(10分钟)
idle-timeout: 180000
# 连接池最大连接数,默认是10
maximum-pool-size: 10
# 此属性控制从池返回的连接的默认自动提交行为,默认值:true
auto-commit: true
# 连接池名称
pool-name: RcProviderHikariCP
# 此属性控制池中连接的最长生命周期,值0表示无限生命周期,默认1800000即30分钟
max-lifetime: 300000
# 数据库连接超时时间,默认30秒,即30000
connection-timeout: 30000
# 控制将测试连接的活动性的最长时间。
validation-timeout: 3000
修改application.yml配置文件添加mybatis配置
#mybatis的相关配置
mybatis:
configuration:
#开启自动驼峰命名规则
map-underscore-to-camel-case: true
#自动映射数据表字段与对象的属性,对所有的 resultMap 都进行自动映射
auto-mapping-behavior: full
#Mapper 所对应的 XML 文件位置
mapper-locations: classpath:mapper/*.xml
现在已经可以启动了,可以右键SpringbootStartupApplication来启动web服务
默认启动是8080端口,为了防止冲突可以在application.yml文件首行添加
server.port: 19999
指定启动19999端口,启动成功可见如下日志
打开postman,用GET方式请求,得到第一页第一条数据
以上就是Mybatis入门实战的全部内容,相信你看完以后会对Mybatis的使用有了大致了解