List problem with index

chainsaw · **Posted:** Thu Dec 23, 2004 4:58 am

Hi,
I have a problem with a List. I don't want to use a Set, but a List.
The Exception wich occurs is below.
What do wrong in the mapping file with the List?

Hibernate version: 2.1.7c

Mapping documents:
<hibernate-mapping>
<class
name="Order"
table="Orders"
dynamic-update="false"
dynamic-insert="false"

>

<id
name="id"
column="Order_ID"
type="int"
unsaved-value="0"
>
<generator class="native">
</generator>
</id>

<list name="orderLineItems"
table="OrderLineItem"
lazy="true"
inverse="true"
cascade="save-update"

>
<key
column="Order_ID"
/>

<index column="index" type="int">
</index>
<one-to-many
class="OrderLineItem"

/>
</list>

<property
name="userName"
type="string"
update="true"
insert="true"
column="UserName"
not-null="true"
unique="false"
/>

<property
name="total"
type="double"
update="true"
insert="true"
column="Total"
not-null="false"
unique="false"
/>
</class>

</hibernate-mapping>

<hibernate-mapping>
<class
name="OrderLineItem"
table="OrderLineItem"
dynamic-update="false"
dynamic-insert="false"

>

<id
name="id"
column="OrderLineItem_ID"
type="int"
unsaved-value="0"
>
<generator class="native">
</generator>
</id>

<many-to-one
name="order"
class="Order"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="Order_ID"

/>

<property
name="description"
type="string"
update="true"
insert="true"
column="Description"
not-null="false"
unique="false"
/>

<property
name="lineItemPrice"
type="double"
update="true"
insert="true"
column="LineItemPrice"
not-null="false"
unique="false"
/>

<property
name="del"
type="int"
update="true"
insert="true"
column="del"
unique="false"
/>

<property name="index"
type="int"
update="true"
column="index"
insert="true"
>

</property>

</class>

</hibernate-mapping>

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

Full stack trace of any exception that occurs:
[23.12.04 09:44:25:714 CET] 18bd3fe7 SystemOut O Hibernate: insert into Orders (UserName, Total) values (?, ?)
[23.12.04 09:44:25:714 CET] 18bd3fe7 SystemOut O Hibernate: select last_insert_id()
[23.12.04 09:44:25:729 CET] 18bd3fe7 SystemOut O Hibernate: insert into OrderLineItem (Order_ID, Description, LineItemPrice, del, index) values (?, ?, ?, ?, ?)
[23.12.04 09:44:25:745 CET] 18bd3fe7 JDBCException W net.sf.hibernate.util.JDBCExceptionReporter SQL Error: 1064, SQLState: 42000
[23.12.04 09:44:25:745 CET] 18bd3fe7 JDBCException E net.sf.hibernate.util.JDBCExceptionReporter Syntax error or access violation message from server: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'index) values (66, 'cewc', 100.0, 0, 0)' at line 1"
[23.12.04 09:44:25:761 CET] 18bd3fe7 SQLErrorCodeS I org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator Translating SQLException with SQLState '42000' and errorCode '1064' and message [Syntax error or access violation message from server: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'index) values (66, 'cewc', 100.0, 0, 0)' at line 1"]; SQL was [] for task [Hibernate operation]
[23.12.04 09:44:25:776 CET] 18bd3fe7 SystemErr R org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [] in task 'Hibernate operation'; nested exception is java.sql.SQLException: Syntax error or access violation message from server: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'index) values (66, 'cewc', 100.0, 0, 0)' at line 1"
[23.12.04 09:44:25:776 CET] 18bd3fe7 SystemErr R java.sql.SQLException: Syntax error or access violation message from server: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'index) values (66, 'cewc', 100.0, 0, 0)' at line 1"
Name and version of the database you are using:MySQL

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

anthony · **Posted:** Thu Dec 23, 2004 4:59 am

inverse collection cannot be indexed, use bag

anthony · **Posted:** Thu Dec 23, 2004 5:00 am

if your associaiton isn't bidirectionnal, remove inverse="true"

chainsaw · **Posted:** Thu Dec 23, 2004 5:04 am

if i set inverse to false, the same error occurs.
i don't want to use bag, because bag updates all items and is not good for performance if i have very much data.

anthony · **Posted:** Thu Dec 23, 2004 5:11 am

Quote:

14.1.2. Lists, maps and sets are the most efficient collections to update
From the discussion above, it should be clear that indexed collections and (usually) sets allow the most efficient operation in terms of adding, removing and updating elements.

There is, arguably, one more advantage that indexed collections have over sets for many to many associations or collections of values. Because of the structure of a Set, Hibernate doesn't ever UPDATE a row when an element is "changed". Changes to a Set always work via INSERT and DELETE (of individual rows). Once again, this consideration does not apply to one to many associations.

After observing that arrays cannot be lazy, we would conclude that lists, maps and sets are the most performant collection types. (With the caveat that a set might be less efficient for some collections of values.)

Sets are expected to be the most common kind of collection in Hibernate applications.

There is an undocumented feature in this release of Hibernate. The <idbag> mapping implements bag semantics for a collection of values or a many to many association and is more efficient that any other style of collection in this case!

14.1.3. Bags and lists are the most efficient inverse collections
Just before you ditch bags forever, there is a particular case in which bags (and also lists) are much more performant than sets. For a collection with inverse="true" (the standard bidirectional one-to-many relationship idiom, for example) we can add elements to a bag or list without needing to initialize (fetch) the bag elements! This is because Collection.add() or Collection.addAll() must always return true for a bag or List (unlike a Set). This can make the following common code much faster.

Parent p = (Parent) sess.load(Parent.class, id);
Child c = new Child();
c.setParent(p);
p.getChildren().add(c); //no need to fetch the collection!
sess.flush();

is your collection inverse or not? that is really important

and show java code if you ask for help

and post mapping file again using code tag

chainsaw · **Posted:** Thu Dec 23, 2004 7:41 am

i found the error, it was in the db itself.
but i have another question:
if i get an Object and change one item only and do the save, the whole order is updated.
How can i make it in that way, that only the changed or new Items are saved/updated and the other ones, which nothing has been changed, are ignored by save/update?