Java连接MySQL无法创建users表并插入初始数据求助

我正在开发一个集成MySQL的Java系统，能够正常连接指定的inventory_system数据库，但无法创建users表并插入初始数据，反复遇到报错：Table 'inventory_system.users' doesn't exist。

相关代码 #

package lngtech.database;

import java.lang.reflect.InvocationTargetException;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.*;
import java.util.Base64;
import java.util.UUID;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

public final class DatabaseHandler {
    private static DatabaseHandler handler;
    
    private final String DB_Driver = "com.mysql.cj.jdbc.Driver";
    private final String DB_URL = "jdbc:mysql://localhost:3306/inventory_system?zeroDateTimeBehavior=CONVERT_TO_NULL";
    private static Connection conn = null;
    private static Statement stmt = null;
    private static PreparedStatement pst = null;
    private static final Logger logger = Logger.getLogger(DatabaseHandler.class.getName());
    
    public DatabaseHandler() {
        createConnection();
        setupTables();
        populateUsersTable();
    }
    
    public static synchronized DatabaseHandler getInstance() {
        if (handler == null) {
            handler = new DatabaseHandler();
        }
        
        return handler;
    }
    
    private void createConnection() {
        try {
            Class.forName(DB_Driver).getDeclaredConstructor().newInstance();
            conn = DriverManager.getConnection(DB_URL, "root", "myDbPassword");
        } catch (ClassNotFoundException | IllegalAccessException | IllegalArgumentException | InstantiationException | NoSuchMethodException | SecurityException | InvocationTargetException | SQLException e) {
            logger.log(Level.SEVERE, "Failed to create database connection", e);
        }
    }
    
    public void closeConnection() {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                logger.log(Level.SEVERE, "Error closing connection", e);
            }
        }
    }
    
    private void setupTables() {
        setupUsersTable();
    }
    
    void setupTable(String tableName, String createStatement) {
        try {
            stmt = conn.createStatement();
            DatabaseMetaData dbm = conn.getMetaData();
            ResultSet tables = dbm.getTables(null, null, tableName, null);

            if (!tables.next()) {
                stmt.execute(createStatement);
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage() + " ... setupDatabase");
        }
    }
    
    void setupUsersTable() {
        String createTable = """
                             CREATE TABLE IF NOT EXISTS users (
                             id VARCHAR(255) PRIMARY KEY NOT NULL,
                             firstName VARCHAR(255) NOT NULL,
                             lastName VARCHAR(255) NOT NULL,
                             email VARCHAR(255) UNIQUE NOT NULL,
                             username VARCHAR(255) UNIQUE NOT NULL,
                             password VARCHAR(255) NOT NULL,
                             isActive BOOLEAN DEFAULT TRUE NOT NULL,
                             created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
                             updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
                             deleted_at TIMESTAMP NULL DEFAULT NULL
                             )
                             """;
        
        setupTable("users", createTable);
    }
    
    private void populateUsersTable() {
        String userId = UUID.randomUUID().toString();
        String firstName = "John Smith";
        String lastName = "Doe";
        String email = "sample@gmail.com";
        String username = "lngtech";
        String password = hashPassword("S@mpl3P@s$w0rD");
        
        String insertData = "INSERT INTO users (id, firstName, lastName, email, username, password) VALUES ('" + userId + "', '" + firstName + "', '" + lastName + "', '"  + email + "', '"  + username + "', '"  + password + "')";
        execAction(insertData);
    }
    
    private String hashPassword(String password) {
        try {
            MessageDigest digest = MessageDigest.getInstance("SHA-256");
            byte[] hashedBytes = digest.digest(password.getBytes());
            return Base64.getEncoder().encodeToString(hashedBytes);
        } catch (NoSuchAlgorithmException e) {
            logger.log(Level.SEVERE, "Error hashing password", e);
            return null;
        }
    }
    
    public ResultSet execQuery(String query) {
        try {
            stmt = conn.createStatement();
            return stmt.executeQuery(query);
        }
        catch (SQLException e) {
            logger.log(Level.SEVERE, "Error executing query: " + query, e);
            return null;
        }
    }
    
    public boolean execAction(String qu) {
        try {
            stmt = conn.createStatement();
            stmt.execute(qu);
            return true;
        }
        catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Error: " + e.getMessage(), "Error occured", JOptionPane.ERROR_MESSAGE);
            logger.log(Level.SEVERE, "Error executing action: " + qu, e);
            return false;
        }
    }
    
    public boolean execUpdate(String qu, Object... params) {
        try {
            pst = conn.prepareStatement(qu);
            
            for (int i = 0; i < params.length; i++) {
                pst.setObject(i + 1, params[i]);
            }
            pst.executeUpdate();
            return true;
        }
        catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Error: " + e.getMessage(), "Error occured", JOptionPane.ERROR_MESSAGE);
            logger.log(Level.SEVERE, "Error executing update: " + qu, e);
            return false;
        }
    }
}

错误日志 #

run:
Mar 14, 2025 9:16:43 PM lngtech.database.DatabaseHandler execAction
SEVERE: Error executing action: INSERT INTO users (id, firstName, lastName, email, username, password) VALUES ('ad0186e4-bfdf-4710-b437-8c1c42d9fde5', 'John Smith', 'Doe', 'sample@gmail.com', 'lngtech', 'qrAHYe/qRPJW5pN8YXSxBI9mmlsiWDbhwUtVT9xcxts=')
java.sql.SQLSyntaxErrorException: Table 'inventory_system.users' doesn't exist
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:112)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:114)
    at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:837)
    at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:685)
    at lngtech.database.DatabaseHandler.execAction(DatabaseHandler.java:133)
    at lngtech.database.DatabaseHandler.populateUsersTable(DatabaseHandler.java:105)
    at lngtech.database.DatabaseHandler.<init>(DatabaseHandler.java:29)
    at lngtech.database.DatabaseHandler.getInstance(DatabaseHandler.java:34)
    at lngtech.app.SplashScreen$DatabaseConnectionThread.run(SplashScreen.java:41)

BUILD SUCCESSFUL (total time: 1 minute 4 seconds)

解决方法 #

1. 修正表名大小写匹配问题 #

MySQL在Linux环境下默认区分表名大小写，DatabaseMetaData.getTables方法查询表名时需要传入大写形式。修改setupTable方法中的表名参数：

ResultSet tables = dbm.getTables(null, null, tableName.toUpperCase(), null);

或者直接将调用时的表名改为"USERS"，确保和查询逻辑匹配。

2. 简化建表逻辑，移除冗余检查 #

你的建表语句已经包含CREATE TABLE IF NOT EXISTS，可以直接执行建表语句，跳过表存在性判断，避免匹配错误导致建表失败：

void setupTable(String createStatement) {
    try {
        stmt = conn.createStatement();
        stmt.execute(createStatement);
    } catch (SQLException e) {
        logger.log(Level.SEVERE, "Failed to create table", e);
    }
}

调用时直接传建表字符串即可：setupTable(createTable);

3. 确认数据库连接有效性 #

在createConnection方法末尾添加日志，验证连接是否成功初始化：

if (conn != null) {
    logger.info("Database connection established successfully");
} else {
    logger.severe("Failed to establish database connection");
}

如果连接失败，后续所有数据库操作都会报错。

4. 修复SQL注入风险（优化） #

populateUsersTable使用字符串拼接生成SQL存在注入风险，改用预编译语句：

private void populateUsersTable() {
    String userId = UUID.randomUUID().toString();
    String firstName = "John Smith";
    String lastName = "Doe";
    String email = "sample@gmail.com";
    String username = "lngtech";
    String password = hashPassword("S@mpl3P@s$w0rD");
    
    String insertData = "INSERT INTO users (id, firstName, lastName, email, username, password) VALUES (?, ?, ?, ?, ?, ?)";
    execUpdate(insertData, userId, firstName, lastName, email, username, password);
}

5. 检查MySQL用户权限 #

确保root用户拥有inventory_system数据库的建表权限，执行以下SQL：

GRANT ALL PRIVILEGES ON inventory_system.* TO 'root'@'localhost';
FLUSH PRIVILEGES;

内容的提问来源于stack exchange，提问作者Lourd Nathaniel Gonzalez