EclipseLink does not see created tables in H2 DB

Question

For test run of the application I am using H2 database 1.4.197. The app is deployed on TomEE 7.1.0 which uses EclipseLink 2.6.4.

I see in logs that during the startup EclipseLink creates tables, sequences etc. Even initialize sequence with value 0:

[EL Fine]: 2019-02-13 20:25:37.442--ServerSession(1981996085)--Connection(1317890500)--Thread(Thread[http-nio-8080-exec-4,5,main])--CREATE TABLE USER (ID BIGINT NOT NULL, FIRSTNAME VARCHAR, LASTNAME VARCHAR, PRIMARY KEY (ID)) [EL Fine]: 2019-02-13 20:25:37.467--ServerSession(1981996085)--Connection(1320052060)--Thread(Thread[http-nio-8080-exec-4,5,main])--CREATE TABLE ADDRESS (ID BIGINT NOT NULL, CITY VARCHAR, HOUSENO VARCHAR, STREET VARCHAR, PRIMARY KEY (ID)) [EL Fine]: 2019-02-13 20:25:37.474--ServerSession(1981996085)--Connection(1328271122)--Thread(Thread[http-nio-8080-exec-4,5,main])--CREATE TABLE SEQUENCE (SEQ_NAME VARCHAR(50) NOT NULL, SEQ_COUNT NUMERIC(38), PRIMARY KEY (SEQ_NAME)) [EL Fine]: 2019-02-13 20:25:37.489--ServerSession(1981996085)--Connection(432174967)--Thread(Thread[http-nio-8080-exec-4,5,main])--SELECT * FROM SEQUENCE WHERE SEQ_NAME = 'SEQ_GEN' [EL Fine]: 2019-02-13 20:25:37.51--ServerSession(1981996085)--Connection(1127939384)--Thread(Thread[http-nio-8080-exec-4,5,main])--INSERT INTO SEQUENCE(SEQ_NAME, SEQ_COUNT) values ('SEQ_GEN', 0)

I also was able to connect via H2 console (I started WebServer manually inside the application) and see there tables / sequences.

When I do a simple select via JPQL then all works fine:

Collection<User> getUsers() {
    Query query = entityManager.createQuery("select u from User u");
    return query.getResultList();
}

However, when I try to insert a new row with persist method:

void saveUser(User user) {
    entityManager.persist(user);
}

then I get the exception that tables do not exist:

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.4.v20160829-44060b6): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: org.h2.jdbc.JdbcSQLException: Tabela "SEQUENCE" nie istnieje Table "SEQUENCE" not found; SQL statement: UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ? [42102-197] Error Code: 42102 Call: UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ? bind => [50, SEQ_GEN] Query: DataModifyQuery(name="SEQUENCE" sql="UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?")

EDIT:

User entity implementation:

@Entity
public class User {
    private Long id;
    private String firstName;
    private String lastName;
    @Id
    @GeneratedValue
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    @Column
    public String getFirstName() {
        return firstName;
    }
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }
    @Column
    public String getLastName() {
        return lastName;
    }
    public void setLastName(String lastName) {
        this.lastName = lastName;
    }
}

Show your User entity. It complains Tabela "SEQUENCE" but not about missing table User? And try to change table name SEQUENCE as a reserved word it might cause problems. — pirho
Try specifying the table name to something other then "SEQUENCE" by using a TableGenerator. i.e. @GeneratedValue(generator="TabGen") @TableGenerator(name="TabGen", table="ID_TABLE") — Chris
Also show how you have setup your datasource connection pools. EclipseLink will be using a different connection for the Sequence table updates, avoiding visibility and rollback issues when they use the active connect/transaction. This pool might have a different view of the database. — Chris

Emil Hotkowski Emil Hotkowski · Accepted Answer · 2019-02-14T00:11:40

Probably the problem is with your definition of H2 connection. If you use memory version with wrong parameters, H2 will automaticlly discard your tables.

Assuming you would have configuration in that manner :

jdbc:h2:mem:test

Change it to

jdbc:h2:mem:test;DB_CLOSE_DELAY=-1

Keep H2 in-memory database between connections

For the future avoid using names like USER for table names. It is reserved word for most providers. It won't cause any trouble for H2, because it is acceptable name for this provider, but it won't be compitable with other databases.

Keywords / Reserved Words

EclipseLink does not see created tables in H2 DB

1 Answers