java.sql.SQLSyntaxErrorException:You have an error in your SQL syntax; When I try to run SQL in Java

Question

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

I tried but not work, it shows "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" — Gigi
– Gigi, Commented Dec 2, 2022 at 8:05
How are you connecting to the database. The JDBC URL should already have the Database name specified, you should not have to use the USE testDB statement and you should remove it. Also, remove the semicolon at the end of the SQL statement. — Vini
– Vini, Commented Dec 2, 2022 at 8:07
I removed the USE testDB and it shows "java.sql.SQLException: No database selected" — Gigi
– Gigi, Commented Dec 2, 2022 at 8:13
What does your JDBC URL look like? It should include the database name in there - something like jdbc:mysql://server_ip_here:3306/testdb (or useConnection.setCatalog() method in your code to select the database as suggested by Mark in his answer below) — Vini
– Vini, Commented Dec 2, 2022 at 8:47
The only other option I can think of is that you have some unprintable character in your script that isn't valid (some where after userName VARCHAR(100). — Mark Rotteveel
– Mark Rotteveel, Commented Dec 2, 2022 at 9:01

Mark Rotteveel · Accepted Answer · 2022-12-02 08:40:19Z

2

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.

edited Dec 2, 2022 at 8:40

answered Dec 2, 2022 at 8:23

Mark Rotteveel

110k240 gold badges160 silver badges232 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vini Over a year ago

Awesome! 👍 I've never used setCatalog ever - you learn a new thing everyday!

Ivan Dong · Accepted Answer · 2022-12-02 08:12:54Z

0

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

answered Dec 2, 2022 at 8:12

Ivan Dong

12 bronze badges

3 Comments

Mark Rotteveel Over a year ago

This isn't a class in the standard library. Which library provides this class, and what is its fully-qualified name?

Ivan Dong Over a year ago

sorry, it could be found in MyBatis project, if you using Spring framework, it also has a class named 'org.springframework.jdbc.datasource.init.ScriptUtils' has a similar method executeSqlScript. Or you could check the source code to write a similar util class.

Ivan Dong Over a year ago

Source links: github.com/mybatis/mybatis-3/blob/…

Collectives™ on Stack Overflow

java.sql.SQLSyntaxErrorException:You have an error in your SQL syntax; When I try to run SQL in Java

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related