1、入参
package com.ldygo.order.dao.mybatis.dto.custom;
import java.io.Serializable;
import java.util.List;
import cn.openlo.gear.dataobject.PersistentDTOSupport;
/**
* <p> Title: OrderReportQuery </p>
* <p> Description: 订单报表导出查询输入参数 </p>
* <p> Copyright: openlo.cn Copyright (C) 2017 </p>
*
* @author huangl
* @since 2017年5月23日 上午10:49:10
*/
public class OrderReportQuery extends PersistentDTOSupport implements Serializable {
private static final long serialVersionUID = 1L;
private String orderNo;
private List<String> orderStatus;
private String umName;
private int caroutMethod;
private int delayHour;
public String getOrderNo() {
return orderNo;
}
public void setOrderNo(String orderNo) {
this.orderNo = orderNo;
}
public List<String> getOrderStatus() {
return orderStatus;
}
public void setOrderStatus(List<String> orderStatus) {
this.orderStatus = orderStatus;
}
public String getUmName() {
return umName;
}
public void setUmName(String umName) {
this.umName = umName;
}
public int getCaroutMethod() {
return caroutMethod;
}
public void setCaroutMethod(int caroutMethod) {
this.caroutMethod = caroutMethod;
}
public int getDelayHour() {
return delayHour;
}
public void setDelayHour(int delayHour) {
this.delayHour = delayHour;
}
}
2、返回结果
package com.ldygo.order.dao.mybatis.dto.custom;
import java.io.Serializable;
import java.util.List;
import cn.openlo.gear.dataobject.PersistentDTOSupport;
/**
* <p> Title: OrderReportQueryResults </p>
* <p> Description: 订单报表导出结果 </p>
* <p> Copyright: openlo.cn Copyright (C) 2017 </p>
*
* @author huangl
* @since 2017年5月23日 上午10:49:10
*/
public class OrderReportQueryResults extends PersistentDTOSupport implements Serializable {
private static final long serialVersionUID = 1L;
private String orderNo;
private String orderStatus;
private String umName;
private int caroutMethod;
private int delayHour;
public String getOrderNo() {
return orderNo;
}
public void setOrderNo(String orderNo) {
this.orderNo = orderNo;
}
public String getOrderStatus() {
return orderStatus;
}
public void setOrderStatus(String orderStatus) {
this.orderStatus = orderStatus;
}
public String getUmName() {
return umName;
}
public void setUmName(String umName) {
this.umName = umName;
}
public int getCaroutMethod() {
return caroutMethod;
}
public void setCaroutMethod(int caroutMethod) {
this.caroutMethod = caroutMethod;
}
public int getDelayHour() {
return delayHour;
}
public void setDelayHour(int delayHour) {
this.delayHour = delayHour;
}
}
3、查询mapper接口
package com.ldygo.order.dao.mybatis.client.custom;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.ldygo.order.dao.mybatis.dto.OrderMaster;
import com.ldygo.order.dao.mybatis.dto.custom.OrderReportQuery;
import com.ldygo.order.dao.mybatis.dto.custom.OrderReportQueryResults;
import cn.openlo.dataobject.DAO;
/**
* <p> Title: OrderOutportReportMapper </p>
* <p> Description: 查询报表导出接口方法 </p>
* <p> Copyright: openlo.cn Copyright (C) 2017 </p>
*
* @author huangl
* @since 2017年5月23日 上午10:45:33
*/
public interface OrderReportQueryMapper extends DAO {
List<OrderReportQueryResults> queryOrderReport(OrderReportQuery params);
}
4、【OrderReportQueryMapper.xml】
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ldygo.order.dao.mybatis.client.custom.OrderReportQueryMapper">
<!-- 返回结果map与object的关系 -->
<resultMap id="OrderCouponResultMap" type="com.ldygo.order.dao.mybatis.dto.custom.OrderReportQueryResults" >
<result column="ORDER_NO" jdbcType="VARCHAR" property="orderNo" />
<result column="ORDER_STATUS" jdbcType="VARCHAR" property="orderStatus" />
<result column="UM_NAME" jdbcType="VARCHAR" property="umName" />
<result column="CAROUT_DELIVER_METHOD" jdbcType="VARCHAR" property="caroutMethod" />
<result column="DELAY_HOUR" jdbcType="VARCHAR" property="delayHour" />
</resultMap>
<!-- 接口名称,查询入参,返回结果 -->
<select id="queryOrderReport" parameterType="com.ldygo.order.dao.mybatis.dto.custom.OrderReportQuery" resultMap="OrderCouponResultMap">
select *
from order_master
<where>
<if test="orderStatus != null and orderStatus.size() > 0 ">
ORDER_STATUS in
<foreach collection="orderStatus" item="listItem" open="(" separator="," close=")" >
#{listItem}
</foreach>
</if>
<if test="orderNo != null">
and order_no = #{orderNo,jdbcType=VARCHAR}
</if>
<if test="caroutMethod != null">
and CAROUT_DELIVER_METHOD = #{caroutMethod,jdbcType=INTEGER}
</if>
<if test="delayHour != null">
and DELAY_HOUR = ${delayHour}
</if>
</where>
</select>
</mapper>
4.1
<if test="****java代码*****">
</if>
4.2
#{orderNo,jdbcType.INTEGER}
org.apache.ibatis.type.JdbcType.INTEGER
4.3
${orderNo}
5、测试
package com.ldygo.order.controller.test;
import java.util.List;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import com.alibaba.fastjson.JSON;
import com.google.common.collect.Lists;
import com.ldygo.order.dao.mybatis.client.custom.OrderReportQueryMapper;
import com.ldygo.order.dao.mybatis.dto.custom.OrderReportQuery;
import com.ldygo.order.dao.mybatis.dto.custom.OrderReportQueryResults;
import cn.openlo.gear.test.GearContextConfiguration;
import cn.openlo.gear.test.GearTestBase;
@GearContextConfiguration(boxName = "box01", boxHome = "${user.dir}/src/test/resources/box01/", gearName = "zuche-order-online", gearStartTimeout = 2000000)
public class OrderTaskTest extends GearTestBase {
@Autowired
private OrderReportQueryMapper orderOutportReportMapper;
@Test
public void getActRentDays() throws Exception {
OrderReportQuery params = new OrderReportQuery();
params.setOrderNo("20161225000047");
List<String> orderStatus = Lists.newArrayList();
orderStatus.add("S03");
orderStatus.add("s06");
params.setOrderStatus(orderStatus);
params.setCaroutMethod(1);
params.setDelayHour(0);
List<OrderReportQueryResults> bb = this.orderOutportReportMapper.queryOrderReport(params);
System.out.println("<<<<<<<<<<<<<<<<<<<");
System.out.println(JSON.toJSONString(bb));
}
}
6、注意#{}、${}的差别
select * from order_master where order_no = ?
11212121(String)
select * from order_mastet where order_no = '11212121'
7、执行sql语句
==> Preparing: select * from order_master WHERE ORDER_STATUS in ( ? , ? ) and order_no = ? and CAROUT_DELIVER_METHOD = ? and DELAY_HOUR = 0
==> Parameters: S03(String), s06(String), 20161225000047(String), 1(Integer)
<== Total: 1