ibatis中mapper.xml编写

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