Tag Archives: SQLException

[Solved] java.sql.SQLSyntaxErrorException: unexpected token: XYZ


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

Advertisements

[Solved] com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure


  • com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure.
  • java.net.ConnectException: Connection refused.
  • SQLException: Connection refused or Connection timeout

All above exceptions occurred while connecting with database or communication issues because of one or more following causes:

  1. IP address or hostname in JDBC URL is wrong.
  2. Hostname in JDBC URL is not recognized by local DNS server.
  3. Port number is missing or wrong in JDBC URL.
  4. DB server is down.
  5. DB server doesn’t accept TCP/IP connections.
  6. DB server has run out of connections.
  7. Something in between Java and DB is blocking connections, e.g. a firewall or proxy.

Solutions:

To solve the one or the other, follow the following suggestions:

  1. Verify and test them with ping.
  2. Refresh DNS or use IP address in JDBC URL instead.
  3. Verify it based on my.cnf of MySQL DB.
  4. Start the DB.
  5. Verify if MySQL is started without the --skip-networking option.
  6. Restart the DB and fix your code accordingly that it closes connections in finally.
  7. Disable firewall and/or configure firewall/proxy to allow/forward the port.

For Example :

For my case this exception occurred because database was not started.


Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:172)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:862)
    at com.mysql.cj.jdbc.ConnectionImpl.(ConnectionImpl.java:444)
    at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:230)
    at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:226)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:247)
    at connection.MyConnection.getConnectionMain(MyConnection.java:59)
    at procedure_functions.StoredProcedureTest.main(StoredProcedureTest.java:42)
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:59)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:103)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:149)
    at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:165)
    at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:92)
    at com.mysql.cj.NativeSession.connect(NativeSession.java:152)
    at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:982)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:852)
    ... 7 more
Caused by: java.net.ConnectException: Connection refused: connect
    at java.net.DualStackPlainSocketImpl.connect0(Native Method)
    at java.net.DualStackPlainSocketImpl.socketConnect(DualStackPlainSocketImpl.java:79)
    at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
    at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
    at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
    at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:172)
    at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
    at java.net.Socket.connect(Socket.java:589)
    at com.mysql.cj.protocol.StandardSocketFactory.connect(StandardSocketFactory.java:173)
    at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:66)
    ... 10 more

More on JDBC

Follow below links to know more posts on JDBC and solving JDBC issues :

JDBC : Exception and Warning Handling


SQLException is Checked Exception it encounters an error when interacting with database , executing query on Databases etc. It throws instance of SQLException.

SQLException contains several kind information on a database access error or other errors which can help to determine the cause of error:

  • Description of Error : A string describing the error mesage. It can be retrieved by SQLException.getMessage().
  • SQLStateCode : These codes and their respective meanings have been standardized by ISO/ANSI and Open Group (XOPEN), although some codes have been reserved for database vendors to define for themselves. This String object consists of five alphanumeric characters. Retrieve this code by calling the method SQLException.getSQLState().
  • DatabaseMetaData: The DatabaseMetaData method getSQLStateType can be used to discover whether the driver returns the XOPEN type or the SQL:2003 type.
  • ErrorCode: This is an integer value identifying the error that caused the SQLException instance to be thrown. Its value and meaning are implementation-specific and might be the actual error code returned by the underlying data source. Retrieve the error by calling the method SQLException.getErrorCode().
  • Exception Chaining: If more than one error occurs, the exceptions are referenced through this chain. Retrieve these exceptions by calling the method SQLException.getNextException() on the exception that was thrown.
  • Cause: A SQLException instance might have a causal relationship, which consists of one or more Throwable objects that caused the SQLException instance to be thrown. To navigate this chain of causes, recursively call the method SQLException.getCause() until a null value is returned.

Constructors

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

SQLException Sub-classes

Our JDBC driver might throw a subclass of SQLException that corresponds to a common SQLState or a common error state that is not associated with a specific SQLState class value. This enables you to write more portable error-handling code. These exceptions are subclasses of one of the following classes:

  • BatchUpdateException
  • RowSetWarning
  • SerialException
  • SQLClientInfoException
  • SQLNonTransientException
  • SQLRecoverableException
  • SQLTransientException
  • SQLWarning
  • SynchFactoryException
  • SynchProviderException

SQLException Hierarchy

Lets focus on some most common happening SQLException:

BatchUpdateException

BatchUpdateException is thrown when an error occurs during a batch update operation. In addition to the information provided by SQLException, BatchUpdateException provides the update counts for all statements that were executed before the error occurred.

SQLClientInfoException

SQLClientInfoException is thrown when one or more client information properties could not be set on a Connection. In addition to the information provided by SQLException, SQLClientInfoException provides a list of client information properties that were not set.

SQLWarning

SQLWarning objects are a subclass of SQLException that deal with database access warnings. Warnings do not stop the execution of an application, as exceptions they simply alert the user that something did not happen as planned. For example, a warning might let you know that a privilege you attempted to revoke was not revoked. Or a warning might tell you that an error occurred during a requested disconnection.

A warning can be reported on a Connection object, a Statement object (including PreparedStatement and CallableStatement objects), or a ResultSet object. Each of these classes has a getWarnings method, which you must invoke in order to see the first warning reported on the calling object. If getWarnings returns a warning, you can call the SQLWarning method getNextWarning on it to get any additional warnings. Executing a statement automatically clears the warnings from a previous statement, so they do not build up. This means, however, that if you want to retrieve warnings reported on a statement, you must do so before you execute another statement.

Example of SQLWarning

The most common warning is a DataTruncation warning, a subclass of SQLWarning. All DataTruncation objects have a SQLState of 01004, indicating that there was a problem with reading or writing data. DataTruncation methods let you find out in which column or parameter data was truncated, whether the truncation was on a read or write operation, how many bytes should have been transferred, and how many bytes were actually transferred.

Complete Example for Handling SQLException and Warning

In below example i have created one JDBC utilities class which will help to handle SQLException, SQLWarning chained Exception related to Connection, Statement and ResultSet etc. Here you will also see like how we can skip particular warning and exceptions.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCExceptionHandeling {

public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/facingissuesonitdb","root","root");
Statement stmt = conn.createStatement();
// Print JDBC Statement Warnings
JDBCUtilities.getWarningsFromStatement(stmt);
ResultSet rs = stmt.executeQuery("select * from employee");
// Print JDBC ResultSet Warnings
JDBCUtilities.getWarningsFromResultSet(rs);

while (rs.next()) {
System.out.println("ID :"+rs.getInt(1));
System.out.println("Name :"+rs.getString(2));
System.out.println("Job :"+rs.getString(3));
System.out.println("Salary :"+rs.getDouble(4));
System.out.println("*******************************");
	}
} catch (ClassNotFoundException ex) {
  System.err.println("SQL Driver is Not Found :"+ex.getMessage());
} catch (SQLException ex) {
  JDBCUtilities.printSQLException(ex);
 }
}
}

Utilities Class to Handle JDBC Exception and Warnings

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;

public class JDBCUtilities {
// Write method so that can handle SQL exception easily
public static void printSQLException(SQLException ex) {

for (Throwable e : ex) {
  if (e instanceof SQLException) {
     if (ignoreSQLException(((SQLException) e).getSQLState()) == false) {
	e.printStackTrace(System.err);
        System.err.println("SQLState: " + ((SQLException) e).getSQLState());
	System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
	System.err.println("Message: " + e.getMessage());

	Throwable t = ex.getCause();
	while (t != null) {
	  System.out.println("Cause: " + t);
	  t = t.getCause();
					}
				}
			}
		}
	}

	// Some Exception we can ignore so that no any action required.
	public static boolean ignoreSQLException(String sqlState) {

	if (sqlState == null) {
	 System.out.println("The SQL state code is not defined!");
	 return false;
	}

	// X0Y32: Jar file already exists in schema
	if (sqlState.equalsIgnoreCase("X0Y32"))
	return true;

	// 42Y55: Table already exists in schema
	if (sqlState.equalsIgnoreCase("42Y55"))
	return true;

	return false;
	}
	//Print Batch update Exception
	public static void printBatchUpdateException(BatchUpdateException b) {

	System.err.println("----BatchUpdateException----");
	System.err.println("SQLState:  " + b.getSQLState());
	System.err.println("Message:  " + b.getMessage());
	System.err.println("Vendor:  " + b.getErrorCode());
	//BatchUpdateException contains array of update counts that is similar to execute update
	System.err.print("Update counts:  ");
	int [] updateCounts = b.getUpdateCounts();

	for (int i = 0; i < updateCounts.length; i++) {
	        System.err.print(updateCounts[i] + "   ");
	    }
	}

	// retrieve result set related warnings
	public static void getWarningsFromConnection(Connection conn) throws SQLException {
		JDBCUtilities.printWarnings(conn.getWarnings());
	}

	// retrieve result set related warnings
	public static void getWarningsFromResultSet(ResultSet rs) throws SQLException {
		JDBCUtilities.printWarnings(rs.getWarnings());
	}

	// retrieve statement related warnings
	public static void getWarningsFromStatement(Statement stmt) throws SQLException {
		JDBCUtilities.printWarnings(stmt.getWarnings());
	}

	// This method is to print any warning from JDBC statement or result set
	public static void printWarnings(SQLWarning warning) throws SQLException {

	if (warning != null) {
	System.out.println("\n---Warning---\n");

	while (warning != null) {
	System.out.println("Message: " + warning.getMessage());
	System.out.println("SQLState: " + warning.getSQLState());
	System.out.print("Vendor error code: ");
	System.out.println(warning.getErrorCode());
	System.out.println("");
	warning = warning.getNextWarning();
			}
		}

	}
}

below is one SQlException output detail for one example where String value retrive with getInt()


ID :301
Name :Tester
Job :Rajesh
java.sql.SQLException: Bad format for number 'Jain' in column 4.
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
    at com.mysql.jdbc.ResultSetImpl.getDoubleInternal(ResultSetImpl.java:2549)
    at com.mysql.jdbc.ResultSetImpl.getDoubleInternal(ResultSetImpl.java:2488)
    at com.mysql.jdbc.ResultSetImpl.getDouble(ResultSetImpl.java:2449)
    at JDBCExceptionHandeling.main(JDBCExceptionHandeling.java:24)
SQLState: S1009
Error Code: 0
Message: Bad format for number 'Jain' in column 4.

For more other JAVA/JDBC issues solution follow link JDBC Issues and Solutions.

[Solved] ClassNotFoundException: com.mysql.jdbc.Driver


java.lang.ClassNotFoundException is Checked Exception which is subclass of  java.lang.ReflectiveOperationException. This  is thrown when application load a class by String name whose definition is not found. This exception occurs by below methods :

  • The forName method in the class Class.

                 Class.forName(java.lang.String)

  • The findSystemClass method in the class ClassLoader.

                ClassLoader.findSystemClass()

  • The loadClass method in class ClassLoader.

                ClassLoader.loadClass(java.lang.String, boolean)

 Difference between Class.forName() and ClassLoader.loadClass()

Example 1: ClassNotFoundException

Below is example of connecting with database and retrieve data from sql table. This will throw ClassNotFoundException because sql driver jar is not in classpath. After this example also mentioned solution.

package example;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ClassNotFoundException1 {

	public static void main(String[] args) {
		try {
			Class.forName("com.mysql.jdbc.Driver");

			Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/university", "root", "");

			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select name from employee");
			String dbtime;
			while (rs.next()) {
				dbtime = rs.getString(1);
				System.out.println(dbtime);
			}

			con.close();

		} catch (ClassNotFoundException | SQLException e) {
			System.out.println("Connection Failed! Check output console");
			e.printStackTrace();
		}

	}

}

Output:

Connection Failed! Check output console
java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
	at java.net.URLClassLoader.findClass(Unknown Source)
	at java.lang.ClassLoader.loadClass(Unknown Source)
	at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
	at java.lang.ClassLoader.loadClass(Unknown Source)
	at java.lang.Class.forName0(Native Method)
	at java.lang.Class.forName(Unknown Source)
	at example.ClassNotFoundException1.main(ClassNotFoundException1.java:13)

Solutions:

For solving ClassNotFoundException by Class.ForName() method  considering above example to load MySQL driver same way you can follow for other classes and different DB drivers.

Solution in Eclipse :Follow below steps :

  • Right click your project folder and open up Properties.
  • From the right panel, select Java Build Path then go to Libraries tab.
  • Select Add External JARs to import the MySql driver.
  • From the right panel, select Deployment Assembly.
  • Select Add…, then select Java Build Path Entries and click Next.
  • You should see the SQL driver on the list. Select it and click first.

Tomcat :

If directly running from tomcat.Just copy the MySql-Connector.jar into Tomcat’s lib folder/directory, and then remove the jar from the webApp’s lib folder, and then, run the project.

Summary :

  • Define what is ClassNotFoundException.
  • What are methods throws ClassNotFoundException?
  • Example for ClassNotFoundException.
  • How to fix ClassNotFoundException in Eclipse and Tomcat.