有两张表,一张为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='旅客名称'
试试这个看看吧。