java.sql.SQLException: No suitable driver found for jdbc:jtds:sqlserver - Cause and 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, makes 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 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




Common reasons of "No suitable driver found" Error

Let's see some of the most common reasons for getting java.sql.SQLException: No suitable driver found for jdbc: error while connecting to SQL SERVER 2008, 2012 and 2014.

1) 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 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 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 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) Many junior programmer's make 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)  The third common reason of No suitable driver found the error is spelling mistake. For example, if you are using jTDS driver to connect SQL SERVER 2008 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 after spending hours checking CLASSPATH settings.  You will be greeted with 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) 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 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 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 e.g. MySQL using JDBC API. You must make sure that JDBC URL is absolutely correct as instructed by driver manual.

Further Resources to Learn JDBC in Java :
  • Practical database programming with Java by Ying Bai (book)
  • JDBC Recipes: A Problem-Solution Approach by Mahmoud Parsian (book)
  • 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]


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

Post a Comment