Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 11 posts ] 
Author Message
 Post subject: Oracle Spatial and createSQLQuery
PostPosted: Fri Dec 15, 2006 10:10 am 
Newbie

Joined: Fri Dec 15, 2006 6:08 am
Posts: 18
Location: France
Hi,

I have a problem with oracle spatial and hibernate createSQLQuery method.

Here is an example :
I have a table ALARM with a column of type SDO_GEOMETRY called geometry.

When I'm trying to get the column value with the following code :
session = getSession();
tx = session.beginTransaction();

String sql = "select a.geometry from alarm a where a.oid = 1";
SQLQuery query = session.createSQLQuery(sql);

Geometry polygon = (Geometry) query.uniqueResult();
commitAndClose(tx, session);


then the following exception is thrown by the query.uniqueResult() method:
org.hibernate.MappingException: No Dialect mapping for JDBC type: 2002


If I try to get the geometry with a hql query, it works :
String hql = "select a.geometry from Alarm a where a.oid = 1";
Query query = session.createQuery(hql);
Geometry polygon = (Geometry) query.uniqueResult();



But I would like to use an oracle spatial operator (SDO_UTIL.CIRCLE_POLYGON)
in other queries so I need to use an sql query (or it's possible in HQL?).

I ever tried to create my own dialect but it's not working :
public class OracleSpatialDialect extends Oracle9Dialect {
public OracleSpatialDialect() {
super();
registerColumnType(Types.STRUCT, "sdo_geometry");
}
}


Any ideas?

Thanks

Hibernate version:
3.2

Code between sessionFactory.openSession() and session.close():
session = getSession();
tx = session.beginTransaction();

String sql = "select a.geometry from alarm a where a.oid = 1";
SQLQuery query = session.createSQLQuery(sql);

Geometry polygon = (Geometry) query.uniqueResult();
commitAndClose(tx, session);

Full stack trace of any exception that occurs:
Caused by: org.hibernate.MappingException: No Dialect mapping for JDBC type: 2002
at org.hibernate.dialect.TypeNames.get(TypeNames.java:56)
at org.hibernate.dialect.TypeNames.get(TypeNames.java:81)
at org.hibernate.dialect.Dialect.getHibernateTypeName(Dialect.java:231)
at org.hibernate.loader.custom.CustomLoader$Metadata.getHibernateType(CustomLoader.java:559)
at org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.performDiscovery(CustomLoader.java:485)
at org.hibernate.loader.custom.CustomLoader.autoDiscoverTypes(CustomLoader.java:501)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1677)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2144)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2028)
at org.hibernate.loader.Loader.list(Loader.java:2023)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:150)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:780)


Name and version of the database you are using:
Oracle 10

The generated SQL (show_sql=true):
select a.geometry from alarm a where a.oid = 1[i]


Last edited by will_mad on Mon Jan 29, 2007 4:57 am, edited 1 time in total.

Top
 Profile  
 
 Post subject:
PostPosted: Fri Dec 15, 2006 12:48 pm 
Expert
Expert

Joined: Tue Dec 28, 2004 7:02 am
Posts: 573
Location: Toulouse, France
Maybe define a custom type to load this column. This should work I guess. Have a look here : http://forum.hibernate.org/viewtopic.ph ... 47#2334047

_________________
Baptiste
PS : please don't forget to give credits below if you found this answer useful :)


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jan 04, 2007 6:48 am 
Newbie

Joined: Fri Dec 15, 2006 6:08 am
Posts: 18
Location: France
I've already done this, but it's still not working.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jan 04, 2007 7:02 am 
Expert
Expert

Joined: Tue Dec 28, 2004 7:02 am
Posts: 573
Location: Toulouse, France
How did you do it? It should work. Did you map this UserType and use it correctly? What was the exception you were getting?

_________________
Baptiste
PS : please don't forget to give credits below if you found this answer useful :)


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jan 05, 2007 6:01 am 
Newbie

Joined: Fri Dec 15, 2006 6:08 am
Posts: 18
Location: France
I get the following exception :
org.hibernate.MappingException: No Dialect mapping for JDBC type: 2002

But as I said, when using an hql query to query an sdo_geometry object, it works. If I try with an sql query, it fails so I'm not able to call oracle spatial operators.


Top
 Profile  
 
 Post subject: Oracle Spatial and createSQLQuery
PostPosted: Thu Mar 22, 2007 11:33 am 
Newbie

Joined: Thu Oct 27, 2005 4:07 am
Posts: 11
Hi,

Has any found a way to query for Oracle spatial databases?
Any idea how I query for objects e.g. with a certain distance?

select
a.name
from
node a
WHERE
MDSYS.SDO_WITHIN_DISTANCE(
a.shape,
MDSYS.SDO_GEOMETRY(2001, NULL,
MDSYS.SDO_POINT_TYPE(-1233, 343123, NULL), NULL, NULL),
'distance = 100') = 'TRUE'
;

could I extend the OracleSpatialDialect? how?
what else could I do?

thanks

Peter


Top
 Profile  
 
 Post subject: Oracle Spatial and createCriterion
PostPosted: Thu Mar 22, 2007 2:28 pm 
Newbie

Joined: Thu Oct 27, 2005 4:07 am
Posts: 11
I found is possle querying oracle spatial data using
OracleSpatialCriterion implements org.hibernate.criterion.Criterion

e.g.
public static Criterion createDWithin(String geometryfieldname,
Geometry geometry, Double searchdistance)

how could I connect the spatial query to the Hibernate Dialect?

thanks

Peter


Top
 Profile  
 
 Post subject: hibernate spatial query
PostPosted: Fri May 18, 2007 10:32 am 
Newbie

Joined: Thu Oct 27, 2005 4:07 am
Posts: 11
Hi,

I am trying to use hibernate with spatial queries.
I am using a SQLEncoderOracleSpatialHibernate class
which is mainly org.geotools.filter.SQLEncoderOracle with changes in the visit Method.

This works for me, since the queries return the correct results,
but I would have to rewrite this class for PostGIS.

How could I use the getTypedValues-method?

I am trying to make sth. like a "hibernate spatial".
the classes I produced so far do work, but do not
to well integrate into hibernate core.
Are there others working on a "hibernate spatial"-framework?

thx

Peter



[code]
package at.prismasolutions.hibernatespatial;

import org.geotools.filter.Filter;
import org.geotools.filter.SQLEncoderException;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.criterion.CriteriaQuery;
import org.hibernate.criterion.Criterion;
import org.hibernate.engine.TypedValue;

public class OracleSpatialRestriction implements Criterion {

private Filter filter;
private int srid;

public OracleSpatialRestriction(Filter filter, int srid) {
this.filter = filter;
this.srid = srid;
}

public String toSqlString(
Criteria criteria,
CriteriaQuery criteriaQuery
) throws HibernateException {

SQLEncoderOracleSpatialHibernate encdr =
new SQLEncoderOracleSpatialHibernate (null, this.srid, criteria, criteriaQuery);

return encdr.encodeToSnippet(filter);

}

public TypedValue[] getTypedValues(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {

// TODO Auto-generated method stub
return new TypedValue[]{};

}
}[/code]


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jun 04, 2007 12:30 pm 
Newbie

Joined: Fri Dec 15, 2006 6:08 am
Posts: 18
Location: France
Here is my sql query :
Code:
select SDO_UTIL.POINT_AT_BEARING(SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(44, 44, NULL), NULL, NULL), SDO_UTIL.CONVERT_UNIT(10.0, 'Degree', 'Radian'), 520.0) from dual;


To execute the query, I have the following java code :
Code:
SQLQuery query = session.createSQLQuery(sql.toString());
Geometry g = (Geometry)query.uniqueResult();


But i get the following error : No Dialect mapping for JDBC type: 2002

I followed the example on the hibernate website : http://www.hibernate.org/402.html
It works everywhere in my application, I'm able to create, update, delete... oracle spatial objects using HQL, but when I call an oracle spatial operator with an sql query... it fails.

So when using oracle spatial in hibernate, are there any differences between HQL queries and SQL queries?

Thanks for your help.


Top
 Profile  
 
 Post subject: generally Map Geometry to spatial UserType?
PostPosted: Fri Jun 08, 2007 4:29 am 
Newbie

Joined: Thu Oct 27, 2005 4:07 am
Posts: 11
Hi,

I have not found a way around this.
Obviously the problem is that by mapping an entity I can tell hibernate to use a specific UserType for a property, but still I do not know how to generally map a GeometryType (e.g. JTS) to a UserType.
Maybe there there is an expert out there how can help?

thanks

Peter


P.S.: there seems to be a rather interesting project with a hibernate spatial extension on http://www.cadrie.com/


Top
 Profile  
 
 Post subject: mapping exception, register function?
PostPosted: Sun Jun 10, 2007 4:26 am 
Newbie

Joined: Wed Apr 11, 2007 8:48 am
Posts: 13
hii

i am using OracleSpatial 9 with EJB3. For sdo_geometry i am using the code available here: http://www.hibernate.org/402.html

Everything was working fine but now i am trying to use oracle spatial related operators in my queries and am facing problems. e.g. here is a query:

Code:
String sqlQuery = "SELECT lc.shape FROM location lc WHERE SDO_WITHIN_DISTANCE(lc.shape, mdsys.sdo_geometry(1,4326,NULL, mdsys.sdo_elem_info_array(1,1,1), mdsys.sdo_ordinate_array(5,5)), 'distance = 0.00005') = 'TRUE'";
Query query = em.createNativeQuery(sqlQuery);
List locations = query.getResultList();


here location is an entity which has a column named shape of type sdo_geometry. this query works fine when executed from the command prompt but does not work when i put it in my session bean and call from the client. i get the same
Code:
Caused by: org.hibernate.MappingException: No Dialect mapping for JDBC type: 2002


exception. The tutorial given for using Oracle spatial with hibernate also gives a special OracleSpatialDialect class. I am also using that but i think we have to specify the registerFunction in that dialect class. Any clue on how to use that?

many thanks


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 11 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.