mybatis多表联查，mybatis多表联查模糊查询，以及多表联查模糊查询查询不出数据问题

本文详细介绍了使用MyBatis进行多表联查的方法，包括根据产品名称精确查询、根据编号集合查询以及模糊查询产品信息。通过具体的SQL语句示例，展示了如何运用if条件判断和foreach循环来实现灵活的查询需求。

##多表联查

<select id="findByProductName" resultType="com.test.MyProductInfo">
       select
        p.id,
        p.product_name as productName,
        ps.sales_volumes as salesVolumes,
        sp.my_price as myPrice,
        sp.distribution_amount as distributionAmount,
        sp.cost
        from
          gms_product p,
          gms_product_sale ps,
          gms_store_product sp
        where
          p.sn=ps.product_sn and sp.product_sn=p.sn
        <if test="storeSn !=null">
          and p.store_sn=#{storeSn}
        </if>
        <if test="productSn !=null">
          and ps.product_sn=#{productSn}
        </if>
</select>

##多表联查根据编号集合查询所需产品信息

<select id="findByProductSnList" parameterType="java.util.List" resultMap="StoreHomepageProductMap">
      select 
          p.product_name,
          p.product_specification,
          p.photo,
          ps.sales_volumes,
          sp.amount
         from
          gms_product p,
          gms_product_sale ps,
          gms_store_product sp
          where p.sn=ps.product_sn 
                and ps.product_sn=sp.product_sn 
                and ps.product_sn in
                <foreach collection="productSn" index="index" item="item" open="(" close=")" separator=",">
                     #{item}
               </foreach>
</select>

##多表联查模糊查询

<select id="findByProductName" resultType="com.test.MyProductInfo">
        select
        p.id,
        p.product_name as productName,
        ps.sales_volumes as salesVolumes,
        sp.my_price as myPrice,
        sp.distribution_amount as distributionAmount,
        sp.cost
        from
        gms_product p,
        gms_product_sale ps,
        gms_store_product sp
        where
            p.sn=ps.product_sn and sp.product_sn=p.sn
        <if test="storeSn !=null">
            and p.store_sn=#{storeSn}
        </if>
        <if test="productName != null">
            AND p.product_name like concat('%',concat(#{productName}),'%')
        </if>
</select>

##模糊查询查询不出数据问题

模糊查询改成这样

product_name like concat('%',concat(#{productName}),'%')

mybatis多表联查，mybatis多表联查模糊查询，以及多表联查模糊查询查询不出数据问题

1 条评论