Java在MySQL数据库中创建表格

Question

首先感谢之前帮助过我的人。

我目前遇到的问题是，可能是由于这行代码引起的：

    statement.executeUpdate(myTableName);

或者使用以下几行代码

    String myTableName = "CREATE TABLE AgentDetail (" 
        + "idNo INT(64) NOT NULL AUTO_INCREMENT,"  
        + "initials VARCHAR(2)," 
        + "agentDate DATE,"  
        + "agentCount INT(64))";  

当代码到达这些点时，会生成一个错误，被SQLException块捕获。

它要么非常简单，要么非常复杂。

能否有人指出这个Java MySQL编程新手犯了什么错误，希望不是错误，提前感谢。

以下是完整的代码：

    public class DbStuff {
    private String jdbcDriver = "com.mysql.jdbc.Driver";
    private String dbAddress = "jdbc:mysql://localhost:3306/";
    private String userPass = "?user=root&password=";
    private String dbName = "TIGER19";
    private String userName = "root";
    private String password = "";

    private PreparedStatement preStatement;
    private Statement statement;
    private ResultSet result;
    private Connection con;

    public DbStuff() {
        try {
            Class.forName(jdbcDriver);
            con = DriverManager.getConnection(dbAddress + dbName, userName, password);
        } 
        catch (ClassNotFoundException e) {
            e.printStackTrace();
        } 
        catch (SQLException e) {
            createDatabase();
            createTableCub1();
        }
    }

    private void createDatabase() {
        try {
            Class.forName(jdbcDriver);
            con = DriverManager.getConnection(dbAddress + userPass);
            Statement s = con.createStatement();
            int myResult = s.executeUpdate("CREATE DATABASE IF NOT EXISTS " + dbName);
        } 
        catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    private void createTableCub1() {
        String myTableName = "CREATE TABLE AgentDetail (" 
            + "idNo INT(64) NOT NULL AUTO_INCREMENT,"  
            + "initials VARCHAR(2)," 
            + "agentDate DATE,"  
            + "agentCount INT(64))";  
        try {
            Class.forName(jdbcDriver);
            con = DriverManager.getConnection(dbAddress + dbName, userName, password);
            statement = con.createStatement();
            //The next line has the issue
            statement.executeUpdate(myTableName);
            System.out.println("Table Created");
        }
        catch (SQLException e ) {
            System.out.println("An error has occurred on Table Creation");
        }
        catch (ClassNotFoundException e) {
            System.out.println("An Mysql drivers were not found");
        }
    }
    }

- K Spriggs

---

在你的catch块中使用e.printStackTrace()。获取堆栈跟踪并添加到你的帖子中。 - Smit

请尝试这样做，我猜想您在数据库创建和表格创建之间漏掉了 Connection.setCatalog(dbName);。但需要实际的错误信息来确认。 - rsanchez

只是为了让事情更清晰，并补充Smit的评论，在你的catch块中添加System.out.println(e.getMessage())（和e.printStackTrace()）以获取错误消息和发生错误的位置。 - Barranka

Answer 1

首先感谢您的帮助和建议。这确实非常有帮助。

由此，我已经学会了三件事：

• 如何编写创建表格的正确指令。
• 如何在Java中创建MySQL表格。
• Smit所说的一些话，即阅读e.printStackTrace()信息。

以下是我编写的代码。

private void createTableCub1() {
    String myTableName = "CREATE TABLE AgentDetail (" 
            + "idNo INT(64) NOT NULL AUTO_INCREMENT,"  
            + "initials VARCHAR(2)," 
            + "agentDate DATE,"  
            + "agentCount INT(64), "
            + "PRIMARY KEY(idNo))";  
    try {
        Class.forName(jdbcDriver);
        con = DriverManager.getConnection(dbAddress + dbName, userName, password);
        statement = con.createStatement();
        //This line has the issue
        statement.executeUpdate(myTableName);
        System.out.println("Table Created");
    }
    catch (SQLException e ) {
        System.out.println("An error has occured on Table Creation");
        e.printStackTrace();
    }
    catch (ClassNotFoundException e) {
        System.out.println("An Mysql drivers were not found");
    }
}

当然，我非常欢迎并感激任何反馈意见。

- K Spriggs

Answer 2

您的表创建SQL语句不正确。在mysql中设置列自增，必须将其设置为主键。

CREATE TABLE AgentDetail ( 
        idNo INT(64) NOT NULL AUTO_INCREMENT, 
        initials VARCHAR(2),
        agentDate DATE,  
        agentCount INT(64),PRIMARY KEY (`idNo`));

- Shamim Ahmmed

---

这可能是唯一的原因。 - Smit