Using JOIN Mappings

rrva · **Joined:** Tue May 15, 2007 11:24 pm **Posts:** 2

Hi. I have the following existing db layout which I wish to map (postgresql) using hibernate 3.2.1:

Code:

CREATE TABLE "public"."category"(
"id" int4 NOT NULL DEFAULT nextval('category_id_seq'::regclass),
"category" varchar(300) NOT NULL ,
PRIMARY KEY ("id")
)  WITH OIDS;

CREATE TABLE "public"."news"(
"id" int4 NOT NULL DEFAULT nextval('news_id_seq'::regclass),
"title" varchar(300) NOT NULL ,
"date" date NOT NULL ,
"category_id" int8 NOT NULL ,
"text_id" int8 NOT NULL ,
PRIMARY KEY ("id"),
FOREIGN KEY ("text_id") REFERENCES "public"."text"("id") ON UPDATE NO ACTION ON DELETE NO ACTION ,
FOREIGN KEY ("category_id") REFERENCES "public"."category"("id") ON UPDATE NO ACTION ON DELETE NO ACTION 
)  WITH OIDS;

CREATE TABLE "public"."text"(
"id" int4 NOT NULL DEFAULT nextval('text_id_seq'::regclass),
"text" text NOT NULL ,
PRIMARY KEY ("id")
)  WITH OIDS;

And the following mapping:

Code:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN" 
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
    <class name="org.appfuse.model.NewsItem" table="news">
        <id name="id" column="id" unsaved-value="null">
            <generator class="increment"/>
        </id>
        <property name="title" column="title" not-null="true"/>
        <property name="date" column="date" type="java.util.Date" not-null="true"/>
        <property name="categoryId" column="category_id"/>
        <property name="textId" column="text_id"/>
        
        <join table="category">
        <key column="category_id" not-null="true"/>
        <property name="category"/>
        </join>
        <join table="text">
        <key column="text_id" not-null="true"/>
        <property name="text"/>
        </join>        
    </class>
</hibernate-mapping>

This does not work. Here the debug log and exceptions with full stack trace and generated SQL:

Code:

Hibernate: select max(id) from news
DEBUG - NewsItemDaoHibernate.saveNewsItem(34) | Id set to: 1
Hibernate: insert into news (title, date, category_id, text_id, id) values (?, ?, ?, ?, ?)
WARN - JDBCExceptionReporter.logExceptions(77) | SQL Error: 0, SQLState: null
ERROR - JDBCExceptionReporter.logExceptions(78) | Batch entry 0 insert into news (title, date, category_id, text_id, id) values (The title is coming, 2007-05-16 05:20:58.980000 +0200, NULL, NULL, 1) was aborted.  Call getNextException to see the cause.
WARN - JDBCExceptionReporter.logExceptions(77) | SQL Error: 0, SQLState: 23502
ERROR - JDBCExceptionReporter.logExceptions(78) | ERROR: null value in column "category_id" violates not-null constraint
ERROR - AbstractFlushingEventListener.performExecutions(301) | Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:249)
   at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:92)
   at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:87)
   at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2182)
   at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2610)
   at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:52)
   at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:232)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:139)
   at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
   at org.hibernate.event.def.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:41)
   at org.hibernate.impl.SessionImpl.autoFlushIfRequired(SessionImpl.java:969)
   at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1114)
   at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
   at org.springframework.orm.hibernate3.HibernateTemplate$29.doInHibernate(HibernateTemplate.java:846)
   at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:369)
   at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:837)
   at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:829)
   at org.appfuse.dao.hibernate.NewsItemDaoHibernate.getNewsItems(NewsItemDaoHibernate.java:19)
   at org.appfuse.dao.NewsItemDaoTest.testGetNewsItems(NewsItemDaoTest.java:23)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:585)
   at junit.framework.TestCase.runTest(TestCase.java:164)
   at junit.framework.TestCase.runBare(TestCase.java:130)
   at org.springframework.test.ConditionalTestCase.runBare(ConditionalTestCase.java:69)
   at junit.framework.TestResult$1.protect(TestResult.java:106)
   at junit.framework.TestResult.runProtected(TestResult.java:124)
   at junit.framework.TestResult.run(TestResult.java:109)
   at junit.framework.TestCase.run(TestCase.java:120)
   at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:128)
   at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into news (title, date, category_id, text_id, id) values (The title is coming, 2007-05-16 05:20:58.980000 +0200, NULL, NULL, 1) was aborted.  Call getNextException to see the cause.
   at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2512)
   at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1310)
   at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:347)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2574)
   at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
   at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:242)
   ... 36 more
INFO - AbstractTransactionalSpringContextTests.endTransaction(281) | Rolled back transaction after test execution

The code I am using:

Code:

    public void testGetNewsItems() {
        newsitem = new NewsItem();
        newsitem.setTitle("The title is coming");
        newsitem.setDate(new java.util.Date());
        newsitem.setCategory("Sadness");
        newsitem.setText("Foo");

        dao.saveNewsItem(newsitem);

        assertTrue(dao.getNewsItems().size() >= 1);
    }

public class NewsItem extends BaseObject {
    private static final long serialVersionUID = 3257568390917667125L;
    private Long id;
    private Long textId;
    private Long categoryId;
    private String title;
    private String category;
    private Date date;
    private String text;
....

How can I create a mapping for these three tables?

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

<join> assumes that the foreign table is joining to the table's PK. You want to join it to different properties (which is unnecessary given your schema, but what-the-hey).

In the two <key> elements inside the <join> elements, the column attribute refers to the PK of the foreign table. This is "id" in both cases, so change those. Add a new property-ref="categoryId"/property-ref="textId" as appropriate.

However, this is not a good schema. Is there any reason you can't do all this in one table? If there's a collection of categories/texts, you don't want <join>, you want <set> or similar. If there is a one-to-one and you don't want nested objects (and seems to be what you're going for), you can safely get rid of the category_id and text_id columns, and omit the aforementioned property-refs.

rrva · **Joined:** Tue May 15, 2007 11:24 pm **Posts:** 2

I can't change the schema. I don't want nested classes. I'm considering not using hibernate for this mapping at all, but I want to learn how it could be done.

Table news has a many-to-one association with the table category through news.category_id = category.id

news.category_id is a FK to the PK category.id

Similar for news--text.

I tried this mapping:

Code:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN" 
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
   <class name="org.appfuse.model.NewsItem" table="news">
      <id name="id" column="id" unsaved-value="null">
         <generator class="increment" />
      </id>
      <property name="title" column="title" not-null="true" />
      <property name="date" column="date" type="java.util.Date"
         not-null="true" />
      <property name="categoryId" column="category_id"
         not-null="true" />
      <property name="textId" column="text_id" not-null="true" />
      <join table="category">
         <key column="id" not-null="true" property-ref="categoryId" />
         <property name="category" />
      </join>
      <join table="text">
         <key column="id" not-null="true" property-ref="textId" />
         <property name="text" />
      </join>

   </class>
</hibernate-mapping>

And I get:

Code:

org.springframework.dao.DataIntegrityViolationException: not-null property references a null or transient value: org.appfuse.model.NewsItem.categoryId; nested exception is org.hibernate.PropertyValueException: not-null property references a null or transient value: org.appfuse.model.NewsItem.categoryId
Caused by: org.hibernate.PropertyValueException: not-null property references a null or transient value: org.appfuse.model.NewsItem.categoryId
   at org.hibernate.engine.Nullability.checkNullability(Nullability.java:72)
   at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:284)
   at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:180)
   at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:121)
   at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:186)
   at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:175)
   at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:98)
   at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70)
   at org.hibernate.impl.SessionImpl.fireSaveOrUpdate(SessionImpl.java:507)
   at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:499)
   at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:495)
   at org.springframework.orm.hibernate3.HibernateTemplate$16.doInHibernate(HibernateTemplate.java:686)
   at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:369)
   at org.springframework.orm.hibernate3.HibernateTemplate.saveOrUpdate(HibernateTemplate.java:683)
   at org.appfuse.dao.hibernate.NewsItemDaoHibernate.saveNewsItem(NewsItemDaoHibernate.java:31)
   at org.appfuse.dao.NewsItemDaoTest.testGetNewsItems(NewsItemDaoTest.java:21)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:585)
   at junit.framework.TestCase.runTest(TestCase.java:164)
   at junit.framework.TestCase.runBare(TestCase.java:130)
   at org.springframework.test.ConditionalTestCase.runBare(ConditionalTestCase.java:69)
   at junit.framework.TestResult$1.protect(TestResult.java:106)
   at junit.framework.TestResult.runProtected(TestResult.java:124)
   at junit.framework.TestResult.run(TestResult.java:109)
   at junit.framework.TestCase.run(TestCase.java:120)
   at junit.framework.TestSuite.runTest(TestSuite.java:230)
   at junit.framework.TestSuite.run(TestSuite.java:225)
   at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:128)
   at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Obviously <join> won't populate a PK of category or text. That makes sense, as you have to insert the rows in category and text before you know the values that will go into textId and categoryId (this is one of the reasons that this is a bad schema). You'll have to use <many-to-one unique="true" join="fetch"> and nested objects. You can hide this through delegation, though.

joerg20 · **Joined:** Tue Dec 12, 2006 5:01 am **Posts:** 11

I guess my problem is very similar. I use Liferay as portal server and they have following tables:

Code:

create table User_ (
   userId bigint not null primary key,
   ...
   contactId bigint,
   ...
}

where contactId is the foreign key to table Contact_:

Code:

create table Contact_ (
   contactId bigint not null primary key,
   ...
}

Up to recently it was actually a primary key relationship, i.e. userId = contactId, so I could easily use the following mapping:

Code:

<class name="User" table="user_">
  <id name="id" column="userid">
    <generator class="assigned"/>
  </id>
  <join table="contact_">
    <key column="contactid"/>
  </join>
</class>

Now Liferay 4.3 introduced the foreign key to User_ table and my mapping fails. From what I understand there is really no way to get it working again mapping multiple tables to one object, isn't it? Any reason why this is not supported at all? It seems sooo trivial.

And you can't hide this through delegation. Hibernate makes it impossible. I have for example the criteria API and ordering in mind where you need the full path to the property you are ordering by. Other frameworks can manage this and really hide delegation like the data binding in Spring.

Joerg

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Of course you can use delegation to hide it. You have methods, associations etc. that hibernate is aware of and that you use in your HQL and Criteria, and you have public API methods that delegate. So when your API code is able to do "value = delegator.getValue()", the equivalent Criteria would have to be "crit = sess.createCriteria(Delegator.class).createAlias("priv").add(Restrictions.eq("priv.value", value))".

Anyway, back to your question. Yes you can do what you want. If the many-to-one/many-to-many unique="true" solution (using delegation) doesn't appeal, you can use joined-subclass. You'll need an abstract class for User which defines contactId as a property. Then your UserContact concrete class, defined as a joined-subclass, can use key column="contactId" property-ref="contactId".

My preferred solution in this case, assuming a true one-to-one, would be to use <one-to-one property-ref="contactId">, with optional delegation. It may be desirable to not use a single class in the API: what if they change their model in the future to allow one user to have multiple rows in the Contact table?

joerg20 · **Joined:** Tue Dec 12, 2006 5:01 am **Posts:** 11

tenwit wrote:

Of course you can use delegation to hide it. You have methods, associations etc. that hibernate is aware of and that you use in your HQL and Criteria, and you have public API methods that delegate. So when your API code is able to do "value = delegator.getValue()", the equivalent Criteria would have to be "crit = sess.createCriteria(Delegator.class).createAlias("priv").add(Restrictions.eq("priv.value", value))".

What I meant is that you need the exact path for ordering in the criteria API. Since I use DetachedCriteria I now have to adapt code on completely different place. I query a particular object and want to sort it by lastname, a property of user (or now contact). Despite that it actually does not work it has to look like the following:

Code:

criteria.createAlias("user", "u").addOrder(Order.asc("u.lastname"));

changes to

Code:

criteria.createAlias("user.contact", "u").addOrder(Order.asc("u.lastname"));

Actually I need to know that there is a delegation - that's not what I consider a hidden detail.

tenwit wrote:

you can use joined-subclass. You'll need an abstract class for User which defines contactId as a property. Then your UserContact concrete class, defined as a joined-subclass, can use key column="contactId" property-ref="contactId".

I have now the following:

Code:

<class name="AbstractUser" abstract="true" table="user_">
  <id name="id" column="userId">
  <property name="contactId"/>
  <joined-subclass name="User" table="contact_">
    <key column="contactId" property-ref="contactId"/>
    <property name="lastname" type="string"/>
  </joined-subclass>
</class>

This seems not to work for two reasons:
1. It again tries to match the primary key of User_ with the primary key of Contact_. The same happened when using the former solution with <join>:

Code:

select ... from contact_ user0_ inner join user_ user0_1_ on user0_.contactId=user0_1_.userId where user0_.contactId=?

2. When searching for AbstractUser it fails since it tries to instantiate it. When searching for User it does not find them since it searches for them by contactId, not by userId. But I only have the userId. That's what you can access in a portlet by PortletRequest.getUserPrincipal() (as for HttpServletRequest).

But even if I get that one to work, is Hibernate mapping in anyway intuitive? I don't consider myself to be simple-minded, but I wonder how one should find out such a work-around. The original version was at least somehow documented in the reference.

tenwit wrote:

what if they change their model in the future to allow one user to have multiple rows in the Contact table?

That's unlikely since Contact_ contains all the data of the user like firstname, lastname, birthday etc. while User_ only contains the access data to the portal (username, password, number of failed logins, etc.) But anyway, I only need very few details from the user like email and name. What I tried to do with the Hibernate mapping is to hide the actual portal database details. You can prove me wrong (see above), but it seems not to be possible.

Joerg

joerg20 · **Joined:** Tue Dec 12, 2006 5:01 am **Posts:** 11

joerg20 wrote:

Code:

criteria.createAlias("user.contact", "u").addOrder(Order.asc("u.lastname"));

That does not even work. It has to be

Code:

criteria.createAlias("user", "u").createAlias("user.contact", "c").addOrder(Order.asc("c.lastname"));

or

Code:

criteria.createAlias("user", "u").createAlias("u.contact", "c").addOrder(Order.asc("c.lastname"));

Joerg

joerg20 · **Joined:** Tue Dec 12, 2006 5:01 am **Posts:** 11

I'd really appreciate an update on this one. This seemingly necessary delegation introduces portal/ Liferay specifics into my portlets.

Thanks in advance,
Joerg