Java MySQL SyntaxErrorException

Question

everyone! I'm trying to create a database using java. Here's the code

public void createDatabase(String user, String password) {
    String query = "CREATE DATABASE IF NOT EXISTS Contacts;use Contacts;CREATE TABLE IF NOT EXISTS info(first_name TEXT,surname TEXT,login TEXT,email TEXT,phone_number INT NOT NULL primary key)";
    Connection connection = createConnection(user, password);
        try {
        Statement statement = connection.createStatement();
        int result = statement.executeUpdate(query);
        System.out.println("Database is ready for use");
    } catch (SQLException e1) {
        e1.printStackTrace();
    }
finally {
        if (connection != null)
            try {
                connection.close();
                System.out.println("Connection closed");
            } catch (SQLException e) {
                e.printStackTrace();
            }

After running this I get

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'use Contacts;CREATE TABLE IF NOT EXISTS info(first_name TEXT,surname TEXT,login ' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
at com.mysql.jdbc.Util.getInstance(Util.java:383)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:980)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2541)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1604)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1535)
at task.DatabaseManager.createDatabase(DatabaseManager.java:28)
at task.Test.main(Test.java:14)
Connection closed

The database is created, but the tables are not. So the problem is somewhere there. And one more thing. When I paste this query into MySQL Workbench and execute it - it works perfectly and creates everything I need.

What is 'info' keyword in your sql query? Haven't seen it before. — Timofey
– Timofey, Commented Oct 1, 2015 at 9:00
And by the way, when you make several changes in a single query always put the whole query in a transaction to prevent incomplete changes. — Timofey
– Timofey, Commented Oct 1, 2015 at 9:02
@Timofey: not sure about MySQL but not all databases (e.g. Oracle) allow DDL in transactions. — davek
– davek, Commented Oct 1, 2015 at 9:19
@Timofey It's not a keyword, it's the name of the table in my database — Dmitry Klimov
– Dmitry Klimov, Commented Oct 2, 2015 at 9:12

James Jithin · Accepted Answer · 2015-10-01 09:38:40Z

Do a batch:

        String[] sqlStatements = new String[] {
                "CREATE DATABASE IF NOT EXISTS Contacts",
                "use Contacts",
                "CREATE TABLE IF NOT EXISTS info(first_name TEXT,surname TEXT,login TEXT,email TEXT,phone_number INT NOT NULL primary key)" };
        Connection connection = null;
        try {
            connection = createConnection(user, password);
            Statement statement = connection.createStatement();
            for (String sqlStatement : sqlStatements) {
                statement.addBatch(sqlStatement);
            }
            int[] result = statement.executeBatch();
            for (int i = 0; i < result.length; i++) {
                System.out.println(sqlStatements[i] + " - " + result[i]);
            }
            System.out.println(result);
            System.out.println("Database is ready for use");
        } catch (SQLException e1) {
            e1.printStackTrace();
        } finally {
            if (connection != null)
                try {
                    connection.close();
                    System.out.println("Connection closed");
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }

Aurasphere · Accepted Answer · 2016-05-06 12:19:40Z

1

Your problem may be due to the fact that you didn't set the multiple query property on your database connection, so it is executing only the first statement. Try modifying the database url like this:

jdbc:mysql:<your_url>?allowMultiQueries=true

edited May 6, 2016 at 12:19

answered Oct 1, 2015 at 9:04

Aurasphere

4,03912 gold badges53 silver badges80 bronze badges

1 Comment

Dmitry Klimov Over a year ago

Thank you so much! That was the problem. Now it works as it should

Collectives™ on Stack Overflow

Java MySQL SyntaxErrorException

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related