mybatis表连接查询

下面是mybatis表连接查询：

 <!--订单表-->
  <resultMap id="BaseResultList" type="com.qianmo.foru.Cache.OrderInfoAndUser">
    <id column="user_id" jdbcType="CHAR" property="userId" />
    <id column="order_no" jdbcType="CHAR" property="orderNo" />
    <id column="order_type" jdbcType="CHAR" property="orderType" />
    <result column="order_stat" jdbcType="VARCHAR" property="orderStat" />
    <result column="txn_type" jdbcType="VARCHAR" property="txnType" />
    <result column="order_user_id" jdbcType="VARCHAR" property="orderUserId" />
    <result column="recive_user_id" jdbcType="VARCHAR" property="reciveUserId" />
    <result column="order_user_name" jdbcType="VARCHAR" property="orderUserName" />
    <result column="mobl_no" jdbcType="VARCHAR" property="moblNo" />
    <result column="demand_title" jdbcType="VARCHAR" property="demandTitle" />
    <result column="demand_content" jdbcType="VARCHAR" property="demandContent" />
    <result column="demand_pic" jdbcType="VARCHAR" property="demandPic" />
    <result column="urgent_push_ident" jdbcType="CHAR" property="urgentPushIdent" />
    <result column="order_address" jdbcType="VARCHAR" property="orderAddress" />
    <result column="favor_price" jdbcType="VARCHAR" property="favorPrice" />
    <result column="goods_budget" jdbcType="VARCHAR" property="goodsBudget" />
    <result column="spread_price" jdbcType="VARCHAR" property="spreadPrice" />
    <result column="snap_date_time" jdbcType="VARCHAR" property="snapDateTime" />
    <result column="order_txn_amt" jdbcType="VARCHAR" property="orderTxnAmt" />
    <result column="order_finish_stat" jdbcType="VARCHAR" property="orderFinishStat" />
    <result column="order_crt_date_time" jdbcType="CHAR" property="orderCrtDateTime" />
    <result column="order_upd_date_time" jdbcType="CHAR" property="orderUpdDateTime" />
    <result column="order_comment_finish_date_time" jdbcType="CHAR" property="orderCommentFinishDateTime" />
    <result column="auto_trans_date_time" jdbcType="CHAR" property="autoTransDateTime" />
    <result column="version" jdbcType="VARCHAR" property="version" />
    <result column="remark" jdbcType="VARCHAR" property="remark" />
    <result column="profit_amt" jdbcType="VARCHAR" property="profitAmt" />
    <result column="user_name" jdbcType="VARCHAR" property="userName" />//用户昵称
    <result column="user_face_img" jdbcType="VARCHAR" property="userFaceImg" /> //用户表的用户头像
  </resultMap>

业务需求是查询订单，并且查出下单人的头像和昵称，这个时候需要新建一个适合这个resultMap的实体been进行接收，

type="com.qianmo.foru.Cache.OrderInfoAndUser"

<!--查询订单-->
  <select id="queryOrderByAll" parameterType="com.qianmo.foru.bean.dto.OrderInfo" resultMap="BaseResultList">
    select oi.order_no, oi.order_type, oi.order_stat, oi.txn_type, oi.order_user_id, oi.recive_user_id, oi.order_user_name,
    oi.mobl_no, oi.demand_title, oi.demand_content, oi.demand_pic, oi.urgent_push_ident, oi.order_address,
    oi.favor_price, oi.goods_budget, oi.spread_price, oi.snap_date_time, oi.order_txn_amt, oi.order_finish_stat,
    oi.order_crt_date_time, oi.order_upd_date_time, oi.order_comment_finish_date_time, oi.auto_trans_date_time,
    oi.version, oi.remark, ui.user_name as userName, ui.user_face_img as userFaceImage
    from order_info oi,user_info ui
    where oi.order_user_id = ui.user_id
    <if test="orderNo != null">
      AND oi.order_no = #{orderNo,jdbcType=VARCHAR}
    </if>
    <if test="orderUserId != null">
      AND oi.order_user_id = #{orderUserId,jdbcType=VARCHAR}
    </if>
    <if test="reciveUserId != null">
      AND oi.recive_user_id = #{reciveUserId,jdbcType=VARCHAR}
      AND oi.order_finish_stat != '00'
    </if>
    <if test="orderFinishStat != null">
      AND oi.order_finish_stat = #{orderFinishStat,jdbcType=VARCHAR}
    </if>
    <if test="remark != null">
      OR oi.order_finish_stat = #{remark,jdbcType=VARCHAR}
    </if>
    <if test="orderType != null">
      AND oi.order_type = #{orderType,jdbcType=CHAR}
    </if>
      AND oi.order_stat = #{orderStat,jdbcType=VARCHAR}
      AND oi.order_finish_stat!='04'
      AND  oi.txn_type != '01'
      AND  oi.txn_type != '02'
      ORDER BY oi.order_crt_date_time DESC
  </select>

package com.qianmo.foru.Cache;

import com.qianmo.foru.bean.dto.OrderInfoKey;

/**
 * Created Author: anpei
 * Created Time 2017/6/13 0013.
 */
public class OrderInfoAndUser extends OrderInfoKey{
    private String orderStat;//订单状态

    private String txnType;//交易类型

    private String orderUserId;//下单人userId

    private String reciveUserId;//接单人userId

    private String orderUserName;//下单人姓名

    private String moblNo;//下单人手机号

    private String demandTitle;//需求标题

    private String demandContent;//需求内容

    private String demandPic;//需求图片

    private String urgentPushIdent;//加急推送标识

    private String orderAddress;//订单地址

    private String favorPrice;//跑腿价格

    private String goodsBudget;//商品预算

    private String spreadPrice;//差价

    private String snapDateTime;//抢单时间

    private String orderTxnAmt;//订单交易金额

    private String orderFinishStat;//订单完成状态

    private String orderCrtDateTime;//订单生成时间

    private String orderUpdDateTime;//订单更新时间

    private String orderCommentFinishDateTime;//订单好评转账时间

    private String autoTransDateTime;//自动好评转账时间

    private String version;//版本

    private String remark;//备注

    private String profitAmt;//获利金额

    private String userName;//用户名称
    private String userFaceImage;//下单人用户头像
    //响应的临时字段
    private String yyyyMMdd;//日期
    private String hhmm;//时间
    private String[] imgStr;//多张图片数组
    private String showOrderType;//移动端显示的订单类型 00：求助订单  01：服务订单  02：需求详情

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName.trim();
    }

    public String getShowOrderType() {
        return showOrderType;
    }

    public void setShowOrderType(String showOrderType) {
        this.showOrderType = showOrderType.trim();
    }

    public String getProfitAmt() {
        return profitAmt;
    }

    public void setProfitAmt(String profitAmt) {
        this.profitAmt = profitAmt;
    }

    public String[] getImgStr() {
        return imgStr;
    }

    public void setImgStr(String[] imgStr) {
        this.imgStr = imgStr;
    }

    public String getYyyyMMdd() {
        return yyyyMMdd;
    }

    public void setYyyyMMdd(String yyyyMMdd) {
        this.yyyyMMdd = yyyyMMdd;
    }

    public String getHhmm() {
        return hhmm;
    }

    public void setHhmm(String hhmm) {
        this.hhmm = hhmm;
    }

    public String getUserFaceImage() {
        return userFaceImage;
    }

    public void setUserFaceImage(String userFaceImage) {
        this.userFaceImage = userFaceImage;
    }

    public String getOrderStat() {
        return orderStat;
    }

    public void setOrderStat(String orderStat) {
        this.orderStat = orderStat == null ? null : orderStat.trim();
    }

    public String getTxnType() {
        return txnType;
    }

    public void setTxnType(String txnType) {
        this.txnType = txnType == null ? null : txnType.trim();
    }

    public String getOrderUserId() {
        return orderUserId;
    }

    public void setOrderUserId(String orderUserId) {
        this.orderUserId = orderUserId == null ? null : orderUserId.trim();
    }

    public String getReciveUserId() {
        return reciveUserId;
    }

    public void setReciveUserId(String reciveUserId) {
        this.reciveUserId = reciveUserId == null ? null : reciveUserId.trim();
    }

    public String getOrderUserName() {
        return orderUserName;
    }

    public void setOrderUserName(String orderUserName) {
        this.orderUserName = orderUserName == null ? null : orderUserName.trim();
    }

    public String getMoblNo() {
        return moblNo;
    }

    public void setMoblNo(String moblNo) {
        this.moblNo = moblNo == null ? null : moblNo.trim();
    }

    public String getDemandTitle() {
        return demandTitle;
    }

    public void setDemandTitle(String demandTitle) {
        this.demandTitle = demandTitle == null ? null : demandTitle.trim();
    }

    public String getDemandContent() {
        return demandContent;
    }

    public void setDemandContent(String demandContent) {
        this.demandContent = demandContent == null ? null : demandContent.trim();
    }

    public String getDemandPic() {
        return demandPic;
    }

    public void setDemandPic(String demandPic) {
        this.demandPic = demandPic;
    }

    public String getUrgentPushIdent() {
        return urgentPushIdent;
    }

    public void setUrgentPushIdent(String urgentPushIdent) {
        this.urgentPushIdent = urgentPushIdent == null ? null : urgentPushIdent.trim();
    }

    public String getOrderAddress() {
        return orderAddress;
    }

    public void setOrderAddress(String orderAddress) {
        this.orderAddress = orderAddress == null ? null : orderAddress.trim();
    }

    public String getFavorPrice() {
        return favorPrice;
    }

    public void setFavorPrice(String favorPrice) {
        this.favorPrice = favorPrice == null ? null : favorPrice.trim();
    }

    public String getGoodsBudget() {
        return goodsBudget;
    }

    public void setGoodsBudget(String goodsBudget) {
        this.goodsBudget = goodsBudget == null ? null : goodsBudget.trim();
    }

    public String getSpreadPrice() {
        return spreadPrice;
    }

    public void setSpreadPrice(String spreadPrice) {
        this.spreadPrice = spreadPrice == null ? null : spreadPrice.trim();
    }

    public String getSnapDateTime() {
        return snapDateTime;
    }

    public void setSnapDateTime(String snapDateTime) {
        this.snapDateTime = snapDateTime == null ? null : snapDateTime.trim();
    }

    public String getOrderTxnAmt() {
        return orderTxnAmt;
    }

    public void setOrderTxnAmt(String orderTxnAmt) {
        this.orderTxnAmt = orderTxnAmt == null ? null : orderTxnAmt.trim();
    }

    public String getOrderFinishStat() {
        return orderFinishStat;
    }

    public void setOrderFinishStat(String orderFinishStat) {
        this.orderFinishStat = orderFinishStat == null ? null : orderFinishStat.trim();
    }

    public String getOrderCrtDateTime() {
        return orderCrtDateTime;
    }

    public void setOrderCrtDateTime(String orderCrtDateTime) {
        this.orderCrtDateTime = orderCrtDateTime == null ? null : orderCrtDateTime.trim();
    }

    public String getOrderUpdDateTime() {
        return orderUpdDateTime;
    }

    public void setOrderUpdDateTime(String orderUpdDateTime) {
        this.orderUpdDateTime = orderUpdDateTime == null ? null : orderUpdDateTime.trim();
    }

    public String getOrderCommentFinishDateTime() {
        return orderCommentFinishDateTime;
    }

    public void setOrderCommentFinishDateTime(String orderCommentFinishDateTime) {
        this.orderCommentFinishDateTime = orderCommentFinishDateTime == null ? null : orderCommentFinishDateTime.trim();
    }

    public String getAutoTransDateTime() {
        return autoTransDateTime;
    }

    public void setAutoTransDateTime(String autoTransDateTime) {
        this.autoTransDateTime = autoTransDateTime == null ? null : autoTransDateTime.trim();
    }

    public String getVersion() {
        return version;
    }

    public void setVersion(String version) {
        this.version = version == null ? null : version.trim();
    }

    public String getRemark() {
        return remark;
    }

    public void setRemark(String remark) {
        this.remark = remark == null ? null : remark.trim();
    }
}

大概就是这样子了。

mapper代码：

    //按各种条件查询订单
    List<OrderInfoAndUser> queryOrderByAll(OrderInfo orderInfo);