JPA, SQlite no such table: SEQUENCE

Question

I have a problem with JPA and SQlite.

I have created an Entity from Table. My generated Entity looks like:

    @Entity
    @Table(name="sqliteTestTable")
    public class Test implements Serializable {
        private static final long serialVersionUID = 1L;

        @Id  
        @GeneratedValue(strategy=GenerationType.IDENTITY)
        @Column(name="id")
        private int id;

        @Column(name="name")
        private String name;



        public Test() {
        }
        ------
    }

When i try to persist a few Test objects i get following error: (I have executed the same code on mysql without problems)

Exception in thread "main" Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.4.1.v20121003-ad44345): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: no such table: SEQUENCE
Error Code: 0
Call: UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
    bind => [50, SEQ_GEN_TABLE]
Query: DataModifyQuery(name="SEQ_GEN_TABLE" sql="UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?")
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)

How can i solve this problem?

Thats in the actual code: @GeneratedValue(strategy=GenerationType.AUTO) — zennon
– zennon, Commented Apr 20, 2013 at 8:44
When i change the GenerationType to IDENTITY this error occurse: Exception Description: SEQ_GEN_IDENTITY: platform DatabasePlatform does not support NativeSequence. — zennon
– zennon, Commented Apr 20, 2013 at 8:52

christopherson · Accepted Answer · 2019-04-29 15:40:25Z

6

IDENTITY will default to TABLE if IDENTITY is not supported in the database platform.

For TABLE you need to have a sequence table created. If you have ElipseLink create your tables it will automatically be created for you, otherwise you need to create it yourself.

If SQlite has IDENTITY support, then you can create your own DatabasePlatform subclass that adds the identity support.

In general I would never recommend using IDENTITY, it does not support pre-allocation and will lead to poor performance.

edited Apr 29, 2019 at 15:40

christopherson

4233 gold badges8 silver badges21 bronze badges

answered Apr 22, 2013 at 13:23

James

18.5k13 gold badges100 silver badges154 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

colpenney · Accepted Answer · 2013-04-30 18:35:24Z

3

sqlite uses the table sqlite_sequence for AUTOINCREMENT field

replace:

@GeneratedValue(strategy=GenerationType.IDENTITY)

with

@GeneratedValue(generator="sqlite")
@TableGenerator(name="sqlite", table="sqlite_sequence",
    pkColumnName="name", valueColumnName="seq",
    pkColumnValue="sqliteTestTable")

If you are using multiple tables then the generators have to be unique names. With a new table, there is no sqlite_sequence record so you have to create yourself.

insert into sqlite_sequence (name,seq) values ("sqliteTestTable",1);

answered Apr 30, 2013 at 18:35

colpenney

563 bronze badges

1 Comment

5agado Over a year ago

Good advice! In my case from this strategy derived a problem with the autoincrement value allocation size; if you are in the same situation just add the initialValue=1, allocationSize=1 elements (see also here)

Collectives™ on Stack Overflow

JPA, SQlite no such table: SEQUENCE

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related