Mybatis高级查询(一对一,一对多,多对多)

数据准备

CREATE TABLE &＃96;student_info&＃96; (&＃96;classes&＃96; varchar(255) NOT NULL,&＃96;name&＃96; varchar(255) NOT NULL,&＃96;student_numer&＃96; int(11) DEFAULT NULL,&＃96;subject&＃96; varchar(255) DEFAULT NULL,&＃96;score&＃96; int(11) DEFAULT NULL
) ENGINE&＃61;InnoDB DEFAULT CHARSET&＃61;utf8;INSERT INTO &＃96;student_info&＃96; VALUES (&＃39;1班&＃39;, &＃39;张三&＃39;, &＃39;1&＃39;, &＃39;语文&＃39;, &＃39;100&＃39;);
INSERT INTO &＃96;student_info&＃96; VALUES (&＃39;1班&＃39;, &＃39;张三&＃39;, &＃39;1&＃39;, &＃39;数学&＃39;, &＃39;99&＃39;);
INSERT INTO &＃96;student_info&＃96; VALUES (&＃39;1班&＃39;, &＃39;张三&＃39;, &＃39;1&＃39;, &＃39;英语&＃39;, &＃39;98&＃39;);
INSERT INTO &＃96;student_info&＃96; VALUES (&＃39;1班&＃39;, &＃39;李四&＃39;, &＃39;2&＃39;, &＃39;语文&＃39;, &＃39;100&＃39;);
INSERT INTO &＃96;student_info&＃96; VALUES (&＃39;1班&＃39;, &＃39;李四&＃39;, &＃39;2&＃39;, &＃39;数学&＃39;, &＃39;99&＃39;);
INSERT INTO &＃96;student_info&＃96; VALUES (&＃39;1班&＃39;, &＃39;李四&＃39;, &＃39;2&＃39;, &＃39;英语&＃39;, &＃39;99&＃39;);
INSERT INTO &＃96;student_info&＃96; VALUES (&＃39;2班&＃39;, &＃39;王五&＃39;, &＃39;3&＃39;, &＃39;数学&＃39;, &＃39;100&＃39;);
INSERT INTO &＃96;student_info&＃96; VALUES (&＃39;2班&＃39;, &＃39;王五&＃39;, &＃39;3&＃39;, &＃39;语文&＃39;, &＃39;100&＃39;);
INSERT INTO &＃96;student_info&＃96; VALUES (&＃39;2班&＃39;, &＃39;王五&＃39;, &＃39;3&＃39;, &＃39;英语&＃39;, &＃39;100&＃39;);


代码环境搭建:
依赖:

<parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.1.3.RELEASE</version></parent><dependencies><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><scope>runtime</scope></dependency><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>2.0.0</version></dependency></dependencies>


application.yml配置文件:

server:port: 8888spring:datasource:url: jdbc:mysql://localhost:3306/test?useUnicode&＃61;true&characterEncoding&＃61;utf8&zeroDateTimeBehavior&＃61;convertToNull&useSSL&＃61;true&serverTimezone&＃61;GMT%2B8username: rootpassword: rootdriver-class-name: com.mysql.cj.jdbc.Driver#必须配置mapperxml的路径
mybatis:mapper-locations: classpath:mybatis/*.xml


启动类:

&＃64;SpringBootApplication
public class Application {public static void main(String[] args) {SpringApplication.run(Application.class,args);}
}


Controller

&＃64;RestController
&＃64;RequestMapping("/")
public class MyConrtroller {&＃64;AutowiredMyService service;//一对一&＃64;GetMapping("/getStudentInfo1")public Object getStudentInfo1(){return service.getStudentInfo1();}//一对多&＃64;GetMapping("/getStudentInfo2")public Object getStudentInfo2(){return service.getStudentInfo2();}//多对多&＃64;GetMapping("/getStudentInfo3")public Object getStudentInfo3(){return service.getStudentInfo3();}
}


service:

&＃64;Service
public class MyService {&＃64;AutowiredMyMapper mapper;public Object getStudentInfo1() {return mapper.getStudentInfo1();}public Object getStudentInfo2() {return mapper.getStudentInfo2();}public Object getStudentInfo3() {return mapper.getStudentInfo3();}
}

mapper接口

&＃64;Mapper
public interface MyMapper {StudentInfo getStudentInfo1();StudentInfo getStudentInfo2();ArrayList<StudentInfo> getStudentInfo3();}


一对一

Mapper.xml文件

<select id&＃61;"getStudentInfo1" resultMap&＃61;"resultMap1">select * from test.student_info where classes&＃61;&＃39;1班&＃39; and name &＃61;&＃39;张三&＃39; and subject &＃61; &＃39;语文&＃39;select><resultMap id&＃61;"resultMap1" type&＃61;"com.anjiplus.mybatistest.bean.StudentInfo" autoMapping&＃61;"true"><id column&＃61;"classes" property&＃61;"classes">id><association property&＃61;"student" javaType&＃61;"com.anjiplus.mybatistest.bean.Student" autoMapping&＃61;"true" ><id column&＃61;"name" property&＃61;"name">id><id column&＃61;"student_numer" property&＃61;"studentNumber">id><association property&＃61;"subjectAndScore" javaType&＃61;"com.anjiplus.mybatistest.bean.SubjectAndScore" autoMapping&＃61;"true" ><id column&＃61;"subject" property&＃61;"subject">id><result column&＃61;"score" property&＃61;"score">result>association>association>resultMap>


sql查询结果为:

对应的javaBean:

public class StudentInfo {private String classes;private Student student;//为了篇幅省略getter和setter方法
}public class Student {private String name;private Integer studentNumber;private SubjectAndScore subjectAndScore;//为了篇幅省略getter和setter方法
}public class SubjectAndScore {private String subject;private Integer score;//为了篇幅省略getter和setter方法
}


调用getStudentInfo1接口返回:

一对多

Mapper.xml

<select id&＃61;"getStudentInfo2" resultMap&＃61;"resultMap2">select * from test.student_info where classes &＃61;&＃39;1班&＃39; and name &＃61;&＃39;张三&＃39;select><resultMap id&＃61;"resultMap2" type&＃61;"com.anjiplus.mybatistest.bean.StudentInfo" autoMapping&＃61;"true"><id column&＃61;"classes" property&＃61;"classes">id><association property&＃61;"student" javaType&＃61;"com.anjiplus.mybatistest.bean.Student" autoMapping&＃61;"true" ><id column&＃61;"name" property&＃61;"name">id><id column&＃61;"student_numer" property&＃61;"studentNumber">id><collection property&＃61;"subjectAndScore" javaType&＃61;"java.util.ArrayList" ofType&＃61;"com.anjiplus.mybatistest.bean.SubjectAndScore"><id column&＃61;"subject" property&＃61;"subject">id><result column&＃61;"score" property&＃61;"score">result>collection>association>resultMap>


sql查询结果

对应javabean:

public class StudentInfo {private String classes;private Student student;//为了篇幅省略getter和setter方法
}public class Student {private String name;private Integer studentNumber;private ArrayList<SubjectAndScore> subjectAndScore;//为了篇幅省略getter和setter方法
}public class SubjectAndScore {private String subject;private Integer score;//为了篇幅省略getter和setter方法
}


调用getStudentInfo2接口返回:

多对多

Mapper.xml

<select id&＃61;"getStudentInfo3" resultMap&＃61;"resultMap3">select * from test.student_infoselect><resultMap id&＃61;"resultMap3" type&＃61;"com.anjiplus.mybatistest.bean.StudentInfo" autoMapping&＃61;"true"><id column&＃61;"classes" property&＃61;"classes">id><collection property&＃61;"student" javaType&＃61;"java.util.ArrayList" ofType&＃61;"com.anjiplus.mybatistest.bean.Student" autoMapping&＃61;"true" ><id column&＃61;"name" property&＃61;"name">id><id column&＃61;"student_numer" property&＃61;"studentNumber">id><collection property&＃61;"subjectAndScore" javaType&＃61;"java.util.ArrayList" ofType&＃61;"com.anjiplus.mybatistest.bean.SubjectAndScore"><id column&＃61;"subject" property&＃61;"subject">id><result column&＃61;"score" property&＃61;"score">result>collection>collection>resultMap>


sql查询结果:

对应javabean:

public class StudentInfo {private String classes;private ArrayList<Student> student;//为了篇幅省略getter和setter方法
}public class Student {private String name;private Integer studentNumber;private ArrayList<SubjectAndScore> subjectAndScore;//为了篇幅省略getter和setter方法
}public class SubjectAndScore {private String subject;private Integer score;//为了篇幅省略getter和setter方法
}


调用getStudentInfo3接口返回:

ResultMap

在select标签中,有resultType属性,这个属性用于对应sql查询出来的每一行数据封装一个数据类型,而resultMap可以做到多行数据封装成一个类型.,但是需要我们在xml中对数据类型进行提前拼接定义.
ResultMap标签属性:
id属性:用于识别唯一,在select 标签中通过resultMap属性进行引用.
type属性:指ResultMap封装成的java数据类型
autoMapping属性:是否将没有在ResultMap中配置的映射的列自动映射到结果类型中.比如在A类中有a b c d四个列,但是resutMap中我们只设置了a和b列,设置为true后,可以将查询出来的c d直接封装到A类中.
extending属性:ResultMap的继承,a继承了b那么在a中就不想要再写b中已经有的内容了.

ResultMap子标签:

id 标签: 用来配置"主键"(比如查询出两行数据a列都是1,那么在结果类型中a字段就是一个值:1)
column:sql中查询出来的字段名
property:映射的java类型中的属性名
javaType:对应的java类型
jdbcType:数据库类型
typeHandler:基本不用,要用自行百度
result 标签:用来设置sql查询出来的列与java类型中的列的映射(非主键)
属性同id标签
**constructor 标签:**通过构造函数属性注入,否则的话是通过setter方法注入属性,并且通过setter方法注入的类必须只有无参构造
**association 标签:**标注一对一关系
property:指association所封装的数据结构在resultmap返回类型中的属性名
javaType:指association锁封装的数据结构类型
autoMapping:
其他属性:略
select:指定一个mapper接口的方法,比如com.anjiplus.mapper.MyMapper.getId
**collection 标签:**标注多对多关系
property:指collection 所封装的数据结构在上一层返回类型中的属性名
javaType:指collection 锁封装的数据结构类型
ofType:指collection中每一个元素的数据类型
autoMapping:
select:指定一个mapper接口的方法,比如com.anjiplus.mapper.MyMapper.getId
其他不常用属性略
discriminator 标签: 对查询出的某个列的值进行鉴别,如果是值1就xxx 如果是值2就xxx(不常用,自行百度)
collection 与 association 标签的字标签与ResultMap的子标签相同(所以ResultMap是镶嵌结构)