JSP Tutorials - Herong's Tutorial Examples - v5.11, by Herong Yang
Load JDBC Driver Class in Servlet
This section provides a tutorial example on how to load the SQL Server JDBC driver class in Servlet Java code to resolve the 'No suitable driver found' exception from the Tomcat server.
From the last tutorial, I noticed that Tomcat server is not able to load the SQL Server JDBC driver JAR file from the WEB-INF/lib sub-directory.
To understand the root cause of the issue, we need to review how Tomcat server load JAR files with 4 types of class loaders as described on the "Class Loader HOW-TO" Website at https://tomcat.apache.org/tomcat-7.0-doc/class-loader-howto.html.
According to above descriptions, the SQL Server JDBC driver JAR file, mssql-jdbc-7.4.1.jre12.jar should be loaded by my Webapp class loader to support my SqlServerServlet.java code, because it is located in /Library/Tomcat/apache-tomcat-9.0.26/webapps/herong/WEB-INF/lib.
However if I look at my SqlServerServlet.java code, there is no direct request to for the actually SQL Server JDBC driver implementation class, which is called com.microsoft.sqlserver.jdbc.SQLServerDriver. My SqlServerServlet.java code only calls the java.sql.DriverManager.java code, which should in turn calls com.microsoft.sqlserver.jdbc.SQLServerDriver.java code. But it failed to do so.
There are 3 options to solve this issue:
The third option sounds more logical than other two options. So I Updated my SqlServerServlet.java code as below:
/* SqlServerServlet.java * Copyright (c) 2002 HerongYang.com. All Rights Reserved. */ import java.io.*; import javax.servlet.*; import javax.servlet.http.*; import java.sql.*; public class SqlServerServlet extends HttpServlet { public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException { res.setContentType("text/html"); PrintWriter out = res.getWriter(); out.println("<html><boby>"); out.println("<pre>"); Connection con = null; try { // Helping Tomcat to load SQL Server JDBC driver JAR file Class.forName( "com.microsoft.sqlserver.jdbc.SQLServerDriver"); // Obtaining a connection to SQL Server con = DriverManager.getConnection( "jdbc:sqlserver://localhost;" + "user=herong;password=Y@ng;database=HerongDB"); // Connection is ready to use DatabaseMetaData meta = con.getMetaData(); out.println("Driver name: " + meta.getDriverName()); out.println("Driver version: " + meta.getDriverVersion()); out.println("Server name: " + meta.getDatabaseProductName()); out.println("Server version: " + meta.getDatabaseProductVersion()); out.println("Connection URL: " + meta.getURL()); out.println("Login name: " + meta.getUserName()); } catch (Exception e) { e.printStackTrace(out); } out.println("</pre>"); out.println("</body></html>"); out.close(); } }
Recompile SqlServerServlet.java, restart the Tomcat server, and reload http://localhost:8080/herong/SqlServer.servlet in the Web browser, the "No suitable driver found" exception is gone. I am getting the expected output now:
Driver name: Microsoft JDBC Driver 7.4 for SQL Server Driver version: 7.4.1.0 Server name: Microsoft SQL Server Server version: 11.00.3128 Connection URL: jdbc:sqlserver://localhost:1433;useFmtOnly=false;... Login name: herong
Table of Contents
JSP (JavaServer Pages) Overview
Tomcat Installation on Windows Systems
Syntax of JSP Pages and JSP Documents
JavaBean Objects and "useBean" Action Elements
Managing HTTP Response Header Lines
Non-ASCII Characters Support in JSP Pages
Overview of JSTL (JSP Standard Tag Libraries)
Multiple Tags Working Together
Using Tomcat on CentOS Systems
►Connecting to SQL Server from Servlet
SQL Server Connection Requirements
Download and Install SQL Server JDBC Driver
►Load JDBC Driver Class in Servlet