You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Tracey Annison <ta...@trisystems.co.uk> on 2008/04/28 16:35:28 UTC
Using Ibats with Group By and sums, etc
Hiya
I am working on an ongoing project that uses Ibatis to an AS/400, mapping each table to an Ibatis object, with simple "get" and "getAll" type of Selects, and all is well. But now I need to do something more complex using SQL's SUM and GROUP BY functions, and I can't seem to make it work for me...
In my sqlMap XML file, I have a resultMap for the full object that represents my file, and queries on that. Like this :
<resultMap id="ledgerResult" class=".....LedgerIbatis">
<result column= "BCUS" property = "customerRef" />
<result column= "BCMP" property = "companyCode" />
<result column= "BDPT" property = "departmentCode" />
<result column= "BVAL" property = "value" />
</resultMap>
<select id="getLedgersForCustomer"
parameterClass="java.util.HashMap"
resultMap="LedgerResult"
resultClass="uk.co.....LedgerIbatis">
<![CDATA[
select '$library$' as library,
BCUS,
BCMP,
BDPT,
BVAL
from
$library$/FILENAME
where BCUS = #customerRef#
]]>
</select>
With a Java object called LedgerIbatis that has a property for every item above, ie library, customerRef,companyCode, departmentCode and value. And this works fine!
Now I need to add a cut-down object that just represents several keys, plus a total value. I need to get the total value for every unique combination of companyCode and departmentCode for a given customerRef. So, this is what I'm trying to do here :
<resultMap id="ledgerTotalsResult" class="uk.co......LedgerTotalsIbatis">
<result column= "BCUS" property = "customerRef" />
<result column= "BCMP" property = "companyCode" />
<result column= "BDPT" property = "departmentCode" />
</resultMap>
With a Java object LedgerTotalsIbatis that has these properties : library, customerRef,companyCode, departmentCode and valueTotal (to hold the new total value). And I'm trying this query :
<select id="getLedgerTotals"
parameterClass="java.util.HashMap"
resultMap="ledgerTotalsResult"
resultClass="uk.co......LedgerTotalsIbatis">
select '$library$' as library,
BCMP as companyCode,
BDPT as departmentCode,
SUM(BVAL) as valueTotal
from
from
$library$/FILENAME
where BCUS = #customerRef#
group by
BCMP,
BDPT
</select>
I'm hoping for one row for every unique combination of companyCode & departmentCode that exists for my customerRef, holding the total value of all the entries with that key combination. What I'm getting is an "An undefined column name was detected" errormessage!
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in /..../Ledger.xml.
--- The error occurred while applying a result map.
--- Check the Ledger.ledgerTotalsResult.
--- Check the result mapping for the 'companyCode' property.
--- Cause: java.sql.SQLException: An undefined column name was detected.
Caused by: java.sql.SQLException: An undefined column name was detected.
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:185)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList(GeneralStatement.java:123)
etc, etc
And I don't see why this should be, as I'm clearly mapping the companyCode property. And I know that I can explicitly set a value in the ResultMap (and thus the Java object) that's not in the AS/400 table, as we do that with the library value...
Anyone have a clue what's going on here, or how I shoud proceed for the best?
I'm hoping to do all this work in the SQL, rather than with embedded resultMaps and other specific Ibatis features, as this query will later have to be more elaborate, taking data from more than one file, etc.
Cheers
Tracey Annison
----------------------------------------------------------------------
The information in this email is confidential and may be legally privileged.
It is intended solely for the addressee. Access to this email by
anyone else is unauthorised. If you are not the intended recipient,
any disclosure, copying, distribution, or any action taken or omitted
to be taken in reliance on it, is prohibited and may be unlawful.
TriSystems Ltd. cannot accept liability for statements made which are clearly
the sender's own.
RE: Using Ibats with Group By and sums, etc
Posted by Tracey Annison <ta...@trisystems.co.uk>.
Ah, right... that helps!
Cheers
Tracey Annison
_____
From: Niels Beekman [mailto:n.beekman@wis.nl]
Sent: 28 April 2008 16:39
To: user-java@ibatis.apache.org
Subject: RE: Using Ibats with Group By and sums, etc
Hi,
First, you're using both the resultClass and resultMap attributes; use only one of those, not both. Second, in your resultmap you have the following:
<result column= "BCMP" property = "companyCode" />
This means, map resultset column 'BCMP' to property 'companyCode'. In your SQL you have:
select '$library$' as library,
BCMP as companyCode,
BDPT as departmentCode,
SUM(BVAL) as valueTotal
So, iBATIS can't find them. Either remove the 'AS companyCode' statement, or change the resultmap.
Hope this helps,
Niels
P.S. You're having duplicate FROM-keywords in the second query, typo or copy/paste error?
_____
From: Tracey Annison [mailto:tannison@trisystems.co.uk]
Sent: Monday, April 28, 2008 4:35 PM
To: user-java@ibatis.apache.org
Subject: Using Ibats with Group By and sums, etc
Hiya
I am working on an ongoing project that uses Ibatis to an AS/400, mapping each table to an Ibatis object, with simple "get" and "getAll" type of Selects, and all is well. But now I need to do something more complex using SQL's SUM and GROUP BY functions, and I can't seem to make it work for me...
In my sqlMap XML file, I have a resultMap for the full object that represents my file, and queries on that. Like this :
<resultMap id="ledgerResult" class=".....LedgerIbatis">
<result column= "BCUS" property = "customerRef" />
<result column= "BCMP" property = "companyCode" />
<result column= "BDPT" property = "departmentCode" />
<result column= "BVAL" property = "value" />
</resultMap>
<select id="getLedgersForCustomer"
parameterClass="java.util.HashMap"
resultMap="LedgerResult"
resultClass="uk.co.....LedgerIbatis">
<![CDATA[
select '$library$' as library,
BCUS,
BCMP,
BDPT,
BVAL
from
$library$/FILENAME
where BCUS = #customerRef#
]]>
</select>
With a Java object called LedgerIbatis that has a property for every item above, ie library, customerRef,companyCode, departmentCode and value. And this works fine!
Now I need to add a cut-down object that just represents several keys, plus a total value. I need to get the total value for every unique combination of companyCode and departmentCode for a given customerRef. So, this is what I'm trying to do here :
<resultMap id="ledgerTotalsResult" class="uk.co......LedgerTotalsIbatis">
<result column= "BCUS" property = "customerRef" />
<result column= "BCMP" property = "companyCode" />
<result column= "BDPT" property = "departmentCode" />
</resultMap>
With a Java object LedgerTotalsIbatis that has these properties : library, customerRef,companyCode, departmentCode and valueTotal (to hold the new total value). And I'm trying this query :
<select id="getLedgerTotals"
parameterClass="java.util.HashMap"
resultMap="ledgerTotalsResult"
resultClass="uk.co......LedgerTotalsIbatis">
select '$library$' as library,
BCMP as companyCode,
BDPT as departmentCode,
SUM(BVAL) as valueTotal
from
from
$library$/FILENAME
where BCUS = #customerRef#
group by
BCMP,
BDPT
</select>
I'm hoping for one row for every unique combination of companyCode & departmentCode that exists for my customerRef, holding the total value of all the entries with that key combination. What I'm getting is an "An undefined column name was detected" errormessage!
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in /..../Ledger.xml.
--- The error occurred while applying a result map.
--- Check the Ledger.ledgerTotalsResult.
--- Check the result mapping for the 'companyCode' property.
--- Cause: java.sql.SQLException: An undefined column name was detected.
Caused by: java.sql.SQLException: An undefined column name was detected.
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:185)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList(GeneralStatement.java:123)
etc, etc
And I don't see why this should be, as I'm clearly mapping the companyCode property. And I know that I can explicitly set a value in the ResultMap (and thus the Java object) that's not in the AS/400 table, as we do that with the library value...
Anyone have a clue what's going on here, or how I shoud proceed for the best?
I'm hoping to do all this work in the SQL, rather than with embedded resultMaps and other specific Ibatis features, as this query will later have to be more elaborate, taking data from more than one file, etc.
Cheers
Tracey Annison
----------------------------------------------------------------------
The information in this email is confidential and may be legally privileged.
It is intended solely for the addressee. Access to this email by
anyone else is unauthorised. If you are not the intended recipient,
any disclosure, copying, distribution, or any action taken or omitted
to be taken in reliance on it, is prohibited and may be unlawful.
TriSystems Ltd. cannot accept liability for statements made which are clearly
the sender's own.
----------------------------------------------------------------------
The information in this email is confidential and may be legally privileged.
It is intended solely for the addressee. Access to this email by
anyone else is unauthorised. If you are not the intended recipient,
any disclosure, copying, distribution, or any action taken or omitted
to be taken in reliance on it, is prohibited and may be unlawful.
TriSystems Ltd. cannot accept liability for statements made which are clearly
the sender's own.
RE: Using Ibats with Group By and sums, etc
Posted by Niels Beekman <n....@wis.nl>.
Hi,
First, you're using both the resultClass and resultMap attributes; use
only one of those, not both. Second, in your resultmap you have the
following:
<result column= "BCMP" property = "companyCode" />
This means, map resultset column 'BCMP' to property 'companyCode'. In
your SQL you have:
select '$library$' as library,
BCMP as companyCode,
BDPT as departmentCode,
SUM(BVAL) as valueTotal
So, iBATIS can't find them. Either remove the 'AS companyCode'
statement, or change the resultmap.
Hope this helps,
Niels
P.S. You're having duplicate FROM-keywords in the second query, typo or
copy/paste error?
________________________________
From: Tracey Annison [mailto:tannison@trisystems.co.uk]
Sent: Monday, April 28, 2008 4:35 PM
To: user-java@ibatis.apache.org
Subject: Using Ibats with Group By and sums, etc
Hiya
I am working on an ongoing project that uses Ibatis to an AS/400,
mapping each table to an Ibatis object, with simple "get" and "getAll"
type of Selects, and all is well. But now I need to do something more
complex using SQL's SUM and GROUP BY functions, and I can't seem to make
it work for me...
In my sqlMap XML file, I have a resultMap for the full object that
represents my file, and queries on that. Like this :
<resultMap id="ledgerResult" class=".....LedgerIbatis">
<result column= "BCUS" property = "customerRef" />
<result column= "BCMP" property = "companyCode" />
<result column= "BDPT" property = "departmentCode" />
<result column= "BVAL" property = "value" />
</resultMap>
<select id="getLedgersForCustomer"
parameterClass="java.util.HashMap"
resultMap="LedgerResult"
resultClass="uk.co.....LedgerIbatis">
<![CDATA[
select '$library$' as library,
BCUS,
BCMP,
BDPT,
BVAL
from
$library$/FILENAME
where BCUS = #customerRef#
]]>
</select>
With a Java object called LedgerIbatis that has a property for every
item above, ie library, customerRef,companyCode, departmentCode and
value. And this works fine!
Now I need to add a cut-down object that just represents several keys,
plus a total value. I need to get the total value for every unique
combination of companyCode and departmentCode for a given customerRef.
So, this is what I'm trying to do here :
<resultMap id="ledgerTotalsResult"
class="uk.co......LedgerTotalsIbatis">
<result column= "BCUS" property = "customerRef" />
<result column= "BCMP" property = "companyCode" />
<result column= "BDPT" property = "departmentCode" />
</resultMap>
With a Java object LedgerTotalsIbatis that has these properties :
library, customerRef,companyCode, departmentCode and valueTotal (to hold
the new total value). And I'm trying this query :
<select id="getLedgerTotals"
parameterClass="java.util.HashMap"
resultMap="ledgerTotalsResult"
resultClass="uk.co......LedgerTotalsIbatis">
select '$library$' as library,
BCMP as companyCode,
BDPT as departmentCode,
SUM(BVAL) as valueTotal
from
from
$library$/FILENAME
where BCUS = #customerRef#
group by
BCMP,
BDPT
</select>
I'm hoping for one row for every unique combination of companyCode &
departmentCode that exists for my customerRef, holding the total value
of all the entries with that key combination. What I'm getting is an "An
undefined column name was detected" errormessage!
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in /..../Ledger.xml.
--- The error occurred while applying a result map.
--- Check the Ledger.ledgerTotalsResult.
--- Check the result mapping for the 'companyCode' property.
--- Cause: java.sql.SQLException: An undefined column name was detected.
Caused by: java.sql.SQLException: An undefined column name was detected.
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQuery
WithCallback(GeneralStatement.java:185)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQuery
ForList(GeneralStatement.java:123)
etc, etc
And I don't see why this should be, as I'm clearly mapping the
companyCode property. And I know that I can explicitly set a value in
the ResultMap (and thus the Java object) that's not in the AS/400 table,
as we do that with the library value...
Anyone have a clue what's going on here, or how I shoud proceed for the
best?
I'm hoping to do all this work in the SQL, rather than with embedded
resultMaps and other specific Ibatis features, as this query will later
have to be more elaborate, taking data from more than one file, etc.
Cheers
Tracey Annison
----------------------------------------------------------------------
The information in this email is confidential and may be legally
privileged.
It is intended solely for the addressee. Access to this email by
anyone else is unauthorised. If you are not the intended recipient,
any disclosure, copying, distribution, or any action taken or omitted
to be taken in reliance on it, is prohibited and may be unlawful.
TriSystems Ltd. cannot accept liability for statements made which are
clearly
the sender's own.