Load JDBC Driver Class in Servlet

JSP Tutorials - Herong's Tutorial Examples

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

Bootstrap class loader: Loads JVM basic classes and all JAR files in the $JAVA_HOME/jre/lib/ext directory automatically.
System class loader: Loads JAR files required by the Tomcat server itself from the $CATALINA_HOME/bin directory automatically.
Common class loader: Loads JAR files that supports all applications from $CATALINA_BASE/lib and $CATALINA_HOME/lib automatically.
Webapp* class loaders: Loads JAR files from $CATALINA_HOME/webapps/*/WEB-INF/lib sub-directory of each application as needed.

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:

Put mssql-jdbc-7.4.1.jre12.jar in $JAVA_HOME/jre/lib/ext, so the System class loader can load it automatically.
Put mssql-jdbc-7.4.1.jre12.jar in $CATALINA_HOME/lib, so the Common class loader can load it automatically.
Make a request to com.microsoft.sqlserver.jdbc.SQLServerDriver in my SqlServerServlet.java code. This will trigger my Webapp class loader to load it.

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