十三、Mybatis之“多对一”和“一对一”

（一）类文件

1.多方
public class Business extends BaseBean {
    private String title;
    private String subtitle;
    private String imgFileName;
    private Integer price;
    private Integer distance;
    /**
     * 已售数量
     */
    private Integer number;
    /**
     * 星级
     */
    private Integer star;
    private String desc;
    /**
     * 这里对应字典里的代码
     */
    private String city;
    private String category;

    /**
     * 多对一
     * 上面对应字典里的代码，而我们需要显示的是字典中的name，所以这里加了两个Dictionary对象
     */
    private Dictionary cityDictionary;
    private Dictionary categoryDictionary;
 }
2.1方
 public class Dictionary extends BaseBean {
    private String type;
    private String code;
    private String name;
    private String weight;
}

（二）Mybatis配置文件

<resultMap id="businessMap" type="Business">
        <id column="id" property="id"/>
        <result column="title" property="title"/>
        <result column="subtitle" property="subtitle"/>
        <result column="img_file_name" property="imgFileName"/>
        <result column="price" property="price"/>
        <result column="distance" property="distance"/>
        <result column="number" property="number"/>
        <result column="star" property="star"/>
        <result column="desc" property="desc"/>
        <result column="city" property="city"/>
        <result column="category" property="category"/>
        <association property="cityDictionary" javaType="Dictionary">
            <!--这里的column对应的是查询出来的别名，不是数据库中的列名-->
            <result column="city_name" property="name"/>
        </association>
        <association property="categoryDictionary" javaType="Dictionary">
            <result column="category_name" property="name"/>
        </association>
    </resultMap>

  注意：必须配置完所有的属性，否则只要没配置，查询出来的就是null
（三）查询语句

<select id="searchByPage" parameterType="Business" resultMap="businessMap">
        SELECT b.id,b.title,b.subtitle,b.img_file_name,b.price,b.distance,b.desc,dic_city.name city_name,dic_category.name category_name FROM business b
        LEFT JOIN dic dic_city ON b.city=dic_city.code AND dic_city.type='${@com.imooc.constant.DictionaryConstant@TYPE_DICTIONARY_CITY}'
        LEFT JOIN dic dic_category ON b.category=dic_category.code AND dic_category.type='${@com.imooc.constant.DictionaryConstant@TYPE_DICTIONARY_CATEGORY}'
        <where>
            <if test="city != null and !&quot;&quot;.equals(city.trim())">AND city=#{city}</if>
            <if test="category != null and !&quot;&quot;.equals(category.trim())">AND category=#{category}</if>
            <if test="searchKey != null and !&quot;&quot;.equals(searchKey.trim())">
                AND (
                b.title LIKE CONCAT('%',#{searchKey},'%')
                OR  b.subtitle LIKE CONCAT('%',#{searchKey},'%')
                OR  b.desc LIKE CONCAT('%',#{searchKey},'%')
                )
            </if>
        </where>
    </select>