How to do it...

Using the MySQL server configured in Chapter 1, Getting Started with Spring, let us now scrutinize some popular connection pool libraries that can be used to perform JDBC transactions:

1. The first few steps will be devoted to the listing of needed Maven libraries for Spring 5.0, especially Spring JDBC dependency and MySQL 5.7 connector. With regard to its connection, add this current MySQL-Java connector to the Maven dependencies:

<dependency> 
    <groupId>mysql</groupId> 
    <artifactId>mysql-connector-java</artifactId> 
    <version>5.1.40</version> 
</dependency>

1. Next, we will be doing some comparative analysis on which database JDBC resource pooling best fits with our applications. Consider the following third-party libraries to be included in our Maven repository:

<!-- Apache DBCP Connection Pooling --> 
    <dependency> 
      <groupId>org.apache.commons</groupId> 
      <artifactId>commons-dbcp2</artifactId> 
      <version>2.1.1</version> 
    </dependency> 
    <!-- C3P0 --> 
    <dependency> 
      <groupId>com.mchange</groupId> 
      <artifactId>c3p0</artifactId> 
      <version>0.9.5.2</version> 
    </dependency> 
    <!-- Tomcat JDBC Connection Pooling --> 
    <dependency> 
      <groupId>org.apache.tomcat</groupId> 
      <artifactId>tomcat-jdbc</artifactId> 
      <version>9.0.0.M15</version> 
    </dependency> 
    <!-- Hikari Connection Pooling --> 
    <dependency> 
      <groupId>com.zaxxer</groupId> 
      <artifactId>HikariCP</artifactId> 
      <version>2.5.1</version> 
</dependency> 

1. At this point, let us use the Spring Test Framework to validate the capability of each JDBC resource pooling library by creating a test SQL script for MySQL's world schema. To enable Spring Test, add the following Maven dependencies in the test scope:

<dependency> 
  <groupId>org.springframework</groupId> 
  <artifactId>spring-test</artifactId> 
  <version>${spring.version}</version> 
  <scope>test</scope> 
</dependency> 
<dependency> 
  <groupId>junit</groupId> 
  <artifactId>junit</artifactId> 
  <version>4.12</version> 
  <scope>test</scope> 
  <exclusions> 
    <exclusion> 
      <groupId>org.hamcrest</groupId> 
      <artifactId>hamcrest-core</artifactId> 
    </exclusion> 
  </exclusions> 
</dependency> 
<dependency> 
  <groupId>org.hamcrest</groupId> 
  <artifactId>hamcrest-library</artifactId> 
  <version>1.3</version> 
  <scope>test</scope> 
</dependency> 

1. Create a property file, src\main\resources\config\jdbc.properties, containing the necessary details for database connectivity:

jdbc.driverClassName=com.mysql.jdbc.Driver 
jdbc.url=jdbc:mysql://localhost:3306/world?autoReconnect=true 
&useSSL=true&serverSslCert=classpath:config/spring5packt.crt 
jdbc.username=root 
jdbc.password=spring5mysql 

1. In order for Spring components to read all the properties in jdbc.properties, inject into the web context root container the org.springframework.context.support.PropertySourcesPlaceholderConfigurer class and make reference to jdbc.properties through @PropertySource:

@EnableWebMvc 
@ComponentScan(basePackages="org.packt.dissect.mvc") 
@PropertySource("classpath:config/jdbc.properties") 
@Configuration 
public class SpringDispatcherConfig extends  
     WebMvcConfigurerAdapter{ 
 
   // refer to sources 
    @Bean 
    public static PropertySourcesPlaceholderConfigurer 
       propertyConfig() { 
      return new PropertySourcesPlaceholderConfigurer(); 
    } 
}

1. Include also all the JavaScript, CSS, images, and all other static resources inside SpringDispatcherConfig by overriding its method addResourceHandlers(). Also include some web-related configurations like caching:

import org.springframework.web.servlet.config.annotation.ResourceHandlerRegistry; 
// refer to sources 
@Override 
   public void addResourceHandlers(ResourceHandlerRegistry registry) { 
      registry 
         .addResourceHandler("/css/**") 
         .addResourceLocations("/js/**") 
         .setCachePeriod(31556926); 
   } 

1. Now, create a new JavaConfig context named SpringDbConfig, which contains all @Bean related to database connectivity. The first injection is the creation of the java.sql.DataSource which implements the Connection object per user access. The DataSource needs to retrieve the JDBC details in @PropertySource("classpath:config/jdbc.properties") through the Enviornment class, which throws PropertyVetoException when used:

@Configuration 
@EnableWebMvc 
@ComponentScan(basePackages =  
     "org.packt.dissect.mvc.model.data") 
public class SpringDbConfig { 
 
   @Autowired 
   private Environment environment; 
 
   @Bean 
   public DataSource dataSource() throws  
      PropertyVetoException { } 

1. Implement the first DataSource using Spring's built-in DataSource implementation, the org.springframework.jdbc.datasource.DriverManagerDataSource:

@Configuration 
@EnableWebMvc 
@ComponentScan(basePackages = "org.packt.dissect.mvc.model.data") 
public class SpringDbConfig { 
 
  @Autowired 
  private Environment environment; 
 
  @Bean 
  public DataSource dataSource() throws  
      PropertyVetoException { 
    DriverManagerDataSource dataSource = 
       new DriverManagerDataSource(); 
    dataSource.setDriverClassName(environment 
.getProperty("jdbc.driverClassName")); 
    dataSource.setUrl(environment.getProperty("jdbc.url")); 
    dataSource.setUsername(environment 
.getProperty("jdbc.username")); 
    dataSource.setPassword(environment 
.getProperty("jdbc.password")); 
    return dataSource; 
} 

1. To measure the performance of each JDBC connection pooling, let's use Metrics API DropWizard to report the statistics of resource pooling running time, given 1,000 simulated users accessing the database. Add the following Maven dependencies in order to use this library.

<dependency>
   <groupId>com.codahale.metrics</groupId> 
    <artifactId>metrics-core</artifactId> 
    <version>3.0.2</version> 
</dependency> 

1. Create a test class TestDbPool in src\test\java, with ConsoleReporter, MetricRegsitry, and Timer for the performance testing:

@RunWith(SpringJUnit4ClassRunner.class) 
@WebAppConfiguration 
@ContextConfiguration(classes = {SpringDbConfig.class,  
       SpringDispatcherConfig.class}) 
public class TestDbPool { 
  
  @Autowired 
  private DataSource dataSource; 
  
  private static final int MAX_ITERATIONS = 1000; 
  private ConsoleReporter logReporter; 
  private Timer timer; 
  
  @Before 
  public void init() { 
 
      MetricRegistry metricRegistry = new MetricRegistry(); 
      this.logReporter = ConsoleReporter 
              .forRegistry(metricRegistry) 
              .build(); 
      logReporter.start(1, TimeUnit.MINUTES); 
      timer = metricRegistry.timer("connection"); 
  } 
   
  @Test 
  public void testOpenCloseConnections() throws  
      SQLException { 
 
      for (int i = 0; i < MAX_ITERATIONS; i++) { 
          Context context = timer.time(); 
          Connection conn = dataSource.getConnection(); 
          Statement stmt = conn.createStatement(); 
          stmt.executeQuery("select * from city"); 
          conn.close(); 
          context.stop(); 
      } 
      logReporter.report(); 
   } 
} 

1. Execute TestDbPool using the Eclipse STS JUnit plugin:

1. Check the statistics shown on the console view:

------ Timers -----------------connection
count = 1000
mean rate = 140.19 calls/second
1-minute rate = 128.20 calls/second
5-minute rate = 128.20 calls/second
15-minute rate = 128.20 calls/second
min = 5.36 milliseconds
max = 839.60 milliseconds
mean = 7.10 milliseconds
stddev = 26.44 milliseconds
median = 5.91 milliseconds
75% <= 6.24 milliseconds
95% <= 7.24 milliseconds
98% <= 8.81 milliseconds
99% <= 17.81 milliseconds
99.9% <= 838.81 milliseconds

1. Apply the test to the following third-party database connection pooling implementations and examine the results:

/* 
    BasicDataSource dataSource = new BasicDataSource(); 
    dataSource.setDriverClassName(environment 
.getProperty("jdbc.driverClassName")); 
    dataSource.setUrl(environment 
.getProperty("jdbc.url")); 
    dataSource.setUsername(environment 
.getProperty("jdbc.username")); 
    dataSource.setPassword(environment 
.getProperty("jdbc.password")); 
    dataSource.setMaxTotal(100); 
    */ 
    
 /* 
    ComboPooledDataSource dataSource = 
 new ComboPooledDataSource(); 
   dataSource.setDriverClass(environment 
.getProperty("jdbc.driverClassName")); 
   dataSource.setJdbcUrl(environment 
.getProperty("jdbc.url")); 
   dataSource.setUser(environment 
.getProperty("jdbc.username")); 
   dataSource.setPassword(environment 
.getProperty("jdbc.password")); 
   dataSource.setMaxPoolSize(100); 
      */ 
    
/* 
    org.apache.tomcat.jdbc.pool.DataSource dataSource =  
new org.apache.tomcat.jdbc.pool.DataSource(); 
    PoolProperties props = new PoolProperties(); 
    props.setUrl(environment.getProperty("jdbc.url")); 
    props.setDriverClassName(environment 
.getProperty("jdbc.driverClassName")); 
    props.setUsername(environment 
.getProperty("jdbc.username")); 
    props.setPassword(environment 
.getProperty("jdbc.password")); 
    props.setMaxActive(100); 
    dataSource.setPoolProperties(props); 
     */ 
          
   HikariDataSource dataSource = new HikariDataSource(); 
   dataSource.setMaximumPoolSize(100); 
   dataSource.setDriverClassName(environment 
.getProperty("jdbc.driverClassName")); 
   dataSource.setJdbcUrl(environment 
.getProperty("jdbc.url")); 
   dataSource.setUsername(environment 
.getProperty("jdbc.username")); 
   dataSource.setPassword(environment 
.getProperty("jdbc.password")); 
   dataSource.setMaximumPoolSize(100);