Oracle转Pg中SQL语句中As别名字段大小写问题

[huangjianhua]

一段SQL语句:

			sql = " select (case when coalesce(max(TOTAL_PRICE),0) = 0 then 1 else max(TOTAL_PRICE) end) as max_TOTAL_PRICE, " +
                                " (case when coalesce(max(WEEK_CLICK),0) = 0 then 1 else max(WEEK_CLICK) end) as max_WEEK_CLICK, " +
                                " (case when coalesce(max(WEEK_NEWS),0) = 0 then 1 else max(WEEK_NEWS) end) as max_WEEK_NEWS, " +
                                " (case when coalesce(max(WEEK_ADD_BRAND),0) = 0 then 1 else max(WEEK_ADD_BRAND) end) as max_WEEK_ADD_BRAND, " +
                                " (case when coalesce(max(WEEK_MESSAGE),0) = 0 then 1 else max(WEEK_MESSAGE) end) as max_WEEK_MESSAGE " +
                                " from ENT_ACTIVE_RANK ";

注意SQL语句中 as 字段的别名,如: max_WEEK_MESSAGE,max_WEEK_ADD_BRAND ...等等..
下面是直接用了JdbcTemplate 的查询:

			JdbcTemplate jt = new JdbcTemplate(((DataSource) env.getApplicationContext().getBean("dataSource")));
                        Map map = jt.queryForMap(sql);

返回了一个Map对象,于是乎就在Map中根据这些别名的Key去取了,如:max_WEEK_MESSAGE,max_WEEK_ADD_BRAND ...等等..

结果打印出来一看,全是null,null...

然后循环打印下Map中的Key一看:

 === === = == = = == ==max_total_price
 === === = == = = == ==max_week_click
 === === = == = = == ==max_week_news
 === === = == = = == ==max_week_add_brand
 === === = == = = == ==max_week_message

全是小写的.
所以现在就知道原因了.

就将Pg的Sql语句中要用的别名,全部改成小写,如下:

			sql = " select (case when coalesce(max(TOTAL_PRICE),0) = 0 then 1 else max(TOTAL_PRICE) end) as max_total_price, " +
                                " (case when coalesce(max(WEEK_CLICK),0) = 0 then 1 else max(WEEK_CLICK) end) as max_week_click, " +
                                " (case when coalesce(max(WEEK_NEWS),0) = 0 then 1 else max(WEEK_NEWS) end) as max_week_news, " +
                                " (case when coalesce(max(WEEK_ADD_BRAND),0) = 0 then 1 else max(WEEK_ADD_BRAND) end) as max_week_add_brand, " +
                                " (case when coalesce(max(WEEK_MESSAGE),0) = 0 then 1 else max(WEEK_MESSAGE) end) as max_week_message " +
                                " from ENT_ACTIVE_RANK ";

一运行,结果全部有值.成功!

总结:
在Pg下面,别名的大小写,在输出别名的时候已经全部转成小写的了,所以在拿别名作Key的时候需要注意下的.

[chenchongqi]

sql最好都统一小写