Mybatis中collection和association的使用

Mybatis中的collection标签中的javaType和ofType属性的区别
在使用mybatis时，有时候需要在数据库中进行关联查询(left/right join)来根据某个字段获取到另一个表的的一个List集合。在配置resultMap时，需要用到collection标签对这个LIst属性进行映射：比如在部门表中有一个列表List存放这个表中的所有员工，javaBean如下：
public class Department {

	private Integer id;
	private String departmentName;
	private List<Employee> emps;
}
123456
员工表如下：
public class Employee {

	private Integer id;
	private String lastName;
	private String email;
	private String gender;
}
1234567
用mybatis对这两个表进行关联查询查询：
<select id="getDeptByIdPlus" resultMap="MyDept">
		SELECT d.id did, d.dept_name dept_name,
			e.id eid, e.last_name last_name, e.email email,e.gender gender
		FROM department d
		LEFT JOIN employee e
		ON d.id = e.d_id
		WHERE d.id = 1
</select> 
12345678
由于是通过关联查询得到的这个List，所以此时需要用到resultMap标签对返回值的类型进行自定义：
<resultMap type="bean.Department" id="MyDept">
		<id column="did" property="id"/>
		<result column="dept_name" property="departmentName"/>
		<!-- 
			collection定义关联的集合类型的属性的封装规则:
			property="emps":指定这是哪个集合属性，这里为那个集合属性emps
			ofType:指定集合内封装的JavaBean类型(集合内装的什么)，这里即为Employee类
		 -->
		<collection property="emps" ofType="bean.Employee">
			<!-- 定义集合中元素的封装规则 -->
			<id column="eid" property="id"/>
			<result column="last_name" property="lastName"/>
			<result column="email" property="email"/>
			<result column="gender" property="gender"/>
		</collection>
</resultMap>
12345678910111213141516
在这个resultMap 标签中，用collection这个子标签对这个List进行映射。通过Alt+/可以发现，collection标签中包含两个关于javaBean的Type属性分别是ofType和javaType。其中ofType指定的这个List所存放的javaBean的类型，比如这里就是Employee类型。而javaType指定的当前这个配置的标签所对应的属性，比如我们这里的collection配置的是一个List，就可以配置成javaType=“java.util.ArrayList”（此处没写）。

*collection和association的使用总结：
关于标签association和collection相同点和不同点如图所示：测试结果集
在这里插入图片描述
（一）准备阶段
（1）实体类（User）
package com.jyk.entity;
public class User {
    private Integer id;
    private String name;
    private String password;
    private Address address;

    public Address getAddress() {
        return address;
    }

    public void setAddress(Address address) {
        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 getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }


    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", password='" + password + '\'' +
                ", address=" + address +
                '}';
    }
}
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950
（2）实体类Address
package com.jyk.entity;

/**
 * @Classname Address
 * @Description TODO
 * @Date 2020/7/11 10:50
 * @Created by JYK
 */
public class Address {
    private Integer addno;
    private String city;
    private String county;
    private String town;

    public Integer getAddno() {
        return addno;
    }

    public void setAddno(Integer addno) {
        this.addno = addno;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

    public String getCounty() {
        return county;
    }

    public void setCounty(String county) {
        this.county = county;
    }

    public String getTown() {
        return town;
    }

    public void setTown(String town) {
        this.town = town;
    }

    @Override
    public String toString() {
        return "Address{" +
                "addno=" + addno +
                ", city='" + city + '\'' +
                ", county='" + county + '\'' +
                ", town='" + town + '\'' +
                '}';
    }
}

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657
（二）测试阶段（xml文件配置）
association
<resultMap id="userid" type="com.jyk.entity.User">
        <id property="id" column="id"></id>
        <result property="name" column="name"></result>
        <result property="password" column="password"></result>
        <association property="address" javaType="com.jyk.entity.Address" >
            <id property="addno" column="addno"></id>
            <result property="city" column="city"></result>
            <result property="county" column="country"></result>
            <result property="town" column="town"></result>
        </association>
    </resultMap>
    <select id="selectById" resultMap="userid">
        select * from user left join address on address.addno=user.id where id=#{id}
    </select>
1234567891011121314
collection
<resultMap id="userid" type="com.jyk.entity.User">
        <id property="id" column="id"></id>
        <result property="name" column="name"></result>
        <result property="password" column="password"></result>
        <collection property="address" ofType="com.jyk.entity.Address">
            <id property="addno" column="addno"></id>
            <result property="city" column="city"></result>
            <result property="town" column="town"></result>
            <result property="county" column="county"></result>
        </collection>
    </resultMap>
    <select id="selectById" resultMap="userid">
        select * from user left join address on user.id=address.addno where id=#{id}
    </select>
1234567891011121314
association测试结果collection测试结果因此得出结论：一些场景下，association和collection两者都可以满足需求，达到关联查询的效果，使用两者任意一个标签都可以，具体情况具体分析，以需求为导向实现相应需求。使用任何标签其实都无所谓，只要能满足需求即可。
在这里插入图片描述
在这里插入图片描述
（四）不同点案例及测试
collection属性值包含：association标签属性值包含：因此ofType成为，ofType为collection传入的类型，javaType为返回值类型，例如下图，ofType=Address(完全限定名)，javaType=List（完全限定名）。实体类对象：表示一个人可以有多个收货地址 BaseDao.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.jyk.dao.BaseDao">

    <resultMap id="userid" type="com.jyk.entity.User">
        <id property="id" column="id"></id>
        <result property="name" column="name"></result>
        <result property="password" column="password"></result>
        <!--另外一个dao层的接口-->
        <collection property="addresses" ofType="com.jyk.entity.Address" column="id" javaType="java.util.List" fetchType="lazy" select="com.jyk.dao.AddressDao.selectaddId">
        </collection>
    </resultMap>
    <select id="selectById" resultMap="userid">
        select * from user where id=#{id}
    </select>

    <select id="selectAll" resultType="com.jyk.entity.User" >
        select * from test
     </select>
    <insert id="insert" parameterType="com.jyk.entity.User" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
        insert into user(name,password) values (#{name},#{password})
    </insert>
    <update id="update" parameterType="com.jyk.entity.User" useGeneratedKeys="true" keyProperty="id">
        update user set name=#{name},password=#{password} where id=#{id}
    </update>

<!--    <delete id="delete" >-->
<!--        delete from test where id=#{id} and name=#{name}-->
<!--    </delete>-->

</mapper>

12345678910111213141516171819202122232425262728293031323334
AddressDao.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.jyk.dao.AddressDao">
    <select id="selectaddId" resultType="com.jyk.entity.Address">
        select * from address where addno=#{addno}
    </select>
</mapper>
123456789
最终实验结果：
在这里插入图片描述
总结：
出错点：（1）.xml的文件和dao层接口的文件名称需要一致，在mybatis-config.xml中使用的是package扫描。这一块需要注意算法题：（1）求一个数中二进制1的个数思路：使用N&(~(N)+1)求最右侧为1的位数
在这里插入图片描述
public class Demo {
    public static void main(String[] args) {
        int count = getCount(15);
        System.out.println(count);
    }
    public static int getCount(int N){
        int count=0;
        while (N!=0){
            count++;
            N^=(N&((~N)+1));
        }
        return count;
    }
}
1234567891011121314
（2）一个数组中，只有一个数出现奇数，其他数都是偶数，求此数为多少？(思路：异或)
在这里插入图片描述
public int number(int[]arr){
	int result = 0;
	for(int i=0;i<arr.length;i++){
		result^=arr[i];
	}
	return result;
}

mybatis中association和collection的column传入多个参数值
项目中在使用association和collection实现一对一和一对多关系时需要对关系中结果集进行筛选，如果使用懒加载模式，即联合使用select标签时，主sql和关系映射里的sql是分开的，查询参数传递成为问题。
mybatis文档:
propertydescriptioncolumn数据库的列名或者列标签别名。与传递给resultSet.getString(columnName)的参数名称相同。注意： 在处理组合键时，您可以使用column=“{prop1=col1,prop2=col2}”这样的语法，设置多个列名传入到嵌套查询语句。这就会把prop1和prop2设置到目标嵌套选择语句的参数对象中。
<resultMap id="findCountryCityAddressMap" type="map">
    <result property="country" column="country"/>
    <collection property="cityList"
                column="{cityId=city_id,adr=addressCol, dis=districtCol}" //adr作为第二个sql查询条件key,即prop1属性
                ofType="map"                                              //addressCol即为虚拟列名
                javaType="java.util.List" select="selectAddressByCityId"/>
</resultMap>

<resultMap id="selectAddressByCityIdMap" type="map">
    <result property="city" column="city"/>
    <collection property="addressList" column="city" ofType="map" javaType="java.util.List">
        <result property="address" column="address"/>
        <result property="district" column="district"/>
    </collection>
</resultMap>

<select id="findCountryCityAddress" resultMap="findCountryCityAddressMap">
    SELECT
        ct.country,
        ci.city_id,
        IFNULL(#{addressQuery},'') addressCol, //为传入查询条件,构造虚拟列，虚拟列为查询条件参数值
        IFNULL(#{districtQuery},'') districtCol
    FROM
        country ct
    LEFT JOIN city ci ON ct.country_id = ci.country_id
    ORDER BY ct.country_id
</select>

<select id="selectAddressByCityId" parameterType="java.util.Map" resultMap="selectAddressByCityIdMap">
    SELECT
        ci.city,
        ads.address,
      ads.district
    FROM
        (
            SELECT
                city,
                city_id
            FROM
                city ci
            WHERE
                ci.city_id = #{cityId}
        ) ci
    LEFT JOIN address ads ON ads.city_id = ci.city_id
    <where>
        <if test="adr!=null and adr!=''">
            and ads.address RegExp #{adr}
        </if>
        <if test="dis!=null and dis!=''">
            ads.district Regexp #{dis}
        </if>
    </where>

</select>
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354
测试文件:
@Test
public void findCountryCityAddressTest() throws JsonProcessingException {
    Map<String,Object> param = new HashMap<>();
    param.put("addressQuery","1168");
    List<Map<String, Object>> rs = countryManager.findCountryCityAddress(param);
    ObjectMapper mapper = new ObjectMapper();
    ObjectWriter writer = mapper.writerWithDefaultPrettyPrinter();
    System.out.println(writer.writeValueAsString(rs));
}
123456789
测试结果:
[
    {
        "country": "Afghanistan",
        "cityList": [{
                "city": "Kabul",
                "addressList": [{
                        "address": "1168 Najafabad Parkway",
                        "district": "Kabol"
                    }
                ]
            }
        ],
        "city_id": 251
    },
    {
        "country": "Algeria",
        "cityList": [],
        "city_id": 59
    }
]
1234567891011121314151617181920
可以看到，确实将查询条件通过column参数传入到第二个sql中，并执行成功