github地址:https://github.com/lyncodes/mybatis-rest
如果有用,给我一个赞!!!
一直被spring boot 和mybatis整合的问题所困扰,由于spring boot大量使用了反射机制,虽然说是方便了熟手快速开发一个服务,但是对于新手却是比较难上手的,所以我再趟过了无数的坑之后,决定回馈社区,帮助更多的人.
综述
首先,你要明白你的restful api要对外界提供那些资源,说白了也就是那些数据需要被塞到json里面去,所以就先准备数据,那自然是自己先往数据库先创建一些咯,然后通过mybatis在spring boot和mysql之间做桥梁,完成数据的CRUD,然后spring boot再把获取的数据打包成json传到客户端或者从客户端接收到表单,然后把数据存到数据库.
所以步骤如下
- 往数据库先塞数据
- 创建根据数据库的字段创建相应的实体类
- 创建dao层负责和数据库的交互
- 创建service层作为controller和dao层的中介(业务逻辑也是在这里发生,但是我们的demo不会写具体的复杂业务逻辑)
- controller负责和客户端进行数据的交互
建表和插入数据
drop table if exists `person`;
create table `person`
(
`user_id` INTEGER auto_increment primary key not null comment '用户id',
`name` varchar(20) not null comment '名字',
`birthday` timestamp not null comment '生日',
`edu` enum ("primary","middle","high","graduate","master","phd") not null comment '教育程度',
`marry` enum ("married","unmarried","divorced","widower","widow") not null comment '婚姻状况',
`income` int not null comment '收入',
`gender` enum ("male","female") not null comment '性别',
`career` varchar(30) not null comment '职业'
) ENGINE = INNODB comment '用户基本信息表';
drop table if exists `training`;
create table `training`
(
`training_id` integer primary key auto_increment not null comment '训练id',
`user_id` INTEGER not null comment '用户id',
`training_start` timestamp not null comment '训练开始时间',
`training_end` timestamp not null comment '训练结束时间',
`training_kind` enum ("strength","endurance","speed","flexibility") not null comment '训练种类',
`coach` varchar(20) not null comment '教练名称',
`position` enum ("outdoor","playground","pool") not null comment '训练地点',
`performance` enum ("A+","A","A-","B+","B","B-","C+","C","C-") not null COMMENT '训练评价'
) ENGINE = INNODB comment '训练表';
drop table if exists `diet`;
create table `diet`
(
`diet_id` integer primary key auto_increment not null comment '饮食id',
`user_id` INTEGER not null comment '用户id',
`diet_start` timestamp not null comment '饮食开始时间',
`diet_end` timestamp not null comment '饮食结束时间',
`staple_kind` varchar(20) not null comment '主食种类',
`staple_amount` int not null comment '主食量(克)',
`water` int not null comment '水的摄入量',
`protein` int not null comment '蛋白质摄入量(克)',
`vegetable_kind` varchar(20) not null comment '摄入蔬菜种类',
`vegetable_amount` int not null comment '摄入蔬菜量(克)'
) ENGINE = INNODB comment '饮食表';
注意三张表的数据类型使用了时间戳和枚举类型,并且所有字段不允许为空,有助于提高鲁棒性,并且方便后续做数据分析的清洗.
接下来是插入数据,由于我在开发环境下,需要常常删表和重新恢复数据,所以这里用来删除自增主键,然后插入数据,然后再恢复自增主键的sql代码.
alter table test.person
modify user_id int;
alter table test.person
drop primary key;
INSERT INTO test.person (user_id, name, birthday, edu, marry, income, gender, career) VALUES (1, 'tom', '2019-08-27 15:53:34', 'graduate', 'married', 10000, 'male', 'engineer');
INSERT INTO test.person (user_id, name, birthday, edu, marry, income, gender, career) VALUES (2, 'jim', '2019-08-27 16:26:34', 'phd', 'unmarried', 20000, 'male', 'docter');
# -----------------插入---------------------
alter table test.person
add primary key (user_id);
alter table test.person
modify user_id int auto_increment;
# ==================下一个表=====================
alter table test.diet
modify diet_id int;
alter table test.diet
drop primary key;
INSERT INTO test.diet (diet_id, user_id, diet_start, diet_end, staple_kind, staple_amount, water, protein, vegetable_kind, vegetable_amount) VALUES (1, 1, '2019-08-27 16:16:21', '2019-08-27 16:16:23', 'rice', 100, 300, 30, 'potato', 200);
INSERT INTO test.diet (diet_id, user_id, diet_start, diet_end, staple_kind, staple_amount, water, protein, vegetable_kind, vegetable_amount) VALUES (2, 1, '2019-08-27 16:16:45', '2019-08-27 16:16:47', 'noddle', 200, 400, 40, 'tomato', 300);
INSERT INTO test.diet (diet_id, user_id, diet_start, diet_end, staple_kind, staple_amount, water, protein, vegetable_kind, vegetable_amount) VALUES (3, 1, '2019-08-27 16:17:54', '2019-08-27 16:17:56', 'pizza', 200, 200, 50, 'onion', 100);
# -----------------插入---------------------
alter table test.diet
add primary key (diet_id);
alter table test.diet
modify diet_id int auto_increment;
# ==================下一个表=====================
alter table test.training
modify training_id int;
alter table test.training
drop primary key;
INSERT INTO test.training (training_id, user_id, training_start, training_end, training_kind, coach, position, performance) VALUES (1, 1, '2019-08-27 16:19:11', '2019-08-27 16:19:13', 'speed', 'jim', 'outdoor', 'A+');
INSERT INTO test.training (training_id, user_id, training_start, training_end, training_kind, coach, position, performance) VALUES (2, 1, '2019-08-27 16:19:36', '2019-08-27 16:19:37', 'endurance', 'jack', 'playground', 'B');
INSERT INTO test.training (training_id, user_id, training_start, training_end, training_kind, coach, position, performance) VALUES (3, 1, '2019-08-27 16:20:59', '2019-08-27 16:21:00', 'flexibility', 'john', 'pool', 'A');
INSERT INTO test.training (training_id, user_id, training_start, training_end, training_kind, coach, position, performance) VALUES (4, 2, '2019-08-27 16:27:34', '2019-08-27 16:27:35', 'flexibility', 'jim', 'playground', 'A-');
# -----------------插入---------------------
alter table test.training
add primary key (training_id);
alter table test.training
modify training_id int auto_increment;
# ==================下一个表=====================
分别是两个用户的用户信息和训练,饮食记录,使用user_id作为三张表关联的外键,并且person表的user_id作为自增主键,保证一致性.
项目结构
如上面的图所示,用户的请求到达controller后,会把服务转发到service层,然后根据service层的接口中的函数,去接口的实现类中去拿相应的数据,这时候请求被转发到dao层,dao层只有interface,interface中的函数则不会用过java代码去执行了,而是通过了反射机制,转发到了resource/mybatis/mappers中写好的xml配置文件,这个时候才会去执行xml中定义好的sql语句,拿到真正的数据后,再一层一层往上回传到controller,在返回给请求端.
数据字段
可以看见training表有若干字段,然后在entity中建立好对应的实体类Training,java,
其实就是一个pojo,记得要自动生成所有字段的getter 和 setter,还有constructor
public class Training {
private long trainingId;
private long userId;
private java.sql.Timestamp trainingStart;
private java.sql.Timestamp trainingEnd;
private String trainingKind;
private String coach;
private String position;
private String performance;
public Training(long trainingId, long userId, Timestamp trainingStart,
Timestamp trainingEnd, String trainingKind, String coach,
String position, String performance) {
this.trainingId = trainingId;
this.userId = userId;
this.trainingStart = trainingStart;
this.trainingEnd = trainingEnd;
this.trainingKind = trainingKind;
this.coach = coach;
this.position = position;
this.performance = performance;
}
public long getTrainingId() {
return trainingId;
}
public void setTrainingId(long trainingId) {
this.trainingId = trainingId;
}
public long getUserId() {
return userId;
}
public void setUserId(long userId) {
this.userId = userId;
}
public java.sql.Timestamp getTrainingStart() {
return trainingStart;
}
public void setTrainingStart(java.sql.Timestamp trainingStart) {
this.trainingStart = trainingStart;
}
public java.sql.Timestamp getTrainingEnd() {
return trainingEnd;
}
public void setTrainingEnd(java.sql.Timestamp trainingEnd) {
this.trainingEnd = trainingEnd;
}
public String getTrainingKind() {
return trainingKind;
}
public void setTrainingKind(String trainingKind) {
this.trainingKind = trainingKind;
}
public String getCoach() {
return coach;
}
public void setCoach(String coach) {
this.coach = coach;
}
public String getPosition() {
return position;
}
public void setPosition(String position) {
this.position = position;
}
public String getPerformance() {
return performance;
}
public void setPerformance(String performance) {
this.performance = performance;
}
}
dao层
dao层负责了实体类和数据库表的映射,所以在这里编写数据交互的接口
spring.boot.mybatisrest.dao.Training.TrainingMapper.java
// spring.boot.mybatisrest.dao.Training.TrainingMapper.java
package spring.boot.mybatisrest.dao.Training;
import org.springframework.stereotype.Repository;
import spring.boot.mybatisrest.entity.Training;
import java.util.List;
@Repository
public interface TrainingMapper {
List<Training> getTrainings(int user_id);
}
关键点来了,这里的getTrainings
函数是没有对应java代码实现的,而是通过了配置文件来实现的,也就是对应的training-mapper.xml
resources/mybatis/mappers/training-mapper.xml
其中的select标签中,id后就跟着上面的getTrainings
函数,resultType则是把查询回来的数据映射到这个类型的实体类中,也就是training类,(所以,我们的实体类需要显示的声明构造函数), 当然,这个sql语句是很简单的,只需要说明一点就是变量的获取是#{}
,相似的操作在linux中,其实就是${}
,所以还是很好理解的.
<?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="spring.boot.mybatisrest.dao.Training.TrainingMapper">
<select id="getTrainings" resultType="spring.boot.mybatisrest.entity.Training">
SELECT * from training where user_id = #{user_id}
</select>
</mapper>
再往上的内容,其实就很简单了,就是controller到service层,再到service接口的实现类,为了简单起见,只实现了一个查询功能,其余的功能大家可以自行试试看.
spring.boot.mybatisrest.service.Training.TrainingService.java
package spring.boot.mybatisrest.service.Training;
import spring.boot.mybatisrest.entity.Training;
import java.util.List;
public interface TrainingService {
List<Training> getTrainings(int user_id);
TrainingService addTraining(int user_id);
TrainingService deleteTraining(int training_id);
TrainingService editTraining(int training_id);
}
spring.boot.mybatisrest.service.Training.TrainingServiceImpl.java
主要要把实现类加上@Service的注解
可以清晰的看到,这里就是通过去调用的mapper接口的函数去实现了和数据库的交互
package spring.boot.mybatisrest.service.Training;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import spring.boot.mybatisrest.dao.Training.TrainingMapper;
import spring.boot.mybatisrest.entity.Training;
import java.util.List;
@Service
public class TrainingServiceImpl implements TrainingService {
@Autowired
private TrainingMapper trainingMapper;
@Override
public List<Training> getTrainings(int user_id) {
return trainingMapper.getTrainings(user_id);
}
@Override
public TrainingService addTraining(int user_id) {
return null;
}
@Override
public TrainingService deleteTraining(int training_id) {
return null;
}
@Override
public TrainingService editTraining(int training_id) {
return null;
}
}
最后,不要忘了修改主启动类MybatisRestAppliation.ajva
一定要加上MapperScan,中间添加上你的mapper所在的路径
package spring.boot.mybatisrest;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("spring.boot.mybatisrest.dao")
public class MybatisRestApplication {
public static void main(String[] args) {
SpringApplication.run(MybatisRestApplication.class, args);
}
}
还有整个project的全局配置文件,指定mybatis的配置文件和所有的mapper映射文件的路径,当然还有数据库的相关配置
spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/test
username: root
password: root
mybatis:
mapper-locations: classpath:mybatis/mappers/*.xml
config-location: classpath:mybatis/mybatis-config.xml
运行一下,OK