关于mybatis传空值的处理

 关于mybatis传空值的处理：

 如果不做空处理在是null的时候就不会转化成数据库的无意义的null，直接存入null后面由于类型吧匹配错误报错

 1，mybatis-config.xml中设置

 

 <configuration>

 

 <settings>

        <setting name="callSettersOnNulls" value="true"/>  

        <setting name="jdbcTypeForNull" value="NULL" />

        <setting name="logImpl" value="STDOUT_LOGGING"/>

</settings>

 

</configuration>

2，具体sql.xml中用select用if判断（其实也不用第一步已经设置好了，为null空的时候会自动处理成""什么都没有），insert，update等不用因为第一步设置了

<select id="onLineFirmList" parameterType="map" resultType="map">

  select a.customer_key,a.customer_id,a.customer_name

,nvl(b.login_num,0) login_num,nvl(c.obj_sale_num,0) obj_sale_num,nvl(c.obj_buy_num,0) obj_buy_num

,nvl(d.ord_sale_num,0) ord_sale_num,nvl(d.ord_buy_num,0) ord_buy_num

from tb_cus_firm a

left join (select customer_key,count(1) login_num from TB_ACCESS_RECORD where url like '%/login%' group by customer_key) b on a.customer_key=b.customer_key

left join (select customer_key,sum(decode(CONTRADE_TYPE,'A',1,0)) obj_sale_num,sum(decode(CONTRADE_TYPE,'B',1,0)) obj_buy_num from tb_con_obj group by customer_key) c on a.customer_key=c.customer_key

left join (select t2.customer_key,sum(decode(t3.CONTRADE_TYPE,'A',1,0)) ord_sale_num,sum(decode(t3.CONTRADE_TYPE,'B',1,0)) ord_buy_num

from tb_pact_cnt t1,tb_con_ord t2,tb_con_obj t3

where t1.ord_key=t2.ord_key and t2.conobj_key=t3.conobj_key and t1.pact_type='B'

group by t2.customer_key) d on a.customer_key=d.customer_key where 1=1

 <if test="customerId !=null and customerId !=''">

 and a.customer_id like '%${customerId}%' ///like是不能用#

 </if>

 <if test="customerName !=null and customerName !=''">

 and a.customer_name like '%${customerName}%'

 </if>

  </select>

 

打印出来的sql：

  SELECT count(*) FROM (SELECT a.customer_key, a.customer_id, a.customer_name, nvl(b.login_num, 0) login_num, nvl(c.obj_sale_num, 0) obj_sale_num, nvl(c.obj_buy_num, 0) obj_buy_num,

  nvl(d.ord_sale_num, 0) ord_sale_num, nvl(d.ord_buy_num, 0) ord_buy_num FROM tb_cus_firm a LEFT JOIN (SELECT customer_key, count(1) login_num 

  FROM TB_ACCESS_RECORD WHERE url LIKE '%/login%' GROUP BY customer_key) b ON a.customer_key = b.customer_key LEFT JOIN (SELECT customer_key, 

  sum(decode(CONTRADE_TYPE, 'A', 1, 0)) obj_sale_num, sum(decode(CONTRADE_TYPE, 'B', 1, 0)) obj_buy_num FROM tb_con_obj GROUP BY customer_key) 

  c ON a.customer_key = c.customer_key LEFT JOIN (SELECT t2.customer_key, sum(decode(t3.CONTRADE_TYPE, 'A', 1, 0)) ord_sale_num, sum(decode(t3.CONTRADE_TYPE, 'B', 1, 0)) 

  ord_buy_num FROM tb_pact_cnt t1, tb_con_ord t2, tb_con_obj t3 WHERE t1.ord_key = t2.ord_key AND t2.conobj_key = t3.conobj_key AND t1.pact_type = 'B' GROUP BY t2.customer_key) 

  d ON a.customer_key = d.customer_key WHERE 1 = 1 AND a.customer_id LIKE '%%' AND a.customer_name LIKE '%%') table_count