2

I've a spring boot project, and I'm using hibernate to map my entities to DB, However now I have a new requirement: I need to be able to dynamically create tables in DB, without any mapping (so far).

Does anyone know about some framework, to help me to deal with this: -I want to execute SQL or similar(ddl) to create my tables -I need to deal with connection management

I've been told about spring-data, but I need some opinion about this.

An example: Imagine I have a service available to client, for example:

class DBHACKService {
  void executeSQL(String ddl).
}

The client invoke this like :

new DBHackService.executeSQL ("create table mytable (name varchar)");

In this method I can do some manipulation to the sql. The question is: which is the best way to send it to DBEngine.

Thanks again

Thanks in advance. rui

user1680680
  • 213
  • 2
  • 9
  • Hello your question is very I want to achieve the same thing. I see no one answered you really. Did you get an answer somewhere? – Antoine SAMAHA Jan 16 '22 at 12:28

4 Answers4

2

You can have a look at DdlUtils where one can feed it at any time with the changes you need at runtime and have your changes reflected on the DB. The format is database-independent so you won't have to worry about portability yourself.

I do not know how up to date it is though.

If you fancy Groovy.

Timmo
  • 3,142
  • 4
  • 26
  • 43
1

Take a look at the Spring Boot documentation on database initialization.

The simplest option is to place a schema.sql file in the root of the classpath (for example in src/main/resources). Any DDL in this file will be run as part of your application's startup. One of Spring Boot's JDBC samples shows this in action.

A second, more sophisticated, option is to use a database migration tool like Liquibase or Flyway. The advantage of a migration tool is that it makes it much easier to evolve your database schema as your application's needs change. Spring Boot has small sample applications for both Liquibase and Flyway.

Andy Wilkinson
  • 108,729
  • 24
  • 257
  • 242
  • Hi. I believe there's here some miss understanding. What I actually need is to send SQL (DDL) to my DB Engine when my application is already running, hence in runtime. – user1680680 Oct 15 '14 at 10:15
  • Imagine I have a service available to client, for example: class DBHACKService { void executeSQL(String ddl). } The client invoke this like : new DBHackService.executeSQL ("create table mytable (name varchar)"); In this method I can do some manipulation to the sql. The question is: which is the best way to send it to DBEngine. Thanks again – user1680680 Oct 15 '14 at 10:21
0

If you wish to create schema dynamically I guess this should help you.

Hibernate: Automatically creating/updating the db tables based on entity classes

You could issue ddl statments using jdbc to create tables on the fly. Keeping the queries in some xml file. So that it remains generic. Have a watcher service on the config file, the moment file gets updated with new query you can fire required jdbc calls

Community
  • 1
  • 1
jithin iyyani
  • 751
  • 1
  • 7
  • 19
  • Hi. Thanks for your answer. This is not about to automatically generate the schema. This is about to create new tables not related with domain (or at least no directly related). I want a way to request for table creation in runtime. – user1680680 Oct 15 '14 at 00:01
-1

What do you wish to do with the sql passed as parameters.

If you wish to validate the sql, one option would be to fire query and if you get an exception which implies there is some issue with passed argument.

If you wish to parse sql query then you can use jSqlParser. http://jsqlparser.sourceforge.net/

jithin iyyani
  • 751
  • 1
  • 7
  • 19
  • Hi. Thanks for replying. What I want is to create and alter tables in runtime. Not actually validate the SQL. I want to create, alter, delete tables in runtime. – user1680680 Oct 17 '14 at 10:22
  • for create/alter you can use DdlUtils. You can read the model from the database and from there on make changes to the model and then write the changes to the database. I am not sure about the delete case but in the worst case you can use the DROP statement. – Timmo Oct 17 '14 at 13:57