Mybatis--参数与结果的映射

1 参数的映射
传递一个Map，在Xml中parameterType设置为"map"，在sql中可直接取map的key传递进去即可。
接口：

public List<User> testMapParam(Map<String,Object> param);

XML：

<select id="testMapParam" parameterType="map" resultType="com.test.mybatis.pojo.User">
		SELECT
		<include refid="Base_Column_List" />
		FROM user
		WHERE name LIKE CONCAT("%",#{name},"%")
	</select>

测试：

public static void testMapParam() {
		Map<String,Object> param=new HashMap<String,Object>();
		param.put("name", "测试");
		List<User> lstUser = userDao.testMapParam(param);
		for(User user:lstUser) {
			System.out.println(user);
		}
	}

参数传递Map

    <update id="updateCompany">
        UPDATE company
        <set>
            <foreach collection="map.entrySet()" index="key" item="value" separator=" , ">
                ${key} = #{value}
            </foreach>
        </set>
        WHERE id=#{companyId}
    </update>

参数传递List

    <insert id="batchInsert">
        INSERT INTO company(company_name,user_id,create_time)
        <foreach collection="lstCompany" item="company" open=" VALUES " close="" separator=",">
            (#{company.companyName},#{company.userId},now())
        </foreach>
    </insert>

参考：https://www.cnblogs.com/ismallboy/p/11076333.html

2 结果映射
映射到实体可以将resultType设置为实体的全限定名。映射成Map将resultType设置为"map"即可。
Dao：

public List<Map<String,Object>> testResultMap(Integer id);

XML

<select id="testResultMap" parameterType="java.lang.Integer" resultType="map">
		SELECT
		<include refid="Base_Column_List" />
		FROM user
		WHERE id=#{id}
	</select>

测试：

public static void testResultMap() {
		List<Map<String, Object>> testResultMap = userDao.testResultMap(3);
		for(Map<String, Object> map:testResultMap) {
			System.out.println(map);
		}
	}

association标签
主要用在一对一的场景下，比如一个用户对于一个公司，现在有user表和company表，company的user_id对于user表的id。
Company Model中有一个User类型的变量，利用association实现嵌套查询
CompanyMapper.xml

    <resultMap id="companyAndUserMap" type="com.test.model.CompanyAndUserDetail" extends="BaseResultMap">
        <association property="user" column="user_id" javaType="com.test.model.User" select="com.test.dao.UserMapper.selectByPrimaryKey">
        </association>
    </resultMap>

    <select id="selectUserAndCompanyInfo" resultMap="companyAndUserMap">
        SELECT *
        FROM company
    </select>

子查询用户信息

    <select id="selectByPrimaryKey" resultType="com.test.model.User">
        select *
        FROM user
        WHERE id=#{userId}
    </select>

打印SQL日志

发现执行了一次company查询，多次user查询，这就是n+1查询问题，一次查询（即可+1）出了多条记录，每条记录然后去执行子查询（即为n）。

使用关联的嵌套结果映射

    <resultMap id="companyAndUserMap02" type="com.test.model.CompanyAndUserDetail" extends="BaseResultMap">
        <association property="user" javaType="com.test.model.User" columnPrefix="u_">
            <id column="id" property="id" />
            <result column="username" property="username" />
            <result column="password" property="password" />
            <result column="create_time" property="createTime" />
        </association>
    </resultMap>
    <select id="selectCompanyAndUserInfo02" resultMap="companyAndUserMap02">
        SELECT c.*,
         u.id AS u_id,u.username AS u_username,u.password AS u_password,
         u.create_time AS u_create_time
        FROM company AS c
        LEFT JOIN user AS u ON c.user_id=u.id
    </select>

可以避免N+1查询问题

collection集合
适用于一对多的场景，比如一个用户有多个角色，在用户model中有一个角色集合，通过collection标签，将数据映射到List变量中，需要设置get和set方法

    <resultMap id="userAndRoleMap" type="com.test.model.UserRoleDetail" extends="BaseResultMap">
        <collection property="role" columnPrefix="r_" ofType="com.test.model.Role">
            <id column="id" property="id" />
            <result column="role_name" property="roleName" />
            <result column="create_time" property="createTime" />
        </collection>
    </resultMap>
    <select id="selectUserAndRoleDetail" resultMap="userAndRoleMap">
        SELECT u.*,
        r.id AS r_id,
        r.role_name AS r_role_name,
        r.create_time AS r_create_time
        FROM user AS u
        LEFT JOIN user_role AS ur ON u.id=ur.user_id
        LEFT JOIN role AS r ON ur.role_id=r.id
    </select>