Small legacy DB schema mapping puzzle ;-)

Hibernate ORM
1

Is it possible to map the classes in the given legacy DB schema? Or is this a bridge to far and is it beter to use plain SQL?

Domain logic: A transaction has a list of signatures(A SignaturesSet), wich can be shared over several transactions. So a group of persons can sign multiple transactions in 1 go. Eg, multiple money transfers signed by the 2 account holders in 1 wizard.

So it is not a strict oneToMany or a ManyToMany, it is a list of signatures shared over mulitiple transactions. So a signature cannot belong to mulitiple lists like in a strict manyToMany with a join table.

The classes:

@Entity
public class Transaction {

    @Id
    private Long id;

    @Column
    private Double amount;

    private SignaturesSet signaturesSet;
}

public class SignaturesSet {

    @Column
    private Long signaturesSetIdentifier;
    
    //SQL: Select * from Signature where SignatureListId = transaction.signaturesSetIdentifier
    private Set<Signature> signatures;
    
    //SQL: Select * Transaction where SignatureListId = signature.signaturesSetIdentifier
    private Set<Transaction> transactions;
}

@Entity
public class Signature {

    @Id
    private Long id;

    @Column
    private Long signaturesSetIdentifier;

    @Column
    private String cryptogram;

}

The Legacy Db schema:

image
image267×301 3.27 KB

In plain SQL the logic to retrieve the signatures and transactions:

  • get signaturesSet of transaction with SQL: Select * from Signature where SignatureListId = transaction.signaturesSetIdentifier
  • get transactions of signaturesSet with SQL: Select * Transaction where SignatureListId = signature.signaturesSetIdentifier

How should I annotated the classes to map this?

2

Do you also have a table with rows for these SignatureListId values where that value would be the primary key? Then you could do e.g.


@Entity
public class Signature {
    @Id
    private Long id;
    @ManyToOne(fetch = LAZY)
    private SignatureSet signaturesSet;
    @Column
    private String cryptogram;
}
@Entity
public class Transaction {
    @Id
    private Long id;
    @Column
    private Double amount;
    @ManyToOne(fetch = LAZY)
    private SignaturesSet signaturesSet;
}
@Entity
public class SignaturesSet {
    @Id
    private Long signaturesSetIdentifier;
    @OneToMany(mappedBy = "signaturesSet")
    private Set<Signature> signatures;
    @OneToMany(mappedBy = "signaturesSet")
    private Set<Transaction> transactions;
}

If you don’t have a table, you could theoretically use a “view” e.g.

@Entity
@Subselect("select distinct SignatureListId as signaturesSetIdentifier from Transaction")
public class SignaturesSet {
    @Id
    private Long signaturesSetIdentifier;
    @OneToMany(mappedBy = "signaturesSet")
    private Set<Signature> signatures;
    @OneToMany(mappedBy = "signaturesSet")
    private Set<Transaction> transactions;
}
3

Thanks for you’re anwer Christian.

I tried the @Subselect you suggested,but then I get errors when Oracle tries to do updates on the query specified in the @Subselect. I can image it works fine as long you only do queries and don’t except hibernate to still have full update capabilities.

So I regard my provided DB schema as not suited for the hibernate framework, and will refactor the database to a schema which follows more standard hibernate practices. Probably adding an extra table for the SignaturesSet.

Thanks again for you’re feedback

4

You just jump to conclusions without giving further details.
Surely you can adapt your table model to an IMO more structured form, but if you can’t do that, mark the entity as @Immutable, since clearly that @Subselect entity can’t be updated.