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，并直接封装给前台。同时实现动态查询以及分页和排序。
一个十分简单的需求-------但是我突然搞不定了。。。。。。
最开始打算这样做————再封装一层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> {
}- 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);
}
但是还是无法动态改变排序的字段，而且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的实现坑
用到一个辅助类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）
反射异常
按照网上的说法debug一下。。
于是乎，，打算更改封装类属性，，就这样不可避免又进入一个坑。。。。
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。。
头大。。。搞不定，越看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的使用，奈何技术太菜，时间紧迫，最后还是用的原生来进行实现。踩坑无数。。。看到的兄弟指点一下正规的操作方式，万分感谢！！
跪求正确思路，，摸爬的菜鸡~~
8281
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
