该接口采用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;
}
}