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 Filipe David Manana <fd...@ieee.org> on 2008/05/27 16:09:52 UTC
iBATIS querying pattern for web applications
Hello,
I am developing a webapp and using iBATIS for the first time. I configured
iBATIS to use a JDBC transaction manager and a JNDI datasource, provided by
Tomcat+DBCP. When my webapp is deployed, a Servlet executes and initializes
iBATIS :
SqlMapClient sqlMap = null;
Reader reader = null;
try
{
reader = Resources.getResourceAsReader(configFile);
}
catch( Exception ex )
{
String error = "Could not get Reader for iBATIS config file: " +
ex;
log.fatal(error);
throw new DatabaseException(error);
}
try
{
sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
}
catch( Exception ex )
{
String error = "Could not create iBATIS SqlMapClient object: " +
ex;
log.fatal(error);
throw new DatabaseException(error);
}
This initialization is done only once. For each http request, I use the
sqlMap object to get a new session, execute select queries (through
queryForList method) and close the session. The pattern is like this:
SqlMapSession session = sqlMap.openSession();
List list = null;
list = (List) session.queryForList("queryId");
session.close();
Although I frequently get the SQLException :
java.sql.SQLException: ORA-02391: exceeded simultaneous
SESSIONS_PER_USER limit
Am I doing something wrong? I am the only user. My Tomcat+DBCP configuration
is supposedly ok, as it is the same I used for another application (using
Hibernate and same Database Server). It seems to me that iBATIS is somehow
not releasing the DB connections.
I am using iBATIS 2.3.0
cheers
--
Filipe David Manana,
fdmanana@ieee.org
"First they ignore you, then they laugh at you, then they fight you, then
you win."
Re: iBATIS querying pattern for web applications
Posted by Nicholoz Koka Kiknadze <ki...@gmail.com>.
Can you look from another side, i.e. check database sessions when the the
exception occurs. I'd look at last sql executed in those sessions to find
if there's same sql that's stuck or long running. It could tell you which
part of your code to check... Last time I've seen that exception it was pure
JDBC where a guy forgot to close a statement and most of sleeping database
connections showed that statement was the last one.
On Tue, May 27, 2008 at 7:08 PM, Filipe David Manana <fd...@ieee.org>
wrote:
> Not really. I was catching exceptions, just omitted them in the email for
> the sake of shortness :).
> So, the possibility of leaving sessions open is strange, as there are no
> more than 1 request at a time, as the queries are very simple and with small
> result sets, so the life period of sessions should be very short, less than
> a few seconds maybe.
>
>
> On Tue, May 27, 2008 at 4:28 PM, Nicholoz Koka Kiknadze <
> kiknadze@gmail.com> wrote:
>
>> Can your query raise exception that's handled elsewhere, so that
>> session.close() is not called? Try this:
>>
>>
>> SqlMapSession session = sqlMap.openSession();
>> List list = null;
>> try {
>> list = (List) session.queryForList("queryId");
>> }
>> catch (Exception e)
>> {
>> log.....
>> }
>> finally {
>> session.close();
>> }
>>
>>
>> On Tue, May 27, 2008 at 6:09 PM, Filipe David Manana <fd...@ieee.org>
>> wrote:
>>
>>> Hello,
>>>
>>> I am developing a webapp and using iBATIS for the first time. I
>>> configured iBATIS to use a JDBC transaction manager and a JNDI datasource,
>>> provided by Tomcat+DBCP. When my webapp is deployed, a Servlet executes and
>>> initializes iBATIS :
>>>
>>> SqlMapClient sqlMap = null;
>>> Reader reader = null;
>>>
>>> try
>>> {
>>> reader = Resources.getResourceAsReader(configFile);
>>> }
>>> catch( Exception ex )
>>> {
>>> String error = "Could not get Reader for iBATIS config file: " +
>>> ex;
>>> log.fatal(error);
>>>
>>> throw new DatabaseException(error);
>>> }
>>>
>>> try
>>> {
>>> sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
>>> }
>>> catch( Exception ex )
>>> {
>>> String error = "Could not create iBATIS SqlMapClient object: " +
>>> ex;
>>> log.fatal(error);
>>>
>>> throw new DatabaseException(error);
>>> }
>>>
>>>
>>> This initialization is done only once. For each http request, I use the
>>> sqlMap object to get a new session, execute select queries (through
>>> queryForList method) and close the session. The pattern is like this:
>>>
>>> SqlMapSession session = sqlMap.openSession();
>>> List list = null;
>>>
>>> list = (List) session.queryForList("queryId");
>>> session.close();
>>>
>>> Although I frequently get the SQLException :
>>>
>>> java.sql.SQLException: ORA-02391: exceeded simultaneous
>>> SESSIONS_PER_USER limit
>>>
>>> Am I doing something wrong? I am the only user. My Tomcat+DBCP
>>> configuration is supposedly ok, as it is the same I used for another
>>> application (using Hibernate and same Database Server). It seems to me that
>>> iBATIS is somehow not releasing the DB connections.
>>> I am using iBATIS 2.3.0
>>>
>>> cheers
>>>
>>> --
>>> Filipe David Manana,
>>> fdmanana@ieee.org
>>>
>>> "First they ignore you, then they laugh at you, then they fight you, then
>>> you win."
>>>
>>
>>
>
>
> --
> Filipe David Manana,
> fdmanana@ieee.org
>
> "First they ignore you, then they laugh at you, then they fight you, then
> you win."
>
Re: iBATIS querying pattern for web applications
Posted by Filipe David Manana <fd...@ieee.org>.
Not really. I was catching exceptions, just omitted them in the email for
the sake of shortness :).
So, the possibility of leaving sessions open is strange, as there are no
more than 1 request at a time, as the queries are very simple and with small
result sets, so the life period of sessions should be very short, less than
a few seconds maybe.
On Tue, May 27, 2008 at 4:28 PM, Nicholoz Koka Kiknadze <ki...@gmail.com>
wrote:
> Can your query raise exception that's handled elsewhere, so that
> session.close() is not called? Try this:
>
>
> SqlMapSession session = sqlMap.openSession();
> List list = null;
> try {
> list = (List) session.queryForList("queryId");
> }
> catch (Exception e)
> {
> log.....
> }
> finally {
> session.close();
> }
>
>
> On Tue, May 27, 2008 at 6:09 PM, Filipe David Manana <fd...@ieee.org>
> wrote:
>
>> Hello,
>>
>> I am developing a webapp and using iBATIS for the first time. I configured
>> iBATIS to use a JDBC transaction manager and a JNDI datasource, provided by
>> Tomcat+DBCP. When my webapp is deployed, a Servlet executes and initializes
>> iBATIS :
>>
>> SqlMapClient sqlMap = null;
>> Reader reader = null;
>>
>> try
>> {
>> reader = Resources.getResourceAsReader(configFile);
>> }
>> catch( Exception ex )
>> {
>> String error = "Could not get Reader for iBATIS config file: " +
>> ex;
>> log.fatal(error);
>>
>> throw new DatabaseException(error);
>> }
>>
>> try
>> {
>> sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
>> }
>> catch( Exception ex )
>> {
>> String error = "Could not create iBATIS SqlMapClient object: " +
>> ex;
>> log.fatal(error);
>>
>> throw new DatabaseException(error);
>> }
>>
>>
>> This initialization is done only once. For each http request, I use the
>> sqlMap object to get a new session, execute select queries (through
>> queryForList method) and close the session. The pattern is like this:
>>
>> SqlMapSession session = sqlMap.openSession();
>> List list = null;
>>
>> list = (List) session.queryForList("queryId");
>> session.close();
>>
>> Although I frequently get the SQLException :
>>
>> java.sql.SQLException: ORA-02391: exceeded simultaneous
>> SESSIONS_PER_USER limit
>>
>> Am I doing something wrong? I am the only user. My Tomcat+DBCP
>> configuration is supposedly ok, as it is the same I used for another
>> application (using Hibernate and same Database Server). It seems to me that
>> iBATIS is somehow not releasing the DB connections.
>> I am using iBATIS 2.3.0
>>
>> cheers
>>
>> --
>> Filipe David Manana,
>> fdmanana@ieee.org
>>
>> "First they ignore you, then they laugh at you, then they fight you, then
>> you win."
>>
>
>
--
Filipe David Manana,
fdmanana@ieee.org
"First they ignore you, then they laugh at you, then they fight you, then
you win."
Re: iBATIS querying pattern for web applications
Posted by Nicholoz Koka Kiknadze <ki...@gmail.com>.
Can your query raise exception that's handled elsewhere, so that
session.close() is not called? Try this:
SqlMapSession session = sqlMap.openSession();
List list = null;
try {
list = (List) session.queryForList("queryId");
}
catch (Exception e)
{
log.....
}
finally {
session.close();
}
On Tue, May 27, 2008 at 6:09 PM, Filipe David Manana <fd...@ieee.org>
wrote:
> Hello,
>
> I am developing a webapp and using iBATIS for the first time. I configured
> iBATIS to use a JDBC transaction manager and a JNDI datasource, provided by
> Tomcat+DBCP. When my webapp is deployed, a Servlet executes and initializes
> iBATIS :
>
> SqlMapClient sqlMap = null;
> Reader reader = null;
>
> try
> {
> reader = Resources.getResourceAsReader(configFile);
> }
> catch( Exception ex )
> {
> String error = "Could not get Reader for iBATIS config file: " +
> ex;
> log.fatal(error);
>
> throw new DatabaseException(error);
> }
>
> try
> {
> sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
> }
> catch( Exception ex )
> {
> String error = "Could not create iBATIS SqlMapClient object: " +
> ex;
> log.fatal(error);
>
> throw new DatabaseException(error);
> }
>
>
> This initialization is done only once. For each http request, I use the
> sqlMap object to get a new session, execute select queries (through
> queryForList method) and close the session. The pattern is like this:
>
> SqlMapSession session = sqlMap.openSession();
> List list = null;
>
> list = (List) session.queryForList("queryId");
> session.close();
>
> Although I frequently get the SQLException :
>
> java.sql.SQLException: ORA-02391: exceeded simultaneous
> SESSIONS_PER_USER limit
>
> Am I doing something wrong? I am the only user. My Tomcat+DBCP
> configuration is supposedly ok, as it is the same I used for another
> application (using Hibernate and same Database Server). It seems to me that
> iBATIS is somehow not releasing the DB connections.
> I am using iBATIS 2.3.0
>
> cheers
>
> --
> Filipe David Manana,
> fdmanana@ieee.org
>
> "First they ignore you, then they laugh at you, then they fight you, then
> you win."
>