aspose file tools
The moose likes Object Relational Mapping and the fly likes insert data into 2 tables using HQL Big Moose Saloon
  Search | Java FAQ | Recent Topics
Register / Login


Win a copy of The Mikado Method this week in the Agile and other Processes forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Reply Bookmark "insert data into 2 tables using HQL" Watch "insert data into 2 tables using HQL" New topic
Author

insert data into 2 tables using HQL

Bharath Gowda
Greenhorn

Joined: Apr 15, 2008
Posts: 17
posted private message
0
Quote
Hi,

I'm trying to insert data into 2 tables in Hibernate using session.save(). But the issue is one of the column value inserted(generated) to the first table should be inserted as a foreign key value to the second table.

In this case, the value generated in the first table is inserted to the second table when I update the existing row in the second table.

When I try to create a new row in the second table than I'm getting below error..Please let me know how to solve this issue.

SEVERE: DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502, SQLERRMC=TBSPACEID=3, TABLEID=85, COLNO=31, DRIVER=3.50.152
Nov 20, 2008 4:01:18 PM org.hibernate.event.def.AbstractFlushingEventListener performExecutions
SEVERE: Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: could not insert:
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2272)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2665)
at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:60)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
at
Caused by: com.ibm.db2.jcc.b.lm: DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502, SQLERRMC=TBSPACEID=3, TABLEID=85, COLNO=31, DRIVER=3.50.152
at com.ibm.db2.jcc.b.wc.a(wc.java:575)
at com.ibm.db2.jcc.b.wc.a(wc.java:57)
at com.ibm.db2.jcc.b.wc.a(wc.java:126)
at com.ibm.db2.jcc.b.tk.b(tk.java:1593)
at com.ibm.db2.jcc.b.tk.c(tk.java:1576)
at com.ibm.db2.jcc.t4.db.k(db.java:353)
at com.ibm.db2.jcc.t4.db.a(db.java:59)
at com.ibm.db2.jcc.t4.t.a(t.java:50)
at com.ibm.db2.jcc.t4.tb.b(tb.java:200)
at com.ibm.db2.jcc.b.uk.Gb(uk.java:2355)
at com.ibm.db2.jcc.b.uk.e(uk.java:3129)
at com.ibm.db2.jcc.b.uk.e(uk.java:3238)
at com.ibm.db2.jcc.b.uk.zb(uk.java:568)
at com.ibm.db2.jcc.b.uk.executeUpdate(uk.java:551)
at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:23)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2252)
... 14 more
Exception in thread "main" org.hibernate.exception.ConstraintViolationException: could not insert:
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2272)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2665)
at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:60)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
at
Caused by: com.ibm.db2.jcc.b.lm: DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502, SQLERRMC=TBSPACEID=3, TABLEID=85, COLNO=31, DRIVER=3.50.152
at com.ibm.db2.jcc.b.wc.a(wc.java:575)
at com.ibm.db2.jcc.b.wc.a(wc.java:57)
at com.ibm.db2.jcc.b.wc.a(wc.java:126)
at com.ibm.db2.jcc.b.tk.b(tk.java:1593)
at com.ibm.db2.jcc.b.tk.c(tk.java:1576)
at com.ibm.db2.jcc.t4.db.k(db.java:353)
at com.ibm.db2.jcc.t4.db.a(db.java:59)
at com.ibm.db2.jcc.t4.t.a(t.java:50)
at com.ibm.db2.jcc.t4.tb.b(tb.java:200)
at com.ibm.db2.jcc.b.uk.Gb(uk.java:2355)
at com.ibm.db2.jcc.b.uk.e(uk.java:3129)
at com.ibm.db2.jcc.b.uk.e(uk.java:3238)
at com.ibm.db2.jcc.b.uk.zb(uk.java:568)
at com.ibm.db2.jcc.b.uk.executeUpdate(uk.java:551)
at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:23)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2252)
... 14 more

Bharath PU<br />SCJP - 1.4 <br />IBM Certified Database Associate<br />IBM Certified Advanced Application Developer -- Rational Application Developer for WebSphere Software V6.0
Chandra Bhatt
Ranch Hand

Joined: Feb 28, 2007
Posts: 1707
posted private message
0
Quote
Hi Bharath,

You have not provided the hibernate mapping and other details.

cmbhatt
Bharath Gowda
Greenhorn

Joined: Apr 15, 2008
Posts: 17
posted private message
0
Quote
Hi Please find below mapping file and the code I'm using

<class name="com.mlb.eventportal.arch.businessobjects.TravelInfoBO" table="t_travel_info">
<id name="travelInfoId" column="travel_info_id">
<generator class="increment"/>
</id>

<many-to-one name="person" column="person_id"
class="com.mlb.eventportal.arch.businessobjects.PersonBO" cascade="save-update"/>

<property name="nationality" column="nationality" type="string"/>
<property name="passportNamePrimary" column="prim_passport_name" type="string"/>
<property name="passportNbrPrimary" column="prim_passport_nbr" type="string"/>
<property name="passportCountryPrimary" column="prim_passport_country" type="string"/>
<property name="passportExpPrimary" column="prim_passport_exp" type="date"/>
<property name="passportNameSecondary" column="sec_passport_name" type="string"/>
<property name="passportNbrSecondary" column="sec_passport_nbr" type="string"/>
<property name="passportCountrySecondary" column="sec_passport_country" type="string"/>
<property name="passportExpSecondary" column="sec_passport_exp" type="date"/>
<property name="greenCardSerNbr" column="green_card_ser_nbr" type="string"/>

data service to insert:

while ((travelInfoIt!= null) && (travelInfoIt.hasNext())) {
if (event == null) {
throw new MLBPortalException("No Event is populated");
}
travelInfo = (TravelInfoBO) travelInfoIt.next();
//session.lock(travelinfo, LockMode.NONE);
VisaStatusBO visastatus = travelInfo.getUsVisaStatus();
session.lock(visastatus, LockMode.NONE);

test Method.....
// Setup Travel Info
TravelInfoBO travelInfo = new TravelInfoBO();

Set travelInfos = new HashSet();

travelInfo.setPerson(person);
travelInfo.setTravelInfoId(4);
travelInfo.setNationality("Asian");
travelInfo.setSocialSecurityNbr("123456779");

VisaStatusBO usVisaStatus = new VisaStatusBO();
usVisaStatus.setVisaStatusId("1A");


//usVisaStatus.setDescription("Normal");
//usVisaStatus.setActive(true);
//usVisaStatus.setTsAdd(Calendar.getInstance().getTime());
//usVisaStatus.setTsUpdate(Calendar.getInstance().getTime());

travelInfo.setUsVisaStatus(usVisaStatus);
travelInfo.setRowSource("123");


when I set the TravelInfoId, I do not get any error as it executes the update query...
Chandra Bhatt
Ranch Hand

Joined: Feb 28, 2007
Posts: 1707
posted private message
0
Quote
hi,

Looking into your top post, I get its something foreign key constraint
violation, as you can't insert a new row into the table where there in no
reference into the base table that is having foreign key.
Bharath Gowda
Greenhorn

Joined: Apr 15, 2008
Posts: 17
posted private message
0
Quote
Hey Chandra,

Thanks for yor reply.

But I'm inserting a row into the base table then I'm trying to insert into the foreign key table...am I doing something wrong?
 
I agree. Here's the link: http://ej-technologies/jprofiler - if it wasn't for jprofiler, we would need to run our stuff on 16 servers instead of 3.
 
subject: insert data into 2 tables using HQL
 
Similar Threads
getNextException SQLGrammarException
Exception in update
Problem when delete 1 record in ROOM table
Eclispe + Hibernate + DB2 version 9
On SQL Error 407