Creating a "Java DB" database and associated tables in main checking to see if they exist?

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 开发者_Go百科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.

---

I don't know how much Oracle changed Derby before rebranding it, but if they didn't change too much then you might be helped by Delete all tables in Derby DB. The answers to that question list several ways to check what tables exist within a database.

You will specify the database when you create your DB connection; otherwise the connection will not be created successfully. (The exact syntax of this is up to how you are connecting to your db, but the logic of it is the same as in shree's answer.)

---

The create=true property will create a new database if it is not exists. You may use DatabaseMetadata.getTables() method to check the existence of Tables.

Connection cn=DriverManager.getConnection("jdbc:derby://localhost:1527/testdb3;create=true", "testdb3", "testdb3");

ResultSet mrs=cn.getMetaData().getTables(null, null, null, new String[]{"TABLE"});

while(mrs.next())
 {
  if(!"EMP".equals(mrs.getString("TABLE_NAME")))
   {
    Statement st=cn.createStatement(); 
    st.executeUpdate("create table emp (eno int primary key, ename varchar(30))");
    st.close();;
   }
}
mrs.close();
cn.close();

---

Connection conn = getMySqlConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();

    String tableName = ur table name ;
    String query = ur query;
    Statement stmt = null;
    ResultSet rs = null;
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      System.out.println("Exist");;
    }
    catch (Exception e ) {
      // table does not exist or some other problem
      //e.printStackTrace();
      System.out.println("Not Exist");
    }

    st.close();
    conn.close();