浅谈动态sql的优雅之美

女爷i 2022-05-20 06:29 151阅读 0赞

习惯了ssh,一开始还有点不喜欢写sql，但在找工作的途中，让我充分认识到sql的重要性，这让我更加对ssm或者说手写sql是多么重要的的一件事。其它的不说了，分享下项目中的使用的sql,什么通用mapper就让成为过去吧！！

1.mapper里面的list查询方法

<select id="list" parameterType="xxxxx.AppLittleTodoVo"
    
    resultType="xxxxxx.AppLittleTodoEntity">
    select 
    <include refid="selectColumn"/>,c.cust_name as custName,c.main_mobile as mainMobile,
    p.property_name propertyName
    from t_app_little_todo as t
    LEFT JOIN t_crm_customer c on t.request_cust_code = c.cust_code
    left join t_crm_property p on p.property_code=t.request_property_code
    <trim prefix="WHERE" prefixOverrides="AND |OR ">
    <include refid="queryCondition"/>
    </trim> 
    </select>

2.查询字段

<sql id="selectColumn">
    t.id as id,
    t.request_cust_code as requestCustCode,
    t.request_property_code as requestPropertyCode,
    t.status as status,
    t.handle_cust_code as handleCustCode,
    DATE_FORMAT(t.handle_time,'%Y-%m-%d %H:%i:%s') as handleTime,
    t.remarks as remarks,
    DATE_FORMAT(t.operate_date,'%Y-%m-%d %H:%i:%s') as operateDate,
    t.operate_user as operateUser
    
    </sql>

3.动态加载查询条件，无需关注具体哪一个字段，前端传过来的是一个对象

<sql id="queryCondition">
    <if test="id != null and id != ''">
    AND t.id = #{id} 
    </if>
    <if test="requestCustCode != null and requestCustCode != ''">
    AND t.request_cust_code = #{requestCustCode} 
    </if>
    <if test="requestPropertyCode != null and requestPropertyCode != ''">
    AND t.request_property_code = #{requestPropertyCode} 
    </if>
    <if test="status != null and status == 0">
    AND t.status = #{status} 
    </if>
    <if test="status != null and status == 1">
    AND t.status > #{status} 
    </if>
    <if test="handleCustCode != null and handleCustCode != ''">
    AND t.handle_cust_code = #{handleCustCode} 
    </if>
    <if test="handleTime != null and handleTime != ''">
    AND t.handle_time = #{handleTime} 
    </if>
    <if test="remarks != null and remarks != ''">
    AND t.remarks = #{remarks} 
    </if>
    <if test="operateDate != null and operateDate != ''">
    AND t.operate_date = #{operateDate} 
    </if>
    <if test="operateUser != null and operateUser != ''">
    AND t.operate_user = #{operateUser} 
    </if>
    <if test="custCode != null and custCode != ''">
    AND (t.request_cust_code = #{custCode} or t.handle_cust_code = #{custCode})
    </if>
    </sql>

总结：在这里我要说一下，越来越多的项目中的借口，传的并不是一个固定的参数，而是一个可变参数（即对象），所以，通过不同的参数，可以获取想要的数据，同理，这种好处，这可以极大提高接口的利用率。