Mybatis-Plus自定义SQL实现分页及模糊查询

1、Mapper.java及Mapper.xml

 - Mapper.java
	//RechargeLogList  为自定义查询结果集对象
	List<RechargeLogList> getRechargePageByUserId(Pagination page, 
			@Param("userId")Integer userId,
			@Param("orderNo")String orderNo);
			

 - Mapper.xml
 	<!--消费明细详情 -->
    <select id="getRechargePageByUserId" resultType="com.ptw.pojo.diy.RechargeLogList">
		SELECT
			r.userid AS userId,
			s.name AS channelName,
			m.mobile AS mobile,
			m.username AS userName,
			r.order_no AS orderNo,
			r.recharge_money AS orderMoney,
			r.recharge_time AS rechargeTime,
			r.discount_amount AS discountAmount,
			r.refund_amount AS refundAmount,
			r.recharge_money AS rechargeMoney 
		FROM
			recharge_log r
			LEFT JOIN sell_channel s ON r.sell_channel_id = s.id
			LEFT JOIN member_base m ON r.userid = m.userid 
		<where>
			1=1 AND r.userid = #{userId}
			<if test="orderNo!=null and orderNo!='' ">
				AND r.order_no like '%${orderNo}%'
			</if>
		</where>
		ORDER BY
			r.recharge_time DESC  
    </select>
 

2、ServiceImpl.java

	@Autowired
	private RechargeLogMapper rechargeLogMapper;
	
	@Override
	public Page<RechargeLogList> getRechargePageByUserId(Page<RechargeLogList> page, 
			Integer userId,String orderNo) {
		return page.setRecords(rechargeLogMapper.getRechargePageByUserId(page, userId ,orderNo));
	}

3、Controller.java

	/**
	 * 消费明细详情
	 * RechargeLogListForm 为传递参数对象
	 */
	@RequestMapping(value = "recharge_log/consumption_details")
	public String test(RechargeLogListForm rechargeLogListForm,ModelMap model, HttpServletRequest request,Integer userId) {
		//System.out.println("---------------userId:"+userId);
		int pageNum = (int) rechargeLogListForm.getPg().getPageNum();
		int pageSize = (int) rechargeLogListForm.getPg().getSize();
		Page<RechargeLogList> page =  rechargeLogService.getRechargePageByUserId(
				new Page<RechargeLogList>(pageNum, pageSize),userId,
				rechargeLogListForm.getOrderNo());
		PageResult pageResult = Pageresultutils.conver(page);
		pageOper(model, pageResult);
		model.addAttribute("rechargeLogListForm", rechargeLogListForm);
		return "byk/recharge_log/consumption_details";
	}

4、RechargeLogList.java 和 RechargeLogListForm.java 结构一样

@Data
public class RechargeLogList implements Serializable{
	private static final long serialVersionUID = 1L;
	 
	private Integer userId; //用户id
	private String channelName;  //渠道
	private String mobile; //账号
	private String userName; //用户名
	
	private String orderNo; //订单编号
	private double orderMoney; //订单金额
	private String rechargeTime; //消费时间
	private double discountAmount; //优惠金额
	private double refundAmount; //订单退款
	
	private double rechargeMoney; //消费金额

}