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

雨点打透心脏的1/2处 2022-06-08 13:00 409阅读 0赞

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的一样.通过类点属性把它点出来,否则找不到.