Statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint

Question

Error while running the test. I get a SQLIntegrityConstraintViolationException.

Internal Exception: java.sql.SQLIntegrityConstraintViolationException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL160412141534310' defined on 'CHARACTERS'.

Code for Character class

@Entity
@Table(name = "CHARACTERS")
@NamedQueries({
@NamedQuery(name = Character.FIND_ALL, query = "SELECT c FROM Character c")
})
public class Character implements Serializable {

public static final String FIND_ALL = "Character.findAll";

private static final long serialVersionUID = 1L;

@Id 
private int id;

@Column(length = 50)
private String name;

public Character() {
}

public Character(int id, String name) {
    this.id = id;
    this.name = name;
}

public Character(String name) {
    this.name = name;
}

/* Getter and setter below */

CharactersBean class

@Stateful
@TransactionAttribute(TransactionAttributeType.NEVER)
public class CharactersBean implements Serializable {

@PersistenceContext(type = PersistenceContextType.EXTENDED)
EntityManager em;

public void save(Character e) {
    em.persist(e);
}

@TransactionAttribute(TransactionAttributeType.REQUIRED)
public void commitChanges() {

}

public List<Character> get() {
    return em.createNamedQuery(Character.FIND_ALL, Character.class).getResultList();
}

Testing class When it executes the should_update_characters_after_transaction_flush, above exception is thrown.

@RunWith(Arquillian.class)
public class ExtendedPersistenceContextTest {

@PersistenceContext
EntityManager em;

@EJB
CharactersBean bean;

@Deployment
public static WebArchive deploy() {
    return ShrinkWrap.create(WebArchive.class)
            .addPackage("org.javaee7.jpa.extended.pc")
            .addAsResource("META-INF/persistence.xml")
            .addAsResource("META-INF/create.sql")
            .addAsResource("META-INF/drop.sql")
            .addAsResource("META-INF/load.sql");
}

@Before
public void setup() {
    Character wil = new Character(8, "Wil Wheaton");
    bean.save(wil);

    for (Character c : bean.get()) {
        if ("Raj".equals(c.getName())) {
            c.setName("Rajesh Ramayan");
            bean.save(c);
        }
    }
}

@Test
@InSequence(1)
public void should_not_persist_changes_without_transaction_flush() {
    List<Character> characters = em.createNamedQuery(Character.FIND_ALL, Character.class).getResultList();
    Character raj = em.find(Character.class, 6);

    assertThat(characters, hasSize(7));
    assertThat(raj.getName(), is(equalTo("Raj")));
}

@Test
@InSequence(2)
public void should_update_characters_after_transaction_flush() {
    //when
    bean.commitChanges();

    //then
    List<Character> characters = em.createNamedQuery(Character.FIND_ALL, Character.class).getResultList();
    Character rajesh = em.find(Character.class, 6);
    Character wil = em.find(Character.class, 8);

    assertThat(characters, hasSize(8));
    assertThat(rajesh.getName(), is(equalTo("Rajesh Ramayan")));
    assertThat(wil.getName(), is(equalTo("Wil Wheaton")));
}

Data in enter in Derby database.

INSERT INTO CHARACTERS("ID", "NAME") VALUES (1, 'Penny')
INSERT INTO CHARACTERS("ID", "NAME") VALUES (2, 'Sheldon')
INSERT INTO CHARACTERS("ID", "NAME") VALUES (3, 'Amy')
INSERT INTO CHARACTERS("ID", "NAME") VALUES (4, 'Leonard')
INSERT INTO CHARACTERS("ID", "NAME") VALUES (5, 'Bernadette')
INSERT INTO CHARACTERS("ID", "NAME") VALUES (6, 'Raj')
INSERT INTO CHARACTERS("ID", "NAME") VALUES (7, 'Howard')

Answer 1

This is related to how JUnit test framework operates: There are a total of 5 annotation marking methods that shall be executed at a certain event:

• @BeforeClass: This method will be executed once before any test is beeing performed.
• @Before: This method will be executed before the actualy Test method gets invoked, and that for each test method a new
• @Test: This is an actual test method
• @After: This method will be executed after each test method finished execution, to clean up for the next Test
• @AfterClass: This method will be executed after all test methods have finished, to close open Database connections or something...

In your test class you try to persist Mr. Wil Wheaton before every test anew. This is working at the first time, but at the second time, the entry is already persisted. trying to persisting it with the exact same ID then violates your unique Constraint.

The simple solution here is to replace @Before with @BeforeClass annotation, so the entry will only be persisted once, or, add an method with @After annotation that is removing the entry from your database, so you can insert it anew.

Additionally, I would suggest to clean your database of any entries inserted by Tests, after they finish. You can easily do that by using a method annotated @After or @AfterClass

Best Regards

J.Adam