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:
We can also create a new database user and assign full privileges:
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:
Similarly, our Spring src/main/resources/applicationContext.xml must also be configured for a PostgreSQL, rather than Oracle, connection:
We can also create a very simple domain of authors. Every Author will have a first name, a last name and a generated id:
We can register our domain with Hibernate in the src/main/resources/hibernate.cfg.xml:
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:
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:
We can test the execution of our query and the mapping of the results in JUnit with an AuthorTest:
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.
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
We will create the list of surnames directly through SQL:
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:
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:
We can run the query with a few additions to the JUnit test:
In this 12K project, we now have two examples for registering Hibernate-annotated POJOs with database structures that do not correspond directly to tables.