Hibernate Community • View topic - Incorrect update SQL statement for null value

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Incorrect update SQL statement for null value

Page 1 of 1

[ 2 posts ]

Previous topic | Next topic

Author

Message

JeffreyAi

Post subject: Incorrect update SQL statement for null value

Posted: Wed Aug 16, 2006 9:29 pm

Newbie

Joined: Wed Aug 16, 2006 8:56 pm
Posts: 2

Hi there,

I've encoutered an interesting error when doing cascading update in hibernate3. I tried seaching it on this forum, but couldn't find anything related.

The problem seems to be related the sql generated by cascading update. I have traced into hibernate source code and found the update sql generated is as below.

Code:

update TPRODUCTPRICETIER set LIST_PRICE='299.99', SALE_PRICE='259.99', MIN_QUANTITY=1 where PRODUCT_PRICE_UID=786526 and LIST_PRICE='399.00' and SALE_PRICE=null and MIN_QUANTITY=1

It is using "=null", not "is null". I believe the correct one should be :

Code:

update TPRODUCTPRICETIER set LIST_PRICE='299.99', SALE_PRICE='259.99', MIN_QUANTITY=1 where PRODUCT_PRICE_UID=786526 and LIST_PRICE='399.00' and SALE_PRICE is null and MIN_QUANTITY=1

Does anyone has the similar problem?
Any hints to solve this problem are appreciated.

Hibernate version: 3.1; 3.1.3; 3.2.CR2

Mapping documents:

Code:

<class name="Product" ....>
    ....
    <map lazy="false" cascade="all,delete-orphan" name="productPrices">
        <key column="PRODUCT_UID" not-null="true"/>
        <map-key type="java.util.Currency" column="CURRENCY" />
        <one-to-many class="ProductPrice"/>
    </map>
</class>

<class name="ProductPrice" ....>
    ....
     <map lazy="false" cascade="all,delete-orphan" name="priceTiers" table="TPRODUCTPRICETIER" order-by="MIN_QUANTITY asc">
           <key column="PRODUCT_PRICE_UID"/>
           <map-key type="java.lang.Integer" formula="MIN_QUANTITY" />
           <composite-element class="PriceTierImpl">
             <property name="listPrice" type="java.math.BigDecimal" column="LIST_PRICE" access="field" />
             <property name="salePrice" type="java.math.BigDecimal" column="SALE_PRICE" access="field" not-null="false" />
             <property name="minQty" type="int" column="MIN_QUANTITY" access="field" />
          </composite-element>
        </map>
</class>

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

Code:

Price price = product.getPrice(currency);
PriceTier priceTier = price.getPriceTier(minQuantity);
priceTier.setListPrice(listPrice);
priceTier.setSalePrice(salePrice);
session.update(product);

Full stack trace of any exception that occurs:

Code:

SEVERE: Could not synchronize database state with session
org.hibernate.StaleStateException: Unexpected row count: 0 expected: 1
   at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:27)
   at org.hibernate.persister.collection.BasicCollectionPersister.doUpdateRows(BasicCollectionPersister.java:209)
   at org.hibernate.persister.collection.AbstractCollectionPersister.updateRows(AbstractCollectionPersister.java:1394)
   at org.hibernate.action.CollectionUpdateAction.execute(CollectionUpdateAction.java:55)
   at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:232)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:142)
   at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:297)
   at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
   at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:993)
   at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:340)
   at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)

Name and version of the database you are using:

Code:

mysql 5.0.20

The generated SQL (show_sql=true):

Code:

update TPRODUCTPRICETIER set LIST_PRICE='299.99', SALE_PRICE='259.99', MIN_QUANTITY=1 where PRODUCT_PRICE_UID=786526 and LIST_PRICE='399.00' and SALE_PRICE=null and MIN_QUANTITY=1

Code:

Top

JeffreyAi

Post subject: I've figured out the reason.

Posted: Fri Sep 01, 2006 7:34 pm

Newbie

Joined: Wed Aug 16, 2006 8:56 pm
Posts: 2

When using map as a collection mapping, the key and map-key must be mapped as a column to make hibernate generate the right sql statement.
Bad Example :

Code:

<map lazy="false" cascade="all,delete-orphan" name="priceTiers" table="TPRODUCTPRICETIER" order-by="MIN_QUANTITY asc">
               <key column="PRODUCT_PRICE_UID"/>
             <map-key type="java.lang.Integer" formula="MIN_QUANTITY" />
          <composite-element class="com.elasticpath.domain.catalog.impl.PriceTierImpl">
           <property name="listPrice" type="java.math.BigDecimal" column="LIST_PRICE"/>
                  <property name="salePrice" type="java.math.BigDecimal" column="SALE_PRICE"/>
                  <property name="minQty" type="int" column="MIN_QUANTITY"/>
             </composite-element>
      </map>

The update sql statement hibernate created for the bad example:

Code:

update TPRODUCTPRICETIER set LIST_PRICE=?, SALE_PRICE=?, MIN_QUANTITY=? where PRODUCT_PRICE_UID=? and LIST_PRICE=? and SALE_PRICE=? and MIN_QUANTITY=?

The above sql statement will fail if any column, like SALE_PRICE, has a null value in the db because "SALE_PRICE=null" doesn't work well.
Good Example(Notice the "map-key" line has been changed to "column" rather than "formula".):

Code:

<map lazy="false" cascade="all,delete-orphan" name="priceTiers" table="TPRODUCTPRICETIER" order-by="MIN_QUANTITY asc">
               <key column="PRODUCT_PRICE_UID"/>
             <map-key type="java.lang.Integer" column="MIN_QUANTITY" />
           <composite-element class="com.elasticpath.domain.catalog.impl.PriceTierImpl">
           <property name="listPrice" type="java.math.BigDecimal" column="LIST_PRICE"/>
                  <property name="salePrice" type="java.math.BigDecimal" column="SALE_PRICE"/>
                  <property name="minQty" type="int" formula="MIN_QUANTITY"/>
                    </composite-element>
      </map>

The update sql statement hibernate created for the above good example:

Code:

update TPRODUCTPRICETIER set LIST_PRICE=?, SALE_PRICE=?, MIN_QUANTITY=? where PRODUCT_PRICE_UID=? and MIN_QUANTITY=?

Top

Page 1 of 1

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