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.
I don’t see zipped archive , where is it ?
Hi Jose,
I just added the link for the archive. Please check it out. Sorry for the inconvenience.
thanks – this is really clear and concise. Much appreciated!