mybaits工作整理笔记

分类属性一对多查询

<resultMap type="CollectDictDataAttribute" id="CollectDictDataAttributeResult">
    <result property="id"    column="id"    />
    <result property="collectDictDataId"    column="collect_dict_data_id"    />
    <result property="paramContent"    column="param_content"    />
    <result property="insertTime"    column="insert_time"    />
    <result property="insertPerson"    column="insert_person"    />
    <result property="renewalTime"    column="renewal_time"    />
    <result property="updatePerson"    column="update_person"    />
    <result property="isDel"    column="is_del"    />
    <result property="isComponent"    column="is_component"    />
    <result property="isRequire"    column="is_require"    />
    <result property="remarks"    column="remarks"    />
    <result property="filterType"    column="filter_type"    />

    <collection property="typeList" ofType="CollectDictDataAttributeOption">
        <result property="id" column="opId"/>
        <result property="attributeId" column="attribute_id"/>
        <result property="optionName" column="option_name"/>

    </collection>

</resultMap>
public class CollectDictDataAttribute extends BaseEntity
private List<CollectDictDataAttributeOption> typeList;
  <select id="selectCollectDictDataAttributeisNoComponentById" parameterType="integer" resultMap="CollectDictDataAttributeResult">
  select collect_dict_data_attribute.*,cddac.is_show as is_require,addao.option_name,addao.id  as opid from collect_dict_data_attribute
      left join collect_dict_data_attribute_config cddac on cddac.collect_dict_data_attribute_id = collect_dict_data_attribute.id
LEFT JOIN collect_dict_data_attribute_option addao ON addao.attribute_id = collect_dict_data_attribute.id AND addao.is_del=0
      where collect_dict_data_id = #{collect_dict_data_id} and collect_dict_data_attribute.is_del = 0 and is_component = 1

  </select>


<!-- /一对多的第一种写法，一般考虑到性能问题，不会这么实现 -->
    <resultMap type="dcc.domain.Teacher" id="teacherMap">  
        <id column="id" property="id"/>  
        <result column="name" property="name"/>  
        <collection property="students" ofType="dcc.domain.Student" column="id">  
            <id column="sid" property="id"/><!-- 这里的column对应的是下面查询的别名，而不是表字段名 -->  
            <result column="sname" property="name"/><!-- property对应JavaBean中的属性名 -->  
            <result column="className" property="className"/>  
        </collection>  
    </resultMap>  
      
      
    <!-- 查询所有的老师级各自的所有学生 -->  
    <select id="getTeachers" parameterType="dcc.domain.Teacher" resultMap="teacherMap">  
        SELECT  
            t.id,  
            t.NAME,  
            t.class_Name,  
            s.id AS sid,  
            s. NAME AS sname,  
            s.class_name as className  
        FROM  
            teacher t  
        LEFT JOIN student s ON t.id = s.teacher_id  
    </select>  
    
    <!-- //一对多的第二种写法/ -->
    <resultMap type="dcc.domain.Teacher" id="teacherMaps">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="class_name" property="className"/>
        <collection property="students" ofType="dcc.domain.Student" select="getStudents" column="{id=id,className=class_name}" >
        </collection>
    </resultMap>
 
 
    
  
    <!-- 查询所有的老师级各自的所有学生 -->
    <select id="getAllTeacher" parameterType="dcc.domain.Teacher" resultMap="teacherMaps">
        SELECT
        t.id,
        t.NAME,
        t.class_name
        FROM
        teacher t
    </select>
 
    <select id="getStudents"  resultType="dcc.domain.Student">
        select
        s.id,
        s. NAME,
        s.class_name as className
        from student s
        where teacher_id = #{id}
        and class_name = #{className}
    </select>

<collection property="attachmentList" ofType="PictureAttachmentOrder" column="id"   select="selectAttachmentByOrderId"></collection>

<!--订单图片及附件结果集-->
<resultMap type="PictureAttachmentOrder" id="PictureAndAttachmentOrderResult">
    <result property="id"    column="id"    />
    <result property="enclosureType"    column="enclosure_type"    />
    <result property="orderId"    column="order_id"    />
    <result property="enclosureUrl"    column="enclosure_url"    />
</resultMap>

<!--查询订单附件-->
<select id="selectAttachmentByOrderId" resultMap="PictureAndAttachmentOrderResult">
    SELECT
        id, order_id, enclosure_type, enclosure_url
    FROM
        picture_attachment_order
    WHERE
        order_id= #{id}
        and enclosure_type = 2
        and is_del = 0
</select>

修改日期格式  拼接字符

订单上线时间/订单发布时间

订单上线时间：order_start_time  订单开始时间(后台审核通过时间)

订单发布时间：insert_time  添加时间(客户操作)

SELECT order_start_time,insert_time,
    CONCAT_WS("/",IFNULL(date_format(order_start_time,'%Y-%m-%d'),"待上线")
    , date_format(insert_time ,'%Y-%m-%d')) AS a
FROM
    `order`

 无规则排序

 <!--热销产品-->
    <select id="getHotProductList"  resultMap="ProductListFormResult" >
( SELECT
   ep.uuid,
   ep.enterprise_product_name product_name,
   ep.brand,
   epi.img_url main_url
   FROM
      enterprise_product ep
      LEFT JOIN enterprise_product_img epi ON epi.enterprise_product_id = ep.id
   WHERE
      ep.hot_sort != 0
      AND ep.is_del = 0
      AND ep.audit_status = 1
      AND epi.is_del = 0
      AND epi.img_type = 0
   ORDER BY
      ep.hot_sort
      LIMIT 3
   ) UNION
   (
   SELECT
      *
   FROM
      (
      SELECT
         ep.uuid,
         ep.enterprise_product_name product_name,
         ep.brand,
         epi.img_url main_url
      FROM
         enterprise_product ep
         LEFT JOIN enterprise_product_img epi ON epi.enterprise_product_id = ep.id
      WHERE
         ep.hot_sort != 0
         AND ep.is_del = 0
         AND ep.audit_status = 1
         AND epi.is_del = 0
         AND epi.img_type = 0
      ORDER BY
         ep.hot_sort
         LIMIT 3,9
      ) AS nep
   ORDER BY
      RAND()  
   LIMIT 9
   )

重复查询提取

<sql id="selectBannerInfoVo">
        select id, banner_category, banner_location_category, banner_name, show_type, banner_img, banner_url, info_img, is_show, sort, insert_time, insert_person, renewal_time, update_person, is_del, del_time, sys_remarks from banner_info
    </sql>



<select id="selectBannerInfoList" parameterType="BannerInfo" resultMap="BannerInfoResult">
    <include refid="selectBannerInfoVo"/>
    <where>  
        <if test="id != null "> and id = #{id}</if>
         <if test="bannerCategory != null "> and banner_category = #{bannerCategory}</if>
         <if test="bannerLocationCategory != null "> and banner_location_category = #{bannerLocationCategory}</if>
         <if test="bannerName != null  and bannerName != '' "> and banner_name = #{bannerName}</if>
         <if test="showType != null "> and show_type = #{showType}</if>
         <if test="bannerImg != null  and bannerImg != '' "> and banner_img = #{bannerImg}</if>
         <if test="bannerUrl != null  and bannerUrl != '' "> and banner_url = #{bannerUrl}</if>
         <if test="infoImg != null  and infoImg != '' "> and info_img = #{infoImg}</if>
         <if test="isShow != null "> and is_show = #{isShow}</if>
         <if test="sort != null "> and sort = #{sort}</if>
         <if test="insertTime != null "> and insert_time = #{insertTime}</if>
         <if test="insertPerson != null  and insertPerson != '' "> and insert_person = #{insertPerson}</if>
         <if test="renewalTime != null "> and renewal_time = #{renewalTime}</if>
         <if test="updatePerson != null  and updatePerson != '' "> and update_person = #{updatePerson}</if>
         <if test="isDel != null "> and is_del = #{isDel}</if>
         <if test="delTime != null "> and del_time = #{delTime}</if>
         <if test="sysRemarks != null  and sysRemarks != '' "> and sys_remarks = #{sysRemarks}</if>
     </where>
</select>

 插入

 <insert id="insertBannerInfo" parameterType="BannerInfo">
      insert into banner_info
<trim prefix="(" suffix=")" suffixOverrides=",">
   <if test="id != null  ">id,</if>
   <if test="bannerCategory != null  ">banner_category,</if>
   <if test="bannerLocationCategory != null  ">banner_location_category,</if>
   <if test="bannerName != null  and bannerName != ''  ">banner_name,</if>
   <if test="showType != null  ">show_type,</if>
   <if test="bannerImg != null  and bannerImg != ''  ">banner_img,</if>
   <if test="bannerUrl != null  and bannerUrl != ''  ">banner_url,</if>
   <if test="infoImg != null  and infoImg != ''  ">info_img,</if>
   <if test="isShow != null  ">is_show,</if>
   <if test="sort != null  ">sort,</if>
   <if test="insertTime != null  ">insert_time,</if>
   <if test="insertPerson != null  and insertPerson != ''  ">insert_person,</if>
   <if test="renewalTime != null  ">renewal_time,</if>
   <if test="updatePerson != null  and updatePerson != ''  ">update_person,</if>
   <if test="isDel != null  ">is_del,</if>
   <if test="delTime != null  ">del_time,</if>
   <if test="sysRemarks != null  and sysRemarks != ''  ">sys_remarks,</if>
       </trim>
      <trim prefix="values (" suffix=")" suffixOverrides=",">
   <if test="id != null  ">#{id},</if>
   <if test="bannerCategory != null  ">#{bannerCategory},</if>
   <if test="bannerLocationCategory != null  ">#{bannerLocationCategory},</if>
   <if test="bannerName != null  and bannerName != ''  ">#{bannerName},</if>
   <if test="showType != null  ">#{showType},</if>
   <if test="bannerImg != null  and bannerImg != ''  ">#{bannerImg},</if>
   <if test="bannerUrl != null  and bannerUrl != ''  ">#{bannerUrl},</if>
   <if test="infoImg != null  and infoImg != ''  ">#{infoImg},</if>
   <if test="isShow != null  ">#{isShow},</if>
   <if test="sort != null  ">#{sort},</if>
   <if test="insertTime != null  ">#{insertTime},</if>
   <if test="insertPerson != null  and insertPerson != ''  ">#{insertPerson},</if>
   <if test="renewalTime != null  ">#{renewalTime},</if>
   <if test="updatePerson != null  and updatePerson != ''  ">#{updatePerson},</if>
   <if test="isDel != null  ">#{isDel},</if>
   <if test="delTime != null  ">#{delTime},</if>
   <if test="sysRemarks != null  and sysRemarks != ''  ">#{sysRemarks},</if>
       </trim>
  </insert>

/**
    * 批量删除banner配置
    * 
    * @param ids 需要删除的数据ID
    * @return 结果
    */
public int deleteBannerInfoByIds(String[] ids);

    <delete id="deleteBannerInfoByIds" parameterType="String">
        delete from banner_info where id in 
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

排序相关：

order by
if(ISNULL(sort),9999,sort) asc,
insert_time DESC

排序状态按照审核状态 

order by  case when mpep.factory_status  = 1 then 1
when  mpep.factory_status  = 2 then 2
when mpep.factory_status  = 0 then 99 end desc,
mpep.renewal_time desc

指定字段 指定值排序放在最后 enterprise_manager = 34 放在最后

ORDER BY
  FIELD(ebip.enterprise_manager,34 ),
    ebip.member_level DESC,
    
    ebip.vip_start_time ASC,
        
        ebip.id ASC,

    ebip.reg_time ASC

mysql 排序 固定值放在最前边

SELECT * FROM `zhangsan_db` ORDER BY `zhangsan` = 3 DESC;

数据表中zhangsan字段的值等于3的会排到前面

<resultMap type="EnterpriseInfoProviderDetail" id="EnterpriseInfoDetailResult">
    <result property="id"    column="id"    />
    <result property="uuid"    column="uuid"    />

    <collection property="bypassAccountList" ofType="BypassAccount" column="uuid" select="selectBypassAccountByUuid"></collection>
</resultMap>

<select id="selectBypassAccountByUuid" parameterType="string" resultType="BypassAccount">

select *from a where uuid = #{uuid}

</select>