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 Mikael Andersson <ma...@gmail.com> on 2007/03/21 10:20:36 UTC
Semi complex mapping question
Hi,
I'm new to iBATIS and need some help with a problem I'm having.
I have a table called CURVE which contains curve points for loads of curves
(graph curves), where each curve can have a dynamic number of curve points.
Table structure:
CURVE_NAME | CURVE_CATEGORY | OFFSET | VALUE
CURVE_NAME and CURVE_CATEGORY are the primary keys, OFFSET is the x-value
and VALUE is the y-value
SqlMap :
<resultMap id="CurveBOResult" class="test.bo.CurveBO">
<result column="CURVE_NAME" jdbcType="CHAR" property="curveName"/>
<result column="CURVE_CATEGORY" jdbcType="CHAR"
property="curveCategory"/>
<result property="curvePoints" select="getCurvePoints"
column="{curveName=CURVE_NAME,curveCategory=CURVE_CATEGORY}"/>
</resultMap>
<resultMap id="CurvePointBOResult" class="test.bo.CurvePointBO">
<result column="VALUE" jdbcType="DOUBLE" property="value"/>
<result column="OFFSET" jdbcType="DOUBLE" property="offset"/>
</resultMap>
<select id="selectAll" resultMap="CurveBOResult">
select distinct
CURVE_NAME, CURVE_CATEGORY
from dbo.CURVE
</select>
<select id="getCurvePoints" resultMap="CurvePointBOResult">
select
VALUE, OFFSET
from dbo.CURVE
where CURVE_NAME = #curveName#
and CURVE_CATEGORY = #curveCategory#
</select>
CurveBO.java has a: private List<CurvePointBO> curvePoints = new
ArrayList<CurvePointBO>(19);
with public getter and setter.
When I run this I get the following error message:
org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation;
uncategorized SQLException for SQL []; SQL state [null]; error code [0];
--- The error occurred in sqlmap/dbo_CURVES_SqlMap.xml.
--- The error occurred while applying a result map.
--- Check the dbo_CURVE.CreditSpreadBOResult.
--- Check the result mapping for the 'curvePoints' property.
--- Cause: java.lang.NullPointerException; nested exception is
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in sqlmap/dbo_CURVE_SqlMap.xml.
--- The error occurred while applying a result map.
--- Check the dbo_CURVE.CurveBOResult.
--- Check the result mapping for the 'curvePoints' property.
--- Cause: java.lang.NullPointerException
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in sqlmap/dbo_CURVE_SqlMap.xml.
--- The error occurred while applying a result map.
--- Check the dbo_CURVE.CurveBOResult.
--- Check the result mapping for the 'curvePoints' property.
--- Cause: java.lang.NullPointerException
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback
(GeneralStatement.java:188)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList
(GeneralStatement.java:123)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(
SqlMapExecutorDelegate.java:615)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(
SqlMapExecutorDelegate.java:589)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(
SqlMapSessionImpl.java:118)
at
org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient(
SqlMapClientTemplate.java:231)
at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(
SqlMapClientTemplate.java:168)
at
org.springframework.orm.ibatis.SqlMapClientTemplate.executeWithListResult(
SqlMapClientTemplate.java:190)
at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList(
SqlMapClientTemplate.java:229)
at test.dao.CurveBODAOImpl.selectAll(CreditSpreadBODAOImpl.java:40)
at testa.dao.TestCurveBODAOImpl.testSelectdAll(
TestCreditSpreadBODAOImpl.java:38)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(
NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(
DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(
JUnit3TestReference.java:128)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(
TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(
RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(
RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(
RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(
RemoteTestRunner.java:196)
Caused by: java.lang.NullPointerException
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.endTransaction(
SqlMapExecutorDelegate.java:782)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.endTransaction(
SqlMapSessionImpl.java:176)
at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.endTransaction(
SqlMapClientImpl.java:154)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.autoEndTransaction(
SqlMapExecutorDelegate.java:883)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(
SqlMapExecutorDelegate.java:622)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(
SqlMapExecutorDelegate.java:589)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(
SqlMapSessionImpl.java:118)
at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(
SqlMapClientImpl.java:95)
at com.ibatis.sqlmap.engine.mapping.result.loader.ResultLoader.getResult
(ResultLoader.java:72)
at
com.ibatis.sqlmap.engine.mapping.result.loader.ResultLoader.loadResult(
ResultLoader.java:59)
at
com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.getNestedSelectMappingValue
(BasicResultMap.java:502)
at com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.getResults(
BasicResultMap.java:340)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.handleResults(
SqlExecutor.java:381)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.handleMultipleResults(
SqlExecutor.java:301)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(
SqlExecutor.java:190)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQuery(
GeneralStatement.java:205)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback
(GeneralStatement.java:173)
... 28 more
Have a feeling that I missed something simple :)
Cheers,
Mike
Re: Semi complex mapping question
Posted by Mikael Andersson <ma...@gmail.com>.
Forgot to add that suggestions for better approaches would be greatly
appreciated (trying to do it with resultMap attribute at the moment).
- Mike
On 21/03/07, Mikael Andersson <ma...@gmail.com> wrote:
>
> Hi Ted,
> just started using iBATIS and the manual contains a similar approach, that
> is why I did it that way. I figured I would get that working and then tackle
> the problem of N+1 select statements.
>
> The table contains lots and lots of curves, where each curve has many
> curve points. Stupid design I know, loads of redundancy...
> The primary keys in the table are CURVE_NAME,CURVE_CATEGORY and OFFSET ,
> which gives me one point on a curve.
>
> The columns CURVE_NAME and CURVE_CATEGORY whil give me a list of points
> defining a complete curve.
>
> Hope the above makes some sence.
>
> I used Abator to generate the code, and then manually changed stuff. This
> is how it is called in the DAO:
>
> public List<CurveBO> selectAll() {
> List<CurveBO> list =
> (List<CurveBO>)getSqlMapClientTemplate().queryForList("selectAll", null);
> return list;
> }
>
>
> Thanks,
> Mike
>
> On 21/03/07, Ted Schrader <teds.mailing.lists@gmail.com > wrote:
> >
> > Hi Mike,
> >
> > How are you calling the mapped statement on the Java side?
> >
> > Also, it's not clear why you're going to the trouble of a nested
> > select ("getCurvePoints") when it seems from your table design that
> > there will be one and only one x-y pair for a primary key. Would you
> > elaborate on why you are attempting this approach?
> >
> > Ted
> >
> > On 21/03/07, Mikael Andersson <ma...@gmail.com> wrote:
> > > Hi,
> > > I'm new to iBATIS and need some help with a problem I'm having.
> > >
> > > I have a table called CURVE which contains curve points for loads of
> > curves
> > > (graph curves), where each curve can have a dynamic number of curve
> > points.
> > >
> > > Table structure:
> > > CURVE_NAME | CURVE_CATEGORY | OFFSET | VALUE
> > >
> > > CURVE_NAME and CURVE_CATEGORY are the primary keys, OFFSET is the
> > x-value
> > > and VALUE is the y-value
> > >
> > > SqlMap :
> > >
> > > <resultMap id="CurveBOResult" class=" test.bo.CurveBO">
> > > <result column="CURVE_NAME" jdbcType="CHAR" property="curveName"/>
> > > <result column="CURVE_CATEGORY" jdbcType="CHAR"
> > > property="curveCategory"/>
> > > <result property="curvePoints" select="getCurvePoints"
> > > column="{curveName=CURVE_NAME,curveCategory=CURVE_CATEGORY}"/>
> > > </resultMap>
> > >
> > > <resultMap id="CurvePointBOResult" class=" test.bo.CurvePointBO">
> > > <result column="VALUE" jdbcType="DOUBLE" property="value"/>
> > > <result column="OFFSET" jdbcType="DOUBLE" property="offset"/>
> > > </resultMap>
> > >
> > > <select id="selectAll" resultMap="CurveBOResult">
> > > select distinct
> > > CURVE_NAME, CURVE_CATEGORY
> > > from dbo.CURVE
> > > </select>
> > >
> > > <select id="getCurvePoints" resultMap="CurvePointBOResult">
> > > select
> > > VALUE, OFFSET
> > > from dbo.CURVE
> > > where CURVE_NAME = #curveName#
> > > and CURVE_CATEGORY = #curveCategory#
> > > </select>
> > >
> > > CurveBO.java has a: private List<CurvePointBO> curvePoints = new
> > > ArrayList<CurvePointBO>(19);
> > > with public getter and setter.
> > >
> > > When I run this I get the following error message:
> > >
> > > org.springframework.jdbc.UncategorizedSQLException:
> > > SqlMapClient operation; uncategorized SQLException for SQL []; SQL
> > state
> > > [null]; error code [0];
> > > --- The error occurred in sqlmap/dbo_CURVES_SqlMap.xml.
> > > --- The error occurred while applying a result map.
> > > --- Check the dbo_CURVE.CreditSpreadBOResult.
> > > --- Check the result mapping for the 'curvePoints' property.
> > > --- Cause: java.lang.NullPointerException ; nested exception is
> > > com.ibatis.common.jdbc.exception.NestedSQLException:
> > > --- The error occurred in sqlmap/dbo_CURVE_SqlMap.xml.
> > > --- The error occurred while applying a result map.
> > > --- Check the dbo_CURVE.CurveBOResult.
> > > --- Check the result mapping for the 'curvePoints' property.
> > > --- Cause: java.lang.NullPointerException
> > > com.ibatis.common.jdbc.exception.NestedSQLException:
> > > --- The error occurred in sqlmap/dbo_CURVE_SqlMap.xml.
> > > --- The error occurred while applying a result map.
> > > --- Check the dbo_CURVE.CurveBOResult.
> > > --- Check the result mapping for the 'curvePoints' property.
> > > --- Cause: java.lang.NullPointerException
> > > at
> > >
> > com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback
> > (GeneralStatement.java:188)
> > > at
> > >
> > com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList(
> > GeneralStatement.java
> > > :123)
> > > at
> > > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(
> > SqlMapExecutorDelegate.java:615)
> > > at
> > > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList (
> > SqlMapExecutorDelegate.java:589)
> > > at
> > > com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(
> > SqlMapSessionImpl.java:118)
> > > at
> > > org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient(
> > SqlMapClientTemplate.java:231)
> > > at
> > > org.springframework.orm.ibatis.SqlMapClientTemplate.execute(
> > SqlMapClientTemplate.java:168)
> > > at
> > >
> > org.springframework.orm.ibatis.SqlMapClientTemplate.executeWithListResult(
> > SqlMapClientTemplate.java:190)
> > > at
> > > org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList
> > > (SqlMapClientTemplate.java:229)
> > > at
> > > test.dao.CurveBODAOImpl.selectAll(CreditSpreadBODAOImpl.java :40)
> > > at
> > > testa.dao.TestCurveBODAOImpl.testSelectdAll(
> > TestCreditSpreadBODAOImpl.java:38)
> > > at sun.reflect.NativeMethodAccessorImpl.invoke0 (Native
> > > Method)
> > > at
> > > sun.reflect.NativeMethodAccessorImpl.invoke (
> > NativeMethodAccessorImpl.java:39)
> > > at
> > > sun.reflect.DelegatingMethodAccessorImpl.invoke(
> > DelegatingMethodAccessorImpl.java:25)
> > > at java.lang.reflect.Method.invoke (Method.java:585)
> > > at junit.framework.TestCase.runTest(TestCase.java:154)
> > > at junit.framework.TestCase.runBare(TestCase.java:127)
> > > at
> > > junit.framework.TestResult$1.protect(TestResult.java:106)
> > > at junit.framework.TestResult.runProtected
> > > (TestResult.java:124)
> > > at junit.framework.TestResult.run(TestResult.java:109)
> > > at junit.framework.TestCase.run(TestCase.java:118)
> > > at
> > > junit.framework.TestSuite.runTest(TestSuite.java :208)
> > > at junit.framework.TestSuite.run (TestSuite.java:203)
> > > at
> > > org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(
> > JUnit3TestReference.java:128)
> > > at
> > > org.eclipse.jdt.internal.junit.runner.TestExecution.run (
> > TestExecution.java:38)
> > > at
> > > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(
> > RemoteTestRunner.java:460)
> > > at
> > > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests (
> > RemoteTestRunner.java:673)
> > > at
> > > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run
> > > (RemoteTestRunner.java:386)
> > > at
> > > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main (
> > RemoteTestRunner.java:196)
> > > Caused by: java.lang.NullPointerException
> > > at
> > > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.endTransaction
> > > (SqlMapExecutorDelegate.java:782)
> > > at
> > > com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.endTransaction(
> > SqlMapSessionImpl.java:176)
> > > at
> > > com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.endTransaction(
> > SqlMapClientImpl.java
> > > :154)
> > > at
> > >
> > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.autoEndTransaction(
> > SqlMapExecutorDelegate.java:883)
> > > at
> > > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(
> > SqlMapExecutorDelegate.java
> > > :622)
> > > at
> > > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(
> > SqlMapExecutorDelegate.java:589)
> > > at
> > > com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(
> > SqlMapSessionImpl.java:118)
> > > at
> > > com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(
> > SqlMapClientImpl.java:95)
> > > at
> > > com.ibatis.sqlmap.engine.mapping.result.loader.ResultLoader.getResult(
> > ResultLoader.java:72)
> > > at
> > > com.ibatis.sqlmap.engine.mapping.result.loader.ResultLoader.loadResult
> > > (ResultLoader.java:59)
> > > at
> > >
> > com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.getNestedSelectMappingValue(
> > BasicResultMap.java:502)
> > > at
> > > com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.getResults(
> > BasicResultMap.java
> > > :340)
> > > at
> > > com.ibatis.sqlmap.engine.execution.SqlExecutor.handleResults (
> > SqlExecutor.java:381)
> > > at
> > > com.ibatis.sqlmap.engine.execution.SqlExecutor.handleMultipleResults(
> > SqlExecutor.java:301)
> > > at
> > > com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery
> > > (SqlExecutor.java:190)
> > > at
> > >
> > com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQuery
> > (GeneralStatement.java:205)
> > > at
> > >
> > com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback
> > > (GeneralStatement.java:173)
> > > ... 28 more
> > >
> > >
> > > Have a feeling that I missed something simple :)
> > >
> > > Cheers,
> > > Mike
> > >
> > >
> >
>
>
Re: Semi complex mapping question
Posted by Ted Schrader <te...@gmail.com>.
Thanks for posting your solution; you've answered my question as well!
Tell your boss I said it was okay for you to take the rest of the day off. ;)
Ted
On 21/03/07, Mikael Andersson <ma...@gmail.com> wrote:
> Hi,
> I had a bit of a breakthrough :)
>
> Ditched the subselect; started experimenting with using resultMap and
> groupBy instead of select attribute and got it working.
>
> Important change (for new users):
>
> <resultMap id="CurveBOResult" class=" test.bo.CurveBO"
> groupBy="curveName,curveCategory">
> <result column="CURVE_NAME" jdbcType="CHAR" property="curveName"/>
> <result column="CURVE_CATEGORY" jdbcType="CHAR"
> property="curveCategory"/>
> <result property="curvePoints" resultMap="getCurvePoints"/>
> </resultMap>
>
> I assume this is the preferred way of doing things as well, so I'm happy.
>
> Thanks,
> Mike
>
>
> On 21/03/07, Ted Schrader <te...@gmail.com> wrote:
> > Hi Mike,
> >
> > I see where you found the composite key syntax in the Developer's
> > Guide ("Composite Keys or Multiple Complex Parameters Properties").
> >
> > The guide does make it sound like the values are being passed in to
> > the nested select. I've run out of ideas.
> >
> > However, when you get this approach working, this will not avoid N+1
> > selects from being executed; iBATIS will simply execute them for you
> > with a single invocation of queryForXXXX().
> >
> >
> > To the list: the guide also gives the impression that the composite
> > key syntax can only be used with nested statements and not with the
> > "groupBy" attribute. Is this true?
> >
> > Thanks,
> >
> > Ted
> >
>
>
Re: Semi complex mapping question
Posted by Mikael Andersson <ma...@gmail.com>.
Hi,
I had a bit of a breakthrough :)
Ditched the subselect; started experimenting with using resultMap and
groupBy instead of select attribute and got it working.
Important change (for new users):
<resultMap id="CurveBOResult" class=" test.bo.CurveBO"
groupBy="curveName,curveCategory">
<result column="CURVE_NAME" jdbcType="CHAR" property="curveName"/>
<result column="CURVE_CATEGORY" jdbcType="CHAR"
property="curveCategory"/>
<result property="curvePoints" resultMap="getCurvePoints"/>
</resultMap>
I assume this is the preferred way of doing things as well, so I'm happy.
Thanks,
Mike
On 21/03/07, Ted Schrader <te...@gmail.com> wrote:
>
> Hi Mike,
>
> I see where you found the composite key syntax in the Developer's
> Guide ("Composite Keys or Multiple Complex Parameters Properties").
>
> The guide does make it sound like the values are being passed in to
> the nested select. I've run out of ideas.
>
> However, when you get this approach working, this will not avoid N+1
> selects from being executed; iBATIS will simply execute them for you
> with a single invocation of queryForXXXX().
>
>
> To the list: the guide also gives the impression that the composite
> key syntax can only be used with nested statements and not with the
> "groupBy" attribute. Is this true?
>
> Thanks,
>
> Ted
>
Re: Semi complex mapping question
Posted by Ted Schrader <te...@gmail.com>.
Hi Mike,
I see where you found the composite key syntax in the Developer's
Guide ("Composite Keys or Multiple Complex Parameters Properties").
The guide does make it sound like the values are being passed in to
the nested select. I've run out of ideas.
However, when you get this approach working, this will not avoid N+1
selects from being executed; iBATIS will simply execute them for you
with a single invocation of queryForXXXX().
To the list: the guide also gives the impression that the composite
key syntax can only be used with nested statements and not with the
"groupBy" attribute. Is this true?
Thanks,
Ted
Re: Semi complex mapping question
Posted by Mikael Andersson <ma...@gmail.com>.
Hi
Thanks for the help so far.
I thought that this bit:
<result property="curvePoints"
select="getCurvePoints"
column="{curveName=CURVE_NAME,curveCategory=CURVE_CATEGORY}"/
Would pass values into the "getCurvePoints" select under the names curveName
and curveCategory??
I'm going to fire off some questionts here :) :
I am only passing null into the selectAll, how would that affect the
subquery?
If I don't specify a parameterClass for the subquery I thought it used the
names defined under the column attribute on the element calling the
subquery. Is this correct?
When I meant suggestions it was about better usage of iBATIS, afraid that
the database model is out of my control. By the way; the data you prototyped
is what could be in the model.
Confession: I'm no database guru, so I may be missing things obvious to more
experienced db guys/gals.
Many thanks,
Mikael
On 21/03/07, Ted Schrader <te...@gmail.com> wrote:
>
> Hi Mike,
>
> Okay, I think I know what the immediate problem is. Again, your
> nested query goes like this:
> <select id="getCurvePoints" resultMap="CurvePointBOResult">
> select
> VALUE, OFFSET
> from dbo.CURVE
> where CURVE_NAME = #curveName#
> and CURVE_CATEGORY = #curveCategory#
> </select>
>
> So, if you pass in null as your parameter object:
> >> getSqlMapClientTemplate().queryForList("selectAll", null);
>
> iBATIS will have big problems inserting the #curveName# and
> #curveCategory# values. Since the <select> is nested under the name
> "curvePoints, that's why you see
>
> --- Check the result mapping for the >>>>>'curvePoints'<<<<<< property.
> --- Cause: java.lang.NullPointerException ;
>
> (>>>> and <<<<< added for emphasis).
>
>
> In regards to getting suggestions for better approaches, seeing some
> example data would help. Does your data look something like this?
>
> CURVE_NAME CURVE_CATEGORY OFFSET VALUE
>
> ----------------------------------------------------------------------------------------
> '45DegreeCurve' 'boring' 0 0
> '45DegreeCurve' 'boring' 1 1
> 'coolCurve' 'exponential' 0 0
> 'coolCurve' 'exponential' 1 1
> 'coolCurve' 'exponential' 2 4
> 'coolCurve' 'exponential' 3 9
> 'coolCurve' 'exponential' 4 16
> 'coolCurve' 'exponential' 5 25
>
>
> One strategy is to use the "groupBy" attribute of a result map.
> However, I'm not sure if this will easily work with your composite key
> set-up. Again, this is why seeing some example data would be helpful.
>
> Also, I admit that I have not used N+1 solutions, but I'm sure others
> on the list will lend a hand once we can give them more details.
>
> Ted
>
>
> On 21/03/07, Mikael Andersson <ma...@gmail.com> wrote:
> > Hi Ted,
> > just started using iBATIS and the manual contains a similar approach,
> that
> > is why I did it that way. I figured I would get that working and then
> tackle
> > the problem of N+1 select statements.
> >
> > The table contains lots and lots of curves, where each curve has many
> curve
> > points. Stupid design I know, loads of redundancy...
> > The primary keys in the table are CURVE_NAME,CURVE_CATEGORY and OFFSET ,
> > which gives me one point on a curve.
> >
> > The columns CURVE_NAME and CURVE_CATEGORY whil give me a list of points
> > defining a complete curve.
> >
> > Hope the above makes some sence.
> >
> > I used Abator to generate the code, and then manually changed stuff.
> This is
> > how it is called in the DAO:
> >
> > public List<CurveBO> selectAll() {
> > List<CurveBO> list =
> > (List<CurveBO>)getSqlMapClientTemplate().queryForList("selectAll",
> > null);
> > return list;
> > }
> >
> >
> > Thanks,
> > Mike
> >
> >
> > On 21/03/07, Ted Schrader <teds.mailing.lists@gmail.com > wrote:
> > > Hi Mike,
> > >
> > > How are you calling the mapped statement on the Java side?
> > >
> > > Also, it's not clear why you're going to the trouble of a nested
> > > select ("getCurvePoints") when it seems from your table design that
> > > there will be one and only one x-y pair for a primary key. Would you
> > > elaborate on why you are attempting this approach?
> > >
> > > Ted
> > >
> > > On 21/03/07, Mikael Andersson <ma...@gmail.com> wrote:
> > > > Hi,
> > > > I'm new to iBATIS and need some help with a problem I'm having.
> > > >
> > > > I have a table called CURVE which contains curve points for loads of
> > curves
> > > > (graph curves), where each curve can have a dynamic number of curve
> > points.
> > > >
> > > > Table structure:
> > > > CURVE_NAME | CURVE_CATEGORY | OFFSET | VALUE
> > > >
> > > > CURVE_NAME and CURVE_CATEGORY are the primary keys, OFFSET is the
> > x-value
> > > > and VALUE is the y-value
> > > >
> > > > SqlMap :
> > > >
> > > > <resultMap id="CurveBOResult" class=" test.bo.CurveBO">
> > > > <result column="CURVE_NAME" jdbcType="CHAR"
> property="curveName"/>
> > > > <result column="CURVE_CATEGORY" jdbcType="CHAR"
> > > > property="curveCategory"/>
> > > > <result property="curvePoints" select="getCurvePoints"
> > > >
> > column="{curveName=CURVE_NAME,curveCategory=CURVE_CATEGORY}"/>
> > > > </resultMap>
> > > >
> > > > <resultMap id="CurvePointBOResult" class=" test.bo.CurvePointBO">
> > > > <result column="VALUE" jdbcType="DOUBLE" property="value"/>
> > > > <result column="OFFSET" jdbcType="DOUBLE" property="offset"/>
> > > > </resultMap>
> > > >
> > > > <select id="selectAll" resultMap="CurveBOResult">
> > > > select distinct
> > > > CURVE_NAME, CURVE_CATEGORY
> > > > from dbo.CURVE
> > > > </select>
> > > >
> > > > <select id="getCurvePoints" resultMap="CurvePointBOResult">
> > > > select
> > > > VALUE, OFFSET
> > > > from dbo.CURVE
> > > > where CURVE_NAME = #curveName#
> > > > and CURVE_CATEGORY = #curveCategory#
> > > > </select>
> > > >
> > > > CurveBO.java has a: private List<CurvePointBO> curvePoints = new
> > > > ArrayList<CurvePointBO>(19);
> > > > with public getter and setter.
> > > >
> > > > When I run this I get the following error message:
> > > >
> > > > org.springframework.jdbc.UncategorizedSQLException:
> > > > SqlMapClient operation; uncategorized SQLException for SQL []; SQL
> state
> > > > [null]; error code [0];
> > > > --- The error occurred in sqlmap/dbo_CURVES_SqlMap.xml.
> > > > --- The error occurred while applying a result map.
> > > > --- Check the dbo_CURVE.CreditSpreadBOResult.
> > > > --- Check the result mapping for the 'curvePoints' property.
> > > > --- Cause: java.lang.NullPointerException ; nested exception is
> > > > com.ibatis.common.jdbc.exception.NestedSQLException:
> > > > --- The error occurred in sqlmap/dbo_CURVE_SqlMap.xml.
> > > > --- The error occurred while applying a result map.
> > > > --- Check the dbo_CURVE.CurveBOResult.
> > > > --- Check the result mapping for the 'curvePoints' property.
> > > > --- Cause: java.lang.NullPointerException
> > > > com.ibatis.common.jdbc.exception.NestedSQLException:
> > > > --- The error occurred in sqlmap/dbo_CURVE_SqlMap.xml.
> > > > --- The error occurred while applying a result map.
> > > > --- Check the dbo_CURVE.CurveBOResult.
> > > > --- Check the result mapping for the 'curvePoints' property.
> > > > --- Cause: java.lang.NullPointerException
> > > > at
> > > >
> >
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback
> (GeneralStatement.java:188)
> > > > at
> > > >
> >
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList
> > (GeneralStatement.java
> > > > :123)
> > > > at
> > > >
> > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(
> SqlMapExecutorDelegate.java:615)
> > > > at
> > > >
> > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList
> > (SqlMapExecutorDelegate.java:589)
> > > > at
> > > >
> > com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(
> SqlMapSessionImpl.java:118)
> > > > at
> > > >
> > org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient
> > (SqlMapClientTemplate.java:231)
> > > > at
> > > >
> > org.springframework.orm.ibatis.SqlMapClientTemplate.execute(
> SqlMapClientTemplate.java:168)
> > > > at
> > > >
> >
> org.springframework.orm.ibatis.SqlMapClientTemplate.executeWithListResult
> > (SqlMapClientTemplate.java:190)
> > > > at
> > > >
> > org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList
> > > > (SqlMapClientTemplate.java:229)
> > > > at
> > > >
> > test.dao.CurveBODAOImpl.selectAll(CreditSpreadBODAOImpl.java
> > :40)
> > > > at
> > > >
> > testa.dao.TestCurveBODAOImpl.testSelectdAll(
> TestCreditSpreadBODAOImpl.java:38)
> > > > at sun.reflect.NativeMethodAccessorImpl.invoke0
> > (Native
> > > > Method)
> > > > at
> > > > sun.reflect.NativeMethodAccessorImpl.invoke
> > (NativeMethodAccessorImpl.java:39)
> > > > at
> > > >
> > sun.reflect.DelegatingMethodAccessorImpl.invoke(
> DelegatingMethodAccessorImpl.java:25)
> > > > at java.lang.reflect.Method.invoke (Method.java:585)
> > > > at
> > junit.framework.TestCase.runTest(TestCase.java:154)
> > > > at
> > junit.framework.TestCase.runBare(TestCase.java:127)
> > > > at
> > > >
> > junit.framework.TestResult$1.protect(TestResult.java:106)
> > > > at junit.framework.TestResult.runProtected
> > > > (TestResult.java:124)
> > > > at junit.framework.TestResult.run(TestResult.java:109)
> > > > at junit.framework.TestCase.run(TestCase.java:118)
> > > > at
> > > > junit.framework.TestSuite.runTest(TestSuite.java :208)
> > > > at junit.framework.TestSuite.run (TestSuite.java:203)
> > > > at
> > > >
> > org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(
> JUnit3TestReference.java:128)
> > > > at
> > > > org.eclipse.jdt.internal.junit.runner.TestExecution.run
> > (TestExecution.java:38)
> > > > at
> > > >
> > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(
> RemoteTestRunner.java:460)
> > > > at
> > > >
> > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests
> > (RemoteTestRunner.java:673)
> > > > at
> > > >
> > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run
> > > > (RemoteTestRunner.java:386)
> > > > at
> > > >
> > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main
> > (RemoteTestRunner.java:196)
> > > > Caused by: java.lang.NullPointerException
> > > > at
> > > >
> > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.endTransaction
> > > > (SqlMapExecutorDelegate.java:782)
> > > > at
> > > >
> > com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.endTransaction(
> SqlMapSessionImpl.java:176)
> > > > at
> > > >
> > com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.endTransaction(
> SqlMapClientImpl.java
> > > > :154)
> > > > at
> > > >
> > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.autoEndTransaction(
> SqlMapExecutorDelegate.java:883)
> > > > at
> > > >
> > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(
> SqlMapExecutorDelegate.java
> > > > :622)
> > > > at
> > > >
> > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(
> SqlMapExecutorDelegate.java:589)
> > > > at
> > > >
> > com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(
> > SqlMapSessionImpl.java:118)
> > > > at
> > > >
> > com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(
> SqlMapClientImpl.java:95)
> > > > at
> > > >
> > com.ibatis.sqlmap.engine.mapping.result.loader.ResultLoader.getResult
> > (ResultLoader.java:72)
> > > > at
> > > >
> > com.ibatis.sqlmap.engine.mapping.result.loader.ResultLoader.loadResult
> > > > (ResultLoader.java:59)
> > > > at
> > > >
> >
> com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.getNestedSelectMappingValue
> > (BasicResultMap.java:502)
> > > > at
> > > >
> > com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.getResults(
> BasicResultMap.java
> > > > :340)
> > > > at
> > > >
> > com.ibatis.sqlmap.engine.execution.SqlExecutor.handleResults
> > (SqlExecutor.java:381)
> > > > at
> > > >
> > com.ibatis.sqlmap.engine.execution.SqlExecutor.handleMultipleResults(
> SqlExecutor.java:301)
> > > > at
> > > >
> > com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery
> > > > (SqlExecutor.java:190)
> > > > at
> > > >
> >
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQuery
> (GeneralStatement.java:205)
> > > > at
> > > >
> >
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback
> > > > (GeneralStatement.java:173)
> > > > ... 28 more
> > > >
> > > >
> > > > Have a feeling that I missed something simple :)
> > > >
> > > > Cheers,
> > > > Mike
> > > >
> > > >
> > >
> >
> >
>
Re: Semi complex mapping question
Posted by Ted Schrader <te...@gmail.com>.
Hi Mike,
Okay, I think I know what the immediate problem is. Again, your
nested query goes like this:
<select id="getCurvePoints" resultMap="CurvePointBOResult">
select
VALUE, OFFSET
from dbo.CURVE
where CURVE_NAME = #curveName#
and CURVE_CATEGORY = #curveCategory#
</select>
So, if you pass in null as your parameter object:
>> getSqlMapClientTemplate().queryForList("selectAll", null);
iBATIS will have big problems inserting the #curveName# and
#curveCategory# values. Since the <select> is nested under the name
"curvePoints, that's why you see
--- Check the result mapping for the >>>>>'curvePoints'<<<<<< property.
--- Cause: java.lang.NullPointerException ;
(>>>> and <<<<< added for emphasis).
In regards to getting suggestions for better approaches, seeing some
example data would help. Does your data look something like this?
CURVE_NAME CURVE_CATEGORY OFFSET VALUE
----------------------------------------------------------------------------------------
'45DegreeCurve' 'boring' 0 0
'45DegreeCurve' 'boring' 1 1
'coolCurve' 'exponential' 0 0
'coolCurve' 'exponential' 1 1
'coolCurve' 'exponential' 2 4
'coolCurve' 'exponential' 3 9
'coolCurve' 'exponential' 4 16
'coolCurve' 'exponential' 5 25
One strategy is to use the "groupBy" attribute of a result map.
However, I'm not sure if this will easily work with your composite key
set-up. Again, this is why seeing some example data would be helpful.
Also, I admit that I have not used N+1 solutions, but I'm sure others
on the list will lend a hand once we can give them more details.
Ted
On 21/03/07, Mikael Andersson <ma...@gmail.com> wrote:
> Hi Ted,
> just started using iBATIS and the manual contains a similar approach, that
> is why I did it that way. I figured I would get that working and then tackle
> the problem of N+1 select statements.
>
> The table contains lots and lots of curves, where each curve has many curve
> points. Stupid design I know, loads of redundancy...
> The primary keys in the table are CURVE_NAME,CURVE_CATEGORY and OFFSET ,
> which gives me one point on a curve.
>
> The columns CURVE_NAME and CURVE_CATEGORY whil give me a list of points
> defining a complete curve.
>
> Hope the above makes some sence.
>
> I used Abator to generate the code, and then manually changed stuff. This is
> how it is called in the DAO:
>
> public List<CurveBO> selectAll() {
> List<CurveBO> list =
> (List<CurveBO>)getSqlMapClientTemplate().queryForList("selectAll",
> null);
> return list;
> }
>
>
> Thanks,
> Mike
>
>
> On 21/03/07, Ted Schrader <teds.mailing.lists@gmail.com > wrote:
> > Hi Mike,
> >
> > How are you calling the mapped statement on the Java side?
> >
> > Also, it's not clear why you're going to the trouble of a nested
> > select ("getCurvePoints") when it seems from your table design that
> > there will be one and only one x-y pair for a primary key. Would you
> > elaborate on why you are attempting this approach?
> >
> > Ted
> >
> > On 21/03/07, Mikael Andersson <ma...@gmail.com> wrote:
> > > Hi,
> > > I'm new to iBATIS and need some help with a problem I'm having.
> > >
> > > I have a table called CURVE which contains curve points for loads of
> curves
> > > (graph curves), where each curve can have a dynamic number of curve
> points.
> > >
> > > Table structure:
> > > CURVE_NAME | CURVE_CATEGORY | OFFSET | VALUE
> > >
> > > CURVE_NAME and CURVE_CATEGORY are the primary keys, OFFSET is the
> x-value
> > > and VALUE is the y-value
> > >
> > > SqlMap :
> > >
> > > <resultMap id="CurveBOResult" class=" test.bo.CurveBO">
> > > <result column="CURVE_NAME" jdbcType="CHAR" property="curveName"/>
> > > <result column="CURVE_CATEGORY" jdbcType="CHAR"
> > > property="curveCategory"/>
> > > <result property="curvePoints" select="getCurvePoints"
> > >
> column="{curveName=CURVE_NAME,curveCategory=CURVE_CATEGORY}"/>
> > > </resultMap>
> > >
> > > <resultMap id="CurvePointBOResult" class=" test.bo.CurvePointBO">
> > > <result column="VALUE" jdbcType="DOUBLE" property="value"/>
> > > <result column="OFFSET" jdbcType="DOUBLE" property="offset"/>
> > > </resultMap>
> > >
> > > <select id="selectAll" resultMap="CurveBOResult">
> > > select distinct
> > > CURVE_NAME, CURVE_CATEGORY
> > > from dbo.CURVE
> > > </select>
> > >
> > > <select id="getCurvePoints" resultMap="CurvePointBOResult">
> > > select
> > > VALUE, OFFSET
> > > from dbo.CURVE
> > > where CURVE_NAME = #curveName#
> > > and CURVE_CATEGORY = #curveCategory#
> > > </select>
> > >
> > > CurveBO.java has a: private List<CurvePointBO> curvePoints = new
> > > ArrayList<CurvePointBO>(19);
> > > with public getter and setter.
> > >
> > > When I run this I get the following error message:
> > >
> > > org.springframework.jdbc.UncategorizedSQLException:
> > > SqlMapClient operation; uncategorized SQLException for SQL []; SQL state
> > > [null]; error code [0];
> > > --- The error occurred in sqlmap/dbo_CURVES_SqlMap.xml.
> > > --- The error occurred while applying a result map.
> > > --- Check the dbo_CURVE.CreditSpreadBOResult.
> > > --- Check the result mapping for the 'curvePoints' property.
> > > --- Cause: java.lang.NullPointerException ; nested exception is
> > > com.ibatis.common.jdbc.exception.NestedSQLException:
> > > --- The error occurred in sqlmap/dbo_CURVE_SqlMap.xml.
> > > --- The error occurred while applying a result map.
> > > --- Check the dbo_CURVE.CurveBOResult.
> > > --- Check the result mapping for the 'curvePoints' property.
> > > --- Cause: java.lang.NullPointerException
> > > com.ibatis.common.jdbc.exception.NestedSQLException:
> > > --- The error occurred in sqlmap/dbo_CURVE_SqlMap.xml.
> > > --- The error occurred while applying a result map.
> > > --- Check the dbo_CURVE.CurveBOResult.
> > > --- Check the result mapping for the 'curvePoints' property.
> > > --- Cause: java.lang.NullPointerException
> > > at
> > >
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:188)
> > > at
> > >
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList
> (GeneralStatement.java
> > > :123)
> > > at
> > >
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:615)
> > > at
> > >
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList
> (SqlMapExecutorDelegate.java:589)
> > > at
> > >
> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118)
> > > at
> > >
> org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient
> (SqlMapClientTemplate.java:231)
> > > at
> > >
> org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:168)
> > > at
> > >
> org.springframework.orm.ibatis.SqlMapClientTemplate.executeWithListResult
> (SqlMapClientTemplate.java:190)
> > > at
> > >
> org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList
> > > (SqlMapClientTemplate.java:229)
> > > at
> > >
> test.dao.CurveBODAOImpl.selectAll(CreditSpreadBODAOImpl.java
> :40)
> > > at
> > >
> testa.dao.TestCurveBODAOImpl.testSelectdAll(TestCreditSpreadBODAOImpl.java:38)
> > > at sun.reflect.NativeMethodAccessorImpl.invoke0
> (Native
> > > Method)
> > > at
> > > sun.reflect.NativeMethodAccessorImpl.invoke
> (NativeMethodAccessorImpl.java:39)
> > > at
> > >
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
> > > at java.lang.reflect.Method.invoke (Method.java:585)
> > > at
> junit.framework.TestCase.runTest(TestCase.java:154)
> > > at
> junit.framework.TestCase.runBare(TestCase.java:127)
> > > at
> > >
> junit.framework.TestResult$1.protect(TestResult.java:106)
> > > at junit.framework.TestResult.runProtected
> > > (TestResult.java:124)
> > > at junit.framework.TestResult.run(TestResult.java:109)
> > > at junit.framework.TestCase.run(TestCase.java:118)
> > > at
> > > junit.framework.TestSuite.runTest(TestSuite.java :208)
> > > at junit.framework.TestSuite.run (TestSuite.java:203)
> > > at
> > >
> org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:128)
> > > at
> > > org.eclipse.jdt.internal.junit.runner.TestExecution.run
> (TestExecution.java:38)
> > > at
> > >
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
> > > at
> > >
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests
> (RemoteTestRunner.java:673)
> > > at
> > >
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run
> > > (RemoteTestRunner.java:386)
> > > at
> > >
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main
> (RemoteTestRunner.java:196)
> > > Caused by: java.lang.NullPointerException
> > > at
> > >
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.endTransaction
> > > (SqlMapExecutorDelegate.java:782)
> > > at
> > >
> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.endTransaction(SqlMapSessionImpl.java:176)
> > > at
> > >
> com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.endTransaction(SqlMapClientImpl.java
> > > :154)
> > > at
> > >
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.autoEndTransaction(SqlMapExecutorDelegate.java:883)
> > > at
> > >
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java
> > > :622)
> > > at
> > >
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:589)
> > > at
> > >
> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(
> SqlMapSessionImpl.java:118)
> > > at
> > >
> com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(SqlMapClientImpl.java:95)
> > > at
> > >
> com.ibatis.sqlmap.engine.mapping.result.loader.ResultLoader.getResult
> (ResultLoader.java:72)
> > > at
> > >
> com.ibatis.sqlmap.engine.mapping.result.loader.ResultLoader.loadResult
> > > (ResultLoader.java:59)
> > > at
> > >
> com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.getNestedSelectMappingValue
> (BasicResultMap.java:502)
> > > at
> > >
> com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.getResults(BasicResultMap.java
> > > :340)
> > > at
> > >
> com.ibatis.sqlmap.engine.execution.SqlExecutor.handleResults
> (SqlExecutor.java:381)
> > > at
> > >
> com.ibatis.sqlmap.engine.execution.SqlExecutor.handleMultipleResults(SqlExecutor.java:301)
> > > at
> > >
> com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery
> > > (SqlExecutor.java:190)
> > > at
> > >
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQuery(GeneralStatement.java:205)
> > > at
> > >
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback
> > > (GeneralStatement.java:173)
> > > ... 28 more
> > >
> > >
> > > Have a feeling that I missed something simple :)
> > >
> > > Cheers,
> > > Mike
> > >
> > >
> >
>
>
Re: Semi complex mapping question
Posted by Mikael Andersson <ma...@gmail.com>.
Hi Ted,
just started using iBATIS and the manual contains a similar approach, that
is why I did it that way. I figured I would get that working and then tackle
the problem of N+1 select statements.
The table contains lots and lots of curves, where each curve has many curve
points. Stupid design I know, loads of redundancy...
The primary keys in the table are CURVE_NAME,CURVE_CATEGORY and OFFSET ,
which gives me one point on a curve.
The columns CURVE_NAME and CURVE_CATEGORY whil give me a list of points
defining a complete curve.
Hope the above makes some sence.
I used Abator to generate the code, and then manually changed stuff. This is
how it is called in the DAO:
public List<CurveBO> selectAll() {
List<CurveBO> list =
(List<CurveBO>)getSqlMapClientTemplate().queryForList("selectAll", null);
return list;
}
Thanks,
Mike
On 21/03/07, Ted Schrader <te...@gmail.com> wrote:
>
> Hi Mike,
>
> How are you calling the mapped statement on the Java side?
>
> Also, it's not clear why you're going to the trouble of a nested
> select ("getCurvePoints") when it seems from your table design that
> there will be one and only one x-y pair for a primary key. Would you
> elaborate on why you are attempting this approach?
>
> Ted
>
> On 21/03/07, Mikael Andersson <ma...@gmail.com> wrote:
> > Hi,
> > I'm new to iBATIS and need some help with a problem I'm having.
> >
> > I have a table called CURVE which contains curve points for loads of
> curves
> > (graph curves), where each curve can have a dynamic number of curve
> points.
> >
> > Table structure:
> > CURVE_NAME | CURVE_CATEGORY | OFFSET | VALUE
> >
> > CURVE_NAME and CURVE_CATEGORY are the primary keys, OFFSET is the
> x-value
> > and VALUE is the y-value
> >
> > SqlMap :
> >
> > <resultMap id="CurveBOResult" class=" test.bo.CurveBO">
> > <result column="CURVE_NAME" jdbcType="CHAR" property="curveName"/>
> > <result column="CURVE_CATEGORY" jdbcType="CHAR"
> > property="curveCategory"/>
> > <result property="curvePoints" select="getCurvePoints"
> > column="{curveName=CURVE_NAME,curveCategory=CURVE_CATEGORY}"/>
> > </resultMap>
> >
> > <resultMap id="CurvePointBOResult" class=" test.bo.CurvePointBO">
> > <result column="VALUE" jdbcType="DOUBLE" property="value"/>
> > <result column="OFFSET" jdbcType="DOUBLE" property="offset"/>
> > </resultMap>
> >
> > <select id="selectAll" resultMap="CurveBOResult">
> > select distinct
> > CURVE_NAME, CURVE_CATEGORY
> > from dbo.CURVE
> > </select>
> >
> > <select id="getCurvePoints" resultMap="CurvePointBOResult">
> > select
> > VALUE, OFFSET
> > from dbo.CURVE
> > where CURVE_NAME = #curveName#
> > and CURVE_CATEGORY = #curveCategory#
> > </select>
> >
> > CurveBO.java has a: private List<CurvePointBO> curvePoints = new
> > ArrayList<CurvePointBO>(19);
> > with public getter and setter.
> >
> > When I run this I get the following error message:
> >
> > org.springframework.jdbc.UncategorizedSQLException:
> > SqlMapClient operation; uncategorized SQLException for SQL []; SQL state
> > [null]; error code [0];
> > --- The error occurred in sqlmap/dbo_CURVES_SqlMap.xml.
> > --- The error occurred while applying a result map.
> > --- Check the dbo_CURVE.CreditSpreadBOResult.
> > --- Check the result mapping for the 'curvePoints' property.
> > --- Cause: java.lang.NullPointerException ; nested exception is
> > com.ibatis.common.jdbc.exception.NestedSQLException:
> > --- The error occurred in sqlmap/dbo_CURVE_SqlMap.xml.
> > --- The error occurred while applying a result map.
> > --- Check the dbo_CURVE.CurveBOResult.
> > --- Check the result mapping for the 'curvePoints' property.
> > --- Cause: java.lang.NullPointerException
> > com.ibatis.common.jdbc.exception.NestedSQLException:
> > --- The error occurred in sqlmap/dbo_CURVE_SqlMap.xml.
> > --- The error occurred while applying a result map.
> > --- Check the dbo_CURVE.CurveBOResult.
> > --- Check the result mapping for the 'curvePoints' property.
> > --- Cause: java.lang.NullPointerException
> > at
> >
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback
> (GeneralStatement.java:188)
> > at
> >
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList
> (GeneralStatement.java
> > :123)
> > at
> > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(
> SqlMapExecutorDelegate.java:615)
> > at
> > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(
> SqlMapExecutorDelegate.java:589)
> > at
> > com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(
> SqlMapSessionImpl.java:118)
> > at
> > org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient(
> SqlMapClientTemplate.java:231)
> > at
> > org.springframework.orm.ibatis.SqlMapClientTemplate.execute(
> SqlMapClientTemplate.java:168)
> > at
> >
> org.springframework.orm.ibatis.SqlMapClientTemplate.executeWithListResult(
> SqlMapClientTemplate.java:190)
> > at
> > org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList
> > (SqlMapClientTemplate.java:229)
> > at
> > test.dao.CurveBODAOImpl.selectAll(CreditSpreadBODAOImpl.java:40)
> > at
> > testa.dao.TestCurveBODAOImpl.testSelectdAll(
> TestCreditSpreadBODAOImpl.java:38)
> > at sun.reflect.NativeMethodAccessorImpl.invoke0 (Native
> > Method)
> > at
> > sun.reflect.NativeMethodAccessorImpl.invoke(
> NativeMethodAccessorImpl.java:39)
> > at
> > sun.reflect.DelegatingMethodAccessorImpl.invoke(
> DelegatingMethodAccessorImpl.java:25)
> > at java.lang.reflect.Method.invoke (Method.java:585)
> > at junit.framework.TestCase.runTest(TestCase.java:154)
> > at junit.framework.TestCase.runBare(TestCase.java:127)
> > at
> > junit.framework.TestResult$1.protect(TestResult.java:106)
> > at junit.framework.TestResult.runProtected
> > (TestResult.java:124)
> > at junit.framework.TestResult.run(TestResult.java:109)
> > at junit.framework.TestCase.run(TestCase.java:118)
> > at
> > junit.framework.TestSuite.runTest(TestSuite.java:208)
> > at junit.framework.TestSuite.run (TestSuite.java:203)
> > at
> > org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(
> JUnit3TestReference.java:128)
> > at
> > org.eclipse.jdt.internal.junit.runner.TestExecution.run(
> TestExecution.java:38)
> > at
> > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(
> RemoteTestRunner.java:460)
> > at
> > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(
> RemoteTestRunner.java:673)
> > at
> > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run
> > (RemoteTestRunner.java:386)
> > at
> > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(
> RemoteTestRunner.java:196)
> > Caused by: java.lang.NullPointerException
> > at
> > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.endTransaction
> > (SqlMapExecutorDelegate.java:782)
> > at
> > com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.endTransaction(
> SqlMapSessionImpl.java:176)
> > at
> > com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.endTransaction(
> SqlMapClientImpl.java
> > :154)
> > at
> > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.autoEndTransaction(
> SqlMapExecutorDelegate.java:883)
> > at
> > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(
> SqlMapExecutorDelegate.java
> > :622)
> > at
> > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(
> SqlMapExecutorDelegate.java:589)
> > at
> > com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(
> SqlMapSessionImpl.java:118)
> > at
> > com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(
> SqlMapClientImpl.java:95)
> > at
> > com.ibatis.sqlmap.engine.mapping.result.loader.ResultLoader.getResult(
> ResultLoader.java:72)
> > at
> > com.ibatis.sqlmap.engine.mapping.result.loader.ResultLoader.loadResult
> > (ResultLoader.java:59)
> > at
> >
> com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.getNestedSelectMappingValue
> (BasicResultMap.java:502)
> > at
> > com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.getResults(
> BasicResultMap.java
> > :340)
> > at
> > com.ibatis.sqlmap.engine.execution.SqlExecutor.handleResults(
> SqlExecutor.java:381)
> > at
> > com.ibatis.sqlmap.engine.execution.SqlExecutor.handleMultipleResults(
> SqlExecutor.java:301)
> > at
> > com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery
> > (SqlExecutor.java:190)
> > at
> >
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQuery
> (GeneralStatement.java:205)
> > at
> >
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback
> > (GeneralStatement.java:173)
> > ... 28 more
> >
> >
> > Have a feeling that I missed something simple :)
> >
> > Cheers,
> > Mike
> >
> >
>
Re: Semi complex mapping question
Posted by Ted Schrader <te...@gmail.com>.
Hi Mike,
How are you calling the mapped statement on the Java side?
Also, it's not clear why you're going to the trouble of a nested
select ("getCurvePoints") when it seems from your table design that
there will be one and only one x-y pair for a primary key. Would you
elaborate on why you are attempting this approach?
Ted
On 21/03/07, Mikael Andersson <ma...@gmail.com> wrote:
> Hi,
> I'm new to iBATIS and need some help with a problem I'm having.
>
> I have a table called CURVE which contains curve points for loads of curves
> (graph curves), where each curve can have a dynamic number of curve points.
>
> Table structure:
> CURVE_NAME | CURVE_CATEGORY | OFFSET | VALUE
>
> CURVE_NAME and CURVE_CATEGORY are the primary keys, OFFSET is the x-value
> and VALUE is the y-value
>
> SqlMap :
>
> <resultMap id="CurveBOResult" class=" test.bo.CurveBO">
> <result column="CURVE_NAME" jdbcType="CHAR" property="curveName"/>
> <result column="CURVE_CATEGORY" jdbcType="CHAR"
> property="curveCategory"/>
> <result property="curvePoints" select="getCurvePoints"
> column="{curveName=CURVE_NAME,curveCategory=CURVE_CATEGORY}"/>
> </resultMap>
>
> <resultMap id="CurvePointBOResult" class=" test.bo.CurvePointBO">
> <result column="VALUE" jdbcType="DOUBLE" property="value"/>
> <result column="OFFSET" jdbcType="DOUBLE" property="offset"/>
> </resultMap>
>
> <select id="selectAll" resultMap="CurveBOResult">
> select distinct
> CURVE_NAME, CURVE_CATEGORY
> from dbo.CURVE
> </select>
>
> <select id="getCurvePoints" resultMap="CurvePointBOResult">
> select
> VALUE, OFFSET
> from dbo.CURVE
> where CURVE_NAME = #curveName#
> and CURVE_CATEGORY = #curveCategory#
> </select>
>
> CurveBO.java has a: private List<CurvePointBO> curvePoints = new
> ArrayList<CurvePointBO>(19);
> with public getter and setter.
>
> When I run this I get the following error message:
>
> org.springframework.jdbc.UncategorizedSQLException:
> SqlMapClient operation; uncategorized SQLException for SQL []; SQL state
> [null]; error code [0];
> --- The error occurred in sqlmap/dbo_CURVES_SqlMap.xml.
> --- The error occurred while applying a result map.
> --- Check the dbo_CURVE.CreditSpreadBOResult.
> --- Check the result mapping for the 'curvePoints' property.
> --- Cause: java.lang.NullPointerException ; nested exception is
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in sqlmap/dbo_CURVE_SqlMap.xml.
> --- The error occurred while applying a result map.
> --- Check the dbo_CURVE.CurveBOResult.
> --- Check the result mapping for the 'curvePoints' property.
> --- Cause: java.lang.NullPointerException
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in sqlmap/dbo_CURVE_SqlMap.xml.
> --- The error occurred while applying a result map.
> --- Check the dbo_CURVE.CurveBOResult.
> --- Check the result mapping for the 'curvePoints' property.
> --- Cause: java.lang.NullPointerException
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:188)
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList(GeneralStatement.java
> :123)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:615)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:589)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118)
> at
> org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient(SqlMapClientTemplate.java:231)
> at
> org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:168)
> at
> org.springframework.orm.ibatis.SqlMapClientTemplate.executeWithListResult(SqlMapClientTemplate.java:190)
> at
> org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList
> (SqlMapClientTemplate.java:229)
> at
> test.dao.CurveBODAOImpl.selectAll(CreditSpreadBODAOImpl.java:40)
> at
> testa.dao.TestCurveBODAOImpl.testSelectdAll(TestCreditSpreadBODAOImpl.java:38)
> at sun.reflect.NativeMethodAccessorImpl.invoke0 (Native
> Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
> at java.lang.reflect.Method.invoke (Method.java:585)
> at junit.framework.TestCase.runTest(TestCase.java:154)
> at junit.framework.TestCase.runBare(TestCase.java:127)
> at
> junit.framework.TestResult$1.protect(TestResult.java:106)
> at junit.framework.TestResult.runProtected
> (TestResult.java:124)
> at junit.framework.TestResult.run(TestResult.java:109)
> at junit.framework.TestCase.run(TestCase.java:118)
> at
> junit.framework.TestSuite.runTest(TestSuite.java:208)
> at junit.framework.TestSuite.run (TestSuite.java:203)
> at
> org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:128)
> at
> org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run
> (RemoteTestRunner.java:386)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
> Caused by: java.lang.NullPointerException
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.endTransaction
> (SqlMapExecutorDelegate.java:782)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.endTransaction(SqlMapSessionImpl.java:176)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.endTransaction(SqlMapClientImpl.java
> :154)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.autoEndTransaction(SqlMapExecutorDelegate.java:883)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java
> :622)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:589)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(SqlMapClientImpl.java:95)
> at
> com.ibatis.sqlmap.engine.mapping.result.loader.ResultLoader.getResult(ResultLoader.java:72)
> at
> com.ibatis.sqlmap.engine.mapping.result.loader.ResultLoader.loadResult
> (ResultLoader.java:59)
> at
> com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.getNestedSelectMappingValue(BasicResultMap.java:502)
> at
> com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.getResults(BasicResultMap.java
> :340)
> at
> com.ibatis.sqlmap.engine.execution.SqlExecutor.handleResults(SqlExecutor.java:381)
> at
> com.ibatis.sqlmap.engine.execution.SqlExecutor.handleMultipleResults(SqlExecutor.java:301)
> at
> com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery
> (SqlExecutor.java:190)
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQuery(GeneralStatement.java:205)
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback
> (GeneralStatement.java:173)
> ... 28 more
>
>
> Have a feeling that I missed something simple :)
>
> Cheers,
> Mike
>
>