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]

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

I've already done this, but it's still not working.

How did you do it? It should work. Did you map this UserType and use it correctly? What was the exception you were getting?

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.

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

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

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]

Here is my sql query :


To execute the query, I have the following java code :


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.

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/

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:



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


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