mybatis-spring-1.0.0-RC3 and jboss5.1: Closing a result set you left open! Please close it yourself.

[Dmitry Mamonov]

Hi everyone!

Us labeled in title, I'm using mybatis-spring-1.0.0-RC3 with JDBC
DataSource configured in jboss5.1.

I have two questions.

QUESTION FIRST:


I recieved some wired exception from jboss data source (see below).
Any ideas about what is wrong,
or may be I just missed to configure something? (but I belive it
should work as is).

This code is executed within sprnig TransactionTemplate, if so.

19:28:43,136 WARN [WrappedConnection] Closing a result set you left
open! Please close it yourself.
java.lang.Throwable: STACKTRACE
at
org.jboss.resource.adapter.jdbc.WrappedStatement.registerResultSet(WrappedStatement.java:
909)
at
org.jboss.resource.adapter.jdbc.WrappedStatement.getResultSet(WrappedStatement.java:
521)
at
org.apache.ibatis.executor.resultset.FastResultSetHandler.handleResultSets(FastResultSetHandler.java:
94)
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:
216)
at
org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:95)
at
org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:
72)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:
75)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:
69)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:
40)
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:597)
at org.mybatis.spring.SqlSessionTemplate
$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:341)
at $Proxy398.selectOne(Unknown Source)
at
org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:
158)
at
org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:70)
at
org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:35)
at $Proxy400.findExport(Unknown Source)

SECOND QUESTION:
It might be wired, but I'm using @Select statements to perform INSERTS
and UPDATES.
In postgres I can write someting like:
@Select("INSERT INTO client(id, name) values
(nextval('id_seq'),#{name}) RETURNING id")
int insertClient(String name);
So, as result I receive generated identifier of new client. Quite
handy.
But may be someone kwnows, could it cause any problems with
transaction management?
--
Dmitry

[Simone Tripodi]

Привет Dimitri

1) looks like something went wrong while selecting your data and the
exception occurred in a fatal way to break the whole transaction;
please upgrade the mybatis-spring module dependency to 1.0.0 and give
once more try. If the problem persists, don't hesitate to post more
details about your code so we can help you.

2) I don't know if there are issues with your approach, BTW be aware
that mybatis supports as well the generated keys from server side via
jdbc when inserting/updating, please take a look at the user manual!
[1]

имся, all the best
Simo

[1] http://code.google.com/p/mybatis/downloads/list?can=3&q=Docs

http://people.apache.org/~simonetripodi/
http://www.99soft.org/

[Dmitry Mamonov]

> 1) looks like something went wrong while selecting your data and the
> exception occurred in a fatal way to break the whole transaction;

No, it is not a fatal-exception. It is more like house-keeping notice
from jboss for me. To make my code clean.
I have seen some suggests to ignore this message at all. But I prefer
to eliminate this problem, exceptions logging takes a lot of machine-
time.

> please upgrade the mybatis-spring module dependency to 1.0.0 and give
> once more try. If the problem persists, don't hesitate to post more
> details about your code so we can help you.

I have upgraded to latest version of mybatis and mybatis-spring (3.0.4
and 1.0.0), nothing is changed.
I have reviewed code and here what I see:
public List handleResultSets(Statement stmt) throws SQLException {
final List multipleResults = new ArrayList();
final List<ResultMap> resultMaps =
mappedStatement.getResultMaps();
int resultMapCount = resultMaps.size();
int resultSetCount = 0;
ResultSet rs = stmt.getResultSet(); //at
org.apache.ibatis.executor.resultset.FastResultSetHandler.handleResultSets(FastResultSetHandler.java:
94)
validateResultMapsCount(rs,resultMapCount);
while (rs != null && resultMapCount > resultSetCount) {
final ResultMap resultMap = resultMaps.get(resultSetCount);
handleResultSet(rs, resultMap, multipleResults);
rs = getNextResultSet(stmt);
cleanUpAfterHandlingResultSet();
resultSetCount++;
}
return collapseSingleResultList(multipleResults);
}
And this happed even for norml select queries,
like select sum(price) from ...
or select id, name from account where ...

> 2) I don't know if there are issues with your approach, BTW be aware
> that mybatis supports as well the generated keys from server side via
> jdbc when inserting/updating, please take a look at the user manual!

Yes, I know about mybatis support of autogenerated keys.
But some times more complex issues appears, like:
UPDATE notes
SET endpoint=now()
WHERE endpoint='2222-01-01'
RETURNING id, author_id
which returns set of notes-ids and corresponding users. Just as
example.

[Dmitry Mamonov]

But definitely can't see where ResultSet is closed.

public List handleResultSets(Statement stmt) throws SQLException {
final List multipleResults = new ArrayList();
final List<ResultMap> resultMaps =
mappedStatement.getResultMaps();
int resultMapCount = resultMaps.size();
int resultSetCount = 0;
ResultSet rs = stmt.getResultSet(); //at
org.apache.ibatis.executor.resultset.FastResultSetHandler.handleResultSets(FastResultSetHandler.java:
94)
validateResultMapsCount(rs,resultMapCount);
while (rs != null && resultMapCount > resultSetCount) {
final ResultMap resultMap = resultMaps.get(resultSetCount);
handleResultSet(rs, resultMap, multipleResults);

//TODO no rs.close() here,as so as no .close() in
handleResultSet() and it's nested methods as I see.
rs = getNextResultSet(stmt); //TODO no clean-up after all, is it
intentionally?
cleanUpAfterHandlingResultSet();
resultSetCount++;
}
return collapseSingleResultList(multipleResults);
}

[Dmitry Mamonov]

I filed issue with patch corresponding to this problem:
http://code.google.com/p/mybatis/issues/detail?id=228

[Eduardo]

Seems that there are more users with the same problem:
http://groups.google.com/group/mybatis-user/browse_thread/thread/6420c96d65b0567b/d3233eeafb3991e5?lnk=gst&q=close+resultset#d3233eeafb3991e5

[Simone Tripodi]

Hola Edu!!!
if you have spare time and already know how to fix it, can take it in
charge, please?
Unfortunately it's a very hard time for me :(
Many thanks in advance, all the best,
Simo

http://people.apache.org/~simonetripodi/
http://www.99soft.org/

[Eduardo]

Ok Simo!

[Herman Bovens]

Hi Dmitry,
Concerning this second question: I think it can indeed cause problems with transasction management, as I found out I needed to force a commit to be sure the insert is written to the database.  Another thing is that if you use caching, you should tell MyBatis to flush the cache (as normally happens with insert statements, not with select statements) and to not use the cache for this statement (otherwise MyBatis may just get the generated id of a previous call from the cache if the parameter is the same).  I do this by writing the <select> in the mapper xml, and specifying flushCache=true and useCache=false.

Regards,
Herman

2011/1/13 Dmitry Mamonov <dmitry.s...@gmail.com>