Mapping SQL Joins using Anorm

Anorm is a Scala framework that is a fairly thin veneer over JDBC that allows you to write SQL queries and map results into Scala objects. The examples easily found on the web have a tendency to be fairly simple. One of the first problems I ran into was mapping a Parent-Child hierarchy where the parent has a collection of values from a different table.

For this post, I’m using a simple, contrived schema that looks like the following:

CREATE TABLE USER (
  id         SERIAL,
  user_name  VARCHAR(100),
  CONSTRAINT pk_user PRIMARY KEY (id)
);
 
CREATE TABLE email (
  user_id    LONG,
  email      VARCHAR(100),
  CONSTRAINT pk_email PRIMARY_KEY (user_id, email)
);
ALTER TABLE email ADD CONSTRAINT fk_email_user FOREIGN_KEY (user_id) REFERENCES USER (id);
 
CREATE TABLE phone (
  user_id    LONG,
  phone      VARCHAR(11),
CONSTRAINT pk_phone PRIMARY_KEY (user_id, phone)
);
ALTER TABLE phone ADD CONSTRAINT fk_phone_user FOREIGN_KEY (user_id) REFERENCES USER (id);

The Simple Case

In the simplest case, Anorm allows you to map the results of a query to a Scala case class like the following:

case class User(id:Long, name: String)
object User {
  def rowMapper = {
      long("id") ~
      str("user_name") map {
        case id ~ name => User(id, name)
      }
  }
}
 
def getUser(id: String): Option[User] = {
  DB.withConnection {
    implicit conn =>
      SQL("SELECT id, user_name FROM user WHERE user.id = {id}")
        .on("id" -> id)
        .as(User.rowMapper singleOpt)
  }
}

The query is executed and the results of the query are mapped to the User using a RowMapper which converts the result columns into Scala types and ultimately to a Scala User object that you’ve defined.

Joins

But what if you want a more complex object, such as adding Phone numbers and Email addresses to your user object? Lets say you want something more like the following:

case class User(id:Long, name: String, emails: List[String], phones: List[String])
object User {
  def rowMapper = {
      long("id") ~
      str("user_name") ~
      (str("email") ?) ~
      (str("number") ?) map {
        case id ~ name ~ email ~ number => ((id, name), email, number)
      }
  }
}

This row mapper doesn’t return a User object directly, but rather the columns grouped into a Triple (with id and name as the first part of the Triple).

Anorm doesn’t have a lot of support for that out of the box, but Scala’s built in functions for dealing with Lists and Maps have the tools that you need. Take a look at the following example. If you’re new to Scala, good luck wrapping your brain around it.

def getUser(id: String): Option[User] = {
  DB.withConnection {
    implicit conn =>
      SQL(
        """SELECT user_name, email.email, phone.number
          |FROM user
          |LEFT JOIN email ON email.user_id = user.id
          |LEFT JOIN phone ON phone.user_id = user.id
          |WHERE user.id = {id}""".stripMargin)
        .on("id" -> id)
        .as(User.rowMapper *)
        .groupBy(_._1)
        .map {
          case ((dbId, name), rest) => User(dbId, name, rest.unzip3._2.map(_.orNull), rest.unzip3._3.map(_.orNull))
        }.headOption
  }
}

But we can break down those steps a little bit, include the type declarations of what happens at each step to make it more clear as to what’s being done. Using those type declarations you end up with something like the following.

def getUser(id: String): Option[User] = {
  DB.withConnection {
    implicit conn =>
      val result: List[((Long, String), Option[String], Option[String])] = SQL(
        """SELECT user_name, email.email, phone.number
          |FROM user
          |LEFT JOIN email ON email.user_id = user.id
          |LEFT JOIN phone ON phone.user_id = user.id
          |WHERE user.id = {id}""".stripMargin)
        .on("id" -> id)
        .as(User.rowMapper *)
 
      val queryGroupedByUser: Map[(Long, String), List[((Long, String), Option[String], Option[String])]]
        = result.groupBy(_._1)
 
      val listOfUser: Iterable[User] = queryGroupedByUser.map {
        case ((dbId, name), rest) => {
          val emails: List[String] = rest.unzip3._2.map(_.orNull) // convert Option[String]s to List[String] where Some[String]
          val phones: List[String] = rest.unzip3._3.map(_.orNull) // convert Option[String]s to List[String] where Some[String]
          User(dbId, name, emails, phones)
        }
      }
 
      listOfUser.headOption
  }
}

Let’s break that down a little more:

val result: List[((Long, String), Option[String], Option[String])] = SQL(
  """SELECT user_name, email.email, phone.number
    |FROM user
    |LEFT JOIN email ON email.user_id = user.id
    |LEFT JOIN phone ON phone.user_id = user.id
    |WHERE user.id = {id}""".stripMargin)
  .on("id" -> id)
  .as(User.rowMapper *)

This code creates a List as you can see from the type declaration. The List contains an entry for each row returned in the result set. Because we used JOIN clauses, we might have gotten back many rows. For example, if a user had 2 emails the results might have looked like:

id, name, email, number
1, Geoff, geoff@example.com, 15135551212
1, Geoff, geoff2@example.com, 15135551212

The resulting Scala List that directly contains the data from that result set. But we take an extra step of grouping the basic User data (the parent) into its own Tuple which we’ll use later to identify the unique Users. The Scala list of the above result set would contain:

List(((1, "Geoff"), Some("geoff@example.com"), Some("15135551212")), 
     ((1, "Geoff"), Some("geoff2@example.com"), Some("15135551212")))

Next we create a map of the results where the key to the map is the unique users:

val queryGroupedByUser: Map[(Long, String), List[((Long, String), Option[String], Option[String])]]
  = result.groupBy(_._1)

From that list, we create a map, where the keys of the map are the unique parent objects. This turns the list shown above into a map like:

Map((1, "Geoff"), 
  List(((1, "Geoff"), Some("geoff@example.com"), Some("15135551212")), 
       ((1, "Geoff"), Some("geoff2@example.com"), Some("15135551212")))
)

This mapping will work if there are many keys returned as well (assuming you were querying by something non-unique). In that case your map will contain one entry for each of the unique parents.

Finally, we need to take apart the Map and turn it into our domain object:

val listOfUser: Iterable[User] = queryGroupedByUser.map {
  case ((dbId, name), rest) => {
    val emails: List[String] = rest.unzip3._2.map(_.orNull) // convert Option[String]s to List[String] where not null
    val phones: List[String] = rest.unzip3._3.map(_.orNull) // convert Option[String]s to List[String] where not null
    User(dbId, name, emails, phones)
  }
}

The case statement destructures the Map back into the key containing the basic user information and then the list of all the other data associated with that user. rest.unzip3 turns the List(A, B, C) into (List[A], List[B], List[C]). _.2 takes the second element out of the Triple, in this case the List[String] containing the emails. We then map over them to get the value or null from the Option[String] to create a list of the items that are not null. The same process is done for emails and phones. Those values along with the key from the map are used to create the instances of our Users. In this case, since we only expect one based on an id, we also use listOfUser.headOption to get the first element of the list (or None if the list is empty).

Hopefully breaking down the Scala into smaller chunks will help some people understand how this stuff works.

MongoDB Replication is Easy

Database replication with MongoDB is easy to setup. Replication duplicates all of the data from a master to one or more slave instances and allows for safety and quick recovery in case of a problem with your master database. Here is an example of how quick and easy it is to test out replication in MongoDB. Create a couple of directories for holding your mongo databases.

    mkdir master slave

Start by running an instance of the “master” database.

cd master
mongod  --master --dbpath .

Start a new terminal window and continue by running an instance of a “slave” database. This example is running on the same machine as master which is great for testing, but wouldn’t be a good setup if you were really trying to implement replication in a production environment since you would still have a single-point-of-failure in the single server case.

cd slave
mongod --slave --port 27018 --dbpath . --source localhost

And start another terminal window to use as the client

mongo
db.person.save( {name:'Geoff Lane'} )
db.person.save( {name:'Joe Smith'} )
db.person.find()
db.person.save( {name:'Jim Johnson', age: 65} )
db.person.find()

Now kill the master instance in your terminal with Control+C. This simulates the the master server dying. Lastly connect to the slave instance with a mongo client by specifying the port.

mongo --port 27018
db.person.find()

As you can see, the db.person.find() returns all of the values that were saved in the master list as well which shows that replication is working. One of the other interesting facts is that you can start a slave instance even after the mongod master is already running and has data and all of the data will be replicated over to the slave instance as well. This all works without ever shutting down your mongod master instance. This allows you to add replication after the fact with no downtime. For more on MongoDB check out these books:
* MongoDB: The Definitive Guide
* The Definitive Guide to MongoDB: The NoSQL Database for Cloud and Desktop Computing
* MongoDB for Web Development (Developer’s Library)

MongoDB and Java: Find an item by Id

MongoDB is one of a number of new databases that have cropped up lately eschewing SQL. These NoSQL databases provide non-relational models that are suitable for solving different kinds of problems. This camp includes document oriented, tabular and key/value oriented models among others. These non-relational databases are supposed to excel at scalability through parallelization and replication but sometimes (although not always) at the expense of some of the transactional guarantees of SQL databases.

Why would you care about any of this? Document oriented databases allow for each document to store arbitrary pieces of data. This could allow for much easier customization of data storage such as when you want to store custom fields. Many of these databases also make horizontal scaling quite simple as well as providing high performance for write heavy applications.

With this in mind I figured I should look and see what’s there. So I started looking at MongoDB.

Start by creating an object to add to the database

With MongoDB, a collection is conceptually similar to a table in a SQL database. It holds a collection of related documents. A DBObject represents a document that you want to add to a collection. MongoDB automatically creates an id for each document that you add. That id is set in the DBObject after you pass it to the save method of the collection. In a real world application you might need that id to later access the document.

DBObject obj = new BasicDBObject();
obj.put("title", getTitle());
obj.put("body", getBody());
 
DBCollection coll = db.getCollection("note"));
coll.save(obj);
 
String idString = obj.get("_id").toString();

Retrieve an object previously added to a collection

To get a document from MongoDB you again use a DBObject. It does double duty in this case acting as a the parameters you want to use to identify a matching document. (There are ways you can do comparisons other than equality, of course, but I’ll leave that for a later post.) Using this as a “query by example” model we can set the _id property that we previously retrieved. The one catch is that the id is not just a string, it’s actually an instance of an ObjectId. Fortunately when we know that it’s quite easy to construct an instance with the string value.

String idString = "a456fd23ac56d";
DBCollection coll = db.getCollection(getCollectionName());
DBObject searchById = new BasicDBObject("_id", new ObjectId(idString));
DBObject found = coll.findOne(searchById);

A couple of easy examples, but it wasn’t obvious to me when I started how to get the id of a document that I just added to the database. More to come in the future.

For more on MongoDB check out these books:

Database Migrations for .NET

One of the more difficult things to manage in software projects is often changing a database schema over time. On the projects that I work on, we don’t usually have DBAs who manage the schema so it is left up to the developers to figure out. The other thing you have to manage is applying changes to the database in such a way that you don’t disrupt the work of other developers on your team. We need the change to go in at the same time as the code so that Continuous Integration can work.

Migrations

While I don’t know if they were invented there, migrations seem to have been popularized by Ruby on Rails. Rails is a database centric framework that implies the properties of your domain from the schema of your database. For that reason it makes sense that they came up with a very good way of These are some example migrations to give you an idea of the basics of creating a schema.

001_AddAddressTable.cs:

using Migrator.Framework;
using System.Data;
[Migration(1)]
public class AddAddressTable : Migration
{
    override public void Up()
    {
         Database.AddTable("Address",
             new Column("id", DbType.Int32, ColumnProperty.PrimaryKey),
             new Column("street", DbType.String, 50),
             new Column("city", DbType.String, 50),
             new Column("state", DbType.StringFixedLength, 2),
             new Column("postal_code", DbType.String, 10)
    }
    override public void Down()
    {
        Database.RemoveTable("Address");
    }
}

02_AddAddressColumns.cs:

using Migrator.Framework;
using System.Data;
[Migration(2)]
public class AddAddressColumns : Migration
{
    public override void Up()
    {
        Database.AddColumn("Address", new Column("street2", DbType.String, 50));
        Database.AddColumn("Address", new Column("street3", DbType.String, 50));
    }
    public override void Down()
    {
        Database.RemoveColumn("Address", "street2");
        Database.RemoveColumn("Address", "street3");
    }
}

003_AddPersonTable.cs:

using Migrator.Framework;
using System.Data;
[Migration(3)]
public class AddPersonTable : Migration
{
    public override void Up()
    {
        Database.AddTable("Person", 
            new Column("id", DbType.Int32, ColumnProperty.PrimaryKey),
            new Column("first_name", DbType.String, 50),
            new Column("last_name", DbType.String, 50),
            new Column("address_id", DbType.Int32, ColumnProperty.Unsigned)
        );
        Database.AddForeignKey("FK_PERSON_ADDRESS", "Person", "address_id", "Address", "id");
    }
    public override void Down()
    {
        Database.RemoveTable("Person");
    }
}

Run Your Migrations

The best way to run your migrations will be to integrate it into your build automation tool of choice. If you are not using one, now is the time.

MigratorDotNet supports MSBuild and NAnt.

MSBuild:

<Target name="Migrate" DependsOnTargets="Build">
    <CreateProperty Value="-1"  Condition="'$(SchemaVersion)'==''">
        <Output TaskParameter="Value" PropertyName="SchemaVersion"/>
    </CreateProperty>
    <Migrate Provider="SqlServer" 
            Connectionstring="Database=MyDB;Data Source=localhost;User Id=;Password=;" 
            Migrations="bin/MyProject.dll" 
            To="$(SchemaVersion)"/>
</Target>

NAnt:

<target name="migrate" description="Migrate the database" depends="build">
  <property name="version" value="-1" overwrite="false" />
  <migrate
    provider="MySql|PostgreSQL|SqlServer"
    connectionstring="Database=MyDB;Data Source=localhost;User Id=;Password=;"
    migrations="bin/MyProject.dll"
    to="${version}" />
</target>

So You Want to Migrate?

Some more documentation and example are available MigratorDotNet. Some of the changes represented are still in an experimental branch that is in the process of being merged.


MigratorDotNet is a continuation of code started by Marc-André Cournoyer and Nick Hemsley.

Using SQL Compact Edition Under ASP.NET

What is SQLCE?

SQL Compact Edition is the “low end” version of a SQL database solution from Microsoft. It is a single-file, application managed, database implementation. It doesn’t have all of the bells and whistles of the high end database solutions. This is great when you realize the next lowest version, SQL Express is over a 100MB install.

The beta of this software was called SQL Everywhere Edition (SQLEV). Microsoft decided that they didn’t like the name so they went with SQL Compact Edition (SQLCE ). The name Compact Edition is a bit of a misnomer. It can be used on the Compact Framework, but it can also be used on the full-framework anywhere a single-file, zero-install database might be needed. Well almost Everywhere at least. There is an explicit check when running on the Full Framework to make sure that you are not using it in an ASP.NET application. SQLCE is not a very scalable solution. It has some inherent limitations with concurrent connections for example. This is fine though if you go back to “what are you using this for”? An embedded, single-file database. Well I ran into a case where I need a small web-service to be running where an embedded database makes a lot of sense. I’m using the HttpListener class to run my own Http.sys server without using IIS. This still counts as ASP.NET to the SQLCE code though.

Force SQLCE to run under ASP.NET

Steve Lasker posted blog entry on how to use SQLCE under ASP.NET using the pre-release version of the SQLEV. Under SQLEV you set a flag that would tell the SQL Connection, “yes I know this isn’t supported, but let me do it anyway”:

AppDomain.CurrentDomain.SetData("SQLServerEverywhereUnderWebHosting", true)

As you can see the name of the product is right there in the key. Well they changed the name of the product and so they changed the name of the key. So, if you were using the beta for development and are now switching over to the release version of SQLCE , you will need to change the key:

AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true)

That should allow you to use the database under ASP.NET. Now you can revel in the fact that you are swimming in unsupported waters!

Special Thanks
I found this using the great .NET disassembler Lutz Roeder’s .NET Reflector. You should check it out. It can be a great way to track down details of an API implementation when the abstraction is a bit leaky.

Fake Materialized Views

In a previous post, I discussed materialized views in Oracle. I wanted to share a relatively simple technique that can be used to create similar functionality in Oracle or another database.

Why Use Something Else?

Materialized Views are a feature available in Oracle. If you’re not using Oracle, then that’s reason enough on its own. Materialized Views also have limitations in terms of how they are refreshed. Refreshing is the concept of updating the contents of the view. This can be done as a fast refresh when the base tables that it relies on changes. Certain things like union or other more “complex” SQL prevents any sort of fast refreshing from going on. The other option for updating the materialized view is a periodic update. Every n minute you can have the view refreshed. This is fine in certain circumstances, but there are use cases where this will be a show stopper. So what do you do?

Yes, I ran into the refreshing problem and had to work with a DBA to figure out another way to accomplish the same thing.

Table, View, Trigger

A materialized view is an object in the database that is backed by a table behind the scenes. We often want to use them as a way to denormalize data for reporting or performance reasons. Knowing this, you can create a structure that is a directly a table instead. This table will be the structure that you query against. To keep the data up to date you can implement a trigger that responds to changes in the base table to keep the dernomalized table up-to-date.

The simplest way to do this is to create a regular database view of the data that you want. Why not use the view directly? When you query a view it re-runs the query against the base table. This can be an expensive operation and put a lot of load on your database. Once you have a view containing the data that you want, you can then “copy” the data into your table for day-to-day querying.

Example


create or replace view V$MY_VIEW AS
select table1.v1, table2.v2, table2.v3
from Table as table1
inner join Child_Table table2
on table1.id=table2.parent_id;


create table MY_TABLE
value1 varchar2(32),
value2 varchar2(128),
value3 int;
insert into MY_TABLE select * from V$MY_VIEW;


create trigger UPDATE_MY_TABLE
after insert or update or delete on Table
begin
delete from MY_TABLE;
insert into MY_TABLE select * from V$MY_VIEW;
end;

Conclusion

This is a denormalization technique that you can use for some read performance benefits in certain scenarios. Of course you will likely suffer from write performance when you do this. This particular technique is likely only useful if there are relatively few rows in your dependent tables, otherwise the performance will probably degrade quickly.

Obviously this is a pretty simple example, but hopefully it would help give you some inspiration if you need a similar solution.

Oracle Materialized Views

So the existence of Materialized Views might not be news to the Oracle DBAs around the world, but I present topics from the perspective of a software developer. As software developers we often have to use databases with our applications. As a user of a database, I think it is very important that software developers know what is available to them to leverage when they build their applications. I don’t want developers to be afraid of the database.

(As an aside, I have not found Materialized Views in any database other than Oracle. If you know of any others that support this, please leave a comment.)

What Are Materialized Views?

Database Views offer a way to encapsulate a query in the database and present it to a caller in a way that it looks like a regular table. Every time you query a view, join a view to another table or take similar actions, the query that makes up the view is rerun against the database.

Materialized views are a similar concept to regular views but with one very interesting difference. Materialized views are backed by a real object in the database. When a materialized view is created the query is run and a table (or a table-like structure) is created in the database. Materialized views, like regular Views, can be read-only or can be configured as read-write as well. The other thing that you can configure is who that materialized view is refreshed. The interesting thing is that these materialized views can be asynchronously refreshed in the database when dependent tables are changed. The refresh rules can be everything from never to whenever a row is updated, deleted or inserted. There are a number of rules and limitations on the various refresh schemes, rules that are too complicated to address in a short article, which means having the help of a good DBA would likely be very helpful.

This denormalization can give you really good performance gains. Of course you can do this with a View as well, but the database has to do even less work in the case of a materialized view because all of the relationships, aggregations, etc are pre-calculated and the results stored in a database object.

How Do I Use Them in my Application?

Tying this back into software development, how do we make use of them in our applications? The good news is that this is really straight forward. Using tools like Hibernate, you can map to materialized views just like you can to real tables. You can query them using JDBC (or PHP or Ruby Active Record) the same as you would a regular table. What I’ve been finding is that Materialized Views can be a great tool for denormalization. You can maintain a nicely normalized database schema for you application, but use the Materialized Views to offer some denormalized views of the data.

Some Example of Materialized View Use Cases:

  • Pre-calculatd Aggregate Values (sum, max, min)
  • Flatten hierarchies
  • Giving pre-filtered data selections
  • Anything that involves a complicated or slow series of calculations or joins

In an application I’m currently working with I found it very helpful to map parent-child relationships of objects using Hibernate through a materialized view to flatten a hierarchy. (Using the many-to-many mapping where the relationship table was a Materialized View.)

Example:
Company -> Division -> Department -> Employee

To answer the question of who works for a given Company you would join all the way down through those tables. Now image you have just one logical Organization tables that is self-referential (i.e. the Division of a Company has a parent_id of another row in the Organization table). Those hierarchical queries can be complicated and expensive. But you don’t suffer from the expense of the query (or many queries) if you flatten the hierarchy and create a “denormalized” table that maps Employees to their Company. The flattened tables or calculation tables that you choose to use are, of course, driven by your applications need for the data.

Example Query

A pre-calculated value table:

CREATE materialized VIEW dept_salary AS
SELECT dept.id AS dept_id, SUM(emp.salary) AS total, avg(emp.salary) AS avg,
    MIN(emp.salary) AS MIN, MAX(emp.salary) AS MAX
FROM Department dept
    INNER JOIN Employee emp
        ON emp.dept_id=dept.id
GROUP BY dept.id

Some More Reading

Ask Tom “Materialized Views
Secrets of Materialized Views
Materialized Views for Hierarchy Expansion

Hibernate HQL And Performance

The Hibernate ORM tool give you the ability to write SQL-esque queries using HQL to do custom joining, filtering, etc. to pull Objects from your database. The documentation gives you a lot of examples of the things you can do, but I haven’t seen any caveats or warnings.

Database Performance

As far as database performance goes there are two major things to start with when you want to understand your database performance:

  • How many queries are run?
  • How expensive are the individual queries?

Not too earth shattering is it? Basically if you run fewer queries of the same cost you’re better off. Likewise, if you make the queries themselves cost less (by optimizing the queries themselves, creating the proper indexes, etc) then they will run faster. So of course the best is to do both. Identify you to run fewer, faster queries. (Yes, I’m still waiting on my Nobel prize.)

I’ll talk more about fewer queries later…

To make queries faster, you mostly are working in the database. You depend on good tools and good statistics. If the size and kind of data changes, you might have to redo this stuff.

To Optimize your database queries:

  1. Run some queries examining their execution plans
  2. Find some possible columns to index
  3. Create an index
  4. Re-run the queries and examine the execution plans again
  5. Keep it if it’s faster, get rid of it if it’s not
  6. Goto 1

Hibernate and Caches

Hibernate does one thing: It maps Objects to a Relational database. Hibernate is really pretty good at that mapping and can support all kinds of schemas. So you should be able to (relatively) easily map your objects to your schema.

Hibernate also has two potential caching schemes. What it calls Level-1 and Level-2 caching. Level-1 caching is done through the Hibernate session. As long as the Hibernate session is open, any object that you have loaded will be pulled from the session if you query for it again.

The Level-2 cache is a longer-running, more advanced caching scheme. It allows you to store objects across Hibernate sessions. You’re often discouraged against using Level-2 caching, but it is very nice for read-only objects that you don’t expect to change in the database (think of pre-defined type information and the like). Again, if you query or one of these objects using Hibernate, then you’ll get an object from the Level-2 cache.

Notice how the Level-1 and Level-2 cache prevent Hibernate from having to re-query the database for a lot of objects. This of course can be a huge performance benefit. Likewise, Hibernate supports Lazy Loading of collections, so if your object is related to a collection of other objects, Hibernate will wait to load them until you need them. Once they’ve been loaded though, they are in the Object graph, so accessing them a second time does not require another round-trip to the database.

All of this lazy loading and caching is about reducing the number of queries you need to run against the database. You can also tweak your Hibernate mapping files to implement things like batching (loading children of multiple parents in one query) to greatly reduce the number of queries that need to be run. You can also specify to pre-load a related object using a left join if you will always need the object and want to get both in the same query. Most of the decisions are dependent on your application and what you are doing, but they are very easy to play with in your configuration and see if they improve your application performance.

Why the hard time for HQL?

All of the Caching and tweaking you can do in your Hibernate mappings (or using Annotations) is totally wasted if you using HQL queries to load your objects.

If you specify a fetch=”join” in your mapping to do a left join and load a dependent object, that doesn’t get used when you use HQL to load the object, so you will be doing more queries than you need.

If you have natural mappings of parent/child relationships then the following code will only generate a single query to load the Person and a single query to get the Addresses.

Person p = session.get(Person.class, 1);
List

address = p.getAddresses();
List
address2 = p.getAddresses();

This code still only generates two queries:

Person p = session.createQuery("from Person where id=:id")
.setParameter("id", 1).uniqueResult();
List

address = p.getAddresses();
List
address = p.getAddresses();

But the following code generates twice as many queries to load the addresses.

Person p = session.createQuery("from Person where id=:id")
.setParameter("id", 1).uniqueResult();
List

address = session
.createQuery("from Addresses where person_id=:id")
.setParameter("id", 1).list();
List
address2 = session
.createQuery("from Addresses where person_id=:id")
.setParameter("id", 1).list();

Of course this is a totally contrived example, but if you’ve built out a large system with a Service Facade and DAOs these kinds of things can easily be hidden deep in the application where it would be hard to know whether a call would trigger a database call or not. So be very conscious of using HQL queries and the consequences of using them.

Hibernate rewards you for using natural relationships in your Objects. It rewards you with performance for building a POJO based Object Oriented system.

Hibernate HQL Rules

Rule #1: Don’t use HQL.
Rule #2: If you really need to use HQL, see Rule #1.
Rule #3: If you really, really need HQL and you know what you’re doing, then carefully use HQL.

Ok, so if I’m right about this, why is this not at the top of the HQL documentation? Don’t you think they should talk about this as a method of last resort?

Time to start reading POJOs in Action again.