I try to initialise my database in Java by running testdb.sql file

private static Connection con;
private static void initConenction() throws SQLException{
    //Getting the connection
    String url = "jdbc:mysql://localhost:3306/testDB?allowMultiQueries=true";
    final String userName = "root";
    final String password = "mypassword";
    try {
        con = DriverManager.getConnection(url, userName, password);
        System.out.println("Connection established!");
    } catch (SQLException e) {
        e.printStackTrace();
        throw e;
    }
}`

private static void initDatabase() throws SQLException, IOException {
try{
Statement stmt = con.createStatement();
String sqlStr = Files.readString(Paths.get("src/testdb.sql"));
stmt.execute(sqlStr);
System.out.println("Database initialization completed!");
} catch (IOException e) {
System.out.format("I/O error: %s%n", e);
throw e;
}catch (SQLException e){
e.printStackTrace();
throw e;
}
}

And here is the testdb.sql

CREATE DATABASE IF NOT EXISTS testDB;
USE testDB;

CREATE TABLE USERS(
    userType VARCHAR(50) not null,
    userName VARCHAR(100) not null,
    password VARCHAR(100) not null,
    primary key(userName)
);

I ran the SQL exactly the same in MySQL bench and it works, but why when I try to do it in java it fails and warns error in SQL syntax? Here is the error message:

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 'CREATE TABLE USERS( userType VARCHAR(50) not null, userName VARCHAR(100)' at line 3

I expect to create table in database by codes in Java

CodePudding user response：

The JDBC API is per statement, while your code executes two statements at once. In general this won't work. In the case of MySQL Connector/J you can configure it to accept multiple statements by setting the connection property allowMultiQueries to true.

However, you shouldn't use statements like USE when using the JDBC driver, it can potentially bring the driver in an inconsistent state. You should connect to the right database, or use the Connection.setCatalog method. As documented in the MySQL Connector/J documentation:

Always use the Connection.setCatalog() method to specify the desired database in JDBC applications, rather than the USE database statement.

If you want more advanced ways to execute schema changes, look at schema evolution tools like Flyway or Liquibase.

CodePudding user response：

you have a multiple linse sql scripts, not a signle line statement.
you could use script runner to run the lines

 ScriptRunner sr = new ScriptRunner(con);
 Reader sqlFile = new BufferedReader(new FileReader(Paths.get("src/testdb.sql")))
 sr.runScript(sqlFile);