javadatabasepostgresqlactivejdbc

activejdbc: compositie pk, multiple fk

I am using a PostgreSQL DB with two tables: a parent table A and child table B, where a one-to-many relationship applies. We manage our data using the activejdbc library.

The parent table A has a composite primary key, consisting of an id (serial) and timestamp (automatically populated on row insertion).

The child table B has these PKs as foreign keys.

We use a Java application with activejdbc to persists records in our database. Currently, the challenge is on how we should define our models to allow for insertion on the data.

I have read through the docs of activejdbc and checked SO but could not find an example of how to configure this with our models.

Is this possible at all, using a composite PK that contains a timestamp which is auto-generated on insert? And if so, how do we need to configure our child model (table B) to successfully persist?

Our parent table A works, so this purely about the parent-child relationship.

Edit 1: add simplified example tables and code

CREATE TABLE some_schema.a
(
    id                         SERIAL,
    insert_date                TIMESTAMP(3) with time zone DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE (insert_date);

CREATE TABLE some_schema.b
(
    id                              SERIAL,    
    insert_date                     TIMESTAMP(3) with time zone DEFAULT CURRENT_TIMESTAMP,
    a_id          BIGINT       NOT NULL,
    a_insert_date TIMESTAMP(3) NOT NULL

) PARTITION BY RANGE (insert_date);

ALTER TABLE some_schema.a
    ADD CONSTRAINT a_pk PRIMARY KEY (id, insert_date);
ALTER TABLE some_schema.b
    ADD CONSTRAINT b_pk PRIMARY KEY (id, insert_date);

ALTER TABLE some_schema.b
    ADD CONSTRAINT b_fk FOREIGN KEY (a_id, a_insert_date) REFERENCES some_schema.a (id, insert_date);

The current (non-working) Java records:

@Table("some_schema.b")
public class ARecord extends Model {
}

@Table("some_schema.b")
@BelongsToParents({
        @BelongsTo(foreignKeyName = "a_id", parent = A.class),
        @BelongsTo(foreignKeyName = "a_insert_date", parent = A.class)
})
public class BRecord extends Model {
}

Edit 2: Java sniplet to sort of make it work using refresh and manually setting one of the foreign keys (not sure if intended this way)

ARecord a = new ARecord();
// populateA
a.saveIt();
a.refresh(); // Required, otherwise retrieving the insert_date will be null during a.getTimestamp("insert_date")

Timestamp insertDate = a.getTimestamp("insert_date");
            
BRecord b = new BRecord();
b.setTimestamp("a_insert_date", insertDate);
a.add(b);
Solution

  • Before I get into solutions, here are a couple of comments:

    1. You are using the annotation BelongsToParents in a One-to-many relationship, while it is really intended for overriding the default polymorphic associations. Your schema clearly has A as a parent and B as a child, with the B not possibly having a parent of a type that is different from A. For more on polymorphic associations, see:https://javalite.io/polymorphic_associations So, in your case, I would define the child model like this:
    @BelongsTo(parent = ARecord.class, foreignKeyName = "a_id")
public class BRecord extends Model {}

    The database can have a composite key, but in your case, the some_schema.a.id is already a natural PK column. You cannot have two records in this table with the same id value, so it is OK to use it like that.

    1. Your tables already have a column id serial, which will have unique values across. Why not make it also a PK: id serial PRIMARY KEY - this way everything will be simplified.
    2. Why have b.a_insert_date if this will simply duplicate data from table a?

    To your actual question: I will be using table names Parent for a anf Child for b.

    Solution 1: Change your schema:

    Tables:

    CREATE TABLE parent(
    id         SERIAL PRIMARY KEY,
    name       varchar(56),
    created_at TIMESTAMP(3) with time zone DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE child(
    id         SERIAL PRIMARY KEY,
    name       varchar(56),
    created_at TIMESTAMP(3) with time zone DEFAULT CURRENT_TIMESTAMP,
    parent_id  BIGINT NOT NULL -- you can add a FK constraint if you want.
);

    The created_at is an auto-generated field (https://javalite.io/autogenerated_fields), it will be set to the current time at the insertion of a new record. ActiveJDBC will detect and update this field accordingly.

    The Java code will look like this:

    
public class Parent extends Model{}
public class Child extends Model{}

...
        
Parent p = Parent.createIt("name", "Parent 1"); // will save p to DB
Child c = Child.create("name", "Child 1"); // will not save c to DB yet
p.add(c); // will save c to DB

    Both parent and child records will have their own created_at values generated by ActiveJDBC.

    The advantages of this approach:

    1. No data duplication
    2. No composite PK keys, which will require more resources to be used on the DB side, as well as a cause for data duplication.
    3. Simple code

    The main disadvantages of this approach:

    1. The timestamps are generated on the Java side, not on the database. Maybe this is what you need, maybe not
    2. Need to make schema changes

    Solution 2: your solution with the refresh() method.

    The issue is not even ActiveJDBC or JDBC, but real-world implementations of JDBC drivers. For instance, in your parent, you have

    insert_date                     TIMESTAMP(3) with time zone DEFAULT CURRENT_TIMESTAMP

    which means, that if the Insert statement does not have a value for insert_date, the database will provide it. However, it will also provide a new value for id SERIAL. The JDBC specification has a provision for returning auto-generated values from the insert operations via a special java.sql.ResultSet: https://github.com/javalite/javalite/blob/24cb215f230fb9a5331fea4449eb1c2b68f247a1/activejdbc/src/main/java/org/javalite/activejdbc/DB.java#L723

    But, in your specific case, the Postgres JDBC driver, when called java.sql.PreparedStatement.getGeneratedKeys(), returns a single value for id and does not return insert_date despite the fact that it was also auto-generated. I saw this in the debugger when preparing this answer.

    This means that no framework will make this happen for you, as the data is not coming back from the insert operation. You will have to "refresh" it somehow.

    I strongly suggest that you follow the first recommendation, as it will simplify your data structures, reduce data duplication, and make the Java code simpler.