一对一关系association标签使用
association字面翻译为联合之意,Java项目开发中常遇到一对一关系的结果,例如,一个商品对应一个生产商,在查询结果中如果某两个对象是一对一关系一般使用association标签,用法有两种:
1、嵌套的resultMap
一次性查询出所有结果的相关字段,结果把所有字段映射到不同的对象的类变量中
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
< resultMap id = "map01" type = "Model01" > < id column = "..." property = "..." /> < result column = "..." property = "..." > ... <!--Model01和Model02为一对一关系--> < association property = "数据类型为Model02在Model01的类变量名称" javaType = "Model02" resultMap = "map02" /> </ resultMap > < resultMap id = "map02" type = "Model02" > < id column = "..." property = "..." /> < result column = "..." property = "..." > ... </ resultMap > < select id = "select01" resultMap = "map01" > select ...最多查询出Model01,Model02所对应的所有字段 from table1(,table2可能需要) where ... order by ... </ select > |
说明:
分别有两个类Model01,Model02,但Model01中有一个类变量的数据类型为Model02 ,id为 select01 的SQL语句所查询的结果映射到 map01 的对应 Model01 对象的各类变量中,因 map01 中使用 association 标签,其 property属性 指定 Model01 中的数据类型为 Model02 的类变量,JavaType属性指定该类变量的数据类型,即Model02,resultMap属性指定对应的结果映射为 map02,map02中列出了相应的表子段和类变量的映射关系,所以一次查出所有需要的字段,只是按不同形式映射到相应各个类的类变量中
2、嵌套的select语句
这种方式实为嵌套一个子查询语句查出关联的实体数据(会产生N+1问题,在多次循环中不好,建议在java层面进行业务分离)
例子如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
< resultMap id = "map03" type = "Model03" > < id column = "..." property = "..." /> < result column = "..." property = "..." > ... < association property = "数据类型为Model04在Model03的类变量名称" javaType = "Model04" column = "对应map03查询结果的某字段,并且该字段正是子查询select04对应需要的参数值" select = "X命名空间.select04" /> </ resultMap > < resultMap id = "map04" type = "Model04" > < id column = "..." property = "..." /> < result column = "..." property = "..." > ... </ resultMap > < select id = "select03" parameterType = "Java某数据类型" resultMap = "map03" > select ...最多查询出Model03所对应的所有(不含Model04对应的字段)字段 from table1 where ... order by ... </ select > < select id = "select04" parameterType = "Java某数据类型" resultMap = "map04" > select ...最多查询出Model04所对应的所有字段 from table2 where ... order by ... </ select > |
说明:
分别有两个类 Model03,Model04,但Model03中有一个类变量的数据类型为Model04 ,id 为 select03 的SQL语句所查询的结果映射到 map03 的对应 Model03 对象的各类变量中,因 map03 中使用 association 标签,其 property属性 指定 Model03 中的数据类型为 Model04 的类变量,column属性为 map03 中的某字段,该字段值正是子查询select04所需的参数,select属性为指定需要的子查询,即ID为select04的子查询,map04中列出本查询结果相应的表子段和类变量的映射关系,所以首先查出父对象所有需要的所有字段,完成映射,同时使用嵌套的子查询查出所需的字段并映射到相应的类,再把该类付给父级对象对应的变量
association标签三种用法
`father`表
1
2
3
4
5
6
7
8
|
CREATE TABLE `father` ( `ID` int (11) NOT NULL , ` NAME ` varchar (255) DEFAULT NULL , PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `father` VALUES ( '1' , '李靖' ); INSERT INTO `father` VALUES ( '2' , '康熙' ); |
`son`表
1
2
3
4
5
6
7
8
9
|
CREATE TABLE `son` ( `ID` int (11) NOT NULL , `FATHER_ID` int (11) DEFAULT NULL , ` NAME ` varchar (255) DEFAULT NULL , PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `son` VALUES ( '1' , '2' , '雍正' ); INSERT INTO `son` VALUES ( '2' , '1' , '哪吒' ); |
Father.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
public class Father { 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(); } } |
Son.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
public class Son { private Integer id; private Father father; private String name; public Integer getId() { return id; } public void setId(Integer id) { this .id = id; } public Father getFather() { return father; } public void setFather(Father father) { this .father = father; } public String getName() { return name; } public void setName(String name) { this .name = name == null ? null : name.trim(); } } |
FatherMapper.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
<? 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.ksy.kuaishiyan.mapper.FatherMapper" > < resultMap id = "BaseResultMap" type = "com.ksy.kuaishiyan.entity.Father" > < id column = "ID" property = "id" jdbcType = "INTEGER" /> < result column = "NAME" property = "name" jdbcType = "VARCHAR" /> </ resultMap > < sql id = "Base_Column_List" > ID, NAME </ sql > < select id = "selectByPrimaryKey" resultMap = "BaseResultMap" parameterType = "java.lang.Integer" > select < include refid = "Base_Column_List" /> from father where ID = #{id,jdbcType=INTEGER} </ select > </ mapper > |
SonMapper.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
<? 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.ksy.kuaishiyan.mapper.SonMapper" > < resultMap id = "BaseResultMap" type = "com.ksy.kuaishiyan.entity.Son" > < id column = "ID" property = "id" jdbcType = "INTEGER" /> < result column = "FATHER_ID" property = "fatherId" jdbcType = "INTEGER" /> < result column = "NAME" property = "name" jdbcType = "VARCHAR" /> </ resultMap > < sql id = "Base_Column_List" > ID, FATHER_ID, NAME </ sql > < select id = "selectByPrimaryKey" resultMap = "BaseResultMap" parameterType = "java.lang.Integer" > select < include refid = "Base_Column_List" /> from son where ID = #{id,jdbcType=INTEGER} </ select > </ mapper > |
association的用法一
直接在SonMapper.xml中的association标签里写对应的列名, 且列明需要写别名, 例如: father.ID AS F_ID
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
<? 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.ksy.kuaishiyan.mapper.SonMapper" > < resultMap id = "BaseResultMap" type = "com.ksy.kuaishiyan.entity.Son" > < id column = "ID" property = "id" jdbcType = "INTEGER" /> < result column = "NAME" property = "name" jdbcType = "VARCHAR" /> < association property = "father" javaType = "com.ksy.kuaishiyan.entity.Father" > < id column = "F_ID" property = "id" jdbcType = "INTEGER" /> < result column = "F_NAME" property = "name" jdbcType = "VARCHAR" /> </ association > </ resultMap > < sql id = "Base_Column_List" > son.ID, son.NAME, father.ID AS F_ID, father.NAME AS F_NAME </ sql > < select id = "selectByPrimaryKey" resultMap = "BaseResultMap" parameterType = "java.lang.Integer" > select < include refid = "Base_Column_List" /> from son, father where son.FATHER_ID=father.ID AND son.ID = #{id,jdbcType=INTEGER} </ select > </ mapper > |
调用selectByPrimaryKey传入id=2, 查询结果如下
{
"id": 2,
"father": {
"id": 1,
"name": "李靖"
},
"name": "哪吒"
}
association的用法二
association传入一个ResultMap, 改写SonMapper.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
<? 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.ksy.kuaishiyan.mapper.SonMapper" > < resultMap id = "BaseResultMap" type = "com.ksy.kuaishiyan.entity.Son" > < id column = "ID" property = "id" jdbcType = "INTEGER" /> < result column = "NAME" property = "name" jdbcType = "VARCHAR" /> < association property = "father" javaType = "com.ksy.kuaishiyan.entity.Father" resultMap = "com.ksy.kuaishiyan.mapper.FatherMapper.BaseResultMap" ></ association > </ resultMap > < sql id = "Base_Column_List" > son.ID, son.NAME, father.ID AS F_ID, father.NAME AS F_NAME </ sql > < select id = "selectByPrimaryKey" resultMap = "BaseResultMap" parameterType = "java.lang.Integer" > select < include refid = "Base_Column_List" /> from son, father where son.FATHER_ID=father.ID AND son.ID = #{id,jdbcType=INTEGER} </ select > </ mapper > |
association 标签中resultMap属性指向FatherMapper.xml中的BaseResultMap, 这种情况下要求father表和son表没有相同名字的字段, 否则会失败. 调用selectByPrimaryKey传入id=2, 查询结果失败如下:
{
"id": 2,
"father": {
"id": 2,
"name": "哪吒"
},
"name": "哪吒"
}
association的用法三
给association传入一个select
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
<? 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.ksy.kuaishiyan.mapper.SonMapper" > < resultMap id = "BaseResultMap" type = "com.ksy.kuaishiyan.entity.Son" > < id column = "ID" property = "id" jdbcType = "INTEGER" /> < result column = "NAME" property = "name" jdbcType = "VARCHAR" /> < association column = "FATHER_ID" property = "father" javaType = "com.ksy.kuaishiyan.entity.Father" select = "com.ksy.kuaishiyan.mapper.FatherMapper.selectByPrimaryKey" ></ association > </ resultMap > < sql id = "Base_Column_List" > ID, NAME, FATHER_ID </ sql > < select id = "selectByPrimaryKey" resultMap = "BaseResultMap" parameterType = "java.lang.Integer" > select < include refid = "Base_Column_List" /> from son where ID = #{id,jdbcType=INTEGER} </ select > </ mapper > |
这种方式相当于将原来的
1
|
< result column = "FATHER_ID" property = "fatherId" jdbcType = "INTEGER" /> |
替换成
1
|
< association column = "FATHER_ID" property = "father" javaType = "com.ksy.kuaishiyan.entity.Father" select = "com.ksy.kuaishiyan.mapper.FatherMapper.selectByPrimaryKey" ></ association > |
改动最小, 需要assonciation标签放到所有result标签之后, select语句还可以延迟加载.
在一个<resultMap/>中,属性出现的先后顺序必须是:constructor-->id --> result--> association-->collection -->discriminator。
以上为个人经验,希望能给大家一个参考,也希望大家多多支持服务器之家。
原文链接:https://blog.csdn.net/shenzhenNBA/article/details/86502918