❮ Home Servlet Writing Filters ❯

Servlet Database Access

This tutorial assumes you already understand how JDBC applications work. Before you start learning about Servlet database access, please visit Java MySQL Connection to set up the relevant driver and configuration.

Note:

You can download the jar package provided by this site: **

In a Java project, you only need to include mysql-connector-java-5.1.39-bin.jar in Eclipse to run the Java project.

However, in an Eclipse web project, when executing Class.forName("com.mysql.jdbc.Driver"); it won't look for the driver. Therefore, in this example, we need to copy mysql-connector-java-5.1.39-bin.jar to the lib directory under Tomcat.

Starting with the basic concepts, let's create a simple table and insert a few records into it.

Create Test Data

Next, we will create a tutorialpro database in MySQL and a websites table with the following structure:

CREATE TABLE `websites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL DEFAULT '' COMMENT 'Site Name',
  `url` varchar(255) NOT NULL DEFAULT '',
  `alexa` int(11) NOT NULL DEFAULT '0' COMMENT 'Alexa Rank',
  `country` char(10) NOT NULL DEFAULT '' COMMENT 'Country',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

Insert some data:

INSERT INTO `websites` VALUES ('1', 'Google', 'https://www.google.cm/', '1', 'USA'), ('2', 'Taobao', 'https://www.taobao.com/', '13', 'CN'), ('3', 'tutorialpro.org', 'http://www.tutorialpro.org', '5892', ''), ('4', 'Weibo', 'http://weibo.com/', '20', 'CN'), ('5', 'Facebook', 'https://www.facebook.com/', '3', 'USA');

The table will display as follows:

Access the Database

The following example demonstrates how to use Servlet to access the tutorialpro database.

package com.tutorialpro.test;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class DatabaseAccess
 */
@WebServlet("/DatabaseAccess")
public class DatabaseAccess extends HttpServlet {
    private static final long serialVersionUID = 1L;
    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
    static final String DB_URL = "jdbc:mysql://localhost:3306/tutorialpro";
    
    // Database username and password, need to be set according to your configuration
    static final String USER = "root";
    static final String PASS = "123456"; 
    /**
```java
/**
 * @see HttpServlet#HttpServlet()
 */
public DatabaseAccess() {
    super();
    // TODO Auto-generated constructor stub
}

/**
 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
 */
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    Connection conn = null;
    Statement stmt = null;
    // Set response content type
    response.setContentType("text/html;charset=UTF-8");
    PrintWriter out = response.getWriter();
    String title = "Servlet Mysql Test - tutorialpro.org";
    String docType = "<!DOCTYPE html>\n";
    out.println(docType +
    "<html>\n" +
    "<head><title>" + title + "</title></head>\n" +
    "&lt;body bgcolor=\"#f0f0f0\">\n" +
    "&lt;h1 align=\"center\">" + title + "</h1>\n");
    try {
        // Register JDBC driver
        Class.forName("com.mysql.jdbc.Driver");

        // Open a connection
        conn = DriverManager.getConnection(DB_URL, USER, PASS);

        // Execute SQL query
        stmt = conn.createStatement();
        String sql;
        sql = "SELECT id, name, url FROM websites";
        ResultSet rs = stmt.executeQuery(sql);

        // Extract data from result set
        while(rs.next()){
            // Retrieve by field name
            int id  = rs.getInt("id");
            String name = rs.getString("name");
            String url = rs.getString("url");

            // Output data
            out.println("ID: " + id);
            out.println(", Site Name: " + name);
            out.println(", Site URL: " + url);
            out.println("<br />");
        }
        out.println("</body></html>");

        // Clean-up environment
        rs.close();
        stmt.close();
        conn.close();
    } catch(SQLException se) {
        // Handle JDBC errors
        se.printStackTrace();
    } catch(Exception e) {
        // Handle Class.forName errors
        e.printStackTrace();
    } finally {
        // Finally block to close resources
        try {
            if(stmt != null)
                stmt.close();
        } catch(SQLException se2) {
        } // Nothing we can do
        try {
            if(conn != null)
                conn.close();
        } catch(SQLException se) {
            se.printStackTrace();
        } // End finally try
    } // End try
}

} finally {
    // The finally block is used to close resources
    try {
        if (stmt != null)
            stmt.close();
    } catch (SQLException se2) {
    }
    try {
        if (conn != null)
            conn.close();
    } catch (SQLException se) {
        se.printStackTrace();
    }
}

}

/**
 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
 */
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    // TODO Auto-generated method stub
    doGet(request, response);
}
}

Now let's compile the above Servlet and create the following entry in the web.xml file:

....
    <servlet>
        <servlet-name>DatabaseAccess</servlet-name>
        <servlet-class>com.tutorialpro.test.DatabaseAccess</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>DatabaseAccess</servlet-name>
        <url-pattern>/TomcatTest/DatabaseAccess</url-pattern>
    </servlet-mapping>
....

Now call this Servlet with the link: http://localhost:8080/TomcatTest/DatabaseAccess, the following response will be displayed: ```

❮ Home Servlet Writing Filters ❯