At this moment I'm using DriverManagerDataSource with @Transactional annotation to manage transactions. But all transactions are very very slow, probably because data source open and close connection to db each time.
What data source should I use to speed up transaction?
I am using in my application combination of two approaches. the first one is c3p0 connection pooling, its almost the same solution as chkal sugested. The second approach is to use Spring lazyConnectionDataSourceProxy, which creates lazy loading proxy that loads connection only if you hit the database. This is very useful, when you have second level cache and you are only reading cached data and queries - database wont be hit, and you don't need to acquire connection (which is pretty expensive).
<bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${jdbc.driverClassName}" />
<property name="jdbcUrl" value="${jdbc.url}" />
<property name="user" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<!-- Pool properties -->
<property name="minPoolSize" value="5" />
<property name="initialPoolSize" value="10" />
<property name="maxPoolSize" value="50" />
<property name="maxStatements" value="50" />
<property name="idleConnectionTestPeriod" value="120" />
<property name="maxIdleTime" value="1200" />
</bean>
<bean name="lazyConnectionDataSourceProxy" class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
<property name="targetDataSource" ref="dataSource" />
</bean>
DriverManagerDataSource isn't actually a connection pool and should only be used for testing. You should try BasicDataSource from Apache Commons DBCP. Something like:
<bean id="dataSource" destroy-method="close"
class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="${jdbc.driverClassName}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</bean>
