-->
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
  [ 13 posts ] 
  Previous topic | Next topic 
Author Message
chris_1982
 Post subject: SQLGrammarException when filtering collection in mapping
PostPosted: Mon Jun 05, 2006 3:49 pm 
Newbie

Joined: Wed May 10, 2006 4:58 am
Posts: 10
Hallo,

I use a delete-flag instead of a delete-operation. But I a SQLGrammarException will be thrown when I try to filter the collection with a where in my mapping-file.

I use Hibernate 3.0.

Mapping-File:
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 package="mapping">
  <class lazy="false" name="Model" table="model" optimistic-lock="none">
    <id name="id" type="integer" unsaved-value="null">
      <column name="id" not-null="true" unique="true" index="PRIMARY"/>
      <generator class="native"/>
    </id>
    <property name="title" type="string" column="title" length="255"/>
    <property name="isCaravan" type="boolean" column="is_caravan"/>
    <property name="deleted" type="boolean" column="deleted"/>
    <property name="pic1" type="string" column="pic1" length="255"/>
    <property name="pic2" type="string" column="pic2" length="255"/>
    <property name="ranking" type="integer" column="ranking"/>
    <many-to-one name="series" entity-name="mapping.Series" cascade="none" foreign-key="model_ibfk_1" column="series_id"/>
    <bag name="substructure" inverse="true" cascade="none" lazy="false">
      <key foreign-key="substructure_ibfk_1" not-null="true" column="model_id"/>
      <one-to-many entity-name="mapping.Substructure"/>
    </bag>
    <bag name="equipmentPropertyMany" table="equipment_value" cascade="none" lazy="false" where="equipment_property.deleted=0">
      <key foreign-key="equipment_value_ibfk_1" not-null="true" column="model_id"/>
      <composite-element class="mapping.EquipmentValue">
        <parent name="model"/>
        <property name="text" type="string" column="text" length="255"/>
        <many-to-one name="equipmentProperty" entity-name="mapping.EquipmentProperty" cascade="none" foreign-key="equipment_value_ibfk_2" column="equipment_property_id"/>
      </composite-element>
    </bag>
  </class>
</hibernate-mapping>



Generated SQL:
Code:
    select
        equipmentp0_.model_id as model3_1_,
        equipmentp0_.text as text1_,
        equipmentp0_.equipment_property_id as equipment1_1_,
        equipmentp1_.id as id3_0_,
        equipmentp1_.title as title3_0_,
        equipmentp1_.ranking as ranking3_0_,
        equipmentp1_.deleted as deleted3_0_,
        equipmentp1_.is_caravan as is5_3_0_,
        equipmentp1_.sys_user_id as sys6_3_0_
    from
        kotep.equipment_value equipmentp0_
    left outer join
        kotep.equipment_property equipmentp1_
            on equipmentp0_.equipment_property_id=equipmentp1_.id
    where
        equipment_property.deleted = 0
        and equipmentp0_.model_id=?


Can anyone help me?

Thanks,
Chris
Top
 Profile  
 
pepelnm
 Post subject:
PostPosted: Tue Jun 06, 2006 5:50 pm 
Expert
Expert

Joined: Thu Sep 22, 2005 10:29 am
Posts: 285
Location: Almassera/Valencia/Spain/EU/Earth/Solar system/Milky Way/Local Group/Virgo Supercluster
in the where filter you must put SQL code, not HQL code.

try with
Code:
where="deleted=0"
Top
 Profile  
 
chris_1982
 Post subject:
PostPosted: Wed Jun 07, 2006 4:50 am 
Newbie

Joined: Wed May 10, 2006 4:58 am
Posts: 10
Hi,
then I also get an error, because "equipmentp0_.deleted=0" does not exist. It must be "equipmentp1_.deleted=0".

Code:
Hibernate:
    select
        equipmentp0_.model_id as model3_1_,
        equipmentp0_.text as text1_,
        equipmentp0_.equipment_property_id as equipment1_1_,
        equipmentp1_.id as id3_0_,
        equipmentp1_.title as title3_0_,
        equipmentp1_.ranking as ranking3_0_,
        equipmentp1_.deleted as deleted3_0_,
        equipmentp1_.is_caravan as is5_3_0_,
        equipmentp1_.sys_user_id as sys6_3_0_
    from
        kotep.equipment_value equipmentp0_
    left outer join
        kotep.equipment_property equipmentp1_
            on equipmentp0_.equipment_property_id=equipmentp1_.id
    where
        equipmentp0_.deleted=0
        and equipmentp0_.model_id=?
2006-06-07 10:43:37,688 ERROR [http-8080-Processor25] java.lang.String: org.hibernate.exception.SQLGrammarException: could not initialize a collection: [mapping.Model.equipmentPropertyMany#188]
....
Top
 Profile  
 
JayeshJ
 Post subject:
PostPosted: Wed Jun 07, 2006 5:46 am 
Regular
Regular

Joined: Tue May 16, 2006 3:32 am
Posts: 117
It is seaching the column in equipment_value table. If you could move the column here that would solve the problem. You could also add a class level 'where="delete=0"' condition on mapping.EquipmentProperty and remove the where from the collection. However, though it may work for this case (not sure it will, just guessing), I've never tried a class level "where" so I'm not sure how it will overall affect the usage of mapping.EquipmentProperty in other cases. You could see if it works in your case.
Top
 Profile  
 
chris_1982
 Post subject:
PostPosted: Wed Jun 07, 2006 6:18 am 
Newbie

Joined: Wed May 10, 2006 4:58 am
Posts: 10
Hi
JayeshJ wrote:
It is seaching the column in equipment_value table. If you could move the column here that would solve the problem.

It`s not possible. I need the deleted-flag in the equipment_property.


JayeshJ wrote:
You could also add a class level 'where="delete=0"' condition on mapping.EquipmentProperty and remove the where from the collection. However, though it may work for this case (not sure it will, just guessing), I've never tried a class level "where" so I'm not sure how it will overall affect the usage of mapping.EquipmentProperty in other cases. You could see if it works in your case.

It works fine only if I access equipment_property directly.

Is there a way to assign the alias of equipment_property in the generated sql by myself?
Top
 Profile  
 
pepelnm
 Post subject:
PostPosted: Wed Jun 07, 2006 7:40 am 
Expert
Expert

Joined: Thu Sep 22, 2005 10:29 am
Posts: 285
Location: Almassera/Valencia/Spain/EU/Earth/Solar system/Milky Way/Local Group/Virgo Supercluster
Which is the HQL query or the java code you are using?
Top
 Profile  
 
chris_1982
 Post subject:
PostPosted: Wed Jun 07, 2006 8:26 am 
Newbie

Joined: Wed May 10, 2006 4:58 am
Posts: 10
It's

Code:
hsession.createQuery("from Model where id = :id");
Top
 Profile  
 
pepelnm
 Post subject:
PostPosted: Wed Jun 07, 2006 12:52 pm 
Expert
Expert

Joined: Thu Sep 22, 2005 10:29 am
Posts: 285
Location: Almassera/Valencia/Spain/EU/Earth/Solar system/Milky Way/Local Group/Virgo Supercluster
Are you sure?
I don't see table model in the generated query.
Top
 Profile  
 
chris_1982
 Post subject:
PostPosted: Wed Jun 07, 2006 1:07 pm 
Newbie

Joined: Wed May 10, 2006 4:58 am
Posts: 10
Yes,

it was only the affeted paragraph.


The complete generated SQL ist:

Code:
Hibernate:
    select
        series0_.id as id8_,
        series0_.ranking as ranking8_,
        series0_.title as title8_,
        series0_.is_caravan as is4_8_,
        series0_.deleted as deleted8_,
        series0_.simulated as simulated8_,
        series0_.sys_user_id as sys7_8_
    from
        kotep.series series0_
    where
        series0_.id=?
Hibernate:
    select
        model0_.id as id5_,
        model0_.title as title5_,
        model0_.is_caravan as is3_5_,
        model0_.deleted as deleted5_,
        model0_.pic1 as pic5_5_,
        model0_.pic2 as pic6_5_,
        model0_.ranking as ranking5_,
        model0_.series_id as series8_5_
    from
        kotep.model model0_
    where
        model0_.is_caravan=1
        and model0_.deleted=0
        and model0_.series_id=?
    order by
        model0_.ranking
Hibernate:
    select
        series0_.id as id8_0_,
        series0_.ranking as ranking8_0_,
        series0_.title as title8_0_,
        series0_.is_caravan as is4_8_0_,
        series0_.deleted as deleted8_0_,
        series0_.simulated as simulated8_0_,
        series0_.sys_user_id as sys7_8_0_
    from
        kotep.series series0_
    where
        series0_.id=?
Hibernate:
    select
        equipmentp0_.model_id as model3_1_,
        equipmentp0_.text as text1_,
        equipmentp0_.equipment_property_id as equipment1_1_,
        equipmentp1_.id as id3_0_,
        equipmentp1_.title as title3_0_,
        equipmentp1_.ranking as ranking3_0_,
        equipmentp1_.deleted as deleted3_0_,
        equipmentp1_.is_caravan as is5_3_0_,
        equipmentp1_.sys_user_id as sys6_3_0_
    from
        kotep.equipment_value equipmentp0_
    left outer join
        kotep.equipment_property equipmentp1_
            on equipmentp0_.equipment_property_id=equipmentp1_.id
    where
        equipmentp0_.deleted=0
        and equipmentp0_.model_id=?
2006-06-07 19:00:16,086 ERROR [http-8080-Processor25] java.lang.String: org.hibernate.exception.SQLGrammarException: could not initialize a collection: [mapping.Model.equipmentPropertyMany#188]
log4j:ERROR Failed to rename [kotep.log] to [kotep.log.2006-06-07_18-59].
2006-06-07 19:00:16,086 ERROR [http-8080-Processor25] java.lang.String: org.hibernate.exception.SQLGrammarException: could not initialize a collection: [mapping.Model.equipmentPropertyMany#188]
log4j:ERROR Failed to rename [kotep.log] to [kotep.log.2006-06-07_18-59].
2006-06-07 19:00:16,086 ERROR [http-8080-Processor25] java.lang.String: org.hibernate.exception.SQLGrammarException: could not initialize a collection: [mapping.Model.equipmentPropertyMany#188]
log4j:ERROR Failed to rename [kotep.log] to [kotep.log.2006-06-07_18-59].
2006-06-07 19:00:16,086 ERROR [http-8080-Processor25] java.lang.String: org.hibernate.exception.SQLGrammarException: could not initialize a collection: [mapping.Model.equipmentPropertyMany#188]
log4j:ERROR Failed to rename [kotep.log] to [kotep.log.2006-06-07_18-59].
Top
 Profile  
 
pepelnm
 Post subject:
PostPosted: Wed Jun 07, 2006 4:19 pm 
Expert
Expert

Joined: Thu Sep 22, 2005 10:29 am
Posts: 285
Location: Almassera/Valencia/Spain/EU/Earth/Solar system/Milky Way/Local Group/Virgo Supercluster
I have thought of two solutions:

First, quick and dirty (very quick and dirty):
Code:
where="equipmentp1_.deleted=0"


Second:
A cascaded delete-flag in equipment_value table so then
Code:
where="deleted=0"
would work.
Top
 Profile  
 
chris_1982
 Post subject:
PostPosted: Wed Jun 07, 2006 5:45 pm 
Newbie

Joined: Wed May 10, 2006 4:58 am
Posts: 10
Hi,

The first way does only work for :
Code:
hsession.createQuery("from Model where id = :id");


When I access the entity by another query, I get a MappingException.


The second way sounds interesting. How do I implement a "cascaded delete-flag". Are there any hibernate functions?

Triggers are unfortunately not possible, beacause MySQL 4.1 is used.
Top
 Profile  
 
pepelnm
 Post subject:
PostPosted: Fri Jun 09, 2006 4:19 am 
Expert
Expert

Joined: Thu Sep 22, 2005 10:29 am
Posts: 285
Location: Almassera/Valencia/Spain/EU/Earth/Solar system/Milky Way/Local Group/Virgo Supercluster
Are you using DAO objects?

Your <hibernate-mapping default-access> is "property" (default value)?
Top
 Profile  
 
chris_1982
 Post subject:
PostPosted: Fri Jun 09, 2006 8:31 am 
Newbie

Joined: Wed May 10, 2006 4:58 am
Posts: 10
pepelnm wrote:
Are you using DAO objects?

No, i didn't.

pepelnm wrote:
Your <hibernate-mapping default-access> is "property" (default value)?

Yes, it is.
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
  [ 13 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.