Creating tables on demand with Spring Boot Data JPA

Question

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

Answer 1

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.

Answer 2

A couple of years ago I used to work on such a project - we used to work with spring boot back than but have not used hibernate/jpa/spring data intentionally.

IMHO The whole idea of JPA is to provide entities (as the assumption is that you're a java programmer who doesn't necessarily know anything about SQL / Db Schema) and let hibernate/jpa do its magic and define the schema for you (given the properties). Its also possible to go the "other-way-around" and have your tables already and try to create entities so that they will "map" onto the existing structure in the RDBMS.

What you've describe simply doesn't fit to this approach. So in our project, since we didn't want to deal with the complexity of queries introduced by hibernate and wanted to optionally go with a dynamic structure like you describe, we've done the following:

• Used Flyway to generate the schemas. This tool is integrated with Spring boot so that it gets called when the application starts and checks the schema version, if it has more "recent" scripts - it applies the changes to the DB - something that is called "migrations". Another popular alternative is liquidbase, both work with spring boot AFAIK.
• We've used JOOQ to avoid writing a plain JDBC queries. If you decide to work with JOOQ (I can totally recommend this tool - it worked great for us, simple, convenient, type-safe), you can read this tutorial to integrate with spring boot. Other alternatives are MyBatis or if you want to go "low-level", use JDBI or maybe Spring's JdbcTemplate. I'm sure there are other alternatives as well.

So basically with this setup the flow looks like this: In a build time you generate the Java Code (JOOQ) that stands behind the tables. This code is no-way near like Hibernate entities, it contains just enough information to prepare the query in JAVA DSL-like language, it doesn't generate queries by itself.

When the application starts - it checks the schema and applies migrations (flyway). Migrations are source files in which you write the SQL.

Now one possible caveat is that you need a permission at the level of RDBMS to actually create tables like that. So depending on your organization it might be not be trivial, so I suggest to discuss this approach with your DBA first.