"NULL not allowed for column 'id'" even though log says it had value bound

Question

So, I've been bashing my head on this for hours!

I've been testing this with a lot of different settings..

The bottom line is this, when i start my EAR in JBoss, I am able to interact with the Persistence-Jar and ALL it's @Entities without a problem. The difference is that running it in JBoss a different persistence.xml is used (maven main/test catalogue structure) that connects to the PostgreSQL server through a JTA-connection.

It is only when i run my tests in maven and JUnit that they fail.

I've been trying to use a H2-database in PostgreSQL mode (but I've run it as a database connected server without success as well)

It seems to fall handling my Systemuser-entity id-value

This is the top of my Systemuser class:

@Entity
@Table(name = "systemuser")
public class SystemUser extends BaseOldStyleEntity implements Serializable {
private static final long serialVersionUID = -1L;
@Id
@SequenceGenerator(name = "SYSTEMUSER_SYSTEMUSERIDNR_GENERATOR", sequenceName = "systemuser_pk_seq", allocationSize = 1)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SYSTEMUSER_SYSTEMUSERIDNR_GENERATOR")
@Column(name = "systemuseridnr", unique = true, nullable = false)
@BusinessKey
private Integer id;
...

This is the BaseOldStyleEntity which almost all tables inherit from

@MappedSuperclass
@AdditionalCriteria("this.statusNr < 31000")
@Customizer(value = EntityCustomizer.class)
public abstract class BaseOldStyleEntity extends BaseEntity implements Serializable {

@Column(name = "editby", length = 10, nullable = false)
protected String editBy;

@Column(name = "lastedittime", columnDefinition = "timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP::timestamp(6)")
@Temporal(TemporalType.TIMESTAMP)
protected Date lastEditTime = new Date();

@Column(name = "statusnr", nullable = false, columnDefinition = "integer default '0'")
protected Integer statusNr = 0;

public static final String EDIT_BY = "editBy";
public static final String LAST_EDIT_TIME = "lastEditTime";
public static final String STATUS_NR = "statusNr";

public String getEditBy() {
    return editBy;
}

public void setEditBy(String editBy) {
    this.editBy = editBy;
    this.lastEditTime = new Date();
}

public Date getLastEditTime() {
    return lastEditTime;
}

public void setLastEditTime(Date lastEditTime) {
    this.lastEditTime = lastEditTime;
}

public Integer getStatusNr() {
    return statusNr;
}

public void setStatusNr(Integer statusNr) {
    this.statusNr = statusNr;
}
}

and the BaseOldStyleEntity in turn inherits from BaseEntity

@MappedSuperclass
public abstract class BaseEntity implements Serializable {
    @Override
    public boolean equals(Object obj) {
        return BusinessIdentity.areEqual(this, obj);

    }
    @Override
    public int hashCode() {
        return BusinessIdentity.getHashCode(this);
    }

    @Override
    public String toString() {
        //return BusinessIdentity.toString(this);
        return "toString() not set on entity " + getClass().getName();
    }
}

And this is what my persistence.xml looks like:

<?xml version="1.0" encoding="UTF-8"?>
    <persistence version="2.0"
        xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
        <persistence-unit name="foo-test" transaction-type="RESOURCE_LOCAL">
        <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>

<!-- ALL MY CLASSES -->

        <properties>
            <property name="javax.persistence.jdbc.driver" value="org.h2.Driver" />
            <property name="javax.persistence.jdbc.url" value="jdbc:h2:target/test-db/foo-test;MODE=PostgreSQL" />
            <property name="eclipselink.target-database" value="org.eclipse.persistence.platform.database.H2Platform" />
            <!-- EclipseLink should create the database schema automatically -->
            <property name="eclipselink.ddl-generation" value="drop-and-create-tables" />
            <property name="eclipselink.ddl-generation.output-mode" value="database" />
            <!-- Configure logging -->
            <property name="eclipselink.logging.level" value="ALL" />
        </properties>
    </persistence-unit>
</persistence>

And this is the log output i receive: (First 6 rows are inserts of entities for tables "systemuserrole" and "systemusergroup")

[EL Finest]: query: 2014-03-17 18:28:42.761--UnitOfWork(134154804)--Thread(Thread[main,5,main])--Execute query InsertObjectQuery(toString() not set on entity se.point.unity.domain.SystemUserRole)
[EL Fine]: sql: 2014-03-17 18:28:42.761--ClientSession(350731153)--Connection(2020456228)--Thread(Thread[main,5,main])--INSERT INTO systemuserrole (systemuserroleidnr, editby, lastedittime, systemuserrolename, statusnr) VALUES (?, ?, ?, ?, ?)
    bind => [-1, test, 2014-03-17 18:28:42.511, test, 0]
[EL Finest]: query: 2014-03-17 18:28:42.762--UnitOfWork(134154804)--Thread(Thread[main,5,main])--Execute query InsertObjectQuery(test (-1))
[EL Fine]: sql: 2014-03-17 18:28:42.762--ClientSession(350731153)--Connection(2020456228)--Thread(Thread[main,5,main])--INSERT INTO systemusergroup (systemusergroupidnr, editby, lastedittime, systemusergroupname, statusnr, systemuserroleidnr) VALUES (?, ?, ?, ?, ?, ?)
    bind => [-1, test, 2014-03-17 18:28:42.512, test, 0, -1]
[EL Finest]: query: 2014-03-17 18:28:42.763--UnitOfWork(134154804)--Thread(Thread[main,5,main])--Execute query InsertObjectQuery(name (-1))
[EL Fine]: sql: 2014-03-17 18:28:42.763--ClientSession(350731153)--Connection(2020456228)--Thread(Thread[main,5,main])--INSERT INTO systemuser (systemuseridnr, editby, failedloginattempts, firstname, lastedittime, lastfailedlogindate, lastlogindate, lastname, systemusername, password, phone, statusnr, systemusergroupidnr) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    bind => [-1, editby, 0, firstname, 2014-03-17 18:28:42.512, 2014-03-17 18:28:42.512, 2014-03-17 18:28:42.512, lastname, name, password, phone, 0, -1]
[EL Fine]: sql: 2014-03-17 18:28:42.763--ClientSession(350731153)--Thread(Thread[main,5,main])--SELECT 1
[EL Warning]: 2014-03-17 18:28:42.764--UnitOfWork(134154804)--Thread(Thread[main,5,main])--Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.4.1.v20121003-ad44345): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.h2.jdbc.JdbcSQLException: NULL not allowed for column "ID"; SQL statement:
INSERT INTO systemuser (systemuseridnr, editby, failedloginattempts, firstname, lastedittime, lastfailedlogindate, lastlogindate, lastname, systemusername, password, phone, statusnr, systemusergroupidnr) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [23502-168]
Error Code: 23502
Call: INSERT INTO systemuser (systemuseridnr, editby, failedloginattempts, firstname, lastedittime, lastfailedlogindate, lastlogindate, lastname, systemusername, password, phone, statusnr, systemusergroupidnr) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    bind => [-1, editby, 0, firstname, 2014-03-17 18:28:42.512, 2014-03-17 18:28:42.512, 2014-03-17 18:28:42.512, lastname, name, password, phone, 0, -1]
Query: InsertObjectQuery(name (-1))
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:324)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:851)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:913)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:594)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:537)
    at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1800)
    at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:286)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:207)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:193)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.insertObject(DatasourceCallQueryMechanism.java:342)
    at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:162)
    at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:177)
    at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:471)
    at org.eclipse.persistence.queries.InsertObjectQuery.executeCommit(InsertObjectQuery.java:80)
    at org.eclipse.persistence.queries.InsertObjectQuery.executeCommitWithChangeSet(InsertObjectQuery.java:90)
    at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:286)
    at org.eclipse.persistence.queries.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:58)
    at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:852)
    at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:751)
    at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:108)
    at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:85)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2875)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1602)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1584)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1535)
    at org.eclipse.persistence.internal.sessions.CommitManager.commitNewObjectsForClassWithChangeSet(CommitManager.java:224)
    at org.eclipse.persistence.internal.sessions.CommitManager.commitAllObjectsForClassWithChangeSet(CommitManager.java:191)
    at org.eclipse.persistence.internal.sessions.CommitManager.commitAllObjectsWithChangeSet(CommitManager.java:136)
    at org.eclipse.persistence.internal.sessions.AbstractSession.writeAllObjectsWithChangeSet(AbstractSession.java:3914)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1419)
    at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitToDatabase(RepeatableWriteUnitOfWork.java:634)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithPreBuiltChangeSet(UnitOfWorkImpl.java:1565)
    at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.writeChanges(RepeatableWriteUnitOfWork.java:445)
    at org.eclipse.persistence.internal.jpa.EntityManagerImpl.flush(EntityManagerImpl.java:798)
    at se.point.unity.service.search.SearchStructureTest.setUp(SearchStructureTest.java:30)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:24)
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: org.h2.jdbc.JdbcSQLException: NULL not allowed for column "ID"; SQL statement:
INSERT INTO systemuser (systemuseridnr, editby, failedloginattempts, firstname, lastedittime, lastfailedlogindate, lastlogindate, lastname, systemusername, password, phone, statusnr, systemusergroupidnr) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [23502-168]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
    at org.h2.message.DbException.get(DbException.java:169)
    at org.h2.message.DbException.get(DbException.java:146)
    at org.h2.table.Column.validateConvertUpdateSequence(Column.java:293)
    at org.h2.table.Table.validateConvertUpdateSequence(Table.java:689)
    at org.h2.command.dml.Insert.insertRows(Insert.java:120)
    at org.h2.command.dml.Insert.update(Insert.java:84)
    at org.h2.command.CommandContainer.update(CommandContainer.java:75)
    at org.h2.command.Command.executeUpdate(Command.java:230)
    at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:156)
    at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:142)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:842)
    ... 59 more

[EL Finer]: transaction: 2014-03-17 18:28:42.765--UnitOfWork(134154804)--Thread(Thread[main,5,main])--release unit of work
[EL Finer]: transaction: 2014-03-17 18:28:42.765--ClientSession(350731153)--Connection(2020456228)--Thread(Thread[main,5,main])--rollback transaction
[EL Finest]: connection: 2014-03-17 18:28:42.77--ServerSession(1278414937)--Connection(2020456228)--Thread(Thread[main,5,main])--Connection released to connection pool [default].
[EL Finer]: connection: 2014-03-17 18:28:42.771--ClientSession(350731153)--Thread(Thread[main,5,main])--client released
[EL Finest]: jpa: 2014-03-17 18:28:42.775--ServerSession(1278414937)--Thread(Thread[main,5,main])--Begin undeploying Persistence Unit foo-test; session file:/C:/Users/jedmin/Desktop/unity/branches/DEVELOPMENT/Persistence-Unit/target/test-classes/_foo-test; state Deployed; factoryCount 1
[EL Finest]: sequencing: 2014-03-17 18:28:42.775--ServerSession(1278414937)--Thread(Thread[main,5,main])--sequencing disconnected
[EL Config]: connection: 2014-03-17 18:28:42.775--ServerSession(1278414937)--Connection(2020456228)--Thread(Thread[main,5,main])--disconnect
[EL Finer]: cache: 2014-03-17 18:28:42.93--ServerSession(1278414937)--Thread(Thread[main,5,main])--initialize identitymaps
[EL Info]: connection: 2014-03-17 18:28:42.93--ServerSession(1278414937)--Thread(Thread[main,5,main])--file:/C:/Users/jedmin/Desktop/unity/branches/DEVELOPMENT/Persistence-Unit/target/test-classes/_foo-test logout successful
[EL Config]: connection: 2014-03-17 18:28:42.93--ServerSession(1278414937)--Connection(1864311781)--Thread(Thread[main,5,main])--disconnect
[EL Finest]: jpa: 2014-03-17 18:28:42.931--ServerSession(1278414937)--Thread(Thread[main,5,main])--End undeploying Persistence Unit foo-test; session file:/C:/Users/jedmin/Desktop/unity/branches/DEVELOPMENT/Persistence-Unit/target/test-classes/_foo-test; state Undeployed; factoryCount 0

And from what i can glean from the log - none of the values are null.. Also,

What is going on here?

UPDATE1:

So I tested a few of the suggestions and discussed a bit with my coworkers, and then we noticed that the generated table has a column we did not expect; 'id'! That is part of the compound pk with 'systemuseridnr'!

The strange thing here is that I tried renaming the variable 'id' to match the name-value of the @Column annotation, and the problem is still there after i droped-created the database, and let EclipseLink rebuild the tables.

I've also tried looking for other entities that might be referring @OneToMany with a static string in the mappedBy value like shown below, But I found none!

@OneToMany(mappedBy = "id")
private List<SystemUser> users;

Update2:

This is the definition of the table that is autogenerated by Eclipselink (and Hibernate if i use that)

Notice the column named 'id'! And how it is part of the primary key!

CREATE TABLE systemuser
(
      systemuseridnr integer NOT NULL,
      editby character varying(10) NOT NULL,
      failedloginattempts smallint,
      firstname character varying(40),
      lastedittime timestamp(0) without time zone NOT NULL DEFAULT (now())::timestamp(6) without time zone,
      lastfailedlogindate timestamp without time zone,
      lastlogindate timestamp without time zone,
      lastname character varying(40),
      systemusername character varying(10) NOT NULL,
      password character varying(100) NOT NULL,
      phone character varying(20),
      statusnr integer DEFAULT 0,
      systemusergroupidnr integer NOT NULL,
      id integer NOT NULL,
      vismavalue integer,
      terminalvendor_id integer,
      CONSTRAINT systemuser_pkey PRIMARY KEY (systemuseridnr , id ),
      CONSTRAINT fk_systemuser_terminalvendor_id FOREIGN KEY (terminalvendor_id)
          REFERENCES terminalvendor (terminalvendoridnr) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE NO ACTION,
      CONSTRAINT systemuser_systemusername_key UNIQUE (systemusername )
);

Answer 1

it occured to me another possible solution for creating a correct CREATE TABLE statement.

http://mrbool.com/how-to-create-database-table-using-hibernate/28269

The great thing is that:

<property name="hbmdl.auto">update</property>

With this solution, your code will generate a statement itself. Try it!

Answer 2

The problem turned out to be an @Entity with a @UniqueConstraint that someone had missed completing before committing the code.

As it turns out this gave us a plethora of different behaviors and errors when generating tables for H2 & postgres through both Hibernate and EclipseLink during test phase!

We finally caught the problem by remote-debugging the the maven surefire plugin through Eclipse.

@Entity
@Table(name = "dyn_bin_constraint_group", schema = "public", 
        uniqueConstraints = { @UniqueConstraint(columnNames = {}) })
public class BinConstraintGroup implements Serializable {

The solution was simply completing the @UniqueConstraint!

@Entity
@Table(name = "dyn_bin_constraint_group", schema = "public", 
        uniqueConstraints = { @UniqueConstraint(columnNames = {BinConstraintGroup.NAME}) })
public class BinConstraintGroup implements Serializable {