Read the rules before posting!
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 2.1.7

Mapping documents:

<hibernate-mapping>
<class
name="com.model.Organization"
table="organization"
dynamic-update="false"
dynamic-insert="false"
select-before-update="false"
optimistic-lock="version"
discriminator-value="0"
>

<id
name="id"
column="id"
type="java.lang.Long"
unsaved-value="null"
>
<generator class="native">
</generator>
</id>

<discriminator
column="discriminator"
type="char"
length="1"
/>

<property
name="name"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="name"
length="30"
not-null="true"
/>


<property
name="activeFlag"
type="java.lang.Boolean"
update="true"
insert="true"
access="property"
column="activeFlag"
not-null="true"
/>


<subclass
name="com.model.Retailer"
dynamic-update="false"
dynamic-insert="false"
discriminator-value="R"
>

<bag
name="retailerItems"
lazy="true"
inverse="true"
cascade="all"
where="where activeFlag = 1"
>

<key
column="fk_retailer_id"
>
</key>

<one-to-many
class="com.model.RetailerItem"
/>

</bag>


</subclass>


</class>

</hibernate-mapping>



<hibernate-mapping>
<class
name="com.model.RetailerItem"
table="retailer_item"
dynamic-update="false"
dynamic-insert="false"
select-before-update="false"
optimistic-lock="version"
>

<id
name="id"
column="id"
type="java.lang.Long"
unsaved-value="null"
>
<generator class="native">
</generator>
</id>

<property
name="name"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="name"
/>

<property
name="activeFlag"
type="java.lang.Boolean"
update="true"
insert="true"
access="property"
column="activeFlag"
not-null="true"
/>

<many-to-one
name="retailer"
class="com.model.Retailer"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
column="fk_retailer_id"
/>
.
.

</class>

</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():

Retailer retailer = retailerDAO.getRetailer(new Long(1));
testList = retailer.getRetailerItems();


Full stack trace of any exception that occurs:

[junit] WARN [main] JDBCExceptionReporter.logExceptions(57) | SQL Err
or: 1064, SQLState: 42000
[junit] ERROR [main] JDBCExceptionReporter.logExceptions(58) | Syntax
error or access violation message from server: "You have an error in your SQL s
yntax; check the manual that corresponds to your MySQL server version for the ri
ght syntax to use near 'where retailerit0_.activeFlag = 1' at line 1"
[junit] WARN [main] JDBCExceptionReporter.logExceptions(57) | SQL Err
or: 1064, SQLState: 42000
[junit] ERROR [main] JDBCExceptionReporter.logExceptions(58) | Syntax
error or access violation message from server: "You have an error in your SQL s
yntax; check the manual that corresponds to your MySQL server version for the ri
ght syntax to use near 'where retailerit0_.activeFlag = 1' at line 1"
[junit] ERROR [main] PersistentCollection.initialize(198) | Failed to
lazily initialize a collection
[junit] net.sf.hibernate.exception.SQLGrammarException: could not initialize
collection: [com.model.Retailer.retailerItems#17]
[junit] at net.sf.hibernate.exception.ErrorCodeConverter.convert(ErrorCo
deConverter.java:69)
[junit] at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCEx
ceptionHelper.java:30)
[junit] at net.sf.hibernate.collection.AbstractCollectionPersister.conve
rt(AbstractCollectionPersister.java:728)
[junit] at net.sf.hibernate.collection.AbstractCollectionPersister.initi
alize(AbstractCollectionPersister.java:291)
[junit] at net.sf.hibernate.impl.SessionImpl.initializeCollection(Sessio
nImpl.java:3303)
[junit] at net.sf.hibernate.collection.PersistentCollection.initialize(P
ersistentCollection.java:195)
[junit] at net.sf.hibernate.collection.PersistentCollection.read(Persist
entCollection.java:71)
[junit] at net.sf.hibernate.collection.Bag.size(Bag.java:232)
[junit] at com.dao.RetailerDAOTest.testUpdateRetailer(Ret
ailerDAOTest.java:320)
[junit] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[junit] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAcces
sorImpl.java:39)
[junit] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMet
hodAccessorImpl.java:25)
[junit] at java.lang.reflect.Method.invoke(Method.java:324)
[junit] at junit.framework.TestCase.runTest(TestCase.java:154)


MySql 4.1 but will be migratingto DB2

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

I am trying to restrict the collection in, retailer.getRetailerItems() call, to values that contain an activeFlag of true(1) on the child (RetailerItems) side. I am atempting this through the bag attribute - where="where activeFlag = 1". The exception states the error is due to bad SQL - 'where retailerit0_.activeFlag = 1'. Where is this coming from?

I cannot figure out the correct syntax to restrict the collection. The collection is being lazily initialized. I am approaching this the correct way. My goal is to have the parent class Retailer be able to return a collection of children RetailerItems where the child collection only contains values with an activeFlag of true.

Retailer is a subclass of Organization in this example. Not sure if that is significant.

Help is greatly appreciated.
Thanks
Tim

Old question, but if you are googling 'Hibernate bag restriction with where attribute' or similar first that occurs and has appropriate question, but without answer. To save your time:

<bag
name="retailerItems"
lazy="true"
inverse="true"
cascade="all"
where="activeFlag = 'true'"
>