A表关联B表2次，A表得用左连接

案例：

采购表和用户表，采购表里有创建人和审核人的id，人员的id在用户表里

表分别是：用户表：sys_user   采购表：procurment

实体类：用户表：User   采购表：Procurment

 

Mybatis：

<sql id="ProcurementColumns">
   p.id as pid,
   p.procurementCode,
   p.procurementType,
   p.createTime,
   p.createById,
   p.auditTime,
   p.auditById,
   p.status,
   su.id as suId,
   su.name as suName,
   sur.id as surId,
   sur.name as surName
</sql>
<resultMap id="ProcurementList" type="Procurement">
   <result property="id" column="pid"/>
   <result property="procurementCode" column="procurementCode"/>
   <result property="procurementType" column="procurementType"/>
   <result property="createTime" column="createTime"/>
   <result property="createById" column="createById"/>
   <result property="auditTime" column="auditTime"/>
   <result property="auditById" column="auditById"/>
   <result property="status" column="status"/>
   <association property="user" javaType="User" >
      <id property="id" column="suId"/>
      <result property="name" column="suName"/>
   </association>
       <association property="user1" javaType="User" >
           <id property="id" column="surId"/>
           <result property="name" column="surName"/>
       </association>
</resultMap>

<select id="findList"  resultMap="ProcurementList">
   SELECT
   <include refid="ProcurementColumns"/>
   from procurement p left join sys_user su on su.id=p.createById
       left join sys_user sur on p.auditById=sur.id
       where 1=1
   <if test="procurementCode!=null and procurementCode!=''">and p.procurementCode like CONCAT ('%',#{procurementCode},'%')</if>
   <if test="procurementType!=null and procurementType!=-1">and p.procurementType = #{procurementType}</if>
   <if test="status!=null and status!=-1">and p.status = #{status}</if>
       ORDER BY p.createTime DESC
</select>

 

 

实体类：

User

private String id; 
private String loginName;// 登录名
private String password;// 密码
private String no;    // 工号
private String name;   // 姓名
private String phone;  // 电话

 

Procurement:

private String id; 
private String procurementCode;//采购单号
private Date createTime;
private Integer createById;//创建人
private Date auditTime;//审核时间
private Integer auditById;//审核人
private User user;
private User user1;

 

 

JSP:页面：

 

<table id="contentTable" class="table table-striped table-bordered table-condensed">
   <thead><tr>
      <th>采购单号</th>
      <th>采购类型</th>
      <th>创建时间</th>
      <th>创建人</th>
      <th>审核时间</th>
      <th>审核人</th>
      <th>审核状态</th>
      <th>操作</th>
   </tr></thead>
   <tbody>
   <c:forEach items="${page.list}" var="procurement">
      <tr>
         <td>${procurement.procurementCode }</td>
         <td>
            <c:if test="${procurement.procurementType==1}">加急</c:if>
            <c:if test="${procurement.procurementType==0}">普通</c:if>
         </td>
         <td><fmt:formatDate pattern="yyyy-MM-dd HH:mm:ss"
                        value="${procurement.createTime }" /></td>
         <td>${procurement.user.name}</td>
         <td><fmt:formatDate pattern="yyyy-MM-dd HH:mm:ss"
                        value="${procurement.auditTime }" /></td>
         <td>${procurement.user1.name }</td>
         <td>
            <c:if test="${procurement.status==2}"><span style="color: red">已作废</span></c:if>
            <c:if test="${procurement.status==1}"><span style="color: blue">已审核</span></c:if>
            <c:if test="${procurement.status==0}"><span style="color: #B2CBFF">未审核</span></c:if>
            <c:if test="${procurement.status==3}"><span style="color: #B2CBFF">已入库</span></c:if>
         </td>
         <td>

               <a href="${ctx}/mm/procurement/view?procurementCode=${procurement.procurementCode }">查看详情</a>

            <c:if test="${procurement.status==1}"><%--已审核可以查看和入库操作--%>

               <a href="${ctx}/mm/procurement/warehouse?procurementCode=${procurement.procurementCode }">到货入库</a>
               <a href="${ctx}/mm/procurement/cancellation?id=${procurement.id}" onclick="return confirmx('确认要作废该采购单吗？', this.href)">作废</a>
            </c:if>
            <c:if test="${procurement.status<1}"><%--未审核和已经审核的可以操作--%>

               <a href="${ctx}/mm/procurement/cancellation?id=${procurement.id}" onclick="return confirmx('确认要作废该采购单吗？', this.href)">作废</a>
            </c:if>

         </td>
      </tr>

   </c:forEach>
   </tbody>
</table>