2

I'm doing some research around cryptocurrencies and when getting to the technical aspects of it I found a problem that maybe its more common and someone already found a solution.

I have found a database with historic information by product and it has different tables for the different combinations but the structure of the table is the same.

I have design this DBO, nothing rocket science:

public class ProductHistoryDbo {
    private long id;
    private long startTime;
    private long endTime;
    private float low;
    private float high;
    private float open;
    private float close;
    private float volume;
}

And the database has one table per (exchange, currency_in, currency_to)

product_history_gdax_bch_btc
product_history_gdax_bch_eur
...

There are 12 tables with the same structure and one additional with all the other tables that you can find inside.

So my idea is to have only one Entity and Repository but dynamically change, if possible, from which table to retrieve the data in spring-boot in order to adapt if in the future new tables are added without the need of adding boilerplate code.

Final E2E is to have an admin page with a combobox with all the tuples which will do a request to this server and changes in the database will not imply a change in the backend code.

pvpkiran
  • 25,582
  • 8
  • 87
  • 134
RamonBoza
  • 8,898
  • 6
  • 36
  • 48
  • Possible duplicate of [In @Table(name = "tableName") - make "tableName" a variable in JPA](https://stackoverflow.com/questions/2444962/in-tablename-tablename-make-tablename-a-variable-in-jpa) – pvpkiran Jan 25 '18 at 14:06
  • I don't think there is a clean way of doing that. Why not simply keep `exchange, currency_in, currency_to` as part of `ProductHistoryDbo` state? If performance is your concern, you might consider creating an index composed of these three attributes (obviously, whether this improves performance depends on read/write ratio) – crizzis Jan 25 '18 at 14:58
  • @crizzis because I don't have control of those tables – RamonBoza Jan 25 '18 at 15:32
  • 1
    You cannot map the two tables to the same entity. You have to create two. – Simon Martinelli Jan 25 '18 at 15:35
  • In such case, the only workaround I see is to create an entity for each class, as @SimonMartinelli suggested, and use `ProductHistoryDbo` as a `@MappedSuperclass`. – crizzis Jan 25 '18 at 16:21
  • Also, in such an solution, you could use the approach described here: https://docs.spring.io/spring-data/jpa/docs/1.5.0.RELEASE/reference/html/jpa.repositories.html#d0e2093 to provide common functionality (query definitions etc.) for your Spring Data repository classes – crizzis Jan 25 '18 at 16:32

1 Answers1

0

You can create a base class and then extend it with the only difference for each final class being the table name.

Base class:

@Entity
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
public abstract class Lut implements BaseCrudEntity<Long> {

}

Subclasses:

package xxx.lut;
import javax.persistence.*;

@Entity
@Table(name = "rwx_gnrl_lut_dm")
public class LutDm extends Lut {

}
Syscall
  • 19,327
  • 10
  • 37
  • 52
Werner Reiche
  • 31
  • 4