Creating a & ldquo; Java DB & rdquo; the database and associated tables in the main audit to see if they exist?

advertisements

I'm creating an applicaation on Netbeans 7! I'd like my application to have a little code in main so that it can create a Java DB connection checking to see if the database and the associate tables exist, if not create the database and the tables in it. If you could provide a sample code, it'd be just as great! I have already looked at http://java.sun.com/developer/technicalArticles/J2SE/Desktop/javadb/ but I'm still not sure how to check for an existing database before creating it!


I'd like my application to have a little code in main so that it can create a Java DB connection checking to see if the database and the associate tables exist, if not create the database and the tables in it.

You can add the create=true property, in the JDBC URL. This creates a Derby database instance if the database specified by the databaseName does not exist at the time of connection. A warning is issued if the database already exists, but as far as I know, no SQLException will be thrown.

As far as creation of the tables is concerned, this is best done on application startup before you access the database for typical transactional activity. You will need to query the SYSTABLES system table in Derby/JavaDB to ascertain whether your tables exist.

Connection conn;
try
{
    String[] tableNames = {"tableA", "tableB"};
    String[] createTableStmts = ... // read the CREATE TABLE SQL statements from a file into this String array. First statement is for the tableA, and so on.
    conn = DriverManager.getConnection("jdbc:derby:sampleDB;create=true");
    for(int ctr =0 ; ctr < tableNames.length; ctr++)
    {
        PreparedStatement pStmt = conn.prepareStatement("SELECT t.tablename FROM sys.systables t WHERE t.tablename = ?");
        pStmt.setString(1, tableNames[ctr]);
        ResultSet rs = pStmt.executeQuery();
        if(!rs.next())
        {
            // Create the table
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(createTableStmts[ctr]);
            stmt.close();
        }
        rs.close();
        pStmt.close();
    }
}
catch (SQLException e)
{
    throw new RuntimeException("Problem starting the app...", e);
}

Any non-existent tables may then be created. This is of course, not a good practice, if your application has multiple versions, and the schema varies from one version of the application to another. If you must handle such a scenario, you should store the version of the application in a distinct table (that will usually not change across versions), and then apply database delta scripts specific to the newer version, to migrate your database from the older version. Using database change management tools like DbDeploy or LiquiBase is recommended. Under the hood, the tools perform the same operation by storing the version number of the application in a table, and execute delta scripts having versions greater than the one in the database.

On a final note, there is no significant difference between JavaDB and Apache Derby.