JPA with SQLite autoincrement troubles

03 Apr 2014

Today i’ve got an exception when JPA was trying to insert a new row to a table.

My model definition was like this:

@Entity
public class Merchant {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private int id;
    private String name;

    // ...

    public Merchant(){   }

    // ...

And the table creation script was the following:

CREATE TABLE MERCHANT
(
    ID INTEGER PRIMARY KEY NOT NULL,
    NAME TEXT NOT NULL,
    BANKNAME TEXT NOT NULL,
    SWIFT TEXT NOT NULL,
    ACCOUNT TEXT NOT NULL,
    CHARGE REAL NOT NULL,
    PERIOD INTEGER NOT NULL,
    MINSUM REAL NOT NULL,
    TOTAL REAL NOT NULL
);

I faced some strange exceptions like id could not be null, SQL error or missing database (near “values”: syntax error), etc.

The solution to this consists of two steps:

in database: remove the AUTOINCREMENT and NOT NULL annotations from the ID column as SQLite will automatically increment its value (in DB)
in entity’ code: remove the GenerationStrategyType from the id member annotation