JPA: dynamically mapping an Entity to a table based on an instance variable

Question

Here is the problem i have:

class CurrencyPrice {
    @Transient
    String pair;
    float spotPrice;
    Date timeStamp;
}

And I have 3 table the names of which stand for "usd value of euro/gbp/yen respectively": usd_euro, usd_gbp & usd_yen. They all have the same 3 columns: id, spotprice, timestamp.

For some reason i cannot have a single table. The transient instance variable 'pair' will have the following values depending on what it represents: "usd_euro", "usd_gbp" & "usd_yen"

1. And depending on the value in 'pair' I want to insert a row in one of the tables, eg: if I have the value "usd_yen" in 'pair' then the object should be persisted in usd_yen table.

2. And when I want to fetch data, I want JPA to decide which table to SELECT from based on the value in 'pair'

This is simple in JDBC but is there a way to do this in JPA?

Thank you.

Answer 1

If I understand your requirements correctly, this might actually be feasible in JPA now (those threads you cite are quite old), if you can use inheritance on your entity and an additional join table and if it's acceptable that the ID for each type is not contiguous.

You could basically define your classes like this then:

@Entity
@Table(name="curr_base") // choose a suitable name
@Inheritance(strategy=InheritanceType.JOINED)
@DiscriminatorColumn(name="currency", discriminatorType=DiscriminatorType.STRING) // your join table needs this column in addition to the id
public abstract class CurrencyPrice {
    @Id
    private int id;
}

@Entity
@Table(name="usd_euro")
@DiscriminatorValue("usd_euro")
public class UsdEuroPrice extends CurrencyPrice {
    float spotPrice;
    Date timeStamp;
}

@Entity
@Table(name="usd_gbp")
@DiscriminatorValue("usd_euro")
public class UsdGbpPrice extends CurrencyPrice {
    float spotPrice;
    Date timeStamp;
}

@Entity
@Table(name="usd_yen")
@DiscriminatorValue("usd_euro")
public class UsdYenPrice extends CurrencyPrice {
    float spotPrice;
    Date timeStamp;
}

I replicated spotPrice and timeStamp on each subclass so that you don't have to modify your existing table definitions - of course it would be much cleaner to only have them on the superclass/join table.

This mapping allows it for example to perform a EntityManager.persist(new UsdGbpPrice(...)) and have JPA insert a row into the right table. For more information, look here.