Use JDBC Driver in Servlet

JSP Tutorials - Herong's Tutorial Examples

∟Use JDBC Driver in Servlet

This section provides a tutorial example on how to test the SQL Server JDBC driver on macOS systems.

Now I am ready to use the JDBC driver in my Servlet page to connect to my SQL Server.

1. Create the test Servlet Java source code file, SqlServerServlet.java:

/* 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 {

  // 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();
   }
}

Compile it with the Servlet API JAR file:

herong$ cp SqlServerServlet.java \
  /Library/Tomcat/apache-tomcat-9.0.26/webapps/herong/WEB-INF/classes

herong$ cd \
  /Library/Tomcat/apache-tomcat-9.0.26/webapps/herong/WEB-INF/classes

herong$ javac -cp ../../../../lib/servlet-api.jar SqlServerServlet.java

3. Add the Servlet configuration in the WEB-INF/web.xml file:

<web-app>
  ...
  <servlet>
    <servlet-name>SqlServer</servlet-name>
    <servlet-class>SqlServerServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>SqlServer</servlet-name>
    <url-pattern>/SqlServer.servlet</url-pattern>
  </servlet-mapping>
</web-app>

4. View the Servlet page on a Web browser with this URL http://localhost:8080/herong/SqlServer.servlet. I get the following exception:

java.sql.SQLException: No suitable driver found for
  jdbc:sqlserver://localhost;user=herong;password=Y@ng;database=HerongDB
 at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:702)
 at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:251)
 at SqlServerServlet.doGet(SqlServerServlet.java:24)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:634)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
  ...
 at java.base/java.lang.Thread.run(Thread.java:830)

Too bad. Tomcat server failed to load the SQL Server JDBC driver JAR file from the WEB-INF/lib sub-directory. See the next tutorial on how to help Tomcat to load JDBC driver JAR file.