When you are using the Eclipselink JPA implementation with a replicated MSSQL database (or a database with more complex triggers on some tables) you’re running probably into major problems when persisting objects, especially when you would like to use the cascaded persisting mechanism. Sometimes the database returns a wrong generated primary key which results in a foreign key constraint violation.

Reason is, that Eclipselink internally uses the query

SELECT @@IDENTITY

to fetch the generated primary key. The database uses this field to memorize the last generated ID but, and that’s the point, it doesn’t matter in which table the insert statement is been executed.

Let’s see an example:
You have a table T1 on which you defined a trigger. This trigger inserts a record into table T2.
Now, you insert your record into T1 and expect to find the generated ID of the inserted record in @@IDENTITY. But you won’t. Because of the trigger, which inserted a new record into T2, you’ll find the generated ID of T2 in @@IDENTITY.

Therefore you should use one of those methods to get the newly generated ID when you’re extensively working with triggers or the like:

SELECT SCOPE_IDENTITY()
-- OR
SELECT IDENT_CURRENT('tablename')

SCOPE_IDENTITY returns the last generated id in the same scope, whereby a scope is a stored procedure, trigger, function, or batch. IDENT_CURRENT returns the last generated id for the specified table.

Since you know in which table your record has been inserted the most secure way to fetch the id is to use IDENT_CURRENT, imho.

See some further links:
http://msdn.microsoft.com/en-us/library/ms187342.aspx
http://msdn.microsoft.com/en-us/library/ms190315.aspx
http://msdn.microsoft.com/en-us/library/ms175098.aspx

But how to fix this issue in Eclipselink?

You can implement a custom sequence strategy which uses the new query and attach this strategy to your entities. In my implementation I use the old mechanism (with @@IDENTITY) as fall-back-solution if there is no custom sequence strategy specified to an entity.

Here’s some code. First of all you need to implement the custom sequence.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

package de.ibs.persistence.sequencing;
 
import org.eclipse.persistence.queries.ValueReadQuery;
import org.eclipse.persistence.sequencing.NativeSequence;
 
public class IdentCurrentSequence extends NativeSequence {
 
 public IdentCurrentSequence() {
 super();
 }
 
 // ... all other constructors
 
 @Override
 public ValueReadQuery getSelectQuery() {
 String query = "SELECT IDENT_CURRENT('#tablename#')";
 query = query.replaceFirst("(#tablename#)", this.getName().substring(9));
 return new ValueReadQuery(query);
 }
}

To declare the new sequences (one for each table), I use a separate SessionCustomizer class.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

package de.ibs.persistence.sequencing;
 
import org.eclipse.persistence.config.SessionCustomizer;
import org.eclipse.persistence.sessions.Session;
 
public class IdentCurrentSessionCustomizer implements SessionCustomizer {
 
 public void customize(Session session) throws Exception {
 String[] identCurrentSequences = {
 "CUST_SEQ_tdtaLagerTransfer",
 "CUST_SEQ_trelServiceArtikel"
 };
 
 for (int i = 0; i<identCurrentSequences.length; i++) {
 IdentCurrentSequence sequence = new IdentCurrentSequence(identCurrentSequences[i]);
 session.getLogin().addSequence(sequence);
 }
 }
}

This IdentCurrentSessionCustomizer has to be published to the persistence unit so that it can be used.

properties.put(PersistenceUnitProperties.SESSION_CUSTOMIZER, "de.ibs.persistence.sequencing.IdentCurrentSessionCustomizer");

or in XML:

<property name="eclipselink.session.customizer" value="de.ibs.persistence.sequencing.IdentCurrentSessionCustomizer"/>

To use the sequences, add following to your entities:

1
2
3
4
5
6
7
8
9
10

@Entity
@Table(name = "tdtaLagerTransfer")
@SequenceGenerator(name="CUST_SEQ_tdtaLagerTransfer", sequenceName="CUST_SEQ_tdtaLagerTransfer")
public class LagerTransfer extends ExtendedModel implements Serializable {
 @Id
 @GeneratedValue(generator="CUST_SEQ_tdtaLagerTransfer")
 private Integer idLagerTransfer;
 
 // ... other fields and methods
}

See also:
http://wiki.eclipse.org/EclipseLink/Examples/JPA/CustomSequencing

Certainly you can implement this more clean that I did it when you understand the problem we’re facing here.

I would be delighted to hear some feedback from you.