删除 Derby DB 中的所有表

Question

如何使用 JDBC 删除 Apache Derby DB 上架构中的所有表？

Answer 1

对于执行此操作的实际代码，请检查 CleanDatabaseTestSetup.java 位于 Derby 发行版的 Derby 测试套件部分。

Answer 2

在java中执行一个小方法，在其中执行

DROP TABLE [tablename]

通过参数传递的tablename。

查询形成的记录集

SELECT tablename FROM SYSTABLES

另一种方法是循环遍历由调用第一个方法的

。 Derby 最新文档

Answer 3

我认为大多数数据库提供商不允许 DROP TABLE * （或类似的）。

我认为最好的方法是显示表格，然后通过结果集循环执行每个删除。

HTH。

Answer 4

JDBC 允许您以与数据库无关的方式解决您的任务：

1. 打开连接
2. 获取 DatabaseMetaData
3. 使用它列出数据库中的所有表 JavaDoc
4. 迭代结果集并为每个表触发 DROP TABLE

Answer 5

1. 您必须从 Derby DB 系统目录生成架构和表名称。
2. 按关系对所有表进行排序。
3. 生成用于删除所有表的 java 语句
4. 使用 autoCommit() 方法并将该方法设置为 false。 用于在出现错误时手动提交或回滚事务。
5. 运行你的java进程。
   祝你好运。

Answer 6

一个更简单的解决方案是使用 JDBC 运行“删除数据库 foo”，然后运行“创建数据库 foo”。 但是，这将导致数据库中的所有对象被删除（即不仅仅是表）。

Answer 7

如果您从命令提示符而不是通过 JDBC 工作，这应该可以帮助您入门。

SELECT 'DROP TABLE ' || schemaname ||'.' || tablename || ';'
FROM SYS.SYSTABLES
INNER JOIN SYS.SYSSCHEMAS ON SYS.SYSTABLES.SCHEMAID = SYS.SYSSCHEMAS.SCHEMAID
;

Answer 8

一个简单的解决方案是右键单击 -> 断开连接，然后删除包含数据库的文件夹并重新连接。

Answer 9

从 http://squirrel-sql.sourceforge.net/ 下载 Squirrel SQL

连接到数据库。

展开表节点。

选择要删除的表。

右键单击并选择-> 脚本-> 删除表脚本

运行生成的查询

您甚至可以选择删除记录来清空选定的表。

Answer 10

对于那些希望以编程方式删除所有架构而不必每次手动复制粘贴 SQL 的人，这里的代码来自 org.apache.derbyTesting.junit.CleanDatabaseTestSetup 和 org.apache.derbyTesting.junit .JDBC。 您只需调用 dropAllSchemas(connection);

public static void dropAllSchemas(Connection conn) throws SQLException {
    DatabaseMetaData dmd = conn.getMetaData();
    SQLException sqle = null;
    // Loop a number of arbitary times to catch cases
    // where objects are dependent on objects in
    // different schemas.
    for (int count = 0; count < 5; count++) {
        // Fetch all the user schemas into a list
        List<String> schemas = new ArrayList<String>();
        ResultSet rs = dmd.getSchemas();
        while (rs.next()) {
            String schema = rs.getString("TABLE_SCHEM");
            if (schema.startsWith("SYS"))
                continue;
            if (schema.equals("SQLJ"))
                continue;
            if (schema.equals("NULLID"))
                continue;
            schemas.add(schema);
        }
        rs.close();
        // DROP all the user schemas.
        sqle = null;
        for (String schema : schemas) {
            try {
                dropSchema(dmd, schema);
            } catch (SQLException e) {
                sqle = e;
            }
        }
        // No errors means all the schemas we wanted to
        // drop were dropped, so nothing more to do.
        if (sqle == null)
            return;
    }
    throw sqle;
}


/**
 * Constant to pass to DatabaseMetaData.getTables() to fetch
 * just tables.
 */
public static final String[] GET_TABLES_TABLE = new String[] {"TABLE"};
/**
 * Constant to pass to DatabaseMetaData.getTables() to fetch
 * just views.
 */
public static final String[] GET_TABLES_VIEW = new String[] {"VIEW"};
/**
 * Constant to pass to DatabaseMetaData.getTables() to fetch
 * just synonyms.
 */
public static final String[] GET_TABLES_SYNONYM =
    new String[] {"SYNONYM"};
/**
 * Drop a database schema by dropping all objects in it
 * and then executing DROP SCHEMA. If the schema is
 * APP it is cleaned but DROP SCHEMA is not executed.
 * 
 * TODO: Handle dependencies by looping in some intelligent
 * way until everything can be dropped.
 * 

 * 
 * @param dmd DatabaseMetaData object for database
 * @param schema Name of the schema
 * @throws SQLException database error
 */
public static void dropSchema(DatabaseMetaData dmd, String schema) throws SQLException{     
    Connection conn = dmd.getConnection();
    Statement s = dmd.getConnection().createStatement();

    // Triggers
    PreparedStatement pstr = conn.prepareStatement(
            "SELECT TRIGGERNAME FROM SYS.SYSSCHEMAS S, SYS.SYSTRIGGERS T "
            + "WHERE S.SCHEMAID = T.SCHEMAID AND SCHEMANAME = ?");
    pstr.setString(1, schema);
    ResultSet trrs = pstr.executeQuery();
    while (trrs.next()) {
        String trigger = trrs.getString(1);
        s.execute("DROP TRIGGER " + escape(schema, trigger));
    }
    trrs.close();
    pstr.close();

    // Functions - not supported by JDBC meta data until JDBC 4
    // Need to use the CHAR() function on A.ALIASTYPE
    // so that the compare will work in any schema.
    PreparedStatement psf = conn.prepareStatement(
            "SELECT ALIAS FROM SYS.SYSALIASES A, SYS.SYSSCHEMAS S" +
            " WHERE A.SCHEMAID = S.SCHEMAID " +
            " AND CHAR(A.ALIASTYPE) = ? " +
            " AND S.SCHEMANAME = ?");
    psf.setString(1, "F" );
    psf.setString(2, schema);
    ResultSet rs = psf.executeQuery();
    dropUsingDMD(s, rs, schema, "ALIAS", "FUNCTION");        

    // Procedures
    rs = dmd.getProcedures((String) null,
            schema, (String) null);
    
    dropUsingDMD(s, rs, schema, "PROCEDURE_NAME", "PROCEDURE");
    
    // Views
    rs = dmd.getTables((String) null, schema, (String) null,
            GET_TABLES_VIEW);
    
    dropUsingDMD(s, rs, schema, "TABLE_NAME", "VIEW");
    
    // Tables
    rs = dmd.getTables((String) null, schema, (String) null,
            GET_TABLES_TABLE);
    
    dropUsingDMD(s, rs, schema, "TABLE_NAME", "TABLE");
    
    // At this point there may be tables left due to
    // foreign key constraints leading to a dependency loop.
    // Drop any constraints that remain and then drop the tables.
    // If there are no tables then this should be a quick no-op.
    ResultSet table_rs = dmd.getTables((String) null, schema, (String) null,
            GET_TABLES_TABLE);

    while (table_rs.next()) {
        String tablename = table_rs.getString("TABLE_NAME");
        rs = dmd.getExportedKeys((String) null, schema, tablename);
        while (rs.next()) {
            short keyPosition = rs.getShort("KEY_SEQ");
            if (keyPosition != 1)
                continue;
            String fkName = rs.getString("FK_NAME");
            // No name, probably can't happen but couldn't drop it anyway.
            if (fkName == null)
                continue;
            String fkSchema = rs.getString("FKTABLE_SCHEM");
            String fkTable = rs.getString("FKTABLE_NAME");

            String ddl = "ALTER TABLE " +
                escape(fkSchema, fkTable) +
                " DROP FOREIGN KEY " +
                escape(fkName);
            s.executeUpdate(ddl);
        }
        rs.close();
    }
    table_rs.close();
    conn.commit();
            
    // Tables (again)
    rs = dmd.getTables((String) null, schema, (String) null,
            GET_TABLES_TABLE);        
    dropUsingDMD(s, rs, schema, "TABLE_NAME", "TABLE");

    // drop UDTs
    psf.setString(1, "A" );
    psf.setString(2, schema);
    rs = psf.executeQuery();
    dropUsingDMD(s, rs, schema, "ALIAS", "TYPE");        

    // drop aggregates
    psf.setString(1, "G" );
    psf.setString(2, schema);
    rs = psf.executeQuery();
    dropUsingDMD(s, rs, schema, "ALIAS", "DERBY AGGREGATE");        
    psf.close();

    // Synonyms - need work around for DERBY-1790 where
    // passing a table type of SYNONYM fails.
    rs = dmd.getTables((String) null, schema, (String) null,
            GET_TABLES_SYNONYM);
    
    dropUsingDMD(s, rs, schema, "TABLE_NAME", "SYNONYM");
            
    // sequences
    if ( sysSequencesExists( conn ) )
    {
        psf = conn.prepareStatement
            (
             "SELECT SEQUENCENAME FROM SYS.SYSSEQUENCES A, SYS.SYSSCHEMAS S" +
             " WHERE A.SCHEMAID = S.SCHEMAID " +
             " AND S.SCHEMANAME = ?");
        psf.setString(1, schema);
        rs = psf.executeQuery();
        dropUsingDMD(s, rs, schema, "SEQUENCENAME", "SEQUENCE");
        psf.close();
    }

    // Finally drop the schema if it is not APP
    if (!schema.equals("APP")) {
        s.executeUpdate("DROP SCHEMA " + escape(schema) + " RESTRICT");
    }
    conn.commit();
    s.close();
}

    /**
 * Return true if the SYSSEQUENCES table exists.
 */
private static boolean sysSequencesExists( Connection conn ) throws SQLException
{
    PreparedStatement ps = null;
    ResultSet rs =  null;
    try {
        ps = conn.prepareStatement
            (
             "select count(*) from sys.systables t, sys.sysschemas s\n" +
             "where t.schemaid = s.schemaid\n" +
             "and ( cast(s.schemaname as varchar(128)))= 'SYS'\n" +
             "and ( cast(t.tablename as varchar(128))) = 'SYSSEQUENCES'" );
        rs = ps.executeQuery();
        rs.next();
        return ( rs.getInt( 1 ) > 0 );
    }
    finally
    {
        if ( rs != null ) { rs.close(); }
        if ( ps != null ) { ps.close(); }
    }
}

/**
 * Escape a non-qualified name so that it is suitable
 * for use in a SQL query executed by JDBC.
 */
public static String escape(String name)
{
    StringBuffer buffer = new StringBuffer(name.length() + 2);
    buffer.append('"');
    for (int i = 0; i < name.length(); i++) {
        char c = name.charAt(i);
        // escape double quote characters with an extra double quote
        if (c == '"') buffer.append('"');
        buffer.append(c);
    }
    buffer.append('"');
    return buffer.toString();
}   

/**
 * Escape a schema-qualified name so that it is suitable
 * for use in a SQL query executed by JDBC.
 */
public static String escape(String schema, String name)
{
    return escape(schema) + "." + escape(name);
}


/**
 * DROP a set of objects based upon a ResultSet from a
 * DatabaseMetaData call.
 * 
 * TODO: Handle errors to ensure all objects are dropped,
 * probably requires interaction with its caller.
 * 
 * @param s Statement object used to execute the DROP commands.
 * @param rs DatabaseMetaData ResultSet
 * @param schema Schema the objects are contained in
 * @param mdColumn The column name used to extract the object's
 * name from rs
 * @param dropType The keyword to use after DROP in the SQL statement
 * @throws SQLException database errors.
 */
private static void dropUsingDMD(
        Statement s, ResultSet rs, String schema,
        String mdColumn,
        String dropType) throws SQLException
{
    String dropLeadIn = "DROP " + dropType + " ";
    
    // First collect the set of DROP SQL statements.
    ArrayList<String> ddl = new ArrayList<String>();
    while (rs.next())
    {
        String objectName = rs.getString(mdColumn);
        String raw = dropLeadIn + escape(schema, objectName);
        if (
            "TYPE".equals( dropType )  ||
            "SEQUENCE".equals( dropType ) ||
            "DERBY AGGREGATE".equals( dropType )
            )
        { raw = raw + " restrict "; }
        ddl.add( raw );
    }
    rs.close();
    if (ddl.isEmpty())
        return;
            
    // Execute them as a complete batch, hoping they will all succeed.
    s.clearBatch();
    int batchCount = 0;
    for (Iterator i = ddl.iterator(); i.hasNext(); )
    {
        Object sql = i.next();
        if (sql != null) {
            s.addBatch(sql.toString());
            batchCount++;
        }
    }

    int[] results;
    boolean hadError;
    try {
        results = s.executeBatch();
        //Assert.assertNotNull(results);
        //Assert.assertEquals("Incorrect result length from executeBatch", batchCount, results.length);
        hadError = false;
    } catch (BatchUpdateException batchException) {
        results = batchException.getUpdateCounts();
        //Assert.assertNotNull(results);
        //Assert.assertTrue("Too many results in BatchUpdateException", results.length <= batchCount);
        hadError = true;
    }
    
    // Remove any statements from the list that succeeded.
    boolean didDrop = false;
    for (int i = 0; i < results.length; i++)
    {
        int result = results[i];
        if (result == Statement.EXECUTE_FAILED)
            hadError = true;
        else if (result == Statement.SUCCESS_NO_INFO || result >= 0) {
            didDrop = true;
            ddl.set(i, null);
        }
        //else
            //Assert.fail("Negative executeBatch status");
    }
    s.clearBatch();
    if (didDrop) {
        // Commit any work we did do.
        s.getConnection().commit();
    }

    // If we had failures drop them as individual statements
    // until there are none left or none succeed. We need to
    // do this because the batch processing stops at the first
    // error. This copes with the simple case where there
    // are objects of the same type that depend on each other
    // and a different drop order will allow all or most
    // to be dropped.
    if (hadError) {
        do {
            hadError = false;
            didDrop = false;
            for (ListIterator<String> i = ddl.listIterator(); i.hasNext();) {
                String sql = i.next();
                if (sql != null) {
                    try {
                        s.executeUpdate(sql);
                        i.set(null);
                        didDrop = true;
                    } catch (SQLException e) {
                        hadError = true;
                    }
                }
            }
            if (didDrop)
                s.getConnection().commit();
        } while (hadError && didDrop);
    }
}

PS：当我将数据库从支持 DROP ALL OBJECTS 的 H2 迁移到 Apache Derby 时，此代码派上了用场，其中 不（头痛）。 我从 H2 迁移出来的唯一原因是它是一个完全内存数据库，对于我的服务器 RAM 来说太大了，所以我决定尝试 Apache Derby。 H2 比 Derby 更容易、更用户友好，我强烈推荐它。 我很遗憾我买不起 RAM 来继续使用 H2。
顺便说一句，对于那些受 Derby 缺乏 LIMIT 或 UPSERT 影响的人，请参阅这篇关于替换 FETCH NEXT 而不是 LIMIT 这一个关于正确使用 合并到。

Answer 11

感谢博客：

步骤1：

运行 SQL 语句，但不要忘记将下面 2 处出现的 schema 名称“APP”替换为您的 schema 名称：

SELECT
'ALTER TABLE '||S.SCHEMANAME||'.'||T.TABLENAME||' DROP CONSTRAINT '||C.CONSTRAINTNAME||';'
FROM
    SYS.SYSCONSTRAINTS C,
    SYS.SYSSCHEMAS S,
    SYS.SYSTABLES T
WHERE
    C.SCHEMAID = S.SCHEMAID
AND
    C.TABLEID = T.TABLEID
AND
S.SCHEMANAME = 'APP'
UNION
SELECT 'DROP TABLE ' || schemaname ||'.' || tablename || ';'
FROM SYS.SYSTABLES
INNER JOIN SYS.SYSSCHEMAS ON SYS.SYSTABLES.SCHEMAID = SYS.SYSSCHEMAS.SCHEMAID
where schemaname='APP';

步骤 2：

上面执行的结果是一组 SQL 语句，将它们复制到SQL 编辑器，执行它们，然后约束和表被删除。