Dyamic table name for JPQL / Hibernate query

Question

I've a database with many thousands of tables that have been (and continue to be) created with a naming strategy - one table per calendar day:

data_2010_01_01
data_2010_01_02
...
data_2020_01_01

All tables contain sensor data from the same system in the same shape. So a single entity (lets call it SensorRecord) will absolutely map to all tables.

I'd imagined something like this would work:

@Query(nativeQuery = true, value = "SELECT * FROM \"?1\"")
Collection<SensorRecord> findSensorDataForDate(String tableName);

But it does not, and reading around the topic seems to suggest I am on the wrong path. Most posts on dynamic naming seem to state explicitly that you need one entity per table, but generating thousands of duplicate entities also seems wrong.

How can I use JPA (JPQL?) to work with this data where the table name follows a naming convention and can be changed as part of the query?

Does this answer your question? [How to replace table name with value from parameter while using Spring Data JPA nativeQuery](https://stackoverflow.com/questions/40956013/how-to-replace-table-name-with-value-from-parameter-while-using-spring-data-jpa) — Robert Niestroj, Jan 03 '20 at 10:55
Partially. One answer says "you can't" and another shows an entity manager code snip without context of why that is the preferred solution. Alex's answer below is a considerably more complete and useful response. — Dan, Jan 03 '20 at 16:35

Oleksii Valuiskyi · Accepted Answer · 2020-01-03T15:34:48.470

Parameters are only allowed in the where clause. You can create custom repository method returns collection of SensorRecord dto. No need to map so many entities. You should get List<Object []> as query result and manually create dto objects.

    @Autowired 
    EntityManager entityManager;    

    public List<SensorRecord> findSensorDataForDate(LocalDate date) {
           DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy_MM_dd");
           String tableName = "data_" + date.format(formatter);

           Query query = entityManager.createNativeQuery(
                             "select t.first_column, t.second_column from " + tableName + " t");

           List<Object[]> queryResults = query.getResultList();

           List<SensorRecord> sensorRecords = new ArrayList<>();
           for (Object[] row : queryResults) {
               SensorRecord record = new SensorRecord();
               record.setFirstParameter((Integer) row[0]);
               record.setSecondParameter((String) row[1]);

               sensorRecords.add(record);
           }

           return sensorRecords;
   }

Thanks for the worked example. This is very helpful. Seems there is no way to have the table name as a JPQL parameter so this is what I need. — Dan, Jan 03 '20 at 16:32

score -1 · Answer 2 · answered Jan 03 '20 at 06:38

-1

Could it be just syntax error?

This has worked for me:

    @Query(value = "select * from job where job.locked = 1 and job.user = ?1", nativeQuery = true)
    public List<JobDAO> getJobsForUser(@Param("user") String user);

answered Jan 03 '20 at 06:38

gai-jin

653
2
10
24

This example has a fixed table name "job". I need to be able to change the table name. So in your example: select * from job_123 or select * from job_456 ... – Dan Jan 03 '20 at 14:45

Dyamic table name for JPQL / Hibernate query

2 Answers2