mybatis @Select注解中如何拼写动态sql

逃离我推掉我的手 2022-06-07 12:10 204阅读 0赞

package cn.erongcai.hrplatform.dao.demand;
    
    import java.util.List;
    
    import org.apache.ibatis.annotations.Mapper;
    import org.apache.ibatis.annotations.Param;
    import org.apache.ibatis.annotations.Select;
    
    import com.baomidou.mybatisplus.mapper.BaseMapper;
    
    import cn.erongcai.hrplatform.model.demand.DemandComment;
    
    @Mapper
    public interface DemandCommentMapper extends BaseMapper<DemandComment>{
    	
    	@Select("SELECT "
    			+ "a.id as 'id',a.create_date as 'createDate',a.content as 'content',"
    			+ "a.parent_id as 'parentId',a.first_comment_id as 'firstCommentId',"
    			+ "b.id as 'fromUser.id',b.realname as 'fromUser.realname',b.avatar as 'fromUser.avatar',"
    			+ "c.id as 'toUser.id',c.realname as 'toUser.realname',c.avatar as 'toUser.avatar' "
    			+ "FROM t_demand_comment a "
    			+ "LEFT JOIN t_user b ON b.id = a.from_uid "
    			+ "LEFT JOIN t_user c ON c.id = a.to_uid "
    			+ "WHERE a.demand_id = #{demandId} "
    			+ "ORDER BY a.create_date ASC"
    			+ "LIMIT #{startNo},#{pageSize}")
    	public List<DemandComment> listDemandComment(@Param("demandId") Long demandId, @Param("startNo") Integer pageNo, @Param("pageSize") Integer pageSize);
    }

这样整个语句是写死的,如果我想根据pageNo与pageSize是否为空来判断是否需要分页,该怎么做呢?

如果使用xml来配置的话可以用

<when test='startNo!=null and pageSize != null '>
      LIMIT #{startNo},#{pageSize}
    </when>

如果是用@Select 这种该如何做呢？

方法:用script标签包围，然后像xml语法一样书写

package cn.erongcai.hrplatform.dao.demand;
    
    import java.util.List;
    
    import org.apache.ibatis.annotations.Mapper;
    import org.apache.ibatis.annotations.Param;
    import org.apache.ibatis.annotations.Select;
    
    import com.baomidou.mybatisplus.mapper.BaseMapper;
    
    import cn.erongcai.hrplatform.model.demand.DemandComment;
    
    @Mapper
    public interface DemandCommentMapper extends BaseMapper<DemandComment>{
    	
    	@Select("<script>"
    			+ "SELECT "
    			+ "a.id as 'id',a.create_date as 'createDate',a.content as 'content',"
    			+ "a.parent_id as 'parentId',a.first_comment_id as 'firstCommentId',"
    			+ "b.id as 'fromUser.id',b.realname as 'fromUser.realname',b.avatar as 'fromUser.avatar',"
    			+ "c.id as 'toUser.id',c.realname as 'toUser.realname',c.avatar as 'toUser.avatar' "
    			+ "FROM t_demand_comment a "
    			+ "LEFT JOIN t_user b ON b.id = a.from_uid "
    			+ "LEFT JOIN t_user c ON c.id = a.to_uid "
    			+ "WHERE a.demand_id = #{demandId} "
    			+ "ORDER BY a.create_date ASC "
    			+ "<if test='startNo!=null and pageSize != null '>"
    			+ "LIMIT #{startNo},#{pageSize}"
    			+ "</if>"
    			+ "</script>")
    	public List<DemandComment> listDemandComment(@Param("demandId") Long demandId, @Param("startNo") Integer pageNo, @Param("pageSize") Integer pageSize);
    }