需求:
根据用户勾选的列,在生成的报表中只包含特定的列。
在xml文件的语句如下:
<select id="findTradeByPage" parameterClass="extpage" resultClass="trade" >
<![CDATA[
select r_id
]]>
<dynamic>
<isNotNull property="t_id" prepend=",">
t_id
</isNotNull>
<isNotNull property="i_jcard_no" prepend=",">
i_jcard_no
</isNotNull>
<isNotNull property="o_jcard_no" prepend=",">
o_jcard_no
</isNotNull>
<isNotNull property="i_card_no" prepend=",">
i_card_no
</isNotNull>
<isNotNull property="comp_name" prepend=",">
comp_name
</isNotNull>
<isNotNull property="o_card_no" prepend=",">
o_card_no
</isNotNull>
<isNotNull property="t_op_code" prepend=",">
t_op_code
</isNotNull>
<isNotNull property="local_date" prepend=",">
local_date
</isNotNull>
<isNotNull property="local_time" prepend=",">
local_time
</isNotNull>
<isNotNull property="trans_code" prepend=",">
trans_code
</isNotNull>
<isNotNull property="amount" prepend=",">
amount
</isNotNull>
<isNotNull property="trace" prepend=",">
trace
</isNotNull>
<isNotNull property="opbank_id" prepend=",">
opbank_id
</isNotNull>
<isNotNull property="opbank_name" prepend=",">
opbank_name
</isNotNull>
<isNotNull property="trans_name" prepend=",">
trans_name
</isNotNull>
<isNotNull property="comp_id" prepend=",">
comp_id
</isNotNull>
</dynamic>
<![CDATA[
 from view_trade
]]>
<dynamic prepend="where 1=1">
<isNotEqual property="before" compareValue="0">
where r_id not in (select r_id from view_trade
<isNotNull property="objCondition" prepend="and">
opbank_id = #objCondition:VARCHAR#
</isNotNull>
<isNotNull property="objConditionTwo" prepend="and">
comp_id = #objConditionTwo:VARCHAR#
</isNotNull>
<isNotNull property="objConditionThree" prepend="and">
t_op_code LIKE '%'||#objConditionThree:VARCHAR#||'%'
</isNotNull>
<isNotNull property="objConditionFour" prepend="and">
trans_code = #objConditionFour:VARCHAR#
</isNotNull>
<isNotNull property="objConditionFive" prepend="and">
<![CDATA[
local_date >= #objConditionFive:VARCHAR#
]]>
</isNotNull>
<isNotNull property="objConditionSix" prepend="and">
<![CDATA[
local_date <= #objConditionSix:VARCHAR#
]]>
</isNotNull>
fetch first $before$ row only)
</isNotEqual>
</dynamic>
<dynamic prepend="where 2=2">
<isNotNull property="objCondition">
and opbank_id = #objCondition:VARCHAR#
</isNotNull>
<isNotNull property="objConditionTwo">
and comp_id = #objConditionTwo:VARCHAR#
</isNotNull>
<isNotNull property="objConditionThree">
and t_op_code LIKE '%'||#objConditionThree:VARCHAR#||'%'
</isNotNull>
<isNotNull property="objConditionFour">
and trans_code = #objConditionFour:VARCHAR#
</isNotNull>
<isNotNull property="objConditionFive">
<![CDATA[
and local_date >= #objConditionFive:VARCHAR#
]]>
</isNotNull>
<isNotNull property="objConditionSix">
<![CDATA[
and local_date <= #objConditionSix:VARCHAR#
]]>
</isNotNull>
</dynamic>
<![CDATA[
order by r_id fetch first $limit$ row only
]]>
</select>界面效果为: 
比如第一次勾选图中的几列,第一次查询是成功的。
Eclipse输出如下:
Connection - {conn-100042} Preparing Statement:        select r_id          ,     t_id                        ,     o_card_no        ,     t_op_code        ,     local_date        ,     local_time        ,     trans_code        ,     amount                                   from view_trade                                                       order by r_id fetch first 5 row only     
PreparedStatement - {pstm-100043} Executing Statement:        select r_id          ,     t_id                        ,     o_card_no        ,     t_op_code        ,     local_date        ,     local_time        ,     trans_code        ,     amount                                   from view_trade                                                       order by r_id fetch first 5 row only     
PreparedStatement - {pstm-100043} Parameters: []
PreparedStatement - {pstm-100043} Types: []
ResultSet - {rset-100044} ResultSet
ResultSet - {rset-100044} Header: [R_ID, T_ID, O_CARD_NO, T_OP_CODE, LOCAL_DATE, LOCAL_TIME, TRANS_CODE, AMOUNT]
ResultSet - {rset-100044} Result: [1, 100001, 1234567890, chenkai, 20101119, 085800, 200, 10000.0]但当我去掉其中某一行时,就出错了。
错误信息如下:
Connection - {conn-100054} Preparing Statement:        select r_id                              ,     o_card_no        ,     t_op_code        ,     local_date        ,     local_time        ,     trans_code        ,     amount                                   from view_trade                                                       order by r_id fetch first 5 row only     
PreparedStatement - {pstm-100055} Executing Statement:        select r_id                              ,     o_card_no        ,     t_op_code        ,     local_date        ,     local_time        ,     trans_code        ,     amount                                   from view_trade                                                       order by r_id fetch first 5 row only     
PreparedStatement - {pstm-100055} Parameters: []
PreparedStatement - {pstm-100055} Types: []
ResultSet - {rset-100056} ResultSet
XmlBeanDefinitionReader - Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
SQLErrorCodesFactory - SQLErrorCodes loaded: [DB2, HSQL, MS-SQL, MySQL, Oracle, Informix, PostgreSQL, Sybase]
RequestProcessor - Unhandled Exception thrown: class org.springframework.jdbc.UncategorizedSQLException
2010-11-19 11:32:40 org.apache.catalina.core.StandardWrapperValve invoke
严重: Servlet.service() for servlet action threw exception
com.ibm.db2.jcc.am.SqlException: [jcc][10150][10300][3.57.82] 参数无效:未知列名 T_ID。 ERRORCODE=-4460, SQLSTATE=null
at com.ibm.db2.jcc.am.bd.a(bd.java:660)
at com.ibm.db2.jcc.am.bd.a(bd.java:60)
at com.ibm.db2.jcc.am.bd.a(bd.java:103)
at com.ibm.db2.jcc.am.hb.a(hb.java:1700)
at com.ibm.db2.jcc.am.zl.a(zl.java:1642)
at com.ibm.db2.jcc.am.zl.getString(zl.java:1485)
at sun.reflect.GeneratedMethodAccessor42.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.ibatis.common.jdbc.logging.ResultSetLogProxy.invoke(ResultSetLogProxy.java:47)
at $Proxy20.getString(Unknown Source)
at com.ibatis.sqlmap.engine.type.StringTypeHandler.getResult(StringTypeHandler.java:35)
at com.ibatis.sqlmap.engine.mapping.result.ResultMap.getPrimitiveResultMappingValue(ResultMap.java:610)
对比发现,resultset中去取T_ID的值,但新的sql中不查询该列。
新增几列也是一样的问题,他没有去取新增的那几列的值。不知道如何解决,急。