想了解MyBatis查询：一对多映射关系的新动态吗？本文将为您提供详细的信息，我们还将为您解答关于mybatis一对多映射原理的相关问题，此外，我们还将为您介绍关于javaday55【Mybatis连
想了解MyBatis查询：一对多映射关系的新动态吗？本文将为您提供详细的信息，我们还将为您解答关于mybatis一对多映射原理的相关问题，此外，我们还将为您介绍关于java day55【 Mybatis 连接池与事务深入 、 Mybatis 的动态 SQL 语句、 Mybatis 多表查询之一对多 、 Mybatis 多表查询之多对多】、Java SSM框架之MyBatis3（四）MyBatis之一对一、一对多、多对多、java web(六):mybatis之一对一、一对多、多对多映射、Java_myBatis_一对多映射的新知识。
本文目录一览：- MyBatis查询：一对多映射关系（mybatis一对多映射原理）
- java day55【 Mybatis 连接池与事务深入 、 Mybatis 的动态 SQL 语句、 Mybatis 多表查询之一对多 、 Mybatis 多表查询之多对多】
- Java SSM框架之MyBatis3（四）MyBatis之一对一、一对多、多对多
- java web(六):mybatis之一对一、一对多、多对多映射
- Java_myBatis_一对多映射
MyBatis查询：一对多映射关系（mybatis一对多映射原理）
@Mapper
public interface ParameterMapper {
Dept GetDeptAndEmp(@Param("id") Integer did);
}
<?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.example.mybatis_review.mapper.ParameterMapper">
<resultMap id="GetDeptAndEmpMap" type="com.example.mybatis_review.bean.Dept">
<id property="did" column="did"></id>
<result property="deptName" column="dept_name"></result>
<collection property="emps" ofType="com.example.mybatis_review.bean.Emp">
<id property="eid" column="eid"></id>
<result property="did" column="did"></result>
<result property="empName" column="emp_name"></result>
<result property="email" column="email"></result>
<result property="did" column="did"></result>
<result property="age" column="age"></result>
<result property="sex" column="sex"></result>
</collection>
</resultMap>
<!-- Emp GetDeptAndEmp(@Param("id") Integer did);-->
<select id="GetDeptAndEmp" resultMap="GetDeptAndEmpMap">
select * from t_dept left join t_emp on t_dept.did = t_emp.did where t_dept.did=#{id}
</select>
</mapper>
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
java web(六):mybatis之一对一、一对多、多对多映射
前言：
百度百科：
MyBatis 是一款优秀的持久层框架，它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可 以使用简单的 XML 或注解来配置和映射原生信息，将接口和 Java 的 POJOs(Plain Ordinary Java Object,普通的 Java对象)映射成数据库中的记录。
这篇文章讲解如何 使用mysql数据库，总结mybatis的一对一、一对多、多对多映射如何进行增删改查。
准备工作：
1.打开ecplise创建一个普通的java项目。项目结构如下图所示：
2.先看jar包，这次我们用到4个jar包，单元测试jar包junit-4.7.jar,日志包log4j-1.2.17.jar,mybatis所用的jar包以及连接mysql数据库用到的mysql-connector-java-5.1.47.jar
【ojdbc14.jar是连接oracle数据库用到的jar包】。
注：jar包记得build path
3.dtd约束文件：是对全局配置文件和sql映射文件的约束。在ecplise中点击 window --> preference ,在输入栏键入xml，然后选择 XML Catalog,点击Add。
注：-//mybatis.org//DTD Mapper 3.0//EN 、-//mybatis.org//DTD Config 3.0//EN ，key值是规定的，location是点击workspace在项目中选择的约束文件。
4.driver.properties配置文件是连接数据库所用到的配置信息。在mybaits-config.xml中会用到。
#基于mysql数据库
driver=com.mysql.jdbc.Driver
#?useUnicode=true&characterEncoding=utf8为了支持中文数据的写入
url=jdbc:mysql://188.131.246.182:3306/cnblogs?useUnicode=true&characterEncoding=utf-8
username=study
password=123456
#基于Oracle数据库
#driver=oracle.jdbc.driver.OracleDriver
#url=jdbc:oracle:thin:@127.0.0.1:1521:XE
#username=briup
#password=briup
5.log4j.properties是日志jar包要用到的配置文件。其中定义了输出日志级别、输出位置以及是否打印sql语句。
log4j.rootLogger=DEBUG, stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d [%-5p] %c - %m%n
#show sql
log4j.logger.java.sql.ResultSet=INFO
log4j.logger.org.apache=INFO
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
6.全局配置文件：mybatis-config.xml。该文件的详细信息可网上找资料。我这里给一个简单能用的。
<?xml version="1.0" encoding="UTF-8"?>
<!-- dtd约束 -->
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd" >
<configuration>
<!-- properties必须是第一个子标签
定义变量,定义完成后，可以在后面的标签中使用
resource引入配置文件，优先级更高
-->
<properties resource="driver.properties">
<property name="driver" value=""/>
<property name="url" value=""/>
<property name="username" value=""/>
<property name="password" value=""/>
</properties>
<typeAliases>
<!-- 给包下的类定义别名 -->
<package name="com.cnblogs.bean"/>
</typeAliases>
<!-- 使用mysql数据库,如果要切换到Oracle数据库,default="oracle" -->
<environments default="mysql">
<!-- mysql -->
<environment id="mysql">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="${driver}" />
<property name="url" value="${url}" />
<property name="username" value="${username}" />
<property name="password" value="${password}" />
</dataSource>
</environment>
<!-- oracle -->
<environment id="oracle">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="${driver}" />
<property name="url" value="${url}" />
<property name="username" value="${username}" />
<property name="password" value="${password}" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/cnblogs/OneToMany/OneToMany.xml" />
</mappers>
</configuration>
准备工作完成以后就可以开始写sql语句了。
注：嵌套结果查询：一次查一张表，分多次查。
嵌套结果映射：一次查多张表，分别映射。
一对一映射
1.创建bean类：Student【学生】、FoodCard【饭卡】
Student.java
package com.cnblogs.bean;
/**
* 和饭卡一一对应
* CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`gender` varchar(255) NOT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
)
*
*/
public class Student {
private Integer id;
private String name;
private String gender;
private int age;
public Student() {
super();
// TODO Auto-generated constructor stub
}
public Student(String name, String gender, int age) {
super();
this.name = name;
this.gender = gender;
this.age = age;
}
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 getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", gender=" + gender +
", age=" + age + "]";
}
}FoodCard.java
package com.cnblogs.bean;
/**
* 饭卡，和学生一对一映射
* CREATE TABLE `food_card` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`number` varchar(255) NOT NULL,
`balance` double DEFAULT NULL,
`stu_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `stu_id` (`stu_id`),
CONSTRAINT `stu_id` FOREIGN KEY (`stu_id`) REFERENCES `student` (`id`)
)
*
*/
public class FoodCard {
private Integer id;
// 卡号
private String number;
//余额
private Double balance;
// 对应的学生
private Student stu;
public FoodCard() {
super();
// TODO Auto-generated constructor stub
}
public FoodCard(String number, Double balance) {
super();
this.number = number;
this.balance = balance;
}
public FoodCard(String number, Double balance, Student stu) {
super();
this.number = number;
this.balance = balance;
this.stu = stu;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
public Double getBalance() {
return balance;
}
public void setBalance(Double balance) {
this.balance = balance;
}
public Student getStu() {
return stu;
}
public void setStu(Student stu) {
this.stu = stu;
}
@Override
public String toString() {
return "FoodCard [id=" + id + ", number=" + number + ", balance=" +
balance + ", stu=" + stu + "]";
}
}
3.编写sql映射文件。首先告诉mybatis那个xml文件是你的映射文件。在mybatis的<Mappers>标签中添加一条
" <mapper resource="com/cnblogs/oneToOne/OneToOne.xml"/> " 按住Ctrl+鼠标左键点击跳转到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">
<!-- com.cnblogs.oneToOne.OneToOne是我们定义接口的全限定名字 这样就可以使用接口调用映射的SQL语句了 这个
名字一定要和接口对应上-->
<mapper namespace="com.cnblogs.oneToOne.OneToOne">
<!-- 插入一条学生信息 -->
<insert id="insertStudent" parameterType="Student" useGeneratedKeys="true"
keyProperty="id">
insert into student(name,gender,age)
values(#{name},#{gender},#{age})
</insert>
<!-- 插入一条饭卡信息 自动生成主键
#{number}代表调用getNumber()方法
-->
<insert id="insertFoodCard" parameterType="FoodCard" useGeneratedKeys="true"
keyProperty="id">
insert into food_card(number,balance,stu_id)
values(#{number},#{balance},#{stu.id})
</insert>
<!-- 删除一条饭卡信息-->
<delete id="deleteFoodCardById" parameterType="int">
delete from food_card where id=#{id}
</delete>
<!-- 根据学生id删除一条饭卡信息 -->
<delete id="deleteFoodCardByStuId" parameterType="int">
delete from food_card where stu_id=#{id}
</delete>
<!-- 删除一条学生信息 由于主键被饭卡表引用,需要级联删除,先调用deleteFoodCardByStuId -->
<delete id="deleteStudentById" parameterType="int">
delete from student where id=#{id}
</delete>
<!-- 模拟一个业务需求：假设一批学生毕业了，需要删除关于这批学生的所有饭卡信息 -->
<delete id="deleteFoodCardByStuIds" parameterType="java.util.List">
delete from food_card where stu_id in(
<foreach collection="list" item="stu" separator="," >
#{stu.id}
</foreach>
)
</delete>
<!-- 如果方法要传入两个参数,#{param1}代表第一个参数,#{param2}代表第二个参数 -->
<!-- 根据名字和年龄更改一条学生记录 -->
<update id="updateStudentByNameAndAge">
update student set age=21
where name=#{param1} and age=#{param2}
</update>
<!-- 一对一映射
查询两张表food_card和student,如何将student查到的记录映射到
FoodCard对象中？
-->
<resultMap type="Student" id="StudentMap">
<id column="id" property="id" />
<result column="name" property="name"/>
<result column="gender" property="gender"/>
<result column="age" property="age"/>
</resultMap>
<!-- 嵌套结果映射 -->
<resultMap type="FoodCard" id="FoodCardMap">
<id column="id" property="id" />
<result column="number" property="number"/>
<result column="balance" property="balance"/>
<!-- 一对一映射,将很多列映射到stu属性上 -->
<association property="stu" resultMap="StudentMap" />
</resultMap>
<select id="selectFoodCardWithStudent" parameterType="int" resultMap="FoodCardMap">
select * from student stu,food_card fc
where fc.id=#{id} and fc.stu_id=stu.id
</select>
<!-- 嵌套结果查询 -->
<select id="selectStudentById" resultType="Student">
select * from student where id=#{id}
</select>
<resultMap type="FoodCard" id="FoodCardMap2">
<id column="id" property="id" />
<result column="number" property="number"/>
<result column="balance" property="balance"/>
<!-- 一对一 嵌套结果查询-->
<association property="stu" select="selectStudentById" column="stu_id"/>
</resultMap>
<select id="selectFoodCardWithStudent2" parameterType="int" resultMap="FoodCardMap2">
select * from food_card fc where fc.id=#{id}
</select>
</mapper>*嵌套结果映射：
<!-- 嵌套结果映射 -->
<resultMap type="FoodCard" id="FoodCardMap">
<id column="id" property="id" />
<result column="number" property="number"/>
<result column="balance" property="balance"/>
<!-- 一对一映射,将很多列映射到stu属性上 -->
<association property="stu" resultMap="StudentMap" />
</resultMap>
<select id="selectFoodCardWithStudent" parameterType="int" resultMap="FoodCardMap">
select * from student stu,food_card fc where fc.id=#{id} and fc.stu_id=stu.id
</select>
resultMap标签，type属性表示和那种类型对应，id值起唯一标识作用，主键用id标签，表的其它列用result标签，column代表和数据库表对应的列，property
表示type值对应的属性【property指getXXX和setXXX】，column和property组合起来表示把查到的记录对应的列映射到对应的属性上。
嵌套结果查询的SQL语句，返回值不能再用resultType指定了，用resultMap指定。
注：嵌套结果映射中,如果两个表有同名的列，自然链接的时候只会保留一个列，上述中如果直接 ''select * '' 会导致饭卡id和学生id一致。
必须显式的指定返回的列, '' select fc.id,fc.number,fc.balance,fc.stu_id,stu.id,stu.name,stu.age,stu.gender
from student stu,food_card fc where fc.id=#{id} and fc.stu_id=stu.id''。嵌套结果查询不需要显式指定。
*嵌套结果查询
<!-- 嵌套结果查询 -->
<select id="selectStudentById" resultType="Student"> select * from student where id=#{id} </select> <resultMap type="FoodCard" id="FoodCardMap2"> <id column="id" property="id" /> <result column="number" property="number"/> <result column="balance" property="balance"/> <!-- 一对一 嵌套结果查询--> <association property="stu" select="selectStudentById" column="stu_id"/>
</resultMap> <select id="selectFoodCardWithStudent2" parameterType="int" resultMap="FoodCardMap2"> select * from food_card fc where fc.id=#{id} </select>
一次查一张表，多次查。<association property="stu" select="selectStudentById" column="stu_id"/>
property代表映射到那个属性,select代表调用那个方法，column指定当前查表返回的那个列作为方法的参数。如果
还需要再查一张表，按照规则再加一条<association>
所有的sql语句对应的接口中必须有该方法。
package com.cnblogs.oneToOne;
import java.util.List;
import com.cnblogs.bean.FoodCard;
import com.cnblogs.bean.Student;
public interface OneToOne {
/**
* 插入一条饭卡信息
*/
public abstract void insertFoodCard(FoodCard foodCard);
/**
* 插入一条学生信息
*/
public abstract void insertStudent(Student stu);
/**
* 删除一条饭卡记录
*/
public abstract void deleteFoodCardById(Integer id);
/**
* 根据学生id删除一条饭卡记录
*/
public abstract void deleteFoodCardByStuId(Integer id);
/**
* 删除一条学生记录根据id
* 调用该方(法应该先调用deleteFoodCardByStuId(Integer)
*/
public abstract void deleteStudentById(Integer id);
/**
* 提供一批学生信息，删除对应的饭卡
*/
public abstract void deleteFoodCardByStuIds(List<Student> stus);
/**
* 根据年龄和姓名更改一条学生记录
*/
public abstract void updateStudentByNameAndAge(String name,Integer age);
/**
* 根据id查找饭卡的详细信息[包括学生属性]
* 使用嵌套结果映射
*/
public abstract FoodCard selectFoodCardWithStudent(Integer id);
/**
* 根据id查找学生记录
*/
public abstract Student selectStudentById(Integer id);
/**
* 根据id查找饭卡的详细信息[包括学生属性]
* 使用嵌套结果查询
*/
public abstract FoodCard selectFoodCardWithStudent2(Integer id);
}单元测试类：
package com.cnblogs.jtest;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import com.cnblogs.bean.FoodCard;
import com.cnblogs.bean.Student;
import com.cnblogs.oneToOne.OneToOne;
import com.cnblogs.utils.MySqlSessionFactory;
public class OneToOneTest {
@Test
public void insertStudent() {
Student student1 = new Student("jack","男",20);
Student student2 = new Student("Tina", "女", 16);
InputStream inputStream;
try{
// 读取配置文件
inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder().build(inputStream);
// 手动提交事务
SqlSession sqlSession = sqlSessionFactory.openSession(false);
OneToOne mapper = sqlSession.getMapper(OneToOne.class);
mapper.insertStudent(student1);
mapper.insertStudent(student2);
// 提交事务
sqlSession.commit();
} catch(Exception e) {
e.printStackTrace();
}
}
@Test
public void insertFoodCard() {
Student stu1 = new Student();
stu1.setId(15);
Student stu2 = new Student();
stu2.setId(16);
FoodCard foodCard1 = new FoodCard("123456",100.0,stu1);
FoodCard foodCard2 = new FoodCard("123457",312.4,stu2);
InputStream inputStream;
try{
// 读取配置文件
inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
OneToOne mapper = sqlSession.getMapper(OneToOne.class);
mapper.insertFoodCard(foodCard1);
mapper.insertFoodCard(foodCard2);
// 提交事务
sqlSession.commit();
} catch(Exception e) {
e.printStackTrace();
}
}
@Test
public void deleteFoodCardById() {
InputStream inputStream;
try{
// 读取配置文件
inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
OneToOne mapper = sqlSession.getMapper(OneToOne.class);
mapper.deleteFoodCardById(5);
// 提交事务
sqlSession.commit();
} catch(Exception e) {
e.printStackTrace();
}
}
@Test
public void deleteStudentById() {
InputStream inputStream;
try{
// 读取配置文件
inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
OneToOne mapper = sqlSession.getMapper(OneToOne.class);
mapper.deleteFoodCardByStuId(6);
mapper.deleteStudentById(6);
// 提交事务
sqlSession.commit();
} catch(Exception e) {
e.printStackTrace();
}
}
@Test
public void deleteFoodCardByStuIds() {
InputStream inputStream;
try{
// 读取配置文件
inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
OneToOne mapper = sqlSession.getMapper(OneToOne.class);
Student stu1 = new Student();
stu1.setId(5);
Student stu2 = new Student();
stu2.setId(7);
Student stu3 = new Student();
stu3.setId(10);
ArrayList<Student> stus = new ArrayList<Student>();
stus.add(stu1);
stus.add(stu2);
stus.add(stu3);
mapper.deleteFoodCardByStuIds(stus);
// 提交事务
sqlSession.commit();
} catch(Exception e) {
e.printStackTrace();
}
}
@SuppressWarnings("unused")
@Test
public void updateStudentByNameAndAge() {
SqlSession sqlSession = null;
try {
// 手动提交事务
sqlSession = MySqlSessionFactory.opensession();
OneToOne mapper = sqlSession.getMapper(OneToOne.class);
mapper.updateStudentByNameAndAge("jack", 20);
sqlSession.commit();
} catch (IOException e) {
if(sqlSession != null)
sqlSession.rollback();
e.printStackTrace();
}
}
@Test
public void selectFoodCardWithStudent() {
SqlSession sqlSession = null;
try {
sqlSession = MySqlSessionFactory.opensession();
OneToOne mapper = sqlSession.getMapper(OneToOne.class);
FoodCard foodCard = mapper.selectFoodCardWithStudent(12);
System.out.println(foodCard);
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void selectFoodCardWithStudent2() {
SqlSession sqlSession = null;
try {
sqlSession = MySqlSessionFactory.opensession();
OneToOne mapper = sqlSession.getMapper(OneToOne.class);
FoodCard foodCard = mapper.selectFoodCardWithStudent2(12);
System.out.println(foodCard);
} catch (IOException e) {
e.printStackTrace();
}
}
}获得SqlSesion对象的步骤重多，我们可以进行封装成一个工厂类。
package com.cnblogs.utils;
import java.io.IOException;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class MySqlSessionFactory {
public static SqlSessionFactory getSqlSessionFactory() throws IOException{
// 读取配置文件
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
if(inputStream == null)
throw new IOException("配置文件路径不对或者配置文件内容出错");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
return sqlSessionFactory;
}
/**
*
* @param flag true 自动提交事务,false 手动提交事务
* @return
*/
public static SqlSession opensession(boolean flag) throws IOException{
return getSqlSessionFactory().openSession(flag);
}
/**
* 默认手动提交事务
* @return
*/
public static SqlSession opensession() throws IOException{
return opensession(false);
}
}
一对多映射
简单模拟一下映射关系,一个人只能有一个户籍所在地，而一个户籍所在地可以对应许多人。把外键放在一的那边。
1)pojo类：
Human.java
package com.cnblogs.bean;
import java.util.Date;
/**
* create table human(
* id int,
* name varchar,
* gender varchar,
* age int,
* dob datetime,
* address_id int reference address(id)
* )
*
*/
public class Human {
private Integer id;
private String name;
private String gender;
private Integer age;
private Date dob;
// 人对应的地址
private Address address;
public Human() {
super();
}
public Human(String name, String gender, Integer age, Date dob) {
super();
this.name = name;
this.gender = gender;
this.age = age;
this.dob = dob;
}
public Human(String name, String gender, Integer age, Date dob, Address address) {
super();
this.name = name;
this.gender = gender;
this.age = age;
this.dob = dob;
this.address = address;
}
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 getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Date getDob() {
return dob;
}
public void setDob(Date dob) {
this.dob = dob;
}
public Address getAddress() {
return address;
}
public void setAddress(Address address) {
this.address = address;
}
@Override
public String toString() {
return "Human [id=" + id + ", name=" + name + ", gender=" + gender + ", age=" + age +
", dob=" + dob + ", address=" + address + "]";
}
}Address.java
package com.cnblogs.bean;
import java.util.List;
/**
* table
* address(
* id int key,
* province varchar,
* city varchar,
* street varchar
* )
*
*/
public class Address {
private Integer id;
private String province;
private String city;
private String street;
// 地址对应的人
private List<Human> humans;
public Address() {
super();
}
public Address(String province, String city, String street) {
super();
this.province = province;
this.city = city;
this.street = street;
}
public Address(String province, String city, String street, List<Human> humans) {
super();
this.province = province;
this.city = city;
this.street = street;
this.humans = humans;
}
public Address(Integer id, String province, String city, String street) {
super();
this.id = id;
this.province = province;
this.city = city;
this.street = street;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getProvince() {
return province;
}
public void setProvince(String province) {
this.province = province;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public String getStreet() {
return street;
}
public void setStreet(String street) {
this.street = street;
}
public List<Human> getHumans() {
return humans;
}
public void setHumans(List<Human> humans) {
this.humans = humans;
}
@Override
public String toString() {
return "Address [id=" + id + ", province=" + province + ", city=" + city +
", street=" + street + "]";
}
}
2)在mybatis<Mappers>下加一条：<mapper resource="com/cnblogs/oneToMany/OneToMany.xml" />
3)编写OneToMany.xml和OneTomany.java接口
<?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">
<!-- com.cnblogs.OneToMany.OneToMany是我们定义接口的全限定名字 这样就可以使用接口调用映射的SQL语句了 这个
名字一定要和接口对应上-->
<mapper namespace="com.cnblogs.oneToMany.OneToMany">
<!-- 插入一条地址信息 -->
<insert id="insertAddress" parameterType="Address"
useGeneratedKeys="true" keyProperty="id">
insert into address(province,city,street)
values(#{province},#{city},#{street})
</insert>
<!-- 插入一条人类信息 -->
<insert id="insertHuman" parameterType="Human"
useGeneratedKeys="true" keyProperty="id">
insert into human(name,gender,age,dob,address_id)
values(#{name},#{gender},#{age},#{dob},#{address.id})
</insert>
<!-- 删改类似，省略.... -->
<!-- 结果集映射
基础结果集,说明了如何从数据库加载对象
-->
<resultMap type="Address" id="addressBaseMap">
<id property="id" column="id" />
<result property="province" column="province"/>
<result property="city" column="city"/>
<result property="street" column="street"/>
</resultMap>
<resultMap type="Human" id="humanBaseMap">
<id property="id" column="id" />
<result property="name" column="name"/>
<result property="gender" column="gender"/>
<result property="age" column="age"/>
<result property="dob" column="dob"/>
</resultMap>
<!-- 结果集映射
一个人对应一个户籍,所以这里还是一对一映射
继承基础结果集
-->
<!-- 嵌套结果映射
继承：namespace + id 完全限定名
-->
<resultMap type="Human" id="humanMap"
extends="com.cnblogs.oneToMany.OneToMany.humanBaseMap">
<!-- 避免human的id和address的id起冲突,sql语句会给列起别名，相应的这里也需要更改 -->
<id property="id" column="hid" />
<association property="address" resultMap="addressBaseMap"></association>
</resultMap>
<select id="selectHumanWithAddressById" parameterType="int" resultMap="humanMap">
select h.id hid,h.name,h.gender,h.age,h.dob,a.*
from human h,address a
where h.id=#{id} and h.address_id = a.id
</select>
<!-- 一对多
一个户籍地对应多个人
-->
<!-- 嵌套结果映射 -->
<resultMap type="Address" id="AddressMap"
extends="com.cnblogs.oneToMany.OneToMany.addressBaseMap">
<!-- 避免human的id和address的id起冲突,sql语句会给列起别名，相应的这里也需要更改 -->
<id property="id" column="aid"/>
<!-- 一对多使用collection集合 -->
<collection property="humans" resultMap="humanBaseMap" />
</resultMap>
<!-- 嵌套结果映射sql语句 -->
<select id="selectAddressWithHumanById" parameterType="int" resultMap="AddressMap">
select a.id aid,a.province,a.city,a.street,h.*
from address a,human h
where a.id=#{id} and a.id=h.address_id
</select>
<!-- 嵌套结果查询 -->
<!-- 嵌套结果查询中被用到的另一条sql语句 -->
<select id="selectHumanByAddressId" parameterType="int" resultMap="humanBaseMap">
select * from human
where address_id=#{id}
</select>
<!-- 结果集 -->
<resultMap type="Address" id="AddressMap2"
extends="com.cnblogs.oneToMany.OneToMany.addressBaseMap">
<!-- 把id传过去 -->
<collection property="humans" select="selectHumanByAddressId" column="id" />
</resultMap>
<!-- 嵌套结果查询sql语句 -->
<select id="selectAddressWithHumanById2" parameterType="int" resultMap="AddressMap2">
select * from address
where id=#{id}
</select>
</mapper>OneTOMany.java
package com.cnblogs.oneToMany;
import java.util.List;
import com.cnblogs.bean.Address;
import com.cnblogs.bean.Human;
public interface OneToMany {
/**
* 插入一条地址信息
* @param address
*/
public abstract void insertAddress(Address address);
/**
* 插入一条人类信息
* @param humam
*/
public abstract void insertHuman(Human humam);
/**
* 根据id查询一条人类信息[包括户籍的详细信息]
* @param id
*/
public abstract Human selectHumanWithAddressById(Integer id);
/**
* 嵌套结果映射
* 根据id查询一条户籍信息[包括户籍所在地人的信息]
* @param id
* @return
*/
public abstract Address selectAddressWithHumanById(Integer id);
/**
* 根据户籍地id查找人
* @param id
* @return
*/
public abstract List<Human> selectHumanByAddressId(Integer id);
/**
* 嵌套结果查询
* 根据id查询一条户籍信息[包括户籍所在地人的信息]
* @param id
* @return
*/
public abstract Address selectAddressWithHumanById2(Integer id);
}
4)测试类
package com.cnblogs.jtest;
import java.io.IOException;
import java.util.Date;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.cnblogs.bean.Address;
import com.cnblogs.bean.Human;
import com.cnblogs.oneToMany.OneToMany;
import com.cnblogs.utils.MySqlSessionFactory;
public class OnetoManyTest {
@Test
public void insertHumanAndAddress() {
try{
SqlSession sqlSession = MySqlSessionFactory.opensession();
OneToMany mapper = sqlSession.getMapper(OneToMany.class);
// 准备数据
Address address1 = new Address("湖南", "张家界", "步行街");
Address address2 = new Address("湖南", "长沙", "步行街");
mapper.insertAddress(address1);
mapper.insertAddress(address2);
//提交事务
sqlSession.commit();
} catch(Exception e) {
e.printStackTrace();
}
}
@SuppressWarnings("unused")
@Test
public void insertHuman() {
SqlSession session = null;
try {
session = MySqlSessionFactory.opensession();
OneToMany mapper = session.getMapper(OneToMany.class);
// 准备数据
Address address1 = new Address();
address1.setId(3);
Address address2 = new Address();
address2.setId(4);
Human human1 = new Human("jack","男",22,new Date(),address1);
Human human2 = new Human("tom","男",20,new Date(),address2);
Human human3 = new Human("tina","女",22,new Date(),address1);
Human human4 = new Human("alias","女",18,new Date(),address1);
// 插入数据
mapper.insertHuman(human1);
mapper.insertHuman(human2);
mapper.insertHuman(human3);
mapper.insertHuman(human4);
// 提交事务
session.commit();
} catch (IOException e) {
if(session != null)
session.close();
e.printStackTrace();
}
}
@SuppressWarnings("unused")
@Test
public void selectHumanWithAddressById() {
SqlSession session = null;
try {
session = MySqlSessionFactory.opensession();
OneToMany mapper = session.getMapper(OneToMany.class);
Human human1 = mapper.selectHumanWithAddressById(1);
Human human2 = mapper.selectHumanWithAddressById(3);
System.out.println("human: " + human1);
System.out.println("human: " + human2);
} catch (IOException e) {
if(session != null)
session.close();
e.printStackTrace();
}
}
@SuppressWarnings("unused")
@Test
public void selectAddressWithHumanById() {
SqlSession session = null;
try {
session = MySqlSessionFactory.opensession();
OneToMany mapper = session.getMapper(OneToMany.class);
Address address1 = mapper.selectAddressWithHumanById(3);
Address address2 = mapper.selectAddressWithHumanById(4);
System.out.println("address: " + address1);
List<Human> humans1 = address1.getHumans();
System.out.println("humans : ");
for(Human h : humans1)
System.out.println(h);
System.out.println("============================");
System.out.println("address: " + address2);
List<Human> humans2 = address2.getHumans();
System.out.println("humans : ");
for(Human h : humans2)
System.out.println(h);
} catch (IOException e) {
if(session != null)
session.close();
e.printStackTrace();
}
}
@SuppressWarnings("unused")
@Test
public void selectAddressWithHumanById2() {
SqlSession session = null;
try {
session = MySqlSessionFactory.opensession();
OneToMany mapper = session.getMapper(OneToMany.class);
Address address1 = mapper.selectAddressWithHumanById2(3);
Address address2 = mapper.selectAddressWithHumanById2(4);
System.out.println("address: " + address1);
List<Human> humans1 = address1.getHumans();
System.out.println("humans : ");
for(Human h : humans1)
System.out.println(h);
System.out.println("============================");
System.out.println("address: " + address2);
List<Human> humans2 = address2.getHumans();
System.out.println("humans : ");
for(Human h : humans2)
System.out.println(h);
} catch (IOException e) {
if(session != null)
session.close();
e.printStackTrace();
}
}
}
多对多
描述：一个老师可以讲多门课，一门课可以有多个老师教。
1）pojo类
Teacher.java
package com.cnblogs.bean;
import java.util.List;
/**
* 教师类
* 和课程对多多
*/
public class Teacher {
// 教师id
private Long id;
// 教师名称
private String name;
// 教师职称 讲师 高级讲师 教授
private String title;
// 教师讲授的课程
private List<Course> courses;
public Teacher() {
super();
}
public Teacher(String name, String title) {
super();
this.name = name;
this.title = title;
}
public Teacher(String name, String title, List<Course> courses) {
super();
this.name = name;
this.title = title;
this.courses = courses;
}
public Teacher(Long id, String name, String title, List<Course> courses) {
super();
this.id = id;
this.name = name;
this.title = title;
this.courses = courses;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public List<Course> getCourses() {
return courses;
}
public void setCourses(List<Course> courses) {
this.courses = courses;
}
@Override
public String toString() {
return "Teacher [id=" + id + ", name=" + name + ", title=" + title + ", courses=" + courses + "]";
}
}
Course.java
package com.cnblogs.bean;
import java.util.List;
/**
* 课程类
* 和教师多对多
*
*/
public class Course {
// 课程id
private Long id;
// 课程名称
private String name;
// 课程描述
private String description;
// 课程对应的老师
private List<Teacher> teachers;
public Course() {
super();
}
public Course(String name, String description) {
super();
this.name = name;
this.description = description;
}
public Course(String name, String description, List<Teacher> teachers) {
super();
this.name = name;
this.description = description;
this.teachers = teachers;
}
public Course(Long id, String name, String description, List<Teacher> teachers) {
super();
this.id = id;
this.name = name;
this.description = description;
this.teachers = teachers;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
public List<Teacher> getTeachers() {
return teachers;
}
public void setTeachers(List<Teacher> teachers) {
this.teachers = teachers;
}
@Override
public String toString() {
return "Course [id=" + id + ", name=" + name + ", description=" + description + "]";
}
}
桥表类:TeacherCourse.java
package com.cnblogs.bean;
import java.util.Date;
/**
* 桥表类
* 连接教师类和课程类
*
*/
public class TeacherCourse {
// id
private Long id;
// 授课开始时间
private Date begin;
// 授课结束时间
private Date end;
// 教师
private Teacher teacher;
// 课程
private Course course;
public TeacherCourse() {
super();
// TODO Auto-generated constructor stub
}
public TeacherCourse(Date begin, Date end) {
super();
this.begin = begin;
this.end = end;
}
public TeacherCourse(Date begin, Date end, Teacher teacher, Course course) {
super();
this.begin = begin;
this.end = end;
this.teacher = teacher;
this.course = course;
}
public TeacherCourse(Long id, Date begin, Date end, Teacher teacher, Course course) {
super();
this.id = id;
this.begin = begin;
this.end = end;
this.teacher = teacher;
this.course = course;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Date getBegin() {
return begin;
}
public void setBegin(Date begin) {
this.begin = begin;
}
public Date getEnd() {
return end;
}
public void setEnd(Date end) {
this.end = end;
}
public Teacher getTeacher() {
return teacher;
}
public void setTeacher(Teacher teacher) {
this.teacher = teacher;
}
public Course getCourse() {
return course;
}
public void setCourse(Course course) {
this.course = course;
}
@Override
public String toString() {
return "TeacherCourse [id=" + id + ", begin=" + begin + ", end=" + end + ", teacher=" + teacher + ", course="
+ course + "]";
}
}
2)在mybatis-config.xml的<Mappers>下加一条：<mapper resource="com/cnblogs/oneToMany/OneToMany.xml" />
3)sql映射文件
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.cnblogs.manyTOMany.ManyToMany">
<!-- 结果集映射关系 -->
<resultMap type="Teacher" id="TeacherBaseMap">
<id property="id" column="t_id"/>
<result property="name" column="t_name"/>
<result property="title" column="t_title"/>
</resultMap>
<resultMap type="Course" id="CourseBaseMap">
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<result property="description" column="c_des"/>
</resultMap>
<resultMap type="TeacherCourse" id="TeacherCourseBaseMap">
<id property="id" column="id"/>
<result property="begin" column="begin"/>
<result property="end" column="end"/>
</resultMap>
<!-- 插入 -->
<insert id="insertTeacher" parameterType="Teacher"
useGeneratedKeys="true" keyProperty="id">
insert into teacher(t_name,t_title)
values(#{name},#{title})
</insert>
<insert id="insertCourse" parameterType="Course"
useGeneratedKeys="true" keyProperty="id">
insert into course(c_name,c_des)
values(#{name},#{description})
</insert>
<insert id="insertTeacherCourse" parameterType="TeacherCourse"
useGeneratedKeys="true" keyProperty="id">
insert into teacher_course(begin,end,t_id,c_id)
values(#{begin},#{end},#{teacher.id},#{course.id})
</insert>
<!-- 嵌套结果映射
其实就是一对多查询，因为桥表的存在
-->
<resultMap type="Teacher" id="TeacherMap1"
extends="com.cnblogs.manyTOMany.ManyToMany.TeacherBaseMap">
<collection property="courses" resultMap="CourseBaseMap" column="c_id" />
</resultMap>
<!-- sql语句 -->
<select id="selectTeacherWithCourseById" parameterType="long" resultMap="TeacherMap1">
select * from teacher t,course c,teacher_course tc
where t.t_id=#{id} and t.t_id=tc.t_id and tc.c_id=c.c_id
</select>
<!-- 嵌套结果查询 -->
<select id="selectCourseById" parameterType="long" resultMap="CourseBaseMap">
select * from course
where c_id=#{id}
</select>
<resultMap type="Teacher" id="TeacherMap2"
extends="com.cnblogs.manyTOMany.ManyToMany.TeacherBaseMap">
<collection property="courses" select="selectCourseById" column="c_id" />
</resultMap>
<select id="selectTeacherWithCourseById2" parameterType="long"
resultMap="TeacherMap2">
select * from teacher t,teacher_course tc
where t.t_id=#{id} and t.t_id=tc.t_id
</select>
</mapper>
接口：
package com.cnblogs.manyTOMany;
import com.cnblogs.bean.Course;
import com.cnblogs.bean.Teacher;
import com.cnblogs.bean.TeacherCourse;
public interface ManyToMany {
/**
* 插入一条教师数据
* @param teacher
*/
public abstract void insertTeacher(Teacher teacher);
/**
* 插入一条课程数据
* @param course
*/
public abstract void insertCourse(Course course);
/**
* 插入一条桥表数据
* @param tc
*/
public abstract void insertTeacherCourse(TeacherCourse tc);
/**
* 嵌套结果映射
* 教师的详细信息
* @param id
* @return
*/
public abstract Teacher selectTeacherWithCourseById(Long id);
/**
* 嵌套结果查询
* @param id
* @return
*/
public abstract Teacher selectTeacherWithCourseById2(Long id);
}
4）测试类
package com.cnblogs.jtest;
import java.io.IOException;
import java.util.Date;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.cnblogs.bean.Course;
import com.cnblogs.bean.Teacher;
import com.cnblogs.bean.TeacherCourse;
import com.cnblogs.manyTOMany.ManyToMany;
import com.cnblogs.utils.MySqlSessionFactory;
public class ManyToManyTest {
@Test
public void insertTeacher() {
SqlSession session = null;
try {
session = MySqlSessionFactory.opensession();
ManyToMany mapper = session.getMapper(ManyToMany.class);
// 准备数据
Teacher teacher1 = new Teacher("li","讲师");
Teacher teacher2 = new Teacher("zj","高级讲师");
Teacher teacher3 = new Teacher("kb","教授");
mapper.insertTeacher(teacher1);
mapper.insertTeacher(teacher2);
mapper.insertTeacher(teacher3);
// 提交事务
session.commit();
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void insertCourse() {
SqlSession session = null;
try {
session = MySqlSessionFactory.opensession();
ManyToMany mapper = session.getMapper(ManyToMany.class);
// 准备数据
Course course1 = new Course("core java","核心java基础");
Course course2 = new Course("c语言","ccc");
Course course3 = new Course("c++","c++++");
mapper.insertCourse(course1);
mapper.insertCourse(course2);
mapper.insertCourse(course3);
// 提交事务
session.commit();
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void insertTeacherCourse() {
SqlSession session = null;
try {
session = MySqlSessionFactory.opensession();
ManyToMany mapper = session.getMapper(ManyToMany.class);
// 准备数据
Course course1 = new Course();
course1.setId(1L);
Course course2 = new Course();
course2.setId(2L);
Course course3 = new Course();
course3.setId(3L);
Teacher teacher1 = new Teacher();
teacher1.setId(4L);
Teacher teacher2 = new Teacher();
teacher2.setId(5L);
Teacher teacher3 = new Teacher();
teacher3.setId(6L);
TeacherCourse tc1 = new TeacherCourse(new Date(),new Date(),teacher1,course1);
TeacherCourse tc2 = new TeacherCourse(new Date(),new Date(),teacher2,course2);
TeacherCourse tc3 = new TeacherCourse(new Date(),new Date(),teacher3,course3);
mapper.insertTeacherCourse(tc1);
mapper.insertTeacherCourse(tc2);
mapper.insertTeacherCourse(tc3);
// 提交事务
session.commit();
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void selectTeacherWithCourseById() {
SqlSession session = null;
try {
session = MySqlSessionFactory.opensession();
ManyToMany mapper = session.getMapper(ManyToMany.class);
// 准备数据
Teacher t1 = mapper.selectTeacherWithCourseById(4L);
Teacher t2 = mapper.selectTeacherWithCourseById(6L);
System.out.println("Teacher: " + t1);
System.out.println("Teacher: " + t2);
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void selectTeacherWithCourseById2() {
SqlSession session = null;
try {
session = MySqlSessionFactory.opensession();
ManyToMany mapper = session.getMapper(ManyToMany.class);
// 准备数据
Teacher t1 = mapper.selectTeacherWithCourseById2(4L);
Teacher t2 = mapper.selectTeacherWithCourseById2(6L);
System.out.println("Teacher: " + t1);
System.out.println("Teacher: " + t2);
} catch (IOException e) {
e.printStackTrace();
}
}
}
总结：
使用mybatis操作数据库，把配置文件配置好以后，就非常简单了，就像在sql命令行直接输入sql语句一样。
要分清嵌套结果映射和嵌套结果查询的区别。
* resultMap – 是最复杂也是最强大的元素，用来描述如何从数据库结果集中来加载对象。
* 一对一：重点掌握<association>标签在嵌套结果映射和嵌套结果查询中的使用
* 一对多：重点掌握<collection>标签在嵌套结果映射和嵌套结果查询中的使用
* 多对多：其实和一对多一样使用，不过多了一个桥接表。
mybatis还有更强大的功能：动态sql，将会在下篇使用。同时还会介绍mybatis generator软件【一款根据数据库的表自动生成pojo类和sql映射文件】
如何想了解更多mybatis，可以去看文档。中文文档网址：http://www.mybatis.org/mybatis-3。
Java_myBatis_一对多映射
例如我们有需求需要实现以下查询 "一个用户对多条订单编号"：
select user.*,o.number,o.createtime from user left JOIN orders o on o.user_id = user.id这时候，我们需要在映射配置文件中使用resultMap
<resultMap type="com.mavenTest.mybatis_test.po.User" id="SelectList">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="birthday" property="birthday"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
<!-- 映射嵌套对象：orders信息 （一对多） -->
<!-- association的类型需要使用javaType来指定 -->
<collection property="orderList" ofType="com.mavenTest.mybatis_test.po.Order">
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
</collection>
</resultMap>
<select id="SelectList" resultMap="SelectList">
select user.*,o.number from user left JOIN orders o on o.user_id = user.id
</select>就是说，当我们的POJO中存在一个List属性的时候，我们需要把这个属性放在collection标签，然后把这个list对应的POJO卸载ofType上
我们今天的关于MyBatis查询：一对多映射关系和mybatis一对多映射原理的分享已经告一段落，感谢您的关注，如果您想了解更多关于java day55【 Mybatis 连接池与事务深入 、 Mybatis 的动态 SQL 语句、 Mybatis 多表查询之一对多 、 Mybatis 多表查询之多对多】、Java SSM框架之MyBatis3（四）MyBatis之一对一、一对多、多对多、java web(六):mybatis之一对一、一对多、多对多映射、Java_myBatis_一对多映射的相关信息，请在本站查询。
本文标签：
![[转帖]Ubuntu 安装 Wine方法（ubuntu如何安装wine）](https://www.gvkun.com/zb_users/cache/thumbs/4c83df0e2303284d68480d1b1378581d-180-120-1.jpg)
