Welcome toVigges Developer Community-Open, Learning,Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
785 views
in Technique[技术] by (71.8m points)

mybatis三表联合查询显示三个表的全部信息同时可以按不同的条件查询

三个表分别是:order,member,setmeal

  • order字段:id,memberId,setmealId,orderState...
  • member字段:id,name,telephone
  • setmeal字段:id,name,price
order的memberId对应member的id,order的setmealId对应setmeal的id

要求是:查询所有的order信息,同时要有member和setmeal的所有信息,并且可以实现以 member的name,telephone,setmeal的name,order的orderState 作为条件查询,实现单条件查询,多条件查询

我目前只实现了查到全部信息,
image.png

但是条件查询就开始迷茫了,也没有Example类,这个怎么实现


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

<resultMap id="map1" type="com.health.pojo.Order">

    <id property="id" column="id"/>
    <result property="memberId" column="memberId"/>
    <result property="orderDate" column="orderDate"/>
    <result property="orderType" column="orderType"/>
    <result property="orderState" column="orderState"/>
    <result property="setmealId" column="setmealId"/>
    <association property="member" column="id" resultMap="memberMap" javaType="com.health.pojo.Member"></association>
    <association property="setMeal" column="id" resultMap="setmealMap" javaType="com.health.pojo.SetMeal"></association>
</resultMap>
<resultMap id="setmealMap" type="com.health.pojo.SetMeal">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <result property="code" column="code"/>
    <result property="helpCode" column="helpCode"/>
    <result property="sex" column="sex"/>
    <result property="age" column="age"/>
    <result property="price" column="price"/>
    <result property="remark" column="remark"/>
    <result property="attention" column="attention"/>
    <result property="img" column="img"/>
</resultMap>
<resultMap id="memberMap" type="com.health.pojo.Member">
    <id property="id" column="id"/>
    <result property="fileNumber" column="fileNumber"/>
    <result property="name" column="name"/>
    <result property="sex" column="sex"/>
    <result property="idCard" column="idCard"/>
    <result property="phoneNumber" column="phoneNumber"/>
    <result property="regTime" column="regTime"/>
    <result property="email" column="email"/>
    <result property="birthday" column="birthday"/>
    <result property="remark" column="remark"/>
</resultMap>
<select id="showOrderList" resultMap="map1" parameterType="map">
    select o.*,m.*,s.*
    from t_order o left outer join t_setmeal s on  o.setmealId=s.id left outer join t_member m on o.memberId=m.id
    <where>
        <if test="setmealName!=null and setmealName!=''">
            and s.name like concat('%',#{setmealName},'%')
        </if>
        <if test="queryString!=null and queryString!=''">
            and (m.name like CONCAT('%',#{queryString},'%') or m.phoneNumber like CONCAT('%',#{queryString},'%'))
        </if>
        <if test="orderType!=null and orderType!=''">
            and o.orderType =#{orderType}
        </if>
        <if test="orderState!=null and orderState!='' ">
            and o.orderState=#{orderState}
        </if>
        <if test="startDate!=null and startDate!='' and endDate!=null and endDate!=''">
            and (o.orderDate between #{startDate} and #{endDate})
        </if>
    </where>
</select>

解决了


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to Vigges Developer Community for programmer and developer-Open, Learning and Share
...