application.properties配置文件
############################################ ## MySQL数据库连接 ############################################ spring.datasource.url = jdbc:mysql://localhost:3306/dongsuo_test?useUnicode=true&characterEncoding=utf8&useSSL=false spring.datasource.username = root spring.datasource.password = 123456 spring.datasource.driverClassName = com.mysql.jdbc.Driver ############################################ ## 配置自动建表:updata:没有表新建,有表更新操作,控制台显示建表语句 ############################################ spring.jpa.hibernate.ddl-auto=update spring.jpa.show-sql=true ############################################ # 加载mybatis 配置的Mapper ############################################ mybatis.mapper-locations=classpath:mybatisMapper/*Mapper.xml
1、创建所用到的表
-- 学生表 create table student( id int primary key auto_increment, name varchar(20), sex varchar(8), age int ) ENGINE=InnoDB DEFAULT CHARSET=utf8 alter table student add column c_id int; alter table student add constraint foreign key(c_id) references headclass(hc_id) insert into student(name,sex,age,c_id) values('tom','男',20,1); insert into student(name,sex,age,c_id) values('jack','男',18,2); insert into student(name,sex,age,c_id) values('chen','男',21,1); -- 课程表 create table course( id int primary key auto_increment, name varchar(20), credit int )ENGINE=InnoDB DEFAULT CHARSET=utf8 insert into course(name,credit) values('Math',5); insert into course(name,credit) values('Computer',3); -- 学生课程中间表 create table student_course( s_id int, c_id int, sc_date date, primary key(s_id,c_id), foreign key(s_id) references student(id), foreign key(c_id) references course(id) )ENGINE=InnoDB DEFAULT CHARSET=utf8 insert into student_course(s_id,c_id,sc_date) values(1,1,'2018-01-01'); insert into student_course(s_id,c_id,sc_date) values(1,2,'2018-01-02'); insert into student_course(s_id,c_id,sc_date) values(2,1,'2018-02-01'); insert into student_course(s_id,c_id,sc_date) values(2,2,'2018-06-12'); -- 班主任表 create table headtecher( ht_id int primary key auto_increment, ht_name varchar(20), ht_age int )engine=InnoDB default charset=utf8 insert into headtecher(ht_name,ht_age) values('张三',35); insert into headtecher(ht_name,ht_age) values('刘能',40); select *from headtecher; -- 班级表 create table headclass( hc_id int primary key auto_increment, hc_name varchar(20), ht_id int unique, foreign key(ht_id) references headtecher(ht_id) )engine=InnoDB default charset=utf8 insert into headclass(hc_name,ht_id) values('软件工程(1)班',1); insert into headclass(hc_name,ht_id) values('软件工程(2)班',2)
2、创建实体类
学生实体:
public class Student { private Integer id; private String name; private String sex; private Integer age; private List<Course> courses; //多对多 public Student() { } public Student(Integer id, String name, String sex, Integer age, List<Course> courses) { this.id = id; this.name = name; this.sex = sex; this.age = age; this.courses = courses; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } public List<Course> getCourses() { return courses; } public void setCourses(List<Course> courses) { this.courses = courses; } }
课程实体:
public class Course { private Integer id; private String name; private Integer credit; private List<Student> students; //多对多 public Course() { } public Course(Integer id, String name, Integer credit, List<Student> students) { this.id = id; this.name = name; this.credit = credit; this.students = students; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getCredit() { return credit; } public void setCredit(Integer credit) { this.credit = credit; } public List<Student> getStudents() { return students; } public void setStudents(List<Student> students) { this.students = students; } }
学生课程实体:
public class StudentCourse { private Student student; private Course course; private Date date; public StudentCourse() { } public StudentCourse(Student student, Course course, Date date) { this.student = student; this.course = course; this.date = date; } public Student getStudent() { return student; } public void setStudent(Student student) { this.student = student; } public Course getCourse() { return course; } public void setCourse(Course course) { this.course = course; } public Date getDate() { return date; } public void setDate(Date date) { this.date = date; } }
班主任实体:
public class HeadTecher { private Integer tid; private String tname; private Integer tage; public HeadTecher() { } public Integer getTid() { return tid; } public void setTid(Integer tid) { this.tid = tid; } public String getTname() { return tname; } public void setTname(String tname) { this.tname = tname; } public Integer getTage() { return tage; } public void setTage(Integer tage) { this.tage = tage; } }
班级实体:
public class HeadClass { private Integer cid; private String cname; private HeadTecher headTecher;//用于一对一 private List<Student> students;//用于一对多 public HeadClass() { } public Integer getCid() { return cid; } public void setCid(Integer cid) { this.cid = cid; } public String getCname() { return cname; } public void setCname(String cname) { this.cname = cname; } public HeadTecher getHeadTecher() { return headTecher; } public void setHeadTecher(HeadTecher headTecher) { this.headTecher = headTecher; } public List<Student> getStudents() { return students; } public void setStudents(List<Student> students) { this.students = students; } }
3、创建Mapper
HeadClassMapper:
@Mapper @Repository public interface HeadClassMapper { HeadClass selectClassById(@Param("id") Integer id); List<HeadTecher> getHeadTecher(); List<HeadClass> selectClassAndStudentById(@Param("id") Integer id); }
StudentMapper:
@Mapper @Repository public interface StudentMapper { public List<Student> selectStudentCourse(); }
StudentAllMapper:
@Mapper @Repository public interface StudentAllMapper { List linkTableSel(@Param("id") Integer id); }
4、创建Mapper对应的Mapper.xml文件
HeadClassMapper.xml:
<?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="com.study.springboot.mybatis.mapper.HeadClassMapper"> <!--一对一的对应关系--> <resultMap id="techermap" type="com.study.springboot.mybatis.entity.HeadTecher"> <id property="tid" column="ht_id" /> <result property="tname" column="ht_name" /> <result property="tage" column="ht_age" /> </resultMap> <resultMap id="classmap" type="com.study.springboot.mybatis.entity.HeadClass"> <id property="cid" column="hc_id" /> <result property="cname" column="hc_name"/> <association property="headTecher" column="ht_id" resultMap="techermap" /> </resultMap> <select id="selectClassById" resultMap="classmap"> SELECT *FROM headclass hc,headtecher ht WHERE hc.ht_id=ht.ht_id AND hc.hc_id=#{id} </select> <select id="getHeadTecher" resultMap="techermap"> SELECT * FROM headtecher </select> <!--一对多的对应关系--> <resultMap id="classmap2" type="com.study.springboot.mybatis.entity.HeadClass"> <id property="cid" column="hc_id" /> <result property="cname" column="hc_name" /> <association property="headTecher" column="ht_id" resultMap="techermap" /> <collection property="students" ofType="com.study.springboot.mybatis.entity.Student"> <id property="id" column="id"/> <result property="name" column="name"/> <result property="sex" column="sex"/> <result property="age" column="age" /> </collection> </resultMap> <select id="selectClassAndStudentById" resultMap="classmap2"> SELECT *FROM headclass hc,student s,headtecher ht WHERE hc.hc_id=s.c_id AND hc.ht_id=ht.ht_id AND hc.hc_id=#{id} </select> </mapper>
StudentMapper.xml:
<?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="com.study.springboot.mybatis.mapper.StudentMapper"> <!--resultMap 多对多的对应关系的映射--> <resultMap id="studentCourseMap" type="com.study.springboot.mybatis.entity.Student"> <id column="id" property="id"></id> <result column="name" property="name"></result> <result column="sex" property="sex"></result> <result column="age" property="age"></result> <collection property="courses" ofType="com.study.springboot.mybatis.entity.Course"> <id property="id" column="id"></id> <result property="name" column="name"></result> <result property="credit" column="credit"></result> </collection> </resultMap> <select id="selectStudentCourse" resultMap="studentCourseMap"> SELECT s.*, c.* FROM student s,course c,student_course sc WHERE s.id=sc.s_id AND c.id=sc.c_id </select> </mapper>
StudentAllMapper.xml(关系整合):
<?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="com.study.springboot.mybatis.mapper.StudentAllMapper"> <!--对应关系--> <resultMap id="studentmap" type="com.study.springboot.mybatis.entity.Student"> <id property="id" column="id"/> <result property="name" column="name"/> <result property="sex" column="sex" /> <result property="age" column="age" /> <collection property="courses" resultMap="coursemap" /> </resultMap> <resultMap id="coursemap" type="com.study.springboot.mybatis.entity.Course"> <id property="id" column="id" /> <result property="name" column="name" /> <result property="credit" column="credit" /> </resultMap> <resultMap id="techermap" type="com.study.springboot.mybatis.entity.HeadTecher"> <id property="tid" column="ht_id" /> <result property="tname" column="ht_name" /> <result property="tage" column="ht_age" /> </resultMap> <resultMap id="classmap" type="com.study.springboot.mybatis.entity.HeadClass"> <id property="cid" column="hc_id" /> <result property="cname" column="hc_name"/> <association property="headTecher" column="ht_id" resultMap="techermap" /> <collection property="students" ofType="com.study.springboot.mybatis.entity.Student" resultMap="studentmap"/> </resultMap> <select id="linkTableSel" resultMap="classmap"> select *from student S left join student_course SC on S.id=SC.s_id left join course C on SC.c_id=C.id left join headclass HC on S.c_id=HC.hc_id left join headtecher HT on HC.ht_id=HT.ht_id where 1=1 <if test="id!=null"> AND S.id=#{id} </if> </select> </mapper>
5、接口调用
@Autowired private HeadClassMapper headClassMapper; @RequestMapping(value = "/classInfo",method = RequestMethod.GET,produces = {"application/json"}) public DataResponseVo getClassInfo(@RequestParam(value = "id",required = true) Integer id){ return new DataResponseVo(0,headClassMapper.selectClassById(id)); } @RequestMapping(value = "/getTecherinfo",method = RequestMethod.GET,produces = {"application/json"}) public DataResponseVo getHeadTecher(){ return new DataResponseVo(0,headClassMapper.getHeadTecher()); } @RequestMapping(value = "/getclassandstudent",method = RequestMethod.GET,produces = {"application/json"}) public DataResponseVo getClassAndStudent(@RequestParam(value = "id",required = true) Integer id){ return new DataResponseVo(0,headClassMapper.selectClassAndStudentById(id)); }
@Autowired private StudentMapper studentMapper; @Autowired private StudentAllMapper studentAllMapper; @RequestMapping(value = "/info",method = RequestMethod.GET,produces = {"application/json"}) public DataResponseVo getStudentInfo(){ return new DataResponseVo(0,studentMapper.selectStudentCourse()); } @RequestMapping(value = "/linktable",method = RequestMethod.GET,produces = {"application/json"}) public DataResponseVo getlinkTableSel(@RequestParam(value = "id",required = true) Integer id){ return new DataResponseVo(0,studentAllMapper.linkTableSel(id)); }
6、返回结果
{
"code": 0,
"content": [
{
"cid": 1,
"cname": "软件工程(1)班",
"headTecher": {
"tid": 1,
"tname": "张三",
"tage": 35
},
"students": [
{
"id": 1,
"name": "tom",
"sex": "男",
"age": 20,
"courses": [
{
"id": 1,
"name": "tom",
"credit": 5,
"students": null
}
]
},
{
"id": 3,
"name": "chen",
"sex": "男",
"age": 21,
"courses": [
{
"id": 3,
"name": "chen",
"credit": null,
"students": null
}
]
}
]
},
{
"cid": 2,
"cname": "软件工程(2)班",
"headTecher": {
"tid": 2,
"tname": "刘能",
"tage": 40
},
"students": [
{
"id": 2,
"name": "jack",
"sex": "男",
"age": 18,
"courses": [
{
"id": 2,
"name": "jack",
"credit": 5,
"students": null
}
]
}
]
}
]
}