I just want to create a table, but if it exists it needs to be dropped and re-created.

I use MySQL 8.0.29

Error:

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 'CREATE TABLE users (Id long, name varchar(100), lastName varchar(100), age tin' at line 1

Code:

public static void main(String[] args) throws SQLException {
     
    try (Connection connection = Util.getConnection()) {

        String sqlCommand = "DROP TABLE IF EXISTS `users`;"  
                "CREATE TABLE `users` (Id long, name varchar(100), lastName varchar(100), age tinyint)";
        Statement statement = connection.createStatement();
        statement.executeUpdate(sqlCommand);
        System.out.println("table created");

        int rows = statement.executeUpdate("INSERT users(Id,name,lastName,age) VALUES (101,'Mike','Manson',31)");            
    }
}

CodePudding user response：

The following may be the reason:

1. long is not a valid MySQL data type.
2. variable sqlCommand contains multiple statements. Each SQL statement should be terminated with a semicolon symbol. Adding a semicolon at the end of CREATE statement can solve your issue.

CodePudding user response：

By default the JDBC driver does not support multiple SQL statements in one execute call. There's a connect string option to enable it:

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-connp-props-security.html#cj-conn-prop_allowMultiQueries

• allowMultiQueries

  Allow the use of ';' to delimit multiple queries during one statement.

But there's no good reason to use multi-queries. They make your code more complex, not simpler. Allowing multi-queries creates the opportunity for a type of SQL injection vulnerabilities that are not possible otherwise. See https://xkcd.com/327/

Just run one statement per call to executeUpdate().