Database Schema

The database has only three tables, Books, Authors and Categories. The SQL script to create the database schema for MySql is located in jdal-sample/db/create.sql.

CREATE TABLE authors (
	id INTEGER PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(100),
	surname VARCHAR(100)
) ENGINE=InnoDB;
 
 
CREATE TABLE categories (
	id INTEGER PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(100)
) ENGINE=InnoDB;
 
 
CREATE TABLE books (
	id INTEGER PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(250),
	ISBN VARCHAR(32),
	publishedDate DATE,
	authorid INTEGER,
	categoryid INTEGER
) ENGINE=InnoDB;
 
ALTER TABLE books ADD CONSTRAINT category_fk FOREIGN KEY (categoryid) REFERENCES categories (id);
ALTER TABLE books ADD CONSTRAINT author_fk FOREIGN KEY (authorid) REFERENCES authors (id);

Domain Model

We start creating the Java classes to model data. The models are one to one to database tables and have not behavior. Obviously, this is not a requeriment, it's only a simple example. Models are located in package org.jdal.samples.library.model.

We use JPA annotations to map the model properties to the database schema. The class info.joseluismartin.model.Entity is a simple class with Long id and String name properties with @MappedSuperClass annotation.

@Entity
@Table(name="categories")
public class Category extends info.joseluismartin.model.Entity {
	// nothing to add a Entity class.
}

In Author class we define a hibernate filter named "patternFilter". We use this filter in presentation to add autocompletion on a JComboBox.

 
@Entity
@Table(name="authors")
@Filter(name="patternFilter", condition="name like :pattern or surname like :pattern")
@FilterDef(name="patternFilter", parameters=@ParamDef(name="pattern", type="string"))
public class Author  extends info.joseluismartin.model.Entity {
 
	private String surname;
 
	public String getSurname() {
		return surname;
	}
 
	public void setSurname(String surname) {
		this.surname = surname;
	}
 
	public String toString() {
		return name + " " + surname;
	}
 
	// Overwrite equals and hashcode using name and surname 
	...
}

Finally the Book class.

@Entity
@Table(name="books")
public class Book extends info.joseluismartin.model.Entity {
 
	private static final long serialVersionUID = 1L;
	@ManyToOne
	@JoinColumn(name="authorid")
	private Author author;
	@ManyToOne
	@JoinColumn(name="categoryid")
	private Category category;
	private String isbn;
	private Date publishedDate;
 
	public String toString() {
		return name;
	}
 
	// Getters and Setters
}

There is nothing new here. lets go to handle the Book Filter.

Filters

As we have seen before, the sample has a filter panel to look for books. Real applications have many similar filters, so we need an easy way to handle them. Jdal provides you with some ways to create filters. At the moment we use the most simple and powerfull one, CriteriaBuilder.

CriteriaBuilder interface declares only a method: Criteria build(Criteria criteria, Object filter). Most times we simply extend the AbstractCriteriaBuilder to setup the filter. In order to add the filter feature to the application, we need to create two classes:

BookFilter: A bean to hold filter data.
BookCriteriaBuilder: A CriteriaBuilder implementation to prepare hibernate criterias with filter restrictions.

BookFilter must implements info.joseluismartin.dao.hibernate.Filter. The most simple way is to extend info.joseluismartin.dao.BeanFilter and add Java properties with filter data.

public class BookFilter extends BeanFilter {
 
	private String name;
	private String authorName;
	private String authorSurname;
	private Date before;
	private Date after;
	private String isbn;
	private Category category;
 
	public BookFilter() {
		this("bookFilter");
	}
 
	public BookFilter(String filterName) {
		super(filterName);
	}
 
	// Getter And Setters...
}

We sets the filter name in constructor, bookFilter. This name is important because we'll use it later to link the bean filter with the CriteriaBuilder using this name in spring context configuration files.

Before creating the CriteriaBuilder, review filter specification:

Book title: to search book titles using ILIKE.
Author name and surname: to search author names and surnames using ILIKE.
Pubished date: to search books published between certain dates.
ISBN: again, do a ilike over isbn property.
Category: to search books with this category.

Now, write the CriteriaBuilder, BookCriteriaBuilder is very straight forward

public class BookCriteriaBuilder extends AbstractCriteriaBuilder {
 
	public Criteria build(Criteria criteria, Object filter) {
		BookFilter f = (BookFilter) filter;
 
		like(criteria, "name", f.getName());
		eq(criteria, "category", f.getCategory());
		le(criteria, "publishedDate", f.getBefore());
		ge(criteria, "publishedDate", f.getAfter());
 
		// Author, add alias (join) only if needed.
		if (StringUtils.hasText(f.getAuthorName()) || StringUtils.hasText(f.getAuthorSurname())) {
			criteria.createAlias("author", "author");
			like(criteria, "author.name", f.getAuthorName());
			like(criteria, "author.surname", f.getAuthorSurname());
		}
 
		return criteria;
	}

Now all Java coding in this tier is done.
- ¿Really? ¿and what about Daos or paging and sorting querys?.
Don't worry, that's really done before you started coding the application.

Spring Context Configuration

I normally write four application context configuracion files.

applicationContext.xml: General beans configuration.
applicationContext-dao.xml: Data access beans configuration
applicatonContext-service.xml: Service beans configuration.
applicationContext-view.xml: Presentation beans configuration.

This is, of course a preference. You can do it in any way

Jdal library doesn´t include any context configuration files on jars. So we need to create all the necesary configuration files.

Writing the applicationContext-dao.xml is the last step of the integration layer. We define here the common dataSource, transactionManager and sessionFactory beans and a dao for Book, Category, and Author models.

<!-- DAOs -->
<bean id="dao" abstract="true">
	<property name="sessionFactory" ref="sessionFactory" />
</bean>
 
<bean id="basicDao" class="info.joseluismartin.dao.hibernate.HibernateDao" parent="dao" />
 
<bean id="bookDao" class="info.joseluismartin.dao.hibernate.HibernateDao"
	parent="dao">
	<constructor-arg value="org.jdal.samples.library.model.Book" />
	<!-- Add the bookCriteriaBuilder to map whit 'filterName' as key -->
	<property name="criteriaBuilderMap">
		<map key-type="java.lang.String" value-type="info.joseluismartin.dao.hibernate.CriteriaBuilder">
			<entry key="bookFilter" value-ref="bookCriteriaBuilder" />
		</map>
	</property>
</bean>
 
<!-- Book Criteria Builder -->
<bean id="bookCriteriaBuilder" class="org.jdal.samples.library.dao.filter.BookCriteriaBuilder" />
 
<bean id="authorDao" class="info.joseluismartin.dao.hibernate.HibernateDao"
	parent="dao">
	<constructor-arg value="org.jdal.samples.library.model.Author" />
</bean>
 
<bean id="categoryDao" class="info.joseluismartin.dao.hibernate.HibernateDao" parent="dao">
	<constructor-arg value="org.jdal.samples.library.model.Category" />
</bean>

We define all daos, bookDao, authorDao and categoryDao of type HibernateDao. HibernateDao is a main class within jdal-core, so we´ll explain it a little.

The more basic interface of data access library is PageableDataSource<T>. It's define two methods for requesting for data:

Page<T> getPage(Page<T>): get data by pages.
List<Serializable> getKeys(Page<T> page): get only the keys of the page.

The class Page, holds all the necesary information to request a page of data:

The page size.
The start index of the page.
The Filter Object.
The sort property name (really property path, for example 'author.name')
The sort Order (ASC/DESC)
A list with result data

The interface Dao<T, Serializable> add CRUD methos to PageableDataSource, and HibernateDao is an hibernate Dao implementation. In code, you may ask for pages of data as follows:

Dao<Book> bookDao = // get HibernateDao, JpaDao or IBatisDao reference, usually from DI
Page<Book> page = new Page(pageSize);
// Search for Fowler's books, ordered by published date
BookFilter filter = new BookFilter();
filter.setAuthorSurname("Fowler");
page.setFilter(filter);
page.serSortName("publishedDate");
// Now we can load Page from dao
bookDao.getPage(page);
// Or use page directly
page.setPageableDataSource(bookDao)
page.firstPage();
// get total records, i.e. all records, not page size.
int recordCount = page.getCount();
// As page implements paginator we can use it directly as PaginatorView model and let 
// the user to control the page load.
PaginatorView view = new PaginatorView(page)
...
// Gets the page results
List<Book> books = page.getData();
...

Page Filters are interpreted by Daos. In the case of HibernateDao implementation there are diferent posibilities

The filter is an entity class: HibernateDao do a Session.getByExample();
The filter is a Filter implementation: HibernateDao try this secuence:
1. filter.getName() match a hibernate parametrized query name: it applies filter parameters to query and executes them.
2. filter.getName() match a hibernate declared filter: it applies filter parameters to filter and enable it.
3. filter.getName() match a key on Map of CriteriaBuilders: it uses the builder to prepare criteria and executes it.
4. finally, if there is a method in Dao named "createCriteria + "filter.getName(), it executes the method to prepare Criteria and execute it.

Now is more clear how our BookCriteriaBuilder works and why we added it to bookDao CriteriaBuilderMap in spring configuration.

In all cases, HibernateDao creates an Order by means of page.getSortName() and applies it before executing criteria. If Order is null, it wil try to find a 'name' property. If this is not found, then it will order by primary key as default.

Summary

We do this work to create the Book Application Data Access Layer:

To make three model beans and map it to database schema using JPA
To make a FilterBean and CriteriaBuilder to filter Book data.
To configure an applicationContext-dao.xml with common data access beans and add three HibernateDao definitions.

The next step, is the service tier.

Principal

Home

Documentation

Sample Application

Support

Database Schema

Domain Model

Filters

Spring Context Configuration

Summary