Select2分页懒加载查询

傷城~ 2023-05-28 05:05 14阅读 0赞

## 1、前言 ##

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

## 2、准备 ##

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

## 3、开发 ##

实现效果图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RodGhjYw_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RodGhjYw_size_16_color_FFFFFF_t_70 1]  
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;
        }
    }

[https_github.com_select2_select2]: https://github.com/select2/select2
[https_select2.org]: https://select2.org/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RodGhjYw_size_16_color_FFFFFF_t_70]: /images/20230528/7b91879cbb824074bea840285c86e601.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RodGhjYw_size_16_color_FFFFFF_t_70 1]: /images/20230528/6fced2da6d6e414da30074fd142512b1.png