Hibernate Community • View topic - update of table per hierarchy

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

update of table per hierarchy

Page 1 of 1

[ 5 posts ]

Previous topic | Next topic

Author

Message

yevgeny88

Post subject: update of table per hierarchy

Posted: Tue Apr 01, 2008 3:03 pm

Newbie

Joined: Tue Apr 01, 2008 2:52 pm
Posts: 6

Hi,
I believe I have a very common and simple question but anyway I cannot find an answer on it.
I have a table per hierarchy strategy with a joined secondary table.

Code:

@javax.persistence.Entity(name="Card")
@javax.persistence.Table(name="Card")
@javax.persistence.DiscriminatorColumn(name="type")
@javax.persistence.Inheritance(strategy=InheritanceType.SINGLE_TABLE)
public class Card
{
   @javax.persistence.Id
   Integer id;

   @javax.persistence.Basic
   String type;

   @javax.perisistence.Basic
   Integer status;
}

@javax.persistence.Entity(name="PhyCard")
@javax.persistence.DiscriminatorValue("Phycard")
@javax.persistence.SecondaryTable{name="PhyCard",
   pkJoinColumns={@javax.persistence.PrimaryKeyJoinColumn(name="id")}
)
public class PhyCard extends Card
{
   @javax.persistence.Basic
   Integer bw;
}

It generated tables:

Code:

create table Card{
    id integer,
    type varchar,
    status integer,
    primary key(id) };

create table PhyCard{
   id integer,
   bw integer,
   primary key(id) };

I successfully created the tables and inserted data there.
Now, I want to update PhyCard (subclass) using hql query:

Code:

update PhyCard set status=1 where id=1;

I expected it to generate a SQL query like

Code:

update Card set status=1 where id=1;

since the field status belongs to Card, not to PhyCard. Why should Hibernate access PhyCard at all? The query could also have an additional predicate for extra validation:

Code:

update Card set status=1 where id=1 and type='PhyCard';

But Hibernate started to do something different. It generated query:

Code:

insert into HT_Card select card.id as id from Card card left outer join PhyCard phycard on card.id=phycard.id where type='UspCard' and id=1;

(I changed the aliases used by Hibernate to make the query more readable)
Why Hibernate needs to join Card and PhyCard in this case I don't know,
but the main problem is that the SQL query fails since id in the where clause is ambiguous. The same field 'id' belongs to two tables.

I tried to alias it in the original query:

Code:

update PhyCard as p set status=1 where p.id=1;

Hibernate stripped the alias from the query and failed for the same reason.
What do I do wrong? I could not find the answer neither in the documentation nor in the forum.

Thank you,
yevgeny

Top

sagimann

Post subject: same prob...

Posted: Tue Apr 29, 2008 3:12 pm

Senior

Joined: Sun Jun 11, 2006 10:41 am
Posts: 164

Hi,
I'm having a similar problem trying a delete like so:

Code:

em.createQuery("delete from JoinedSubClass1 c where c.id = 3").executeUpdate();

The SQL trace shows the nested (internal) query fails:

create temporary table if not exists HT_JoinedSubClass1 (id bigint not null)
insert into HT_JoinedSubClass1 select joinedsubc0_.id as id from JoinedSubClass1 joinedsubc0_ inner join JoinedBaseClass joinedsubc0_1_ on joinedsubc0_.id=joinedsubc0_1_.id where id=3
I was actually expecting the final where clause to read:
where joinedsubc0_.id=3

Exception:
Caused by: com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Column 'id' in where clause is ambiguous

My entities:

Code:

@Entity
@Inheritance(strategy=InheritanceType.JOINED)
@DiscriminatorColumn(name="classname", discriminatorType=DiscriminatorType.STRING, length=255)
public abstract class JoinedBaseClass implements Serializable {
    
    @Id
    @GeneratedValue
    public Long getId() { return this.id; }
    public void setId(Long id) { this.id = id; }
    private Long id;
    
}

@Entity
@DiscriminatorValue(value="joinedsubclass1")
public class JoinedSubClass1 extends JoinedBaseClass {
    
    @Basic
    public String getSubProp1() { return bp1; }
    public void setSubProp1(String bp) { this.bp1 = bp; }
    private String bp1;
}

did you find a solution?
thanks.

Last edited by sagimann on Wed Apr 30, 2008 2:59 am, edited 1 time in total.

Top

yevgeny88

Post subject:

Posted: Tue Apr 29, 2008 3:46 pm

Newbie

Joined: Tue Apr 01, 2008 2:52 pm
Posts: 6

Yes, I hacked the Hibernate code. In particular, I added

Code:

 
else if ( fromElement.getWalker().getStatementType() ==    HqlSqlTokenTypes.UPDATE &&
                    (fromElement.getEntityPersister() instanceof JoinedSubclassEntityPersister
                            || (fromElement.getEntityPersister() instanceof SingleTableEntityPersister
                                 &&  fromElement.getQueryable().isMultiTable() ))

                    && fromElement.getWalker().getCurrentClauseType() == HqlSqlTokenTypes.WHERE) {
               
                return propertyMapping.toColumns( tableAlias, path );
            }
else if ( fromElement.getWalker().getStatementType() == HqlSqlTokenTypes.DELETE
                    &&
                    (fromElement.getEntityPersister() instanceof JoinedSubclassEntityPersister
                    || (fromElement.getEntityPersister() instanceof SingleTableEntityPersister
                            &&  fromElement.getQueryable().isMultiTable() )) &&

                    fromElement.getWalker().getCurrentClauseType() == HqlSqlTokenTypes.WHERE) {
                return propertyMapping.toColumns( tableAlias, path );
            }

to org.hibernate.hql.ast.tree.FromElementType.toColumns(...), just before the last else.

It works.

Regards,
Yevgeny[/code]

Top

sagimann

Post subject: hibernate issue?

Posted: Wed Apr 30, 2008 3:00 am

Senior

Joined: Sun Jun 11, 2006 10:41 am
Posts: 164

Maybe open an issue with Hibernate about this?

Top

yevgeny88

Post subject:

Posted: Wed Apr 30, 2008 10:25 am

Newbie

Joined: Tue Apr 01, 2008 2:52 pm
Posts: 6

I did.

Top

Page 1 of 1

[ 5 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