浅谈动态sql的优雅之美

本文参考自: 原文地址

       习惯了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>

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