how to create table if it doesn't exist using Derby Db
Create the table, catch the SQLException
and check SQL status code.
The full list of error codes can be found here but I couldn't find The code you need is Table <value> already exists
; it's probably X0Y68
.X0Y32
.
Just run the code once and print the error code. Don't forget to add a test to make sure the code works; this way, you can catch changes in the error code (should not happen ...).
In my projects, I usually add a helper class with static methods so I can write:
} catch( SQLException e ) {
if( DerbyHelper.tableAlreadyExists( e ) ) {
return; // That's OK
}
throw e;
}
Another option is to run a SELECT
against the table and check the status code (which should be 42X05
). But that's a second command you need to send and it doesn't offer any additional information.
What's worse, it can fail for other reasons than "Table doesn't exist", so the "create-and-ignore-error" is better IMO.
Derby does not support that sql-statement.
In my program I parse all the Tables from the Database into a Set and check if the table exists there.
Like this:
private Set<String> getDBTables(Connection targetDBConn) throws SQLException
{
Set<String> set = new HashSet<String>();
DatabaseMetaData dbmeta = targetDBConn.getMetaData();
readDBTable(set, dbmeta, "TABLE", null);
readDBTable(set, dbmeta, "VIEW", null);
return set;
}
private void readDBTable(Set<String> set, DatabaseMetaData dbmeta, String searchCriteria, String schema)
throws SQLException
{
ResultSet rs = dbmeta.getTables(null, schema, null, new String[]
{ searchCriteria });
while (rs.next())
{
set.add(rs.getString("TABLE_NAME").toLowerCase());
}
}
to check if table is exist :
Connection con = DriverManager.getConnection(url);
ResultSet res = con.getMetaData().getTables(null, Schema_Name, table_name.toUpperCase(), null);//Default schema name is "APP"
if(res.next())
{
//do some thing;
}else{
JOptionPane.showMessageDialog(null, table_name +" not exist");
}
to show all tables name :
Connection con = DriverManager.getConnection(url);
ResultSet res = con.getMetaData().getTables(null, Schema_Name, "%", null);//Default schema name is "APP"
while(res.next())
{
JOptionPane.showMessageDialog(null, res.getString(3) + " is exist");//Show table name
}else{
JOptionPane.showMessageDialog(null, table_name +" not exist");
}