What is the error in syntax for my code? sql java

Question

I've been staring at my code forever trying to figure out the syntax error that's happening:

ERROR: table creation is interrupted by java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 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 + ", ";
}

Trans Class:

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

The print out of columnSpec:

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

Print out of tableName:

nameoftable

Any insight or help would be be greatly appreciated!

Answer 1

Replace

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

with

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

I suggest you debug/print the value of sql_create before calling st.executeUpdate(sql_create);. It should print like:

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

Update: I also see two calls of nextLine(), one after the other.

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

Why do you need the second call? Remove the second line, kbd.nextLine();.

Not just this, why do you need kbd.nextLine(); again after String columnSpec = "";?

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

and the following kbd.nextLine(); after typeChoice = kbd.nextInt();?

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

Remove all these unnecessary calls of kbd.nextLine();.