db2jcc4.jar无法获取通过别名查询结果的值？原因何在？！！！

先上结论：最简单的办法就是换成db2jcc.jar驱动即可！

若不想换呢？那继续看啊。。。

db2jcc.jar用于jdbc3.0，db2jcc4.jar用于jdbc4.0，而IBM对于jdbc4.0的规范进行了修改！！！

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

DB2的 jdbc驱动升级到V4后使用 ResultSetMetaData.getColumnName不能获取列的别名的问题

最近把db2的jdbc驱动升级到了 Version4.0， 遇到下面的问题：

以前的代码中通过ResultSetMetaData.getColumnName来获取查询语句列的别名，更新后获取的是列的名字。

经查是因为IBM根据jdbc的规范进行了相关修改， 官方信息和解决方案如下：

 

ResultSetMetaData returns different values for the IBM Data Server Driver for JDBC and SQLJ Version 4.0

For the IBM® Data Server Driver for JDBC and SQLJ Version 4.0, the values that are returned for ResultSetMetaData.getColumnName and ResultSetMetaData.getColumnLabel are changed to conform to the JDBC 4.0 standard. Those values differ from the values that are returned for the IBM Data Server Driver for JDBC and SQLJ Version 3.50 and earlier JDBC drivers.

Details

The results vary, depending on the following factors:

• Which data source type and version you are using. DB2® for z/OS® and OS/390 Version 7 and DB2 for i5/OS® V5R2 are unaffected by this change. Only later versions of these database products and all versions of DB2 Database for Linux, UNIX, and Windows and IBM Informix® Dynamic Server are affected.
• Whether a column in the SELECT list of a query has an AS clause. For JDBC drivers before the IBM Data Server Driver for JDBC and SQLJ Version 4.0, if a column in the SELECT list of a query contains an AS clause, ResultSetMetaData.getColumnName returns the argument of the AS clause. Under the IBM Data Server Driver for JDBC and SQLJ Version 4.0, ResultSetMetaData.getColumnName returns the table column name.
• Whether a column in the SELECT list of a query has a label from a LABEL statement but no AS clause. DB2 for z/OS and DB2 for System i support the LABEL statement, which assigns a label to a column. For JDBC drivers before the IBM Data Server Driver for JDBC and SQLJ Version 4.0, if a column in the SELECT list of a query has a label, ResultSetMetaData.getColumnName returns the table column name, and ResultSetMetaData.getColumnLabel returns the column label from the LABEL statement. Under the IBM Data Server Driver for JDBC and SQLJ Version 4.0, ResultSetMetaData.getColumnName and ResultSetMetaData.getColumnLabel return the table column name. The column label from the LABEL statement is not used.
• Whether a column in the SELECT list has a label from a LABEL statement and an AS clause. For JDBC drivers before the IBM Data Server Driver for JDBC and SQLJ Version 4.0, if a column in the SELECT list of a query has a label and an AS clause, ResultSetMetaData.getColumnName returns the argument of the AS clause, and ResultSetMetaData.getColumnLabel returns the column label from the LABEL statement. Under the IBM Data Server Driver for JDBC and SQLJ Version 4.0, ResultSetMetaData.getColumnName returns the table column name, and ResultSetMetaData.getColumnLabel returns the argument of the AS clause. The column label from the LABEL statement is not used.

Resolution

If you cannot change your applications to conform to the new  ResultSetMetaData  behavior but you need other features of JDBC 4.0, set the useJDBC4ColumnNameAndLabelSemantics  Connection  or  DataSource  property to  DB2BaseDataSource.NO  (2) to keep the old behavior.

 

我使用的是tomcat的datasource， 所以在datasource配置中增加连接属性设置：connectionProperties =“useJDBC4ColumnNameAndLabelSemantics=2”

 

 

参考：

https://stackoverflow.com/questions/15981711/db2jcc4-jar-invalid-parameter-unknown-column-name

http://tomcat.apache.org/tomcat-8.5-doc/jndi-resources-howto.html#JDBC_Data_Sources