java - MYBATIS外键对应表的字段查询本表

问题描述

问 题

有两张表,一张为GUEST代表旅客,一张为PREORDER代表订单,现在我想要用户输入GUEST旅客的名字GNAME就能查询到PREORDER订单,如何做到?
PREORDER中有一个字段GID作为外键关联到GUEST中的主键GID,但是旅客的名字GNAME却什么都不是,只是GUEST中的普通字段而已

我觉得我已经描述的很清楚了吧,代码还需要贴嘛? 求大神解惑呀..

<?xml 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.hotelManager.dao.GuestMapper">

  <!-- 配置缓存 -->
  <cache eviction="LRU" flushInterval="50000" size="512" readOnly="true"/>
<!-- PreOrder的结果集 -->
<resultMap type="com.hotelManager.pojo.PreOrder" id="preOrder-result">
    <id property="po_id" column="po_id"/>
    <result property="po_type" column="po_type"/>
    <result property="po_inDateTime" column="po_inDateTime"/>
    <result property="po_outDateTime" column="po_outDateTime"/>
    <result property="advPayment" column="advPayment"/>
    <result property="poCreateTime" column="poCreateTime"/>
    <association property="guest" column="GID" javaType="com.hotelManager.pojo.Guest" resultMap="guest-result"/>
    <association property="roomType" column="RT_ID" javaType="com.hotelManager.pojo.RoomType" resultMap="roomType-result"/>
</resultMap>      
<!-- Guest的结果集 -->
<resultMap type="com.hotelManager.pojo.Guest" id="guest-result">
    <id property="gId" column="GID" />
    <result property="gName" column="GNAME" />
    <result property="gCardType" column="GCARDTYPE" />
    <result property="gCardId" column="GCARDID" />    
    <result property="gCoutry" column="GCOUTRY" />
    <result property="gAddress" column="GADDRESS" />    
    <result property="gPhone" column="GPHONE" />
    <result property="gSex" column="GSEX" />
    <result property="gCreateTime" column="GCREATETIME" />
    <!-- 多对一 -->
    <association property="member" column="MEM_TYPE" javaType="com.hotelManager.pojo.Member" resultMap="member-result"/>
</resultMap>
<!-- Member的结果集 -->
<resultMap type="com.hotelManager.pojo.Member" id="member-result">
    <id property="mem_type" column="mem_type"/>
    <result property="mem_discount" column="mem_discount"/>
</resultMap>
<resultMap type="com.hotelManager.pojo.RoomType" id="roomType-result">
    <id property="rt_id" column="rt_id"/>
    <result property="rt_name" column="rt_name"/>
    <result property="rt_bedNum" column="rt_bedNum"/>
    <result property="rt_basePrice" column="rt_basePrice"/>
    <result property="rt_discount" column="rt_discount"/>
    <result property="rt_hourBasePrice" column="rt_hourBasePrice"/>
    <result property="rt_perHourPrice" column="rt_perHourPrice"/>
</resultMap>

<select id="findById" parameterType="int" resultMap="preOrder-result">
    select p.*,g.gname,r.rt_name from preOrder p left join guest g on p.gid=g.gid left join roomtype r on p.rt_id=r.rt_id where po_id=#{po_id}
</select>

<!-- 模糊查询 分页 排序 -->
<sql id="WhereCase">
    <where>
        
        <if test="beginDate != null and endDate != null">
            and poCreateTime between #{beginDate} and #{endDate}
        </if>
                
    </where>
</sql>

<sql id="orderBy">
    <if test="sort != null and order != null">
        order by
        <choose>
            <when test="sort == 'po_id'">
                po_id
            </when>
            <when test="sort == 'poCreateTime'">
                poCreateTime
            </when>
        </choose>
        <if test="order == 'asc'">
            ASC
        </if>
        <if test="order == 'desc'">
            DESC
        </if>
    </if>
</sql>

<!--头部-->
<sql id="pageSQLHead">
    select * from
    (select row_number() over(
</sql>

<!--尾部-->
<sql id="pageSQLFoot">

<![CDATA[ ) where rn >= #{pageno} and rn <= #{pagesize} ]]><!-- 转化为字符串 -->

</sql>


<select id="findPager" parameterType="java.util.Map" resultMap="preOrder-result"
    useCache="true" flushCache="false">
    <include refid="pageSQLHead" />
    <include refid="orderBy" />
    ) rn,p.* from preOrder p
    <include refid="WhereCase" />
    <include refid="pageSQLFoot" />
</select>

<select id="findPagerTotal" resultType="long">
select count(po_id) from preOrder <include refid="WhereCase" />

</select>

</mapper>

解决方案

SELECT * FROM PREORDER GID=(SELECT GID FROM GUEST WHERE GNAME='旅客名称')

---

SELECT * FROM PREORDER t1 LEFT JOIN GUEST t2 ON t1.GID=t2.GID WHERE t2.GNAME='旅客名称'

试试这个看看吧。

这篇关于java - MYBATIS外键对应表的字段查询本表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！