How to create a view or table from an Entity?-CodePudding

I have a question regarding views in the context of jpa entities in SpringBoot. Up to now I am using the auto create feature that automatically creates the tables by the definitions of the entities in Java. Now my application has grown so far that I need to use views. I do not want to write and maintain the sql create statements for all tables/entities otherwise I could simple add the create view statement to the schema.sql file, which I do not want to use. Instead I have a commandLineRunner that creates the views after startup but when testing the app it fails because the entities reference the views before the idividual views are created.

So is there a way to write an sql create statement in the entity maybe with an annotation to create a view during entity instantiation?

CodePudding user response：

On startup you can initialize a data bootstrap. I got the code from here, which basically is a void method with your repository Autowired where you are able to create and load your data on startup of Spring.

I have implemented this dataloader in my own code and you can find this on my public GitHub.

By using an abstract class you can extend the methods (to seperate dev from prod class loaders) and with IoC you set the repository and load the data. I use CRUD with Redis, but it's pretty universal.

    private final PriceRepository priceRepository;

    @Autowired
    public ProductionDataLoader(PriceRepository priceRepository, KeywordRepository keywordRepository, AccountRepository accountRepository) {
        this.priceRepository = priceRepository;
    }

    @Override
    public void loadEnvironmentSpecificData() {
        doSomethingWithData();
    }

By using @Profile annotations you can seperate dev from prod for example. This is a hobby project of mine, I'm in no way a (certified) developer...

CodePudding user response：

I found a very simple way, how to create a view without having to create all tables that have been managed by JPA with the entity instantiation automatically.

Basically I let spring boot start up and create all tables. This includes creating a table with the same name as the desired view by the JPA entity. After startup I simply drop the table and then create my own view in an sql script.

It works very well and the entity keeps using the view after the table is dropped because they both have the same name.

Here is some code:

public class StartUpRunner implements CommandLineRunner {

   public static final String VIEW_INIT_FILE = "after_hibernate_init.sql";
   @Autowired
   private DataSource dataSource;

   @Override
   public void run(String... arg) throws Exception {
      createSQLViews();
   }

   private void createSQLViews(){
      boolean IGNORE_FAILED_DROPS = true;
      ResourceDatabasePopulator resourceDatabasePopulator = new ResourceDatabasePopulator(false, IGNORE_FAILED_DROPS , "UTF-8", new ClassPathResource(VIEW_INIT_FILE));
      resourceDatabasePopulator.execute(dataSource);
   }
}

And in the sql file there should be something like:

DROP TABLE IF exists YOUR_VIEW_NAME;

CREATE OR REPLACE View YOUR_VIEW_NAME
//Your view creation statement here....

It is very important to set the flag "ignore failed drops" to true because after the first startup the view will already exist and the sql script fails on the drop tables statement which would shut down the application. This way SpringBoot ignores the failed statement and starts up normally.

A downside to this approch is that you cannot test the view with @DataJpaTest anymore since the StartUpRunner needs to create the view. At least if you are like me and use the embedded H2 database from SpringBoot, which needs to be initialized before every test class.

My test annotation for testing the views looks like this:

@ActiveProfiles("sqltest")
@ExtendWith(SpringExtension.class)
@SpringBootTest
@TestMethodOrder(MethodOrderer.OrderAnnotation.class)

I am using the testMethodOrder because the sql inserted data is not cleared after each test anymore and I only insert the data before the first test and use it in all tests.

The activeProfiles annotation should be relatively self-explanatory. There I specify the test H2 database and other app specific settings.

Feel free to ask more about this approach on how to squash views into the auto-create feature of JPA.