SQL Syntax error when connecting to MySQL through Java

Question

I'm connecting to a database through Java with the following code.

import java.sql.*;

public class Can {

//Database Credentials
private static String userName = "root";
private static String password = "root";
private static String databaseName = "books";

static Connection connection = null; // manages connection
static Statement statement = null; // query statement
static ResultSet resultSet = null; // manages results

static Can can;


public static void main(String[] args) {

    can = new Can();
    ResultSetMetaData metaData;
    int numberOfColumns;


    try {
        connection = DriverManager.getConnection("jdbc:mysql://localhost/"+databaseName, userName, password);
        statement = connection.createStatement();
        connection.setAutoCommit(false);

        //creating the tables
        statement.executeUpdate("CREATE TABLE Instructor" + "(instructorID INT, instructorName VARCHAR(30), PRIMARY KEY (instructorID))");
        statement.executeUpdate("CREATE TABLE Lecture" + "(lectureID INT, lectureName VARCHAR(30), time TIME, credit INT, instructorID INT, PRIMARY KEY (lectureID))");
        statement.executeUpdate("CREATE TABLE LectureStudent" + "(lectureID INT, studentID INT, PRIMARY KEY (lectureID, studentID))");
        statement.executeUpdate("CREATE TABLE Student" + "(studentID INT, studentName VARCHAR(30), CGPA FLOAT, PRIMARY KEY (studentID))");
        statement.executeUpdate("CREATE TABLE Assignment" + "(assignmentID INT, deadline INT(30), lectureID INT, PRIMARY KEY (assignmentID))");
        statement.executeUpdate("CREATE TABLE Results" + "(resultID INT, grade FLOAT, PRIMARY KEY (resultID)");


        //inserting info. into the tables
        statement.executeUpdate("INSERT INTO Instructor VALUES('1', 'instructorName1')");
        statement.executeUpdate("INSERT INTO Instructor VALUES('2', 'instructorName2')");

        statement.executeUpdate("INSERT INTO Lecture VALUES('1', 'lectureName1', '20:16:40', '3', '1')");
        statement.executeUpdate("INSERT INTO Lecture VALUES('2', 'lectureName2', '20:33:20', '4', '1')");
        statement.executeUpdate("INSERT INTO Lecture VALUES('3', 'lectureName3', '20:33:25', '4', '2')");

        statement.executeUpdate("INSERT INTO LectureStudent VALUES('1', '100')");
        statement.executeUpdate("INSERT INTO LectureStudent VALUES('2', '100')");
        statement.executeUpdate("INSERT INTO LectureStudent VALUES('3', '100')");

        statement.executeUpdate("INSERT INTO Student VALUES('100', 'studentName1', '2.4')");
        statement.executeUpdate("INSERT INTO Student VALUES('200', 'studentName2', '2.5')");
        statement.executeUpdate("INSERT INTO Student VALUES('300', 'studentName3', '2.6')");

        statement.executeUpdate("INSERT INTO Assignment VALUES('1000', '1969-12-31 10:40:00.0', '1')");

        statement.executeUpdate("INSERT INTO Results VALUES('1', '90.0')");


        //commit the transaction
        connection.commit();

        connection.setAutoCommit(true);


        resultSet = statement.executeQuery("SELECT assignmentID, deadline, lectureID FROM Assignment");
        metaData = resultSet.getMetaData();
        numberOfColumns = metaData.getColumnCount();
        System.out.println("assignment");
        can.displayTable(resultSet, numberOfColumns, metaData);


        resultSet = statement.executeQuery("SELECT instructorID, instructorName FROM Instructor");
        metaData = resultSet.getMetaData();
        numberOfColumns = metaData.getColumnCount();
        System.out.println("instructor");
        can.displayTable(resultSet, numberOfColumns, metaData);


        resultSet = statement.executeQuery("SELECT lectureID, lectureName, time, credit, instructorID FROM LectureStudent");
        metaData = resultSet.getMetaData();
        numberOfColumns = metaData.getColumnCount();
        System.out.println("lecture");
        can.displayTable(resultSet, numberOfColumns, metaData);


        resultSet = statement.executeQuery("SELECT lectureID, studentID FROM LectureStudent");
        metaData = resultSet.getMetaData();
        numberOfColumns = metaData.getColumnCount();
        System.out.println("lecture_student");
        can.displayTable(resultSet, numberOfColumns, metaData);


        resultSet = statement.executeQuery("SELECT resultID, grade FROM Result");
        metaData = resultSet.getMetaData();
        numberOfColumns = metaData.getColumnCount();
        System.out.println("result");
        can.displayTable(resultSet, numberOfColumns, metaData);


        resultSet = statement.executeQuery("SELECT studentID, studentName FROM Student");
        metaData = resultSet.getMetaData();
        numberOfColumns = metaData.getColumnCount();
        System.out.println("student");
        can.displayTable(resultSet, numberOfColumns, metaData);


    } catch (SQLException e) {
        e.printStackTrace();
    }
    finally {
        // close the connection!
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {}
    }

}//END main



public void displayTable(ResultSet resultSet, int numberOfColumns, ResultSetMetaData metaData) {

    try {
        for(int i=1; i<=numberOfColumns; i++)
            System.out.printf("%-8s\t", metaData.getColumnName(i));
        System.out.println();

        while( resultSet.next () ) {

            for( int i=1 ; i<=numberOfColumns ; i++)
                System.out.printf("%-8s\t", resultSet.getObject(i));
            System.out.println();

        }
    } catch (Exception e) { }



}//END displayTable

}//END Can

It's working fine when it comes to creating tables. But the problem is that I get the following strange errors about syntax errors when I run the program.

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 '' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2677)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1748)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1665)
at Can.main(Can.java:35)

Any ideas?

Thanks, C@N.

You are missing a closing parenthesis in the last Create statement: PRIMARY KEY (resultID)"); — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented May 12, 2012 at 20:39
That's quite a lot of code. Why don't you isolate the problem to a single SQL query? You do by the way not have a problem with connecting, you have a problem with executing the SQL query. — BalusC
– BalusC, Commented May 12, 2012 at 20:42

ypercubeᵀᴹ · Accepted Answer · 2012-05-12 20:43:49Z

5

You are missing a closing parenthesis in the last Create statement: ... PRIMARY KEY (resultID)");

It should be:

    statement.executeUpdate("CREATE TABLE Results" + 
         "(resultID INT, grade FLOAT, PRIMARY KEY (resultID))");
                                                            ^
                                parenthesis added here -----|

answered May 12, 2012 at 20:43

ypercubeᵀᴹ

116k19 gold badges181 silver badges248 bronze badges

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

3 Comments

Can Over a year ago

Yeap. Silly me. I shouldn't write code when I'm sleepy. Thanks a lot.

ypercubeᵀᴹ Over a year ago

Throwing a hundred SQL statements into one big try-catch block doesn't help much.

Mattias Isegran Bergander Over a year ago

Well it still pinpoints that line (line: 35) as the source of the problem... It's right there in the stack trace.

Rahul · Accepted Answer · 2012-05-12 20:40:51Z

1

Why you are putting '' in your ID field ... it's a INT type column. make your Insert query like

statement.executeUpdate("INSERT INTO Instructor VALUES(1, 'instructorName1')");

your last create statement missing closing parenthesis ).

answered May 12, 2012 at 20:40

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Comments

Mattias Isegran Bergander · Accepted Answer · 2012-05-12 20:41:43Z

1

Have a look at line 35 as the error message says? If the lines are correct as you pasted them that is the last create Table statement? Something wrong with the SQL there.

Update: As pointed out in the comment to your question by @ypercube: you are missing a closing parenthesis there!

answered May 12, 2012 at 20:41

Mattias Isegran Bergander

11.9k2 gold badges44 silver badges50 bronze badges

Collectives™ on Stack Overflow

SQL Syntax error when connecting to MySQL through Java

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related