java - 我的代码的语法错误是什么？ SQL Java

我一直在盯着我的代码，试图找出正在发生的语法错误：


  错误：表创建被java.sql.SQLSyntaxErrorException中断：您的SQL语法有错误。检查与您的MySQL服务器版本相对应的手册，以在第1行的''附近使用正确的语法

public static void main(String[] args) throws Exception {
    Scanner kbd = new Scanner(System.in);
    getConnection();

    boolean quit = false;
    while (!quit) {
        System.out.println("Select from the following options: \n" + "1) Create Table \n" + "2) Search Table \n"
                + "3) Quit \n" + "> ");
        int choice = kbd.nextInt();
        if (choice == 1) {

            System.out.println("Name your table: ");
            String tableName = kbd.nextLine();

            kbd.nextLine();

            System.out.println("How many columns would you like? ");
            int numColumns = kbd.nextInt();
            String[] columnTypes = new String[numColumns];
            String columnSpec = "";
            kbd.nextLine();
            String[] types = { "VARCHAR(100)", "INT", "decimal(3,2)" };
            int typeChoice;
            for (int i = 0; i < numColumns; i++) {
                System.out.printf(
                        "Select the type for column %d: \n" + "1)VarChar\n" + "2)Integer\n" + "3)Decimal\n > ",
                        i + 1);

                typeChoice = kbd.nextInt();

                kbd.nextLine();

                System.out.printf("Enter Column %d's name: ", i + 1);
                String columnsName = kbd.nextLine();

                if (typeChoice == 1) {
                    columnSpec += columnCreator(columnsName, types[0]);
                    columnTypes[i] = types[0];
                } else if (typeChoice == 2) {
                    columnSpec += columnCreator(columnsName, types[1]);
                    columnTypes[i] = types[1];
                } else {
                    columnSpec += columnCreator(columnsName, types[2]);
                    columnTypes[i] = types[2];
                }

            }
            columnSpec = columnSpec.substring(0, columnSpec.length() - 2);

            System.out.println(columnSpec);
            Trans.create(tableName, columnSpec);
            quit = true;
        }
    }
}

public static Connection getConnection() throws Exception {
    try {
        String driver = "com.mysql.jdbc.Driver";
        System.out.println("\n=> loading driver:");
        Class.forName(driver).newInstance();
        System.out.println("OK");
        String url = "jdbc:mysql://localhost/Walkthrough?useSSL=false";

        System.out.println("\n=> connecting:");
        DriverManager.getConnection(url, Trans.user, Trans.password);
        System.out.println("OK");
    } catch (Exception x) {
        System.err.println(x);
    }
    return null;
}

public static String columnCreator(String name, String type) {
    return name + " " + type + ", ";
}

跨班：

public class Trans {
    static String url = "jdbc:mysql://localhost/Walkthrough?useSSL=false";
    static String user = "root";
    static String password = "password";

    public static void create(String table, String values) {
        // need to ensure not existing.
        // values = "id VARCHAR(6), quiz INT, avg decimal(3,2)";
        try {
            Class.forName("com.mysql.jdbc.Driver");

            Connection cx = DriverManager.getConnection(url, user, password);
            Statement st = cx.createStatement();
            String sql_drop = "DROP TABLE IF EXISTS " + table;
            st.executeUpdate(sql_drop);

            String sql_create = "CREATE TABLE " + table + '(' + values + ')';

            st.executeUpdate(sql_create);
            System.out.println("Table has been created");
        } catch (Exception x) {
            System.err.println("table creation is interrupted by " + x);
        }
    }
}

列规格的打印输出：

col1 VARCHAR(100), col2 VARCHAR(100), col3 VARCHAR(100)

打印出tableName：

nameoftable

任何见解或帮助将不胜感激！

最佳答案

更换

String sql_create = "CREATE TABLE " + table + '(' + values + ')';

与

String sql_create = "CREATE TABLE " + table + " (" + values + ")";

我建议您在调用sql_create之前调试/打印st.executeUpdate(sql_create);的值。它应该像这样打印：

CREATE TABLE nameoftable (col1 VARCHAR(100), col2 VARCHAR(100), col3 VARCHAR(100))

更新：我还看到两个nextLine()调用，一个接一个。

System.out.println("Name your table: ");
String tableName = kbd.nextLine();
kbd.nextLine();

为什么需要第二通电话？删除第二行kbd.nextLine();。

不仅如此，为什么在kbd.nextLine();之后又需要String columnSpec = "";？

String columnSpec = "";
kbd.nextLine();

以及kbd.nextLine();之后的以下typeChoice = kbd.nextInt();？

typeChoice = kbd.nextInt();
kbd.nextLine();

删除所有不必要的kbd.nextLine();调用。

关于java - 我的代码的语法错误是什么？ SQL Java，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/59149285/