Wednesday, May 13, 2009

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.
The query example project structure.

7 comments:

javaserverfaces2-cbts-n-tutorials-jsf2.0 said...

Hey Tim. The post helped a few students of mine out alot. And I was rather flattered to see my name in there along with Christian's. :)

Kind regards,

-Cameron McKenzie

Tim Myer said...

Hi Cameron,
I truly appreciate your taking the time to post feedback. To discover that something I have written has helped or clarified a point or saved time pleases me to no end, especially when the referent of the post becomes a reference.
You have made my week,
---Tim---

EDH said...

Hi, Interesting post.

How would you handle the following ?

I've got one Class A which has a lot (10+) of many-to-one relationships to other classes.

I've mapped Class A to Table A and the related classes to other tables. Table A contains millions of rows.

Before inserting a new A, I need to find out whether an A already exists in table A. I have to retrieve A with it's relationships fully initialized, because if it exists I need to update it.

I would like to create a view that "contains" the join between table A and its related many-to-one tables.

When processing a new A. I would then use the view to quickly fetch A (if it exists).

Given that A exists, how can I then update A and store the updates to the table A ?

Do I need two classes ? Is the object returned from the view the same (identity-wise) as the object returned from table A when directly querying the table ? I guess not ?

Any ideas ?
Thanks,
EDH

BaldinAustin said...

Wow, great article, but I'm faced with exactly the situation that EDH is. Any ideas of a solution?

Tim Myer said...

Hi BaldinAustin,

My initial impression when re-reading the original question by EDH is that this post probably does not apply to the problem described. It is not clear to me exactly what is being attempted.
For example, why do all the relationships for A need to be fully initialized? Are associated elements being added or removed or are columns being changed?

To create a join view, I would probably use Hibernate Criteria and chain Criteria#createCriteria calls to perform the join, then set the Projections for all the columns in the join, then set Restrictions to limit my results, and finally add a custom ResultTransformer to set all the fields in my domain objects.

But again, I am not sure that all this view-mapping is necessary in this situation. For example, there is an easier way to check if an Object exists in the database, insert it if it does not, and update it if it does, all in a concurrency-safe way.

Could you perhaps describe what you are trying to do in more detail (and possibly with a concrete example)?

Thanks,
---Tim---

Seng said...

Good tutorial

Christian said...
This comment has been removed by the author.