该接口采用Redis+数据库查询封装实现：使用Redis做距离范围计算，读取当前距离内坐标数据，再进行数据库查询
优点：查询速度大大加快；缺点：处理逻辑复杂，由于Redis里面保存的只有数据主键与经纬度，读取出的数据只有数据主键和距离信息，因此需拿主键与数据库匹配，匹配出的数据未按距离排序，需再做距离排序处理
1.创建RedisUtils类，封装接口信息
package com.jk.util; import java.util.ArrayList; import java.util.HashMap; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; import org.apache.commons.collections.MapUtils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springside.core.dao.support.Pages; import cn.framework.dao.JdbcService; import redis.clients.jedis.GeoCoordinate; import redis.clients.jedis.GeoRadiusResponse; import redis.clients.jedis.GeoUnit; import redis.clients.jedis.Jedis; import redis.clients.jedis.params.geo.GeoRadiusParam; public class RedisUtils { private final static int ZB_PAGE = 1000; JdbcService jdbcService; private final static Logger log = LoggerFactory.getLogger(RedisUtils.class); /** * Redis数据读取接口 * @title * @date 2017年7月11日 * @author niuchuang * @param redisKey * @param longitude * @param latitude * @param distance * @param pageNum * @param pageSize * @return */ public static Object getListMap(String redisKey,Double longitude, Double latitude,Integer distance,Integer pageNum, Integer pageSize){ Jedis jedis = JedisUtil.getJedis(); //log.info("并发前对象为:{}", jedis); //读取 List<GeoRadiusResponse> responseList=null; try { responseList = jedis.georadius(redisKey, longitude, latitude, distance, GeoUnit.M, GeoRadiusParam.geoRadiusParam().withCoord().withDist().sortAscending()); } catch (Exception e) { log.error(e.getMessage()); } //即时释放jedis //log.info("关闭jedis:{}", jedis); JedisUtil.close(jedis); //没有数据时直接返回 if (responseList==null || responseList.size() == 0) { //没有分页参数时直接返回Map信息 if (pageNum==null || pageSize==null) { return new LinkedHashMap<String, Double>(); }else{ return new Pages(0, 0, pageSize, new ArrayList<Map>()); } } int totalCount = responseList.size(); //封装 if (pageNum==null || pageSize==null) { //封装为Map Map<String, Double> keysAndDistanceMap = new LinkedHashMap<String, Double>(); for (int i = 0; i < totalCount; i++) { GeoRadiusResponse geoRadiusResponse = responseList.get(i); //企业编码 String key = geoRadiusResponse.getMemberByString(); keysAndDistanceMap.put(key, geoRadiusResponse.getDistance()); } return keysAndDistanceMap; }else{ //分页参数初始化 pageNum=pageNum!=null && pageNum<=0?1:pageNum; Integer startIndex=(pageNum!=null && pageSize!=null)?(pageNum-1)*pageSize:null; List<Map<String, Object>> list=new ArrayList<Map<String, Object>>(); /*已做排序处理，此处无需再次处理排序直接分页即可 for (int i = 0; i < totalCount; i++) { GeoRadiusResponse geoRadiusResponse = responseList.get(i); //企业编码 String key = geoRadiusResponse.getMemberByString(); Map<String, Object> _map = new LinkedHashMap<String, Object>(); _map.put("qybm", geoRadiusResponse.getMemberByString()); _map.put("jl_length", geoRadiusResponse.getDistance()); list.add(_map); } //排序 //Collections.sort(list, new DistanceComparator()); Map<String, Double> keysAndDistanceMap = new LinkedHashMap<String, Double>(); Integer maxIndex=startIndex+pageSize>totalCount?totalCount:startIndex+pageSize; for (int i = startIndex;i<maxIndex && i < pageSize*pageNum; i++) { Map _map=list.get(i); keysAndDistanceMap.put(MapUtils.getString(_map, "qybm"), MapUtils.getDouble(_map, "jl_length")); }*/ //外层数据查询可能旬在条件限制，故此处直接做分页处理会存在问题 Integer maxIndex=startIndex+pageSize>totalCount?totalCount:startIndex+pageSize; Map<String, Double> keysAndDistanceMap = new LinkedHashMap<String, Double>(); for (int i = startIndex;i<maxIndex && i < pageSize*pageNum; i++) { GeoRadiusResponse geoRadiusResponse = responseList.get(i); keysAndDistanceMap.put(geoRadiusResponse.getMemberByString(), geoRadiusResponse.getDistance()); } Pages page = new Pages(startIndex, Integer.valueOf(totalCount+""), pageSize, keysAndDistanceMap); return page; } } /** * 程序逻辑数据分页封装接口 * @title * @date 2017年7月11日 * @author niuchuang * @param list * @param pageNum * @param pageSize * @return */ public static Pages getPage(List<Map> list,Integer pageNum, Integer pageSize){ if (list!=null && pageNum!=null && pageSize!=null) { pageNum=pageNum!=null && pageNum<=0?1:pageNum; Integer startIndex=(pageNum!=null && pageSize!=null)?(pageNum-1)*pageSize:null; Integer maxIndex=startIndex+pageSize>list.size()?list.size():startIndex+pageSize; List<Map> _list=new ArrayList<Map>(); for (int i = startIndex;i<maxIndex && i < pageSize*pageNum && list.size()>=i; i++) { _list.add(list.get(i)); } Pages page = new Pages(startIndex, Integer.valueOf(list.size()+""), pageSize, _list); return page; } return new Pages(0, 0, pageSize, new ArrayList<Map>()); } /** * 数据保存Redis接口 * @title * @date 2017年7月11日 * @author niuchuang * @param key * @param geoCoordinateMap */ public void addGeoAll(String key,Map<String, GeoCoordinate> geoCoordinateMap){ Jedis jedis = JedisUtil.getJedis(); try { jedis.geoadd(key, geoCoordinateMap); } catch (Exception e) { e.printStackTrace(); }finally { //即时释放jedis JedisUtil.close(jedis); } } /** * 数据保存Redis接口 * @title * @date 2017年7月11日 * @author niuchuang * @param key * @param ywId * @param dlzbjd * @param dlzbwd */ public void addGeo(String key,String ywId,double dlzbjd,double dlzbwd){ Map<String, GeoCoordinate> geoCoordinateMap = new HashMap<String, GeoCoordinate>(); GeoCoordinate coordinate = new GeoCoordinate(dlzbjd, dlzbwd); geoCoordinateMap.put(ywId, coordinate); addGeoAll(key, geoCoordinateMap); } /** * 数据存放Redis接口 * @title * @date 2017年7月11日 * @author niuchuang * @param args */ public void setZbToRedis() { String sql="select t.id,t.jd,t.wd from b_qyxx t"; int count = jdbcService.count(sql,new Object[]{}); if (count<=0) { return; } Integer pageSize=ZB_PAGE; double tmp = (double) count / pageSize; int totalBatch = (int) Math.ceil(tmp); log.info("一共：["+count+"]条数据 共["+totalBatch+"]页"); //分页进行数据存放 for (Integer pageNum = 1; pageNum <= totalBatch ; pageNum++ ) { pageNum=pageNum!=null && pageNum<=0?1:pageNum; Integer startIndex=(pageNum!=null && pageSize!=null)?(pageNum-1)*pageSize:null; List<Map> list=jdbcService.dialectBaseQuery(sql, new Object[]{},startIndex,pageSize,null); log.info("第：["+(pageNum)+"]页条["+list.size()+"]条数据"); Map<String, GeoCoordinate> geoCoordinateMap = new HashMap<String, GeoCoordinate>(); for (Map map : list) { GeoCoordinate coordinate = new GeoCoordinate(MapUtils.getDoubleValue(map, "JD"), MapUtils.getDoubleValue(map, "WD")); geoCoordinateMap.put(MapUtils.getString(map, "ID"), coordinate); } //Redis存放数据 addGeoAll("qy", geoCoordinateMap); } } } 2.实现类接口调用
package com.jk.service; import java.util.ArrayList; import java.util.Collections; import java.util.List; import java.util.Map; import org.apache.commons.collections.MapUtils; import org.apache.commons.lang.ArrayUtils; import org.apache.commons.lang.StringUtils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springside.core.dao.support.Pages; import com.jk.DistanceComparator; import com.jk.util.ComUtil; import com.jk.util.DistanceUtil; import com.jk.util.RedisUtils; import cn.framework.dao.JdbcService; import cn.framework.system.model.SystemDataField; /** * * @author niuchuang * @data 2017年4月6日 */ @SuppressWarnings({ "unchecked", "rawtypes" }) public class CycycRedisService { private final static Logger log = LoggerFactory.getLogger(CycycRedisService2.class); private JdbcService jdbcService; private Pages getData(Map paramMap, Integer pageNum, Integer pageSize) { pageNum=(pageNum==null || pageNum<1)?1:pageNum; Object valueObject[]=new Object[]{}; String colum="id,qymc,qybm,address,jd,wd"; StringBuilder sql=new StringBuilder(); sql.append("select ").append(colum).append(" from b_qyxx qy where 1=1"); String qymc=MapUtils.getString(paramMap, "qymc"); //对模糊查询进行转译，避免查传入“%”出查询出没有“%”的数据 if (StringUtils.isNotBlank(qymc)){ sql.append(" and cyqymc like ? escape '\\'"); valueObject = ArrayUtils.add(valueObject, "%"+qymc.replaceAll("\\%", "\\\\%")+"%"); } //当所属户地区不为空时不做范围限制，直接查询该所属地区内所有数据 String ssdq = MapUtils.getString(paramMap, "ssdq"); if (StringUtils.isNotBlank(ssdq) && !"-1".equals(ssdq) && !"440100".equals(ssdq)){ sql.append(" and ssdq=?"); valueObject = ArrayUtils.add(valueObject, ssdq); } Double longitude = MapUtils.getDouble(paramMap, "longitude"); Double latitude = MapUtils.getDouble(paramMap, "latitude"); Integer dis = MapUtils.getInteger(paramMap, "dis"); if (longitude!=null && latitude!=null && dis!=null && dis!=-1) { //所属户地区不为空时不做范围限制,直接用java代码进行距离范围计算 if ("1".equals(MapUtils.getString(paramMap, "isPar")) && (StringUtils.isNotBlank(qymc) || (StringUtils.isNotBlank(ssdq)) && !"-1".equals(ssdq) && !"440100".equals(ssdq))) { String ordBy=MapUtils.getString(paramMap, "ordBy"); if (StringUtils.isBlank(ordBy) || "jl".equals(ordBy)) { paramMap.put("ordBy", "id"); } //排序封装 setSort(sql, paramMap, true); Integer startIndex=(pageNum!=null && pageSize!=null)?(pageNum-1)*pageSize:null; //分页读取数据 Pages page=jdbcService.pagesdQuery(sql.toString(), valueObject, startIndex, pageSize,new SystemDataField()); List<Map> data=(List) page.getData(); if (data!=null) { //处理距离范围信息 for (Map _dataMap : data) { if (longitude!=null && latitude!=null && dis==null) { _dataMap.put("jl_length", (int)DistanceUtil.distanceSimplify(MapUtils.getDouble(_dataMap, "wd"),MapUtils.getDouble(_dataMap, "jd"), latitude, longitude)); } } } return page; }else{ //采用Redis计算距离信息 Object redisObj = RedisUtils.getListMap("qy", longitude, latitude, dis, null, null); // 没有查到数据则返回空 Map<String, Double> keysAndDistanceMap = (Map<String, Double>) redisObj; if (keysAndDistanceMap == null || keysAndDistanceMap.size() == 0) { return new Pages(0, 0, pageSize, new ArrayList<Map>()); } sql.append(" and (").append(ComUtil.inStringSqlPrepared(keysAndDistanceMap.size(), "id")).append(")"); valueObject= ArrayUtils.addAll(valueObject, keysAndDistanceMap.keySet().toArray()); List<Map> list = jdbcService.queryForList(sql.toString(), valueObject, new SystemDataField()); // 给结果添加上距离 for (Map map : list) { String ydbm = MapUtils.getString(map, "id"); double _dis = 0; if (StringUtils.isNotBlank(ydbm)) { _dis = MapUtils.getDoubleValue(keysAndDistanceMap, ydbm); } map.put("jl_length", (int) _dis); } // 按照距离进行排序 if (!"1".equals(MapUtils.getString(paramMap, "allDlzb"))) { int i=setSort(sql, paramMap, true); if (i==0) { Collections.sort(list, new DistanceComparator()); } } Pages page=RedisUtils.getPage(list, pageNum, pageSize); return page; } }else{ setSort(sql, paramMap, false); Integer startIndex=(pageNum!=null && pageSize!=null)?(pageNum-1)*pageSize:null; Pages page=jdbcService.pagesdQuery(sql.toString(), valueObject, startIndex, pageSize,new SystemDataField()); return page; } } /** * * @title * @date 2017年5月26日 * @author niuchuang * @param sql * @param paramMap * @param isNullNoSort 是否不默认排序 * @return */ private int setSort(StringBuilder sql,Map paramMap,boolean isNullNoSort){ String ordBy=MapUtils.getString(paramMap, "ordBy"); if(isNullNoSort && ("jl".equals(ordBy) || StringUtils.isBlank(ordBy))){ return 0; }else{ //综合排序：等级最高，评价最高，离我最近 sql.append(" order by "); if ("qymc".equals(ordBy)) {//企业名称 sql.append(" qymc"); }else if ("zm".equals(ordBy)) {//字母 sql.append(" nlssort(translate(lower(qymc),'abcdefghjklmnopqrstwxyz','啊八嚓大额发噶哈几卡拉吗呐哦扒七然仨他哇西呀杂'),'NLS_SORT=SCHINESE_PINYIN_M') "); }else {//距离 ordBy="qymc"; sql.append(" qymc "); } String ordByType=MapUtils.getString(paramMap, "ordByType","0"); if("1".equals(ordByType)){ sql.append(" desc "); }else{ sql.append(" asc "); } return 1; } } public JdbcService getJdbcService() { return jdbcService; } public void setJdbcService(JdbcService jdbcService) { this.jdbcService = jdbcService; } } 3.其他接口信息
/** * 拼接 or in SQL * @title * @date 2017年5月27日 * @author niuchuang * @param size * @param columnName * @return */ public static String inStringSqlPrepared(int size, String columnName) { StringBuilder codeStB = new StringBuilder(); double tmp = (double) size / inCount; int totalBatch = (int) Math.ceil(tmp); for (int i = 1; i <= totalBatch; i++) { if (i<totalBatch || size%inCount==0) { codeStB.append(" "+columnName+" in (").append(StringUtils.repeat("?,", inCount-1)+"?").append(") or"); }else{ codeStB.append(" "+columnName+" in (").append(StringUtils.substringBeforeLast(StringUtils.repeat("?,", size%inCount), ",")).append(")"); } } /* for (int i = 1; i <= size; i++) { codeStB.append(i%inCount==1?" "+columnName+" in (":"").append("?").append(i%inCount==0?") or":i==size?")":","); }*/ if (codeStB.toString().endsWith("or")) { codeStB = new StringBuilder(StringUtils.substringBeforeLast(codeStB.toString(), "or")); } return codeStB.toString(); } public static double distanceSimplify(Double lat1, Double lng1, Double lat2, Double lng2) { if (lat1==null || lng1==null || lat2==null || lng2==null) { return 1000000d; }else{ double dx = lng1 - lng2; // 经度差值 double dy = lat1 - lat2; // 纬度差值 double b = (lat1 + lat2) / 2.0; // 平均纬度 double Lx = Math.toRadians(dx) * 6367000.0 * Math.cos(Math.toRadians(b)); // 东西距离 double Ly = 6367000.0 * Math.toRadians(dy); // 南北距离 return Math.sqrt(Lx * Lx + Ly * Ly); // 用平面的矩形对角距离公式计算总距离 } } public class DistanceComparator implements Comparator<Map>{ @Override public int compare(Map map, Map t1) { double dis1 = MapUtils.getDoubleValue(map, "jl_length"); double dis2 = MapUtils.getDoubleValue(t1, "jl_length"); if(dis1 > dis2){ return 1; }else if(dis1 < dis1){ return -1; } return 0; } }
