Select2分页懒加载查询

最新推荐文章于 2024-09-20 14:20:22 发布

CZ__

最新推荐文章于 2024-09-20 14:20:22 发布

阅读量2.1k

点赞数 1

本文链接：https://blog.csdn.net/ththcc/article/details/105142310

版权

1、前言

由于目前显示在select中的option太多，所以觉得采用select2插件进行ajax后台查询。此方式不仅提升了访问速度，还能使用户的体验感上一个层次。但是在遇到数据多的情况下，显然这种方式已经不再适用，于是可以采用select中的懒加载降低查询的数量。

2、准备

项目环境：tomcat9，jdk1.8，Oracle
项目插件：select2, jquery
select2下载地址：https://github.com/select2/select2
select2官网：https://select2.org/

3、开发

实现效果图：
在这里插入图片描述

Html代码：

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	String ctx = request.getContextPath();
%>
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title></title>
    <script type="text/javascript" src="../../js/jquery-2.2.4.js"></script>
	<script type="text/javascript" src="../../js/select2.min.js"></script>
	<link href="../../js/select2.min.css" rel="stylesheet" />
</head>
<style>
	.input-text{
		width:85%;
		height:35px;
		line-height:30px;
		margin: 0; 
		padding: 0;
		padding-left:5px;
		border-width:1px;
	}
	.select2-dropdown {
	    margin-left: 8px !important;
	    margin-top: 20px !important;
	}
</style>
<body>
    <h1>Select2访问后台懒加载分页</h1>
    <div class="s1-example">
       	<select id="selectDemo" style="width:250px;" class="select2-dropdown" name="selectDemo" ></select>
    </div>
    <script type="text/javascript">
  	$(document).ready(function() {
    	$("#selectDemo").select2({
    		ajax: {
     		url: "<%=ctx%>/servlet/nicole.SelectDemo",
    	    dataType: 'json',
    	    delay: 250,
    	    data: function (params) {
    	        var query = {
    	    		search: params.term,
    	            page: params.page || 1,
    	            rows: 10
    	        }
    	   		return query;
    	    },
    	    processResults: function (data,params) {
    	    params.page = params.page || 1;
    		var array = data.data;
    		var i = 0;
    	    while(i < array.length){
    	        array[i]["id"] = array[i]['value'];
    	        array[i]["text"] = array[i]['label'];
    	        delete array[i]["value"];
    	        delete array[i]["label"];
    	  		i++;
    	    }
   	         return {
   	             results: array,
	   	         pagination : {
	                  more : params.page < data.total
	             }
   	         };
    	    },
    	    cache: true
    	  },
    	  placeholder: '--Please Select--',
    	  escapeMarkup: function (markup) {return markup; },
    	  minimumInputLength: 3,
		  //templateResult : formatPromCode,
    	  formatSelection: formatSelect
    	});
    });

    function format(results){	
    	if (results.loading) {
    		return results.text;
    	}
    	if(results.id){
    		return '<option value="'+ results.id +'">' + results.text + '</option>';
    	}	
    }
    function formatSelect(results){
    	  return results.id || results.text;
    }
   
    </script>
</body>
 
</html>

Java后台代码：
SelectDemo.java

package nicole;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

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

import com.fasterxml.jackson.databind.ObjectMapper;
import com.hgc.util.Debug;

import cmr.BaseCondition;
import cmr.PageGrid;

@WebServlet("/servlet/nicole.SelectDemo")
public class SelectDemo extends HttpServlet {
	private static final long serialVersionUID = 1L;

	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		Connection con = null;
		try {
			con = getConnection();
			String searchCode = req.getParameter("search");
			resp.setContentType("application/x-json;charset=UTF-8");
			BaseCondition bc = getBaseCondition(req);
			List<HashMap<String, String>> list = getSelectDemoList(con, searchCode, bc);
			PageGrid page = createPageGrid(list, bc, getSelectDemoCount(con, searchCode));
			out(page, resp);
		} catch (Exception e) {
			try {
				Debug.println("SelectDemo : " + e.toString(), null);
				throw new SQLException(e.toString());
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public int getSelectDemoCount(Connection con, String searchCode) throws Exception {
		int cnt = 0;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		String sql = null;
		try {
			sql = "SELECT COUNT(0) cnt FROM SELECT_DEMO WHERE TEXT LIKE ? ";
			pstm = con.prepareStatement(sql);
			pstm.setString(1, "%" + searchCode + "%");
			rs = pstm.executeQuery();
			while (rs.next()) {
				cnt = rs.getInt("cnt");
			}
		} catch (Exception e) {
			throw new Exception(e);
		} finally {
			pstm.close();
			rs.close();
		}
		return cnt;
	}

	private List<HashMap<String, String>> getSelectDemoList(Connection con, String searchCode, BaseCondition bc)
			throws Exception {
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
		int startCnt = bc.getPageSize() * (bc.getPage() - 1);
		int endCnt = bc.getPageSize() * bc.getPage();
		String sql = "";
		int i = 1;
		sql = "SELECT * FROM  (SELECT ROWNUM rn,t1.* FROM (SELECT TEXT text FROM SELECT_DEMO WHERE TEXT LIKE ? order by TEXT DESC) t1 "
				+ "WHERE ROWNUM <= ?) t2 WHERE t2.rn > ?";
		try {
			pstm = con.prepareStatement(sql);
			pstm.setString(i++, "%" + searchCode + "%");
			pstm.setInt(i++, endCnt);
			pstm.setInt(i++, startCnt);
			rs = pstm.executeQuery();
			while (rs.next()) {
				HashMap<String, String> map = new HashMap<String, String>();
				map.put("value", rs.getString("text"));
				map.put("label", rs.getString("text"));
				list.add(map);
			}
			return list;
		} catch (Exception e) {
			throw new Exception(e);
		} finally {
			rs.close();
			pstm.close();
		}
	}

	protected PageGrid createPageGrid(List<HashMap<String, String>> list, BaseCondition bc, int totalCount) {
		PageGrid pageGrid = new PageGrid();
		pageGrid.setData(list);
		pageGrid.setPage(bc.getPage());
		pageGrid.setRecords(list.size());
		int total = 0;
		if (pageGrid.getRecords() != 0) {
			total = totalCount % bc.getPageSize() == 0 ? totalCount / bc.getPageSize()
					: totalCount / bc.getPageSize() + 1;
		}

		pageGrid.setTotal(total);
		return pageGrid;
	}

	protected BaseCondition getBaseCondition(HttpServletRequest req) {
		BaseCondition bc = new BaseCondition();
		String pa = req.getParameter("page");
		String sizes = req.getParameter("rows");
		int page = Integer.parseInt(pa);
		int pageSize = Integer.parseInt(sizes);
		bc.setPage(page);
		bc.setPageSize(pageSize);
		return bc;
	}

	protected void out(Object result, HttpServletResponse response) throws IOException {
		ServletOutputStream out = response.getOutputStream();
		ObjectMapper objectMapper = new ObjectMapper();
		objectMapper.writeValue(out, result);
		out.flush();
	}

	private Connection getConnection() throws SQLException {
		Connection con = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url = "jdbc:oracle:thin:@127.0.0.1:1526:nicolesv";
			String user = "nicole";
			String password = "abc123";
			con = DriverManager.getConnection(url, user, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return con;
	}

	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		doPost(req, resp);
	}
}

BaseCondition.java:

package cmr;

import java.util.HashMap;
import java.util.Map;

public class BaseCondition {
	public final static int PAGE_SHOW_COUNT = 10;
	private int page = 1;
	private int pageSize = 0;
	private int totalCount = 0;

	private Map<String, Object> mo = new HashMap<String, Object>();

	public int getPage() {
		return page;
	}

	public void setPage(int page) {
		this.page = page;
	}

	public int getPageSize() {
		return pageSize > 0 ? pageSize : PAGE_SHOW_COUNT;
	}

	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
	}

	public int getTotalCount() {
		return totalCount;
	}

	public void setTotalCount(int totalCount) {
		this.totalCount = totalCount;
	}

	public void addParams(String key, Object value) {
		this.getMo().put(key, value);
	}
	
	public Object getParams(String key) {
		return this.getMo().get(key);
	}

	public Map<String, Object> getMo() {
		return mo;
	}

	public void setMo(Map<String, Object> mo) {
		this.mo = mo;
	}
}

PageGrid.java:

package cmr;

import java.util.HashMap;
import java.util.List;

public class PageGrid {
	private int page;
	private int total;
    private int records;
    private List<HashMap<String, String>> data;

    public int getPage() {
        return this.page;
    }

    public void setPage(int page) {
        this.page = page;
    }

    public int getTotal() {
		return total;
	}

	public void setTotal(int total) {
		this.total = total;
	}

	public int getRecords() {
        return this.records;
    }

    public void setRecords(int records) {
        this.records = records;
    }

    public List<HashMap<String, String>> getData() {
        return this.data;
    }

    public void setData(List<HashMap<String,String>> projCodeInfoMap) {
        this.data = projCodeInfoMap;
    }
}