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?

score 3 · Answer 1 · answered Nov 27 '10 at 21:07

3

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.

answered Nov 27 '10 at 21:07

BalusC

1,082,665
372
3,610
3,555

Oh... That's what I need! Could you give me some links or examples of usage ServletContextListener in such situations? – Ilnur Nov 28 '10 at 20:01
There's an example in [this answer](http://stackoverflow.com/questions/4175726/how-do-i-load-a-java-class-not-a-servlet-when-the-tomcat-server-starts/4175742#4175742). – BalusC Nov 28 '10 at 20:02
Yes, I catch it. But I have big troubles of getting beans inside ServletContextListener. How to @Autowire some fields of this class (e.g. some field like: DBInitService dbInitService). It needs, because dataSource is also bean, so it should be autowired to my app. But inside this Listener I can't get any of this classes. Also, I can't make listener to be autowired. How to do it? – Ilnur Nov 28 '10 at 21:45

Sean Patrick Floyd · Answer 2 · 2010-11-27T22:24:57.373

3

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

edited Nov 27 '10 at 22:24

answered Nov 27 '10 at 22:19

Sean Patrick Floyd

292,901
67
465
588

If I'd use this property: , it will recreate schema, if it already exist? – Ilnur Nov 28 '10 at 19:57
@Ilnur Two ways to handle that. a) code SQL defensively, e.g. `CREATE SCHEMA IF NOT EXISTS` b) create a file in a predefined location (e.g. `%HOME%/.yourapp/.schemagen`) check for the existence of this file (e.g. using SpEL: http://static.springsource.org/spring/docs/3.0.x/spring-framework-reference/html/expressions.html ) – Sean Patrick Floyd Nov 29 '10 at 08:07

score 1 · Answer 3 · answered Nov 27 '10 at 21:21

I have just started to create spring based application. I am using JPA + Hibernate for implementation of database access layer. One good side effect of this way is that it behaviors exactly as you are describing, i.e. the database is created automatically when application starts. It is very convenient. I have not written even one line in SQL for this project and I have database with indexes, foreign and primary keys etc. It's just fantastic and I recommend you to do the same.

JDBC Create table

3 Answers3