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.

Table of Contents

 About This Book

 JSP (JavaServer Pages) Overview

 Tomcat Installation on Windows Systems

 JSP Scripting Elements

 Java Servlet Introduction

 JSP Implicit Objects

 Syntax of JSP Pages and JSP Documents

 JSP Application Session

 Managing Cookies in JSP Pages

 JavaBean Objects and "useBean" Action Elements

 Managing HTTP Response Header Lines

 Non-ASCII Characters Support in JSP Pages

 Performance of JSP Pages

 EL (Expression Language)

 Overview of JSTL (JSP Standard Tag Libraries)

 JSTL Core Library

 JSP Custom Tags

 JSP Java Tag Interface

 Custom Tag Attributes

 Multiple Tags Working Together

 File Upload Test Application

 Using Tomcat on CentOS Systems

 Using Tomcat on macOS Systems

Connecting to SQL Server from Servlet

 SQL Server Connection Requirements

 Download and Install SQL Server JDBC Driver

 Test SQL Server JDBC Driver

Use JDBC Driver in Servlet

 Load JDBC Driver Class in Servlet

 Developing Web Applications with Servlet

 Archived Tutorials

 References

 Full Version in PDF/EPUB