在本文中，您将会了解到关于mybatis-一对多查询的模型的新资讯，同时我们还将为您解释mybatis一对多查询配置详解的相关在本文中，我们将带你探索mybatis-一对多查询的模型的奥秘，分析myb
在本文中，您将会了解到关于mybatis-一对多查询的模型的新资讯，同时我们还将为您解释mybatis一对多查询配置详解的相关在本文中，我们将带你探索mybatis-一对多查询的模型的奥秘，分析mybatis一对多查询配置详解的特点，并给出一些关于java day55【 Mybatis 连接池与事务深入 、 Mybatis 的动态 SQL 语句、 Mybatis 多表查询之一对多 、 Mybatis 多表查询之多对多】、Java SSM框架之MyBatis3（四）MyBatis之一对一、一对多、多对多、Mybatis - 多对一 查询与 一对多查询、Mybatis left join 一对多及多对多查询配置的实用技巧。
本文目录一览：- mybatis-一对多查询的模型（mybatis一对多查询配置详解）
- java day55【 Mybatis 连接池与事务深入 、 Mybatis 的动态 SQL 语句、 Mybatis 多表查询之一对多 、 Mybatis 多表查询之多对多】
- Java SSM框架之MyBatis3（四）MyBatis之一对一、一对多、多对多
- Mybatis - 多对一 查询与 一对多查询
- Mybatis left join 一对多及多对多查询配置
mybatis-一对多查询的模型（mybatis一对多查询配置详解）
用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户，一对多查询的需求：查询一个用户，与此同时查询出该用户具有的订单
对应的sql语句：select *,o.id oid from user u left join orders o on u.id=o.uid;
1）修改User实体
public class Order {
private int id;
private Date ordertime;
private double total;
//代表当前订单从属于哪一个客户
private User user;
}
public class User {
private int id;
private String username;
private String password;
private Date birthday;
//代表当前用户具备哪些订单
private List<Order> orderList;
}
2)创建UserMapper接口
public interface UserMapper {
List<User> findAll();
}
3)配置UserMapper.xml
<mapper namespace="com.itheima.mapper.UserMapper">
<resultMap id="userMap" type="user">
<id column="uid" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="birthday" property="birthday"></result>
<!--配置集合信息
property:集合名称
ofType：当前集合中的数据类型
-->
<collection property="orderList" ofType="order">
<!--封装order的数据-->
<id column="oid" property="id"></id>
<result column="ordertime" property="ordertime"></result>
<result column="total" property="total"></result>
</collection>
</resultMap>
<select id="findAll" resultMap="userMap">
SELECT *,o.id oid FROM USER u,orders o WHERE u.id=o.uid
</select>
</mapper>
4)测试结果
@Test
public void test2() throws IOException {
InputStream resourceAsstream = Resources.getResourceAsstream("sqlMapConfig.xml");
sqlSessionFactory sqlSessionFactory = new sqlSessionFactoryBuilder().build(resourceAsstream);
sqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = mapper.findAll();
for (User user : userList) {
System.out.println(user);
}
sqlSession.close();
}
java day55【 Mybatis 连接池与事务深入 、 Mybatis 的动态 SQL 语句、 Mybatis 多表查询之一对多 、 Mybatis 多表查询之多对多】
第1章 Mybatis 连接池与事务深入
1.1 Mybatis 的连接池技术
1.1.1 Mybatis 连接池的分类
1.1.2 Mybatis 中数据源的配置
1.1.3 Mybatis 中 DataSource 的存取
1.1.4 Mybatis 中连接的获取过程分析
1.2 Mybatis 的事务控制
1.2.1 JDBC 中事务的回顾
1.2.2 Mybatis 中事务提交方式
1.2.3 Mybatis 自动提交事务的设置
第2章 Mybatis 的动态 SQL 语句
2.1 动态 SQL 之标签
2.1.1 持久层 Dao 接口
2.1.2 持久层 Dao 映射配置
2.1.3 测试
2.2 动态 SQL 之标签
2.2.1 持久层 Dao 映射配置
2.3 动态标签之标签
2.3.1 需求
2.3.1.1 在 QueryVo 中加入一个 List 集合用于封装参数
2.3.2 持久层 Dao 接口
2.3.3 持久层 Dao 映射配置
2.3.3.1 编写测试方法
2.4 Mybatis 中简化编写的 SQL 片段
2.4.1 定义代码片段
2.4.2 引用代码片段
第3章 Mybatis 多表查询之一对多
3.1 一对一查询(多对一)
3.1.1 方式一
3.1.1.1 定义账户信息的实体类
3.1.1.2 编写 Sql 语句
3.1.1.3 定义 AccountUser 类
3.1.1.4 定义账户的持久层 Dao 接口
3.1.1.5 定义 AccountDao.xml 文件中的查询配置信息
3.1.1.6 创建 AccountTest 测试类
3.1.1.7 小结：
3.1.2 方式二
3.1.2.1 修改 Account 类
在 Account 类中加入 User 类的对象作为 Account 类的一个属性。
3.1.2.2 修改 AccountDao 接口中的方法
3.1.2.3 重新定义 AccountDao.xml 文件
3.1.2.4 在 AccountTest 类中加入测试方法
3.2 一对多查询
3.2.1 编写 SQL 语句
3.2.2 User 类加入 List
3.2.3 用户持久层 Dao 接口中加入查询方法
3.2.4 用户持久层 Dao 映射文件配置
3.2.5 测试方法
第4章 Mybatis 多表查询之多对多
4.1 实现 Role 到 User 多对多
4.1.1 用户与角色的关系模型
4.1.2 业务要求及实现 SQL
4.1.3 编写角色实体类
4.1.4 编写 Role 持久层接口
4.1.5 编写映射文件
4.1.6 编写测试类
4.2 实现 User 到 Role 的多对多
4.2.1 User 到 Role 的多对多
4.2.2 作业：实现 User 到 Role 的一对多查询
Java SSM框架之MyBatis3（四）MyBatis之一对一、一对多、多对多
项目搭建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
Mybatis - 多对一 查询与 一对多查询
多对一 查询
<resultMap id="" type="">
<result property="" column=""/>
<result property="" column=""/>
<!--
多对一时使用此标签：association(一个对象)
一对多时使用此标签：collection(一个集合)
-->
<association property="" javaType="">
<result property="" column=""/>
<result property="" column=""/>
</association>
</resultMap>
一对多查询
<resultMap id="" type="">
<result property="" column=""/>
<result property="" column=""/>
<!--
多对一时使用此标签：association(一个对象)
一对多时使用此标签：collection(一个集合)
-->
<collection property="" ofType="">
<result property="" column=""/>
<result property="" column=""/>
</collection>
</resultMap>
Mybatis left join 一对多及多对多查询配置
一对一查询配置
<!-- 一对一查询 ，一个订单对应一个用户-->
<select id="findOrdersUser" resultType="com.OrdersCustom" resultMap="OrdersUserResultMap">
SELECT
t1.*,
t2.*
FROM
orders t1
LEFT JOIN
t_user t2
WHERE
t1.user_id=t2.id
</select>
<resultMap type="com.Orders" id="OrdersUserResultMap">
<id column="id" property="id"/>
<result column="user_id" property="userid"/>
<result column="number" property="number"/>
<result column="createtime" property="createTime"/>
<result column="note" property="note"/>
<!-- 订单关联用户 -->
<association property="user" javaType="com.User">
<id column="user_id" property="id"/>
<result column="username" property="username"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
</association>
</resultMap>
一对多查询（left join 主要是为了当多方为null时一方可以查）
<!-- 一对多查询，一个订单对应多个订单明细 -->
<select id="findOrdersUser" resultType="com.OrdersCustom" resultMap="OrdersUserResultMap">
SELECT
t1.*,
t2.*
FROM
orders t1
LEFT JOIN
t_user t2
WHERE
t1.user_id=t2.id
</select>
<resultMap type="com.Orders" id="ordersAndOrderDetailResultMap">
<id column="id" property="id"/>
<result column="user_id" property="userid"/>
<result column="number" property="number"/>
<result column="createtime" property="createTime"/>
<result column="note" property="note"/>
<!-- 关联订单详细 -->
<collection property="orderdetails" ofType="com.OrderDetail">
<id column="orderdetail_id" property="id"/>
<result column="items_id" property="itemsId"/>
<result column="items_num" property="itemsNum"/>
<result column="orders_id" property="ordersId"/>
</collection>
</resultMap>
多对多查询
<!-- 多对多查询，查询用户以及用户购买的商品信息，一个用户对应多个订单，一个订单对应多个订单明细，一个订单明细对应一个商品 -->
<select id="findUserAndItemsResultMap" resultMap="userAndItemsResultMap">
SELECT
t1.*,
t2.username,
t2.sex,
t2.address,
t3.id orderdetail_id,
t3.items_id,
t3.items_num,
t3.orders_id,
t4.itemsname items_name,
t4.detail items_detail,
t4.price items_price
FROM
orders t1,
t_user t2,
orderdetail t3,
items t4
WHERE t1.user_id = t2.id AND t3.orders_id=t1.id AND t3.items_id = t4.id
</select>
<resultMap type="com.User" id="userAndItemsResultMap">
<!-- 用户信息 -->
<id column="user_id" property="id"/>
<result column="username" property="username"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
<!-- 一个用户对应多个订单 -->
<collection property="ordersList" ofType="com.Orders">
<id column="id" property="id"/>
<result column="user_id" property="userid"/>
<result column="number" property="number"/>
<result column="createtime" property="createTime"/>
<result column="note" property="note"/>
<!-- 一个订单对应多个订单明细 -->
<collection property="orderdetails" ofType="com.OrderDetail">
<id column="orderdetail_id" property="id"/>
<result column="items_id" property="itemsId"/>
<result column="items_num" property="itemsNum"/>
<result column="orders_id" property="ordersId"/>
<!-- 一个订单明细对应一个商品 -->
<association property="items" javaType="com.Items">
<id column="items_id" property="id"/>
<result column="items_name" property="itemsName"/>
<result column="items_detail" property="detail"/>
<result column="items_price" property="price"/>
</association>
</collection>
</collection>
</resultMap>
今天的关于mybatis-一对多查询的模型和mybatis一对多查询配置详解的分享已经结束，谢谢您的关注，如果想了解更多关于java day55【 Mybatis 连接池与事务深入 、 Mybatis 的动态 SQL 语句、 Mybatis 多表查询之一对多 、 Mybatis 多表查询之多对多】、Java SSM框架之MyBatis3（四）MyBatis之一对一、一对多、多对多、Mybatis - 多对一 查询与 一对多查询、Mybatis left join 一对多及多对多查询配置的相关知识，请在本站进行查询。
本文标签：
