Spring Boot JPA: Mapping one entity to multiple (a lot) tables with same columns

Question

I have a lot of (like 60+) tables which have the same schema and similar names:

log_2020_07_01
log_2020_07_02
... and so on.

They have the same columns: id, site, size. Each of the table contains around 2 million rows.

I've read Hibernate and tables with same data/columns but with different table names which suggests to use hibernate alone. I hope after seven years maybe there's something new we could do with JPA.

In JPA, is it possible to just write one entity class and let the code to handle which table to use?

E.G.,

for(int i=0;i<60;i++) {
    // read from the i-th table.
}

Just as a comment for this particularly: _I hope after seven years maybe there's something new we could do with JPA_. Why would anyone put effort to handle all possible bad designs? So I would not hold breath waiting a solution for this. There is a real fix for this but it requires that somebody stops generating these tables and somebody is willing to do some database table migration. — pirho, Jul 06 '20 at 19:35
Did I say so:) Are you solving problem related to implementation of horizontal sharding? Are these tables located in several db servers? Well i guess that there are good and bad ways to implement it and no strict standards. Few links [here](https://stackoverflow.com/q/13672019/6413377). At a quick glance these extensions have not gained popularity and the certainly do not use table names to hash rows around servers. [Ex. of using Hibernate shards](https://sleeplessinslc.blogspot.com/2008/09/hibernate-shards-maven-simple-example.html). Guess that not allowing differerent table names either — pirho, Jul 07 '20 at 04:56
@pirho I added an interceptor to dynamically change the table name as needed. Will try the shard link you provided. — Top.Deck, Jul 07 '20 at 05:22
Hope you manage to get it working. If you do, then provide your solution as an answer, looking forward to see it. — pirho, Jul 07 '20 at 06:58
@pirho [Another similar link](https://docs.jboss.org/hibernate/stable/shards/reference/en/html/shards-configuration.html). It requires to config multiple data sources in advance, which doesn't work for my case since the tables will be created dynamically. — Top.Deck, Jul 07 '20 at 13:30

score 0 · Accepted Answer · answered Jul 07 '20 at 13:56

First of all, we could use a middleware to make the sharding transparent to users for middle/large projects.

Here is a quick work around for my small project (and I'm the only developer working it):

Step 1, create an inteceptor:

public class MySqlInterceptor extends EmptyInterceptor {

    private String entityName;
    @Setter
    private int tableId;

    protected MySqlInterceptor() {
        super();
    }

    public MySqlInterceptor(String entityName) {
        this.entityName = entityName;
    }

    @Override
    public String onPrepareStatement(String sql) {
        // Here is the trick.
        String modifiedSql = sql.replaceAll(entityName, entityName + tableId);
        log.debug("{}", modifiedSql);
        return modifiedSql;
    }
}

Step 2, hock up the interceptor:

MySqlInterceptor mySqlInterceptor = new MySqlInterceptor("temp");
                mySqlInterceptor.setTableId(tableId);
                session = entityManagerFactory.unwrap(SessionFactory.class).withOptions().interceptor(mySqlInterceptor).openSession();

Explanation: Hibernate is using JDBC to communicate with the database. The interceptor will change the table name in the sql from an entity's name (in my case it's temp) to a real table name (temp1, temp2, ...) at runtime.

P.S., be careful when you use multi-thread.

Spring Boot JPA: Mapping one entity to multiple (a lot) tables with same columns

1 Answers1