Connection Pool Usage?

wagb278 · **Joined:** Sat Aug 11, 2012 5:37 pm **Posts:** 5

Is "Connection Pool" used only when using a database server such a MySQL on localhost, or a remote server?

For a standalone desktop application (Java SE - I think that is what it is referred to) using Hibernate, Spring and HSQLDB as a File database should I use a Connection Pool? I configured BoneCP, but I think that is causing problems. I keep loosing the connection to the database after adding only a few records to tables.

An excerpt of the console log includes the following:

Code:

Hibernate: insert into Category (id, descr, name) values (default, ?, ?)
Hibernate: insert into Category (id, descr, name) values (default, ?, ?)
Hibernate: insert into Category (id, descr, name) values (default, ?, ?)
Hibernate: insert into Category (id, descr, name) values (default, ?, ?)
Hibernate: insert into Category (id, descr, name) values (default, ?, ?)
Hibernate: insert into Category (id, descr, name) values (default, ?, ?)
WARN  09:01:54,452 [spi.SqlExceptionHelper] SQL Error: -1303, SQLState: 08003 
ERROR 09:01:54,453 [spi.SqlExceptionHelper] connection exception: connection does not exist 
WARN  09:01:54,454 [spi.SqlExceptionHelper] SQL Error: -1303, SQLState: 08003 
ERROR 09:01:54,454 [spi.SqlExceptionHelper] connection exception: connection does not exist 
ERROR 09:01:54,455 [interceptor.TransactionInterceptor] Application exception overridden by rollback exception 
org.hibernate.exception.JDBCConnectionException: connection exception: connection does not exist

I suspect the connection pool is causing the errors. Ideas?

The dependencies in my POM (an excerpt) include:

Code:

  <dependencies>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-orm</artifactId>
        <version>3.1.2.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-core</artifactId>
        <version>3.2.1.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>3.2.1.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-tx</artifactId>
      <version>3.1.2.RELEASE</version>
      <type>jar</type>
    </dependency>
    <dependency>
        <groupId>log4j</groupId>
        <artifactId>log4j</artifactId>
        <version>1.2.17</version>
    </dependency>
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-core</artifactId>
        <version>4.1.9.Final</version>
    </dependency>
    <dependency>
        <groupId>javax.persistence</groupId>
        <artifactId>persistence-api</artifactId>
        <version>1.0.2</version>
    </dependency>            
    <dependency>
        <groupId>com.jolbox</groupId>
        <artifactId>bonecp-provider</artifactId>
        <version>0.8.0-alpha1</version>
    </dependency>

Thanks,
Jim

Akash Mittal · **Joined:** Wed Apr 10, 2013 1:02 am **Posts:** 50

I think it's not ideal for in memory and flat file nevertheless
Can you show your bonecp cfg.

wagb278 · **Joined:** Sat Aug 11, 2012 5:37 pm **Posts:** 5

Thanks for the reply -
Here is my Spring configuration file that inlcudes the BoneCP configuration.

Code:

<?xml version="1.0" encoding="UTF-8"?>

<!--<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">-->

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
           http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd
           http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
           http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.2.xsd">

        
   <bean id="helloWorld" class="com.bss.ppi.app.HelloWorld">
       <property name="message" value="Hello World!"/>
   </bean>

   <context:component-scan base-package="com.bss.ppi.utilities" />

   <!-- Add following for Hibernate Persistence -->
    

   <!-- <bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean" autowire="autodetect"> -->
   <bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean" >
        <property name="dataSource" ref="dataSource" />
        <!-- List all your Entity classes here -->
        <property name="annotatedClasses">
            <list>
                <value>com.bss.ppi.entities.Location</value>
                <value>com.bss.ppi.entities.Room</value>
                <value>com.bss.ppi.entities.Item</value>
                <value>com.bss.ppi.entities.Category</value>
            </list>
        </property>

        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop>
                <prop key="hibernate.show_sql">true</prop>
                <prop key="hibernate.hbm2ddl.auto">create</prop>
            </props>
        </property>
    </bean>     
    
    <!-- Spring bean configuration. Tell Spring to bounce off BoneCP -->
    <bean id="dataSource"
          class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
        <property name="targetDataSource">
            <ref local="mainDataSource" />
        </property>
    </bean>
            
    <!-- BoneCP configuration -->
    <bean id="mainDataSource" class="com.jolbox.bonecp.BoneCPDataSource" destroy-method="close">
        <property name="driverClass" value="org.hsqldb.jdbcDriver" />
        <property name="jdbcUrl" value="jdbc:hsqldb:file:PPIdb;shutdown=true;hsqldb.write_delay=false;" />
        <property name="username" value="SA"/>
        <property name="password" value=""/>
        <property name="idleConnectionTestPeriod" value="60"/>
        <property name="idleMaxAge" value="240"/>      
        <property name="maxConnectionsPerPartition" value="60"/>
        <property name="minConnectionsPerPartition" value="20"/>
        <property name="partitionCount" value="3"/>
        <property name="acquireIncrement" value="10"/>                              
        <property name="statementsCacheSize" value="50"/>
        <property name="releaseHelperThreads" value="3"/>
    </bean>
    
    <bean id="txManager"
          class="org.springframework.orm.hibernate4.HibernateTransactionManager">
        <property name="sessionFactory" ref="sessionFactory" />
    </bean>
    <tx:annotation-driven transaction-manager="txManager" />
    
    <context:annotation-config />
    
    <!-- DAO Classes -->
    <!-- Note the usage of abstract="true" and parent="AbstractHibernateDAO" in the implementations-->
    <bean id="AbstractHibernateDAO" abstract="true"
          class="com.bss.ppi.dao.AbstractHibernateDAO"/>
    
    <bean id="LocationDAO" parent="AbstractHibernateDAO"
          class="com.bss.ppi.dao.LocationDAO"/>
    <bean id="RoomDAO" parent="AbstractHibernateDAO"
          class="com.bss.ppi.dao.RoomDAO"/>
    <bean id="ItemDAO" parent="AbstractHibernateDAO"
          class="com.bss.ppi.dao.ItemDAO"/>
    <bean id="CategoryDAO" parent="AbstractHibernateDAO"
          class="com.bss.ppi.dao.CategoryDAO"/>

    <!-- Classes that are dependent upon the DAO classes - Dependency Injection
         using Setter injection (set a class variable with reference to DAO) -->
    <bean id="CategoryServiceImpl" class="com.bss.ppi.service.CategoryServiceImpl">
        <property name="categoryDao"> 
            <ref bean="CategoryDAO"/>
        </property>
    </bean>
    <bean id="RoomServiceImpl" class="com.bss.ppi.service.RoomServiceImpl">
        <property name="roomDao"> 
            <ref bean="RoomDAO"/>
        </property>
    </bean>

</beans>