Category Archives: JDBC

[Solved] org.h2.jdbc.JdbcSQLNonTransientConnectionException: Database may be already in use: null


Here JdbcSQLNonTransientConnectionException exception occurred because trying to connect H2 database through application while it’s already connected through eclipse data source explorer. That’s why when running application will through exception as “Database may be already in use :null“.

JdbcSQLnonTransientConnectionException Stacktrace


org.h2.jdbc.JdbcSQLNonTransientConnectionException: Database may be already in use: null. Possible solutions: close all other connection(s); use the server mode [90020-199]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:617) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:427) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.message.DbException.get(DbException.java:194) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.mvstore.db.MVTableEngine$Store.convertIllegalStateException(MVTableEngine.java:193) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.mvstore.db.MVTableEngine$Store.open(MVTableEngine.java:173) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.mvstore.db.MVTableEngine.init(MVTableEngine.java:95) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.engine.Database.getPageStore(Database.java:2739) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.engine.Database.open(Database.java:769) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.engine.Database.openDatabase(Database.java:319) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.engine.Database.(Database.java:313) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.engine.Engine.openSession(Engine.java:69) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.engine.Engine.openSession(Engine.java:201) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.engine.Engine.createSessionAndValidate(Engine.java:178) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.engine.Engine.createSession(Engine.java:161) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.engine.Engine.createSession(Engine.java:31) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:336) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.jdbc.JdbcConnection.(JdbcConnection.java:169) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.jdbc.JdbcConnection.(JdbcConnection.java:148) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.Driver.connect(Driver.java:69) ~[h2-1.4.199.jar:1.4.199]

Solutions

Follow below steps to resolve this issue. You just need to follow disconnect steps. Connect steps are just to reference to reach Data source explorer.

Steps to connect with H2 database with Eclipse

  • Go to eclipse
  • Go to tab Window -> View -> Type “Data” – Click on “Data Explorer”.
  • Select “Generic JDBC”
  • Fill properties as below.
  • Then click on Finish.

H2 database connection

Steps to disconnect database through eclipse

  • Go to “Data Explore”.
  • Expend Database connection.
  • Right click on H2DB database
  • Select option Disconnect.

Now run  application your issue get resolved.

You would like to see

Follow below link to see more JDBC, Hibernate and JPA issues solutions.

 

Advertisements

[solved] org.h2.jdbc.JdbcSQLSyntaxErrorException: Column count does not match


JdbcSQLSyntaxErrorException occurred when the executing query has violated SQL syntax rules.

JdbcSQLSyntaxErrorException Example

In this example throwing JdbcSQLSyntaxException with message “Column count does not match” because table having two columns while inserting row with only one value without column name. Here DBMS will confuse like passing value is for which column and throw exception as below “Column count does not match”.

Entity Class

@Entity
@Table(name = "author")
public class Author {

	@Id
	@GeneratedValue
	private Long id;

	@NotNull
    @Size(max = 100)
    @Column(unique = true)
	private String name;

	//getter and setter
	}

Query to insert

#wrong way
insert into author  values('Saurabh Gupta');
insert into author  values ('Gaurav Gupta');

Exception Stacktrace

 
    Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Column count does not match; SQL statement:
     insert into author values('Saurabh Gupta') [21002-199]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:451) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:427) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.message.DbException.get(DbException.java:205) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.message.DbException.get(DbException.java:181) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.message.DbException.get(DbException.java:170) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.command.dml.Insert.prepare(Insert.java:314) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.command.Parser.prepareCommand(Parser.java:689) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.engine.Session.prepareLocal(Session.java:627) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.engine.Session.prepareCommand(Session.java:565) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1292) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:217) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:205) ~[h2-1.4.199.jar:1.4.199]

Solutions

Thumb rule for database insert query is always mention the column name as below while inserting records so that if new column add on table or position change of columns in table then no code and query change required.

For above example the solution would be as below.

insert into author values(10001,'Saurabh Gupta');
insert into author values(10002,'Gaurav Gupta');

Recommend Solution

Here inserting records with column name.

insert into author (id, name) values(10001,'Saurabh Gupta');
insert into author (id, name) values(10002,'Gaurav Gupta');

You would like to see

Follow below link to see more JDBC, Hibernate and JPA issues solutions.

[Solved] java.lang.IllegalStateException: The file is locked


Here this exception occurred because trying to connect H2 database through application while it’s already connected through eclipse data source explorer. That’s why when running application will through exception as “the file is locked.


java.lang.IllegalStateException: The file is locked: nio:C:/Users/saurabh.gupta1/testdb.mv.db [1.4.199/7]
    at org.h2.mvstore.DataUtils.newIllegalStateException(DataUtils.java:883) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.mvstore.FileStore.open(FileStore.java:172) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.mvstore.MVStore.(MVStore.java:390) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.mvstore.MVStore$Builder.open(MVStore.java:3343) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.mvstore.db.MVTableEngine$Store.open(MVTableEngine.java:162) ~[h2-1.4.199.jar:1.4.199]

Solutions

Follow below steps to resolve this issue. You just need to follow disconnect steps. Connect steps are just to reference to reach Data source explorer.

Steps to connect with H2 database with Eclipse

  • Go to eclipse
  • Go to tab Window -> View -> Type “Data” – Click on “Data Explorer”.
  • Select “Generic JDBC”
  • Fill properties as below.
  • Then click on Finish.

H2 database connection

Steps to disconnect database through eclipse

  • Go to “Data Explore”.
  • Expend Database connection.
  • Right click on H2DB database
  • Select option Disconnect.

Now run  application your issue get resolved.

You would like to see

Follow below link to see more JDBC, Hibernate and JPA issues solutions.

[Solved] java.lang.ClassNotFoundException: org.hsqldb.jdbcDriver


JDBC or Springboot or hibernate throw this exception when you mentioned driver as org.hsqldb.jdbcDriver in your JDBC code or XML configuration or data source configuration properties file but required database driver jar or dependencies is not added in class path.

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)

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.

Constructors

  • ClassNotFoundException(): Constructs a ClassNotFoundException with no detail message.
  • ClassNotFoundException(String s) : Constructs a ClassNotFoundException with the specified detail message.
  • ClassNotFoundException(String s, Throwable ex) : Constructs a ClassNotFoundException with the specified detail message and optional exception that was raised while loading the class.

 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("<strong>org.hsqldb.jdbcDriver</strong>");

			Connection con = DriverManager.getConnection("jdbc:hsqldb:hsql//localhost:3306/FacingIssuesOnITDB", "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
<pre><code>
<strong>java.lang.ClassNotFoundException: org.hsqldb.jdbcDriver</strong>
	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 org.springframework.util.ClassUtils.forName(ClassUtils.java:251)
	at org.springframework.jdbc.datasource.embedded.HsqlEmbeddedDatabaseConfigurer.getInstance(HsqlEmbeddedDatabaseConfigurer.java:48)
	at org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseConfigurerFactory.getConfigurer(EmbeddedDatabaseConfigurerFactory.java:43)
	... 54 common frames omitted
</code></pre>

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 HSQLDB 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 HSQL-Connector.jar into Tomcat’s lib folder/directory, and then remove the jar from the webApp’s lib folder, and then, run the project.

Maven Springboot application

Add below dependency in your pom.xml file


<dependency>
    <groupId>org.hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
</dependency>

Summary :

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

[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

JDBC : Function Execution Example


A SQL stored function is a set of PL/SQL statements you can call by name. Stored functions are very similar to procedures, except that a function returns a value to the environment in which it is called. User functions can be used as part of a SQL expression.

SQL stored function called a user function or user-defined function.
See Also :

Types of parameters of functions

  • IN: A parameter whose value is unknown when the SQL statement is created. You bind values to IN parameters with the setXXX() methods.
  • OUT: A parameter whose value is supplied by the SQL statement it returns. You retrieve values from the OUT parameters with the getXXX() methods.\
  • INOUT: A parameter that provides both input and output values. You *bind variables with the setXXX() methods and retrieve values with the getXXX() methods.

Example :

In this example will focus on how to execute user defined function by JDBC instead of writing of user defined function.
Table and Function implementationn : Here is small function getAverageStudentAge based on student name.


Create Table
CREATE TABLE `facingissuesonitdb`.`student` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(25) NULL,
'age' INT,
PRIMARY KEY (`id`),
UNIQUE INDEX `name_UNIQUE` (`name` ASC));

Insert Records  
INSERT INTO `facingissuesonitdb`.`student` (`id`, `name`,'age') VALUES ('1', 'Saurabh Gupta','25');
INSERT INTO `facingissuesonitdb`.`student` (`id`, `name`,'age') VALUES ('2', 'Rajesh Gupta','30');
INSERT INTO `facingissuesonitdb`.`student` (`id`, `name`,'age') VALUES ('3', 'Ramesh Gupta','35');

Create User defined function

USE `facingissuesonitdb`;
DROP function IF EXISTS `getAverageStudentAge`;

DELIMITER $$
USE `facingissuesonitdb`$$
CREATE FUNCTION getAverageStudentAge (name_prefix VARCHAR(25))
  RETURNS NUMERIC
   DETERMINISTIC
    BEGIN
     DECLARE avg_age NUMERIC;
        select avg(s.age) into avg_age from student as s where s.name like '%name_prefix%';
     RETURN avg_age;
    END$$

DELIMITER ;

Java code to execute user defined function by JDBC.

package procedure_functions;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;

import connection.MyConnection;

public class FunctionTest {

public static void main(String[] args) {
try {
   Connection conn = MyConnection.getConnectionMain("MySql");

   CallableStatement stmt = conn.prepareCall("{?= call getAverageStudentAge(?)}");
   stmt.setString("Saurabh Gupta");
   stmt.registerOutParameter(1, Types.INTEGER);
   stmt.execute();

   System.out.println(stmt.getInt(1));
} catch (SQLException ex) {
	ex.printStackTrace();
}

}

}

Output:


MySql Connection created successfully !!
MySql Connection Creation end !!
25.

 

More on JDBC

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

JDBC: DriverManager Class


The DriverManager class acts as an interface between user and drivers. It keeps track of the drivers that are available and handles establishing a connection between a database and the appropriate driver. The DriverManager class maintains a list of Driver classes that have registered themselves by calling the method DriverManager.registerDriver().

Commonly used methods of DriverManager class:

  1. public static void registerDriver(Driver driver): is used to register the given driver with DriverManager.
  2. public static void deregisterDriver(Driver driver): is used to deregister the given driver (drop the driver from the list) with DriverManager.
  3. public static Connection getConnection(String url): is used to establish the connection with the specified url.
  4. public static Connection getConnection(String url,String userName,String password): is used to establish the connection with the specified url, username and password.

For example of DriverManager follow below links:

More on JDBC

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

JDBC : Stored Procedure Example


A SQL stored procedure (SP) is a collection SQL statements , SQL command logic, which is compiled and stored on the database and execute on the server. Stored procedures can also be cached and reused. The main purpose of stored procedures to hide direct SQL queries from the code and improve performance of database operations such as select, update, and delete data.

See Also :

Types of parameters of Stored Procedure:

  • IN: A parameter whose value is unknown when the SQL statement is created. You bind values to IN parameters with the setXXX() methods.
  • OUT: A parameter whose value is supplied by the SQL statement it returns. You retrieve values from the OUT parameters with the getXXX() methods.\
  • INOUT: A parameter that provides both input and output values. You *bind variables with the setXXX() methods and retrieve values with the getXXX() methods.

Example :

In this example will focus on how to execute procedures by JDBC instead of writing of procedure.

Table and Procedure :  Here is small procedure to getStudentName based on student id.


Create Table
CREATE TABLE `facingissuesonitdb`.`student` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(25) NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `name_UNIQUE` (`name` ASC));

Insert Records  
INSERT INTO `facingissuesonitdb`.`student` (`id`, `name`) VALUES ('1', 'Saurabh Gupta');
INSERT INTO `facingissuesonitdb`.`student` (`id`, `name`) VALUES ('2', 'Rajesh Gupta');
INSERT INTO `facingissuesonitdb`.`student` (`id`, `name`) VALUES ('3', 'Ramesh Gupta');

Create Stored procedure
USE `facingissuesonitdb`;
DROP procedure IF EXISTS `getStudentName`;

DELIMITER $$
USE `facingissuesonitdb`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getStudentName`(in student_id integer, out first_name varchar(25))
BEGIN
select name into  first_name from student where id=student_id;
END$$

DELIMITER ;

Java code to execute stored procedure by JDBC.

package procedure_functions;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;

import connection.MyConnection;

public class StoredProcedureTest {

	private static final String PROCEDURE_QUERY = "{call getStudentName(?,?)}";

	public static void main(String[] args) {
	 try {
             //to get connection code check blog for Connection according to your need.
	     Connection conn = MyConnection.getConnectionMain("MySql");
	     CallableStatement csmt = conn.prepareCall(PROCEDURE_QUERY);
	     csmt.setInt(1, 2);
	     // Because second parameter is OUT so register it
	    csmt.registerOutParameter(2, java.sql.Types.VARCHAR);
	    csmt.execute();
	     String firstName = csmt.getString(2);// for retrieving out parameter
											     // after execution
	     System.out.println(firstName);
	    } catch (SQLException ex) {
			ex.printStackTrace();
	   }
	}
}

Output


MySql Connection created successfully !!
MySql Connection Creation end !!
Rajesh Gupta

More on JDBC

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

JDBC : Difference between executeQuery() Vs executeUpdate() Vs execute() method


executeQuery(), executeUpdate() and execute() are the methods of java.sql.Statement interface of JDBC API which are used to execute the SQL statements.

executeQuery() Vs executeUpdate() Vs execute()

executeQuery() executeUpdate() execute()
This method is use to execute the SQL statements which retrieve some data from database. This statement is used to execute SQL statements which update or modify database. This method can be use for any kind of SQL statements.
This method returns a ResultSet object which contains the result returned by query. This method returns an int value which represent number of rows affected by the query. This will be 0 for statement which are returning nothing. This method return a Boolean value. TRUE indicates that query returned a ResultSet object and FALSE indicate returned an int value or returned nothing.
This method is use to execute select query. This method is use to execute non select query. This method is use to execute select and non select queries.
Ex: SELECT Ex:
DML->INSERT , UPDATE and DELETEDDL-> CREATE, ALTER
Any Type of SQL statements.

More on JDBC

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

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

Difference between Stored Procedure and functions


The differences between Stored procedures and Functions are given below:

See also :

Stored Procedure Function
Is used to perform business logic. Is used to perform calculation.
Must not have return type. Must have the return type.
May return zero or more values. May return only one values.
We can call function from procedure. Procedure can not be call from function.
Procedure supports input and output parameters Function supports only input parameters.
Exception handling using try/catch block can be used in stored procedures. Exception handling using try/catch can’t be used in user defined functions.

 

More on JDBC

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

JDBC: Difference between Statement, PreparedStatement and CallableSatement


JDBC API introduced statement, PreparedStatement and CallableStatemnet to execute different types of queries:

  1. Statement : Used to execute Normal SQL Queries.
  2. PreparedStatement: Used to execute dynamic or parameterized queries.
  3. CallableStatement: Used to execute StoredProcedure.

Statement Vs PreparedStatement Vs CallableStatement

Statement Prepared Statement Callable Statement
It is used to execute normal SQL queries. It is used to execute dynamic or parameterized SQL queries. It is used to execute Stored procedure or function.
It is proffered when particular query to be executed only once. It is proffered when particular query to be executed multiple times. It is proffered when stored procedure or functions to be executed.
You can no pass parameter to query by using this interface. You can pass parameter to query at run time by using this interface. You can pass three types of parameters by using this interface IN, OUT and IN OUT
This interface mainly used for DDL statements like CREATE, ALTER , DROP etc. This is used to be any kind of SQL queries which are used multiple times It is used with Stored Procedure and functions.
The performance of this interface is very low. The performance of this interface is better than query while using with multiple queries. Performance of this interface is very high because stored procedure execute on database end.
For More: Statement For More: Prepared Statement For More: Callable Interface

More on JDBC

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

JDBC: Connectivity with Access without DSN (Data Source Name)


In previous blogs you learn 5 steps to connect with database for MySQL and Oracle.

Here we will focus on two ways to connect java application with the access database.

  1. Without DSN (Data Source Name)
  2. With DSN

Example to Connect Java Application with access without DSN
In this example, we are going to connect the java program with the access database. Here, we have created the login table in the access database. There is only one column in the table named name. Let’s get all the name of the login table.

import java.sql.*;
class Test{
public static void main(String ar[]){
 try{
  String database="student.mdb";
//Here database exists in the current directory   

   String url="jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};
          DBQ=" + database + ";DriverID=22;READONLY=true";   

   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   Connection c=DriverManager.getConnection(url);
   Statement st=c.createStatement();
   ResultSet rs=st.executeQuery("select * from login");   

   while(rs.next()){
    System.out.println(rs.getString(1));
   }   

}catch(Exception ee){System.out.println(ee);}   

}}

Example to Connect Java Application with access with DSN
Connectivity with type-1 driver is not considered good. To connect java application with type-1 driver, create DSN first, here we are assuming your dsn name is mydsn.

import java.sql.*;
class Test{
public static void main(String ar[]){
 try{
   String url="jdbc:odbc:mydsn";
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   Connection c=DriverManager.getConnection(url);
   Statement st=c.createStatement();
   ResultSet rs=st.executeQuery("select * from login");   

   while(rs.next()){
    System.out.println(rs.getString(1));
   }   

}catch(Exception ee){System.out.println(ee);}   

}}

Here you have learned both the ways to access with or without DSN (Data Source Name).

See Also:

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: Connection With Oracle Database


For connecting with the Oracle database from JAVA application, you need to follow 5 steps to perform database connectivity. Below are connection information specific to oracle database:

  1. Driver class: The driver class for the oracle database is oracle.jdbc.driver.OracleDriver.
  2. Connection URL: The connection URL for the oracle database is jdbc:oracle:thin:@localhost:1521:FacingIssuesOnITDB here jdbc is the API, oracle is the database, localhost is the server name on which oracle is running, we may also use IP address, 1521 is the port number and FacingIssuesOnITDB is the database name. We may use any database, in such case, you need to replace the FacingIssuesOnITDB with your database name.
  3. Username: The default username for the oracle database is root.
  4. Password: Password is given by the user at the time of installing the oracle database. In this example, we are going to use root as the password.
import java.sql.*;
class OracleConnection{
public static void main(String args[]){
try{
//Step 1: Register the driver class
 Class.forName("oracle.jdbc.driver.OracleDriver");
//Step 2: Create connection with database
   Connection con=DriverManager.getConnection(
   "jdbc:oracle:thin@localhost:1521:FacingIssuesOnITDB","root","root");
//here FacingIssuesOnITDB is database name, root is username and password
//Step 3: Create statement
 Statement stmt=con.createStatement();
//Step 4: Execute query
 ResultSet rs=stmt.executeQuery("select * from emp");   

while(rs.next()){
  System.out.println(rs.getInt(1)+"  "+rs.getString(2)+"  "+rs.getString(3));
}
//Step 5: close connection
con.close();
    }
catch(ClassNotFoundException e){
System.out.println(e);
}
catch(SQLException e){
 System.out.println(e);
    }
}

Here you learn about steps to connect database with application and specific configuration information for Oracle.

See also :

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 Answer

JDBC: Connection With MySQL Database


For connecting with the MySQL database from JAVA application, you need to follow 5 steps to perform database connectivity. Below are connection information specific to MySQL database:

  1. Driver class: The driver class for the MySQL database is com.mysql.jdbc.Driver.
  2. Connection URL: The connection URL for the MySQL database is jdbc:mysql://localhost:3306/FacingIssuesOnITDB where jdbc is the API, MySQL is the database, localhost is the server name on which MySQL is running, we may also use IP address, 3306 is the port number and FacingIssuesOnITDB is the database name. We may use any database, in such case, you need to replace the FacingIssuesOnITDB with your database name.
  3. Username: The default username for the MySQL database is root.
  4. Password: Password is given by the user at the time of installing the MySQL database. In this example, we are going to use root as the password.
import java.sql.*;
class MySQLConnection{
public static void main(String args[]){
try{
//Step 1: Register the driver class
 Class.forName("com.mysql.jdbc.Driver");
//Step 2: Create connection with database
   Connection con=DriverManager.getConnection(
   "jdbc:mysql://localhost:3306/FacingIssuesOnITDB","root","root");
//here FacingIssuesOnITDB is database name, root is username and password
//Step 3: Create statement
 Statement stmt=con.createStatement();
//Step 4: Execute query
 ResultSet rs=stmt.executeQuery("select * from emp");   

while(rs.next()){
  System.out.println(rs.getInt(1)+"  "+rs.getString(2)+"  "+rs.getString(3));
}
//Step 5: close connection
con.close();
    }
catch(ClassNotFoundException e){
System.out.println(e);
}
catch(SQLException e){
 System.out.println(e);
    }
}

Here you learn about steps to connect database with application and specific configuration information for MySQL.

See also :

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: Steps to connect with Database


There are 5 steps to connect any java application with the database by using JDBC. They are as follows:

  1. Register the driver class
  2. Creating connection
  3. Creating statement
  4. Executing queries
  5. Closing connection

JDBC Steps for Connections

Register the driver class

The forName() method of Class class is used to register the driver class. This method is used to dynamically load the driver class.


public static void forName(String className)throws
Class.forName("oracle.jdbc.driver.OracleDriver");

Create the connection object

The getConnection() method of DriverManager class is used to establish connection with the database.


1) public static Connection getConnection(String url)throws SQLException
2) public static Connection getConnection(String url,String name,String password)
throws SQLException

Ex: Connect with Oracle

Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","password");

Create the Statement object

The createStatement() method of Connection interface is used to create statement. The object of statement is responsible to execute queries with the database.


public Statement createStatement()throws SQLException
Statement stmt=con.createStatement();

Execute the query

The executeQuery() method of Statement interface is used to execute queries to the database. This method returns the object of ResultSet that can be used to get all the records of a table.


public ResultSet executeQuery(String sql)throws SQLException
ResultSet rs=stmt.executeQuery("select * from emp");

while(rs.next()){
System.out.println(rs.getInt(1)+" "+rs.getString(2));
}

Close the connection object

By closing connection object statement and ResultSet will be closed automatically. The close() method of Connection interface is used to close the connection.


public void close()throws SQLException

 

See Also :

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: 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

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.