JPA自定义原生sql查询踩坑现场

清疚 2023-06-28 11:08 7阅读 0赞

### JPA自定义原生sql查询踩坑现场 ###

**最近在做一个微信小程序的java后台管理系统，操作数据库要求采用Spring Data API,之前没有使用过，项目比较赶，所以就简单了解了一下操作步骤，工作原理。这方面的详细介绍网上很多，以后时间充裕再从头细过一遍。目测简直是个神器呀~~**  
*Spring Data JPA是Spring Data家族的一部分，可以轻松实现基于JPA的存储库。 此模块处理对基于JPA的数据访问层的增强支持。 它使构建使用数据访问技术的Spring驱动应用程序变得更加容易。*  
**但是坑比的事情出现了---------------**

这是我的店铺实体类，需要一条距离diatance字段，这个当然不能存在数据库里：

@Entity
    @Getter
    @Setter
    @Table(name="shop")
    @NoArgsConstructor
    @AllArgsConstructor
    public class Shop implements Serializable { 
        @Id
        @JoinColumn(name = "id")
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        @NotNull(groups = Shop.Update.class)
        private Long id;
        @JoinColumn(name = "channel_en")
        private String channel_en;
        @JoinColumn(name = "highlevelcategory_en")
        private String highlevelcategory_en;
        @JoinColumn(name = "subcategory_en")
        private String subcategory_en;
        @JoinColumn(name = "name")
        private String name;
        @JoinColumn(name = "star")
        private String star;
        @JoinColumn(name = "star_nr")
        private BigDecimal star_nr;
        @JoinColumn(name = "address")
        private String address;
        @JoinColumn(name = "latitude")
        private BigDecimal latitude;
        @JoinColumn(name = "longitude")
        private BigDecimal longitude;
        @JoinColumn(name = "phonecall")
        private String phonecall;
        @JoinColumn(name = "averagecost")
        private Integer averagecost;
        @JoinColumn(name = "businesshours")
        private String businesshours;
        @JoinColumn(name = "environmentscore")
        private BigDecimal environmentscore;
        @JoinColumn(name = "servicescore")
        private BigDecimal servicescore;
        @JoinColumn(name = "tastescore")
        private BigDecimal tastescore;
        @Column(name = "openclose",nullable = false)
        @NotNull
        private Boolean openclose;
        @Column(name = "create_time")
        @CreationTimestamp
        private Timestamp createTime;
    
    
    //***************************数据库中不存在该字段
    	private Double distance;
    //***************************
    
    
    // private List<Picture> photos;
    
        public @interface Update { }
    
    }

![对应的店铺表（不包含distance）][distance]  
现在我需要一番操作后得到distance，并直接封装给前台。同时实现动态查询以及分页和排序。  
**一个十分简单的需求-------但是我突然搞不定了。。。。。。**  
最开始打算这样做————再封装一层ShopDTO给前台，中间的数据（关注Isattention，距离distance用方法塞进去）

1.实现JpaRepository的对应Shop接口（符合JPA规范）

public interface ShopRepository extends  JpaRepository<Shop, Long>,JpaSpecificationExecutor<Shop> { 
    
    }

2.Mapper接口（Shop（从数据库中查找并持久化的数据）—>ShopDTO(给前台的数据)）

@Mapper(componentModel = "spring",unmappedTargetPolicy = ReportingPolicy.IGNORE)
    public interface ShopMapper  extends BaseMapper<ShopDTO, Shop> { 
    }

1.  JPA著名的操作方式findall(传入条件查询对象，以及追加分页功能的pageable)，简直方便的飞起~~~~

public Object queryAll(ShopQueryCriteria criteria, Pageable pageable) throws ClassNotFoundException { 
            JwtUser jwtUser = (JwtUser)userDetailsService.loadUserByUsername(SecurityUtils.getUsername());
            /*********************************************************/
            JPA著名的操作方式findall(传入条件查询对象，以及追加分页功能的pageable)，简直方便的飞起~~~~
    		/*********************************************************/
            Page<Shop> page =  shopRepository.findAll((root, criteriaQuery, criteriaBuilder) -> QueryHelp.getPredicate(root,criteria,criteriaBuilder),pageable);
            
            Page<ShopDTO> shopDto = page.map(shopMapper::toDto);
            shopDto.forEach(shop -> { 
            //图片
                List<Picture> photos = pictureService.findByShopId(shop.getId().longValue());
                shop.setPhotos(photos);
                System.out.println(shop);
                //是否关注
                Attention attention = attentionRepository.findByShopId(jwtUser.getId(), shop.getId().longValue());
                if(attention == null){ 
                    shop.setIsattention(false);
                }else{ 
                    shop.setIsattention(true);
                }
                //距离--------------------------
               if(criteria.getLatitude()!=null&&criteria.getLongitude()!=null){ 
                    double latitude = criteria.getLatitude().doubleValue();
                    double longitude = criteria.getLongitude().doubleValue();
                    double distance = LocationUtils.getDistance(latitude, longitude, shop.getLatitude().doubleValue(), shop.getLongitude().doubleValue());
     if(distance<1){//小于1公里
     shop.setFar_from(String.format("%.2f", distance*1000)+" m");//保留两位小数
     }else{ 
     shop.setFar_from(String.format("%.2f", distance)+" km");
     }
    // shop.setDistance(distance);
                }
    
            });
            return PageUtil.toPage(shopDto);
        }

如愿所偿，前台得到数据。  
**然而------------------------**  
**因为距离DiStance不在数据库表对应类中，pageable提供的简单排序方法实现不了，哎哟我去。。。。。。。**

所以打算用ShopRepository 里追加原生sql的方式来实现试试。。  
1.把distance作为别名进行sql查询,并且添加nativeQuery = true（为了实现分页）

public interface ShopRepository extends  JpaRepository<Shop, Long>,JpaSpecificationExecutor<Shop> { 
        @Query(value = "select * , " +
                "ROUND( " +
                " 6378137 * 2 * ASIN( " +
                " SQRT( " +
                " POW( " +
                " SIN( " +
                " ( " +
                " ?1 * PI() / 180 - latitude * PI() / 180 " +
                " ) / 2 " +
                " ), " +
                " 2 " +
                " ) + COS( ?1 * PI() / 180) * COS(latitude * PI() / 180) * POW( " +
                " SIN( " +
                " ( " +
                " ?2 * PI() / 180 - longitude * PI() / 180 " +
                " ) / 2 " +
                " ), " +
                " 2 " +
                " ) " +
                " ) " +
                " ) * 10000 " +
                " )/10000 AS distance " +
                " FROM shop " +
                "ORDER BY distance ASC",nativeQuery = true)
        List<Shop> findByFarSort(BigDecimal h_latitude, BigDecimal h_longitude);
    }

![无效。。。。][20200108204602934.png]  
但是还是无法动态改变排序的字段，而且JPA优秀的动态查询也无法添加进去，那我要你这JPA有何用。。。。一筹莫展。。。  
查了两天，依然没法自如的以JPA结合自定义Sql的方式解决问题。。。  
太菜了。。。  
时间紧迫，只好妥协，，还是打算采用sql拼接，看上去不聪明。。。  
1.自定义一个Repository的方法接口，并且进行实现拓展方法，坑也好多。。

/** * 自定义Repository的方法接口 * @author xiaowen * @param <T> 领域对象即实体类 * @param <ID>领域对象的注解 */
    @NoRepositoryBean
    public interface FindShopHasDistanceRepository<T, ID extends Serializable>  { 
    // @Override
        Page<Shop> findAll2(ShopQueryCriteria criteria,Pageable pageable) throws ClassNotFoundException;
    }

@NoRepositoryBean：确保添加了该注解的 repository 接口不会在运行时被创建实例。也就是说，使用了该注解的接口不会被单独创建实例，只会作为其他接口的父接口而被使用。  
2.实现类，添加的查询方法（JPA的优势当然无存。。。跪求正确的解决方式，对包含数据库不存在的字段查询以及持久化如何优雅的结合进JPA，大佬们？？！！）

public class ShopRepositoryImpl<T, ID extends Serializable>  implements FindShopHasDistanceRepository<T, Serializable> { 
        /** * 持久化上下文 */
        private final EntityManager entityManager;
    
        public ShopRepositoryImpl( EntityManager entityManager) { 
            this.entityManager = entityManager;
        }
    
    
        @Override
        public Page<Shop> findAll2(ShopQueryCriteria criteria, Pageable pageable) throws ClassNotFoundException { 
            CriteriaBuilder builder = entityManager.getCriteriaBuilder();
            StringBuilder dataSql = new StringBuilder("select * , " +
                "ROUND( " +//经纬度距离
                " 6378137 * 2 * ASIN( " +
                " SQRT( " +
                " POW( " +
                " SIN( " +
                " ( " +
                " :h_latitude * PI() / 180 - latitude * PI() / 180 " +
                " ) / 2 " +
                " ), " +
                " 2 " +
                " ) + COS( :h_latitude * PI() / 180) * COS(latitude * PI() / 180) * POW( " +
                " SIN( " +
                " ( " +
                " :h_longitude * PI() / 180 - longitude * PI() / 180 " +
                " ) / 2 " +
                " ), " +
                " 2 " +
                " ) " +
                " ) " +
                " ) * 10000 " +
                " )/10000 AS distance " +
                " FROM shop where 1=1 ");
            //创建本地sql查询实例
            if(criteria.getOpenclose()!=null){ 
                dataSql.append(" and openclose ="+criteria.getOpenclose());
            }
            if(criteria.getName()!=null){ 
                dataSql.append(" and name like '%"+criteria.getName()+"%' ");
            }
            if(criteria.getChannel()!=null){ 
                dataSql.append(" and channel_en ='"+criteria.getChannel()+"' ");
            }
            if(criteria.getHighlevelcategory()!=null){ 
                dataSql.append(" and highlevelcategory_en = '"+criteria.getHighlevelcategory()+"' ");
            }
            if(criteria.getDeptId()!=null){ 
                dataSql.append(" and id = "+criteria.getDeptId());
            }
            if(criteria.getDeptIds()!=null){ 
                dataSql.append(" and id in ( ");
                Iterator<Long> it = criteria.getDeptIds().iterator();
                int i =0;
                while(it.hasNext()){ 
                    if(i==0){ 
                        dataSql.append(it.next());
                    }else{ 
                        dataSql.append(","+it.next());
                    }
                    i++;
                }
                dataSql.append(" ) ");
            }
            if(criteria.getStartTime()!=null&&criteria.getEndTime()!=null){ 
                dataSql.append(" and create_time < "+criteria.getEndTime()+" and create_time > "+criteria.getStartTime());
            }
            dataSql.append("order by ");
            String[] sort = pageable.getSort().toString().split(",");
            for (int i = 0; i < sort.length; i++) { 
                String replace = sort[i].replace(':', ' ');
                if(i==0){ 
                    dataSql.append(replace);
                }else{ 
                    dataSql.append(","+replace);
                }
            }
            Query dataQuery = entityManager.createNativeQuery(dataSql.toString());
            dataQuery.setParameter("h_latitude",criteria.getLatitude());
            dataQuery.setParameter("h_longitude",criteria.getLongitude());
    // CriteriaQuery<Shop> query = builder.createQuery(Shop.class);
    
    
            List<Object[]> resultList = dataQuery.getResultList();
            List<Shop> shops = EntityUtils.castEntity(resultList,Shop.class,new Shop());
            int start = (int)pageable.getOffset();
            int end = (start + pageable.getPageSize()) > shops.size() ? shops.size() : (start + pageable.getPageSize());
            return new PageImpl<Shop>(shops.subList(start, end), pageable, shops.size());
        }
    }

3.ShopRepository继承拓展接口，添加该新自定义查询功能

public interface ShopRepository extends  JpaRepository<Shop, Long>,JpaSpecificationExecutor<Shop>, FindShopHasDistanceRepository<Shop, Long> { 
    }

此处命名规则前一篇文章[自定义Repository的实现坑][Repository]

用到一个辅助类EntityUtils

public class EntityUtils { 
        private static Logger logger = LoggerFactory.getLogger(EntityUtils.class);
    
        /** * 将数组数据转换为实体类 * 此处数组元素的顺序必须与实体类构造函数中的属性顺序一致 * * @param list 数组对象集合 * @param clazz 实体类 * @param <T> 实体类 * @param model 实例化的实体类 * @return 实体类集合 */
        public static <T> List<T> castEntity(List<Object[]> list, Class<T> clazz, Object model) throws ClassNotFoundException { 
            List<T> returnList = new ArrayList<T>();
            if (list.isEmpty()) { 
                return returnList;
            }
            //获取每个数组集合的元素个数
            Object[] co = list.get(0);
    
    
            //获取当前实体类的属性名、属性值、属性类别
            List<Map> attributeInfoList = getFiledsInfo(model);
            //创建属性类别数组
            Class[] c2 = new Class[attributeInfoList.size()];
    
            //2.获取此类的所有的公共构造方法
    		Constructor<?>[] constructors = clazz.getConstructors();
            
            //如果数组集合元素个数与实体类属性个数不一致则发生错误
            if (attributeInfoList.size() != co.length) { 
                return returnList;
            }
    // //根据属性类型确定构造方法
            for (int i = 0; i < attributeInfoList.size(); i++) { 
                    c2[i] = (Class) attributeInfoList.get(i).get("type");
            }
            try { 
                for (Object[] o : list) { 
    
                    Constructor<T> constructor = clazz.getConstructor(c2);
                    T t = constructor.newInstance(o);
                    returnList.add(t);
                }
            } catch (Exception ex) { 
                logger.error("实体数据转化为实体类发生异常：异常信息：{}", ex.getMessage());
                return returnList;
            }
            return returnList;
        }
    
        /** * 根据属性名获取属性值 * * @param fieldName 属性名 * @param modle 实体类 * @return 属性值 */
        private static Object getFieldValueByName(String fieldName, Object modle) { 
            try { 
                String firstLetter = fieldName.substring(0, 1).toUpperCase();
                String getter = "get" + firstLetter + fieldName.substring(1);
                Method method = modle.getClass().getMethod(getter, new Class[]{ });
                Object value = method.invoke(modle, new Object[]{ });
                return value;
            } catch (Exception e) { 
                return null;
            }
        }
    
        /** * 获取属性类型(type)，属性名(name)，属性值(value)的map组成的list * * @param model 实体类 * @return list集合 */
        private static List<Map> getFiledsInfo(Object model) { 
            Field[] fields = model.getClass().getDeclaredFields();
            List<Map> list = new ArrayList(fields.length);
            Map infoMap = null;
            for (int i = 0; i < fields.length; i++) { 
                infoMap = new HashMap(3);
                infoMap.put("type", fields[i].getType());
                infoMap.put("name", fields[i].getName());
                infoMap.put("value", getFieldValueByName(fields[i].getName(), model));
                list.add(infoMap);
            }
            return list;
        }
    }

然而并不可行。。。  
报错（反射异常argument type mismatch）  
[反射异常][Link 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMzM5Mzc0_size_16_color_FFFFFF_t_70]  
按照网上的说法debug一下。。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMzM5Mzc0_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMzM5Mzc0_size_16_color_FFFFFF_t_70 2]  
于是乎，，打算更改封装类属性，，就这样不可避免又进入一个坑。。。。

public class Shop implements Serializable { 
        @Id
        @JoinColumn(name = "id")
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        @NotNull(groups = Shop.Update.class)
        private BigInteger id;

id改为BigInteger类型  
查询结果如愿以偿，嘻嘻。  
以为ojbk了,然而，众所周知，bug是改不完的。。  
原生select可行，在对数据库进行改操作时，JPA提供的selectBy则再次报错。。。又是坑壁的反射异常argument type mismatch。。  
![不好使啊！！][20200108211724134.png]  
头大。。。搞不定，越看JPA越蒙蔽，索性放弃，思考了一下，干脆给Shop实体类再增加一个构造方法，并且改动一下EntityUtils 采用哪种构造方法的判断条件，，，时间紧迫，勉强解决一下。。  
1.Shop添加新的构造方法，使得BigInteger反射创建实例不报错（使得id可成功注入）

package me.zhengjie.modules.system.domain;
    
    import lombok.AllArgsConstructor;
    import lombok.Getter;
    import lombok.NoArgsConstructor;
    import lombok.Setter;
    import me.zhengjie.domain.Picture;
    import org.hibernate.annotations.CreationTimestamp;
    
    import javax.persistence.*;
    import javax.validation.constraints.NotNull;
    import java.io.Serializable;
    import java.math.BigDecimal;
    import java.math.BigInteger;
    import java.sql.Timestamp;
    import java.util.List;
    
    @Entity
    @Getter
    @Setter
    @Table(name="shop")
    @NoArgsConstructor
    @AllArgsConstructor
    public class Shop implements Serializable { 
        @Id
        @JoinColumn(name = "id")
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        @NotNull(groups = Shop.Update.class)
        private Long id;
        @JoinColumn(name = "channel_en")
        private String channel_en;
        @JoinColumn(name = "highlevelcategory_en")
        private String highlevelcategory_en;
        @JoinColumn(name = "subcategory_en")
        private String subcategory_en;
        @JoinColumn(name = "name")
        private String name;
        @JoinColumn(name = "star")
        private String star;
        @JoinColumn(name = "star_nr")
        private BigDecimal star_nr;
        @JoinColumn(name = "address")
        private String address;
        @JoinColumn(name = "latitude")
        private BigDecimal latitude;
        @JoinColumn(name = "longitude")
        private BigDecimal longitude;
        @JoinColumn(name = "phonecall")
        private String phonecall;
        @JoinColumn(name = "averagecost")
        private Integer averagecost;
        @JoinColumn(name = "businesshours")
        private String businesshours;
        @JoinColumn(name = "environmentscore")
        private BigDecimal environmentscore;
        @JoinColumn(name = "servicescore")
        private BigDecimal servicescore;
        @JoinColumn(name = "tastescore")
        private BigDecimal tastescore;
        @Column(name = "openclose",nullable = false)
        @NotNull
        private Boolean openclose;
        @Column(name = "create_time")
        @CreationTimestamp
        private Timestamp createTime;
    
    
    // @Column(name = "distance")
    @Transient
    private Double distance;
    
    
    // private List<Picture> photos;
    
        public @interface Update { }
    
        @Override
        public String toString() { 
            return "Shop{" +
                    "id=" + id +
                    ", channel_en='" + channel_en + '\'' +
                    ", highlevelcategory_en='" + highlevelcategory_en + '\'' +
                    ", subcategory_en='" + subcategory_en + '\'' +
                    ", name='" + name + '\'' +
                    ", star='" + star + '\'' +
                    ", star_nr=" + star_nr +
                    ", address='" + address + '\'' +
                    ", latitude=" + latitude +
                    ", longitude=" + longitude +
                    ", phonecall='" + phonecall + '\'' +
                    ", averagecost=" + averagecost +
                    ", businesshours='" + businesshours + '\'' +
                    ", createtime='" + createTime + '\'' +
                    ", environmentscore=" + environmentscore +
                    ", servicescore=" + servicescore +
                    ", tastescore=" + tastescore +
                    ", distance="+distance+
                    '}';
        }
    
        public Shop(BigInteger id,String channel_en, String highlevelcategory_en, String subcategory_en, String name, String star, BigDecimal star_nr, String address, BigDecimal latitude, BigDecimal longitude, String phonecall, Integer averagecost, String businesshours, BigDecimal environmentscore, BigDecimal servicescore, BigDecimal tastescore, @NotNull Boolean openclose, Timestamp createTime, Double distance) { 
            this.id = id.longValue();//BigInteger --》Long
            this.channel_en = channel_en;
            this.highlevelcategory_en = highlevelcategory_en;
            this.subcategory_en = subcategory_en;
            this.name = name;
            this.star = star;
            this.star_nr = star_nr;
            this.address = address;
            this.latitude = latitude;
            this.longitude = longitude;
            this.phonecall = phonecall;
            this.averagecost = averagecost;
            this.businesshours = businesshours;
            this.environmentscore = environmentscore;
            this.servicescore = servicescore;
            this.tastescore = tastescore;
            this.openclose = openclose;
            this.createTime = createTime;
            this.distance = distance;
        }
    }

2.修改方法类部分函数

package me.zhengjie.utils;
    
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    import java.lang.reflect.Constructor;
    import java.lang.reflect.Field;
    import java.lang.reflect.Method;
    import java.math.BigInteger;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    public class EntityUtils { 
        private static Logger logger = LoggerFactory.getLogger(EntityUtils.class);
    
        /** * 将数组数据转换为实体类 * 此处数组元素的顺序必须与实体类构造函数中的属性顺序一致 * * @param list 数组对象集合 * @param clazz 实体类 * @param <T> 实体类 * @param model 实例化的实体类 * @return 实体类集合 */
        public static <T> List<T> castEntity(List<Object[]> list, Class<T> clazz, Object model) throws ClassNotFoundException { 
            List<T> returnList = new ArrayList<T>();
            if (list.isEmpty()) { 
                return returnList;
            }
            //获取每个数组集合的元素个数
            Object[] co = list.get(0);
    
    
            //获取当前实体类的属性名、属性值、属性类别
            List<Map> attributeInfoList = getFiledsInfo(model);
            //创建属性类别数组
            Class[] c2 = new Class[attributeInfoList.size()];
    
            //2.获取此类的所有的公共构造方法
    		Constructor<?>[] constructors = clazz.getConstructors();
            ///
            for (int i = 0; i < co.length; i++) { 
                co[i].getClass().getName();
                System.out.println(co[i].getClass().getName());
    
            }
            //
            //如果数组集合元素个数与实体类属性个数不一致则发生错误
            if (attributeInfoList.size() != co.length) { 
                return returnList;
            }
    // //根据属性类型确定构造方法
            for (int i = 0; i < attributeInfoList.size(); i++) { 
                c2[i] = Class.forName(co[i].getClass().getName());
    // c2[i] = (Class) attributeInfoList.get(i).get("type");
            }
            try { 
                for (Object[] o : list) { 
    
                    Constructor<T> constructor = clazz.getConstructor(c2);
                    T t = constructor.newInstance(o);
                    returnList.add(t);
                }
            } catch (Exception ex) { 
                logger.error("实体数据转化为实体类发生异常：异常信息：{}", ex.getMessage());
                return returnList;
            }
            return returnList;
        }
    
        /** * 根据属性名获取属性值 * * @param fieldName 属性名 * @param modle 实体类 * @return 属性值 */
        private static Object getFieldValueByName(String fieldName, Object modle) { 
            try { 
                String firstLetter = fieldName.substring(0, 1).toUpperCase();
                String getter = "get" + firstLetter + fieldName.substring(1);
                Method method = modle.getClass().getMethod(getter, new Class[]{ });
                Object value = method.invoke(modle, new Object[]{ });
                return value;
            } catch (Exception e) { 
                return null;
            }
        }
    
        /** * 获取属性类型(type)，属性名(name)，属性值(value)的map组成的list * * @param model 实体类 * @return list集合 */
        private static List<Map> getFiledsInfo(Object model) { 
            Field[] fields = model.getClass().getDeclaredFields();
            List<Map> list = new ArrayList(fields.length);
            Map infoMap = null;
            for (int i = 0; i < fields.length; i++) { 
                infoMap = new HashMap(3);
                infoMap.put("type", fields[i].getType());
                infoMap.put("name", fields[i].getName());
                infoMap.put("value", getFieldValueByName(fields[i].getName(), model));
                list.add(infoMap);
            }
            return list;
        }
    }

ojbk…  
**但是很呆！！！**

**\*\*数据封装的店铺类中包含数据库表中并不存在的字段，对JPA不熟悉，完全不知如何下手，呜呜呜~~~~~~**\*\*  
按照spring Data JPA的思想应该是尽量减少原生sql的使用，奈何技术太菜，时间紧迫，最后还是用的原生来进行实现。踩坑无数。。。看到的兄弟指点一下正规的操作方式，万分感谢！！  
**跪求正确思路，，摸爬的菜鸡~~**

[distance]: https://img-blog.csdnimg.cn/20200108201533745.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMzM5Mzc0,size_16,color_FFFFFF,t_70
[20200108204602934.png]: https://img-blog.csdnimg.cn/20200108204602934.png
[Repository]: https://blog.csdn.net/qq_42339374/article/details/103876582
[Link 1]: https://www.iteye.com/problems/18555
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMzM5Mzc0_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200108210713601.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMzM5Mzc0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMzM5Mzc0_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200108210936650.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMzM5Mzc0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMzM5Mzc0_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200108211103799.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMzM5Mzc0,size_16,color_FFFFFF,t_70
[20200108211724134.png]: https://img-blog.csdnimg.cn/20200108211724134.png