Java MyBatis3(4)一对一、一对多、多对多
项目搭建Springboot 1.5
pom.xml
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>cn.cnki</groupId> <artifactId>ref</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>war</packaging> <name>ref</name> <description>Demo project for Spring Boot</description> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>1.5.14.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.2</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-tomcat</artifactId> <scope>provided</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <!-- mysql连接 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <!-- mybatis --> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.1.1</version> </dependency> <!-- 阿里系的Druid依赖包 --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.5</version> </dependency> <!--pageHelper分页插件 --> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper-spring-boot-starter</artifactId> <version>1.1.1</version> </dependency> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis-spring</artifactId> <version>1.3.2</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-tx</artifactId> <version>4.3.18.RELEASE</version> </dependency> <!--To display the auto-configuration report re-run your application with 'debug' enabled. --> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-core</artifactId> <version>5.0.7.Final</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>
application.properties
server.port = 8080 ######################################################## ###mybatis-mysql-druid ######################################################## #sql配置文件的路径 mybatis.mapper-locations="classpath:mapper/*.xml" #这个是mybatis全局配置文件路径 mybatis.config-location="classpath:mapper/config/mybatis-config.xml" #实体类路径 mybatis.type-aliases-package="cn.cnki.ref.pojo" # 驱动配置信息 spring.datasource.type=com.alibaba.druid.pool.DruidDataSource spring.datasource.url = jdbc:mysql://127.0.0.1:3306/springbootmybatis?useUnicode=true&characterEncoding=utf-8 spring.datasource.username = root spring.datasource.password = root spring.datasource.driverClassName = com.mysql.jdbc.Driver ########################################################
mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <properties> <property name="dialect" value="mysql" /> </properties> <settings> <!-- 这个配置使全局的映射器启用或禁用缓存。系统默认值是true,设置只是为了展示出来 --> <setting name="cacheEnabled" value="true" /> <!-- 全局启用或禁用延迟加载。当禁用时,所有关联对象都会即时加载。 系统默认值是true,设置只是为了展示出来 --> <setting name="lazyLoadingEnabled" value="true" /> <!-- 允许或不允许多种结果集从一个单独的语句中返回(需要适合的驱动)。 系统默认值是true,设置只是为了展示出来 --> <setting name="multipleResultSetsEnabled" value="true" /> <!--使用列标签代替列名。不同的驱动在这方便表现不同。参考驱动文档或充分测试两种方法来决定所使用的驱动。 系统默认值是true,设置只是为了展示出来 --> <setting name="useColumnLabel" value="true" /> <!--允许 JDBC 支持生成的键。需要适合的驱动。如果设置为 true 则这个设置强制生成的键被使用,尽管一些驱动拒绝兼容但仍然有效(比如 Derby)。 系统默认值是false,设置只是为了展示出来 --> <setting name="useGeneratedKeys" value="false" /> <!--配置默认的执行器。SIMPLE 执行器没有什么特别之处。REUSE 执行器重用预处理语句。BATCH 执行器重用语句和批量更新 系统默认值是SIMPLE,设置只是为了展示出来 --> <setting name="defaultExecutorType" value="SIMPLE" /> <!--设置超时时间,它决定驱动等待一个数据库响应的时间。 系统默认值是null,设置只是为了展示出来 --> <setting name="defaultStatementTimeout" value="25000" /> <setting name="logImpl" value="STDOUT_LOGGING"/> </settings> <plugins> <plugin interceptor="com.github.pagehelper.PageHelper"> <property name="dialect" value="mysql" /> <property name="offsetAsPageNum" value="true" /> <property name="rowBoundsWithCount" value="true" /> <property name="pageSizeZero" value="true" /> <property name="reasonable" value="true" /> </plugin> </plugins> </configuration>
RefApplication
package cn.cnki.ref; import com.alibaba.druid.pool.DruidDataSource; import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; import org.mybatis.spring.annotation.MapperScan; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration; import org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaAutoConfiguration; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.context.annotation.Bean; import org.springframework.core.io.support.PathMatchingResourcePatternResolver; import org.springframework.jdbc.datasource.DataSourceTransactionManager; import org.springframework.transaction.PlatformTransactionManager; @SpringBootApplication(exclude={DataSourceAutoConfiguration.class,HibernateJpaAutoConfiguration.class}) @MapperScan("cn.cnki.ref.mapper") public class RefApplication { public static void main(String[] args) { SpringApplication.run(RefApplication.class, args); } //datasource注入 @Bean(initMethod = "init", destroyMethod = "close") @ConfigurationProperties(prefix = "spring.datasource") public DruidDataSource dataSource() { return new DruidDataSource(); } //mybatis SQLSession注入 @Bean public SqlSessionFactory sqlSessionFactoryBean() throws Exception { PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver(); SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(dataSource()); sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath*:/mapper/*Mapper.xml")); return sqlSessionFactoryBean.getObject(); } //事务支持 @Bean public PlatformTransactionManager transactionManager() { return new DataSourceTransactionManager(dataSource()); } }
一对一association
每个学生对应一张身份证,每张身份证上有身份证号cardId、开始日期、结束日期。并且建立与student表的外键关系。
cardid表、student表
/*Table structure for table `cardid` */ DROP TABLE IF EXISTS `cardid`; CREATE TABLE `cardid` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `cardId` varchar(18) NOT NULL, `sid` int(10) NOT NULL, `beginTime` datetime NOT NULL, `endTime` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; /*Data for the table `cardid` */ insert into `cardid`(`id`,`cardId`,`sid`,`beginTime`,`endTime`) values (1,'412728199513161601',1,'2008-07-19 11:19:27','2018-07-19 11:19:30'),(2,'412728199513161602',2,'2008-04-01 11:20:13','2018-04-01 11:20:13'),(3,'412728199513161603',3,'2008-07-19 11:21:06','2018-07-19 11:21:16'); /*Table structure for table `student` */ DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; /*Data for the table `student` */ insert into `student`(`id`,`name`) values (1,'张三'),(2,'李四'),(3,'王五');
Cardid实体
package cn.cnki.ref.pojo; import java.util.Date; public class Cardid { private Integer id; private String cardid; private Integer sid; private Date begintime; private Date endtime; private Student student; public Student getStudent() { return student; } public void setStudent(Student student) { this.student = student; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getCardid() { return cardid; } public void setCardid(String cardid) { this.cardid = cardid == null ? null : cardid.trim(); } public Integer getSid() { return sid; } public void setSid(Integer sid) { this.sid = sid; } public Date getBegintime() { return begintime; } public void setBegintime(Date begintime) { this.begintime = begintime; } public Date getEndtime() { return endtime; } public void setEndtime(Date endtime) { this.endtime = endtime; } }
Student实体
package cn.cnki.ref.pojo; public class Student { private Integer id; private String name; 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 == null ? null : name.trim(); } }
CardidMapper
package cn.cnki.ref.mapper; import cn.cnki.ref.pojo.Cardid; import java.util.List; public interface CardidMapper { /** * 获取包括学生信息的身份证列表 * @return */ public List<Cardid> getListIdOfStudent(); }
CardidMapper.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="cn.cnki.ref.mapper.CardidMapper" > <!-- 学生表与身份证表的一对一映射--> <resultMap type="cn.cnki.ref.pojo.Cardid" id="IDCardOfStudentMap"> <id property="id" column="id" jdbcType="INTEGER"/> <result property="cardid" column="cardid" jdbcType="VARCHAR" /> <result property="begintime" column="beginTime" jdbcType="TIMESTAMP"/> <result property="endtime" column="endTime" jdbcType="TIMESTAMP"/> <association property="student" javaType="cn.cnki.ref.pojo.Student"> <id property="id" column="id" jdbcType="INTEGER"/> <result property="name" column="name" jdbcType="VARCHAR"/> </association> </resultMap> <!-- 查出带有学生信息的身份证列表 --> <select id="getListIdOfStudent" resultMap="IDCardOfStudentMap"> SELECT * FROM cardid a,student b WHERE a.sid=b.id; </select> </mapper>
CardidController
package cn.cnki.ref.controller; import cn.cnki.ref.mapper.CardidMapper; import cn.cnki.ref.pojo.Cardid; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.RestController; import java.util.List; @RestController @RequestMapping("/Cardid") public class CardidController { @Autowired private CardidMapper CardidMapper; @RequestMapping(value = "/GetListIdOfStudent", method = RequestMethod.GET) public List<Cardid> index() { return CardidMapper.getListIdOfStudent(); } }
测试
http://localhost:8080/Cardid/GetListIdOfStudent
一对多Collection
一个人有好多本书,每本书的主人只有一个人。当我们查询某个人拥有的所有书籍时,就涉及到了一对多的映射关系。
book表
/*Table structure for table `book` */ DROP TABLE IF EXISTS `book`; CREATE TABLE `book` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `sid` int(10) NOT NULL, `price` decimal(10,0) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; /*Data for the table `book` */ insert into `book`(`id`,`name`,`sid`,`price`) values (1,'《黑客与画家》',1,'45'),(2,'《北方的空地》',1,'40');
Book实体
package cn.cnki.ref.pojo; public class Book { private Integer id; private String name; private Integer sid; private Long price; 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 == null ? null : name.trim(); } public Integer getSid() { return sid; } public void setSid(Integer sid) { this.sid = sid; } public Long getPrice() { return price; } public void setPrice(Long price) { this.price = price; } }
Student实体
package cn.cnki.ref.pojo; import java.util.List; public class Student { private Integer id; private String name; private List<Book> books; public Student() { } public List<Book> getBooks() { return books; } public void setBooks(List<Book> books) { this.books = books; } 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 == null ? null : name.trim(); } }
StudentMapper
package cn.cnki.ref.mapper; import cn.cnki.ref.pojo.Student; import java.util.List; public interface StudentMapper { /** * 根据id查询 * @param id * @return */ public Student getById(Integer id); /** * 查询全部 * @return */ public List<Student> list(); /** * 插入 * @param student */ public int insert(Student student); /** * 主键回填的插入 * @param student * @return */ public int insertToId(Student student); /** * 根据student的id修改 * @param student */ public void update(Student student); /** * 根据id删除 * @param id */ public void delete(Integer id); /** * 根据id查询所有的书 * @param id */ public Student selectBookById(Integer id); }
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="cn.cnki.ref.mapper.StudentMapper"> <!-- 根据id查询 --> <select id="getById" parameterType="int" resultType="cn.cnki.ref.pojo.Student"> select * from student where id=#{id} </select> <!-- 查询所有 --> <select id="list" parameterType="int" resultType="cn.cnki.ref.pojo.Student"> select * from student </select> <!-- 插入一个学生 --> <insert id="insert" parameterType="cn.cnki.ref.pojo.Student"> insert into student(name) values(#{name}) </insert> <!-- 主键回填的插入 --> <insert id="insertToId" parameterType="cn.cnki.ref.pojo.Student" useGeneratedKeys="true" keyProperty="id"> insert into student(name) values(#{name}) </insert> <!-- 根据id修改学生信息 --> <update id="update" parameterType="cn.cnki.ref.pojo.Student"> update student set name=#{name} where id=#{id} </update> <!-- 根据id删除学生 --> <delete id="delete" parameterType="int"> delete from student where id=#{id} </delete> <resultMap type="cn.cnki.ref.pojo.Student" id="bookMap"> <id property="id" column="id" jdbcType="INTEGER"/> <result property="name" column="name" jdbcType="VARCHAR"/> <collection property="books" ofType="cn.cnki.ref.pojo.Book" javaType="ArrayList" > <id property="id" column="bid" jdbcType="INTEGER"/> <result property="name" column="bname" jdbcType="VARCHAR" /> <result property="price" column="price" jdbcType="DECIMAL"/> </collection> </resultMap> <!--根据id查询所有的书 --> <select id="selectBookById" parameterType="int" resultMap="bookMap"> select a.*,b.id bid,b.name bname,b.price from student a,book b where a.id=b.sid and a.id=#{id}; </select> </mapper>
StudentController
package cn.cnki.ref.controller; import cn.cnki.ref.mapper.StudentMapper; import cn.cnki.ref.pojo.Student; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.*; import java.util.List; @RestController public class StudentController { @Autowired private StudentMapper studentMapper; /** * 根据id查询学生 * @param id * @return */ @GetMapping("/student/{id}") public Student getById(@PathVariable("id") Integer id) { Student student = studentMapper.getById(id); return student; } /** * 查询全部 * @return */ @GetMapping("/students") public List<Student> list(){ List<Student> students = studentMapper.list(); return students; } /** * 插入 * @param student */ @PostMapping("/student") public void insert( Student student) { studentMapper.insert(student); } /** * 修改 * @param student */ @PutMapping("/student/{id}") public void update(Student student,@PathVariable("id")Integer id) { studentMapper.update(student); } /** * 根据id删除 * @param id */ @DeleteMapping("/student/{id}") public void delete(@PathVariable("id") Integer id) { studentMapper.delete(id); } /** * 根据id查询所有的书 */ @GetMapping("/student/book/{id}") public Student getBooks(@PathVariable("id") Integer id) { Student student = studentMapper.selectBookById(id); return student; } }
测试
http://localhost:8080//student/book/1
多对多——查询某个学生所选的全部课程代码实现
之前我们探讨了一对一、一对多的映射关系,今天我们来讨论多对多的映射关系。
多对多,其实可以拆成多个一对多来理解。
比如:
学生-------课程----------学生选课的关系:
(1)查询某个学生所选的全部课程;
(2)查询选修某个课程的全部学生;
course表、student_course表
/*Table structure for table `course` */ DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; /*Data for the table `course` */ insert into `course`(`id`,`name`) values (1,'语文'),(2,'数学'),(3,'英语'); /*Table structure for table `student_course` */ DROP TABLE IF EXISTS `student_course`; CREATE TABLE `student_course` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `sid` int(10) NOT NULL, `cid` int(10) NOT NULL, `score` int(3) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8; /*Data for the table `student_course` */ insert into `student_course`(`id`,`sid`,`cid`,`score`) values (1,1,1,91),(2,1,2,92),(3,1,3,93),(4,2,1,81),(5,2,2,82),(6,2,3,83),(7,3,1,71),(8,3,2,72),(9,3,3,73);
Course实体
package cn.cnki.ref.pojo; public class Course { private Integer id; private String name; 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 == null ? null : name.trim(); } }
StudentCourse实体
package cn.cnki.ref.pojo; import java.util.List; public class StudentCourse { private Integer id; private Integer sid; private Integer cid; private Integer score; private List<Student> students;//一门课有多名学生 private List<Course> courses;//一名学生有多门课 public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public Integer getSid() { return sid; } public void setSid(Integer sid) { this.sid = sid; } public Integer getCid() { return cid; } public void setCid(Integer cid) { this.cid = cid; } public Integer getScore() { return score; } public void setScore(Integer score) { this.score = score; } public List<Student> getStudents() { return students; } public void setStudents(List<Student> students) { this.students = students; } public List<Course> getCourses() { return courses; } public void setCourses(List<Course> courses) { this.courses = courses; } }
在Studnent实体中添加StudentCourse列表
package cn.cnki.ref.pojo; import java.util.List; public class Student { private Integer id; private String name; private List<Book> books;//一名学生有多本书 private List<StudentCourse> studentCourses; public Student() { } 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 == null ? null : name.trim(); } public List<Book> getBooks() { return books; } public List<StudentCourse> getStudentCourses() { return studentCourses; } public void setStudentCourses(List<StudentCourse> studentCourses) { this.studentCourses = studentCourses; } public void setBooks(List<Book> books) { this.books = books; } }
StudentCourseMapper
package cn.cnki.ref.mapper; import cn.cnki.ref.pojo.Student; public interface StudentCourseMapper { /** * 根据学生id查询该学生选修的所有课程 * @param id * @return */ public Student selectCourseById(Integer id); }
StudentCourseMapper.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="cn.cnki.ref.mapper.StudentCourseMapper"> <resultMap type="cn.cnki.ref.pojo.Student" id="studentMap"> <id property="id" column="id" jdbcType="INTEGER"/> <result property="name" column="name" jdbcType="VARCHAR"/> </resultMap> <resultMap type="cn.cnki.ref.pojo.Student" id="courseMap" extends="studentMap"> <collection property="studentCourses" ofType="cn.cnki.ref.pojo.StudentCourse" javaType="ArrayList"> <result property="score" column="score" jdbcType="INTEGER"/> <collection property="courses" ofType="cn.cnki.ref.pojo.Course" javaType="ArrayList"> <id property="id" column="cid" jdbcType="INTEGER"/> <result property="name" column="cname" jdbcType="VARCHAR"/> </collection> </collection> </resultMap> <!-- 根据学生id查询该学生选修的所有课程 --> <select id="selectCourseById" parameterType="int" resultMap="courseMap" > select a.*,b.score,c.id cid,c.name cname from student a,student_course b,course c where a.id=b.sid and b.cid=c.id and a.id=#{id} </select> </mapper>
StudentCourseController
package cn.cnki.ref.controller; import cn.cnki.ref.mapper.StudentCourseMapper; import cn.cnki.ref.pojo.Student; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PathVariable; import org.springframework.web.bind.annotation.RestController; @RestController public class StudentCourseController { @Autowired private StudentCourseMapper studentCourseMapper; /************/ /** * 根据学生id查询该学生选修的所有课程 * @param id * @return */ @GetMapping("/student/course/{id}") public Student selectCourseById(@PathVariable("id") Integer id) { Student student = studentCourseMapper.selectCourseById(id); return student; } }
测试
http://localhost:8080/student/course/1
多对多——查询选修某个课程的全部学生代码实现
Course实体中添加StudentCourse列表
package cn.cnki.ref.pojo; import java.util.List; public class Course { private Integer id; private String name; private List<StudentCourse> studentCourses;// public List<StudentCourse> getStudentCourses() { return studentCourses; } public void setStudentCourses(List<StudentCourse> studentCourses) { this.studentCourses = studentCourses; } 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 == null ? null : name.trim(); } }
CourseMapper
package cn.cnki.ref.mapper; import cn.cnki.ref.pojo.Course; public interface CourseMapper { /** * 根据课程id查询选修此课程的全部学生 * @param id * @return */ public Course selectStudentById(Integer id); }
CourseMapper.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="cn.cnki.ref.mapper.CourseMapper"> <resultMap type="cn.cnki.ref.pojo.Course" id="courseMap"> <id property="id" column="id"/> <result property="name" column="name"/> </resultMap> <resultMap type="cn.cnki.ref.pojo.Course" id="studentMap" extends="courseMap"> <collection property="studentCourses" ofType="cn.cnki.ref.pojo.StudentCourse"> <result property="score" column="score"/> <collection property="students" ofType="cn.cnki.ref.pojo.Student"> <id property="id" column="sid" jdbcType="INTEGER"/> <result property="name" column="sname" jdbcType="VARCHAR"/> </collection> </collection> </resultMap> <!-- 根据课程id查询选修此课程的全部学生 --> <select id="selectStudentById" parameterType="int" resultMap="studentMap"> select a.*,b.score,c.id sid,c.name sname from student c,student_course b,course a where a.id=b.cid and b.sid=c.id and a.id=#{id} </select> </mapper>
CourseController
package cn.cnki.ref.controller; import cn.cnki.ref.mapper.CourseMapper; import cn.cnki.ref.pojo.Course; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PathVariable; import org.springframework.web.bind.annotation.RestController; @RestController public class CourseController { @Autowired private CourseMapper courseMapper; /** * 根据课程id查询选修此课程的全部学生 * @param id * @return */ @GetMapping("/course/student/{id}") public Course selectStudentById(@PathVariable("id")Integer id) { Course course = courseMapper.selectStudentById(id); return course; } }
测试
http://localhost:8080//course/student/1
资料
http://www.cnblogs.com/TimerHotel/tag/mybatis/
https://www.cnblogs.com/ashleyboy/p/9271597.html