JPA and distance function

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

JPA and distance function

Xavier Vanderstukken

First of all I want to thank you for the great job you are doing on this project.
I am building a JPA application using hibernate and your spatial extension.
I am able to insert and retrieve spatial data using very simple query.
But now I don't find a way to call the distance function using jpql query language.
jpql has a FUNC reserved word to call a database specific function so I try to call sdo_distance with this mechanism.
This was working when I don't use Oracle10G spatial dialect but your framework seems to disable the "FUNC" mechanism.
Instead I try to use the distance function but I receive the following error:

/* my model object */
public class Location extends BaseModel {


@Type(type="org.hibernatespatial.GeometryUserType")
private Point point;


/* my dao */
TypedQuery<QuizzMarketNoDetail> query = getEntityManager().createQuery("select quizz FROM Quizz quizz JOIN quizz.steps steps JOIN steps.questions questions WHERE quizz.language=:language order by distance(:location, questions.coordinate.point)", QuizzMarketNoDetail.class);
query.setParameter("language",language);
query.setParameter("location",location.getPoint());


And the error and logs:

28700 [tomcat-http--40] DEBUG
org.hibernate.hql.ast.QueryTranslatorImpl - HQL: select quizz FROM
com.ep.testjersey.model.Quizz quizz JOIN quizz.steps steps JOIN steps.questions
questions WHERE quizz.language=:language order by distance(:location,
questions.coordinate.point)
28700 [tomcat-http--40] DEBUG
org.hibernate.hql.ast.QueryTranslatorImpl - SQL: select quizz0_.databaseId as
databaseId9_, quizz0_.creationDate as creation2_9_, quizz0_.id as id9_,
quizz0_.alreadyPlayed as alreadyP4_9_, quizz0_.distance as distance9_,
quizz0_.editor_databaseId as editor33_9_, quizz0_.imageUrl as imageUrl9_,
quizz0_.lang as lang9_, quizz0_.location_databaseId as location34_9_,
quizz0_.numberOfQuestions as numberOf8_9_, quizz0_.properties as properties9_,
quizz0_.quizzName as quizzName9_, quizz0_.quizzVersion as quizzVe11_9_,
quizz0_.radius as radius9_, quizz0_.ratingScore as ratingS13_9_,
quizz0_.targetScan as targetScan9_, quizz0_.description as descrip15_9_,
quizz0_.duration as duration9_, quizz0_.editorName as editorName9_,
quizz0_.imageName as imageName9_, quizz0_.numberOfPlays as numberO19_9_,
quizz0_.numberOfSteps as numberO20_9_, quizz0_.quizzLanguageId as quizzLa21_9_,
quizz0_.scale as scale9_, quizz0_.status as status9_, quizz0_.QRCodeDescription
as QRCodeD24_9_, quizz0_.locationName as locatio25_9_, quizz0_.playHistoryId as
playHis26_9_, quizz0_.proximityRestricted as proximi27_9_,
quizz0_.proximityRestriction as proximi28_9_, quizz0_.quizzStatus as
quizzSt29_9_, quizz0_.rewardDescription as rewardD30_9_, quizz0_.rewarded as
rewarded9_, quizz0_.score as score9_, quizz0_.user_databaseId as user35_9_ from
Quizz quizz0_ inner join Quizz_Step steps1_ on
quizz0_.databaseId=steps1_.Quizz_databaseId inner join Step step2_ on
steps1_.steps_databaseId=step2_.databaseId inner join Step_Question questions3_
on step2_.databaseId=questions3_.Step_databaseId inner join Question question4_
on questions3_.questions_databaseId=question4_.databaseId, Location location5_
where question4_.coordinate_databaseId=location5_.databaseId and quizz0_.lang=?
order by
MDSYS.OGC_DISTANCE(MDSYS.ST_GEOMETRY(?),MDSYS.ST_GEOMETRY(location5_.point))
28700 [tomcat-http--40] DEBUG org.hibernate.hql.ast.ErrorCounter -
throwQueryException() : no errors
28731 [tomcat-http--40] TRACE
org.hibernate.engine.query.HQLQueryPlan - HQL param location recognition took 16
mills (select quizz FROM Quizz quizz JOIN quizz.steps steps JOIN steps.questions
questions WHERE quizz.language=:language order by distance(:location,
questions.coordinate.point))
28747 [tomcat-http--40] TRACE
org.hibernate.engine.query.QueryPlanCache - located HQL query plan in cache
(select quizz FROM Quizz quizz JOIN quizz.steps steps JOIN steps.questions
questions WHERE quizz.language=:language order by distance(:location,
questions.coordinate.point))
28747 [tomcat-http--40] TRACE
org.hibernate.engine.query.QueryPlanCache - located HQL query plan in cache
(select quizz FROM Quizz quizz JOIN quizz.steps steps JOIN steps.questions
questions WHERE quizz.language=:language order by distance(:location,
questions.coordinate.point))
28778 [tomcat-http--40] TRACE
org.springframework.transaction.support.TransactionSynchronizationManager -
Retrieved value [org.springframework.orm.jpa.EntityManagerHolder@429c89] for key
[org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean@132efbc]
bound to thread [tomcat-http--40]
28794 [tomcat-http--40] TRACE
org.hibernate.engine.query.QueryPlanCache - located HQL query plan in cache
(select quizz FROM Quizz quizz JOIN quizz.steps steps JOIN steps.questions
questions WHERE quizz.language=:language order by distance(:location,
questions.coordinate.point))
28794 [tomcat-http--40] TRACE
org.hibernate.engine.query.HQLQueryPlan - find: select quizz FROM Quizz quizz
JOIN quizz.steps steps JOIN steps.questions questions WHERE
quizz.language=:language order by distance(:location,
questions.coordinate.point)
28809 [tomcat-http--40] TRACE org.hibernate.engine.QueryParameters
- named parameters: {location=POINT (0 0), language=fr}
28825 [tomcat-http--40] DEBUG org.hibernate.jdbc.AbstractBatcher -
about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
28825 [tomcat-http--40] DEBUG org.hibernate.SQL - select
quizz0_.databaseId as databaseId9_, quizz0_.creationDate as creation2_9_,
quizz0_.id as id9_, quizz0_.alreadyPlayed as alreadyP4_9_, quizz0_.distance as
distance9_, quizz0_.editor_databaseId as editor33_9_, quizz0_.imageUrl as
imageUrl9_, quizz0_.lang as lang9_, quizz0_.location_databaseId as
location34_9_, quizz0_.numberOfQuestions as numberOf8_9_, quizz0_.properties as
properties9_, quizz0_.quizzName as quizzName9_, quizz0_.quizzVersion as
quizzVe11_9_, quizz0_.radius as radius9_, quizz0_.ratingScore as ratingS13_9_,
quizz0_.targetScan as targetScan9_, quizz0_.description as descrip15_9_,
quizz0_.duration as duration9_, quizz0_.editorName as editorName9_,
quizz0_.imageName as imageName9_, quizz0_.numberOfPlays as numberO19_9_,
quizz0_.numberOfSteps as numberO20_9_, quizz0_.quizzLanguageId as quizzLa21_9_,
quizz0_.scale as scale9_, quizz0_.status as status9_, quizz0_.QRCodeDescription
as QRCodeD24_9_, quizz0_.locationName as locatio25_9_, quizz0_.playHistoryId as
playHis26_9_, quizz0_.proximityRestricted as proximi27_9_,
quizz0_.proximityRestriction as proximi28_9_, quizz0_.quizzStatus as
quizzSt29_9_, quizz0_.rewardDescription as rewardD30_9_, quizz0_.rewarded as
rewarded9_, quizz0_.score as score9_, quizz0_.user_databaseId as user35_9_ from
Quizz quizz0_ inner join Quizz_Step steps1_ on
quizz0_.databaseId=steps1_.Quizz_databaseId inner join Step step2_ on
steps1_.steps_databaseId=step2_.databaseId inner join Step_Question questions3_
on step2_.databaseId=questions3_.Step_databaseId inner join Question question4_
on questions3_.questions_databaseId=question4_.databaseId, Location location5_
where question4_.coordinate_databaseId=location5_.databaseId and quizz0_.lang=?
order by
MDSYS.OGC_DISTANCE(MDSYS.ST_GEOMETRY(?),MDSYS.ST_GEOMETRY(location5_.point))
28825 [tomcat-http--40] TRACE org.hibernate.jdbc.AbstractBatcher -
preparing statement
28825 [tomcat-http--40] TRACE
org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [1] as
[VARCHAR] - fr
28825 [tomcat-http--40] TRACE
org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [2] as
[VARBINARY] - POINT (0 0)
28841 [tomcat-http--40] TRACE
org.hibernate.util.SerializationHelper - Starting serialization of object [POINT
(0 0)]
28856 [tomcat-http--40] TRACE org.hibernate.loader.Loader - Bound
[3] parameters total
28872 [tomcat-http--40] DEBUG org.hibernate.jdbc.AbstractBatcher -
about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
28872 [tomcat-http--40] TRACE org.hibernate.jdbc.AbstractBatcher -
closing statement
28872 [tomcat-http--40] DEBUG
org.hibernate.util.JDBCExceptionReporter - could not execute query [select
quizz0_.databaseId as databaseId9_, quizz0_.creationDate as creation2_9_,
quizz0_.id as id9_, quizz0_.alreadyPlayed as alreadyP4_9_, quizz0_.distance as
distance9_, quizz0_.editor_databaseId as editor33_9_, quizz0_.imageUrl as
imageUrl9_, quizz0_.lang as lang9_, quizz0_.location_databaseId as
location34_9_, quizz0_.numberOfQuestions as numberOf8_9_, quizz0_.properties as
properties9_, quizz0_.quizzName as quizzName9_, quizz0_.quizzVersion as
quizzVe11_9_, quizz0_.radius as radius9_, quizz0_.ratingScore as ratingS13_9_,
quizz0_.targetScan as targetScan9_, quizz0_.description as descrip15_9_,
quizz0_.duration as duration9_, quizz0_.editorName as editorName9_,
quizz0_.imageName as imageName9_, quizz0_.numberOfPlays as numberO19_9_,
quizz0_.numberOfSteps as numberO20_9_, quizz0_.quizzLanguageId as quizzLa21_9_,
quizz0_.scale as scale9_, quizz0_.status as status9_, quizz0_.QRCodeDescription
as QRCodeD24_9_, quizz0_.locationName as locatio25_9_, quizz0_.playHistoryId as
playHis26_9_, quizz0_.proximityRestricted as proximi27_9_,
quizz0_.proximityRestriction as proximi28_9_, quizz0_.quizzStatus as
quizzSt29_9_, quizz0_.rewardDescription as rewardD30_9_, quizz0_.rewarded as
rewarded9_, quizz0_.score as score9_, quizz0_.user_databaseId as user35_9_ from
Quizz quizz0_ inner join Quizz_Step steps1_ on
quizz0_.databaseId=steps1_.Quizz_databaseId inner join Step step2_ on
steps1_.steps_databaseId=step2_.databaseId inner join Step_Question questions3_
on step2_.databaseId=questions3_.Step_databaseId inner join Question question4_
on questions3_.questions_databaseId=question4_.databaseId, Location location5_
where question4_.coordinate_databaseId=location5_.databaseId and quizz0_.lang=?
order by
MDSYS.OGC_DISTANCE(MDSYS.ST_GEOMETRY(?),MDSYS.ST_GEOMETRY(location5_.point))]
java.sql.SQLSyntaxErrorException:
ORA-00932: inconsistent datatypes: expected NUMBER got BINARY

     
_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users
Reply | Threaded
Open this post in threaded view
|

Re: JPA and distance function

Karel Maesen
Administrator
Hello Xavier,

On 17 Aug 2011, at 21:46, Xavier Vanderstukken wrote:

>
> jpql has a FUNC reserved word to call a database specific function so I try to call sdo_distance with this mechanism.
> This was working when I don't use Oracle10G spatial dialect but your framework seems to disable the "FUNC" mechanism.

I wasn't aware of this. How is this manifested? What SQL gets generated?

> Instead I try to use the distance function but I receive the following error:
>
>

> /* my dao */
> TypedQuery<QuizzMarketNoDetail> query = getEntityManager().createQuery("select quizz FROM Quizz quizz JOIN quizz.steps steps JOIN steps.questions questions WHERE quizz.language=:language order by distance(:location, questions.coordinate.point)", QuizzMarketNoDetail.class);
> query.setParameter("language",language);
> query.setParameter("location",location.getPoint());
>


I think this last statement is the source of the trouble. The setParameter() method can't determine the Hibernate Type to use for Point and thus which marshalling/unmarshalling to do. Hence, the error.  For this reason, in HQL you need to specify the type (GeometryUserType) in the setParameter() method of Hibernate Query (see the HS tutorial for an example). This will be fixed in Hibernate 4 (better integration between Hiberate Core and Hibernate Spatial). Would it be possible to use HQL, and verify if it works when the type is specified in the setParameter() method?

Regards,

Karel

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users