New Connecions each time???

Hi,

I have my datasource defined in Jboss as TX datasource.  I have 3 different databases in my App

I configured ibatis in my appconfig.cml (spring) as

<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">

                        <property name="configLocation">

                                    <value>/WEB-INF/SqlMapConfig.xml</value>

                        </property>                  

            </bean>

I do not have any datasurce specified for the ibatis.

I am using Ibator to generate the classes and DAOs. I have the following config in the ibator.xml files

<jdbcConnection driverClass="oracle.jdbc.driver.OracleDriver"

                        connectionURL="xyz"

                        userId="ABC" password="aaa">

                        <classPathEntry location="C:\oraclejdbc\oracleJDBC_9.0.2.jar" />

            </jdbcConnection>

All the 3 databases use the same sqlMap.xml

My datasource in Spring is configured as

<bean id="dataSoruce1" class="org.springframework.jndi.JndiObjectFactoryBean">

            <property name="jndiName" value="java:jdbc/dataSoruce1" />

</bean>

My log file shows a new connection for each SQL. Even if  I am implementing transactions. The transactions are successful, however, it uses different connections

2009-11-06 09:30:53,698 DEBUG [java.sql.Connection] {conn-100172} Connection

2009-11-06 09:30:53,698 DEBUG [java.sql.Connection] {conn-100172} Preparing Call: {? = call profile_value (?,?)}

2009-11-06 09:30:53,698 DEBUG [java.sql.PreparedStatement] {pstm-100173} Executing Statement: {? = call profile_value (?,?)}

2009-11-06 09:30:53,698 DEBUG [java.sql.PreparedStatement] {pstm-100173} Parameters: [223, MULTIVIEW_TABLE_ROWS]

2009-11-06 09:30:53,698 DEBUG [java.sql.PreparedStatement] {pstm-100173} Types: [java.lang.Long, java.lang.String]

2009-11-06 09:30:53,714 DEBUG [java.sql.Connection] {conn-100174} Connection

2009-11-06 09:30:53,714 DEBUG [java.sql.Connection] {conn-100174} Preparing Call: {? = call profile_value (?,?)}

2009-11-06 09:30:53,714 DEBUG [java.sql.PreparedStatement] {pstm-100175} Executing Statement: {? = call profile_value (?,?)}

2009-11-06 09:30:53,714 DEBUG [java.sql.PreparedStatement] {pstm-100175} Parameters: [223, REQUIREMENT_CLOSING_CYCLE]

2009-11-06 09:30:53,714 DEBUG [java.sql.PreparedStatement] {pstm-100175} Types: [java.lang.Long, java.lang.String]

2009-11-06 09:30:53,745 DEBUG [java.sql.Connection] {conn-100176} Connection

2009-11-06 09:30:53,761 DEBUG [java.sql.Connection] {conn-100176} Preparing Call: {? = call profile_value (?,?)}

2009-11-06 09:30:53,761 DEBUG [java.sql.PreparedStatement] {pstm-100177} Executing Statement: {? = call profile_value (?,?)}

2009-11-06 09:30:53,761 DEBUG [java.sql.PreparedStatement] {pstm-100177} Parameters: [223, APPLICATION_ENVIRONMENT]

2009-11-06 09:30:53,761 DEBUG [java.sql.PreparedStatement] {pstm-100177} Types: [java.lang.Long, java.lang.String]

And for Sqls

2009-11-06 09:30:52,183 DEBUG [java.sql.Connection] {conn-100156} Preparing Statement:           select *     from USER_LOGIN   where  (    LOGIN =   ?  )                             

2009-11-06 09:30:52,183 DEBUG [java.sql.PreparedStatement] {pstm-100157} Executing Statement:           select *     from USER_LOGIN   where  (    LOGIN =   ?  )                             

2009-11-06 09:30:52,183 DEBUG [java.sql.PreparedStatement] {pstm-100157} Parameters: [JEVANS]

2009-11-06 09:30:52,183 DEBUG [java.sql.PreparedStatement] {pstm-100157} Types: [java.lang.String]

2009-11-06 09:30:52,198 DEBUG [java.sql.Connection] {conn-100159} Connection

2009-11-06 09:30:52,198 DEBUG [java.sql.Connection] {conn-100159} Preparing Statement:           SELECT  * from …..

Does this means… new connection for each statement?

And every 10 to 15 days, my server runs too slow and would have to restart the server.

I am really confused with all this

Any theories about this?

Vinaya

I have not used a similar configuration as yours, but nothing you include below leads me to believe you are using a connection pool so either:

1)      Everything uses the same connection as shown in your <jdbcConnection> tag [which is probably not the case based on the log entries you included], or

2)      Yes, you are getting a new connection for each SQL statement.  iBatis does not “magically” handle transaction demarcation nor your connection configuration.  The key is your Spring datasource bean which you are looking up via JNDI.  You do not say what that JNDI datasource is but that datasource WILL define the connection behavior.

Hi,

I have my datasource defined in Jboss as TX datasource.  I have 3 different databases in my App

I configured ibatis in my appconfig.cml (spring) as

<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">

                        <property name="configLocation">

                                    <value>/WEB-INF/SqlMapConfig.xml</value>

                        </property>                  

            </bean>

I do not have any datasurce specified for the ibatis.

I am using Ibator to generate the classes and DAOs. I have the following config in the ibator.xml files

<jdbcConnection driverClass="oracle.jdbc.driver.OracleDriver"

                        connectionURL="xyz"

                        userId="ABC" password="aaa">

                        <classPathEntry location="C:\oraclejdbc\oracleJDBC_9.0.2.jar" />

            </jdbcConnection>

All the 3 databases use the same sqlMap.xml

My datasource in Spring is configured as

<bean id="dataSoruce1" class="org.springframework.jndi.JndiObjectFactoryBean">

            <property name="jndiName" value="java:jdbc/dataSoruce1" />

</bean>

My log file shows a new connection for each SQL. Even if  I am implementing transactions. The transactions are successful, however, it uses different connections

2009-11-06 09:30:53,698 DEBUG [java.sql.Connection] {conn-100172} Connection

2009-11-06 09:30:53,698 DEBUG [java.sql.Connection] {conn-100172} Preparing Call: {? = call profile_value (?,?)}

2009-11-06 09:30:53,698 DEBUG [java.sql.PreparedStatement] {pstm-100173} Executing Statement: {? = call profile_value (?,?)}

2009-11-06 09:30:53,698 DEBUG [java.sql.PreparedStatement] {pstm-100173} Parameters: [223, MULTIVIEW_TABLE_ROWS]

2009-11-06 09:30:53,698 DEBUG [java.sql.PreparedStatement] {pstm-100173} Types: [java.lang.Long, java.lang.String]

2009-11-06 09:30:53,714 DEBUG [java.sql.Connection] {conn-100174} Connection

2009-11-06 09:30:53,714 DEBUG [java.sql.Connection] {conn-100174} Preparing Call: {? = call profile_value (?,?)}

2009-11-06 09:30:53,714 DEBUG [java.sql.PreparedStatement] {pstm-100175} Executing Statement: {? = call profile_value (?,?)}

2009-11-06 09:30:53,714 DEBUG [java.sql.PreparedStatement] {pstm-100175} Parameters: [223, REQUIREMENT_CLOSING_CYCLE]

2009-11-06 09:30:53,714 DEBUG [java.sql.PreparedStatement] {pstm-100175} Types: [java.lang.Long, java.lang.String]

2009-11-06 09:30:53,745 DEBUG [java.sql.Connection] {conn-100176} Connection

2009-11-06 09:30:53,761 DEBUG [java.sql.Connection] {conn-100176} Preparing Call: {? = call profile_value (?,?)}

2009-11-06 09:30:53,761 DEBUG [java.sql.PreparedStatement] {pstm-100177} Executing Statement: {? = call profile_value (?,?)}

2009-11-06 09:30:53,761 DEBUG [java.sql.PreparedStatement] {pstm-100177} Parameters: [223, APPLICATION_ENVIRONMENT]

2009-11-06 09:30:53,761 DEBUG [java.sql.PreparedStatement] {pstm-100177} Types: [java.lang.Long, java.lang.String]

And for Sqls

2009-11-06 09:30:52,183 DEBUG [java.sql.Connection] {conn-100156} Preparing Statement:           select *     from USER_LOGIN   where  (    LOGIN =   ?  )                             

2009-11-06 09:30:52,183 DEBUG [java.sql.PreparedStatement] {pstm-100157} Executing Statement:           select *     from USER_LOGIN   where  (    LOGIN =   ?  )                             

2009-11-06 09:30:52,183 DEBUG [java.sql.PreparedStatement] {pstm-100157} Parameters: [JEVANS]

2009-11-06 09:30:52,183 DEBUG [java.sql.PreparedStatement] {pstm-100157} Types: [java.lang.String]

2009-11-06 09:30:52,198 DEBUG [java.sql.Connection] {conn-100159} Connection

2009-11-06 09:30:52,198 DEBUG [java.sql.Connection] {conn-100159} Preparing Statement:           SELECT  * from …..

Does this means… new connection for each statement?

And every 10 to 15 days, my server runs too slow and would have to restart the server.

I am really confused with all this

Any theories about this?

Vinaya