java.sql.SQLSyntaxErrorException with error message Unexpected token exception occurred when progress query has violated SQL Syntax or in technical terms SQLState class value is ’42’, or under vendor-specified conditions.

java.sql.SQLSyntaxErrorException is sub class of  SQLNonTransientException which is also sub class of SQLException.

Constructors

• SQLSyntaxErrorException() : Constructs a SQLSyntaxErrorException object.
• SQLSyntaxErrorException(String reason) : Constructs a SQLSyntaxErrorException object with a given reason.
• SQLSyntaxErrorException(String reason, String SQLState) :Constructs a SQLSyntaxErrorException object with a given reason and SQLState.
• SQLSyntaxErrorException(String reason, String SQLState, int vendorCode) : Constructs a SQLSyntaxErrorException object with a given reason, SQLState and vendorCode.
• SQLSyntaxErrorException(String reason, String SQLState, int vendorCode, Throwable cause) : Constructs a SQLSyntaxErrorException object with a given reason, SQLState, vendorCode and cause.
• SQLSyntaxErrorException(String reason, String SQLState, Throwable cause) : Constructs a SQLSyntaxErrorException object with a given reason, SQLState and cause.
• SQLSyntaxErrorException(String reason, Throwable cause) : Constructs a SQLSyntaxErrorException object with a given reason and cause.
• SQLSyntaxErrorException(Throwable cause): Constructs a SQLSyntaxErrorException object with a given cause.

java.sql.SQLSyntaxErrorException Example

In this below example try to execute below query while JDBC or upload default schema by Spring boot then it will throw exception “java.sql.SQLSyntaxErrorException: unexpected token: EMPLOYEE” because this query is using EMPLOYEE as required keyword TABLE in first line. That is violation of SQL syntax rule.

DROP EMPLOYEE IF EXISTS;
CREATE TABLE EMPLOYEE
(
    ID int  NOT NULL PRIMARY KEY,
    FIRST_NAME varchar(255),
    LAST_NAME varchar(255),
    ADDRESS varchar(255),
);

java.sql.SQLSyntaxErrorException Stacktrace

Caused by: java.sql.SQLSyntaxErrorException: unexpected token: EMPLOYEE
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.3.6.jar:2.3.6]
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.3.6.jar:2.3.6]
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source) ~[hsqldb-2.3.6.jar:2.3.6]
    at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source) ~[hsqldb-2.3.6.jar:2.3.6]
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:472) ~[spring-jdbc-4.3.23.RELEASE.jar:4.3.23.RELEASE]
    ... 73 common frames omitted
Caused by: org.hsqldb.HsqlException: unexpected token: EMPLOYEE
    at org.hsqldb.error.Error.parseError(Unknown Source) ~[hsqldb-2.3.6.jar:2.3.6]
    at org.hsqldb.ParserBase.unexpectedToken(Unknown Source) ~[hsqldb-2.3.6.jar:2.3.6]
    at org.hsqldb.ParserDDL.compileDrop(Unknown Source) ~[hsqldb-2.3.6.jar:2.3.6]
    at org.hsqldb.ParserCommand.compilePart(Unknown Source) ~[hsqldb-2.3.6.jar:2.3.6]
    at org.hsqldb.ParserCommand.compileStatements(Unknown Source) ~[hsqldb-2.3.6.jar:2.3.6]
    at org.hsqldb.Session.executeDirectStatement(Unknown Source) ~[hsqldb-2.3.6.jar:2.3.6]
    at org.hsqldb.Session.execute(Unknown Source) ~[hsqldb-2.3.6.jar:2.3.6]
    ... 76 common frames omitted

Solutions

Below is correct query to resolve this issue .

DROP TABLE IF EXISTS EMPLOYEE;
CREATE TABLE EMPLOYEE
(
ID int NOT NULL PRIMARY KEY,
FIRST_NAME varchar(255),
LAST_NAME varchar(255),
ADDRESS varchar(255),
);

References

https://docs.oracle.com/javase/7/docs/api/java/sql/SQLSyntaxErrorException.html