MyBatis——特殊传参问题小结

近期在写系统报表API的时候遇到MyBatis中的一些特殊写法:

1. 传入两个参数(一般情况下我们更多的是传入一个对象或者map)

public List<MarketVehicleModel> selectVehicleByMarketAndDealer(String marketActivityId,String dealerId);

　其对应的xml写法不能想当然地写成:

<select id="selectVehicleByMarketAndDealer" parameterType="java.lang.String" resultType="bz.sunlight.entity.MarketVehicleModel">
    SELECT DISTINCT mvm.vehicle_model_id as vehicleModelId,mvm.vehicle_model_code as vehicleModelCode,mvm.vehicle_model_name as vehicleModelName
    from vehicle v,vehicle_model vm,Market_Vehicle_Model mvm where v.vehicle_model_id = vm.id and mvm.vehicle_model_id = vm.id
    and mvm.market_activity_id = #{marketActivityId} and v.dealer_id=#{dealerId} 
</select>

　这样写，在编译打包期间不会报什么错，但到正式运行的时候会报告匹配不到 marketActivityId 和 dealerId . 
正确的写法应该是:

 <select id="selectVehicleByMarketAndDealer" parameterType="java.lang.String" resultType="bz.sunlight.entity.MarketVehicleModel">
    SELECT DISTINCT mvm.vehicle_model_id as vehicleModelId,mvm.vehicle_model_code as vehicleModelCode,mvm.vehicle_model_name as vehicleModelName
    from vehicle v,vehicle_model vm,Market_Vehicle_Model mvm where v.vehicle_model_id = vm.id and mvm.vehicle_model_id = vm.id
    and mvm.market_activity_id = #{0} and v.dealer_id=#{1}
</select>

2. 上面的修改虽然暂时解决了可行性的问题，但这种写法并不可靠，如果遇到对两个参数进行条件判断的情况，还是会有问题，比如:

  <select id="getSalesVolumeRanking" parameterType="java.lang.String" 
      resultType="java.util.HashMap">
    SELECT sale_consultant_id AS consultantId,sale_consultant_name AS consultantName,COUNT(*) AS salesVolume
    FROM sales_record 
    <where> 
        <if test="0 != null">
            vehicle_model_id = #{0}
        </if> 
        <if test="1 != null">
            AND dealer_id = #{1}
        </if> 
    </where>
    GROUP BY sale_consultant_id
    ORDER BY salesVolume DESC
  </select>

　这样的写法显然是不对的，这里有两种解决方案:

　　　(1) 将多个参数组合成map后传入

    List<HashMap<String,Object>> getSalesVolumeRanking(HashMap<String, String> map);

  <select id="getSalesVolumeRanking" parameterType="java.util.HashMap" 
      resultType="java.util.HashMap">
    SELECT sale_consultant_id AS consultantId,sale_consultant_name AS consultantName,COUNT(*) AS salesVolume
    FROM sales_record 
    <where> 
        <if test="vehicleModelId != null">
            vehicle_model_id = #{vehicleModelId}
        </if> 
        <if test="dealerId != null">
            AND dealer_id = #{dealerId}
        </if> 
    </where>
    GROUP BY sale_consultant_id
    ORDER BY salesVolume DESC
  </select>

　　(2) 给参数加@Param注解

List<HashMap<String,Object>> getSalesVolumeRanking(@Param("vehicleModelId") String vehicleModelId,@Param("dealerId") String dealerId);

  <select id="getSalesVolumeRanking" parameterType="java.lang.String" 
      resultType="java.util.HashMap">
    SELECT sale_consultant_id AS consultantId,sale_consultant_name AS consultantName,COUNT(*) AS salesVolume
    FROM sales_record 
    <where> 
        <if test="vehicleModelId != null">
            vehicle_model_id = #{vehicleModelId}
        </if> 
        <if test="dealerId != null">
            AND dealer_id = #{dealerId}
        </if> 
    </where>
    GROUP BY sale_consultant_id
    ORDER BY salesVolume DESC
  </select>

3. 传入单个参数，但涉及到对该参数的条件判断:

    List<HashMap<String,Object>> getSatisfactionDegree(String vehicleModelId);

  <select id="getSatisfactionDegree" parameterType="java.lang.String"
      resultType="java.util.HashMap">
    SELECT sale_consultant_id AS consultantId,ROUND(AVG(score)) satisfactionDegree
    FROM reward_record 
    <if test="vehicleModelId != null and vehicleModelId != ''">
      where vehicle_model_id = #{vehicleModelId}
    </if> 
    GROUP BY sale_consultant_id
  </select>

　　这种写法在运行时会报告There is no getter for property named 'vehicleModelId' 这时可将vehicleModelId换成关键字_parameter:

  <select id="getSatisfactionDegree" parameterType="java.lang.String"
      resultType="java.util.HashMap">
    SELECT sale_consultant_id AS consultantId,ROUND(AVG(score)) satisfactionDegree
    FROM reward_record 
    <if test="_parameter != null and _parameter != ''">
      where vehicle_model_id = #{_parameter}
    </if> 
    GROUP BY sale_consultant_id
  </select>

 4. 自定义order/group by clause或者传入int型参数时需将#{parameterName}引用改为${parameterName} :

   HashMap<String, String> parameterMap = new HashMap<String, String>();
   parameterMap.put("marketActivityCode", marketActivity.getCode());
   parameterMap.put("dealerId", dealer.getId());
   parameterMap.put("groupByClause","level_of_intent");
   List<HashMap<String,Object>> currentIntentionCustomers = intentionCustomerMapper.getIntentionCustomerNumWithVehicle(parameterMap);

  <select id="getIntentionCustomerNumWithVehicle" parameterType="java.util.HashMap" 
      resultType="java.util.HashMap">
    SELECT c.level_of_intent,c.Level_Of_Intent_First,m.vehicle_model_id,m.vehicle_model_code,m.vehicle_model_name,COUNT(*) AS num
    FROM intention_customer c,intention_models m
    WHERE c.id = m.intention_customer_id
    AND c.dealer_id = #{dealerId}
    AND m.market_activity_code = #{marketActivityCode}
    <if test="groupByClause != null">
        GROUP BY ${groupByClause},vehicle_model_id
    </if>
  </select>

　　如果继续使用#{groupByClause}会使得执行的sql变成GROUP BY "level_of_intent" 导致得不到正确的分组结果.