-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 10 posts ] 
  Previous topic | Next topic 
Author Message
ernst_pluess
 Post subject: how to map a denormalized parent child relation?
PostPosted: Wed Sep 15, 2004 2:35 pm 
Expert
Expert

Joined: Thu Jan 29, 2004 2:31 am
Posts: 362
Location: Switzerland, Bern
I've a Table, lets call it A. This Table stores a parent child relation in a denormalized way. I could (in theory) split A
into A1(1)<--A2(n) which forms a 1:n relation.

This of course means every column of A1 is duplicated for every corresponding A2. With a
Code:
select distinc all_columns_of_A1 from A
I would get what TableA1 should be.

Furhter more I've to stick to the following requirements:
  1. I've about 50 Tables which follow the (anti?)pattern descirbed above.
  2. This tables do reference each other with 1:1, 1:n and m:n relations.
  3. I've to live with composite ids, but I've a CompositeUserType and my own IdGenerator so I can use the id tag and rellay on cascading where usefull.
  4. I can't do heavy changes (i.e. normalize the tables), because an other application is using the schema as well.
  5. I can do addons like creating a view representing A1 or create a A1 table and update it with a trigger if A is updated/inserted.

My question is how I can map this with Hibernate?


I tried already to map it by creating a view A1 with
Code:
select distinc all_columns_of_A1 from A
and have TableA1-->TableA forming a parent child relation.

This works fine until hibernate tries to update the view. By not using the cascading I managed to avoid the insert/update calls to the view, but then
I get an exception telling me, that I have to call save for A1. If there's a way to tell hibernate not to do inserts/updates for the view I could get it to work this way.

An other approach I was thinkin about was to create a real table A1 so hibernate can insert/update it.
The point here is, that I must be sure that hibernate always updates/inserts the parent A1 before it updates/inserts the children A in order I can write a trigger which fills A1 if A is updated/inserted without a corresponding A1 by the non hibernated application.


I'm hoping that someone can point me to a nice hibernate feature which solves the problem nicely :-).

TIA
Ernst
Top
 Profile  
 
ernst_pluess
 Post subject:
PostPosted: Thu Sep 16, 2004 3:13 am 
Expert
Expert

Joined: Thu Jan 29, 2004 2:31 am
Posts: 362
Location: Switzerland, Bern
I try to simplify my questions:
  1. Assume I have the following DB schema:

    TableA<--ViewB<--TableC

    ViewB consists solely of colums of TableC.

    I want to map this to:
    ClassA-->ClassB-->ClassC

    All --> are parent child relaitions.

    How can I tell hibernate not to do any updates/inserts on ViewB?
  2. Assume the following DB schema:

    TableA<--TableB

    And I want this with a parent child relation to
    ClassA-->ClassB
    Is it guaranteed, that the inserts/updateds for TableA are executed before the inserts/updates for TableB?


Of course I would still be very happy to see an other approach to map a denormalized parent child relation.

TIA
Ernst
Top
 Profile  
 
michael
 Post subject:
PostPosted: Thu Sep 16, 2004 4:47 am 
Hibernate Team
Hibernate Team

Joined: Tue Sep 09, 2003 2:10 pm
Posts: 3246
Location: Passau, Germany
If you already got a view, can't you just create insert/update/save triggers for it? e.g. make it updateable?

Or if you just want to read the data, map the class with mutable=false
Top
 Profile  
 
ernst_pluess
 Post subject:
PostPosted: Thu Sep 16, 2004 8:46 am 
Expert
Expert

Joined: Thu Jan 29, 2004 2:31 am
Posts: 362
Location: Switzerland, Bern
Thanks Michael, that's a good idea.

I tried it with Oracle and it does exactly what I want it to do.
I'v to support Sybase as well. After checking the docs, I think Sybase cannot have triggers on views :-(.

Their's no direct way of mapping the denormalized table to parent-child classes?

TIA
Ernst
Top
 Profile  
 
michael
 Post subject:
PostPosted: Thu Sep 16, 2004 8:48 am 
Hibernate Team
Hibernate Team

Joined: Tue Sep 09, 2003 2:10 pm
Posts: 3246
Location: Passau, Germany
No, no builitin way (at least none with reasonable effort). You will have to solve this at the database level.
Top
 Profile  
 
michael
 Post subject:
PostPosted: Thu Sep 16, 2004 8:48 am 
Hibernate Team
Hibernate Team

Joined: Tue Sep 09, 2003 2:10 pm
Posts: 3246
Location: Passau, Germany
Probably if you can wait for Hibernate3, you could solve it there by defining custom SQL queries for the CRUD operations.
Top
 Profile  
 
ernst_pluess
 Post subject:
PostPosted: Thu Sep 16, 2004 9:27 am 
Expert
Expert

Joined: Thu Jan 29, 2004 2:31 am
Posts: 362
Location: Switzerland, Bern
With Hibernate3, in order to prevent any insert/updates could I map something like that:
Code:
<sql-insert>select 1</sql-insert>
<sql-update>select 1</sql-update>


TIA
Ernst
Top
 Profile  
 
michael
 Post subject:
PostPosted: Thu Sep 16, 2004 9:31 am 
Hibernate Team
Hibernate Team

Joined: Tue Sep 09, 2003 2:10 pm
Posts: 3246
Location: Passau, Germany
You can also just use mutable=false in your class mappings, even in H2
Top
 Profile  
 
ernst_pluess
 Post subject:
PostPosted: Thu Sep 16, 2004 10:28 am 
Expert
Expert

Joined: Thu Jan 29, 2004 2:31 am
Posts: 362
Location: Switzerland, Bern
That was my very first try, but this seamed to have no effect at all.
If I called save for the class mapped with mutable=false, hibernate tryied to insert into the view, if I omited the save I got an exception telling me to call save ...

AFAIK mutable=false means, that this table/view never changes. But that's not true for my view, since the underlying table does change.
If I create a new ClassB and a new ClassC everything must be written into the denormalized TableC, which works fine with oracle and the trigger.

Quote:
TableA<--ViewB<--TableC

ViewB consists solely of colums of TableC.

I want to map this to:
ClassA-->ClassB-->ClassC


Is my understanding of mutable ok?

TIA
Ernst
Top
 Profile  
 
ernst_pluess
 Post subject:
PostPosted: Fri Sep 17, 2004 11:02 am 
Expert
Expert

Joined: Thu Jan 29, 2004 2:31 am
Posts: 362
Location: Switzerland, Bern
Oky doky. I just gave hibernate 3 a try and mapped the parent as you can see below. (the loader and sql-insert tags are the interesting ones)

Since the Oracle jdbc driver is flexible enough to execute a select statement where an insert or update should come, this works fine.

But I'm again at a loss with Sybase. It does not accept the select (minus the dual of course). I replaced it with a "no op" update, but then hibernate isn't happy because the returned row count is 0 instead of 1. (see exception stack trace below)

I also tried to map the columns with a formula, but this produced a ClassCastException (see below).

Any ideas about how to map this with h3 for Oracle and Sybase are very welcome!

TIA
Ernst


Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
   <class
      name="ch.bedag.gba.cap.server.business.model.grundstueck.GebaeudeHist" table="TAR">
      <id name="myPk"
         type="ch.bedag.gba.cap.server.persistence.hibernate.CapiPkType" access="field">
         <column name="FAR01" sql-type="systemId" not-null="true"/>
         <column name="FAR03" sql-type="id" not-null="true"/>
         <generator class="ch.bedag.gba.cap.server.persistence.hibernate.CapiIdentifierGenerator"/>
      </id>
      <many-to-one name="myGrundstueck" update="false" insert="false" access="field"
            class="ch.bedag.gba.cap.server.business.model.grundstueck.Grundstueck">
         <column name="FAR01"/>
         <column name="FAR07"/>
      </many-to-one>
      <property name="myGrundstueckPID" column="FAR07" access="field"></property>
      <loader query-ref="loadGrundstueckHist"/>
      <sql-insert>select ?, ?, ? from dual  -- instead of calling update on TAR</sql-insert>
      <sql-update>select ?, ?, ? from dual  -- instead of calling update on TAR</sql-update>
      <sql-delete>select ?, ?, ? from dual  -- instead of calling delete on TAR</sql-delete>      
   </class>
   <sql-query name="loadGrundstueckHist">
      <return alias="g" class="ch.bedag.gba.cap.server.business.model.grundstueck.GebaeudeHist"/>
      select distinct FAR01 as {g.myPk.systemId},
                      FAR03 as {g.myPk.id},
                      FAR07 as {g.myGrundstueckPID}
         from TAR
   </sql-query>
</hibernate-mapping>


Exception with not machting row count:
Code:
org.hibernate.HibernateException: SQL insert, update or delete failed (row not found)
   at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:25)
   at org.hibernate.persister.BasicEntityPersister.insert(BasicEntityPersister.java:1598)
   at org.hibernate.persister.BasicEntityPersister.insert(BasicEntityPersister.java:1878)
   at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:37)
   at org.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:1224)
   at org.hibernate.event.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:516)
   at org.hibernate.event.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:26)
   at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1191)
   at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:61)
   at ch.bedag.gba.cap.server.persistence.hibernate.HibernateDAO$HibernateTrx.commit(HibernateDAO.java:156)
   at ch.bedag.gba.server.persistence.TransactionManager.commitTransaction(TransactionManager.java:84)
   at ch.bedag.gba.cap.server.persistence.hibernate.HibernateHistTestCase.testReferenceToHistorized(HibernateHistTestCase.java:61)


Exception with formula in colum tags:
Code:
java.lang.ClassCastException
   at org.hibernate.cfg.Binder.makeIdentifier(Binder.java:1212)
   at org.hibernate.cfg.Binder.bindRootClass(Binder.java:465)
   at org.hibernate.cfg.Binder.bindRoot(Binder.java:1570)
   at org.hibernate.cfg.Configuration.add(Configuration.java:336)
   at org.hibernate.cfg.Configuration.addDocument(Configuration.java:325)
   at ch.bedag.gba.cap.server.persistence.hibernate.HibernateUtil.<init>(HibernateUtil.java:129)
   at ch.bedag.gba.cap.server.persistence.hibernate.HibernateUtil.<init>(HibernateUtil.java:94)
   at ch.bedag.gba.cap.server.persistence.hibernate.HibernateUtil$Booter.boot(HibernateUtil.java:270)
   at ch.bedag.gba.common.bootstrap.BootLevel.boot(BootLevel.java:107)
   at ch.bedag.gba.common.bootstrap.BootLevel.boot(BootLevel.java:102)
   at ch.bedag.gba.common.bootstrap.AppBootstrap.boot(AppBootstrap.java:49)
   at ch.bedag.gba.cap.server.application.CapiServerBootStrap.bootAll(CapiServerBootStrap.java:60)
   at ch.bedag.gba.cap.server.application.CapiServer.main(CapiServer.java:31)
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 10 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.