Using Tomcat JDBC Connection Pool in Standalone Java Application

Clarence HoJune 6th, 2012Last Updated: October 26th, 2012

3 327 8 minutes read

This is a guest article from our W4G partner Clarence Ho author of Pro Spring 3 from APress. You may find a discount coupon code for the book at the end of the article, only for the readers of Java Code Geeks! Enjoy!

When using a JDBC connection pool in standalone Java applications that require data access, most of the developers will use either commons-dbcp or c3p0. In this tutorial, we will discuss using the JDBC connection pool in Apache Tomcat web container in standalone Java applications.

One of the new features with Tomcat 7 is the tomcat-jdbc connection pool, which is a replacement to the commons-dbcp connection pool. The main advantages of tomcat-jdbc over commons-dbcp and other connection pool libraries were listed below:

Support for highly concurrent environments and multi core/cpu systems
Commons-dbcp is single-threaded and slow
Commons-dbcp is complex (over 60 classes), while tomcat-jdbc core contains only 8 classes
Support asynchronous connection retrieval
XA connection support
The connection pool object exposes an MBean that can be registered for monitoring purposes
Most of the attributes in common-dbcp were supported, as well as many enhanced attributes
Support of JDBC interceptors

For a detail description and documentation for configuration, please refer to the official documentation page on Apache Tomcat web site.

In this tutorial, we will demonstrate using tomcat-jdbc in developing a simple standalone data access Java application. This application will use the following frameworks and libraries:

Spring Framework 3.1.1
Hibernate 4.1.3
Spring Data JPA 1.1.0
Tomcat JDBC Connection Pool 7.0.27
H2 database 1.3.167
Guava 12.0

The sample was developed using SpringSource Tool Suite and a zipped archive can be downloaded at the end of this article. On the other hand, this tutorial assumes that you already have an understanding on developing JPA applications with Spring and Hibernate.

Dependencies

The project dependencies were managed by Maven. The following is the snippet from the POM file (pom.xml) of the project.

Listing 1 – Project dependencies

<properties>
    <maven.test.failure.ignore>true</maven.test.failure.ignore>
    <spring.framework.version>3.1.1.RELEASE</spring.framework.version>
  <hibernate.version>4.1.3.Final</hibernate.version>
  <spring.data.jpa.version>1.1.0.RELEASE</spring.data.jpa.version>
  <tomcat.dbcp.version>7.0.27</tomcat.dbcp.version>
  <h2.version>1.3.167</h2.version>
  <slf4j.version>1.6.4</slf4j.version>
  <log4j.version>1.2.16</log4j.version>
  <guava.version>12.0</guava.version>
</properties>
 
<dependencies>
 
 <!-- Hibernate -->
 
  <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate-entitymanager</artifactId>       
      <version>${hibernate.version}</version>
  </dependency>
 
 <!-- Spring Framework -->
 
  <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context-support</artifactId>
      <version>${spring.framework.version}</version>
  </dependency>
 
  <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-aop</artifactId>
      <version>${spring.framework.version}</version>
  </dependency>
 
  <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-orm</artifactId>
      <version>${spring.framework.version}</version>
  </dependency>
 
 <!-- Spring Data JPA -->
 
  <dependency>
      <groupId>org.springframework.data</groupId>
      <artifactId>spring-data-jpa</artifactId>
      <version>${spring.data.jpa.version}</version>
  </dependency>
 
 <!-- Tomcat DBCP -->
 
  <dependency>
      <groupId>org.apache.tomcat</groupId>
      <artifactId>tomcat-jdbc</artifactId>
      <version>${tomcat.dbcp.version}</version>
  </dependency>
           
 <!-- Logging -->
 
  <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-api</artifactId>
      <version>${slf4j.version}</version>
  </dependency>
 
  <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>jcl-over-slf4j</artifactId>
      <version>${slf4j.version}</version>
      <scope>runtime</scope>
  </dependency>
 
  <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-log4j12</artifactId>
      <version>${slf4j.version}</version>
      <scope>runtime</scope>
  </dependency>
 
  <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>${log4j.version}</version>
  </dependency>
        
 <!-- Others -->
 
  <dependency>
      <groupId>com.h2database</groupId>
      <artifactId>h2</artifactId>
      <version>${h2.version}</version>
  </dependency>
 
  <dependency>
      <groupId>com.google.guava</groupId>
      <artifactId>guava</artifactId>
      <version>${guava.version}</version>
  </dependency>
</dependencies>

Domain Object Model

The object model is a simple contact information model. Each contact has their first name, last name, and date of birth. Also, each contact will be associated with zero or more hobbies (e.g. swimming, jogging, reading, etc.). In the DOM, there are 2 main classes, namely the Contact and Hobby classes. Listing 2 and 3 shows the code listing of the classes respectively.

Listing 2 – the Contact class

@Entity
@Table(name = "contact")
public class Contact {
 
 private Long id;
 private int version;
 private String firstName;
 private String lastName;
 private Date birthDate;
 private Set<Hobby> hobbies = new HashSet<Hobby>();
  
 @Id
 @GeneratedValue(strategy=GenerationType.IDENTITY)
 @Column(name = "ID")
 public Long getId() {
  return id;
 }
  
 public void setId(Long id) {
  this.id = id;
 }
  
 @Version
 @Column(name = "VERSION")
 public int getVersion() {
  return version;
 }
  
 public void setVersion(int version) {
  this.version = version;
 }
  
 @Column(name = "FIRST_NAME")
 public String getFirstName() {
  return firstName;
 }
  
 public void setFirstName(String firstName) {
  this.firstName = firstName;
 }
 
 @Column(name = "LAST_NAME")
 public String getLastName() {
  return lastName;
 }
  
 public void setLastName(String lastName) {
  this.lastName = lastName;
 }
  
 @Column(name = "BIRTH_DATE")
 @Temporal(TemporalType.DATE)
 public Date getBirthDate() {
  return birthDate;
 }
  
 public void setBirthDate(Date birthDate) {
  this.birthDate = birthDate;
 } 
  
 @ManyToMany
 @JoinTable(name = "contact_hobby_detail", 
       joinColumns = @JoinColumn(name = "CONTACT_ID"), 
       inverseJoinColumns = @JoinColumn(name = "HOBBY_ID"))
 public Set<hobby> getHobbies() {
  return this.hobbies;
 }
 
 public void setHobbies(Set<hobby> hobbies) {
  this.hobbies = hobbies;
 }   
     
 public String toString() {  
  return "Contact - Id: " + id + ", First name: " + firstName 
    + ", Last name: " + lastName + ", Birthday: " + birthDate;
 }  
  
}

Listing 3 – the Hobby class

@Entity
@Table(name = "hobby")
public class Hobby {
 
 private String hobbyId;
 private Set<Contact> contacts = new HashSet<Contact>();
 
 public Hobby() {
 }
 
 public Hobby(String hobbyId) {
  this.hobbyId = hobbyId;
 }
 
 public Hobby(String hobbyId, Set<Contact> contacts) {
  this.hobbyId = hobbyId;
  this.contacts = contacts;
 }
 
 @Id
 @Column(name = "HOBBY_ID")
 public String getHobbyId() {
  return this.hobbyId;
 }
 
 public void setHobbyId(String hobbyId) {
  this.hobbyId = hobbyId;
 }
 
 @ManyToMany
 @JoinTable(name = "contact_hobby_detail", 
       joinColumns = @JoinColumn(name = "HOBBY_ID"), 
       inverseJoinColumns = @JoinColumn(name = "CONTACT_ID"))
 public Set<Contact> getContacts() {
  return this.contacts;
 }
 
 public void setContacts(Set<Contact> contacts) {
  this.contacts = contacts;
 } 
  
}

From Listing 2 and 3, noted that there is a many-to-many relationship between the Contact and Hobby classes.

Database Schema

In this tutorial, we will use H2 in-memory database. There are 3 tables:

CONTACT: the table stores the contact information
HOBBY: the table stores the listing of hobbies available for the application
CONTACT_HOBBY_DETAIL:models the many-to-many relationship between Contact and Hobby classes

Listing 4 and 5 shows the content of the database schema creation script and the testing data population script respectively.

Listing 4 – Database schema creation script (schema.sql)

DROP TABLE IF EXISTS CONTACT;
 
CREATE TABLE CONTACT (
 ID INT NOT NULL AUTO_INCREMENT
 ,FIRST_NAME VARCHAR(60) NOT NULL
 ,LAST_NAME VARCHAR(40) NOT NULL
 ,BIRTH_DATE DATE
 ,VERSION INT NOT NULL DEFAULT 0
 ,UNIQUE UQ_CONTACT_1 (FIRST_NAME, LAST_NAME)
 ,PRIMARY KEY (ID)
);
 
CREATE TABLE HOBBY (
  HOBBY_ID VARCHAR(20) NOT NULL
 ,PRIMARY KEY (HOBBY_ID)
);
 
 
CREATE TABLE CONTACT_HOBBY_DETAIL (
  CONTACT_ID INT NOT NULL
 ,HOBBY_ID VARCHAR(20) NOT NULL
 ,PRIMARY KEY (CONTACT_ID, HOBBY_ID)
 ,CONSTRAINT FK_CONTACT_HOBBY_DETAIL_1 FOREIGN KEY (CONTACT_ID)
  REFERENCES CONTACT (ID) ON DELETE CASCADE
 ,CONSTRAINT FK_CONTACT_HOBBY_DETAIL_2 FOREIGN KEY (HOBBY_ID)
  REFERENCES HOBBY (HOBBY_ID)
);

Listing 5 – Testing data population script (test-data.sql)

insert into contact (first_name, last_name, birth_date) values ('Clarence', 'Ho', '1980-07-30');
insert into contact (first_name, last_name, birth_date) values ('Scott', 'Tiger', '1990-11-02');
 
insert into hobby (hobby_id) values ('Swimming');
insert into hobby (hobby_id) values ('Jogging');
insert into hobby (hobby_id) values ('Programming');
insert into hobby (hobby_id) values ('Movies');
insert into hobby (hobby_id) values ('Reading');
 
insert into contact_hobby_detail(contact_id, hobby_id) values (1, 'Swimming');
insert into contact_hobby_detail(contact_id, hobby_id) values (1, 'Movies'); 
insert into contact_hobby_detail(contact_id, hobby_id) values (2, 'Swimming');

Service Layer

In the service layer, there exist 2 interfaces:

ContactService: provide services for accessing contact information
HobbyService: provide services for accessing hobby information

Listing 6 and 7 show the ContactService and HobbyService interfaces respectively.

Listing 6 – the ContactService interface

public interface ContactService {
 
 public List<Contact> findAll();
  
 public Contact findById(Long id);
  
 public Contact save(Contact contact);
  
}

Listing 7 – the HobbyService interface

public interface HobbyService {
 
 public List<Hobby> findAll();
  
}

Spring Configuration

Let’s take a look on the Spring configurations. Listing 8 shows the data source, transaction and JPA configurations.

Listing 8 – Spring JPA configuration (datasource-tx-jpa.xml)

<!--Tomcat JDBC connection pool configutation -->
<bean id="dataSource" class="org.apache.tomcat.jdbc.pool.DataSource">
   <property name="driverClassName" value="org.h2.Driver" />
   <property name="url" value="jdbc:h2:mem:testdb" />
   <property name="username" value="sa" />
   <property name="password" value="" />
</bean>
 
<!--Intialize the database schema with test data -->
<jdbc:initialize-database data-source="dataSource">
   <jdbc:script location="classpath:schema.sql"/>
   <jdbc:script location="classpath:test-data.sql"/>
</jdbc:initialize-database>
   
<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
   <property name="entityManagerFactory" ref="emf"/>
</bean>
 
<tx:annotation-driven transaction-manager="transactionManager" />
 
<bean id="emf" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
   <property name="dataSource" ref="dataSource" />
   <property name="jpaVendorAdapter">
      <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"/>
   </property>
   <property name="packagesToScan" value="com.skywidesoft.tomcat.dbcp.tutorial.domain"/>
   <property name="jpaProperties">
    <props>
     <prop key="hibernate.dialect">org.hibernate.dialect.H2Dialect</prop>
     <prop key="hibernate.max_fetch_depth">3</prop>
     <prop key="hibernate.jdbc.fetch_size">50</prop>
     <prop key="hibernate.jdbc.batch_size">10</prop>
     <prop key="hibernate.show_sql">true</prop>
    </props>
   </property>
</bean> 
 
<context:annotation-config/>
 
<!--Spring Data JPA Repository Configuration -->
<jpa:repositories base-package="com.skywidesoft.tomcat.dbcp.tutorial.repository"
         entity-manager-factory-ref="emf"
         transaction-manager-ref="transactionManager"/>

Some highlights of the configuration in Listing 8 were listed below:

For the dataSource bean, the class org.apache.tomcat.jdbc.pool.DataSource was used to provide the JDBC DataSource interface for the underlying connection. You will see that the configuration is basically the same as using commons-dbcp.
The <jdbc:initialize-database> tag is Spring 3.1’s support for initializing the database with the database schema and testing data
The <jpa:repositories> tag is to configure the Spring Data JPA’s repository abstraction.

Listing 9 shows the Spring application context configuration.

Listing 9 – Spring application context (app-context.xml)

<import resource="classpath:datasource-tx-jpa.xml"/>
 
        <context:component-scan base-package="com.skywidesoft.tomcat.dbcp.tutorial.service.jpa"/>

Spring Data JPA Repository Abstraction

Spring Data JPA’s repository abstraction provides a simplified approach in developing JPA based data access applications. For details, please refer to the project website. The repository abstraction layer is developed using Java interface. Listing 10 and 11 shows the code listing of the ContactRepository and HobbyRepository interfaces respectively.

Listing 10 – The ContactRepository Interface

public interface ContactRepository extends CrudRepository<Contact, Long>{
 
}

Listing 11 – The HobbyRepository Interface

public interface HobbyRepository extends CrudRepository<Hobby, String>{
 
}

Note that the interface simply extends the Spring Data Common’s CrudRepository<T,ID> interface, which already provides common data access operations (e.g. findAll, findOne, save, delete, etc.).

JPA Implementation Classes

The next step is to develop the JPA implementation of the service layer interfaces in Listing 6 and 7. The classes adopt Spring Framework’s annotations for Spring bean declaration, auto-wiring of dependencies, and transaction requirements, etc. Listing 12 and 13 show the ContactServiceImpl and HobbyServiceImpl classes respectively.

Listing 12 – The ContactServiceImpl class

@Service("contactService")
@Repository
@Transactional
public class ContactServiceImpl implements ContactService {
 
 final static Logger logger = LoggerFactory.getLogger(ContactServiceImpl.class);
  
 @Autowired
 private ContactRepository contactRepository;
  
 @Transactional(readOnly=true)
 public List<Contact> findAll() {
  logger.info("Finding all contacts");
  return Lists.newArrayList(contactRepository.findAll());
 }
 
 @Transactional(readOnly=true)
 public Contact findById(Long id) {
  return contactRepository.findOne(id);
 }
 
 public Contact save(Contact contact) {
  return contactRepository.save(contact);
 }
 
}

Listing 13 – The HobbyServiceImpl class

@Service("hobbyService")
@Repository
@Transactional
public class HobbyServiceImpl implements HobbyService {
 
 @Autowired
 private HobbyRepository hobbyRepository;
 
 @Transactional(readOnly=true)
 public List<Hobby> findAll() {
  return Lists.newArrayList(hobbyRepository.findAll());
 }
 
}

Testing

Let’s see the application in action. Listing 14 shows the ContactServiceTest class, which simply bootstrap the Spring application context from the app-context.xml file, lookup the contactService bean, and invoke the findAll operation to retrieve all the contacts from the database.

Listing 14 – The ContactServiceTest class

public class ContactServiceTest {
 
 public static void main(String[] args) {
 
  GenericXmlApplicationContext ctx = new GenericXmlApplicationContext();
  ctx.load("classpath:app-context.xml");
  ctx.refresh();
   
  ContactService contactService = ctx.getBean("contactService", ContactService.class);
   
  List<Contact> contacts = contactService.findAll(); 
   
  for (Contact contact: contacts) {
   System.out.println(contact);
  }  
 
 }
 
}

Run the above class will produce the following output in the console output window (other non-relevant outputs were omitted):

2012-05-25 13:35:43,552 INFO [com.skywidesoft.tomcat.dbcp.tutorial.service.jpa.ContactServiceImpl] - <Finding all contacts> 2012-05-25 13:35:43,665 DEBUG [org.hibernate.SQL] - <select contact0_.ID as ID0_, contact0_.BIRTH_DATE as BIRTH2_0_, contact0_.FIRST_NAME as FIRST3_0_, contact0_.LAST_NAME as LAST4_0_, contact0_.VERSION as VERSION0_ from contact contact0_> Hibernate: select contact0_.ID as ID0_, contact0_.BIRTH_DATE as BIRTH2_0_, contact0_.FIRST_NAME as FIRST3_0_, contact0_.LAST_NAME as LAST4_0_, contact0_.VERSION as VERSION0_ from contact contact0_ Contact - Id: 1, First name: Clarence, Last name: Ho, Birthday: 1980-07-30 Contact - Id: 2, First name: Scott, Last name: Tiger, Birthday: 1990-11-02

From the above output, you can see that the contact information which was populated by the test-data.sql script was retrieved from the database correctly.

Conclusion

This tutorial presents using Tomcat’s JDBC connection pool in standalone Java applications. Tomcat’s JDBC connection pool is a replacement for the commons-dbcp connection pool, providing a faster and more feature rich JDBC connection pool solution. It’s neat design, high performance, support of highly concurrent environment and multi core/cpu systems make it a compelling choice as the JDBC connection pool provider both in Tomcat’s web container and standalone Java application environments.

Download full Eclipse Maven Project.

Reference: Using Tomcat JDBC Connection Pool in Standalone Java Application from our W4G partner Clarence Ho.

Clarence Ho is lead author of Pro Spring 3 from APress. With Pro Spring 3, you’ll learn Spring basics and core topics, and gain access to the authors’ insights and real–world experiences with remoting, Hibernate, and EJB. Beyond the basics, you’ll learn how to leverage the Spring Framework to build various tiers or parts of an enterprise Java application like transactions, the web and presentations tiers, deployment, and much more. A full sample application allows you to apply many of the technologies and techniques covered in this book and see how they work together.

APress has provided the readers of Java Code Geeks with a discount coupon code. The coupon code is: SPR76 and is valid till July 6, 2012. The code offers a 40% off the eBook only from apress.com.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy