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
– pirho, Commented Jul 6, 2020 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. 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. Guess that not allowing differerent table names either — pirho
– pirho, Commented Jul 7, 2020 at 4:56
@pirho I added an interceptor to dynamically change the table name as needed. Will try the shard link you provided. — Top.Deck
– Top.Deck, Commented Jul 7, 2020 at 5:22
Hope you manage to get it working. If you do, then provide your solution as an answer, looking forward to see it. — pirho
– pirho, Commented Jul 7, 2020 at 6:58

Top.Deck · Accepted Answer · 2020-07-07 13:56:41Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related