Hibernate Spatial › Hibernate Spatial - Users

Problems with executing HQL with hibernate spatial

2 messages Options
Embed this post
Permalink
Coquelicot-2

Problems with executing HQL with hibernate spatial

Reply Threaded More More options
Print post
Permalink
Dear all,

I'm trying to find out why I am getting nowhere when querying my
database using HQL (I know that this is not a prescribed way but I
need to stick to this method).

The HQL I am tying to query looks like that:

=====

select distinct _NSIL_PRODUCT from
int_.nato.nc3a.nsili.server2.model.majiic.NSIL_PRODUCT _NSIL_PRODUCT
inner join _NSIL_PRODUCT.NSIL_PART _NSIL_PART inner join
_NSIL_PART.NSIL_COVERAGE _NSIL_COVERAGE where
Intersects(_NSIL_COVERAGE.spatialGeographicReferenceBox, :geoExp0) =
True

=====


where "geoExp0" is a named parameter defined as s Geometry object
created from 'POINT(X, Y)' WKT string.

It seems that Hibernate manages to parse the HQL tree but for some
reason, named parameter's "geoExp0" type is null, not GeometryUserType
(see last lines below):


=====

Aug 6, 2008 12:58:47 PM org.hibernate.hql.ast.QueryTranslatorImpl analyze
FINE: --- SQL AST ---
 \-[SELECT] QueryNode: 'SELECT'  querySpaces
(NSIL_COVERAGE,NSIL_PART,NSIL_PART_LIST,NSIL_PRODUCT)
    +-[SELECT_CLAUSE] SelectClause: '{select clause}'
    |  +-[DISTINCT] SqlNode: 'distinct'
    |  +-[ALIAS_REF] IdentNode: 'nsil_produ0_.id as id14_'
{alias=_NSIL_PRODUCT,
className=int_.nato.nc3a.nsili.server2.model.majiic.NSIL_PRODUCT,
tableAlias=nsil_produ0_}
    |  \-[SQL_TOKEN] SqlFragment: 'nsil_produ0_.NSIL_APPROVAL_ID as
NSIL2_14_, nsil_produ0_.NSIL_CARD_ID as NSIL3_14_,
nsil_produ0_.NSIL_FILE_ID as NSIL4_14_,
nsil_produ0_.NSIL_METADATASECURITY_ID as NSIL5_14_,
nsil_produ0_.NSIL_SECURITY_ID as NSIL6_14_'
    +-[FROM] FromClause: 'from' FromClause{level=1,
fromElementCounter=3, fromElements=3,
fromElementByClassAlias=[_NSIL_PART, _NSIL_COVERAGE, _NSIL_PRODUCT],
fromElementByTableAlias=[nsil_produ0_, nsil_cover3_, nsil_part2_],
fromElementsByPath=[_NSIL_PRODUCT.NSIL_PART,
_NSIL_PART.NSIL_COVERAGE], collectionJoinFromElementsByPath=[],
impliedElements=[]}
    |  \-[FROM_FRAGMENT] FromElement: 'NSIL_PRODUCT nsil_produ0_'
FromElement{explicit,not a collection join,not a fetch join,fetch
non-lazy properties,classAlias=_NSIL_PRODUCT,role=null,tableName=NSIL_PRODUCT,tableAlias=nsil_produ0_,origin=null,colums={,className=int_.nato.nc3a.nsili.server2.model.majiic.NSIL_PRODUCT}}
    |     \-[JOIN_FRAGMENT] FromElement: 'inner join NSIL_PART_LIST
nsil_part1_ on nsil_produ0_.id=nsil_part1_.id inner join NSIL_PART
nsil_part2_ on nsil_part1_.NSIL_PART_ID=nsil_part2_.id'
FromElement{explicit,not a collection join,not a fetch join,fetch
non-lazy properties,classAlias=_NSIL_PART,role=int_.nato.nc3a.nsili.server2.model.majiic.NSIL_PRODUCT.NSIL_PART,tableName=NSIL_PART,tableAlias=nsil_part2_,origin=NSIL_PRODUCT
nsil_produ0_,colums={nsil_produ0_.id
,className=int_.nato.nc3a.nsili.server2.model.majiic.NSIL_PART}}
    |        \-[JOIN_FRAGMENT] FromElement: 'inner join NSIL_COVERAGE
nsil_cover3_ on nsil_part2_.NSIL_COVERAGE_ID=nsil_cover3_.id'
FromElement{explicit,not a collection join,not a fetch join,fetch
non-lazy properties,classAlias=_NSIL_COVERAGE,role=null,tableName=NSIL_COVERAGE,tableAlias=nsil_cover3_,origin=inner
join NSIL_PART_LIST nsil_part1_ on nsil_produ0_.id=nsil_part1_.id
inner join NSIL_PART nsil_part2_ on
nsil_part1_.NSIL_PART_ID=nsil_part2_.id,colums={nsil_part2_.NSIL_COVERAGE_ID
,className=int_.nato.nc3a.nsili.server2.model.majiic.NSIL_COVERAGE}}
    \-[WHERE] SqlNode: 'where'
       \-[EQ] BinaryLogicOperatorNode: '='
          +-[METHOD_CALL] MethodNode: '('
          |  +-[METHOD_NAME] IdentNode: 'Intersects' {originalText=Intersects}
          |  \-[EXPR_LIST] SqlNode: 'exprList'
          |     +-[DOT] DotNode:
'nsil_cover3_.spatialGeographicReferenceBox'
{propertyName=spatialGeographicReferenceBox,dereferenceType=4,propertyPath=spatialGeographicReferenceBox,path=_NSIL_COVERAGE.spatialGeographicReferenceBox,tableAlias=nsil_cover3_,className=int_.nato.nc3a.nsili.server2.model.majiic.NSIL_COVERAGE,classAlias=_NSIL_COVERAGE}
          |     |  +-[ALIAS_REF] IdentNode: 'nsil_cover3_.id'
{alias=_NSIL_COVERAGE,
className=int_.nato.nc3a.nsili.server2.model.majiic.NSIL_COVERAGE,
tableAlias=nsil_cover3_}
          |     |  \-[IDENT] IdentNode:
'spatialGeographicReferenceBox'
{originalText=spatialGeographicReferenceBox}
          |     \-[NAMED_PARAM] ParameterNode: '?' {name=geoExp0,
expectedType=null}
          \-[TRUE] BooleanLiteralNode: '1'

=====

I am very certain that when I define named parameters in the Query
object, I supply a corect one. Does anyone have a clue what can be
wrong?

big thanks,


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

Re: Problems with executing HQL with hibernate spatial

Reply Threaded More More options
Print post
Permalink
Hi,

You should do something like this:


        //create your query
        Query q = session.createQuery(< your hql > );
        Geometry geom = < your parameter value>
        //now first create a custom type
        Type geometryType = new CustomType(GeometryUserType.class, null);
        q.setParameter(:geoExp0, geom, geometryType);

You need to pass by CustomType because internally Hibernate can't  
figure out what Hibernate Type to use for Geometry objects.

Regards

Karel Maesen





On 06 Aug 2008, at 15:31, Coquelicot wrote:

> Dear all,
>
> I'm trying to find out why I am getting nowhere when querying my
> database using HQL (I know that this is not a prescribed way but I
> need to stick to this method).
>
> The HQL I am tying to query looks like that:
>
> =====
>
> select distinct _NSIL_PRODUCT from
> int_.nato.nc3a.nsili.server2.model.majiic.NSIL_PRODUCT _NSIL_PRODUCT
> inner join _NSIL_PRODUCT.NSIL_PART _NSIL_PART inner join
> _NSIL_PART.NSIL_COVERAGE _NSIL_COVERAGE where
> Intersects(_NSIL_COVERAGE.spatialGeographicReferenceBox, :geoExp0) =
> True
>
> =====
>
>
> where "geoExp0" is a named parameter defined as s Geometry object
> created from 'POINT(X, Y)' WKT string.
>
> It seems that Hibernate manages to parse the HQL tree but for some
> reason, named parameter's "geoExp0" type is null, not GeometryUserType
> (see last lines below):
>
>
> =====
>
> Aug 6, 2008 12:58:47 PM org.hibernate.hql.ast.QueryTranslatorImpl  
> analyze
> FINE: --- SQL AST ---
>  \-[SELECT] QueryNode: 'SELECT'  querySpaces
> (NSIL_COVERAGE,NSIL_PART,NSIL_PART_LIST,NSIL_PRODUCT)
>     +-[SELECT_CLAUSE] SelectClause: '{select clause}'
>     |  +-[DISTINCT] SqlNode: 'distinct'
>     |  +-[ALIAS_REF] IdentNode: 'nsil_produ0_.id as id14_'
> {alias=_NSIL_PRODUCT,
> className=int_.nato.nc3a.nsili.server2.model.majiic.NSIL_PRODUCT,
> tableAlias=nsil_produ0_}
>     |  \-[SQL_TOKEN] SqlFragment: 'nsil_produ0_.NSIL_APPROVAL_ID as
> NSIL2_14_, nsil_produ0_.NSIL_CARD_ID as NSIL3_14_,
> nsil_produ0_.NSIL_FILE_ID as NSIL4_14_,
> nsil_produ0_.NSIL_METADATASECURITY_ID as NSIL5_14_,
> nsil_produ0_.NSIL_SECURITY_ID as NSIL6_14_'
>     +-[FROM] FromClause: 'from' FromClause{level=1,
> fromElementCounter=3, fromElements=3,
> fromElementByClassAlias=[_NSIL_PART, _NSIL_COVERAGE, _NSIL_PRODUCT],
> fromElementByTableAlias=[nsil_produ0_, nsil_cover3_, nsil_part2_],
> fromElementsByPath=[_NSIL_PRODUCT.NSIL_PART,
> _NSIL_PART.NSIL_COVERAGE], collectionJoinFromElementsByPath=[],
> impliedElements=[]}
>     |  \-[FROM_FRAGMENT] FromElement: 'NSIL_PRODUCT nsil_produ0_'
> FromElement{explicit,not a collection join,not a fetch join,fetch
> non-lazy  
> properties,classAlias=_NSIL_PRODUCT,role=null,tableName=NSIL_PRODUCT,t
> ableAlias=nsil_produ0_,origin=null,colums=
> {,className=int_.nato.nc3a.nsili.server2.model.majiic.NSIL_PRODUCT}}
>     |     \-[JOIN_FRAGMENT] FromElement: 'inner join NSIL_PART_LIST
> nsil_part1_ on nsil_produ0_.id=nsil_part1_.id inner join NSIL_PART
> nsil_part2_ on nsil_part1_.NSIL_PART_ID=nsil_part2_.id'
> FromElement{explicit,not a collection join,not a fetch join,fetch
> non-lazy  
> properties,classAlias=_NSIL_PART,role=int_.nato.nc3a.nsili.server2.mod
> el.majiic.NSIL_PRODUCT.NSIL_PART,tableName=NSIL_PART,tableAlias=nsil_p
> art2_,origin=NSIL_PRODUCT
> nsil_produ0_,colums={nsil_produ0_.id
> ,className=int_.nato.nc3a.nsili.server2.model.majiic.NSIL_PART}}
>     |        \-[JOIN_FRAGMENT] FromElement: 'inner join NSIL_COVERAGE
> nsil_cover3_ on nsil_part2_.NSIL_COVERAGE_ID=nsil_cover3_.id'
> FromElement{explicit,not a collection join,not a fetch join,fetch
> non-lazy  
> properties,classAlias=_NSIL_COVERAGE,role=null,tableName=NSIL_COVERAGE
> ,tableAlias=nsil_cover3_,origin=inner
> join NSIL_PART_LIST nsil_part1_ on nsil_produ0_.id=nsil_part1_.id
> inner join NSIL_PART nsil_part2_ on
> nsil_part1_.NSIL_PART_ID=nsil_part2_.id,colums=
> {nsil_part2_.NSIL_COVERAGE_ID
> ,className=int_.nato.nc3a.nsili.server2.model.majiic.NSIL_COVERAGE}}
>     \-[WHERE] SqlNode: 'where'
>        \-[EQ] BinaryLogicOperatorNode: '='
>           +-[METHOD_CALL] MethodNode: '('
>           |  +-[METHOD_NAME] IdentNode:  
> 'Intersects' {originalText=Intersects}
>           |  \-[EXPR_LIST] SqlNode: 'exprList'
>           |     +-[DOT] DotNode:
> 'nsil_cover3_.spatialGeographicReferenceBox'
> {propertyName=spatialGeographicReferenceBox,dereferenceType=4,property
> Path=spatialGeographicReferenceBox,path=_NSIL_COVERAGE.spatialGeograph
> icReferenceBox,tableAlias=nsil_cover3_,className=int_.nato.nc3a.nsili.
> server2.model.majiic.NSIL_COVERAGE,classAlias=_NSIL_COVERAGE}
>           |     |  +-[ALIAS_REF] IdentNode: 'nsil_cover3_.id'
> {alias=_NSIL_COVERAGE,
> className=int_.nato.nc3a.nsili.server2.model.majiic.NSIL_COVERAGE,
> tableAlias=nsil_cover3_}
>           |     |  \-[IDENT] IdentNode:
> 'spatialGeographicReferenceBox'
> {originalText=spatialGeographicReferenceBox}
>           |     \-[NAMED_PARAM] ParameterNode: '?' {name=geoExp0,
> expectedType=null}
>           \-[TRUE] BooleanLiteralNode: '1'
>
> =====
>
> I am very certain that when I define named parameters in the Query
> object, I supply a corect one. Does anyone have a clue what can be
> wrong?
>
> big thanks,
>
>
> --
> Lukasz
> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/ 
> hibernatespatial-users

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users
Free Embeddable Forum Powered by Nabble Help