Monday, August 2, 2021

How to Fix java.sql.SQLException: No suitable driver found for jdbc:jtds:sqlserver? Solution

java.sql.SQLException: No suitable driver found for 
There are two ways to connect Microsoft SQL Server from Java program, either by using Microsoft's official JDBC driver (sqljdbc4.jar) or by using jTDS driver (jtds.jar). This error comes when your supplied database URL didn't match with the JDBC driver present in the CLASSPATH. Many programmers who usually use jtds.jar, make a mistake while using sqljdbc4.jar by adding "microsoft" in JDBC URL. That makes URL invalid and JDBC API throws "java.sql.SQLException: No suitable driver: sqljdbc4.jar" error.

For Example in JTDS, the JDBC URL format is

jdbc:jtds:://[:][/][;=[;...]]

and while using Microsoft's JDBC driver, the URL format is :

jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]

where jdbc:sqlserver string is mandatory because it's used to identify JDBC drive. It is also known as sub-protocol. All other parameters e.g. serverName, instanceName, and portNumber is optional.

If you don't provide serverName then the SQL server will look into properties collection, if an instance is not specified then JDBC will connect to default instance and if the port number is not specified then it will connect to default SQL Server port number 1433.

java.sql.SQLException: No suitable driver found for jdbc:jtds

Btw, if you are new to JDBC then I also recommend you join a comprehensive online course to learn JDBC from scratch. If you need a recommendation then you can take a look at these best Java courses on Udemy. You will learn the right ways of doing things with respect to Java and the database.



4 Common reasons for "No suitable driver found" Error for SQL Server Database 

Let's see some of the most common reasons for getting java.sql.SQLException: No suitable driver found for jdbc: error while connecting to Microsoft SQL Server database

1. Difference Drivers 

You are using JDBC URL format for jTDS driver (jdbc:jtds://localhost:1434";)  but deployed sqljdbc4.jar in CLASSPATH. In this case, you will get the following error :

java.sql.SQLException: No suitable driver found for jdbc:jtds:
//localhost:1434
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)

In order to solve this error, just add jtds.jar in CLASSPATH of your Java application. If you don't have jtds.jar, you can download it from here. Alternatively, you can also add the following Maven dependency, if you are using Maven to build your project :
<dependency>
   <groupId>net.sourceforge.jtds</groupId>
   <artifactId>jtds</artifactId>
   <version>1.3.1</version>
</dependency>

If you already have this JAR file in your CLASSPATH but still getting the above error, maybe it's time to revisit your CLASSPATH settings. See Core Java, Volume II--Advanced Features by Cay S. Horstmann to learn more about JDBC drivers and URL.



2. Wrong JDBC Driver Name

Many junior programmers make the mistake of including "microsoft" in JDBC URL for SQL SERVER like  "jdbc:microsoft:sqlserver://localhost:1433", while using sqljdbc4.jar file to connect MSSQL database. This will result in the following exception :


java.sql.SQLException: No suitable driver found 
    for jdbc:microsoft:sqlserver://localhost:1433
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)

In order to fix this error just remove microsoft from URL. Correct JDBC URL format to connect SQL SERVER is "jdbc:sqlserver://localhost:1433";.  No need to worry about CLASSPATH, because if the SQLJDBC4.jar is not present then it will give you a different error, something like java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver.


3.  Spelling Mistakes in specifying Driver Name

The third common reason for the No suitable driver found the error is a spelling mistake. For example, if you are using jTDS driver to connect SQL Server database but given JDBC URL like "jdbc:jdts://localhost:1434". It's very difficult to spot that instead of writing "jtds", you have written "jdts". I have seen this error many times, only to realize it after spending hours checking CLASSPATH settings.  You will be greeted with the following error :

java.sql.SQLException: No suitable driver found 
for jdbc:jdts://localhost:1434
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)

Solving this error is easy, just correct the spelling in JDBC URL and you are done.




4.  jTDS vs JDBC Drive 

The fourth reason for getting No suitable driver found an error while connecting to MSSQL is specifying JDBC URL as "jdbc:sqlserver://localhost:1433" but deployed jTDS driver in application's CLASSPATH. This happens because many developers use jTDS driver in the development environment and Microsoft JDBC driver (sqljdbc4.jar) in the production environment.

java.sql.SQLException: No suitable driver found 
for jdbc:sqlserver://localhost:1433
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)

In order to solve that error, just remove the jTDS driver and add Microsoft JDBC driver i.e. sqljdbc4.jar in your project's build path.


That's all about how to solve java.sql.SQLException: No suitable driver found for jdbc: XXX error in Java. The root cause of this problem is the incorrect JDBC URL, mostly the protocol part is incorrect. This is not just a Microsoft SQL Server specific error but can come while connecting to any database like MySQL using JDBC API. You must make sure that JDBC URL is absolutely correct as instructed by the driver manual.

Further Resources to Learn JDBC in Java :
  • Step by Step Guide to connect MySQL database using JDBC API (guide)
  • Java guide to connect Oracle 10g database using JDBC thin driver (guide)
  • Solving java.lang.classnotfoundexception sun.jdbc.odbc.jdbcodbcdriver [solution]
  • Fixing java.lang.ClassNotFoundException: org.postgresql.Driver [solution]
  • Solving java.lang.classnotfoundexception oracle.jdbc.driver.oracledriver [solution]
  • Dealing with java.lang.ClassNotFoundException: com.mysql.jdbc.Driver [fix]

5 comments :

Anonymous said...

I use h2database,and this problem occured.
my database bean defination is









but when I replace org.springframework.jdbc.datasource.DriverManagerDataSource with org.apache.commons.dbcp.BasicDataSource , the problem will fix up.

I am so confused.do you have some idea?

javin paul said...

Your bean definition has eaten by blogger, try to escape meta characters while positing.

Anonymous said...

@Javin Paul
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="org.h2.Driver" />
<!-- where the db will be placed (created automatically) -->
<property name="url"
value="jdbc:h2:tcp://xxx.xxx.net:8002/~/test;AUTO_RECONNECT=TRUE" />
<property name="username" value="sa" />
<property name="password" value="pass" />
</bean>

elbaloo said...

Another common reason, as with the 'jdts' typo, are leading/trailing spaces in your connection string. As dumb as that may sound, it happens.

Anonymous said...

bruh, jdbc:jtds + no suitable driver exception - I wanna kiss you - thanks

Post a Comment