MyBatis中动态SQL语句完成多条件查询,条件可以为字符串,数值,时间,以及自定义的类.
写的不好之处请指教~
MyBatis的XML:
<mapper namespace="com.dao.NotifyManagementInfoDao">
<resultMap type="com.model.NotifyManagementInfo" id="notifyManagementInfoResultMap">
<result column="streaming_no" property="streamingNo" />
<result column="id" property="id" />
<result column="id_type" property="idType" />
<result column="status" property="status" />
<result column="time" property="time" />
<result column="result_code" property="resultCode" />
</resultMap>
<select id="find" parameterType="NotifyManagementInfo" resultMap="notifyManagementInfoResultMap">
select * from notifymanagementinfo
where 1 = 1
<if test="notifyManagementInfo.streamingNo != null">
and streaming_no like concat('%',concat(#{notifyManagementInfo.streamingNo},'%'))
</if>
<if test="notifyManagementInfo.idType != -1">
and id_type = #{notifyManagementInfo.idType}
</if>
<if test="notifyManagementInfo.status != -1">
and status = #{notifyManagementInfo.status}
</if>
<if test="start != null">
and time > #{start,jdbcType=TIMESTAMP}
</if>
<if test="end != null">
and time < #{end,jdbcType=TIMESTAMP}
</if>
</select>
</mapper>
DAO层:
public List<NotifyManagementInfo> find(@Param("notifyManagementInfo")NotifyManagementInfo notifyManagementInfo,
@Param("start") Timestamp start,@Param("end") Timestamp end);
Service:
public List<NotifyManagementInfo> find(NotifyManagementInfo notifyManagementInfo,
String start,String end);
Impl:
public List<NotifyManagementInfo> find(
NotifyManagementInfo notifyManagementInfo, String start, String end) {
Timestamp startTime = new Timestamp(System.currentTimeMillis());
Timestamp endTime = new Timestamp(System.currentTimeMillis());
try {
if (start.length() != 0) {
//startTime = Timestamp.valueOf(start + " 00:00:00");
startTime = Timestamp.valueOf(start);
}else{
startTime = null;
}
if (end.length() != 0) {
//endTime = Timestamp.valueOf(end + " 00:00:00");
endTime = Timestamp.valueOf(end);
}else{
endTime = null;
}
} catch (Exception e) {
e.printStackTrace();
}
return notifyManagementInfoDao.find(notifyManagementInfo, startTime, endTime);
}
如果参数为String,dao中带参为@Param("test") String test,并且在MyBatis的XML中的<if test="test != null">即可;
如果参数为int,dao中带参为@Param("test") int test,并且在MyBatis的XML中的<if test="test != -1">即可;(如果在数据库中该参数有0,1,2,3...就是没有-1的值,取-1即可;如有-1取不可能数值就行)
如果参数为自定义类,dao中带参为:
public void alterResultCode(NotifyManagementInfo notifyManagementInfo);
这样在xml中可直接取自定义类的属性.如:
<insert id="add" parameterType="NotifyManagementInfo" >
insert into notifymanagementinfo(streaming_no,id,id_type,status,time,result_code)
values(#{streamingNo},#{id},#{idType},#{status},#{time},#{resultCode})
</insert>
但是如果参数有时间,要传入开始时间,结束时间,即:
public List<NotifyManagementInfo> find(@Param("notifyManagementInfo")NotifyManagementInfo notifyManagementInfo,
@Param("start") Timestamp start,@Param("end") Timestamp end);
XML就必须和上面的XML的一样.通过类点属性把它点出来,否则找不到.