使用Servlet优化数据查询性能

在本文的示例中，我们将提高Web应用程序从数据库中获取记录的性能。大致流程是，我们使用ServletContextListener监听器将表的数据查询出来，存储在一个集合中，然后在查询Servlet中重用该集合。这样我们就无需反复查询数据库，从而提高了查询的性能。

1 创建表和导入数据

在MySQL的test数据库中创建t_user表：

CREATE TABLE `t_user` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `username` varchar(50) DEFAULT NULL,
   `password` varchar(50) DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

插入一些测试数据：

2 编写index.jsp

index.jsp用于点击查询数据：

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>一点教程网-使用Servlet优化数据查询性能</title>
</head>
<body>
<h1>使用Servlet优化数据查询性能</h1>
<a href="Search">获取数据</a>
</body>
</html>

3 编写User实体类

User用于封装数据库表数据：

/**
 * 一点教程网 - http://www.yiidian.com
 */
public class User {
    private Integer id;
    private String username;
    private String password;

    public Integer getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}

4 编写FetchDataListener

FetchDataListener用于查询数据库数据，放入ServletContext对象：

import javax.servlet.ServletContext;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 * 一点教程网 - http://www.yiidian.com
 */
public class FetchDataListener  implements ServletContextListener {

    public void contextInitialized(ServletContextEvent e) {

        ArrayList list=new ArrayList();
        try{
            Class.forName("com.mysql.jdbc.Driver");
            Connection con= DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/test","root","root");

            PreparedStatement ps=con.prepareStatement("select * from t_user");
            ResultSet rs=ps.executeQuery();
            while(rs.next()){
                User u=new User();
                u.setId(rs.getInt(1));
                u.setUsername(rs.getString(2));
                u.setPassword(rs.getString(3));
                list.add(u);
            }
            con.close();

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

        //把数据存储到ServletContext对象
        ServletContext context=e.getServletContext();
        context.setAttribute("data",list);

    }
    public void contextDestroyed(ServletContextEvent arg0) {
        System.out.println("项目卸载...");
    }

}

5 编写SearchServlet

SearchServlet用于查询数据，但无需从数据库查询，而是从ServletContext取出缓存数据：

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import java.util.Iterator;
import java.util.List;

/**
 * 一点教程网 - http://www.yiidian.com
 */
public class SearchServlet extends HttpServlet {

    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=utf-8");
        PrintWriter out = response.getWriter();

        long before=System.currentTimeMillis();

        ServletContext context=getServletContext();
        List list=(List)context.getAttribute("data");

        Iterator itr=list.iterator();
        while(itr.hasNext()){
            User u=(User)itr.next();
            out.print("<br>"+u.getId()+" "+u.getUsername()+" "+u.getPassword());
        }

        long after=System.currentTimeMillis();
        out.print("<br>查询时长 :"+(after-before));

        out.close();
    }

}

6 配置web.xml

web.xml:

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
         version="3.1">

    <servlet>
        <servlet-name>SearchServlet</servlet-name>
        <servlet-class>SearchServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>SearchServlet</servlet-name>
        <url-pattern>/Search</url-pattern>
    </servlet-mapping>

    <listener>
        <listener-class>FetchDataListener</listener-class>
    </listener>
</web-app>

7 运行测试

点击”获取数据“，看到查询非常快：

本案例需要在lib目录导入mysql驱动程序，点击下面链接下载：

下载mysql-connector-java-5.1.40.jar

使用Servlet完成条件查询使用Servlet上传文件