How to create a view or table from an Entity?

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?

Solution

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.