Mapping of non-existent columns causes error when defining ResultMaps
---------------------------------------------------------------------
Key: IBATIS-476
URL: https://issues.apache.org/jira/browse/IBATIS-476
Project: iBatis for Java
Issue Type: Improvement
Components: SQL Maps
Affects Versions: 2.3.0
Environment: Windows XP / JBoss 4.0.5 / Oracle / Teradata
Reporter: Adam Parrish
Fix For: 2.3.1
I defined the following resultMap
<resultMap
id="StatusResult"
class="mil.af.gcssdsrf.led.lims.dto.StatusRow"
>
<result property="reportingDate" column="reportingDate"
javaType="java.util.Date" jdbcType="DATE"/>
<result property="grouping" resultMap="StatusRow.GroupResult"/>
<result property="mcHours" column="mcHours"/>
<result property="fmcHours" column="fmcHours"/>
<result property="pmcHours" column="pmcHours"/>
<result property="nmcmHours" column="nmcmHours"/>
<result property="nmcbHours" column="nmcbHours"/>
<result property="tnmcmHours" column="tnmcmHours"/>
<result property="nmcsHours" column="nmcsHours"/>
<result property="tnmcsHours" column="tnmcsHours"/>
<result property="possessedHours" column="possessedHours"/>
<result property="tpmcsHours" column="tpmcsHours"/>
<result property="tnmcHours" column="tnmcHours"/>
<result property="tpmcmHours" column="tpmcmHours"/>
<result property="mcRate" column="mcRate"/>
<result property="fmcRate" column="fmcRate"/>
<result property="pmcRate" column="pmcRate"/>
<result property="nmcmRate" column="nmcmRate"/>
<result property="nmcsRate" column="nmcsRate"/>
<result property="nmcbRate" column="nmcbRate"/>
<result property="tnmcmRate" column="tnmcmRate"/>
<result property="tnmcsRate" column="tnmcsRate"/>
<result property="tpmcsRate" column="tpmcsRate"/>
<result property="tnmcRate" column="tnmcRate"/>
<result property="tpmcmRate" column="tpmcmRate"/>
</resultMap>
<resultMap
id="GroupResult"
class="mil.af.gcssdsrf.led.lims.dto.Group"
>
<result property="airForceGrouping" column="leadCommand"/>
<result property="theater" column="theater"/>
<result property="command" column="command"/>
<result property="organization" column="organization"/>
<result property="types" column="familyType"/>
<result property="designs" column="design"/>
<result property="serieses" column="series"/>
</resultMap>
When I do the following query:
<select
id="status"
resultMap="StatusResult"
parameterClass="mil.af.gcssdsrf.led.lims.service.request.FilteredAndGroupedRequest"
remapResults="true">
SELECT
<isNotNull property="grouping" removeFirstPrepend="true">
<isEqual prepend=","
property="grouping.groupByStartDate" compareValue="true">
reportingDate
</isEqual>
<isEqual prepend=","
property="grouping.groupByAirforceGrouping" compareValue="true">
leadCommand
</isEqual>
<isEqual prepend="," property="grouping.groupByTheater"
compareValue="true">
theater
</isEqual>
<isEqual prepend="," property="grouping.groupByCommand"
compareValue="true">
command
</isEqual>
<isEqual prepend=","
property="grouping.groupByOrganization" compareValue="true">
organization
</isEqual>
<isEqual prepend="," property="grouping.groupByType"
compareValue="true">
familyType
</isEqual>
<isEqual prepend="," property="grouping.groupByDesign"
compareValue="true">
design
</isEqual>
<isEqual prepend="," property="grouping.groupBySeries"
compareValue="true">
series
</isEqual>
</isNotNull>
,sum(mcHours) as mcHours,
sum(fmcHours) as fmcHours,
sum(pmcHours) as pmcHours,
sum(nmcmHours) as nmcmHours,
sum(nmcbHours) as nmcbHours,
sum(tnmcmHours) as tnmcmHours,
sum(nmcsHours) as nmcsHours,
sum(tnmcsHours) as tnmcsHours,
sum(possessedHours) as possessedHours,
sum(tpmcsHours) as tpmcsHours,
sum(tnmcHours) as tnmcHours,
sum(tpmcmHours) as tpmcmHours,
sum(mcHours) / sum(possessedHours) as mcRate,
sum(fmcHours) / sum(possessedHours)as fmcRate,
sum(pmcHours) / sum(possessedHours)as pmcRate,
sum(nmcmHours) / sum(possessedHours)as nmcmRate,
sum(nmcsHours) / sum(possessedHours)as nmcsRate,
sum(nmcbHours) / sum(possessedHours)as nmcbRate,
sum(tnmcmHours) / sum(possessedHours)as tnmcmRate,
sum(tnmcsHours) / sum(possessedHours)as tnmcsRate,
sum(tpmcsHours) / sum(possessedHours)as tpmcsRate,
sum(tnmcHours) / sum(possessedHours)as tnmcRate,
sum(tpmcmHours) / sum(possessedHours)as tpmcmRate
FROM status_table
GROUP BY
<isNotNull property="grouping" removeFirstPrepend="true">
<isEqual prepend=","
property="grouping.groupByStartDate" compareValue="true">
reportingDate
</isEqual>
<isEqual prepend=","
property="grouping.groupByAirforceGrouping" compareValue="true">
leadCommand
</isEqual>
<isEqual prepend="," property="grouping.groupByTheater"
compareValue="true">
theater
</isEqual>
<isEqual prepend="," property="grouping.groupByCommand"
compareValue="true">
command
</isEqual>
<isEqual prepend=","
property="grouping.groupByOrganization" compareValue="true">
organization
</isEqual>
<isEqual prepend="," property="grouping.groupByType"
compareValue="true">
familyType
</isEqual>
<isEqual prepend="," property="grouping.groupByDesign"
compareValue="true">
design
</isEqual>
<isEqual prepend="," property="grouping.groupBySeries"
compareValue="true">
series
</isEqual>
</isNotNull>
ORDER BY
<isNotNull property="grouping" removeFirstPrepend="true">
<isEqual prepend=","
property="grouping.groupByStartDate" compareValue="true">
reportingDate
</isEqual>
<isEqual prepend=","
property="grouping.groupByAirforceGrouping" compareValue="true">
leadCommand
</isEqual>
<isEqual prepend="," property="grouping.groupByTheater"
compareValue="true">
theater
</isEqual>
<isEqual prepend="," property="grouping.groupByCommand"
compareValue="true">
command
</isEqual>
<isEqual prepend=","
property="grouping.groupByOrganization" compareValue="true">
organization
</isEqual>
<isEqual prepend="," property="grouping.groupByType"
compareValue="true">
familyType
</isEqual>
<isEqual prepend="," property="grouping.groupByDesign"
compareValue="true">
design
</isEqual>
<isEqual prepend="," property="grouping.groupBySeries"
compareValue="true">
series
</isEqual>
</isNotNull>
</select>
I sometimes will receive exceptions because the type handlers attempt to load a
column from the result set regardless of the fact that it doesn't exist. My
impression was that this was a reason to turn on remapResults=true, but that
functionality did not operate as expected.
My solution was to modify the type handlers in your code and rebuild the jar.
The following files were affected in my code
C:\Documents and Settings\aparrish\flex3workspaces\majcom\iBatisMapper>svn stat
M src\com\ibatis\sqlmap\engine\type\StringTypeHandler.java
M src\com\ibatis\sqlmap\engine\type\BaseTypeHandler.java
M src\com\ibatis\sqlmap\engine\type\DateTypeHandler.java
M src\com\ibatis\sqlmap\engine\type\DateOnlyTypeHandler.java
The following lines change
C:\Documents and Settings\aparrish\flex3workspaces\majcom\iBatisMapper>svn diff
Index: src/com/ibatis/sqlmap/engine/type/StringTypeHandler.java
===================================================================
--- src/com/ibatis/sqlmap/engine/type/StringTypeHandler.java (revision 601753
)
+++ src/com/ibatis/sqlmap/engine/type/StringTypeHandler.java (working copy)
@@ -32,12 +32,16 @@
public Object getResult(ResultSet rs, String columnName)
throws SQLException {
- Object s = rs.getString(columnName);
- if (rs.wasNull()) {
- return null;
- } else {
- return s;
- }
+ if(columnExists(rs, columnName)) {
+ Object s = rs.getString(columnName);
+ if (rs.wasNull()) {
+ return null;
+ } else {
+ return s;
+ }
+ } else {
+ return null;
+ }
}
public Object getResult(ResultSet rs, int columnIndex)
Index: src/com/ibatis/sqlmap/engine/type/BaseTypeHandler.java
===================================================================
--- src/com/ibatis/sqlmap/engine/type/BaseTypeHandler.java (revision 601753
)
+++ src/com/ibatis/sqlmap/engine/type/BaseTypeHandler.java (working copy)
@@ -15,11 +15,25 @@
*/
package com.ibatis.sqlmap.engine.type;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+
/**
* Base type handler for convenience
*/
public abstract class BaseTypeHandler implements TypeHandler {
+
+ protected boolean columnExists(ResultSet rs, String columnName) {
+ try {
+ rs.findColumn(columnName);
+ return true;
+ } catch (SQLException e) {
+ System.out.println("Column wasn't found, swallowing exception"
);
+ return false;
+ }
+ }
+
public boolean equals(Object object, String string) {
if (object == null || string == null) {
return object == string;
Index: src/com/ibatis/sqlmap/engine/type/DateTypeHandler.java
===================================================================
--- src/com/ibatis/sqlmap/engine/type/DateTypeHandler.java (revision 601753
)
+++ src/com/ibatis/sqlmap/engine/type/DateTypeHandler.java (working copy)
@@ -35,12 +35,16 @@
public Object getResult(ResultSet rs, String columnName)
throws SQLException {
- java.sql.Timestamp sqlTimestamp = rs.getTimestamp(columnName);
- if (rs.wasNull()) {
- return null;
- } else {
- return new java.util.Date(sqlTimestamp.getTime());
- }
+ if(columnExists(rs, columnName)) {
+ java.sql.Timestamp sqlTimestamp = rs.getTimestamp(columnName);
+ if (rs.wasNull()) {
+ return null;
+ } else {
+ return new java.util.Date(sqlTimestamp.getTime());
+ }
+ } else {
+ return null;
+ }
}
public Object getResult(ResultSet rs, int columnIndex)
Index: src/com/ibatis/sqlmap/engine/type/DateOnlyTypeHandler.java
===================================================================
--- src/com/ibatis/sqlmap/engine/type/DateOnlyTypeHandler.java (revision 601753
)
+++ src/com/ibatis/sqlmap/engine/type/DateOnlyTypeHandler.java (working copy)
@@ -35,12 +35,16 @@
public Object getResult(ResultSet rs, String columnName)
throws SQLException {
- java.sql.Date sqlDate = rs.getDate(columnName);
- if (rs.wasNull()) {
- return null;
- } else {
- return new java.util.Date(sqlDate.getTime());
- }
+ if(columnExists(rs, columnName)) {
+ java.sql.Date sqlDate = rs.getDate(columnName);
+ if (rs.wasNull()) {
+ return null;
+ } else {
+ return new java.util.Date(sqlDate.getTime());
+ }
+ } else {
+ return null;
+ }
}
public Object getResult(ResultSet rs, int columnIndex)
Is there any reason to believe that this is something that others would want to
use, or a more elegant way to solve this problem to plugin to the iBatis
framework. This solution works for my needs, but is there a way to configure
this functionality so that others could leverage it if need be.
Alternatively is this functionality already built in and I am using something
improperly?
Thanks for your time and please let me know if someone wants me to help
contribute back.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
