CODEWITHSUNDEEP
javamysqlspringtomcatjdbctemplate
David Tzoor
David Tzoor

Reputation: 1077

Setting up Tomcat JDBC connection pool with the Spring JDBCTemplate

I'm trying to setup a Tomcat connection pool (to MySQL) in my Java web app, while using the Spring JDBCTemplate.

This is the Java class to create the connection pool:

@Configuration
public class DataAccessConfiguration {

    @Bean(destroyMethod = "close")
    public javax.sql.DataSource datasource() {
        org.apache.tomcat.jdbc.pool.DataSource ds = new org.apache.tomcat.jdbc.pool.DataSource();
        ds.setDriverClassName("org.h2.Driver");
        ds.setUrl("jdbc:h2:java-config");
        ds.setUsername("sa");
        ds.setPassword("");
        ds.setInitialSize(5);
        ds.setMaxActive(10);
        ds.setMaxIdle(5);
        ds.setMinIdle(2);
        return ds;
    }

    @Bean public JdbcOperations tpl() {
        return new JdbcTemplate(datasource());
    }

}

This is how I get the ApplicationContext (in the main method for example): 

   public static void main(String[] args) {
      ApplicationContext context = 
             new ClassPathXmlApplicationContext("Beans.xml");

How should I define the DataAccessConfiguration class in the Beans.xml file so Spring knows to use it?

**

Update:

**

This is the actual configuration method:

public javax.sql.DataSource datasource() {
        org.apache.tomcat.jdbc.pool.DataSource ds = new org.apache.tomcat.jdbc.pool.DataSource();
        PoolProperties p = new PoolProperties();
        p.setUrl("jdbc:mysql://localhost:3306/mysql");
        p.setDriverClassName("com.mysql.jdbc.Driver");
        p.setUsername("root");
        p.setPassword("");
        p.setJmxEnabled(true);
        p.setTestWhileIdle(false);
        p.setTestOnBorrow(true);
        p.setValidationQuery("SELECT 1");
        p.setTestOnReturn(false);
        p.setValidationInterval(30000);
        p.setTimeBetweenEvictionRunsMillis(30000);
        p.setMaxActive(100);
        p.setInitialSize(10);
        p.setMaxWait(10000);
        p.setRemoveAbandonedTimeout(60);
        p.setMinEvictableIdleTimeMillis(30000);
        p.setMinIdle(10);
        p.setLogAbandoned(true);
        p.setRemoveAbandoned(true);
        p.setJdbcInterceptors(
                "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
                "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
        ds.setPoolProperties(p);
        return ds;
    }

Can you please help rewrite in the Beans.xml?

Upvotes: 1

Views: 19163

Answers (2)

Michal
Michal

Reputation: 2423

For the values passed in Properties setter methods exist. Therefore XML-based bean definition goes like:

<bean  name="dataSource" class="org.apache.tomcat.jdbc.pool.DataSource">
  <property name="url" value="jdbc:mysql://localhost:3306/mysql"/>
  <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
  ....
</bean>

<bean name="tpl" class="org.springframework.jdbc.core.JdbcTemplate">
  <property name="dataSource" ref="dataSource"/>
</bean>

Upvotes: 2

Ran Adler
Ran Adler

Reputation: 3711

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
    destroy-method="close">
    <property name="jdbcUrl" value="#{config['db.url']}" />
    <property name="driverClass" value="#{config['db.driver']}" />
    <property name="user" value="#{config['db.username']}" />
    <property name="password" value="#{config['db.password']}" />
    <property name="acquireIncrement" value="1" />
    <property name="idleConnectionTestPeriod" value="300" />
    <property name="minPoolSize" value="1" />
    <property name="maxPoolSize" value="20" />
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource" />
</bean>

Upvotes: -1

Related Questions