aspose file tools*
The moose likes Object Relational Mapping and the fly likes Hibernate insert fails due to generate key failure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Hibernate insert fails due to generate key failure" Watch "Hibernate insert fails due to generate key failure" New topic
Author

Hibernate insert fails due to generate key failure

Nina Anderson
Ranch Hand

Joined: Jul 18, 2006
Posts: 148
Hi,

My web application is able to read data from the database via Hibernate. However, it fails when it needs to do an INSERT with a generate key failure.

Has anyone come across this error? I've been bang my head for the past 1 week to figure it out and I'm getting frustrated.

I'm using the following:
Hibernate3
String
MS SQL Server 2005
Tomcat
Windows Vista

/////////////////////////////

Here's the error:

Hibernate: insert into RX.dbo.Busines (Place_Of_Business_Id, Business_Type_Id, Business_Name, Business_Email_Address, Business_Description) values (?, ?, ?, ?, ?)

Exception Ocurred: org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not insert: [com.RXCoreApp.datamodel.BusinessCbo]; uncategorized SQLException for SQL [insert into RX.dbo.Busines (Place_Of_Business_Id, Business_Type_Id, Business_Name, Business_Email_Address, Business_Description) values (?, ?, ?, ?, ?)]; SQL state [null]; error code [0]; The statement must be run before the generated keys are available.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The statement must be run before the generated keys are available.

Exception Ocurred (cause): com.microsoft.sqlserver.jdbc.SQLServerException: The statement must be run before the generated keys are available.

Exception Ocurred (msg): Hibernate operation: could not insert: [com.RXCoreApp.datamodel.BusinessCbo]; uncategorized SQLException for SQL [insert into RX.dbo.Busines (Place_Of_Business_Id, Business_Type_Id, Business_Name, Business_Email_Address, Business_Description) values (?, ?, ?, ?, ?)]; SQL state [null]; error code [0]; The statement must be run before the generated keys are available.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The statement must be run before the generated keys are available.


///////////

Here's my applicationContext config:

<!-- Local DataSource that works in any environment -->

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">

<property name="driverClassName"><value>com.microsoft.sqlserver.jdbc.SQLServerDriver</value></property>

<property name="url"><value>jdbc:sqlserver://localhost:1433;databaseName=RX</value></property>

<property name="username"><value>sa</value></property>

<property name="password"><value>#something#</value></property>

</bean>


PLS HELP...
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

Can you post your class mapping for the id field?

Thanks

Mark


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Nina Anderson
Ranch Hand

Joined: Jul 18, 2006
Posts: 148
My code was working perfectly (doing inserts) until I changed the server from websphere to Tomcat and Windows Vista OS.

here's my class mapping:

<bean id="sessionFactory"
class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource">
<ref bean="dataSource" />
</property>
<property name="mappingResources">
<list>

<value>config/BusinessCbo.hbm.xml</value>
...

</list>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">com.PBCoreApp.hibernate.PBSQLServerDialect</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.max_fetch_depth">1</prop>
<prop key="hibernate.default_schema">PB</prop>

</props>

<bean id="hibernateTemplate" class="org.springframework.orm.hibernate3.HibernateTemplate">
<property name="sessionFactory">
<ref bean="sessionFactory"/>
</property>
</bean>

<bean id="hibernateInterceptor" class="org.springframework.orm.hibernate3.HibernateInterceptor">
<property name="sessionFactory">
<ref bean="sessionFactory"/>
</property>
</bean>

<bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
<property name="sessionFactory"><ref local="sessionFactory"/></property>
</bean>

<bean id="businessBSTarget" class="com.PBCoreApp.business.impl.BusinessBSImpl">
<property name="businessDao"><ref local="businessDao" /></property></bean>
<bean id="businessBS" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
<property name="transactionManager"><ref bean="transactionManager"/></property>
<property name="target">
<ref local="businessBSTarget" />
</property>
<property name="transactionAttributes">
<props>
<prop key="accept*">PROPAGATION_REQUIRED,-Exception</prop>
<prop key="activate*">PROPAGATION_REQUIRED,-Exception</prop>
<prop key="add*">PROPAGATION_REQUIRED,-Exception</prop>
<prop key="decline*">PROPAGATION_REQUIRED,-Exception</prop>
<prop key="delete*">PROPAGATION_REQUIRED,-Exception</prop>
<prop key="generate*">PROPAGATION_REQUIRED,-Exception</prop>
<prop key="get*">PROPAGATION_REQUIRED,-Exception</prop>
<prop key="is*">PROPAGATION_REQUIRED,-Exception</prop>
<prop key="update*">PROPAGATION_REQUIRED,-Exception</prop>
<prop key="store*">PROPAGATION_REQUIRED,-Exception</prop>
<prop key="find*">PROPAGATION_REQUIRED,-Exception</prop>
</props>
</property>
</bean>

</property>
</bean>
<bean id="businessDao"
class="com.PBCoreApp.dataaccess.impl.BusinessDAOImpl">
<property name="hibernateTemplate">
<ref bean="hibernateTemplate"/>
</property>
</bean>
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

That doesn't look like the Hibernate mapping for your object/class.

I wanted to see the hbm.xml file.



Thanks

Mark
Nina Anderson
Ranch Hand

Joined: Jul 18, 2006
Posts: 148
here is my hibernate class mapping:

<?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">
<!--
Mapping file autogenerated by MyEclipse - Hibernate Tools
-->
<hibernate-mapping>
<class name="com.RXCoreApp.datamodel.BusinessCbo" table="Busines" schema="dbo" catalog="RX">
<id name="businessId" type="java.lang.Integer">
<column name="Business_Id" />
<generator class="identity"></generator>
</id>
<many-to-one name="placeOfBusiness" class="com.PBCoreApp.datamodel.PlaceOfBusinessCbo" fetch="select">
<column name="Place_Of_Business_Id" />
</many-to-one>
<property name="businessTypeId" type="java.lang.Integer">
<column name="Business_Type_Id" />
</property>
<property name="businessName" type="java.lang.String">
<column name="Business_Name" />
</property>
<property name="businessEmailAddress" type="java.lang.String">
<column name="Business_Email_Address" not-null="true" />
</property>
<property name="businessDescription" type="java.lang.String">
<column name="Business_Description" />
</property>
<property name="lastUpdateTimestamp" type="java.util.Date" insert="false" update="false">
<column name="Last_Update_Timestamp"/>
</property>
<set name="businessPackages" inverse="true" cascade="all-delete-orphan">
<key>
<column name="Business_Id" />
</key>
<one-to-many class="com.PBCoreApp.datamodel.BusinessPackageCbo" />
</set>

...

</class>
</hibernate-mapping>


////////////////////////////
Save Method:

public void save(BusinessCbo businessCbo) {
log.debug("saving BusinessCbo instance");
try {

this.getHibernateTemplate().save(businessCbo);

log.debug("save successful");
} catch (RuntimeException re) {
log.error("save failed", re);
throw re;
}
}


public void saveOrUpdate(BusinessCbo businessCbo) {
log.debug("saving BusinessCbo instance");
try {

this.getHibernateTemplate().saveOrUpdate(businessCbo);

log.debug("save successful");
} catch (RuntimeException re) {
log.error("save failed", re);
throw re;
}
}


////Java method where it's invoked:

public void storeBusinessProfile(Integer businessId, BusinessDetailsDTO businessDetailDto)
{
AddressSecurityCbo addressSecurityCbo = new AddressSecurityCbo();
PhoneSecurityCbo phoneSecurityCbo = new PhoneSecurityCbo();
PlaceOfBusinessCbo placeOfBusinessCbo = new PlaceOfBusinessCbo();
PackageCbo packageCbo = new PackageCbo();
BusinessCbo businessCbo = new BusinessCbo();
BusinessPackageCbo businessPackageCbo = new BusinessPackageCbo();
AddressTypeCbo addrTypeCbo = new AddressTypeCbo();
AddressCbo addrCbo = new AddressCbo();
PhoneCbo businessPhoneCbo = new PhoneCbo();
PhoneCbo mobilePhoneCbo = new PhoneCbo();
PhoneCbo faxPhoneCbo = new PhoneCbo();

Set phoneSet = new HashSet();

//find addr type
List addrTypeList = addressTypeDao.findByAddressTypeName("Business");
Integer addrTypeId = null;
if(addrTypeList != null)
{
Iterator itr = addrTypeList.iterator();

while(itr.hasNext())
{
addrTypeCbo = (AddressTypeCbo) itr.next();
addrTypeId = addrTypeCbo.getAddressTypeId();
}
}

//find country
Integer countryId = null;
if( businessDetailDto.getCountryId() != null )
{
countryId = new Integer( businessDetailDto.getCountryId() );
}
CountryCbo countryCbo = countryDao.findById( countryId );

//save addr with type
addrCbo.setAddressLine1(businessDetailDto.getAddrLine1());
addrCbo.setAddressLine2(businessDetailDto.getAddrLine2());
addrCbo.setAddressTypeId(addrTypeCbo.getAddressTypeId());
addrCbo.setCityName(businessDetailDto.getCity());
addrCbo.setState( businessDetailDto.getState() );
addrCbo.setZipCode(businessDetailDto.getZipCode() );
addrCbo.setCountryId( countryCbo.getCountryId() );
AddressCbo savedAddressCbo = addressDao.save(addrCbo);

//save address security
storeOrUpdateAnonymousAddress( savedAddressCbo.getAddressId(), businessDetailDto.getMakeAddressAnonymous());

//save business package
packageCbo.setPackageId(businessDetailDto.getPackageId());
businessCbo.setBusinessId(businessId);
businessPackageCbo.setBusiness(businessCbo);
businessPackageCbo.setPackage(packageCbo);
businessPackageDao.save(businessPackageCbo);


//save phone
if( businessDetailDto.getBusinessPhone() != null )
{
PhoneTypeCbo phoneTypeCbo = new PhoneTypeCbo();
BusinessPhoneCbo bizPhoneCbo = new BusinessPhoneCbo();
PhoneCbo phoneCbo = new PhoneCbo();
Integer phoneTypeId = null;
List phoneTypeList = phoneTypeDao.findByPhoneTypeName("Business");
if(phoneTypeList != null)
{
Iterator itr = phoneTypeList.iterator();

while(itr.hasNext())
{
phoneTypeCbo = (PhoneTypeCbo) itr.next();
phoneTypeId = phoneTypeCbo.getPhoneTypeId();
}
}

//save phone
phoneCbo.setPhoneNumber(businessDetailDto.getBusinessPhone());
phoneCbo.setPhoneTypeId(phoneTypeId);
businessPhoneCbo = phoneDao.save(phoneCbo);

//save phone security
storeOrUpdateAnonymousPhone( businessPhoneCbo.getPhoneId(), businessDetailDto.getMakePhoneAnonymous() );

//save businessPhone
BusinessCbo bizCbo = new BusinessCbo();
bizCbo.setBusinessId(businessId);
bizPhoneCbo.setPhone(businessPhoneCbo);
bizPhoneCbo.setBusines(bizCbo);
phoneSet.add(bizPhoneCbo);

bizCbo.setBusinessPhones(phoneSet);
businessPhoneDao.save(bizPhoneCbo);

}

if( businessDetailDto.getFaxNumber() != null )
{
PhoneTypeCbo phoneTypeCbo = new PhoneTypeCbo();
BusinessPhoneCbo bizPhoneCbo = new BusinessPhoneCbo();
PhoneCbo phoneCbo = new PhoneCbo();
Integer phoneTypeId = null;
List phoneTypeList = phoneTypeDao.findByPhoneTypeName("Fax");
if(phoneTypeList != null)
{
Iterator itr = phoneTypeList.iterator();

while(itr.hasNext())
{
phoneTypeCbo = (PhoneTypeCbo) itr.next();
phoneTypeId = phoneTypeCbo.getPhoneTypeId();
}
}

phoneCbo.setPhoneNumber(businessDetailDto.getFaxNumber());
phoneCbo.setPhoneTypeId(phoneTypeId);
faxPhoneCbo = phoneDao.save(phoneCbo);

//save phone security
storeOrUpdateAnonymousPhone( faxPhoneCbo.getPhoneId(), businessDetailDto.getMakePhoneAnonymous() );

//save businessPhone
BusinessCbo bizCbo = new BusinessCbo();
bizCbo.setBusinessId(businessId);
bizPhoneCbo.setPhone(faxPhoneCbo);
bizPhoneCbo.setBusines(bizCbo);
phoneSet.add(bizPhoneCbo);

bizCbo.setBusinessPhones(phoneSet);
businessPhoneDao.save(bizPhoneCbo);


}

if( businessDetailDto.getMobilePhone() != null )
{
PhoneTypeCbo phoneTypeCbo = new PhoneTypeCbo();
BusinessPhoneCbo bizPhoneCbo = new BusinessPhoneCbo();
PhoneCbo phoneCbo = new PhoneCbo();
Integer phoneTypeId = null;
List phoneTypeList = phoneTypeDao.findByPhoneTypeName("Mobile");
if(phoneTypeList != null)
{
Iterator itr = phoneTypeList.iterator();

while(itr.hasNext())
{
phoneTypeCbo = (PhoneTypeCbo) itr.next();
phoneTypeId = phoneTypeCbo.getPhoneTypeId();
}
}

phoneCbo.setPhoneNumber(businessDetailDto.getMobilePhone());
phoneCbo.setPhoneTypeId(phoneTypeId);
mobilePhoneCbo = phoneDao.save(phoneCbo);

//save phone security
storeOrUpdateAnonymousPhone( mobilePhoneCbo.getPhoneId(), businessDetailDto.getMakePhoneAnonymous() );

//save businessPhone
BusinessCbo bizCbo = new BusinessCbo();
bizCbo.setBusinessId(businessId);
bizPhoneCbo.setPhone(mobilePhoneCbo);
bizPhoneCbo.setBusines(bizCbo);
phoneSet.add(bizPhoneCbo);

bizCbo.setBusinessPhones(phoneSet);
businessPhoneDao.save(bizPhoneCbo);


}

try {

//save biz details
Set bizAddresses = new HashSet();
BusinessAddressCbo businessAddressCbo = new BusinessAddressCbo();
addrTypeCbo = new AddressTypeCbo();

businessCbo.setBusinessId(businessId);
addrTypeCbo.setAddressTypeId(addrTypeId);

businessAddressCbo.setAddressType(addrTypeCbo);
businessAddressCbo.setAddress(savedAddressCbo);
businessAddressCbo.setBusines(businessCbo);
bizAddresses.add(businessAddressCbo);

businessCbo.setBusinessAddresses(bizAddresses);
savedAddressCbo.setBusinessAddresses(bizAddresses);
addrTypeCbo.setBusinessAddresses(bizAddresses);
businessAddressDao.save(businessAddressCbo);

businessCbo.setBusinessName( businessDetailDto.getBusinessName() );
businessCbo.setBusinessEmailAddress( businessDetailDto.getBusinessEmailAddress() );
//businessCbo.setBusinessAddresses(bizAddresses);
businessCbo.setBusinessId(businessId);
businessCbo.setBusinessTypeId( businessDetailDto.getBusinessTypeId() );
//businessCbo.setBusinessPhones(phoneSet);

placeOfBusinessCbo = placeOfBusinessDao.findById(new Integer(businessDetailDto.getPlaceOfBusiness()) );
//add the reference of businessCbo to the collection in placeOfBusinessCbo
Set businesses = placeOfBusinessCbo.getBusineses();
if (businesses == null)
{
businesses = new HashSet();

}

businesses.add(businessCbo);
placeOfBusinessCbo.setBusineses(businesses);
businessCbo.setPlaceOfBusiness(placeOfBusinessCbo);
businessDao.saveOrUpdate(businessCbo);

} catch (Exception e) {

System.out.println("Exception occured: " + e);

}

}
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

Hmm, can you re-post that using the CODE tags below, it will keep your indentation to make the code more readable.

Normally, I would edit the post for you, but what happens with xml and "<" ">" is that they change to ">"

Thanks

Mark
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

Ah,

<set name="businessPackages" inverse="true" cascade="all-delete-orphan">

There isn't a cascade option called "all-delete-orphan"

There is

"all, delete-orphan"



Mark
Nina Anderson
Ranch Hand

Joined: Jul 18, 2006
Posts: 148
I got the "all-delete-orphan" option from Hiberate.org: http://www.hibernate.org/hib_docs/reference/en/html/example-parentchild.html

This code always used to work. The only thing I changed was:
1. From Windows Xp to Windows Vista
2. From IBM Websphere server to Tomcat server

So, I don't understand why the application can retrieve data and all of a sudden cannot Save data in the database.
Bougnon Kipre
Greenhorn

Joined: Sep 13, 2007
Posts: 21
Originally posted by Nina Anderson:
I got the "all-delete-orphan" option from Hiberate.org: http://www.hibernate.org/hib_docs/reference/en/html/example-parentchild.html

This code always used to work. The only thing I changed was:
1. From Windows Xp to Windows Vista
2. From IBM Websphere server to Tomcat server

So, I don't understand why the application can retrieve data and all of a sudden cannot Save data in the database.


Although the syntax you are using is in correctly quited in the link you provided, if I were you I would just try Mark suggestion for that is also the syntax I am familiar with.
I am not saying your are wrong in using, I just want to eliminate this possibility.

Another suggestion to gradually change you environment.
Instead of changing both OS and Web server, I would do as follows:
1) first change OS: from XP to Vista.
2) if 1) works, then change Web server
Bougnon Kipre
Greenhorn

Joined: Sep 13, 2007
Posts: 21
Originally posted by Bougnon Kipre:


Although the syntax you are using is in correctly quited in the link you provided, if I were you I would just try Mark suggestion for that is also the syntax I am familiar with.
I am not saying your are wrong in using the current syntax, I just want to eliminate this possibility.

Another suggestion to gradually change you environment.
Instead of changing both OS and Web server, I would do as follows:
1) First change OS: from XP to Vista.
2) if 1) works, then change Web server
Nina Anderson
Ranch Hand

Joined: Jul 18, 2006
Posts: 148
I tried changing from cascade="all-delete-orphan" to "all, delete-orphan", but I'm still getting the same errors.

I bought a new laptop that's why I had the new installations, so going to a previous version is not an option for me.
Nina Anderson
Ranch Hand

Joined: Jul 18, 2006
Posts: 148
Eureeka! Found the problem.

I had a MS SQL Trigger that invokes table updates whenever the java application performs an insert on my Business_Service table. My updates in the SQL Trigger were failing because the ID field of the table I was trying to do that update did not exist. As a result of this, I was getting the generated Key error.

To resolve this, I'm checking if the ID field of the result exists in the database before I do an update.

I'm at peace now... :roll:
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

Originally posted by Nina Anderson:
Eureeka! Found the problem.

I had a MS SQL Trigger that invokes table updates whenever the java application performs an insert on my Business_Service table. My updates in the SQL Trigger were failing because the ID field of the table I was trying to do that update did not exist. As a result of this, I was getting the generated Key error.

To resolve this, I'm checking if the ID field of the result exists in the database before I do an update.

I'm at peace now... :roll:




I am really glad you found this piece to the puzzle. I doubt I would have ever guessed and figured that one out.

At least for now on, whenever anyone has a problem doing any crud, one of the first questions I will ask is "Do you have a trigger anywhere?"

Thanks for posting your solution when you found it.

Hope to see you around.

Mark
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Hibernate insert fails due to generate key failure
 
Similar Threads
SQLServerException: The statement must be run before the generated keys are available
Hibernate: Couldn't get connection pooling to work
Spring + Hibernate
javax.sql.rowset.serial.SerialBlob cannot be cast to oracle
my servlet can't initialize: null