使用QueryDSL为了查询近距离点的数据库 [英] Using QueryDSL in order to query a database for close distance points
问题描述
在我的应用程序中有以下实体：
成员
FamilyAdvertisment
地址
在成员实体中：
@OneToOne（cascade = CascadeType.ALL）
私有地址;
...
@OneToMany（fetch = FetchType.LAZY，mappedBy =member）
private List< Advertisement>广告;
在 Advertisement entity：
@NotNull
@ManyToOne（fetch = FetchType.LAZY）
私人会员;
完整地址实体：
@Entity
公共类地址{
private String formattedAddress;
私人双纬度;
私人双倍经度;
}
我试图找到所有那些FamilyAdvertisement实例，地址在20KM以内。
以下是我想到的：
QFamilyAdvertisement qFamilyAdvertisement = QFamilyAdvertisement.familyAdvertisement;
NumberPath< Double> lat = qFamilyAdvertisement.member.address.latitude; // NPE
NumberPath< Double> lng = qFamilyAdvertisement.member.address.longitude;
NumberPath< Double>距离=空;
NumberExpression< Double>公式=
（cos（radians（Expressions.constant（requiredAddress.getLatitude（））））
.multiply（cos（radians（lat））
.multiply（cos（radians（ ls）.subtract（radians（Expressions.constant（requiredAddress.getLongitude（））））
.add（sin（radians（Expressions.constant（requiredAddress.getLatitude（））））
.multiply（sin （radians（lat））））））））
.multiply（Expressions.constant（6371）））;
列出<家庭广告> foundFamilyAdvertisements = from（qFamilyAdvertisement.member.address）.where（formula.as（distance）.lt（20））。list（qFamilyAdvertisement）;
但是，我似乎错误地使用NumberPath类，因为我一直在获取NPE。任何人都可以帮我查询我的查询吗？
$ b
edit ：我已经改变了我的FamilyAdvertisement实体，如下所示：
@NotNull
@ManyToOne（fetch = FetchType.LAZY）
@QueryInit（地址）
私人会员会员;
现在我得到以下异常：
java.lang.IllegalArgumentException：连接只允许使用根路径：familyAdvertisement.member.address
com.mysema.query.DefaultQueryMetadata.ensureRoot（DefaultQueryMetadata.java:208）
com.mysema.query.DefaultQueryMetadata.validateJoin（DefaultQueryMetadata.java:132）
com.mysema.query.DefaultQueryMetadata.addJoin（DefaultQueryMetadata.java:118）
com.mysema.query.DefaultQueryMetadata .addJoin（DefaultQueryMetadata.java:110）
com.mysema.query.support.QueryMixin.from（QueryMixin.java:161）
com.mysema.query.jpa.JPQLQueryBase.from（JPQLQueryBase.java ：96）
com.mysema.query.jpa.impl.JPAQuery.from（JPAQuery.java:30）
org.springframework.data.jpa.repository.support.Querydsl.createQuery（Querydsl.java ：88）
org.springframework.data.jpa.repository.support.QueryDslRepositorySupport.from（QueryDslRepositorySupport.java:94）
com .bignibou.repository.FamilyAdvertisementRepositoryImpl.performFamilyAdvertisementSearch（FamilyAdvertisementRepositoryImpl.java:64）
64行是这一行：
列表<家庭广告> foundFamilyAdvertisements = from（qFamilyAdvertisement.member.address）.where（formula.as（distance）.lt（20））。list（qFamilyAdvertisement）;
任何线索现在有什么不对？
edit2 ：我忘了提及 FamilyAdvertisement 扩展广告，并且 member 变量位于 Advertisement 中。
edit3 strong>：这是我试图用QueryDSL重现的SQL：
select * from family_advertisement内部联接成员m
on a.member = m.id
其中m.address
in（
SELECT id
FROM地址其中
6371 *
acos（cos （弧度（48.8558966））
* cos（弧度（纬度））
* cos（弧度（经度） - 弧度（2.3622728））
+ sin（弧度（48.8558966））
* sin（弧度（纬度））
）<20）;
我试过这样的事情：
列表与LT; FamilyAdvertisement> （qAddress.address）.where（formula.lt（20）））$ b $（） b
）.list（qFamilyAdvertisement）;
其中公式如上所示，但我不确定如何在QueryDSL中表示不相关的子查询，特别是在上面的运算符似乎是有问题的...
edit4 ：
以下子查询现在可以运行：
List< FamilyAdvertisement> （QAddress.address）.where（formula.lt（20））.list（QAddress.address））中的新的JPASubQuery（）。新的JPASubQuery ）.LIST（qFamilyAdvertisement）;
>
qFamilyAdvertisement.member.address.latitude;
请在这里阅读更多关于Querydsl http://www.querydsl.com/static/querydsl/3.1.0/reference/html/ch03s04 .html＃d0e1699
I have the following entities in my application:
MemberFamilyAdvertismentAddress
In Member entity:
@OneToOne(cascade=CascadeType.ALL)
private Address address;
...
@OneToMany(fetch = FetchType.LAZY, mappedBy = "member")
private List<Advertisement> advertisements;
In Advertisement entity:
@NotNull
@ManyToOne(fetch = FetchType.LAZY)
private Member member;
Full Address entity:
@Entity
public class Address {
private String formattedAddress;
private double latitude;
private double longitude;
}
I am trying to find all those FamilyAdvertisement instances whose member has an adress within 20KM of the required address.
Here is what I have come up with:
QFamilyAdvertisement qFamilyAdvertisement = QFamilyAdvertisement.familyAdvertisement;
NumberPath<Double> lat = qFamilyAdvertisement.member.address.latitude;//NPE
NumberPath<Double> lng = qFamilyAdvertisement.member.address.longitude;
NumberPath<Double> distance = null;
NumberExpression<Double> formula =
(acos(cos(radians(Expressions.constant(requiredAddress.getLatitude())))
.multiply(cos(radians(lat))
.multiply(cos(radians(lng).subtract(radians(Expressions.constant(requiredAddress.getLongitude())))
.add(sin(radians(Expressions.constant(requiredAddress.getLatitude())))
.multiply(sin(radians(lat))))))))
.multiply(Expressions.constant(6371)));
List<FamilyAdvertisement> foundFamilyAdvertisements = from(qFamilyAdvertisement.member.address).where(formula.as(distance).lt(20)).list(qFamilyAdvertisement);
However, it seems that I am incorrectly using the NumberPath class as I keep getting a NPE. Can anyone please help me get my query right?
edit: I have altered my FamilyAdvertisement entity as follows:
@NotNull
@ManyToOne(fetch = FetchType.LAZY)
@QueryInit("address")
private Member member;
I now get the following exception:
java.lang.IllegalArgumentException: Only root paths are allowed for joins : familyAdvertisement.member.address
com.mysema.query.DefaultQueryMetadata.ensureRoot(DefaultQueryMetadata.java:208)
com.mysema.query.DefaultQueryMetadata.validateJoin(DefaultQueryMetadata.java:132)
com.mysema.query.DefaultQueryMetadata.addJoin(DefaultQueryMetadata.java:118)
com.mysema.query.DefaultQueryMetadata.addJoin(DefaultQueryMetadata.java:110)
com.mysema.query.support.QueryMixin.from(QueryMixin.java:161)
com.mysema.query.jpa.JPQLQueryBase.from(JPQLQueryBase.java:96)
com.mysema.query.jpa.impl.JPAQuery.from(JPAQuery.java:30)
org.springframework.data.jpa.repository.support.Querydsl.createQuery(Querydsl.java:88)
org.springframework.data.jpa.repository.support.QueryDslRepositorySupport.from(QueryDslRepositorySupport.java:94)
com.bignibou.repository.FamilyAdvertisementRepositoryImpl.performFamilyAdvertisementSearch(FamilyAdvertisementRepositoryImpl.java:64)
Line 64 is this one:
List<FamilyAdvertisement> foundFamilyAdvertisements = from(qFamilyAdvertisement.member.address).where(formula.as(distance).lt(20)).list(qFamilyAdvertisement);
Any clue what is wrong now?
edit2: I forgot to mention that FamilyAdvertisement extends Advertisement and that the member variable is in Advertisement.
edit3: Here is the SQL I am trying to reproduce with QueryDSL:
select * from family_advertisement a inner join member m
on a.member = m.id
where m.address
in (
SELECT id
FROM address where
6371 *
acos( cos( radians(48.8558966) )
* cos( radians( latitude ) )
* cos( radians( longitude ) - radians(2.3622728) )
+ sin( radians(48.8558966) )
* sin( radians( latitude ) )
) < 20);
I have tried something like this:
List<FamilyAdvertisement> foundFamilyAdvertisements = from(qFamilyAdvertisement).where(qFamilyAdvertisement.member.address.in(
new JPASubQuery().from(QAddress.address).where(formula.lt(20)))
).list(qFamilyAdvertisement);
where formula is given above but I am not sure how to express a noncorrelated subquery in QueryDSL and especially the in operator above seems to be problematical...
edit4:
The following subquery now works:
List<FamilyAdvertisement> foundFamilyAdvertisements =
from(qFamilyAdvertisement).where(qFamilyAdvertisement.member.address.in(new JPASubQuery().from(QAddress.address).where(formula.lt(20)).list(QAddress.address))).list(qFamilyAdvertisement);
This path is too long for eager initialization
qFamilyAdvertisement.member.address.latitude;
Please read here more about path initialization in Querydsl http://www.querydsl.com/static/querydsl/3.1.0/reference/html/ch03s04.html#d0e1699
这篇关于使用QueryDSL为了查询近距离点的数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
