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 countpo_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=旅客名称

试试这个看看吧。

求大神解惑呀

dao接口:public List<map> queryInfo;

<select id=”queryInfo” resultType=”hashmap”>
select a.??,b.?? from guest a left join preorder b on a.gid = b.gid
</select>

得到的结果,看你想转成什么格式了。如果想自定义key,直接a.xx as “你要的key键”

发表评论

电子邮件地址不会被公开。 必填项已用*标注