Can Spring Data JPA support dynamic table name creation and query support?

[geekxingyun]

hi,
team, as the title above, can Spring Data JPA support dynamic table name creation and query support?
we wish to have the function that Spring Data JPA support dynamic table name creation and query support.

[schauder]

I'm not sure I understand what you are looking for. Could you elaborate? Maybe with some example pseudo code how a solution could look like?

[spring-projects-issues]

If you would like us to look at this issue, please provide the requested information. If the information is not provided within the next 7 days this issue will be closed.

[geekxingyun]

sorry, i am so busy recently.

We want a function, similar to the function of multiple data-source, which table can be dynamically created and selected in the program.

As you know, there are more and more data now, we hope to dynamically create and select tables based on the month or dynamically create and select tables with the same fields based on the user ID.

With the continuous increase of data, we hope to split the data according to users or date, with a table for each user, so that each query will only query the table data of the current user, so that the query data will be faster. I think it is a very important requirement.

At present, this operation can only be supported in frameworks such as mybatis, but I don’t know how to implement it in Spring Data JPA, because each repository must know the name of the table in advance, and the table we need to operate is a dynamic table name. Over time, the data will be split according to the month, day, year, etc., and the database will be divided into tables.

These tables will have the same fields but different table names

Hope this feature will support dynamic creation and selection of tables like this:

one ways split talbe based on user Id :

t_like_video_record_10001

t_like_video_record_10002

t_like_video_record_10003

the another way split table based on date:

t_like_video_record_10001_09_2021

t_like_video_record_10001_08_2020

t_like_video_record_10001_07_2019

1. it maybe be design like this :

Page<T> findAll(T templateEntity,String actualTableName);

Page<T> findAll(T templateEntity,String actualTableName,Example<T> exampleT);

Page<T> findAll(T templateEntity,String actualTableName,Example<T> exampleT,Pageable pable);

define interface example as below:

Page<LikeVideoRecordTempalteEntity> findAll(LikeVideoRecordTempalteEntity templateEntity,String actualTableName);

the LikeVideoRecordEntityTempalte is the template entity table

the actualTableName is the actual table name.

we can called the method

String actualTableName="t_like_video_record_10001";

this.likeVideoRecordTempalteJpaRepository.findAll(LikeVideoRecordTempalteEntity.class,actualTableName);

if t_like_video_record_10001 table is not existed,spring data jpa can create it automatically.

if t_like_video_record_10001 is existed,then do that as below:

SELECT * FROM ${actualTableName}

these filed fetch from LikeVideoRecordTempalteEntity object, but table name use actualTableName.

[schauder]

JPA doesn't support this, so I don't see much of a chance to support it in Spring Data JPA.

I also do think this well covered by the partitioned table feature that many RDBMSs offer.

[geekxingyun]

JPA doesn't support this, so I don't see much of a chance to support it in Spring Data JPA.

I also do think this well covered by the partitioned table feature that many RDBMSs offer.

your meaning is spring data jpa doesnot'support dynamic create and query table name?

Your suggestion is to use Sharding-JDBC, mycat...?

[schauder]

Correct: Spring Data JPA doesn't support this. If one would want to add support it should be added to JPA and/or JPA implementations, not Spring Data JPA.

My suggestion is to look into partitioned tables like: https://docs.oracle.com/cd/B10500_01/server.920/a96524/c12parti.htm

[velpulanaveenreddy]

sorry, i am so busy recently.

We want a function, similar to the function of multiple data-source, which table can be dynamically created and selected in the program.

As you know, there are more and more data now, we hope to dynamically create and select tables based on the month or dynamically create and select tables with the same fields based on the user ID.

With the continuous increase of data, we hope to split the data according to users or date, with a table for each user, so that each query will only query the table data of the current user, so that the query data will be faster. I think it is a very important requirement.

At present, this operation can only be supported in frameworks such as mybatis, but I don’t know how to implement it in Spring Data JPA, because each repository must know the name of the table in advance, and the table we need to operate is a dynamic table name. Over time, the data will be split according to the month, day, year, etc., and the database will be divided into tables.

These tables will have the same fields but different table names

Hope this feature will support dynamic creation and selection of tables like this:

one ways split talbe based on user Id :

t_like_video_record_10001

t_like_video_record_10002

t_like_video_record_10003

the another way split table based on date:

t_like_video_record_10001_09_2021

t_like_video_record_10001_08_2020

t_like_video_record_10001_07_2019

1. it maybe be design like this :

Page<T> findAll(T templateEntity,String actualTableName);

Page<T> findAll(T templateEntity,String actualTableName,Example<T> exampleT);

Page<T> findAll(T templateEntity,String actualTableName,Example<T> exampleT,Pageable pable);

define interface example as below:

Page<LikeVideoRecordTempalteEntity> findAll(LikeVideoRecordTempalteEntity templateEntity,String actualTableName);

the LikeVideoRecordEntityTempalte is the template entity table

the actualTableName is the actual table name.

we can called the method

String actualTableName="t_like_video_record_10001";

this.likeVideoRecordTempalteJpaRepository.findAll(LikeVideoRecordTempalteEntity.class,actualTableName);

if t_like_video_record_10001 table is not existed,spring data jpa can create it automatically.

if t_like_video_record_10001 is existed,then do that as below:

SELECT * FROM ${actualTableName}

these filed fetch from LikeVideoRecordTempalteEntity object, but table name use actualTableName.

For Fetech it's not possible to create you can, by extending PhysicalNamingStrategyStandardImpl
switch (identifier.getText()) {
case " t_like_video_record_":
try {
tableName = identifier.getText()+yourConstant(_10001) + dbDailyDateFormatter.format(dfYearMonthDate.parse(yourdate)(month-year)";
} catch (ParseException e) {
LOG.error("Error while parsing data {} with the format {}",rundate,dfYearMonthDate,e);
}
return Identifier.toIdentifier(tableName);
default:
return Identifier.toIdentifier(identifier.getText());
}
and hbm2ddl must be update.

[jk2K]

use StatementInspector, refer to https://medium.com/@akshitagarwal97/how-we-used-hibernate-filter-and-inspector-to-mitigate-performance-issues-with-partitioning-in-d10babd1bbd1

public class MyJpaInterceptor implements StatementInspector {
    @Override
    public String inspect(String s) {
        if (s.contains("order")) {
            s = s.replace("order", DynamicTableNameDataHelper.getTableName());
        }
        return s;
    }
}

public class DynamicTableNameDataHelper {
    private static final ThreadLocal<String> REQUEST_DATA = new ThreadLocal<>();

    public static void setTableName(String tableName) {
        REQUEST_DATA.set(tableName);
    }

    public static String getTableName() {
        return REQUEST_DATA.get();
    }
}

usage example

DynamicTableNameDataHelper.setTableName("order_" + aa.toLowerCase());
orderRepo.save(order);