Mapping Hibernate Entities to Views

Goal

By the end of this tutorial, we will have the tools to map a Hibernate Entity that does not correspond directly to a database table onto structures such as views or query results.

Setup a PostgreSQL Database

This tutorial breaks from the pattern of previous posts in this series by using PostgreSQL instead of Oracle. The PostgreSQL default one-click installation is sufficient for the example. On Windows, I found it necessary to remove whitespace from the default installation path, and adding the </path/to/postgresql>/bin to the system Path variable has been helpful.

For this example, we can create a new database called examples:

  psql -U postgres -c "CREATE DATABASE examples" -d template1

We can also create a new database user and assign full privileges:

  psql -U postgres -c "CREATE USER hibernate_query WITH PASSWORD 'hibernate_query'" -d examples
  psql -U postgres -c "grant all privileges on database examples to hibernate_query" -d examples

Create an M2Eclipse Project

Creating a simple M2Eclipse project without an archetype should be familiar if you have followed previous posts in this series. Note that, for our example, we will configure our pom.xml for Spring 3.0.0.M3 and for the postgresql 8.3-603.jdbc4 driver. Unlike in prior examples, no extra Maven2 setup of the Oracle JDBC driver is necessary since the PostgreSQL driver is distributed directly from the Maven2 repository.
Our pom.xml will look similar to this:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>examples.hibernate.spring.query</groupId>
    <artifactId>examples.hibernate.spring.query</artifactId>
    <name>examples.hibernate.spring.query</name>
    <version>0.0.1-SNAPSHOT</version>
    <description>Hibernate Query Example</description>
    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>org.springframework.transaction</artifactId>
            <version>3.0.0.M3</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>org.springframework.orm</artifactId>
            <version>3.0.0.M3</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-annotations</artifactId>
            <version>3.4.0.GA</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.5.2</version>
        </dependency>
        <dependency>
            <groupId>javassist</groupId>
            <artifactId>javassist</artifactId>
            <version>3.4.GA</version>
        </dependency>
        <dependency>
            <groupId>cglib</groupId>
            <artifactId>cglib</artifactId>
            <version>2.2</version>
        </dependency>
        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.2.2</version>
        </dependency>
        <dependency>
            <groupId>postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>8.3-603.jdbc4</version>
        </dependency>
        <dependency>
            <scope>test</scope>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.5</version>
        </dependency>
        <dependency>
            <scope>test</scope>
            <groupId>org.springframework</groupId>
            <artifactId>org.springframework.test</artifactId>
            <version>3.0.0.M3</version>
        </dependency>
    </dependencies>
    <repositories>
        <repository>
            <id>SpringSource Enterprise Bundle Repository - External Bundle Milestones</id>
            <url>http://repository.springsource.com/maven/bundles/milestone</url>
        </repository>
        <repository>
            <id>SpringSource Enterprise Bundle Repository - SpringSource Bundle Releases</id>
            <url>http://repository.springsource.com/maven/bundles/release</url>
        </repository>
        <repository>
            <id>SpringSource Enterprise Bundle Repository - External Bundle Releases</id>
            <url>http://repository.springsource.com/maven/bundles/external</url>
        </repository>
    </repositories>
</project>

Similarly, our Spring src/main/resources/applicationContext.xml must also be configured for a PostgreSQL, rather than Oracle, connection:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans.xsd
       http://www.springframework.org/schema/tx
       http://www.springframework.org/schema/tx/spring-tx.xsd">
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName">
            <value>org.postgresql.Driver</value>
        </property>
        <property name="username">
            <value>hibernate_query</value>
        </property>
        <property name="password">
            <value>hibernate_query</value>
        </property>
        <property name="url">
            <value>jdbc:postgresql://localhost:5432/examples</value>
        </property>
    </bean>
    <bean id="sessionFactory"
        class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="configLocation" value="classpath:/hibernate.cfg.xml" />
        <property name="configurationClass" value="org.hibernate.cfg.AnnotationConfiguration" />
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.show_sql">true</prop>
                <prop key="hibernate.format_sql">true</prop>
                <prop key="hibernate.generate_statistics">true</prop>
                <prop key="hibernate.use_sql_comments">true</prop>
                <prop key="hibernate.hbm2ddl.auto">create</prop>
                <prop key="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</prop>
                <prop key="hibernate.query.factory_class">org.hibernate.hql.ast.ASTQueryTranslatorFactory</prop>
            </props>
        </property>
    </bean>
    <bean id="txManager"
        class="org.springframework.orm.hibernate3.HibernateTransactionManager">
        <property name="sessionFactory" ref="sessionFactory" />
    </bean>
    <tx:annotation-driven transaction-manager="txManager" />
</beans>

We can also create a very simple domain of authors. Every Author will have a first name, a last name and a generated id:

package examples.hibernate.spring.query.domain;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;

@Entity
@Table(name = "AUTHOR", schema = "")
public class Author implements java.io.Serializable {

    private static final long serialVersionUID = -6270202393794713117L;

    private int id;

    private String firstName;

    private String lastName;

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "author_id_seq")
    @SequenceGenerator(name = "author_id_seq", sequenceName = "author_id_seq")
    @Column(name = "ID", nullable = false)
    public int getId() {
        return id;
    }

    public void setId(final int id) {
        this.id = id;
    }

    @Column(name = "FIRST_NAME", nullable = false, length = 50)
    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(final String firstName) {
        this.firstName = firstName;
    }

    @Column(name = "LAST_NAME", nullable = false, length = 50)
    public String getLastName() {
        return lastName;
    }

    public void setLastName(final String lastName) {
        this.lastName = lastName;
    }
}

We can register our domain with Hibernate in the src/main/resources/hibernate.cfg.xml:

<!DOCTYPE hibernate-configuration PUBLIC
    "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <mapping class="examples.hibernate.spring.query.domain.Author" />
    </session-factory>
</hibernate-configuration>

The Custom Query

Suppose we want to run a custom query from Hibernate, but the resulting rows do not correspond directly to a Table or to the columns in a table. For example, suppose we want to find the number of unique first names for all authors.
Mapping the results of a custom query to the fields of a Hibernate POJO is very similar to mapping table columns.
For our example, we can call an individual result an AuthorAggregate. It will contain fields for a first name and a count of the number of occurrences for that first name. The aggregate must also have an id for uniquely identifying each result:

package examples.hibernate.spring.query.domain;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

import org.hibernate.annotations.GenericGenerator;

@Entity
public class AuthorAggregate {

    private long id;
    private String firstName;
    private Long nameCOunt;

    public void setId(final long id) {
        this.id = id;
    }

    @Id
    @GeneratedValue(generator = "system-uuid")
    @GenericGenerator(name = "system-uuid", strategy = "uuid")
    public long getId() {
        return id;
    }

    public void setFirstName(final String firstName) {
        this.firstName = firstName;
    }

    @Column(name = "FIRST_NAME", length = 50)
    public String getFirstName() {
        return firstName;
    }

    public void setNameCount(final Long nameCOunt) {
        this.nameCOunt = nameCOunt;
    }

    @Column(name = "NAME_COUNT")
    public Long getNameCount() {
        return nameCOunt;
    }
}

We will register this object in our src/main/resources/hibernate.cfg.xml as part of our domain so that Hibernate can map the results of the query to the object's fields:

....
        <mapping class="examples.hibernate.spring.query.domain.AuthorAggregate" />
....

We can test the execution of our query and the mapping of the results in JUnit with an AuthorTest:

package examples.hibernate.spring.query.domain;

import static org.junit.Assert.assertEquals;

import java.util.Collection;
import org.hibernate.SessionFactory;
import org.hibernate.classic.Session;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.transaction.TransactionConfiguration;
import org.springframework.transaction.annotation.Transactional;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { "/applicationContext.xml" })
@TransactionConfiguration(transactionManager = "txManager", defaultRollback = true)
@Transactional
public class AuthorTest {

    private static final String AUTHORS_BY_FIRST_NAME = //
    "select first_name, count(author.id) as name_count, random() * 100000000000000000 as id " //
            + "from author " //
            + "group by author.first_name";

    @Autowired
    private SessionFactory sessionFactory;

    @Before
    public void setUp() {
        final Session session = sessionFactory.getCurrentSession();
        session.save(createCameronMcKenzie());
        session.save(createChristianBauer());
        session.save(createCameronJudd());
        session.flush();
    }

    @Test
    public void findAggregationOfNames() throws Exception {
        final Collection<AuthorAggregate> authorsByName = findByFirstName();
        assertEquals(2, authorsByName.size());
        for (final AuthorAggregate authorAggregate : authorsByName) {
            if ("Cameron".equals(authorAggregate.getFirstName())) {
                assertEquals(Long.valueOf(2), authorAggregate.getNameCount());
            } else {
                assertEquals("Christian", authorAggregate.getFirstName());
                assertEquals(Long.valueOf(1), authorAggregate.getNameCount());
            }
        }
    }

    @SuppressWarnings("unchecked")
    private Collection<AuthorAggregate> findByFirstName() {
        return sessionFactory.getCurrentSession() //
                .createSQLQuery(AUTHORS_BY_FIRST_NAME) // 
                .addEntity(AuthorAggregate.class) //
                .list();
    }

    private Author createCameronMcKenzie() {
        final Author cameronMcKenzie = new Author();
        cameronMcKenzie.setFirstName("Cameron");
        cameronMcKenzie.setLastName("McKenzie");
        return cameronMcKenzie;
    }

    private Author createCameronJudd() {
        final Author cameronMcKenzie = new Author();
        cameronMcKenzie.setFirstName("Cameron");
        cameronMcKenzie.setLastName("Judd");
        return cameronMcKenzie;
    }

    private Author createChristianBauer() {
        final Author christianBauer = new Author();
        christianBauer.setFirstName("Christian");
        christianBauer.setLastName("Bauer");
        return christianBauer;
    }
}

NB: To generate a unique id for each result, we are using the PostgreSQL random() function. In Oracle, we could generate a string of random characters with a function call such as dbms_random.string('P', 20). Also note that before we execute the native query, we must flush the Hibernate session to ensure that the Objects created and saved through HQL are persisted all the way to the database.

The View

Suppose that we want a list of all the last names of all the authors whose first name is Cameron.
Now that the author table exists in our database, we can create a custom view for this requirement through psql. We can connect to the examples database through psql

  psql -U hibernate_query -d examples

We will create the list of surnames directly through SQL:

CREATE OR REPLACE VIEW cameron AS
  SELECT last_name AS surname
  FROM author
  WHERE first_name = 'Cameron';

Setting up the Hibernate domain object for this view is similar to mapping the results of a custom query to an annotated POJO as described above. For this example, instead of creating the native query inline and registering the output Entity directly with the query, we will utilize a javax.persistence annotation to register a NamedNativeQuery directly with our domain object, Cameron:

package examples.hibernate.spring.query.domain;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.NamedNativeQuery;

@Entity
@NamedNativeQuery(name = "findUniqueCameronsInOrder", query = "select * from cameron order by surname", resultClass = Cameron.class)
public class Cameron implements java.io.Serializable {

    private static final long serialVersionUID = 8765016103450361311L;

    private String surname;

    @Id
    @Column(name = "SURNAME", nullable = false, length = 50)
    public String getSurname() {
        return surname;
    }

    public void setSurname(final String surname) {
        this.surname = surname;
    }
}

NB: Our named query is doing slightly more than just selecting everything from the view. It selects only unique surnames, thus ensuring that the surname can be used as the @Id, and it also orders the returned surnames alphabetically.

We will again register this domain object with Hibernate in the src/main/resources/hibernate.cfg.xml:

....
        <mapping class="examples.hibernate.spring.query.domain.Cameron" />
....

We can run the query with a few additions to the JUnit test:

....
    @Test
    public void findTheCameronsInTheView() throws Exception {
        final List<Cameron> camerons = findUniqueCameronsInOrder();
        assertEquals(2, camerons.size());
        final Cameron judd = camerons.get(0);
        final Cameron mcKenzie = camerons.get(1);
        assertEquals("Judd", judd.getSurname());
        assertEquals("McKenzie", mcKenzie.getSurname());
    }

    @SuppressWarnings("unchecked")
    private List<Cameron> findUniqueCameronsInOrder() {
        return sessionFactory.getCurrentSession() //
                .getNamedQuery("findUniqueCameronsInOrder") //
                .list();
    }
....

Conclusion

In this 12K project, we now have two examples for registering Hibernate-annotated POJOs with database structures that do not correspond directly to tables.