在公司工作有一段时间了,用的是SpringMVC框架,对于一个刚刚毕业不久的新人而言,一切都是新奇的,所以,就将一些自己认为值得记录的东西记录下来,供以后需要的时候使用,SpringMVC的实现类几乎是用Sql执行出来的,灵活性很强,当然也存在一定的规范:
1、最好封装好查询实体,把实体类中的属性和数据库中的属性都一一对照起来——————entitySql
2、封装一个查询sql,把所有的实体都通过if语句进行编写,要怎么查询就怎么查询,这样哪怕传入一个null参数,也会自动过滤掉——————querySql
3、最后在定义的实现方法sql块中引用entitySql和querySql就可以方便的使用了。
<!-- 根据条件查询 -->
<select useCache="false" id="findByProperty" resultType="..........entity.Notice">
SELECT
<include refid="entitySql"/>
FROM SS_NOTICE
<include refid="querySql"/>
</select>
<sql id="entitySql">
NOTICEID as noticeid,
PROID as proid,
NOTICE_NAME as noticeName,
START_TIME as startTime,
END_TIME as endTime,
NOTICE_TYPE as noticeType,
REMARK as remark,
STATUS as status,
CREATE_USER as createUser,
CREATE_TIME as createTime,
UPDATE_TIME as updateTime,
UPDATE_USER as updateUser,
MREALNAME as mrealname,
MMOBILE as mmobile,
AUCREALNAME as aucrealname,
AUCMOBILE as aucmobile,
PROCESS_INS_ID as processInsId,
COURTID as courtid,
COURT_NAME as courtName,
MEET_STIME as meetStime,
SALE_WAY as saleWay,
START_RESHOW as startReshow,
END_RESHOW as endReshow,
ADDRESS_RESHOW as addressReshow,
END_BAILTIME as endBailtime,
PROCESS_TIME as processTime,
AUCTION_ID as auctionId,
AUCTION_USERNAME as auctionUsername
</sql>
<sql id="querySql">
WHERE 1= 1 and STATUS != '-1'
<if test="noticeid != null and noticeid != ''">
and NOTICEID = #{noticeid}
</if>
<if test="proid != null and proid != ''">
and PROID = #{proid}
</if>
<if test="noticeName != null and noticeName != ''">
and NOTICE_NAME like concat(concat('%', #{noticeName}),'%')
</if>
<if test="noticeType != null and noticeType != ''">
<choose>
<when test="@com.zbxsoft.sszc.common.Ognl@isArray(noticeType)">
and NOTICE_TYPE in
<foreach collection="noticeType" item="s" open="(" separator="," close=")">
#{s}
</foreach>
</when>
<otherwise>
and NOTICE_TYPE = #{noticeType}
</otherwise>
</choose>
</if>
...
...
...
...
<if test="processTime != null and processTime != ''">
and PROCESS_TIME = #{processTime}
</if>
<if test="auctionId != null and auctionId != ''">
and AUCTION_ID = #{auctionId}
</if>
<if test="auctionUsername != null and auctionUsername != ''">
and AUCTION_USERNAME = #{auctionUsername}
</if>
<if test="searchFlag == 1"><!-- 今日发布公告 -->
and TO_CHAR(Sysdate, 'YYYY-MM-DD')=substr(START_TIME,0,10)
</if>
<if test="searchFlag == 2"><!-- 公告期内 -->
and STATUS = '6'
<![CDATA[ and substr(START_TIME,0,10)<=TO_CHAR(Sysdate, 'YYYY-MM-DD') ]]>
<![CDATA[ and substr(END_TIME,0,10)>=TO_CHAR(Sysdate, 'YYYY-MM-DD') ]]>
</if>
<if test="searchFlag == 3"><!-- 待发布公告 -->
<![CDATA[ and (STATUS in('1','2','3','4','5') or (status='6' and sysdate<=to_date(start_time,'YYYY-MM-DD'))) ]]>
</if>
order by START_TIME desc
</sql>