How do i create new table in database from same entity JPA Spring boot?

Question

I want to create a new table every day with the full date as table name if the day is new create table for the that day

I have seen that when I change @Table(name="") name to a new string it make a new table but I can't automate this work

@Entity
@Table(name="orders_25_10_2021")
public class game2data {
@Id
private int cid;
private String name;
private Integer address;
private Integer gender;
private String date;
private String Done;

In simple words, I want to pass the name of @Table(name="") as a variable with dynamic according to date.

I can't think of a solution that will make you able to do this and get the benefits of JPA abstractions. You can however take the bare metal path and write some runnable with an infinite loop that checks the time and creates a new table every day. or maybe do some sort of cronjob or something like that. Can you give more information about your business logic? because you might be able to rearchitect your database in a way in which you don't have to write such a hacky solution. — Abdullah Khaled
– Abdullah Khaled, Commented Oct 25, 2021 at 18:39
That's not possible. It looks like you are trying to create partitioning on the JPA level. Why don't you use the database features? What database are you using? — Simon Martinelli
– Simon Martinelli, Commented Oct 26, 2021 at 8:18
@Simon Martinelli in simple words, i want to create a new table with the same columns mentioned above, every day automatically without changing the name String in @Table() annotation — LEGENDRYzZzZ
– LEGENDRYzZzZ, Commented Oct 26, 2021 at 10:50
That's called partitioning in the database world. Please read the documentation. And should be done with the database and not with JPA (despite JPA cannot do that at all) dev.mysql.com/doc/refman/8.0/en/partitioning.html — Simon Martinelli
– Simon Martinelli, Commented Oct 26, 2021 at 12:09

Lookslikeitsnot · Accepted Answer · 2021-11-02 07:34:28Z

1

You can achieve this custom naming strategy by extending SpringPhysicalNamingStrategy and overriding its toPhysicalTableName method :

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import org.hibernate.boot.model.naming.Identifier;
import org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl;
import org.hibernate.engine.jdbc.env.spi.JdbcEnvironment;
import org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy;
import org.springframework.context.annotation.Configuration;

@Configuration
public class TableNamingStrategy extends SpringPhysicalNamingStrategy {
  private static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("dd_MM_YYYY");
  public static final PhysicalNamingStrategyStandardImpl INSTANCE =
      new PhysicalNamingStrategyStandardImpl();

  @Override
  public Identifier toPhysicalTableName(Identifier name, JdbcEnvironment context) {
    if (!name.getText().equals("game2data"))
      return name;
    StringBuilder customName =
        new StringBuilder("orders").append('_').append(DATE_FORMATTER.format(LocalDate.now()));
    return new Identifier(customName.toString(), name.isQuoted());
  }
}

and registering it in your application.properties:

spring.jpa.hibernate.naming.physical-strategy=com.stackoverflow.questions.TableNamingStrategy

and removing the @Table annotation from the game2data entity.
This method is limited by the fact table names are determined at application start-up.

As a proof of concept, here's a way to update the table name every day by extending StatementInspector. By using this, you won't be able to read old data. You'll also have to use custom implementations of the JpaRepository's methods to create the new table every day before you insert data in it.

edited Nov 2, 2021 at 7:34

answered Oct 27, 2021 at 7:41

Lookslikeitsnot

4041 gold badge4 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

LEGENDRYzZzZ Over a year ago

I have to add this code in same entity class or make a new class? please assist

Lookslikeitsnot Over a year ago

You have to make a new class that extends SpringPhysicalNamingStrategy, and give the fully qualified class name to spring.jpa.hibernate.naming.physical-strategy. In my example the class name was TableNamingStrategy and the package was com.stackoverflow.questions but you could give the class any name you prefer and adapt your application.properties file accordingly.

LEGENDRYzZzZ Over a year ago

Hey I have a simple question can do schedule this function for every 24 hrs soo that this automatically make a new table without restarting the server

LEGENDRYzZzZ Over a year ago

and one more thing i want to make one more function for another table with same **_Date formate I can I doo this also

Rohit Chaure Over a year ago

hii I just want to ask that how can we call this naming strategy inside a controller or service file of our project. I am a beginner please help me.

Collectives™ on Stack Overflow

How do i create new table in database from same entity JPA Spring boot?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related