java.sql.SQLException: Column 'TOPIC_ID' not found.

[volodiaL]

Hello!

I stuck with very strange error. I have table Answer and Topic with one-to-many relationship. Table Answer contains join column 

'topic' that references primary key column 'topic_id' of table topic.

My select query is simple:

<select id="get" parameterType="long" resultMap="retrievedAnswer">

SELECT * FROM answer a where a.answer_id=#{id};

</select>

Result map ''retrievedAnswer' is :

<resultMap type="Answer" id="retrievedAnswer">

    <id property="id" column="answer_id"/>

    <result property="content" column="content"/>

    ...................

    <association property="topic" column="topic" javaType="Topic" select="selectTopic" resultMap="resultTopic"/>

</resultMap>

Select attribute contains id of selectTopic:

<select id="selectTopic" parameterType="long" resultMap="resultTopic">

    SELECT t.topic_id, t.rate,t.publish_date,t.content,t.title,t.viewquantity FROM topic t WHERE t.topic_id=#{id};

</select>

Result map "resultTopic" is :

<resultMap type="Topic" id="resultTopic">

    <id property="id" column="TOPIC_ID"/>

    <result property="rate" column="rate"/>

   .......................

</resultMap>

Running these queries I see good output:

2013-05-10 18:42:57,031 [main] DEBUG java.sql.PreparedStatement- ==>  Executing: SELECT * FROM answer a where a.answer_id=?; 

2013-05-10 18:42:57,031 [main] DEBUG java.sql.PreparedStatement- ==> Parameters: 29(Long)

2013-05-10 18:42:57,062 [main] DEBUG java.sql.ResultSet- <==    Columns: ANSWER_ID, RATE, PUBLISH_DATE, CONTENT, TOPIC, USER

2013-05-10 18:42:57,062 [main] DEBUG java.sql.ResultSet- <==        Row: 29, 99, 2013-05-10 18:42:56.0, A lot of witty info that is content of answer, 11, 3

.................................

2013-05-10 18:42:57,109 [main] DEBUG java.sql.PreparedStatement- ==>  Executing: SELECT t.topic_id as TOPIC_ID, t.rate,t.publish_date,t.content,t.title,t.viewquantity FROM topic t WHERE t.topic_id=?; 

2013-05-10 18:42:57,109 [main] DEBUG java.sql.PreparedStatement- ==> Parameters: 11(Long)

2013-05-10 18:42:57,109 [main] DEBUG java.sql.ResultSet- <==    Columns: TOPIC_ID, RATE, PUBLISH_DATE, CONTENT, TITLE, VIEWQUANTITY

2013-05-10 18:42:57,109 [main] DEBUG java.sql.ResultSet- <==        Row: 11, 0, 2013-01-02 20:40:50.0, ...............................

Unfortunately next there is this error :

java.sql.SQLException: Column 'TOPIC_ID' not found.

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)

at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1166)

at com.mysql.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:3058)

at com.mchange.v2.c3p0.impl.NewProxyResultSet.getLong(NewProxyResultSet.java:2625)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:601)

at org.apache.ibatis.logging.jdbc.ResultSetLogger.invoke(ResultSetLogger.java:31)

at com.sun.proxy.$Proxy20.getLong(Unknown Source)

at org.apache.ibatis.type.LongTypeHandler.getNullableResult(LongTypeHandler.java:17)

at org.apache.ibatis.type.LongTypeHandler.getNullableResult(LongTypeHandler.java:8)

at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:29)

at org.apache.ibatis.executor.resultset.NestedResultSetHandler.createRowKeyForMappedProperties(NestedResultSetHandler.java:232)

at org.apache.ibatis.executor.resultset.NestedResultSetHandler.createRowKey(NestedResultSetHandler.java:209)

at org.apache.ibatis.executor.resultset.NestedResultSetHandler.applyNestedResultMappings(NestedResultSetHandler.java:123)

at org.apache.ibatis.executor.resultset.NestedResultSetHandler.getRowValue(NestedResultSetHandler.java:98)

at org.apache.ibatis.executor.resultset.NestedResultSetHandler.handleRowValues(NestedResultSetHandler.java:67)

at org.apache.ibatis.executor.resultset.FastResultSetHandler.handleResultSet(FastResultSetHandler.java:146)

at org.apache.ibatis.executor.resultset.FastResultSetHandler.handleResultSets(FastResultSetHandler.java:112)

at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:40)

at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:55)

at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:41)

at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:243)

at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:117)

at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:60)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:79)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:73)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:39)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:601)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:350)

at com.sun.proxy.$Proxy16.selectOne(Unknown Source)

at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:155)

at com.javahelp.dao.mybatis.GenericDaoMyBatis.get(GenericDaoMyBatis.java:43)

at com.javahelp.dao.mybatis.GenericDaoMyBatis.get(GenericDaoMyBatis.java:1)

at test.javahelp.dao.GenericDaoTest.testSave(GenericDaoTest.java:48)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:601)

at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)

at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)

at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)

at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)

at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)

at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83)

at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)

at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:231)

at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:88)

at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)

at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)

at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)

at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)

at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)

at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)

at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71)

at org.junit.runners.ParentRunner.run(ParentRunner.java:309)

at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:174)

at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)

at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)

I use mybatis and spring of latest versions.

Please, who does know something of this reply me 'cause I have no idea!

Thank you!

[Iwao AVE!]

Hi,

Try removing the 'resultMap' from the association tag.
'select' and 'resultMap' attributes cannot coexist in the same association tag.

Actually, MyBatis should throw an exception when parsing that mapper.
Are you sure you are using the latest version of MyBatis (3.2.2) ?

Regards,
Iwao

2013/5/11 volodiaL <volodi...@mail.ru>:

> --
> You received this message because you are subscribed to the Google Groups
> "mybatis-user" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to mybatis-user...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>

[volodiaL]

Thank you very much for your reply!

As soon as I changed mybatis jar version to 3.2.2 I received corresponding stacktrace:

Caused by: java.lang.IllegalStateException: Cannot define both nestedQueryId and nestedResultMapId in property topic

I removed resultMap tag and everything works as needed.