This question is more of a general one, but I am still looking for a solution in Hibernate. Here is the requirement:
One of the fields in the table is a
serial number, or a 1-based index for a given foreign key owner. When adding a new row to the table, that value needs to be assigned with the next number in the sequence (but no sequence table is being used and this column is not the primary key).
Consider the following data:
Code:
id parent_id item_name serial_number
1 50 item-50-x 1
2 50 item-50-n 2
3 50 item-50-i 3
4 33 item-33-k 1
5 33 item-33-g 2
Now I want to insert a new row for parent(33) that has the following values:
Code:
(generated) 33 item-33-e ???
The logic to grab the serial number (last column) is
Code:
select max(serial_number) + 1 where parent_id = ?
Obviously, if another session is also trying to enter a row into this table for parent(33) with a unique name,
there will be a contention for the serial number spot.
Initially, I thought that this would make a good case for an pre-insert event listener to assign this value. However, I am confused as to whether this really guarantees that the select and the ensuing insert will happen together in the database. I worry that another session will select the same max value and then the two inserts will attempt to use the same serial number.
If the pre-insert event listener below is the wrong approach, what is the right one?Code:
int propIndex = -1;
String[] props = preInsertEvent.getPersister().getPropertyNames();
for (int i = 0; i < props.length; i++) {
if ("serialNumber".equals(props[i])) {
propIndex = i;
break;
}
}
if (propIndex < 0) {
return true;
}
PreparedStatement ps = null;
try {
Item item = (Item) preInsertEvent.getEntity();
ps = preInsertEvent.getSource().getBatcher().prepareStatement("select max(serial_number) + 1 from item where parent_id = ?");
ps.setInt(1, item.getParent().getId());
ResultSet rs = ps.executeQuery();
// NOTE: it is necessary to update both the entity and the state
// so that Hibernate does not see the object as dirty
if (rs.next()) {
preInsertEvent.getState()[propIndex] = rs.getInt(1);
item.setSerialNumber(rs.getInt(1));
} else {
preInsertEvent.getState()[propIndex] = 1;
item.setSerialNumber(1);
}
} catch (SQLException se) {
se.printStackTrace();
return true;
} finally {
if (ps != null) {
try {
ps.close();
} catch (SQLException ex) {}
}
}
Login
FAQ
Search