elango wrote:
Hello, can i use "update tablename set sn='newValue' where sn='oldValue' as a query string in hibernate to update my master table's primary key that should also update the child tables.
I have a master table that is linked to 2 child tables. But in my hibernate map, i have only master and child1 linked using many-to-one. The other child (child2) is not linked. But i want to do an update. I can use oracle trigger to update child2. Any help would be arreciated.
Thanks
If the links between your tables are backed up by foreign key constraints, then you can't perform that update from a SQL Plus prompt. You'll get an
ORA-02922: Integrity Constraint Violated error. There are much more knowledgeable people around than me, but I think primary keys should be immutable for the life of the relation. That caveat aside, you can create a new master record, copy the fields from the old to the new, then update the child records to to the new master, then finally delete the original master. Here's a code snippet that works for me even with foreign keys enabled:
Code:
Session session = sessionFactory.openSession();
Transaction t = session.beginTransaction();
Iterator masterIterator = session.iterate("from MasterTable as m where m.masterId = 1");
MasterTable mt1;
if (masterIterator.hasNext()) {
mt1 = (MasterTable)masterIterator.next();
MasterTable mt2 = new MasterTable();
mt2.setMasterId(new BigDecimal(4));
mt2.setMasterDescription(mt1.getMasterDescription());
session.save(mt2);
Set childTables = mt1.getChildTables();
Iterator childIterator = childTables.iterator();
while (childIterator.hasNext()) {
ChildTable ct = (ChildTable)childIterator.next();
ct.setMasterTable(mt2);
session.update(ct);
}
session.delete(mt1);
}
t.commit();
Here's the data before the run:
Code:
SQL> select *
2 from master_table;
MASTER_ID MASTER_DESCRIPTION
---------- ------------------------------
1 Test Record 1
2 Test Record 2
SQL> select *
2 from child_table;
CHILD_ID CHILD_DESCRIPTION MASTER_ID
---------- ------------------------------ ----------
1 Child_record 1 1
2 Child_record 2 2
3 Child_record 3 1
SQL>
And here's the data after the run:
Code:
SQL> select *
2 from master_table;
MASTER_ID MASTER_DESCRIPTION
---------- ------------------------------
2 Test Record 2
4 Test Record 1
SQL> select *
2 from child_table;
CHILD_ID CHILD_DESCRIPTION MASTER_ID
---------- ------------------------------ ----------
1 Child_record 1 4
2 Child_record 2 2
3 Child_record 3 4
SQL>
I'm pretty new here, so there may be better/cleaner ways to do this.
HTH,
Maury
Login
FAQ
Search
