how to create table if it doesn't exist using Derby Db

Question

I am new to apache derby and I cant seem to make work

    CREATE TABLE IF NOT EXISTS table1 ...

as can be achieved in MySql etc. I am getting a 'Syntax error: Encountered "NOT" at line 1, column 17.', when I try to run this SQL statement in my Java program.

I checked in the documentation page for Derby Db Create Statements, but couldn't find such an alternative.

Answer 1

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 Table <value> already exists; it's probably X0Y68. The code you need is 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.

Answer 2

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());
    }
  }

Answer 3

the query you are executing does not supported by Derby db. Instead, if you know the name of the table you can find if table exists or not quite easily.

public boolean isTableExist(String sTablename) throws SQLException{
    if(connection!=null)
    {
        DatabaseMetaData dbmd = connection.getMetaData();
        ResultSet rs = dbmd.getTables(null, null, sTablename.toUpperCase(),null);
        if(rs.next())
        {
            System.out.println("Table "+rs.getString("TABLE_NAME")+"already exists !!");
        }
        else
        {
            System.out.println("Write your create table function here !!!");
        }
        return true;
    }
    return false;
}

Catch is to specify name of the table in Uppercase else you won't be able to find table name in metadata.

Answer 4

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");
    }

Answer 5

Following Aaron Digulla's lead with a DerbyUtils class to check if the table exists, this is the solution I came up with :

Calling class

public void createTable(String name) {
    Connection connection = null;
    PreparedStatement preparedStatement = null;

    try {
        connection = daoFactory.getConnection();
        String sql = String.format(SQL_CREATE_TABLE, name); 
        preparedStatement = connection.prepareStatement(sql, Statement.NO_GENERATED_KEYS);
        preparedStatement.execute();
    } catch (SQLException e) {
        if(DerbyUtils.tableAlreadyExists(e)) { //check if the exception is because of pre-existing table.
            logger.info("Talbe " + name + " already exists.  No need to recreate");
        } else {
            logger.error(e.getMessage() + " : " + e.getStackTrace());
        }
    } finally {
        close(connection, preparedStatement);  //DAOUtils silently closes
    }
}

DerbyUtils

public class DerbyUtils {

    public DerbyUtils() {
        //empty constructor -- helper class
    }

    public static boolean tableAlreadyExists(SQLException e) {
        boolean exists;
        if(e.getSQLState().equals("X0Y32")) {
            exists = true;
        } else {
            exists = false;
        }
        return exists;
    }
}

---

See also

• https://db.apache.org/derby/docs/10.2/ref/rrefexcept71493.html

Answer 6

I know this was marked with an answer but in case anyone wanted another way of checking I wanted to post anyway. Here I check the table metadata with a method that returns a boolean, true if exists, false if it doesn't. Hope it helps others if they are looking.

private static Connection conn = null;
private static Statement st = null;
private static ResultSet rs = null;
private static DatabaseMetaData dmd;

public Boolean firstTime()
{
    try
    {
        dmd = conn.getMetaData();
        rs = dmd.getTables(null, "APP", "LOGIN", null);
        return !rs.next();
    } catch (SQLException ex)
    {
        Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        return false;
    }
}

Answer 7

Another solution with 2 conditions:

• Willing to drop table before creating each time, with the same being present in a .sql file

• Are using Spring and hence willing to use spring-test as a Maven dependency, your life can become much simpler with it's @Sql annotation

So, firstly adding this as a dependency to your pom:

           <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-test</artifactId>
                <version>4.2.5.RELEASE</version>
                <scope>test</scope>
            </dependency>

Secondly, assuming you have an sql that drops, creates table a in a file rectangle.sql:

DROP TABLE rectangles;

CREATE TABLE rectangles (
    id      INTEGER NOT NULL PRIMARY KEY,
    width   INTEGER NOT NULL,
    height  INTEGER NOT NULL
);

And you have a test class BlahTest that should run this sql before doing whatever test it is to run, simply add the following @Sql annotation to your class:

import org.springframework.test.context.ContextConfiguration; 
import org.springframework.test.context.jdbc.Sql;
import org.springframework.test.context.jdbc.SqlConfig;
import org.springframework.test.context.jdbc.SqlConfig.ErrorMode;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes=XyzClientConfig.class)
@Sql(scripts="/sql/ddl/rectangle.sql", config=@SqlConfig (errorMode=ErrorMode.IGNORE_FAILED_DROPS))
public class BlahTest { 
...
}

The specified config attribute value's @SqlConfig has the magic that makes it skip the drop statement errors in case the table doesn't exist. I believe it's been written to specifically target these types of databases that don't support IF EXISTS for dropping / table creation (which derby really should, even if it's not part of the SQL standard at the moment)

Answer 8

This answer is way late, but it might be helpful for someone.

The following Java (standard JDBC) code can be used to check whether a table exists or not, and if it does then it can be created;

String query = "SELECT TRUE FROM SYS.SYSTABLES WHERE TABLENAME = ? AND TABLETYPE = 'T'"; // Leave TABLETYPE out if you don't care about it
PreparedStatement ps = connection.prepareStatement(query);
ps.setString(1, "TABLE_NAME"); // Must be in capitals
ResultSet rs = ps.executeQuery();
if ( rs.next() && rs.getBoolean(1) )
{
    // Table exists
}
else
{
    // Table does NOT exist ... create it
}

Answer 9

Here is a solution that will you can script in SQL.

1. Create a Class like the following:

   package user.fenris.spring.extensions;
   
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.SQLException;
   
   import org.apache.commons.logging.Log;
   import org.apache.commons.logging.LogFactory;
   import org.springframework.jdbc.core.JdbcTemplate;
   import org.springframework.jdbc.datasource.SingleConnectionDataSource;
   
   public class SqlCreateIfNotExists {
   private static Log log = LogFactory.getLog(SqlCreateIfNotExists.class);
   
   public static void createTableIfNotExists(String tablename, String ddl) throws SQLException { 
       Connection conn = DriverManager.getConnection("jdbc:default:connection");
   
       if (conn != null) {
           JdbcTemplate template = new JdbcTemplate(new SingleConnectionDataSource(conn, true));
           int count = template.queryForInt("select count(*) from SYS.SYSTABLES where TABLENAME = ?", tablename);
           log.debug("Count: " + count);           
           if (count == 0) {
               log.debug("Executing sql statement: " + ddl);
               template.execute(sql);
           } else {
               log.debug("Table exists.  Skipping sql execution...");              
           }
       }       
   }
   

   }

   Note: you don't have to use spring, you can write it in straight JDBC, but then you have to know how to do it correctly. (Left as an exercise for the reader). Also, you could rewrite this to parse out the table name from the ddl parameter. Another thing would be to do proper error handling.

2. Make sure the class is compiled and placed in the classpath of the VM the database will be running in.

3. Write your SQL script:

   -- 2K for ddl statement should be enough.  You want more?  Seriously?
   create procedure CreateTableIfNotExists(in tablename varchar(128), in ddl varchar(2048))    
   PARAMETER STYLE JAVA
   MODIFIES SQL DATA   
   language java   
   external name 'user.fenris.spring.extensions.SqlCreateIfNotExists.createTableIfNotExists';
   
   call CreateTableIfNotExists('TABLE_NAME_MUST_BE_ALL_CAPS',
       'create table TABLE_NAME_MUST_BE_ALL_CAPS
        (entry_id int generated always as identity not null,
         entry_timestamp timestamp,         
         username varchar(128) not null,        
         note varchar(1024) not null,           
         primary key (entry_id))');
   
   -- you don't have to drop this, but you would have to create a similar 
   -- procedure to create the CreateTableIfNotExists procedure, 
   -- (i.e. CreateProcedureIfNotExists) but then it's turtles all the way down
   
   drop procedure CreateIfNotExists;
   

4. ???

5. profit

Answer 10

try {
            connection.createStatement().execute("create table channels(channel varchar(20),topic varchar(20))");
        } catch (Exception e) {
            // TODO Auto-generated catch block
        //  e.printStackTrace();
        }

Surround the create statement by try-catch.and make sure comment the e.printstacktace(); if it is already exists it does not show error ,otherwise it create table..!!