Cannot insert object to HSQLDB with ActiveJDBC

Question

I'm trying to use ActiveJDBC with HSQLDB:

Users.sql

CREATE TABLE users (
    "ID" INTEGER GENERATED BY DEFAULT AS SEQUENCE seq NOT NULL PRIMARY KEY,
    FIRST_NAME VARCHAR(100) NOT NULL,
    LAST_NAME VARCHAR(100) NOT NULL
);

User.java

@IdName("ID")
public class User extends Model {....}

Main.java

User u = new User();
u.setFirstName("first_name");
u.setLastName("last_name");
u.save();

And when I try to save new row I have the following exception:

org.javalite.activejdbc.DBException: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: id, Query: INSERT INTO users (first_name, last_name) VALUES (?, ?), params: first_name,last_name

I think that problem in lower case in insert query. How I can fix this problem?

Answer 1

After googling for this HSQL exception message, I threw this code together that actually works. It uses ActiveJDBC, works with plain query and also works with instrumented model:

Base.open("org.hsqldb.jdbc.JDBCDriver", "jdbc:hsqldb:file:./target/tmp/hsql-test", "sa", "");

String create = "CREATE TABLE people (\n" +
        "    ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,\n" +
        "    FIRST_NAME VARCHAR(100) NOT NULL,\n" +
        "    LAST_NAME VARCHAR(100) NOT NULL\n" +
        ");";

Base.exec(create);
Base.exec("INSERT INTO people (first_name, last_name) VALUES (?, ?)", "John", "Doe");

System.out.println("===>" + Base.findAll("select * from people"));

Person p = new Person();
p.set("first_name", "Jane", "last_name", "Doe").saveIt();

System.out.println(Base.findAll("select * from people"));
System.out.println(Person.findAll());
Base.close();

As you can see, the code that creates a table is a bit different, especially around the ID column.

The model looks like this:

@IdName("ID")
public class Person extends Model {}

Basically, you had the following issues with your code:

1. Definition of ID column with double quotes
2. ID column needs to be defined as IDENTITY (do not hold my feet to the fire, I'm no HSQL expert, but t works)
3. The model needs to overwrite the @IdName("ID"), since ActiveJDBC defaults to lower case id, unless you change that in the table (why not?)

Keep in mind, that some things may not work, since HSQL is not on a list of supported databases.

I hope this helps!