Redis常见的应用场景---查询所有省份案例

-----Redis解决查询的性能问题

• 需求：访问index.jsp页面，使用ajax请求加载省份列表，用户第一次访问数据库获取，以后都从redis里面获取。

 

•  数据库表province

•  写入测试数据

INSERT INTO province(pid,pname) VALUES(1,'广东省'),(2,'湖南省'),(3,'吉林省'),(4,'广西省');

•  需要导入的内容：

 

 

• 分析

---

• 代码结构：

 jedis.properties

#最大连接数
maxTotal=100
#用户超时时间
maxWaitMillis=30000
#redis服务器地址
host=localhost
#端口号
port=6379

druid.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test
username=root
password=root
initialSize=5
maxActive=10
maxWait=30000
maxIdle=8
minIdle=3

 工具类：CharacterFilter、JDBCUtils、JedisUtil

package com.sunny.filter;

import javax.servlet.*;
import javax.servlet.annotation.WebFilter;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * 解决全站乱码问题，处理所有的请求
 */
@WebFilter("/*")
public class CharchaterFilter implements Filter {
    @Override
    public void init(FilterConfig filterConfig) throws ServletException {

    }

    @Override
    public void doFilter(ServletRequest req, ServletResponse rep, FilterChain filterChain) throws IOException, ServletException {
        //将父接口转为子接口
        HttpServletRequest request = (HttpServletRequest) req;
        HttpServletResponse response = (HttpServletResponse) rep;
        //获取请求方法
        String method = request.getMethod();
        //解决post请求中文数据乱码问题
        if(method.equalsIgnoreCase("post")){
            request.setCharacterEncoding("utf-8");
        }
        //处理响应乱码
        response.setContentType("text/html;charset=utf-8");
        filterChain.doFilter(request,response);
    }

    @Override
    public void destroy() {

    }
}

package com.sunny.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.alibaba.druid.util.JdbcUtils;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

//JDBC的工具类
public class JDBCUtils {

    private static DataSource dataSource;

    static{
        try {
            Properties properties = new Properties();
            properties.load(JdbcUtils.class.getResourceAsStream("/druid.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    //对外提供连接池
    public static DataSource getDataSource(){
        return dataSource;
    }




    //获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }


    //关闭资源
    public static void  close(Connection con, Statement st , ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


        if(st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


        if(con!=null){
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }




    }

}

package com.sunny.utils;

import redis.clients.jedis.Jedis;
import redis.clients.jedis.JedisPool;
import redis.clients.jedis.JedisPoolConfig;

import java.util.ResourceBundle;

public class JedisUtil {

    private static JedisPool jedisPool;

    //静态代码块初始化
    static{
        //读取jedis.properties配置文件解析里面的数据
        //ResourceBundle,专门用于解析properties文件，可以根据文件的名字就可以解析文件
        ResourceBundle resourceBundle = ResourceBundle.getBundle("jedis");//会自动读取类路径里面的文件
        //读取里面的数据
        int maxTotal = Integer.parseInt(resourceBundle.getString("maxTotal"));
        int maxWaitMillis = Integer.parseInt(resourceBundle.getString("maxWaitMillis"));
        String host = resourceBundle.getString("host");
        int port = Integer.parseInt(resourceBundle.getString("port"));

        //创建配置对象
        JedisPoolConfig config= new JedisPoolConfig();
        config.setMaxTotal(maxTotal);//最大连接数
        config.setMaxWaitMillis(maxWaitMillis);//用户最大等待超时时间，单位毫秒数
        //创建连接池对象
        //public JedisPool(GenericObjectPoolConfig poolConfig, String host, int port)
        jedisPool = new JedisPool(config,host,port);
    }
    //方便用户获取一个连接对象
    public static Jedis getJedis(){
        return jedisPool.getResource();
    }
}

• index.jsp代码

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
  <title>$Title$</title>
</head>
<body>
省份列表：
<select id="province">
  <option>=请选择=</option>
</select>
<%--导入jquery--%>
<script type="text/javascript" src="js/jquery-3.3.1.min.js"></script>
<script type="text/javascript">
    //发送异步ajax请求
    $.ajax({
        url:"SearchProviceListServlet",
        dataType:"json",
        type:"post",
        success:function (json) {
            //json=[{pid=xx,pname=xx},{pid=xx,pname=xx},...]
            if(json.length>0){
                //遍历数据，更新到页面上
                $(json).each(function (i,province) {
                    //将每个省份数据添加到下拉框中
                    var pid= province.pid;
                    var pname = province.pname;
                    $("#province").append("<option value='"+pid+"'>"+pname+"</option>");
                });
            }
        },
        error:function () {
            alert("服务器忙。。。");
        }
    });
</script>
</body>
</html>

package com.sunny.web;

import com.sunny.service.ProvinceService;

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

@WebServlet(name = "SearchProvinceListServlet", urlPatterns = "/SearchProviceListServlet")
public class SearchProvinceListServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }
    //实例业务
    private ProvinceService provinceService = new ProvinceService();
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        try {
            //1.调用业务获取省份列表数据（返回字符串）
            String jsonData =provinceService.findAllProvince();
            //2.返回给前端
            response.getWriter().write(jsonData);
        } catch (Exception e) {
            e.printStackTrace();

        }
    }
}

package com.sunny.service;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.sunny.Dao.ProvinceDao;
import com.sunny.model.Province;
import com.sunny.utils.JedisUtil;
import redis.clients.jedis.Jedis;

import java.util.List;

public class ProvinceService {

    //实例dao层
    private ProvinceDao provinceDao = new ProvinceDao();

    /**
     * 获取省份列表json数据
     * @return String
     */
    public String findAllProvince()throws Exception {
        //1.从redis里面获取json数据
        //获取jedis连接对象
        Jedis jedis = JedisUtil.getJedis();
        //获取json
        String jsonData = jedis.get("provinceList");
        //2.判断json数据有效性
        if(jsonData==null) {
            //2.1 如果json为空，去数据库获取省份列表数据List集合
            List<Province> provinceList =  provinceDao.findAllProvince();
            //2.2 判断List集合是否为空，不为空转换为json
            if(provinceList!=null && provinceList.size()>0) {
                jsonData = new ObjectMapper().writeValueAsString(provinceList);
                //2.3 将json写入redis
                jedis.set("provinceList",jsonData);
            }
        }
        jedis.close();//关闭连接，返回连接池
        //3.返回json
        return jsonData;
    }
}

package com.sunny.Dao;

import com.sunny.model.Province;
import com.sunny.utils.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.SQLException;
import java.util.List;

public class ProvinceDao {

    private JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());

    /**
     * 获取数据库省份列表
     * @return List<Province>
     * @throws SQLException
     */
    public List<Province> findAllProvince()throws SQLException {
        String sql="select * from province";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<Province>(Province.class));
    }
}

package com.sunny.model;

public class Province {

    private  int pid;
    private String pname;

    public Province(){}
    public Province(int pid, String pname) {
        this.pid = pid;
        this.pname = pname;
    }

    public int getPid() {
        return pid;
    }

    public void setPid(int pid) {
        this.pid = pid;
    }

    public String getPname() {
        return pname;
    }

    public void setPname(String pname) {
        this.pname = pname;
    }
}