id	summary	reporter	owner	description	type	status	priority	milestone	component	version	resolution	keywords	cc	due_date
106	Oracle转Pg中SQL语句中As别名字段大小写问题	huangjianhua	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的时候需要注意下的.


"	defect	closed	major	2012年6.0版本	管理后台	6.0	fixed	Pg中SQL语句中As别名字段大小写,数据库迁移		07/06/2012