ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mikael Andersson" <mail.mi...@gmail.com>
Subject Re: Semi complex mapping question
Date Wed, 21 Mar 2007 16:31:55 GMT
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 <teds.mailing.lists@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 <mail.micke@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 <mail.micke@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
> > > >
> > > >
> > >
> >
> >
>

Mime
View raw message