Mapping normalized rows

jamesntse@hotmail.com · **Joined:** Thu Dec 20, 2007 11:03 pm **Posts:** 2

Hi all,

I'm fairly new to Hibernate and I've been trying to figure out this problem with mapping normalized table data. I've looked through the FAQs, <join>, a few tutorials, and several chapters in _Java Persistence with Hibernate_ book but was only able to find partial solutions at best.

Let's say I have two database tables in a simple message forum app:

Code:

posts(id, user_id, title, body)
users(id, username)

My domain class Post is basically:

Code:

public class Post {
   private Long id;
   private String username;
   private String title;
   private String body;

   // getters/setters...
}

Essentially, I want Hibernate to map posts.user_id to users.username for my Post objects.

I know I can use the formula attribute of <property> to perform sub-select like so:

Code:

<class name=”package.Post” table=”posts”>
   <!-- ... properties... -->
   <property name=”username” type=”string” formula=”(select users.username from users where users.id = user_id)” />
</class>

This is fine if all I wanted to do read from the database. I would not, however, be able to change the value of username for my Post object (-- not that I would want to in a real application but this is just an example of selecting/inserting/updating normalized rows).

I tried using <join> but what ends up happening is that a new row in the table users where users.id is the same as the posts.id.

I know that Hibernate prefers one-class-per-table and I can replace String username in my Post class with a domain object User but I rather stick with a simple String mapping.

Is this possible to do this type of normalized/denormalized mapping Hibernate without resorting to custom SQL inserts/updates?

Thanks,

Jim

eloso · **Joined:** Fri Dec 28, 2007 5:10 pm **Posts:** 1

James,

One thing that might work is to use a <subselect> and a <sql-insert> in tandem. I'm new to Hibernate as well so if anyone else has another suggestion, I would love to hear it.

In your example, create your mapping like this:

Code:

<hibernate-mapping>
  <class name="package.Post" table="posts">
    <subselect>
       SELECT 
          posts.id      AS id,
          users.username      AS username,
          posts.title             AS title, 
                posts.body              AS body
        FROM posts
         LEFT JOIN users      ON posts.user_id = users.id
     </subselect>

    <property name="username" />
    <property name="title" />
    <property name="body" />
    

 <sql-insert>
       INSERT INTO posts(
             user_id,
             title,
             body
          )
       VALUES(
             (SELECT users.id FROM users WHERE username = ?)
             ?,
             ?
          )
    </sql-insert>
   
  </class>
</hibernate-mapping>

Also, for CRUD operations, you can use <sql-update> tag.

damo9f · **Posted:** Fri Dec 28, 2007 6:24 pm

I don't think you really want to do that. In your example, if you were to get the update working you would change a user's name whenever you changed the name in a post.

That is, your object model has the user name in every post, but you probably want the actual User object associated. This is also much better object-oriented practice where you "deal with whole objects" rather than primitive types.

Someday, when you add a first name and last name to the User object, or add code to validate the length, you'll be glad that it's a real object relationship, rather than (multiple) embedded queries that insert it directly into other objects.

jamesntse@hotmail.com · **Joined:** Thu Dec 20, 2007 11:03 pm **Posts:** 2

Eloso, I tried it and it works -- thanks! My only issue is that it seems a bit bunch having to hand write and manage SQL statements. Perhaps there is an easier way?

Damos9f, thanks for your comment. I agree with you in that it's not an ideal object model. Generally speaking, joining an object is better than joining a primitive (-- a string primitive in my case). However, I have work with an existing database where there is at least one case of normalized string data.

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

James,

The design of the Post class looks to me a little contrary to the philosophy of Hibernate.

You should probably not specify members of User flatly on the Post entity.

Instead:

Code:

  class Post{

  ....
  private User user;

  }

When in your code you want to access the user name from the a post, you do

Code:

   Post post;
   ...
   String name=post.getUser().getName();

In other words, your classes should be "normalized" even if your data model isn't. That small trick of having the user name embedded in the Post class ends up creating too many problems.

If your table model has, for example, username both in Post and in User, my advice would be to simply ignore it on Post.