How do I commit a transaction when using JdbcRepository?

Question

I have two @JdbcRepository annotated repositories, which represent two Postgresql schemas. Each schema is in a separate datasource, which means each Repository has its own transaction manager. In the SQL table definitions, one table has a foreign key constraint on the other. The two repos look like:

@Repository("schema1")
@JdbcRepository
public abstract class RepoTypeA implements PageableRepository<TypeA, Long> {
  // ...
}

@Repository("schema2")
@JdbcRepository
public abstract class RepoTypeB implements PageableRepository<TypeB, Long> {
  // ...
}

The Postgres SQL definitions look like:

create table if not exists schema1.typea_table (
    typea_id            bigserial primary key,
    typeb_id            bigint not null ,

    CONSTRAINT typeb_id_fkey FOREIGN KEY (typeb_id)
        REFERENCES schema2.typeb_table (typeb_id),
);

create table if not exists schema2.typeb_table (
    typeb_id            bigserial primary key,
);

And finally the datatypes in Java look like:

@Introspected
@MappedEntity("typea_table")
public class TypeA {
  @Id @GeneratedValue Long typea_id;
  @NonNull Long typeb_id;
}

@Introspected
@MappedEntity("typeb_table")
public class TypeB {
  @Id @GeneratedValue Long typeb_id;
}

The problem is that trying to use RepoTypeA and RepoTypeB together breaks when creating the two objects. Creating a TypeB and then using its typeb_id in TypeA fails the foreign key constraint, since the TypeB record hasn't been committed yet. There doesn't appear to be a way to make both changes in the same transaction. Both repos have their own transaction manager, with names like schema1 and schema2, and it would be infeasible to change them, as other parts of the program depend on them.

Another approach which doesn't work is using the SynchronousTransactionManager<Connection>. The transaction manager expects to call a callback with the connection, but since the RepoTypeA and RepoTypeB classes have their JDBI objects injected for them, there is no way to pass in the transaction. Example:

// This does NOT work
@Inject @Named("schema2") 
SynchronousTransactionManager<Connection> txManager;

{ 
  txManager.executeWrite(
            status -> {
              return repoTypeB.save(new TypeB());
            });
} 

I'm looking for a way to either force both object repositories to use the same transaction, or, force creation of TypeB by committing the transaction, blocking, and then starting a transaction on TypeA.

Answer 1

You can use one datasource and two schemas. Looks like the mapped entity annotation doesn’t support schemas but you can use JPA’s Entity annotation as a workaround.