javaspring-bootspring-data-jpaddlgeneric-programming

Creating tables on demand with Spring Boot Data JPA

I'm trying to create a Spring Boot application where I want to create and use database tables on demand, without defining entities / repositories for them.

I have got a base Business entity, and BusinessType entity to keep the type of business. I want to be able to dynamically create a table for a business type (say Coffeeshop) with its own table (while I can keep the name of the table on the BusinessType table and decide which table to query from there).

So without defining an entity and/or creating a CrudRepository for Coffeeshop can I create a table named Coffeeshop and later do some queries on that?

If not, what are my alternatives? How do you deal with situations where you have got a base type and allow the system to have more concrete types (persisted and associated with base type) in a typical Spring Data JPA application?

Thank you

Solution

  • The persistance strategy you describe is not a good fit for JPA. JPA is an ORM (Object relational mapping) strategy where a framework maps data from class based objects to rows in schema defined relational tables. It would complicate the annotation driven implementation to have tables with no corresponding class, or classes without a specific tables.

    To solve your problem using JPA, an idomatic solution would be to keep all your businesses in one table (mapped to a Business entity class, as you described), and to have a (foreign) key on the table to allow each row to have a business type. Then you could select the set of businesses with a particular type via a query (rather than by targeting a different table.)

    Business
id | type       | name           | etc...
1    COFFEESHOP   Alices Coffee

BusinessType
id          | name | industry
COFFEESHOP    Cafe   HOSPITAILITY

    Then to get all coffee shops in your system you would define a BusinessRepository method

    @Query(value = "{ 'type': ?0  }")
List<Business> findAllByType(String type);

    To solve your problem using the table creation strategy you described, you'd have to write most of the data access layer yourself. You could do this in a handwritten BusinessDAO class (not using JPA or annotations), which would have methods that construct and execute SQL statements using plain JDBC to achieve the required schema manipulations. If you wanted a library to emulate the SQL dialect independance that JPA gives you you could check out JOOQ. This strategy will require a lot more work, and such an application will require schema editing permissions. In enterprise environments that's usually discouraged.