MyBatis中动态SQL语句完成多条件查询

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 &gt; #{start,jdbcType=TIMESTAMP}
   </if>
   <if test="end != null">
      and  time  &lt;  #{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的一样.通过类点属性把它点出来,否则找不到.