MyBatis对日期类型数据的处理

       近日项目上遇到需求：查询某一时间段的数据（数据库中存在字段createTime，类型为date）。编写Mybatis的Mapper文件：

	<!-- public List<VadtaxShow> findList(VadtaxShow vadtaxShow); -->
	<select id="findList" parameterType="com.cdqyzj_WC.Util.Pageshow.VadtaxShow" resultType="com.cdqyzj_WC.Util.Pageshow.VadtaxShow">
    SELECT SUM(t.totalSales),SUM(t.outputTax),SUM(t.inputTax),SUM(t.entryAmount),SUM(t.amountTax),SUM(t.retentionTax),t.createTime,t.taxTime,t.comId,c.comName,c.comType
    FROM t_g_vaddedtax AS t JOIN t_ucompany AS c ON c.comId = t.comId
	<where>
	1=1
			<if test="comType != '' and comType != null"> and c.comType = #{comType}</if>
			<if test="taxTime != null and taxTime != ''"> and t.taxTime =#{taxTime} </if>	
			<if test="comId != null and comId != ''"> and t.comId =#{comId} </if>	
			<if test="start_times != '' and start_times != null and end_times != '' and end_times != null">
				and t.createTime BETWEEN ${start_times} AND ${end_times}
			</if>
			<if test="orderBy != null and orderType != '' ">
				 	order by ${orderBy}  ${orderType}
			</if>
			<if test="pageSize != 0 ">
					limit ${startRows},${pageSize}
			</if>
	</where>
	</select>

start_times,end_times两个属性都为Java.sql.date类型，数据库中字段createTime为Date类型，start_times/end_times都不为空，执行mapper对应方法，出现如下报错信息：

09:57:07 [http-nio-80-exec-10] ERROR c.c.a.c.exception.BDExceptionHandler - nested         
                         exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.lang.IllegalArgumentException: invalid 
                         comparison: java.sql.Date and java.lang.String
### Cause: java.lang.IllegalArgumentException: invalid comparison: java.sql.Date and 
                         java.lang.String
...

显然，出现的问题是mapper文件中将某个Java.sql.date类型的数据进行了String类型转换。经过一番排查，终于找到原因：在if判断时使用了这么一句：“start_times ！= ‘’ ”和“end_times != '' ”，这两个判断使mapper试着去将start_times和end_times转换成String类型然后与“ ‘’ ”进行比较，然后报类型转换出错。更正代码：

	<!-- public List<VadtaxShow> findList(VadtaxShow vadtaxShow); -->
	<select id="findList" parameterType="com.cdqyzj_WC.Util.Pageshow.VadtaxShow" resultType="com.cdqyzj_WC.Util.Pageshow.VadtaxShow">
    SELECT SUM(t.totalSales),SUM(t.outputTax),SUM(t.inputTax),SUM(t.entryAmount),SUM(t.amountTax),SUM(t.retentionTax),t.createTime,t.taxTime,t.comId,c.comName,c.comType
    FROM t_g_vaddedtax AS t JOIN t_ucompany AS c ON c.comId = t.comId
	<where>
	1=1
			<if test="comType != '' and comType != null"> and c.comType = #{comType}</if>
			<if test="taxTime != null and taxTime != ''"> and t.taxTime =#{taxTime} </if>	
			<if test="comId != null and comId != ''"> and t.comId =#{comId} </if>	
			<if test="start_times != null and end_times != null">
				<!-- and t.createTime BETWEEN #{start_times} AND #{end_times} -->
				<![CDATA[ and DATE_FORMAT(t.createTime, '%Y-%m-%d') BETWEEN DATE_FORMAT(#{start_times}, '%Y-%m-%d') AND DATE_FORMAT(#{end_times}, '%Y-%m-%d') ]]>
			</if>
			<if test="orderBy != null and orderType != '' ">
				 	order by ${orderBy}  ${orderType}
			</if>
			<if test="pageSize != 0 ">
					limit ${startRows},${pageSize}
			</if>
	</where>
	</select>

执行通过。

最后，附上本人学习MyBatis时做的《Spring整合MyBatis学习编程代码》：https://download.youkuaiyun.com/download/txd2016_5_11/10596835

与君共勉。