JSF 2, Spring 3, JPA (Hibernate 3), PostgreSQL, c3p0 - everything together (part 1 of 2)

Requirements:

• a working example of JSF 2.0 application with Spring Framework and Spring Security integrated (can be found here)
• installed PostgreSQL database (simple installation variant described here)

You will learn:

• how to use database for loading and storing the data in the sample JSF 2.0  application
• how to access the database from the application using JPA (Hibernate) and Spring
• how to optimize database access by using connection pool (c3p0)

It is a high time to create a real working example with a database. Sample application used in previous posts use no database. We had Spring's managed service named bikeDataProvider acting as a database - all bikes data were created in that service during startup, all bikes data were retrieved from that service, even new bike data were stored internally in the service:

package com.jsfsample.services.impl;
...
@Service("bikeDataProvider")
public class BikeDataProviderImpl implements BikeDataProvider {

 private List<Bike> bikes;

 @PostConstruct
 private void prepareData(){
  bikes = new ArrayList<Bike>();
  
  // MTB
  Bike mtb1 = new Bike();
  mtb1.setId(1);
  mtb1.setName("Kellys Mobster");
  mtb1.setDescription("Kellys Mobster, lorem ipsut...");
  mtb1.setPrice(6500);
  mtb1.setCategory(1);
  
  // ... rest of mock bikes created here
 }
 
 public List<Bike> getBikesByCategory(Integer categoryId, boolean onlyWithDiscount) {
  // returns bikes by given category
 }
 
 public Bike getBikeById(Integer id){
  // returns certain bike for its details
 }

 @Override
 public void add(Bike newBike) {
  // add new bike 
  bikes.add(newBike);
 }
}

The same method of creating mock data was used in Spring's managed service named userDetailsService - users and their authorities were created directly inside the service:

package com.jsfsample.application.impl;
...
@Service("userDetailsService")
public class UserDetailsServiceImpl implements UserDetailsService {

 private HashMap<String, org.springframework.security.core.userdetails.User> users = new HashMap<String, org.springframework.security.core.userdetails.User>();
 
 @Override
 public UserDetails loadUserByUsername(String username) {
  // returns user
 }

 @PostConstruct
 public void init() {
  
  // mocked roles  
  Collection<GrantedAuthority> adminAuthorities = new ArrayList<GrantedAuthority>();
  adminAuthorities.add(new GrantedAuthorityImpl("ROLE_ADMIN"));
  
  Collection<GrantedAuthority> userAuthorities = new ArrayList<GrantedAuthority>();
  userAuthorities.add(new GrantedAuthorityImpl("ROLE_REGISTERED"));
  
  boolean enabled = true;
  boolean accountNonExpired = true;
  boolean credentialsNonExpired = true;
  boolean accountNonLocked = true;
  
  // mocked users with roles
  users.put("admin", new org.springframework.security.core.userdetails.User("admin", "admin", enabled, accountNonExpired,
    credentialsNonExpired, accountNonLocked, adminAuthorities));
  
  users.put("user", new org.springframework.security.core.userdetails.User("user", "user", enabled, accountNonExpired,
    credentialsNonExpired, accountNonLocked, userAuthorities));
 }
}

In both cases all data are created in the method annotated with @PostConstruct. That annotation means, that method will be invoked first after creating an instance of a class (in this case Spring creates instances of those service classes).

Our objective is to remove that mocked data created explicite in the services classes and replace them with data coming from real database.

Step 1: we need database structure:

CREATE TABLE "role" (
 role_id SERIAL NOT NULL,
 name VARCHAR(32),  
 CONSTRAINT role_pk PRIMARY KEY (role_id)
);

CREATE TABLE "account" (
 account_id SERIAL NOT NULL,
 role_id INTEGER NOT NULL,
 login VARCHAR(32),
 password VARCHAR(32),
 CONSTRAINT account_pk PRIMARY KEY (account_id),
 CONSTRAINT role_id_fk FOREIGN KEY (role_id) REFERENCES role(role_id)
);

CREATE TABLE "bike_category" (
 bike_category_id SERIAL NOT NULL,
 name VARCHAR(32),  
 CONSTRAINT bike_category_pk PRIMARY KEY (bike_category_id)
);

CREATE TABLE "bike" (
 bike_id SERIAL NOT NULL,
 bike_category_id INTEGER NOT NULL,
 name VARCHAR(32),
 description TEXT,
 price numeric(10,2),
 discount_price numeric(10,2),    
 CONSTRAINT bike_pk PRIMARY KEY (bike_id),
 CONSTRAINT bike_category_fk FOREIGN KEY (bike_category_id) REFERENCES bike_category(bike_category_id)
);

This is very simple database with two many-to-one relations: many users belongs to (have) one role and many bikes belongs to (have) one category. Of course in real world user would have many roles, so we would use many-to-many relation, but I decided to use many-to-one to simplify it.

Tables account and role will be used by Spring Security - they store users and their authorities (roles). Tables bike and bike_category are the "heart" of bike store.

Step 2: when structure is ready, it is time to insert some sample data:

INSERT INTO role (name) values ('ROLE_ADMIN'); -- id 1
INSERT INTO role (name) values ('ROLE_REGISTERED'); -- id 2

INSERT INTO account (role_id, login, password) values (1, 'admin', 'admin'); -- ROLE_ADMIN
INSERT INTO account (role_id, login, password) values (2, 'user', 'user'); -- ROLE_REGISTERED

INSERT INTO bike_category (name) values ('Mountain'); -- id 1
INSERT INTO bike_category (name) values ('Trekking'); -- id 2
INSERT INTO bike_category (name) values ('Cross'); -- id 3

INSERT INTO bike (bike_category_id, name, description, price, discount_price) values (1, 'Kellys Mobster', 'Kellys Mobster, lorem ipsut...', 6500, null); -- Mountain
INSERT INTO bike (bike_category_id, name, description, price, discount_price) values (1, 'Scott Scale', 'Scott Scale, lorem ipsut...', 18900, null); -- Mountain
INSERT INTO bike (bike_category_id, name, description, price, discount_price) values (1, 'Author Magnum', 'Author Magnum, lorem ipsut...', 17200, 15500); -- Mountain

INSERT INTO bike (bike_category_id, name, description, price, discount_price) values (2, 'Giant Accend', 'Giant Accend, lorem ipsut...', 5000, 4600); -- Trekking
INSERT INTO bike (bike_category_id, name, description, price, discount_price) values (2, 'Merida Freeway', 'Merida Freeway, lorem ipsut...', 2400, 2100); -- Trekking
INSERT INTO bike (bike_category_id, name, description, price, discount_price) values (2, 'Mbike Massive', 'Mbike Massive, lorem ipsut...', 1900, null); -- Trekking

INSERT INTO bike (bike_category_id, name, description, price, discount_price) values (3, 'Giant Roam XR 1', 'Giant Roam XR 1, lorem ipsut...', 3900, null); -- Cross
INSERT INTO bike (bike_category_id, name, description, price, discount_price) values (3, 'Cannondale Quick Cx', 'Cannondale Quick Cx, lorem ipsut...', 4999, null); -- Cross
INSERT INTO bike (bike_category_id, name, description, price, discount_price) values (3, 'Cube Cross', 'Cube Cross, lorem ipsut...', 4500, 4200); -- Cross

Step 3: adding required libraries into project.

Comparing those libraries with libraries from clean JSF 2.0 project (look here) or libraries from JSF 2.0 project with Spring (look here) we have extra libraries here: Hibernate libraries marked green, Spring JPA libraries marked red and some third party libraries marked blue. Those blue libraries are:

• c3p0 libraries for connection pool
• log4j library and bridge library from sl4j to log4j - Hibernate by default uses sl4j, but we would like to use log4j
• JDBC driver for PostgreSQL

Step 4: configuration files - will be continued in next post.

-------------------------------------------

Download source files:

The complete working example of mentioned application which will contain all described issues, will be available in the last (second) article of this serie.