mybatis Mapper XML 文件 Result Maps 一对一与一对查询

 一对一关联查询

• 创建一个实体类

package com.touchspring.annualparty. .entity;

import java.util.Date;

public class Chat {
    private String id;
    private String userId;
    private String content;
    private Date createAt;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

。。。。。。略  自行补足
或在类名前添加  @Data  注解可省略getter 和 setter
  }

• 创建Dto类

package com.touchspring.annualparty. .dto;

import com.touchspring.annualparty. .entity.User;

import java.util.Date;

public class ChatDto {

    private String id;
    private String userId;
    private String content;
    private Date createAt;

    /**
     * 用户信息                一对一关联查询的对象
     */
    private User user;

   
    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }
}

• 编写 文件 

<?  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.touchspring.annualparty. .dao.ChatDao\">
    <resultMap id=\" ResultMap\" type=\"com.touchspring.annualparty. .entity.Chat\">
        <id column=\"id\" property=\"id\"/>
        <result column=\"user_id\" property=\"userId\"/>
        <result column=\"content\" property=\"content\"/>
        <result column=\"create_at\" property=\"createAt\"/>
    </resultMap>

    <resultMap id=\" DtoResultMap\" type=\"com.touchspring.annualparty. .dto.ChatDto\">
        <id column=\"id\" property=\"id\"/>
        <result column=\"user_id\" property=\"userId\"/>
        <result column=\"content\" property=\"content\"/>
        <result column=\"create_at\" property=\"createAt\"/>


    <association property=\"user\" javaType=\"com.touchspring.annualparty. .entity.User\">                                                                   
            <result column=\"firstName\" property=\"firstName\"/>
            <result column=\"lastName\" property=\"lastName\"/>
            <result column=\"photo\" property=\"photo\"/>
     </association>
    </resultMap>




    <select id=\"findAllChats\" resultMap=\" DtoResultMap\">
        SELECT c.*,u.first_name AS firstNmae, u.last_name AS lastName,u.photo AS photo
        FROM chat c LEFT JOIN user u
        ON  c.user_id = u.id
    </select>


</mapper>

 一对一关联 association 中使用 JavaType 返回项目实体类  一对多collection关联查询中的使用  ofType 返回项目实体类

 结果集如下：

 \"comments\": [
            {
                \"id\": \"3\",
                \"userId\": \"10051002\",
                \"topic\": \"他\",
                \"content\": \"信标\",
                \"createAt\": \"2018-12-18 09:41\",
                \"user\": {
                    \"id\": null,
                    \"lastName\": \"1\",
                    \"firstName\": \"1\",
                    \" \": null,
                    \"function\": null,
                    \"department\": null,
                    \"country\": null,
                    \"telephone\": null,
                    \"photo\": \"2\",
                    \"password\": null,
                    \"email\": null,
                    \"userGroupId\": null,
                    \"color\": null,
                    \"passportId\": null,
                    \"visa\": null,
                    \"arrivalFlight\": null,
                    \"hotelCheckInAt\": null,
                    \"hotelCheckOutAt\": null,
                    \"clothingSize\": null,
                    \"foodRestriction\": null,
                    \"createAt\": null
                }
            }
             ]

一对多关联查询

• 实体类和Dto类创建基本一致，Dto中稍有不同  一对一为实体对象，一对多为List< >对象

public class EngineeringDto {

    /**
     * ID
     */
    private String id;
    /**
     * 名称
     */
    private String name;
    /**
     * 创建时间
     */
    private Date createAt;
    /**
     * 更新时间
     */
    private Date updateAt;
    /**
     * 分包商
     */
    private List<Subcontractor> subcontractors;
}

• 文件如下

<mapper namespace=\"com.touchspring.isite. .dao.EngineeringDao\">
    <resultMap id=\" ResultMap\" type=\"com.touchspring.isite. .entity.Engineering\">
        <id column=\"id\" property=\"id\"/>
        <result column=\"name\" property=\"name\"/>
        <result column=\"createAt\" property=\"createAt\"/>
        <result column=\"updateAt\" property=\"updateAt\"/>

    </resultMap>

    <resultMap id=\" ResultMapDto\" type=\"com.touchspring.isite. .dto.EngineeringDto\">
        <id column=\"id\" property=\"id\"/>
        <result column=\"name\" property=\"name\"/>
        <result column=\"createAt\" property=\"createAt\"/>
        <result column=\"updateAt\" property=\"updateAt\"/>
        <result column=\"projectId\" property=\"projectId\"/>

        <collection property=\"subcontractors\" ofType=\"com.touchspring.isite. .entity.Subcontractor\">
            <result column=\"subcontractorId\" property=\"id\"/>
            <result column=\"subcontractorName\" property=\"name\"/>
        </collection>

    </resultMap>

 <select id=\"findListInProject\" resultMap=\" ResultMapDto\">
        SELECT e.*,s.id subcontractorId,s.name subcontractorName FROM engineering e
        LEFT JOIN engineering_subcontractor es
        ON e.id = es.engineering_id
        LEFT JOIN subcontractor s
        ON s.id = es.subcontractor_id
        WHERE
        <if test=\"id != null and id != \'\'\">
            es.project_id = #{id}
        </if>
    </select>

</mapper>

• 结果如下

 \"engineering\": [
            {
                \"id\": \"111\",
                \"name\": \"机电消防安装\",
                \"createAt\": null,
                \"updateAt\": null,
                \"subcontractors\": [
                    {
                        \"id\": \"1041156821873725440\",
                        \"name\": \"上海建工集团股份有限公司\",
                        \"phone\": null,
                        \"subType\": null,
                        \"subManager\": null,
                        \"subAddress\": null,
                        \"parentId\": null,
                        \"createAt\": null,
                        \"updateAt\": null,
                        \"children\": null,
                        \"totalCount\": null,
                        \"image\": null
                    },
                    {
                        \"id\": \"1041199607712976896\",
                        \"name\": \"上海威盟斯建筑工程有限公司\",
                        \"phone\": null,
                        \"subType\": null,
                        \"subManager\": null,
                        \"subAddress\": null,
                        \"parentId\": null,
                        \"createAt\": null,
                        \"updateAt\": null,
                        \"children\": null,
                        \"totalCount\": null,
                        \"image\": null
                    }
                ],
                \"projectId\": null,
                \"engAndSubRelationIdArr\": null
            }
               ]