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个代码文件。它们分别如下 -

index.html - 项目首页
Employees.java - 这是一个简单的bean类，包含几个属性及其getter和setter方法，此类用于表示数据库表：employees。
MyListener.java - 监听器
MyServlet1.java
MyServlet2.java
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.zaixian;

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 -

这是是一个监听类。当部署项目时，默认情况下会调用ServletContextListener的contextInitialized方法。在这里，将查询获取employees表的记录，并将数据记录在添加存储到ArrayList类对象中。最后，表的所有记录将存储在ArrayList类对象(集合)。最后，将ServletConext对象中的ArrayList对象作为属性存储，以便可以在Servlet中获取并使用它。

package com.zaixian.listener;

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

import com.zaixian.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.zaixian;

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.zaixian;

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.zaixian.listener.MyListener</listener-class>
    </listener>

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

    </servlet>
    <servlet>
        <servlet-name>MyServlet2</servlet-name>
        <servlet-class>com.zaixian.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/ ，如果没有错误，应该会看到以下结果 -