Servlet從資料庫讀取記錄效能優化


提高Servlet從資料庫中讀取記錄的效能

在這個例子中,我們將學習如何提高Web應用程式從資料庫中讀取資料記錄的效能。要實現這個工作,我們將employess表的資料預先從資料庫中讀取出來並儲存在一個集合中,以在servlet中重用這個集合。因此,當使用到這個employess表的資料時,只需要從ServletContext獲取即可,而不需要連線資料庫中查詢表的資料記錄。這樣就能提高資料的讀取效能。

要執行此應用程式,需要建立具有一些記錄的表。完整的SQL語句如下 -

DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL DEFAULT '',
  `age` int(3) unsigned NOT NULL DEFAULT '0',
  `address` varchar(254) DEFAULT NULL,
  `salary` float(8,2) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES ('1', '李小春', '23', '海口市人民大道1800號', '8900.00');
INSERT INTO `employees` VALUES ('2', '張輝', '28', '廣州天河區珠村市場', '15800.00');
INSERT INTO `employees` VALUES ('3', '林賢弟', '25', '廣州白雲區龍塘村120號', '18990.00');

提高servlet從資料庫中獲取記錄的效能的範例

在這個例子中,我們建立了6個程式碼檔案。它們分別如下 -

  1. index.html - 專案首頁
  2. Employees.java - 這是一個簡單的bean類,包含幾個屬性及其gettersetter方法,此類用於表示資料庫表:employees
  3. MyListener.java - 監聽器
  4. MyServlet1.java
  5. MyServlet2.java
  6. web.xml - 專案部署類

開啟Eclipse,建立一個動態Web專案:ImprovingFetchRecords,其完整的目錄結構如下所示 -

以下是這個專案中的幾個主要的程式碼檔案。

檔案:index.html -

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Servlet從資料庫讀取記錄效能優化</title>
</head>
<body style="text-algin: center;">
    <a href="servlet1">從資料庫讀取資料</a>|
    <a href="servlet2">讀取儲存的資料</a>
</body>
</html>

員工資訊Bean類:Employees.java -

package com.yiibai;

public class Employees {
    private int id;
    private String name;
    private String address;
    private int age;
    private float salary;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public float getSalary() {
        return salary;
    }

    public void setSalary(float salary) {
        this.salary = salary;
    }

}

檔案:MyListener.java -

這是是一個監聽類。當部署專案時,預設情況下會呼叫ServletContextListenercontextInitialized方法。 在這裡,將查詢獲取employees表的記錄,並將資料記錄在新增儲存到ArrayList類物件中。 最後,表的所有記錄將儲存在ArrayList類物件(集合)。 最後,將ServletConext物件中的ArrayList物件作為屬性儲存,以便可以在Servlet中獲取並使用它。

package com.yiibai.listener;

import javax.servlet.ServletContext;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;

import com.yiibai.Employees;

import java.sql.*;
import java.util.ArrayList;

public class MyListener implements ServletContextListener {

    public void contextInitialized(ServletContextEvent e) {
        String jdbcDriver = "com.mysql.jdbc.Driver";
        String dbURL = "jdbc:mysql://localhost/testdb";

        // Database credentials
        String dbUser = "root";
        final String passwd = "123456";
        Connection con = null;
        ArrayList list = new ArrayList();
        try {
            Class.forName(jdbcDriver);
            con = DriverManager.getConnection(dbURL, dbUser, passwd);
            PreparedStatement ps = con.prepareStatement("SELECT * FROM `employees`");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Employees emp = new Employees();
                emp.setId(rs.getInt("id"));
                emp.setName(rs.getString("name"));
                emp.setAddress(rs.getString("address"));
                emp.setAge(rs.getInt("age"));
                emp.setSalary(rs.getFloat("salary"));
                list.add(emp);
            }
            rs.close();
            ps.close();
            //con.close();

        } catch (Exception ex) {
            System.out.print(ex);
        }

        // storing the ArrayList object in ServletContext
        ServletContext context = e.getServletContext();
        context.setAttribute("con", con);
        context.setAttribute("datalist", list);

    }

    public void contextDestroyed(ServletContextEvent arg0) {
        System.out.println("project undeployed...");
    }

}

檔案:MyServlet1.java -

MyServlet1從servlet上下文物件獲取資訊並列印它。

package com.yiibai;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class MyServlet1 extends HttpServlet {
    public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        response.setCharacterEncoding("UTF-8");
        response.setContentType("text/html;charset=UTF-8");
        request.setCharacterEncoding("UTF-8");
        PrintWriter out = response.getWriter();

        long before = System.currentTimeMillis();
        ServletContext context = getServletContext();
        try {
            Connection con = (Connection) context.getAttribute("con");
            PreparedStatement ps;
            ps = con.prepareStatement("SELECT * FROM `employees`");
            ResultSet rs = ps.executeQuery();
            out.print("員工資料資訊如下所示:<hr/>");
            while (rs.next()) {
                out.print("" + rs.getInt("id") + ", " + rs.getString("name") + ", " + rs.getString("address"));
                out.println("<br/>");
            }
            //con.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        long after = System.currentTimeMillis();
        out.print("<br>總用時 :" + (after - before));
        out.close();
    }

}

檔案:MyServlet2.java -

它與MyServlet1相同,從servlet上下文物件獲取資訊並列印它。

package com.yiibai;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Iterator;
import java.util.List;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class MyServlet2 extends HttpServlet {
    public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        response.setCharacterEncoding("UTF-8");
        response.setContentType("text/html;charset=UTF-8");
        request.setCharacterEncoding("UTF-8");
        PrintWriter out = response.getWriter();

        long before = System.currentTimeMillis();

        ServletContext context = getServletContext();
        List list = (List) context.getAttribute("datalist");
        out.print("員工資料資訊(從ServletContext中預儲存讀取)如下所示:<hr/>");
        Iterator itr = list.iterator();
        while (itr.hasNext()) {
            Employees e = (Employees) itr.next();
            out.print("" + e.getId() + ", " + e.getName() + ", " + e.getAddress());
            out.println("<br/>");
        }

        long after = System.currentTimeMillis();
        out.print("<br>總用時:" + (after - before));

        out.close();
    }

}

檔案:web.xml -

這個檔案中組態包含有關servlet和監聽器的資訊。

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns="http://xmlns.jcp.org/xml/ns/javaee"
    xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
    id="WebApp_ID" version="3.1">
    <display-name>ImprovingFetchRecords</display-name>
    <welcome-file-list>
        <welcome-file>index.html</welcome-file>
        <welcome-file>index.jsp</welcome-file>
    </welcome-file-list>
    <listener>
        <listener-class>com.yiibai.listener.MyListener</listener-class>
    </listener>

    <servlet>
        <servlet-name>MyServlet1</servlet-name>
        <servlet-class>com.yiibai.MyServlet1</servlet-class>

    </servlet>
    <servlet>
        <servlet-name>MyServlet2</servlet-name>
        <servlet-class>com.yiibai.MyServlet2</servlet-class>

    </servlet>

    <servlet-mapping>
        <servlet-name>MyServlet1</servlet-name>
        <url-pattern>/servlet1</url-pattern>
    </servlet-mapping>
    <servlet-mapping>
        <servlet-name>MyServlet2</servlet-name>
        <url-pattern>/servlet2</url-pattern>
    </servlet-mapping>
</web-app>

在編寫上面程式碼後,部署此Web應用程式(在專案名稱上點選右鍵->」Run On Server…」),開啟瀏覽器存取URL: http://localhost:8080/ImprovingFetchRecords/ ,如果沒有錯誤,應該會看到以下結果 -

注意:將需要將MySQL驅動程式庫加到WEB-INFO/lib目錄下。

點選「從資料庫讀取資料」連結,應該會看到以下結果 -

點選「讀取儲存的資料」連結,應該會看到以下結果 -