时间戳

首页 > 解决方案 > 我的代码的语法错误是什么？sql java

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);
        }
    }
}

columnSpec 的打印结果：

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

打印出表名：

nameoftable

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

标签： javamysqlsql

解决方案

代替

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();.

推荐阅读