Pattern for creating a database schema using JDBC

Question

I have a Java-application that loads data from a legacy file format into an SQLite-Database using JDBC. If the database file specified does not exist, it is supposed to create a new one. Currently the schema for the database is hardcoded in the application. I would much rather have it in a separate file as an SQL-Script, but apparently there is no easy way to execute an SQL-Script though JDBC. Is there any other way or a pattern to achieve something like this?

Answer 1

The "right" way is to use Hibernate. Among all other benefits it is capable of creating/updating a DB schema automatically (SO1, SO2).

Answer 2

In case you are working in a development environment, I would advice you to use an ORM tool like Hibernate that forward engineers based on your Java Domain Models to create the DB tables. Hibernate has the feature to auto create/update the tables in case there are changes to DB schema.

As you are using SQLite you could have a look at Hibernate for SQLite

I have read somewhere that it not advisable to use this feature in a production environment because the incremental table creation might negatively affect the existing data.

Answer 3

Your sentence "there is now easy way to execute an SQL-Script though JDBC" confused me for a minute, but I reckon you are saying "there is no easy way". :)

Based on what others have said, yes... the perfect world scenario is to use an ORM tool, like Hibernate, but I also understand the fact when you are dealing with legacy stuff at work, your team may not want to spend too much time refactoring that project.

I agree that you should refactor out the database schema into a separate file. You can actually execute the SQL script using JDBC. I do that all the time when I run my certain testcases.

Here's how I do it. I use SQL Server database in my case. So, you need to tweak the code to fit your needs.

String ddl = ... // load your SQL script file into this string
String delimiter = "GO"; // in my case, SQL Server uses GO as delimiter, you use whatever you want here.


private void executeDDL(String ddl, String delimiter) {
    Connection con = null;

    try {
        con = ... // get the connection

        // enable transaction
        con.setAutoCommit(false);

        Statement statement = con.createStatement();

        // for every DDL statement, execute it
        for (String sql : ddl.split(delimiter)) {
            if (StringUtils.isNotBlank(sql)) {
                statement.executeUpdate(sql);
            }
        }

        statement.close();
        con.commit();
    }
    catch (Exception e) {
        e.printStackTrace();
    }
    finally {
        try {
            con.close();
        }
        catch (Exception ignored) {
        }
    }
}