Hi

I was wondering under what circumstances Hibernate will decide to do an update when I execute a named query (which is just a select).

It's really strange, because if I point to our development database, everything is fine (no update) but when I point to our UAT database it does try an update.

Which makes me think its a decision based at least partly on the data, and not just the mapping.

I haven't included any mapping files etc because this is more a general question, but if my particular set up would help to clarify I can post more details.

Thanks

I noticed a simalar problem which I was actualy going to make a post about tonight with regards to a JUnit test. I'm trying to setup a project on my own which uses Hibernate, Spring and JUnit which although I use at work just fine I've never had to configure and set it up from scratch myself.

Here is my Junit test method:

As I said in my comment I should just be able to call getId() after the save to get the newly saved Object's ID, but it was always comming back as 0 (I had entries in that table already and my PKs are none-zero-indexed), so I tried to impliment a workaround by implimenting a findIdByDesc method:

But that seemed to lead to the same problem MonkeyMagoo is having...:

Any ideas anyone?

Hi

I didn't post the details of my error, but it turns out that Adam's problem seems to be identical to mine.

In case it helps, I've fixed my problem this way:

The update itself was causing an error: trying to insert null into the column in the table. The column of course has a not null constraint on it. The object's field used to be a primitive double, so I had created a typedef for that field that says if Hibernate reads a null value from the table, convert it to 0 before setting it on the object (because you cant set a primitive to null)

The field is now a BigDecimal, so I removed the typedef from the field mapping, and now it doesn't try to set 'null' in the table.

However, this doesn't really explain why it's doing an update in the first place. I suspect it's because some of the rows it brings back are 'PpiProduct' objects which is a subclass (mapped using joined-subclass) of 'Product'. I suspect that it does the update because it's trying to synchronise the two rows that make up the 'PpiProduct' in some way.

If anyone knows more I would be very keen to hear.

I think the issue I'm having is due to my lack of native primary keys which is what I would like to rely on. I made a post about it here:
http://forum.hibernate.org/viewtopic.php?t=973806
With native primary keys I belive if you insert a null the database will automaticaly generate one for you, which is a lot more practical for most real world situations.
I'll let you know how I get on with the CustomRevEngStatergy once I work something out, lol.

Well, I'm stuck now. If someone could take a guess as to why Hibernate is trying to put a NULL value into 'INTEREST_RATE' I would be very grateful.

In the getter that returns 'interestRate' I've made sure it never even returns null, so I have no idea where this null value is coming from.

Thanks

DEBUG actions.ProductRefDataAction - listProducts()
DEBUG hibernate.SQL - select * from ( select productval0_.PLAN_ID as PLAN1_35_0_, generalref1_.GENERAL_REFERENCE_ID as GENERAL1_45_1_, productval0_.DESCRIPTION as DESCRIPT2_35_0_, productval0_.STANDARD_CONSTRUCTION as STANDARD3_35_0_, productval0_.MIN_PROPERTY_VALUE as MIN4_35_0_, productval0_.MIN_AGE as MIN5_35_0_, productval0_.MAX_AGE as MAX6_35_0_, productval0_.MIN_TERM_MONTHS as MIN7_35_0_, productval0_.LOAN_TO_VALUE as LOAN8_35_0_, productval0_.MIN_LOAN as MIN9_35_0_, productval0_.MAX_LOAN as MAX10_35_0_, productval0_.INCOME_MULTIPLE as INCOME11_35_0_, productval0_.SUBORDINATE_LEVEL as SUBORDI12_35_0_, productval0_.LTV_BASED_ON_GROSS_LOAN as LTV13_35_0_, productval0_.INTEREST_RATE as INTEREST14_35_0_, productval0_.INCOME_TEST_1 as INCOME15_35_0_, productval0_.INCOME_TEST_2 as INCOME16_35_0_, productval0_.DAY_OF_MONTH as DAY17_35_0_, productval0_.POOL_ID as POOL18_35_0_, productval0_.PRODUCT_GROUP_ID as PRODUCT19_35_0_, productval0_.PRODUCT_CODE as PRODUCT20_35_0_, productval0_.COOLING_OFF_PERIOD as COOLING21_35_0_, productval0_.AUTOMATIC_CANCELLATION_ARREARS as AUTOMATIC22_35_0_, productval0_.PAYMENT_OFFSET as PAYMENT23_35_0_, productval0_.PRODUCT_STATUS_ID as PRODUCT24_35_0_, productval0_.REGULATED_BY_ID as REGULATED25_35_0_, productval0_.PRODUCT_SUBTYPE_ID as PRODUCT26_35_0_, productval0_.PAYMENT_FREQUENCY_ID as PAYMENT27_35_0_, productval0_.REDEMPTION_PENALTY_ID as REDEMPTION28_35_0_, productval0_.LAST_MODIFIED_BY as LAST29_35_0_, productval0_.LAST_MODIFIED_ON as LAST30_35_0_, productval0_1_.MAX_MONTH_COVER as MAX2_38_0_, productval0_1_.MAX_LUMPSUM_COVER as MAX3_38_0_, productval0_1_.LAST_MODIFIED_BY as LAST4_38_0_, productval0_1_.LAST_MODIFIED_ON as LAST5_38_0_, productval0_1_.INSURER_ID as INSURER6_38_0_, case when productval0_1_.PRODUCT_ID is not null then 1 when productval0_.PLAN_ID is not null then 0 end as clazz_0_, generalref1_.DESCRIPTION as DESCRIPT2_45_1_, generalref1_.GENERAL_REFERENCE_GROUP_ID as GENERAL3_45_1_, generalref1_.MAS_GENERAL_REFERENCE_ID as MAS4_45_1_ from mortgage_plans productval0_ left outer join PRODUCT_PPI productval0_1_ on productval0_.PLAN_ID=productval0_1_.PRODUCT_ID inner join GENERAL_REFERENCE generalref1_ on productval0_.PRODUCT_STATUS_ID=generalref1_.GENERAL_REFERENCE_ID order by productval0_.PRODUCT_CODE ) where rownum <= ?
DEBUG hibernate.SQL - select generalref0_.GENERAL_REFERENCE_GROUP_ID as GENERAL1_46_0_, generalref0_.DESCRIPTION as DESCRIPT2_46_0_ from GENERAL_REFERENCE_GROUP generalref0_ where generalref0_.GENERAL_REFERENCE_GROUP_ID=?
DEBUG hibernate.SQL - select associatev0_.ASSOCIATE_ID as ASSOCIATE1_55_1_, associatev0_.ASSOCIATE_TYPE_ID as ASSOCIATE2_55_1_, associatev0_.ASSOCIATE_NAME as ASSOCIATE3_55_1_, associatev0_.FAX_NUMBER as FAX4_55_1_, associatev0_.MAIN_TELEPHONE as MAIN5_55_1_, associatev0_.ASSOCIATE_AGENCY_NUMBER as ASSOCIATE6_55_1_, associatev0_.ASSOCIATE_FSA_NUMBER as ASSOCIATE7_55_1_, associatev0_.EMAIL_ADDRESS as EMAIL8_55_1_, associatev0_.LAST_MODIFIED_BY as LAST9_55_1_, associatev0_.LAST_MODIFIED_ON as LAST10_55_1_, associatev0_.ADDRESS_ID as ADDRESS11_55_1_, associatev0_.QUALIFICATIONS_ID as QUALIFI12_55_1_, associatev0_1_.STANDARD_PROD_PERCENT_FEE as STANDARD2_56_1_, associatev0_1_.STANDARD_PROD_MIN_FEE as STANDARD3_56_1_, associatev0_1_.STANDARD_PROD_FLAT_FEE as STANDARD4_56_1_, associatev0_1_.BROKER_TYPE_ID as BROKER5_56_1_, associatev0_1_.STATUS_ID as STATUS6_56_1_, associatev0_1_.NETWORK as NETWORK56_1_, associatev0_1_.ADVISING as ADVISING56_1_, associatev0_1_.FEE_PAYABLE_TO as FEE9_56_1_, associatev0_1_.APPROVAL_REF as APPROVAL10_56_1_, associatev0_1_.DPL_RETURNED as DPL11_56_1_, associatev0_1_.DPL_EXPIRES as DPL12_56_1_, associatev0_1_.CCL_EXPIRES as CCL13_56_1_, associatev0_1_.CCL_RETURNED as CCL14_56_1_, associatev0_1_.AGREEMENT_RETURNED as AGREEMENT15_56_1_, associatev0_1_.PACK_SENT as PACK16_56_1_, associatev0_1_.PACK_RECIEVED as PACK17_56_1_, associatev0_1_.PRODUCT_SENT_DETAILS as PRODUCT18_56_1_, associatev0_1_.FOLLOW_UP as FOLLOW19_56_1_, associatev0_1_.DIRECTLY_AUTHORISED as DIRECTLY20_56_1_, associatev0_1_.PRINCIPAL_NAME as PRINCIPAL21_56_1_, associatev0_1_.PRINCIPAL_FSA_NUMBER as PRINCIPAL22_56_1_, associatev0_1_.CONTACT_NAME as CONTACT23_56_1_, associatev0_1_.CONTACT_TELEPHONE as CONTACT24_56_1_, associatev0_1_.NO_CONTACT as NO25_56_1_, associatev0_1_.DATE_LAST_VISIT as DATE26_56_1_, associatev0_1_.DATE_LAST_PHONE_CALL as DATE27_56_1_, associatev0_1_.DATE_LAST_EMAIL as DATE28_56_1_, associatev0_1_.BANK_SORT_CODE as BANK29_56_1_, associatev0_1_.BANK_NAME as BANK30_56_1_, associatev0_1_.BANK_ACCOUNT_NO as BANK31_56_1_, associatev0_1_.BANK_ACCOUNT_NAME as BANK32_56_1_, case when associatev0_1_.BROKER_ASSOCIATE_ID is not null then 1 when associatev0_.ASSOCIATE_ID is not null then 0 end as clazz_1_, addressval1_.ADDRESS_ID as ADDRESS1_4_0_, addressval1_.HOUSE_NO as HOUSE2_4_0_, addressval1_.HOUSE_NAME as HOUSE3_4_0_, addressval1_.FLAT_NO as FLAT4_4_0_, addressval1_.STREET as STREET4_0_, addressval1_.DISTRICT as DISTRICT4_0_, addressval1_.TOWN as TOWN4_0_, addressval1_.COUNTY as COUNTY4_0_, addressval1_.POSTCODE_AREA as POSTCODE9_4_0_, addressval1_.POSTCODE_SECTOR as POSTCODE10_4_0_, addressval1_.LAST_MODIFIED_BY as LAST11_4_0_, addressval1_.LAST_MODIFIED_ON as LAST12_4_0_ from ASSOCIATE_DETAIL associatev0_ left outer join BROKER_DETAIL associatev0_1_ on associatev0_.ASSOCIATE_ID=associatev0_1_.BROKER_ASSOCIATE_ID left outer join ADDRESS_DETAIL addressval1_ on associatev0_.ADDRESS_ID=addressval1_.ADDRESS_ID where associatev0_.ASSOCIATE_ID=?
DEBUG interceptors.AuditInterceptor - >>> State changed: YES
DEBUG interceptors.AuditInterceptor - >>> State changed: YES
DEBUG hibernate.SQL - update mortgage_plans set DESCRIPTION=?, STANDARD_CONSTRUCTION=?, MIN_PROPERTY_VALUE=?, MIN_AGE=?, MAX_AGE=?, MIN_TERM_MONTHS=?, LOAN_TO_VALUE=?, MIN_LOAN=?, MAX_LOAN=?, INCOME_MULTIPLE=?, SUBORDINATE_LEVEL=?, LTV_BASED_ON_GROSS_LOAN=?, INTEREST_RATE=?, INCOME_TEST_1=?, INCOME_TEST_2=?, DAY_OF_MONTH=?, POOL_ID=?, PRODUCT_GROUP_ID=?, PRODUCT_CODE=?, COOLING_OFF_PERIOD=?, AUTOMATIC_CANCELLATION_ARREARS=?, PAYMENT_OFFSET=?, PRODUCT_STATUS_ID=?, REGULATED_BY_ID=?, PRODUCT_SUBTYPE_ID=?, PAYMENT_FREQUENCY_ID=?, REDEMPTION_PENALTY_ID=?, LAST_MODIFIED_BY=?, LAST_MODIFIED_ON=? where PLAN_ID=?
DEBUG hibernate.NullableBigDecimalType - binding null to parameter: 13
DEBUG hibernate.SQL - update mortgage_plans set DESCRIPTION=?, STANDARD_CONSTRUCTION=?, MIN_PROPERTY_VALUE=?, MIN_AGE=?, MAX_AGE=?, MIN_TERM_MONTHS=?, LOAN_TO_VALUE=?, MIN_LOAN=?, MAX_LOAN=?, INCOME_MULTIPLE=?, SUBORDINATE_LEVEL=?, LTV_BASED_ON_GROSS_LOAN=?, INTEREST_RATE=?, INCOME_TEST_1=?, INCOME_TEST_2=?, DAY_OF_MONTH=?, POOL_ID=?, PRODUCT_GROUP_ID=?, PRODUCT_CODE=?, COOLING_OFF_PERIOD=?, AUTOMATIC_CANCELLATION_ARREARS=?, PAYMENT_OFFSET=?, PRODUCT_STATUS_ID=?, REGULATED_BY_ID=?, PRODUCT_SUBTYPE_ID=?, PAYMENT_FREQUENCY_ID=?, REDEMPTION_PENALTY_ID=?, LAST_MODIFIED_BY=?, LAST_MODIFIED_ON=? where PLAN_ID=?
DEBUG hibernate.NullableBigDecimalType - binding null to parameter: 13
DEBUG web.MqsExceptionHandler - execute()
ERROR web.MqsExceptionHandler - org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: Could not execute JDBC batch update; uncategorized SQLException for SQL [update mortgage_plans set DESCRIPTION=?, STANDARD_CONSTRUCTION=?, MIN_PROPERTY_VALUE=?, MIN_AGE=?, MAX_AGE=?, MIN_TERM_MONTHS=?, LOAN_TO_VALUE=?, MIN_LOAN=?, MAX_LOAN=?, INCOME_MULTIPLE=?, SUBORDINATE_LEVEL=?, LTV_BASED_ON_GROSS_LOAN=?, INTEREST_RATE=?, INCOME_TEST_1=?, INCOME_TEST_2=?, DAY_OF_MONTH=?, POOL_ID=?, PRODUCT_GROUP_ID=?, PRODUCT_CODE=?, COOLING_OFF_PERIOD=?, AUTOMATIC_CANCELLATION_ARREARS=?, PAYMENT_OFFSET=?, PRODUCT_STATUS_ID=?, REGULATED_BY_ID=?, PRODUCT_SUBTYPE_ID=?, PAYMENT_FREQUENCY_ID=?, REDEMPTION_PENALTY_ID=?, LAST_MODIFIED_BY=?, LAST_MODIFIED_ON=? where PLAN_ID=?]; SQL state [72000]; error code [1407]; ORA-01407: cannot update ("MQS4"."MORTGAGE_PLANS"."INTEREST_RATE") to NULL
; nested exception is java.sql.BatchUpdateException: ORA-01407: cannot update ("MQS4"."MORTGAGE_PLANS"."INTEREST_RATE") to NULL

org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: Could not execute JDBC batch update; uncategorized SQLException for SQL [update mortgage_plans set DESCRIPTION=?, STANDARD_CONSTRUCTION=?, MIN_PROPERTY_VALUE=?, MIN_AGE=?, MAX_AGE=?, MIN_TERM_MONTHS=?, LOAN_TO_VALUE=?, MIN_LOAN=?, MAX_LOAN=?, INCOME_MULTIPLE=?, SUBORDINATE_LEVEL=?, LTV_BASED_ON_GROSS_LOAN=?, INTEREST_RATE=?, INCOME_TEST_1=?, INCOME_TEST_2=?, DAY_OF_MONTH=?, POOL_ID=?, PRODUCT_GROUP_ID=?, PRODUCT_CODE=?, COOLING_OFF_PERIOD=?, AUTOMATIC_CANCELLATION_ARREARS=?, PAYMENT_OFFSET=?, PRODUCT_STATUS_ID=?, REGULATED_BY_ID=?, PRODUCT_SUBTYPE_ID=?, PAYMENT_FREQUENCY_ID=?, REDEMPTION_PENALTY_ID=?, LAST_MODIFIED_BY=?, LAST_MODIFIED_ON=? where PLAN_ID=?]; SQL state [72000]; error code [1407]; ORA-01407: cannot update ("MQS4"."MORTGAGE_PLANS"."INTEREST_RATE") to NULL
; nested exception is java.sql.BatchUpdateException: ORA-01407: cannot update ("MQS4"."MORTGAGE_PLANS"."INTEREST_RATE") to NULL

Caused by:
java.sql.BatchUpdateException: ORA-01407: cannot update ("MQS4"."MORTGAGE_PLANS"."INTEREST_RATE") to NULL

Hey monkey are you using native/sequence PKs in your ID? If so then implimenting a customRevengeStatergy like mine should help (it fixed my problem).

Forgot to say here's an example of the script I use to make my table (FKs are mapped later).

Cheers for the tips Adam.

I've found that the my DB table is subtly different to the table in the db where the code works fine. Some of the scripts are failing to run, so I need to get the DBAs to fix them before I start looking at this again - I need the tables right before I start looking at the code I think.

Thanks again for your help