JDBC Create table

Question

My web application, written in Spring Web-MVC uses JDBC to work with data. I want to make my app to automatically create tables (and schema), when end-user runs it for the first time (but loads created schema, when it runs it again). I'm using HSQLDB as database engine.

Any ideas, how to do it? (I don't want to write inside app special methods to check, does the table exist, and if it is not, to create them. Does any more useful method doing it exist?)

P.S. I'm thinking about using Hibernate instead of simple connection method. Is there any way to solve it using Hibernate?

Answer 1

I don't want to write inside app special methods to check, is table exist, and if it is not, to create them. Does any more useful method doing it exists?

Just create the DB by the installer of your webapplication. This can also be done webbased.

An alternative is to implement a ServletContextListener which checks the DB and creates tables if necessary. The ServletContextListener get executed only once during application's startup, so you don't need to check it on every request or so.

Answer 2

Spring JDBC has a standard mechanism for initializing databases.

If you want to initialize a database and you can provide a reference to a DataSource bean, use the initialize-database tag in the spring-jdbc namespace:

<jdbc:initialize-database data-source="dataSource">`
  <jdbc:script location="classpath:com/foo/sql/db-schema.sql"/>
  <jdbc:script location="classpath:com/foo/sql/db-test-data.sql"/>
</jdbc:initialize-database>

You can also turn this on or off using system properties:

<jdbc:initialize-database data-source="dataSource"
    enabled="#{systemProperties.INITIALIZE_DATABASE}">
  <jdbc:script location="..."/>
</jdbc:initialize-database>

---

About using hibernate: hibernate can also automatically generate a database schema, here's the relevant part of the reference: Automatic Schema Generation