Category Archives: JDBC

JDBC: List of Drivers for Databases


In previous post you learn about   JDBC: Drivers Types and Uses. Here are JDBC supported database drivers and connection URL format. You can also check corresponding examples for more detail on it.

[table id=1 /]
Elasticsearch driver introduce in Elasticsearch 6+ version  and support with Java 8+. You can see more detail on it on Elasticsearch Tutorial.

More on JDBC

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

JDBC Tutorial

JDBC Sample Code

JDBC Issues and Solutions

JDBC Interview Questions And Answers

Advertisements

JDBC: Drivers Types and Uses


JDBC Driver is a interface that enables JAVA application to interact with the database. The JDBC driver gives out the connection to the database and implements the protocol for transferring the query and result between client and database.

There are 4 types of JDBC drivers:

  1. Type 1: JDBC-ODBC bridge driver
  2. Type 2: Native-API driver (partially java driver)
  3. Type 3: Network protocol driver (fully java driver)
  4. Type 4: Thin driver (fully java driver)

Type 1 : JDBC-ODBC bridge driver

The JDBC-ODBC bridge driver uses ODBC driver to connect to the database. The JDBC-ODBC bridge driver converts JDBC method calls into the ODBC function call (local native code). This is now discouraged because of thin driver.

JDBC-ODBC Bridge driver is not multi threaded  which uses synchronized methods to serialize all the calls made to ODBC.

JDBC-ODBC Bridge can open only one Statement object per connection.

JDBC Type 1-JDBC ODBC Bridge Driver

When to use?

The type 1 driver is not considered a deployment-level driver and is typically used for development and testing purposes only.

Advantage

  • easy to use.
  • Easily connected to any database

Disadvantage

  • Performance degraded because JDBC method call is converted into the ODBC function calls.
  • The ODBC driver needs to be installed on the client machine.

Type 2: Native-API driver (partially java driver)

The Native API driver uses the client-side libraries of the database. The driver converts JDBC method calls into native calls of the database API. It is not written entirely in java.

JDBC Type 2-Native API Driver

When to use?

Type 2 drivers are useful in situations where a type 3 or type 4 driver is not available yet for your database.

Advantage

  • performance upgraded than JDBC-ODBC bridge driver.

Disadvantage

  • The Native driver needs to be installed on the each client machine.
  • The Vendor client library needs to be installed on client machine.

Type 3: Network protocol driver (fully java driver)

The Network Protocol driver uses middleware (application server) that converts JDBC calls directly or indirectly into the vendor-specific database protocol. It is fully written in java.

JDBC Type 3 - Network Protocol Driver

When to use?

If your Java application is accessing multiple types of databases at the same time, type 3 is the preferred driver.

Advantage

  • No client side library is required because of application server that can perform many tasks like load balancing, auditing , logging etc.

Disadvantage

  • Network support is required on client machine.
  • Requires database-specific coding to be done in the middle tier.
  • Maintenance of Network Protocol driver becomes costly because it requires database-specific coding to be done in the middle tier.

Type 4: Thin driver (fully java driver)

The thin driver converts JDBC calls directly into the vendor-specific native database protocol. That is why it is known as thin driver. It is fully written in Java language.

JDBC Type 4 - Thin Driver Pure Java

When to use?

If you are accessing one type of database, such as Oracle, Sybase, or IBM, the preferred driver type is 4.

Advantage

  • Better performance than all other drivers.
  • No software is required at client sided or server side.

Disadvantage

  • Drivers depend on the database.

Note : Type 4 thin driver is fastest JDBC driver written in JAVA.

Some important questions and answers for generally asked in interview when interviewer want to check depth knowledge of interviewee.

What do you mean by fastest driver? Which type of JDBC driver is the fastest one?

JDBC driver performance or fastness depends on a number of issues Quality of the driver code, size of the driver code, database server and its load, Network topology, Number of times your request is translated to a different API.

Type 4: Thin driver (fully Java driver) is the fastest driver because it converts the JDBC calls into vendor specific protocol calls and it directly interacts with the database.

Is it possible to connect to multiple databases? Using single statement can we update or extract data from two or three or many databases?

Yes, it is possible to connect to multiple databases, at the same time, but it depends on the specific driver.

To update and extract data from the different database we can use the single statement. But we need middleware to deal with multiple databases or a single database.

How does JDBC handle the data types of Java and database?

The JDBC driver converts the Java data type to the appropriate JDBC type before sending it to the database. It uses a default mapping for most data types. For example, a Java int is converted to an SQL INTEGER.

More on JDBC

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

JDBC Tutorial

JDBC Sample Code

JDBC Issues and Solutions

JDBC Interview Questions And Answers

JDBC: History and Features Evaluations


JDBC History

Sun Microsystems released JDBC on February 19, 1997 as part of Java Development Kit (JDK) 1.1 .Since then it has been part of the Java Platform, Standard Edition (Java SE).

The JDBC classes are divided in two packages:

  • java.sql
  • javax.sql.

JDBC version by Release

J2SE 1.4 : JDBC version 3.1 developed by the JAVA community process with JSR 54 specification.

J2SE 1.6 : JDBC version 4.0 developed with concept of Rowset and JSR 221 specification.

J2SE     7: JDBC version 4.1 specified by a maintenance release 1 of JSR 221 specification.

J2SE    8: JDBC version 4.1 specified by a maintenance release 2 of JSR 221 specification.

J2SE    9: JDBC version 4.1 specified by a maintenance release 3 of JSR 221 specification.
The latest version of JDBC is 4.1 currently. Java used primary package java.sql and javax.sql  jdbc api to ease and simplify the coding to database interactivity.

JDBC 3 Features

Below are new features in these packages include changes in the following areas:

  • Jdbc RowSet
  • Savepoint in transaction management to rollback and release the savepoint by              Connection.setSavepoint()

Connection.rollback(Savepoint svpt)

Connection.releaseSavepoint(Savepoint svpt) methods.

  • ResultSet and Statement Caching  for Connection Pooling.
  • Switching between Local and Global Transactions .
  • Retrieval of auto generated keys by the method getGeneratedKeys().

JDBC 4 Features

Below are new features in these packages include changes in the following areas:

  • Automatic database driver loading don’t need to write Class.forName().
  • Connection and statement interface enhancements. Some new methods added for PreparedStatement, CallableStatement, ResultSet etc.
  • DataSource improvements.
  • Event Handling support in Statement for Connection Pooling to listen statement error and statement closing events.
  • Exception handling improvements.
  • Enhanced BLOB/CLOB functionality.
  • National character set support.
  • SQL ROWID access.
  • SQL 2003 XML data type support.
  • Annotations.

More on JDBC

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

JDBC Tutorial

JDBC Sample Code

JDBC Issues and Solutions

JDBC Interview Questions And Answers

[Solved] java.sql.SQLException: No suitable driver


java.sql.SQLException: No suitable driver” occurred while connecting with Database by using JDBC api DriverManager.getConnection();

Sample Code

package com.fioit.examples.jdbc.connection;

import java.sql.DriverManager;
import java.sql.SQLException;

public class SQLServerConnection {

	public static void main(String[] args) {
		String db_url      = "jdbc:microsoft:sqlserver://HOST:1433;DatabaseName=DATABASE";
		String db_driver   = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
		String db_username = "USERNAME";
		String db_password = "PASSWORD";
		try
		{
		//Class.forName(db_driver);
		DriverManager.getConnection(db_url,db_username,db_password);
		}
//		catch(ClassNotFoundException ex)
//		{
//			ex.printStackTrace();
//		}
		catch(SQLException ex)
		{
			ex.printStackTrace();
		}

	}
}

Output


java.sql.SQLException: No suitable driver found for jdbc:microsoft:sqlserver://HOST:1433;DatabaseName=DATABASE
    at java.sql.DriverManager.getConnection(DriverManager.java:689)
    at java.sql.DriverManager.getConnection(DriverManager.java:247)
    at com.fioit.examples.jdbc.connection.SQLServerConnection.main(SQLServerConnection.java:16)

Issue

java.sql.SQLException: No suitable driver” occurred while connecting with Database by using JDBC api DriverManager.getConnection();

Solutions

  1. Include SQL deriver jar in your class path. For example for above case add SQLSerer driver jar in class path.
  2. Before Java 8 version: include below line for loading SQL driver

Class.forName(“sql.vendor.driver”);

For example: un comment Class.forName() line froom sample code.

In Java 8 or after version no need to write Class.forName() because it will load driver automatically based on connection URL.

More Issues Solution

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

[Solved] com.microsoft.sqlserver.jdbc. SQLServerException: Invalid object name ‘dual’


Below com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name ‘dual’ exception example is for SQLServer for IBATIS and Hibernate but same can occurred in JDBC also.

Example



      select 1 from dual

Exception Stack

com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'dual'.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:440)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:385)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)

Issue

SQLServer does not support dual table.

Solution

In SQLServer there is no dual table. If you want to use that then there is no need from keyword. We can use it as below


select 1

Note : In MySQL and Oracle need a FROM clause to use where clause while in SQL Server allow WHERE clause directly with out FROM clause.

For Example:
Oracle
SELECT 123 FROM DUAL WHERE 1<2
SQL Server
SELECT 123 WHERE 1<2

More Issues Solution

For more other JAVA/JDBC issues solution follow link JAVA/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] JDBC : java.sql.SQLSyntaxErrorException: Unknown database ‘database_name’


java.sql.SQLSyntaxErrorException is unchecked runtime exception which is sub class of SQLException. Herejava.sql.SQLSyntaxErrorException: Unknown database database_name”  exception occurred when connecting to database schema by JDBC. Please check below for more detail.

Sample Code

try
{
Class.forName("com.mysql.jdbc.Driver");
connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/FacingIssuesOnITDB", "root", "facingissuesonit");
}
catch(ClassNotFoundException ex)
{
ex.printStackTrace();
}

Output


java.sql.SQLSyntaxErrorException: Unknown database 'facingissuesonitdb'
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:118)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
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 com.fioit.examples.jdbc.connection.MyConnection.getConnectionMain(MyConnection.java:59)
at com.fioit.examples.jdbc.crud.CRUDOperation.main(CRUDOperation.java:20)

Issue

Above issue “java.sql.SQLSyntaxErrorException: Unknown database database_name” occurred because no schema exist in MySQL database with name facingissuesonitdb.

Solution

In JDBC always handle java.sql.SQLException in your connection code also because if any issue happen for java.sql.SQLSyntaxErrorException can catch by SQLException block.

More Issues Solution

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

JDBC Architecture Model


JDBC is core part of java platform and is an API specification developed by Sun Microsystems. Nearly all relational DBMSs (Database Management Systems) support SQL, and JAVA itself runs on most platforms, JDBC makes it possible to write a single database application that can run on different platforms and interact with different DBMSs.

“JDBC is similar to ODBC, but specifically designed for JAVA while ODBC is language independent.”

The JDBC driver manager is a traditional backbone of the JDBC architecture, which specifies objects for connecting Java applications to a JDBC driver. The JDBC Test Suite helps to ensure that the JDBC drivers will run the program. The JDBC-ODBC Bridge software offers JDBC access via the open database connectivity drivers.

JDBC Architecture

The JDBC architecture supports two-tier and three-tier processing models for accessing a database.

Two-tier Model (Client/Server)

A Java applet or application communicates directly to the data source. The JDBC driver enables communication between the application and the data source. When a user sends a query to the data source, the answers for those queries are sent back to the user in the form of results. The data source may be located on  single machine or  different machine on a network to which a user is connected. This is known as a client/server configuration, where the user’s machine acts as a client and the machine having the data source running acts as the server. The network connection can be intranet or Internet.
JDBC Two Tiers Architecture

 

Three-tier Model

In this model having one more layer as middle tier where the user’s commands or queries are sent to middle-tier services, from which the commands are again sent to the data source. The results are sent back to the middle tier, and from there to the user. This type of model is found very useful to makes simple to maintain access control and make updates to corporate data in database. Application deployment also becomes easy and provides performance benefits. The middle tier is usually written in C or C++.

JDBC Three Tier Architecture

References

https://docs.oracle.com/javase/tutorial/jdbc/overview/architecture.html

[Solved] JDBC : com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.


com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException is runtime exception thrown while connecting with database. This exception can occurred with so many reasons.

Sample Code

 try
{
Class.forName("com.mysql.jdbc.Driver");
Connection	connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/", "root", "facingissuesonit");
} catch (ClassNotFoundException ex) {
ex.printStackTrace();
}
catch (SQLException ex) {
ex.printStackTrace();
}
 

Output Message


com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database 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.jdbc.Util.handleNewInstance(Util.java:404)
    at com.mysql.jdbc.Util.getInstance(Util.java:387)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:917)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2332)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2085)
    at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:795)
    at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:44)
    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.jdbc.Util.handleNewInstance(Util.java:404)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:400)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:327)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:247)
    at com.fioit.examples.jdbc.connection.MyConnection.getConnection(MyConnection.java:21)
    at com.fioit.jdbc.examples.schemasetup.DatabaseOperationTest.main(DatabaseOperationTest.java:15)
Caused by: java.lang.NullPointerException
    at com.mysql.jdbc.ConnectionImpl.getServerCharset(ConnectionImpl.java:3005)
    at com.mysql.jdbc.MysqlIO.sendConnectionAttributes(MysqlIO.java:1916)
    at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1845)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1215)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2255)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2286)
    ... 14 more

Issue

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException occurs  in above code for line DriverManager.getConnection().

Solutions

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException can occurred by many reasons for above example it was happening because of mismatch version of MYSQLServer 6.0.2 while I was using MYSQL jar for 5+ version that were  creating above exception. To solve I added below dependency on pom.xml and issue got resolved.



    mysql
    mysql-connector-java
    8.0.11

More Issues Solution

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

[Solved] JDBC: 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…


java.sql.SQLSyntaxErrorException is sub class of java.sql.SQLException which occurs in JDBC  while executing any SQL query not following syntax or any typo mistake with keywords. Here trying to drop database schema.

Constructors

  • SQLSyntaxErrorException() : Construct a SQLSyntaxErrorException Object.
  • SQLSyntaxErrorException(String reason) : Construct a SQLSyntaxErrorException Object with a given reason.
  • SQLSyntaxErrorException(String reason, String SQLState) : Construct a SQLSyntaxErrorException Object.,  with a given reason and SQLState.
  • SQLSyntaxErrorException(String reason, String SQLState, int vendorCode) : Construct a SQLSyntaxErrorException Object.with a given reason, SQLState and vendorCode.
  • SQLSyntaxErrorException(String reason, String SQLState, int vendorCode, Throwable cause) : Construct a SQLSyntaxErrorException Object .with a given reason, SQLState  vendorCode and cause..
  • SQLSyntaxErrorException(String reason, String SQLState, ,Throwable cause) : Construct a SQLSyntaxErrorException Object .with a given reason, SQLState and cause..
  • SQLSyntaxErrorException(tring reason, Throwable cause) : Construct a SQLSyntaxErrorException Object with reason and cause.
  • SQLSyntaxErrorException( Throwable cause) : Construct a SQLSyntaxErrorException Object with cause.

 

Sample Code

 try
{
Class.forName("com.mysql.jdbc.Driver");
Connection	connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/", "root", "facingissuesonit");
Statement smt = conn.createStatement();
System.out.println("Drop Database ....");
smt.executeUpdate("drop database ifexist FacingIssuesOnITDB");
System.out.println("Database drop successfully ....");
		}
} catch (ClassNotFoundException ex) {
ex.printStackTrace();
}
catch (SQLException ex) {
ex.printStackTrace();
}
<span style="display:inline !important;float:none;background-color:transparent;color:#3d596d;cursor:text;font-family:'Noto Serif', Georgia, 'Times New Roman', Times, serif;font-size:16px;font-style:normal;font-variant:normal;font-weight:400;letter-spacing:normal;line-height:19.2px;orphans:2;text-align:left;text-decoration:none;text-indent:0;text-transform:none;white-space:normal;word-spacing:0;">catch (SQLSyntaxErrorException ex) { ex.printStackTrace(); } </span>

 

Output Message


java.sql.SQLException: Can't drop database 'facingissuesonitdb'; database doesn't exist
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:127)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1393)
    at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2353)
    at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1303)
    at com.fioit.jdbc.examples.schemasetup.DatabaseOperationTest.dropDatabase(DatabaseOperationTest.java:59)
    at com.fioit.jdbc.examples.schemasetup.DatabaseOperationTest.main(DatabaseOperationTest.java:19)

Issue

java.sql.SQLSyntaxErrorException occurs in JDBC SQL query because of typo mistake there should space between two words ifexist that what throwing this exception. 

Solutions

Use correct query as below for dropping database.

for Example

Drop Database:

drop database if exists FacingIssuesOnITDB

Issues Solution

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

[Solved] JDBC: java.sql.SQLException: Can’t drop database ‘database_name’; database doesn’t exist


java.sql.SQLException occurs in JDBC APIs failure while executing any query. Here trying to drop database schema. which is not exist that’s why server is throwing below exception as output.

Sample Code

 try
{
Class.forName("com.mysql.jdbc.Driver");
Connection	connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/", "root", "facingissuesonit");
Statement smt = conn.createStatement();
System.out.println("Drop Database ....");
smt.executeUpdate("drop database FacingIssuesOnITDB"<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;">&#65279;</span>);
System.out.println("Database drop successfully ....");
		}
} catch (ClassNotFoundException ex) {
ex.printStackTrace();
}
catch (SQLException ex) {
ex.printStackTrace();
}

 

Output Message


java.sql.SQLException: Can't drop database 'facingissuesonitdb'; database doesn't exist
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:127)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1393)
    at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2353)
    at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1303)
    at com.fioit.jdbc.examples.schemasetup.DatabaseOperationTest.dropDatabase(DatabaseOperationTest.java:59)
    at com.fioit.jdbc.examples.schemasetup.DatabaseOperationTest.main(DatabaseOperationTest.java:19)

Issue

java.sql.SQLException occurs in JDBC APIs failure while executing any query. Here trying to drop database schema. which is not exist that’s why server is throwing below exception as output.

Solutions

Instead of directly running drop sql quey .first check for existence of database/table then execute sql query.

for Example

Drop Database:

drop database if exists FacingIssuesOnITDB

Drop Table:

drop table if exists Students

Issues Solution

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

[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.

[Solved] JDBC, “Invalid argument value: java.io.NotSerializableException; nested exception is java.sql.SQLException”


JDBC “java.io.NotSerializableException” occurs while using Spring JDBC Template for connectivity and passing arguments for searching/update as parameters. Because JDBC parameters always required searialize objects.

Mostly happen when forget to use get fields value from object and class is not serializable.

Example :

Exception Message

Invalid argument value: java.io.NotSerializableException; nested exception is java.sql.SQLException: Invalid argument value: java.io.NotSerializableException

Class using

public class DynamicDashboard {
private int dashboardId;
private String dashboardName;
private String htmlContent;
private String scriptContent;
private String ruleContent;

public DynamicDashboard()
{
	super();
}

//Getter and Setters

Below is my DAO class method where this exception occured  for column RULE_CONTENT.

public void addDashboard(DynamicDashboard dynamicDashboard) {
		String sql = "INSERT INTO dynamic_dashboard "
				+ "(DASHBOARD_NAME, HTML_CONTENT,SCRIPT_CONTENT,RULE_CONTENT) VALUES (?, ?, ?,?)";

		try {
			jdbcTemplate = new JdbcTemplate(dataSource);

			jdbcTemplate.update(sql,
					new Object[] { dynamicDashboard.getDashboardName(), dynamicDashboard.getHtmlContent(),
							dynamicDashboard.getScriptContent(), dynamicDashboard});
		} catch (Exception ex) {
			logger.error(ex.getMessage());
		}

	}

In above method we forget to get last parameter value for rule like dynamicDashboard.getRuleContent()for database column RULE_CONTENT because JDBCTemplate always required serializable object and all wrapper classes(Integer, String etc.) are searializable. It will through exception “java.io.NotSerializableException” as above.

Solution :

Add get method for ruleContent for object dynamicDashboard.

Correct Code

public void addDashboard(DynamicDashboard dynamicDashboard) {
		String sql = "INSERT INTO dynamic_dashboard "
				+ "(DASHBOARD_NAME, HTML_CONTENT,SCRIPT_CONTENT,RULE_CONTENT) VALUES (?, ?, ?,?)";

		try {
			jdbcTemplate = new JdbcTemplate(dataSource);

			jdbcTemplate.update(sql,
					new Object[] { dynamicDashboard.getDashboardName(), dynamicDashboard.getHtmlContent(),
							dynamicDashboard.getScriptContent(), dynamicDashboard.getRuleContent()});
		} catch (Exception ex) {
			logger.error(ex.getMessage());
		}

	}

More Issues

For more JDBC issue solution follow link Common JDBC Issues.

Leave you feedback to enhance more on this topic so that make it more helpful for others.

[Solved] JDBC “java.sql.SQLException: ORA-01005: null password given; logon denied”


Generally, we get this issue In JDBC  connection code. This is because of not passing password in JDBC connection code.

Exception :

java.sql.SQLException: ORA-01005: null password given; logon denied

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:389)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:382)
	at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:600)
	at oracle.jdbc.driver.T4CTTIoauthenticate.processError(T4CTTIoauthenticate.java:445)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
	at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:380)
	at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:760)
	at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:401)
	at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:546)
	at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:236)
	at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
	at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
	at java.sql.DriverManager.getConnection(Unknown Source)
	at java.sql.DriverManager.getConnection(Unknown Source)
	at JDBCConnection.openConnection(JDBCConnection.java:31)
	at JDBCConnection.main(JDBCConnection.java:9)

Solution :

Add password to your connection code in password field or connection url.

Issues Solution

For more JDBC issue solution follow link Common JDBC Issues.

 

 

[Solved] JDBC “java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver”


Generally, we get this issue In JDBC  connection code. On compile time it will not show any issue but when we run code will receive it because of Oracle Jar not added in  CLASSPATH  while JDBC is trying to load Oracle Driver by reflection.

Exception :

java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver
	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 JDBCConnection.openConnection(JDBCConnection.java:19)
	at JDBCConnection.main(JDBCConnection.java:9)

 

Solution :

Add Oracle jar in your classpath.

Issues Solution

For more JDBC issue solution follow link Common JDBC Issues.