记一次springboot+mybatis+phoenix在代码集成中的坑
场景：
希望使用phoenix做查询服务，给服务端提供接口
设计：
通过springboot做restful的接口发布，通过mybatis做phoenix的sql处理，因此是springboot+mybatis+phoenix的集成方案；
但是会遇到一个坑!
=========================================我是华丽的分割线=====================================================
先简单汇报下场景：
做网约车的预约单查询处理，如图：
这里面需要使用一个时间窗口查询6种时间：推送订单时间、有效抢单时间、抢单成功时间、取消订单时间、达到目的地时间、支付时间
这里可能遇到一个问题：跨天支付！
因此为了解决跨天支付，需要在处理好的基表基础上做6次查询 ，分别对应不同的查询时间，然后做union处理，最后在求和（注意：就是在最后在求和的地方出错!!!!!）
先看下有问题的代码：
<select id="searchContext" resultType="com.df.entity.SearchInfo"> SELECT TB."driver_id", TB."driver_name"", TB."mobile", TB."alliance_name", TB."register_city", TB."driver_type", SUM(TB."pushOrderNum") AS "pushOrderNum" , SUM(TB."effectiveSlogan") AS "effectiveSlogan", SUM(TB."grab_sus_order") AS "grab_sus_order", SUM(TB."cancel_order_num") AS "cancel_order_num", SUM(TB."cancel_passenger_num") AS "cancel_passenger_num", SUM(TB."cancel_driver_num") AS "cancel_driver_num", SUM(TB."cancel_service_num") AS "cancel_service_num", SUM(TB."destination_reached_num") AS "destination_reached_num", SUM(TB."pay_num") AS "pay_num", SUM(TB."order_cumulative_num") AS "order_cumulative_num", SUM(TB."order_pay_cumulative_num") AS "order_pay_cumulative_num" FROM ( SELECT "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" , sum(to_number("pushOrderNum")) AS "pushOrderNum" , sum(0) AS "effectiveSlogan" , sum(0) AS "grab_sus_order" , sum(0) AS "cancel_order_num", sum(0) AS "cancel_passenger_num", sum(0) AS "cancel_driver_num", sum(0) AS "cancel_service_num" , sum(0) AS "destination_reached_num" , sum(0) AS "pay_num" , sum(0) AS "order_cumulative_num", sum(0) AS "order_pay_cumulative_num" FROM "_crontab_reservation" WHERE CASE WHEN "pushOrderTime" != '0' THEN TO_DATE("pushOrderTime",'yyyy-MM-dd') >= TO_DATE(#{start_time},'yyyy-MM-dd') AND TO_DATE("pushOrderTime",'yyyy-MM-dd') <= TO_DATE(#{end_time},'yyyy-MM-dd') ELSE 1=1 END <if test="driver_id != null and driver_id !=''" > and "driver_id"= #{driver_id} </if> <if test="driver_name != null and driver_name !=''" > and "driver_name" = #{driver_name} </if> <if test="mobile != null and mobile !=''" > and "mobile" = #{mobile} </if> <if test="alliance_name != null and alliance_name !=''" > and "driver_management_id" = #{alliance_name} </if> <if test="register_city != null and register_city !=''" > and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4) </if> <if test="driver_type != null and driver_type !=''" > and "driver_type" = #{driver_type} </if> GROUP BY "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" union all SELECT "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" , sum(0) AS "pushOrderNum" , sum(to_number("effectiveSlogan")) AS "effectiveSlogan" , sum(0) AS "grab_sus_order" , sum(0) AS "cancel_order_num", sum(0) AS "cancel_passenger_num", sum(0) AS "cancel_driver_num", sum(0) AS "cancel_service_num" , sum(0) AS "destination_reached_num" , sum(0) AS "pay_num" , sum(0) AS "order_cumulative_num", sum(0) AS "order_pay_cumulative_num" FROM "_crontab_reservation" WHERE CASE WHEN "_effective_glab_time" != '0' THEN TO_DATE("_effective_glab_time",'yyyy-MM-dd') >= TO_DATE(#{start_time},'yyyy-MM-dd') AND TO_DATE("_effective_glab_time",'yyyy-MM-dd') <= TO_DATE(#{end_time},'yyyy-MM-dd') ELSE 1=1 END <if test="driver_id != null and driver_id !=''" > and "driver_id"= #{driver_id} </if> <if test="driver_name != null and driver_name !=''" > and "driver_name" = #{driver_name} </if> <if test="mobile != null and mobile !=''" > and "mobile" = #{mobile} </if> <if test="alliance_name != null and alliance_name !=''" > and "driver_management_id" = #{alliance_name} </if> <if test="register_city != null and register_city !=''" > and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4) </if> <if test="driver_type != null and driver_type !=''" > and "driver_type" = #{driver_type} </if> GROUP BY "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" union all SELECT "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" , sum(0) AS "pushOrderNum" , sum(0) AS "effectiveSlogan" , sum(to_number("grab_sus_order")) AS "grab_sus_order" , sum(0) AS "cancel_order_num", sum(0) AS "cancel_passenger_num", sum(0) AS "cancel_driver_num", sum(0) AS "cancel_service_num" , sum(0) AS "destination_reached_num" , sum(0) AS "pay_num" , sum(0) AS "order_cumulative_num", sum(0) AS "order_pay_cumulative_num" FROM "_crontab_reservation" WHERE CASE WHEN "grab_sus_time" != '0' THEN TO_DATE("grab_sus_time" , 'yyyy-MM-dd') >= TO_DATE(#{start_time} , 'yyyy-MM-dd') AND TO_DATE("grab_sus_time" , 'yyyy-MM-dd') <= TO_DATE(#{end_time} , 'yyyy-MM-dd') ELSE 1=1 END <if test="driver_id != null and driver_id !=''" > and "driver_id"= #{driver_id} </if> <if test="driver_name != null and driver_name !=''" > and "driver_name" = #{driver_name} </if> <if test="mobile != null and mobile !=''" > and "mobile" = #{mobile} </if> <if test="alliance_name != null and alliance_name !=''" > and "driver_management_id" = #{alliance_name} </if> <if test="register_city != null and register_city !=''" > and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4) </if> <if test="driver_type != null and driver_type !=''" > and "driver_type" = #{driver_type} </if> GROUP BY "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" union all SELECT "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" , sum(0) AS "pushOrderNum" , sum(0) AS "effectiveSlogan" , sum(0) AS "grab_sus_order" , sum(to_number("cancel_order_num")) AS "cancel_order_num", sum(to_number("cancel_passenger_num")) AS "cancel_passenger_num", sum(to_number("cancel_driver_num")) AS "cancel_driver_num", sum(to_number("cancel_service_num")) AS "cancel_service_num" , sum(0) AS "destination_reached_num" , sum(0) AS "pay_num" , sum(0) AS "order_cumulative_num", sum(0) AS "order_pay_cumulative_num" FROM "_crontab_reservation" WHERE CASE WHEN "cancel_time" != '0' THEN TO_DATE("cancel_time",'yyyy-MM-dd') >= TO_DATE(#{start_time},'yyyy-MM-dd') AND TO_DATE("cancel_time",'yyyy-MM-dd') <= TO_DATE(#{end_time},'yyyy-MM-dd') ELSE 1=1 END <if test="driver_id != null and driver_id !=''" > and "driver_id"= #{driver_id} </if> <if test="driver_name != null and driver_name !=''" > and "driver_name" = #{driver_name} </if> <if test="mobile != null and mobile !=''" > and "mobile" = #{mobile} </if> <if test="alliance_name != null and alliance_name !=''" > and "driver_management_id" = #{alliance_name} </if> <if test="register_city != null and register_city !=''" > and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4) </if> <if test="driver_type != null and driver_type !=''" > and "driver_type" = #{driver_type} </if> GROUP BY "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" union all SELECT "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" , sum(0) AS "pushOrderNum" , sum(0) AS "effectiveSlogan" , sum(0) AS "grab_sus_order" , sum(0) AS "cancel_order_num", sum(0) AS "cancel_passenger_num", sum(0) AS "cancel_driver_num", sum(0) AS "cancel_service_num" , sum(to_number("destination_reached_num")) AS "destination_reached_num" , sum(0) AS "pay_num" , sum(0) AS "order_cumulative_num", sum(0) AS "order_pay_cumulative_num" FROM "_crontab_reservation" WHERE CASE WHEN "close_gps_time" != '0' THEN TO_DATE("close_gps_time",'yyyy-MM-dd') >= TO_DATE(#{start_time},'yyyy-MM-dd') AND TO_DATE("close_gps_time",'yyyy-MM-dd') <= TO_DATE(#{end_time},'yyyy-MM-dd') ELSE 1=1 END <if test="driver_id != null and driver_id !=''" > and "driver_id"= #{driver_id} </if> <if test="driver_name != null and driver_name !=''" > and "driver_name" = #{driver_name} </if> <if test="mobile != null and mobile !=''" > and "mobile" = #{mobile} </if> <if test="alliance_name != null and alliance_name !=''" > and "driver_management_id" = #{alliance_name} </if> <if test="register_city != null and register_city !=''" > and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4) </if> <if test="driver_type != null and driver_type !=''" > and "driver_type" = #{driver_type} </if> GROUP BY "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" union all SELECT "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" , sum(0) AS "pushOrderNum" , sum(0) AS "effectiveSlogan" , sum(0) AS "grab_sus_order" , sum(0) AS "cancel_order_num", sum(0) AS "cancel_passenger_num", sum(0) AS "cancel_driver_num", sum(0) AS "cancel_service_num" , sum(0) AS "destination_reached_num" , sum(to_number("pay_num")) AS "pay_num" , sum(to_number("order_cumulative_num")) AS "order_cumulative_num", sum(to_number("order_pay_cumulative_num")) AS "order_pay_cumulative_num" FROM "_crontab_reservation" WHERE CASE WHEN "pay_time" != '0' THEN TO_DATE("pay_time",'yyyy-MM-dd') >= TO_DATE(#{start_time},'yyyy-MM-dd') AND TO_DATE("pay_time",'yyyy-MM-dd') <= TO_DATE(#{end_time},'yyyy-MM-dd') ELSE 1=1 END <if test="driver_id != null and driver_id !=''" > and "driver_id"= #{driver_id} </if> <if test="driver_name != null and driver_name !=''" > and "driver_name" = #{driver_name} </if> <if test="mobile != null and mobile !=''" > and "mobile" = #{mobile} </if> <if test="alliance_name != null and alliance_name !=''" > and "driver_management_id" = #{alliance_name} </if> <if test="register_city != null and register_city !=''" > and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4) </if> <if test="driver_type != null and driver_type !=''" > and "driver_type" = #{driver_type} </if> GROUP BY "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" ) as TB GROUP BY TB."driver_id" , TB."driver_name" , TB."mobile" , TB."alliance_name" , TB."register_city" , TB."driver_type" </select>
这段代码大概就是：
select tb."driver_id" ... , sum(xxx1) , sum(xxx2) , ..... from ( A union B union C union D union E union F union G ) as tb group by tb."driver_id" ...
这种操作的问题就在于：使用mybatis做union后，生成了一张表tb ， 按照phoenix的官方说法是，phoenix严格区别大小写的，因此我们做完phoenix后，习惯性的在字段上加上双引号；
但是如果是使用mybatis做了集成后，union生成的表名称和字段名称，是不需要也不能在区分大小写的，因此在加双引号，就会出现字段找不到等一系列错误
解决后的代码：
<select id="searchContext" resultType="com.df.entity.SearchInfo"> SELECT "TB1".driver_id, "TB1".driver_name, "TB1".mobile, "TB1".alliance_name, "TB1".register_city, "TB1".driver_type, SUM("TB1".pushOrderNum) AS "pushOrderNum" , SUM("TB1".effectiveSlogan) AS "effectiveSlogan", SUM("TB1".grab_sus_order) AS "grab_sus_order", SUM("TB1".cancel_order_num) AS "cancel_order_num", SUM("TB1".cancel_passenger_num) AS "cancel_passenger_num", SUM("TB1".cancel_driver_num) AS "cancel_driver_num", SUM("TB1".cancel_service_num) AS "cancel_service_num", SUM("TB1".destination_reached_num) AS "destination_reached_num", SUM("TB1".pay_num) AS "pay_num", SUM("TB1".order_cumulative_num) AS "order_cumulative_num", SUM("TB1".order_pay_cumulative_num) AS "order_pay_cumulative_num" from (SELECT "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" , sum(to_number("pushOrderNum")) AS "pushOrderNum" , sum(0) AS "effectiveSlogan" , sum(0) AS "grab_sus_order" , sum(0) AS "cancel_order_num", sum(0) AS "cancel_passenger_num", sum(0) AS "cancel_driver_num", sum(0) AS "cancel_service_num" , sum(0) AS "destination_reached_num" , sum(0) AS "pay_num" , sum(0) AS "order_cumulative_num", sum(0) AS "order_pay_cumulative_num" FROM "_crontab_reservation" WHERE CASE WHEN "pushOrderTime" != '0' THEN TO_DATE("pushOrderTime",'yyyy-MM-dd') >= TO_DATE(#{start_time},'yyyy-MM-dd') AND TO_DATE("pushOrderTime",'yyyy-MM-dd') <= TO_DATE(#{end_time},'yyyy-MM-dd') ELSE 1=1 END <if test="driver_id != null and driver_id !=''"> and "driver_id"= #{driver_id} </if> <if test="driver_name != null and driver_name !=''"> and "driver_name" = #{driver_name} </if> <if test="mobile != null and mobile !=''"> and "mobile" = #{mobile} </if> <if test="alliance_name != null and alliance_name !=''"> and "driver_management_id" = #{alliance_name} </if> <if test="register_city != null and register_city !=''"> and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4) </if> <if test="driver_type != null and driver_type !=''"> and "driver_type" = #{driver_type} </if> GROUP BY "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" union all SELECT "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" , sum(0) AS "pushOrderNum" , sum(to_number("effectiveSlogan")) AS "effectiveSlogan" , sum(0) AS "grab_sus_order" , sum(0) AS "cancel_order_num", sum(0) AS "cancel_passenger_num", sum(0) AS "cancel_driver_num", sum(0) AS "cancel_service_num" , sum(0) AS "destination_reached_num" , sum(0) AS "pay_num" , sum(0) AS "order_cumulative_num", sum(0) AS "order_pay_cumulative_num" FROM "_crontab_reservation" WHERE CASE WHEN "_effective_glab_time" != '0' THEN TO_DATE("_effective_glab_time",'yyyy-MM-dd') >= TO_DATE(#{start_time},'yyyy-MM-dd') AND TO_DATE("_effective_glab_time",'yyyy-MM-dd') <= TO_DATE(#{end_time},'yyyy-MM-dd') ELSE 1=1 END <if test="driver_id != null and driver_id !=''"> and "driver_id"= #{driver_id} </if> <if test="driver_name != null and driver_name !=''"> and "driver_name" = #{driver_name} </if> <if test="mobile != null and mobile !=''"> and "mobile" = #{mobile} </if> <if test="alliance_name != null and alliance_name !=''"> and "driver_management_id" = #{alliance_name} </if> <if test="register_city != null and register_city !=''"> and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4) </if> <if test="driver_type != null and driver_type !=''"> and "driver_type" = #{driver_type} </if> GROUP BY "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" union all SELECT "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" , sum(0) AS "pushOrderNum" , sum(0) AS "effectiveSlogan" , sum(to_number("grab_sus_order")) AS "grab_sus_order" , sum(0) AS "cancel_order_num", sum(0) AS "cancel_passenger_num", sum(0) AS "cancel_driver_num", sum(0) AS "cancel_service_num" , sum(0) AS "destination_reached_num" , sum(0) AS "pay_num" , sum(0) AS "order_cumulative_num", sum(0) AS "order_pay_cumulative_num" FROM "_crontab_reservation" WHERE CASE WHEN "grab_sus_time" != '0' THEN TO_DATE("grab_sus_time" , 'yyyy-MM-dd') >= TO_DATE(#{start_time} , 'yyyy-MM-dd') AND TO_DATE("grab_sus_time" , 'yyyy-MM-dd') <= TO_DATE(#{end_time} , 'yyyy-MM-dd') ELSE 1=1 END <if test="driver_id != null and driver_id !=''"> and "driver_id"= #{driver_id} </if> <if test="driver_name != null and driver_name !=''"> and "driver_name" = #{driver_name} </if> <if test="mobile != null and mobile !=''"> and "mobile" = #{mobile} </if> <if test="alliance_name != null and alliance_name !=''"> and "driver_management_id" = #{alliance_name} </if> <if test="register_city != null and register_city !=''"> and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4) </if> <if test="driver_type != null and driver_type !=''"> and "driver_type" = #{driver_type} </if> GROUP BY "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" union all SELECT "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" , sum(0) AS "pushOrderNum" , sum(0) AS "effectiveSlogan" , sum(0) AS "grab_sus_order" , sum(to_number("cancel_order_num")) AS "cancel_order_num", sum(to_number("cancel_passenger_num")) AS "cancel_passenger_num", sum(to_number("cancel_driver_num")) AS "cancel_driver_num", sum(to_number("cancel_service_num")) AS "cancel_service_num" , sum(0) AS "destination_reached_num" , sum(0) AS "pay_num" , sum(0) AS "order_cumulative_num", sum(0) AS "order_pay_cumulative_num" FROM "_crontab_reservation" WHERE CASE WHEN "cancel_time" != '0' THEN TO_DATE("cancel_time",'yyyy-MM-dd') >= TO_DATE(#{start_time},'yyyy-MM-dd') AND TO_DATE("cancel_time",'yyyy-MM-dd') <= TO_DATE(#{end_time},'yyyy-MM-dd') ELSE 1=1 END <if test="driver_id != null and driver_id !=''"> and "driver_id"= #{driver_id} </if> <if test="driver_name != null and driver_name !=''"> and "driver_name" = #{driver_name} </if> <if test="mobile != null and mobile !=''"> and "mobile" = #{mobile} </if> <if test="alliance_name != null and alliance_name !=''"> and "driver_management_id" = #{alliance_name} </if> <if test="register_city != null and register_city !=''"> and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4) </if> <if test="driver_type != null and driver_type !=''"> and "driver_type" = #{driver_type} </if> GROUP BY "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" union all SELECT "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" , sum(0) AS "pushOrderNum" , sum(0) AS "effectiveSlogan" , sum(0) AS "grab_sus_order" , sum(0) AS "cancel_order_num", sum(0) AS "cancel_passenger_num", sum(0) AS "cancel_driver_num", sum(0) AS "cancel_service_num" , sum(to_number("destination_reached_num")) AS "destination_reached_num" , sum(0) AS "pay_num" , sum(0) AS "order_cumulative_num", sum(0) AS "order_pay_cumulative_num" FROM "_crontab_reservation" WHERE CASE WHEN "close_gps_time" != '0' THEN TO_DATE("close_gps_time",'yyyy-MM-dd') >= TO_DATE(#{start_time},'yyyy-MM-dd') AND TO_DATE("close_gps_time",'yyyy-MM-dd') <= TO_DATE(#{end_time},'yyyy-MM-dd') ELSE 1=1 END <if test="driver_id != null and driver_id !=''"> and "driver_id"= #{driver_id} </if> <if test="driver_name != null and driver_name !=''"> and "driver_name" = #{driver_name} </if> <if test="mobile != null and mobile !=''"> and "mobile" = #{mobile} </if> <if test="alliance_name != null and alliance_name !=''"> and "driver_management_id" = #{alliance_name} </if> <if test="register_city != null and register_city !=''"> and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4) </if> <if test="driver_type != null and driver_type !=''"> and "driver_type" = #{driver_type} </if> GROUP BY "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" union all SELECT "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" , sum(0) AS "pushOrderNum" , sum(0) AS "effectiveSlogan" , sum(0) AS "grab_sus_order" , sum(0) AS "cancel_order_num", sum(0) AS "cancel_passenger_num", sum(0) AS "cancel_driver_num", sum(0) AS "cancel_service_num" , sum(0) AS "destination_reached_num" , sum(to_number("pay_num")) AS "pay_num" , sum(to_number("order_cumulative_num")) AS "order_cumulative_num", sum(to_number("order_pay_cumulative_num")) AS "order_pay_cumulative_num" FROM "_crontab_reservation" WHERE CASE WHEN "pay_time" != '0' THEN TO_DATE("pay_time",'yyyy-MM-dd') >= TO_DATE(#{start_time},'yyyy-MM-dd') AND TO_DATE("pay_time",'yyyy-MM-dd') <= TO_DATE(#{end_time},'yyyy-MM-dd') ELSE 1=1 END <if test="driver_id != null and driver_id !=''"> and "driver_id"= #{driver_id} </if> <if test="driver_name != null and driver_name !=''"> and "driver_name" = #{driver_name} </if> <if test="mobile != null and mobile !=''"> and "mobile" = #{mobile} </if> <if test="alliance_name != null and alliance_name !=''"> and "driver_management_id" = #{alliance_name} </if> <if test="register_city != null and register_city !=''"> and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4) </if> <if test="driver_type != null and driver_type !=''"> and "driver_type" = #{driver_type} </if> GROUP BY "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" ) AS "TB1" GROUP BY "TB1".driver_id , "TB1".driver_name , "TB1".mobile , "TB1".alliance_name , "TB1".register_city , "TB1".driver_type </select>
