Derby数据库表初始化问题：如何实现表不存在则创建

Hey there! I’ve run into this exact problem when working with embedded Derby for desktop apps, so let me share a straightforward, reliable approach to handle table initialization just like you do with the database itself.

Core Idea #

Derby offers two solid ways to achieve "use existing table or create new" logic—one built-in for newer versions, and a manual check that works for all releases. Let’s break both down:

Method 1: Use Derby’s Built-in IF NOT EXISTS (Derby 10.10+) #

If you’re using Derby 10.10 or later, you can skip manual checks entirely and use native SQL syntax. This is the cleanest option:

try (Connection conn = DriverManager.getConnection("jdbc:derby:wordGameDB;create=true")) {
    String createTableSQL = "CREATE TABLE IF NOT EXISTS USER_RESULTS (" +
            "ID INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY," +
            "USER_NAME VARCHAR(50) NOT NULL," +
            "SCORE INT NOT NULL," +
            "PLAY_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP" +
            ")";
    try (Statement stmt = conn.createStatement()) {
        stmt.executeUpdate(createTableSQL);
        System.out.println("Table ready (either existed or created new)!");
    }
} catch (SQLException e) {
    e.printStackTrace();
}

Just plug in your own table schema, and Derby handles the rest.

Method 2: Manual Table Existence Check (Works for All Derby Versions) #

If you need compatibility with older Derby releases, use DatabaseMetaData to verify the table exists before creating it. Here’s how to implement it:

Step 1: Helper Method to Check Table Existence #

private static boolean isTablePresent(Connection conn, String tableName) throws SQLException {
    // Derby stores table names in uppercase by default, so we match that
    String upperTableName = tableName.toUpperCase();
    DatabaseMetaData metaData = conn.getMetaData();
    
    // Query system tables to locate the target table
    ResultSet resultSet = metaData.getTables(
        null,       // Catalog name (null works for Derby's default setup)
        null,       // Schema name (use null for default schema)
        upperTableName,
        new String[] {"TABLE"}  // Filter to only check tables, not views or system objects
    );
    
    // If the result set has any rows, the table exists
    return resultSet.next();
}

Step 2: Initialize Table in Your Connection Flow #

public static void initGameDatabase() {
    String dbUrl = "jdbc:derby:wordGameDB;create=true";
    String userResultsTable = "USER_RESULTS";
    
    try (Connection conn = DriverManager.getConnection(dbUrl)) {
        if (!isTablePresent(conn, userResultsTable)) {
            // Create the table with your desired schema
            String createSql = "CREATE TABLE " + userResultsTable + " (" +
                    "ID INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY," +
                    "USER_NAME VARCHAR(50) NOT NULL," +
                    "SCORE INT NOT NULL," +
                    "PLAY_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP" +
                    ")";
            try (Statement stmt = conn.createStatement()) {
                stmt.executeUpdate(createSql);
                System.out.println("New table " + userResultsTable + " created successfully!");
            }
        } else {
            System.out.println("Table " + userResultsTable + " already exists, proceeding with existing table.");
        }
        
        // Add your other database setup logic (like indexes) here if needed
    } catch (SQLException e) {
        System.err.println("Error initializing database:");
        e.printStackTrace();
    }
}

Key Tips to Avoid Issues #

• Table Name Case: Derby defaults to uppercase table names, so converting your table name to uppercase in the check prevents case-sensitivity bugs.
• Embedded Derby Path: When running your Eclipse console app, the Derby database folder (wordGameDB) will be created in your project’s root directory (Eclipse’s default working directory for console applications).
• Auto-Commit: Derby’s default connection auto-commit is true, so your table creation will save immediately without needing to call conn.commit().

内容的提问来源于stack exchange，提问作者realworldjoe