Springboot application with hibernate and c3P0 connection pooling

Just another playground example. This time it demonstrates the usage of c3P0 for hibernate database environments e.g. for connection pooling. As this is a very basic example, there will be only a reduced UserController that listens on http://: in order to insert that name via hibernate into the database.

Here’s the project tree:

├── pom.xml
├── springboot-hibernate-mysql.iml
└── src
    └── main
        ├── java
        │   └── eu
        │       └── christophburmeister
        │           └── examples
        │               └── springboot
        │                   ├── Application.java
        │                   ├── controller
        │                   │   ├── JmxController.java
        │                   ├── DatabaseConfig.java
        │                   └── model
        │                       ├── UserDao.java
        │                       └── User.java
        └── resources
            ├── application.properties
            └── logback.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>eu.christophburmeister.examples</groupId>
    <artifactId>springboot-hibernate-mysql</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>1.5.1.RELEASE</version>
                <executions>
                    <execution>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>1.5.1.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <version>1.5.1.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>6.0.5</version>
        </dependency>
        <!-- used for connection pooling -->
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-c3p0</artifactId>
            <version>5.2.6.Final</version>
        </dependency>
    </dependencies>
</project>

The standard starter class:

package eu.christophburmeister.examples.springboot;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

}

The model and its data access object:

package eu.christophburmeister.examples.springboot.model;

import javax.persistence.*;
import javax.validation.constraints.NotNull;

@Entity
@Table(name="users")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;

    @NotNull
    private String name;

    public User() {
        // required for hibernate
    }

    public User( String name) { this.name = name; }

    public long getId() { return id; }
    public void setId(long id) { this.id = id; }
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }

}
package eu.christophburmeister.examples.springboot.model;

import javax.transaction.Transactional;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
@Transactional
public class UserDao {

    @Autowired
    private SessionFactory sessionFactory;

    private Session getSession() {
        return sessionFactory.getCurrentSession();
    }

    public void save(User user) {
        getSession().save(user);
    }
}

The controller:

package eu.christophburmeister.examples.springboot.controller;

import eu.christophburmeister.examples.springboot.model.User;
import eu.christophburmeister.examples.springboot.model.UserDao;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

@Controller
@RequestMapping(value="/user")
public class UserController {

    static final Logger LOGGER = LogManager.getLogger(UserController.class.getName());

    @Autowired
    private UserDao userDao;

    @RequestMapping(value="/save")
    @ResponseBody
    public String saveUser(String name) {
        LOGGER.info("received save request with name=" + name);
        try {
            User user = new User(name);
            userDao.save(user);
            LOGGER.info("saved User with name=" + name);
        }
        catch(Exception e) {
            LOGGER.error(e.getMessage());
            return "User '" + name + "' could not be saved! " +  e.getMessage();
        }
        return "User '" + name + "' succesfully saved!";
    }

}

and the place where the persistence magic happens:

package eu.christophburmeister.examples.springboot;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.orm.hibernate5.HibernateTransactionManager;
import org.springframework.orm.hibernate5.LocalSessionFactoryBean;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import java.beans.PropertyVetoException;
import java.util.Properties;

@Configuration
@EnableTransactionManagement
public class DatabaseConfig {

    @Value("${db.driver}")
    private String DB_DRIVER;

    @Value("${db.password}")
    private String DB_PASSWORD;

    @Value("${db.url}")
    private String DB_URL;

    @Value("${db.username}")
    private String DB_USERNAME;

    @Value("${hibernate.dialect}")
    private String HIBERNATE_DIALECT;

    @Value("${hibernate.show_sql}")
    private String HIBERNATE_SHOW_SQL;

    @Value("${hibernate.hbm2ddl.auto}")
    private String HIBERNATE_HBM2DDL_AUTO;

    @Value("${entitymanager.packagesToScan}")
    private String ENTITYMANAGER_PACKAGES_TO_SCAN;

    @Value("${hibernate.c3p0.max_size}")
    private String CONN_POOL_MAX_SIZE;

    @Value("${hibernate.c3p0.min_size}")
    private String CONN_POOL_MIN_SIZE;

    @Value("${hibernate.c3p0.idle_test_period}")
    private String CONN_POOL_IDLE_PERIOD;

    /*
    // this is the standard datasource used by spring : import org.springframework.jdbc.datasource.DriverManagerDataSource;
    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(DB_DRIVER);
        dataSource.setUrl(DB_URL);
        dataSource.setUsername(DB_USERNAME);
        dataSource.setPassword(DB_PASSWORD);
        return dataSource;
    }
    */

    @Bean
    public ComboPooledDataSource dataSource() {
        // a named datasource is best practice for later jmx monitoring
        ComboPooledDataSource dataSource = new ComboPooledDataSource("jupiter");

        try {
            dataSource.setDriverClass(DB_DRIVER);
        } catch (PropertyVetoException pve){
            System.out.println("Cannot load datasource driver (" + DB_DRIVER +") : " + pve.getMessage());
            return null;
        }
        dataSource.setJdbcUrl(DB_URL);
        dataSource.setUser(DB_USERNAME);
        dataSource.setPassword(DB_PASSWORD);
        dataSource.setMinPoolSize(Integer.parseInt(CONN_POOL_MIN_SIZE));
        dataSource.setMaxPoolSize(Integer.parseInt(CONN_POOL_MAX_SIZE));
        dataSource.setMaxIdleTime(Integer.parseInt(CONN_POOL_IDLE_PERIOD));

        return dataSource;
    }

    @Bean
    public LocalSessionFactoryBean sessionFactory() {
        LocalSessionFactoryBean sessionFactoryBean = new LocalSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource());
        sessionFactoryBean.setPackagesToScan(ENTITYMANAGER_PACKAGES_TO_SCAN);
        Properties hibernateProperties = new Properties();
        hibernateProperties.put("hibernate.dialect", HIBERNATE_DIALECT);
        hibernateProperties.put("hibernate.show_sql", HIBERNATE_SHOW_SQL);
        hibernateProperties.put("hibernate.hbm2ddl.auto", HIBERNATE_HBM2DDL_AUTO);
        sessionFactoryBean.setHibernateProperties(hibernateProperties);

        return sessionFactoryBean;
    }

    @Bean
    public HibernateTransactionManager transactionManager() {
        HibernateTransactionManager transactionManager =
                new HibernateTransactionManager();
        transactionManager.setSessionFactory(sessionFactory().getObject());
        return transactionManager;
    }
}

Note that I commented out the standard datasource() method wher the DriverManagerDataSource is used. Instead I will rely on c3P0’s ComboPooledDataSource. That’s why the hibernate-c3p0 dependency has been added to the pom.xml.

The properties that are used here come from application.properties:

# Thymeleaf
spring.thymeleaf.cache: false

# Database
db.driver: com.mysql.cj.jdbc.Driver
db.url: jdbc:mysql://localhost:3306/springbootexample
db.username: paul
db.password: paul_123

# Hibernate
hibernate.dialect: org.hibernate.dialect.MySQL5Dialect
hibernate.show_sql: true
hibernate.hbm2ddl.auto: create
entitymanager.packagesToScan: eu.christophburmeister.examples.springboot.model

# c3p0  pooling
hibernate.c3p0.max_size=20
hibernate.c3p0.min_size=5
hibernate.c3p0.idle_test_period=600

# Logging
logging.file=application.log

And the logback.xml that contains standard file appender from Spring:

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <include resource="org/springframework/boot/logging/logback/defaults.xml" />
    <property name="LOG_FILE" value="${LOG_FILE:-${LOG_PATH:-${LOG_TEMP:-${java.io.tmpdir:-/tmp}}/}spring.log}"/>
    <include resource="org/springframework/boot/logging/logback/file-appender.xml" />

    <root level="INFO">
        <appender-ref ref="FILE" />
    </root>
</configuration>

Running this springboot-application via

$ mvn clean package
$ jav -jar target/springboot-hibernate-mysql-0.0.1-SNAPSHOT.jar

will start the service on localhost:8080 and in logfile you can find the entry where the connection pool is getting configured:

2017-02-04 19:44:29.461  INFO 12302 --- [main] c.m.v.c.i.AbstractPoolBackedDataSource   : Initializing c3p0 pool... 
com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, 
   acquireRetryAttempts -> 30, 
   acquireRetryDelay -> 1000, 
   autoCommitOnClose -> false, 
   automaticTestTable -> null, 
   breakAfterAcquireFailure -> false, 
   checkoutTimeout -> 0, 
   connectionCustomizerClassName -> null, 
   connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, 
   contextClassLoaderSource -> caller, 
   dataSourceName -> jupiter, 
   debugUnreturnedConnectionStackTraces -> false, 
   description -> null, 
   driverClass -> com.mysql.cj.jdbc.Driver, 
   extensions -> {}, 
   factoryClassLocation -> null, 
   forceIgnoreUnresolvedTransactions -> false, 
   forceSynchronousCheckins -> false, 
   forceUseNamedDriverClass -> false, 
   identityToken -> z8kfsx9m2wat6t1gf5ujn|382db087, 
   idleConnectionTestPeriod -> 0, 
   initialPoolSize -> 3, 
   jdbcUrl -> jdbc:mysql://localhost:3306/springboot, 
   maxAdministrativeTaskTime -> 0, 
   maxConnectionAge -> 0, 
   maxIdleTime -> 600, 
   maxIdleTimeExcessConnections -> 0, 
   maxPoolSize -> 20, 
   maxStatements -> 0,
   maxStatementsPerConnection -> 0, 
   minPoolSize -> 5, 
   numHelperThreads -> 3, 
   preferredTestQuery -> null, 
   privilegeSpawnedThreads -> false, 
   properties -> {user=******, password=******}, 
   propertyCycle -> 0, 
   statementCacheNumDeferredCloseThreads -> 0, 
   testConnectionOnCheckin -> false, 
   testConnectionOnCheckout -> false, 
   unreturnedConnectionTimeout -> 0, 
   userOverrides -> {}, 
   usesTraditionalReflectiveProxies -> false 
]

There you can find the configured values for maxPoolSize and minPoolSize as basic parameters for a connection pool.

Another point to remark is that the datasource is called „jupiter“ (because of „Jupiter Ascending movie“, never mind…). This is a preparation for the next step: Monitoring of actually used (busy) connections of that datasoure:
Therefor I created a controller class called „JmxController“ that listens on /jmx/datasource:

package eu.christophburmeister.examples.springboot.controller;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.management.*;
import java.lang.management.ManagementFactory;
import java.util.*;

@Controller
@RequestMapping(value="/jmx")
public class JmxController {

    private List<String> wantedDatasourceParameters = Arrays.asList(
            "dataSourceName",
            "jdbcUrl",
            "driverClass",
            "threadPoolSize",
            "numIdleConnections",
            "numIdleConnectionsAllUsers",
            "numIdleConnectionsDefaultUser",
            "numConnections",
            "numConnectionsAllUsers",
            "numConnectionsDefaultUser",
            "numBusyConnections",
            "numBusyConnectionsAllUsers",
            "numBusyConnectionsDefaultUser",
            "minPoolSize",
            "maxPoolSize",
            "maxIdleTime",
            "initialPoolSize"
    );

    @RequestMapping(value="/datasource")
    @ResponseBody
    public String getDatasource() {
        StringBuilder response = new StringBuilder();

        Map<String, String> datasourceDetails = getDatasourceDetails();

        response.append("<table border='1' width='100' cellspacing='0' cellpadding='0'>");
        for (Map.Entry<String, String> details : datasourceDetails.entrySet()) {
            response.append("<tr><td>"+details.getKey()+"</td><td>"+details.getValue()+"</td></tr>");
        }
        response.append("</table>");

        return response.toString();
    }

    private Map<String, String> getDatasourceDetails(){
        Map<String, String> result = new HashMap<String, String>();

        try {
            MBeanServer mBeanServer = ManagementFactory.getPlatformMBeanServer();
            ObjectName pooldDataSourceName = null;

            // access all c3p0 mbeans
            Set c3P0MBbeanNames = mBeanServer.queryNames(new ObjectName("com.mchange.v2.c3p0:*"),null);

            // find the datasource name
            for (Iterator i = c3P0MBbeanNames.iterator(); i.hasNext();) {
                ObjectName name = (ObjectName) i.next();
                // the name of the pooled data source is sth like
                // com.mchange.v2.c3p0:identityToken=z8kfsx9m2qfi3o1ie5nfx|77e2a6e2,name=jupiter,type=PooledDataSource
                if (name.getCanonicalName().contains("name=jupiter")) {
                    pooldDataSourceName = name;
                }
            }

            MBeanInfo pooldDataSourceInfos = mBeanServer.getMBeanInfo(pooldDataSourceName);
            MBeanAttributeInfo[] attributes = pooldDataSourceInfos.getAttributes();

            for (MBeanAttributeInfo attr : attributes) {
                String attrName = attr.getName();

                if (wantedDatasourceParameters.contains(attrName)) {
                    // finding the value of the attribute
                    Object attrValueObject = mBeanServer.getAttribute(pooldDataSourceName, attrName);
                    if (attrValueObject != null) {
                        result.put(attrName, attrValueObject.toString());
                    }
                }
            }
        } catch (Exception e){
           // do some error handlng
        }

        return result;
    }
}

The above servlet is just a quick-and-dirty solution (!) I can be enriched with all the stuff a serious webinterface needs, like security, layout and error handling. With that solution you have an easy solution for avoiding working with jconsole or jvisualvm via external JMX communication to monitor the current important values of the datasource like busy connections. The result looks like that: