Mybatis中collection和association的使用

蔚落 2023-01-02 01:29 225阅读 0赞

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" 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
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    
    <!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>
            
            <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>
    
    
    
    
    
    </mapper>
    
    12345678910111213141516171819202122232425262728293031323334
    AddressDao.xml
    
    <!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中，并执行成功