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 Ted Schrader <ts...@gmail.com> on 2006/03/09 16:30:07 UTC
using $value$ with collection property
Hello,
I'm having some trouble populating a collection property on my result
bean in an "iBATIS" way while using the $value$ idiom. I have an
object structure like this:
OrderDetailVO (order header info value object)
OrderDetailVO.orderNum (String)
OrderDetailVO.detailLines (List of OrderDetailLineVO objects)
I'm attempting to gradually move towards avoiding N+1, so my
intermediate step is the config below. In my queries, please note the
use of "FROM $companyLibrary$.ORHED" and "FROM $companyLibrary$.ORDET"
(data for our various clients are stored in different AS/400
libraries. For the non-AS/400 folks, think "database" when you see
"library"). It appears the value for $companyLibrary$ is not getting
passed along to the second query for order detail lines, per this
error:
com.ibatis.common.exception.NestedRuntimeException: Error lazy loading result.
Cause: com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in mycom/sqlmaps/OrderDetail.xml.
--- The error occurred while executing query.
--- Check the SELECT ODITEM FROM .ORDET WHERE ODCOID = ? AND ODORD# =
? ORDER BY ODITEM.
--- Check the SQL Statement (preparation failed).
--- Cause: java.sql.SQLException: [SQL0104] Token . was not valid.
Valid tokens: ( TABLE
Without further ado, here's the distilled mapping:
<sqlMap namespace="OrderDetail">
<resultMap id="orderDetailResult" class="mycom.OrderDetailVO">
<result column="OHORD#" property="orderNum" />
<!-- load list of detail lines. -->
<result column="{companyId=OHCOID, orderNum=OHORD#}"
property="detailLines"
select="OrderDetail.orderDetailLines" />
</resultMap>
<select id="orderDetail" resultMap="orderDetailResult">
SELECT OHCOID, OHORD##
FROM $companyLibrary$.ORHED
WHERE OHCOID = #companyId#
AND OHORD## = #orderNum#
</select>
<resultMap id="orderDetailLineResult" class="mycom.OrderDetailLineVO">
<result column="ODITEM" property="item" />
</resultMap>
<select id="orderDetailLines" resultMap="orderDetailLineResult">
SELECT ODITEM
FROM $companyLibrary$.ORDET
WHERE ODCOID = #companyId#
AND ODORD## = #orderNum#
ORDER BY ODITEM
</select>
</sqlMap>
Any ideas? Thanks,
Ted
Re: using $value$ with collection property
Posted by Ted Schrader <ts...@gmail.com>.
Hi Larry,
Your observation is an excellent one, assuming I only needed to access
a single library(database). In addition, I could also specify a
default library on the DataSource config hosted by WebSphere, which I
do use for every other web application I maintain (which is great
because my SQL doesn't have to know about libraries at all, just
files[tables]).
In this case, however, the application must span different libraries
on the fly. In other words, as an administrator of the site, I have a
choice of which companies to see. I can pick companyX which means I
need libraryX. When I change to companyY, the application should then
look at libraryY. This is why I'm using the $companyLibrary$ idiom in
the SQL. The schemas are identical between the various libraries, so I
can get away with doing this.
I've got it working good enough, fast enough, and clean enough. At
this point, I'm simply curious if the non-propagation of $value$ to
complex property queries is a bug, or perhaps something that was
addressed sometime between versions 2.1.5 and 2.1.7.
Thanks again!
Ted
On 10/03/06, Larry Meadors <lm...@apache.org> wrote:
> Ted, I think if you use system naming on the 400, you can specify a
> library list in the connection string...if that helps.
>
> Larry
>
>
> On 3/9/06, Ted Schrader <ts...@gmail.com> wrote:
> > Hello,
> >
> > I'm having some trouble populating a collection property on my result
> > bean in an "iBATIS" way while using the $value$ idiom. I have an
> > object structure like this:
> >
> > OrderDetailVO (order header info value object)
> > OrderDetailVO.orderNum (String)
> > OrderDetailVO.detailLines (List of OrderDetailLineVO objects)
> >
> > I'm attempting to gradually move towards avoiding N+1, so my
> > intermediate step is the config below. In my queries, please note the
> > use of "FROM $companyLibrary$.ORHED" and "FROM $companyLibrary$.ORDET"
> > (data for our various clients are stored in different AS/400
> > libraries. For the non-AS/400 folks, think "database" when you see
> > "library"). It appears the value for $companyLibrary$ is not getting
> > passed along to the second query for order detail lines, per this
> > error:
> >
> > com.ibatis.common.exception.NestedRuntimeException: Error lazy loading result.
> > Cause: com.ibatis.common.jdbc.exception.NestedSQLException:
> > --- The error occurred in mycom/sqlmaps/OrderDetail.xml.
> > --- The error occurred while executing query.
> > --- Check the SELECT ODITEM FROM .ORDET WHERE ODCOID = ? AND ODORD# =
> > ? ORDER BY ODITEM.
> > --- Check the SQL Statement (preparation failed).
> > --- Cause: java.sql.SQLException: [SQL0104] Token . was not valid.
> > Valid tokens: ( TABLE
> >
> > Without further ado, here's the distilled mapping:
> >
> > <sqlMap namespace="OrderDetail">
> > <resultMap id="orderDetailResult" class="mycom.OrderDetailVO">
> > <result column="OHORD#" property="orderNum" />
> >
> > <!-- load list of detail lines. -->
> > <result column="{companyId=OHCOID, orderNum=OHORD#}"
> > property="detailLines"
> > select="OrderDetail.orderDetailLines" />
> > </resultMap>
> >
> > <select id="orderDetail" resultMap="orderDetailResult">
> > SELECT OHCOID, OHORD##
> > FROM $companyLibrary$.ORHED
> > WHERE OHCOID = #companyId#
> > AND OHORD## = #orderNum#
> > </select>
> >
> >
> > <resultMap id="orderDetailLineResult" class="mycom.OrderDetailLineVO">
> > <result column="ODITEM" property="item" />
> > </resultMap>
> >
> > <select id="orderDetailLines" resultMap="orderDetailLineResult">
> > SELECT ODITEM
> > FROM $companyLibrary$.ORDET
> > WHERE ODCOID = #companyId#
> > AND ODORD## = #orderNum#
> > ORDER BY ODITEM
> > </select>
> > </sqlMap>
> >
> > Any ideas? Thanks,
> >
> > Ted
> >
>
Re: using $value$ with collection property
Posted by Larry Meadors <lm...@apache.org>.
Ted, I think if you use system naming on the 400, you can specify a
library list in the connection string...if that helps.
Larry
On 3/9/06, Ted Schrader <ts...@gmail.com> wrote:
> Hello,
>
> I'm having some trouble populating a collection property on my result
> bean in an "iBATIS" way while using the $value$ idiom. I have an
> object structure like this:
>
> OrderDetailVO (order header info value object)
> OrderDetailVO.orderNum (String)
> OrderDetailVO.detailLines (List of OrderDetailLineVO objects)
>
> I'm attempting to gradually move towards avoiding N+1, so my
> intermediate step is the config below. In my queries, please note the
> use of "FROM $companyLibrary$.ORHED" and "FROM $companyLibrary$.ORDET"
> (data for our various clients are stored in different AS/400
> libraries. For the non-AS/400 folks, think "database" when you see
> "library"). It appears the value for $companyLibrary$ is not getting
> passed along to the second query for order detail lines, per this
> error:
>
> com.ibatis.common.exception.NestedRuntimeException: Error lazy loading result.
> Cause: com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in mycom/sqlmaps/OrderDetail.xml.
> --- The error occurred while executing query.
> --- Check the SELECT ODITEM FROM .ORDET WHERE ODCOID = ? AND ODORD# =
> ? ORDER BY ODITEM.
> --- Check the SQL Statement (preparation failed).
> --- Cause: java.sql.SQLException: [SQL0104] Token . was not valid.
> Valid tokens: ( TABLE
>
> Without further ado, here's the distilled mapping:
>
> <sqlMap namespace="OrderDetail">
> <resultMap id="orderDetailResult" class="mycom.OrderDetailVO">
> <result column="OHORD#" property="orderNum" />
>
> <!-- load list of detail lines. -->
> <result column="{companyId=OHCOID, orderNum=OHORD#}"
> property="detailLines"
> select="OrderDetail.orderDetailLines" />
> </resultMap>
>
> <select id="orderDetail" resultMap="orderDetailResult">
> SELECT OHCOID, OHORD##
> FROM $companyLibrary$.ORHED
> WHERE OHCOID = #companyId#
> AND OHORD## = #orderNum#
> </select>
>
>
> <resultMap id="orderDetailLineResult" class="mycom.OrderDetailLineVO">
> <result column="ODITEM" property="item" />
> </resultMap>
>
> <select id="orderDetailLines" resultMap="orderDetailLineResult">
> SELECT ODITEM
> FROM $companyLibrary$.ORDET
> WHERE ODCOID = #companyId#
> AND ODORD## = #orderNum#
> ORDER BY ODITEM
> </select>
> </sqlMap>
>
> Any ideas? Thanks,
>
> Ted
>
Re: using $value$ with collection property
Posted by Ted Schrader <ts...@gmail.com>.
Yeah, "companyLibrary=someLibrary" didn't fly:
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in mycom/sqlmaps/OrderDetail.xml.
--- The error occurred while applying a result map.
--- Check the OrderDetail.orderDetailResult.
--- Check the result mapping for the 'detailLines' property.
--- Cause: java.sql.SQLException: An undefined column name was detected.
Caused by: java.sql.SQLException: An undefined column name was detected.
I suppose jumping straight to the N+1 avoidance would work since that
solution entails a single query. Perhaps the non-propagation of
$values$ to subsequent complex property queries is a bug.
Performance is not a big issue for me in this case because I only dig
up a single Order, not lots of them. I think I'll play Josh Bloch's
"Item 37: Optimize Judiciously" card here and say "Good 'nuff". With
that said, I'm still interesting in any insight.
Thanks!
Ted
Re: using $value$ with collection property
Posted by Ted Schrader <ts...@gmail.com>.
Hi Niels,
I thought of that, but didn't try it since $companyLibrary$ isn't a
column, per se. I'll give it a whirl, nonetheless.
Thanks,
Ted
On 09/03/06, Ted Schrader <ts...@gmail.com> wrote:
> Hello,
>
> I neglected to state something important in my first message: I'm
> using version 2.1.5:
>
> From release.txt:
> >> Build Date: 2005/07/17 20:58
> >> Build Number: 582
> >>
> >> ------------------------------
> >> 2.1.5 - July 17, 2005
> >> ------------------------------
>
> Thanks,
>
> Ted
>
> On 09/03/06, Ted Schrader <ts...@gmail.com> wrote:
> > Hello,
> >
> > I'm having some trouble populating a collection property on my result
> > bean in an "iBATIS" way while using the $value$ idiom. I have an
> > object structure like this:
> >
> > OrderDetailVO (order header info value object)
> > OrderDetailVO.orderNum (String)
> > OrderDetailVO.detailLines (List of OrderDetailLineVO objects)
> >
> > I'm attempting to gradually move towards avoiding N+1, so my
> > intermediate step is the config below. In my queries, please note the
> > use of "FROM $companyLibrary$.ORHED" and "FROM $companyLibrary$.ORDET"
> > (data for our various clients are stored in different AS/400
> > libraries. For the non-AS/400 folks, think "database" when you see
> > "library"). It appears the value for $companyLibrary$ is not getting
> > passed along to the second query for order detail lines, per this
> > error:
> >
> > com.ibatis.common.exception.NestedRuntimeException: Error lazy loading result.
> > Cause: com.ibatis.common.jdbc.exception.NestedSQLException:
> > --- The error occurred in mycom/sqlmaps/OrderDetail.xml.
> > --- The error occurred while executing query.
> > --- Check the SELECT ODITEM FROM .ORDET WHERE ODCOID = ? AND ODORD# =
> > ? ORDER BY ODITEM.
> > --- Check the SQL Statement (preparation failed).
> > --- Cause: java.sql.SQLException: [SQL0104] Token . was not valid.
> > Valid tokens: ( TABLE
> >
> > Without further ado, here's the distilled mapping:
> >
> > <sqlMap namespace="OrderDetail">
> > <resultMap id="orderDetailResult" class="mycom.OrderDetailVO">
> > <result column="OHORD#" property="orderNum" />
> >
> > <!-- load list of detail lines. -->
> > <result column="{companyId=OHCOID, orderNum=OHORD#}"
> > property="detailLines"
> > select="OrderDetail.orderDetailLines" />
> > </resultMap>
> >
> > <select id="orderDetail" resultMap="orderDetailResult">
> > SELECT OHCOID, OHORD##
> > FROM $companyLibrary$.ORHED
> > WHERE OHCOID = #companyId#
> > AND OHORD## = #orderNum#
> > </select>
> >
> >
> > <resultMap id="orderDetailLineResult" class="mycom.OrderDetailLineVO">
> > <result column="ODITEM" property="item" />
> > </resultMap>
> >
> > <select id="orderDetailLines" resultMap="orderDetailLineResult">
> > SELECT ODITEM
> > FROM $companyLibrary$.ORDET
> > WHERE ODCOID = #companyId#
> > AND ODORD## = #orderNum#
> > ORDER BY ODITEM
> > </select>
> > </sqlMap>
> >
> > Any ideas? Thanks,
> >
> > Ted
> >
>
Re: using $value$ with collection property
Posted by Ted Schrader <ts...@gmail.com>.
Hello,
I neglected to state something important in my first message: I'm
using version 2.1.5:
>From release.txt:
>> Build Date: 2005/07/17 20:58
>> Build Number: 582
>>
>> ------------------------------
>> 2.1.5 - July 17, 2005
>> ------------------------------
Thanks,
Ted
On 09/03/06, Ted Schrader <ts...@gmail.com> wrote:
> Hello,
>
> I'm having some trouble populating a collection property on my result
> bean in an "iBATIS" way while using the $value$ idiom. I have an
> object structure like this:
>
> OrderDetailVO (order header info value object)
> OrderDetailVO.orderNum (String)
> OrderDetailVO.detailLines (List of OrderDetailLineVO objects)
>
> I'm attempting to gradually move towards avoiding N+1, so my
> intermediate step is the config below. In my queries, please note the
> use of "FROM $companyLibrary$.ORHED" and "FROM $companyLibrary$.ORDET"
> (data for our various clients are stored in different AS/400
> libraries. For the non-AS/400 folks, think "database" when you see
> "library"). It appears the value for $companyLibrary$ is not getting
> passed along to the second query for order detail lines, per this
> error:
>
> com.ibatis.common.exception.NestedRuntimeException: Error lazy loading result.
> Cause: com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in mycom/sqlmaps/OrderDetail.xml.
> --- The error occurred while executing query.
> --- Check the SELECT ODITEM FROM .ORDET WHERE ODCOID = ? AND ODORD# =
> ? ORDER BY ODITEM.
> --- Check the SQL Statement (preparation failed).
> --- Cause: java.sql.SQLException: [SQL0104] Token . was not valid.
> Valid tokens: ( TABLE
>
> Without further ado, here's the distilled mapping:
>
> <sqlMap namespace="OrderDetail">
> <resultMap id="orderDetailResult" class="mycom.OrderDetailVO">
> <result column="OHORD#" property="orderNum" />
>
> <!-- load list of detail lines. -->
> <result column="{companyId=OHCOID, orderNum=OHORD#}"
> property="detailLines"
> select="OrderDetail.orderDetailLines" />
> </resultMap>
>
> <select id="orderDetail" resultMap="orderDetailResult">
> SELECT OHCOID, OHORD##
> FROM $companyLibrary$.ORHED
> WHERE OHCOID = #companyId#
> AND OHORD## = #orderNum#
> </select>
>
>
> <resultMap id="orderDetailLineResult" class="mycom.OrderDetailLineVO">
> <result column="ODITEM" property="item" />
> </resultMap>
>
> <select id="orderDetailLines" resultMap="orderDetailLineResult">
> SELECT ODITEM
> FROM $companyLibrary$.ORDET
> WHERE ODCOID = #companyId#
> AND ODORD## = #orderNum#
> ORDER BY ODITEM
> </select>
> </sqlMap>
>
> Any ideas? Thanks,
>
> Ted
>