适用于Oracle Spatial的Hibernate 5 HQL转换程序 [英] Hibernate 5 HQL translator for Oracle Spatial

问题描述

我正在将应用程序从Hibernate 3.6.10.Final升级到5.0.7.Final
我现在的主要问题是，在Oracle方言生成足够快的查询之前， p>

  SELECT * FROM MY_TABLE 
 WHERE SDO_RELATE（geom，SDO_GEOMETRY（？，4326），'mask = INSIDE + COVEREDBY'） ='TRUE'
  

现在它会产生非常慢的效果：

  SELECT * FROM MY_TABLE 
 WHERE MDSYS.OGC_WITHIN（MDSYS.ST_GEOMETRY.FROM_SDO_GEOM（geom），MDSYS.ST_GEOMETRY.FROM_SDO_GEOM（？））= 1 
  

这一项不会按时完成并引发交易超时：

  JTA事务意外回滚（可能是由于超时
  

我只能认为为了将HQL转换为适当的高性能Oracle空间SQL而使用的任何方言类都有问题。

我的配置是如

pom.xml：

 <依赖关系> 
< groupId> org.hibernate< / groupId> 
< artifactId> hibernate-core< / artifactId> 
< version> 5.0.7.Final< / version> 
< /依赖关系> 
< dependency> 
< groupId> org.hibernate< / groupId> 
< artifactId> hibernate-entitymanager< / artifactId> 
< version> 5.0.7.Final< / version> 
< /依赖关系> 
< dependency> 
< groupId> org.hibernate< / groupId> 
< artifactId> hibernate-spatial< / artifactId> 
< version> 5.0.7.Final< / version> 
< /依赖关系> 
  

我在persistence.xml中将Atomikos（4.0.0M4）配置为事务管理器。

 < persistence-unit name =pers_unit_nametransaction-type =JTA> 
< provider> org.hibernate.ejb.HibernatePersistence< / provider> 
< jta-data-source> jta_data_source_name< / jta-data-source> 
< mapping-file> oracle.hbm.xml< / mapping-file> 
< class> ...< / class> 
<属性> 
< property name =hibernate.dialectvalue =org.hibernate.spatial.dialect.oracle.OracleSpatial10gDialect/> 
< property name =hibernate.spatial.dialectvalue =org.hibernate.spatial.dialect.oracle.OracleSpatial10gDialect/> 
< property name =hibernate.spatial.connection_findervalue =org.geolatte.geom.codec.db.oracle.DefaultConnectionFinder/> 
< property name =hibernate.connection.autocommitvalue =false/> 
< property name =hibernate.transaction.manager_lookup_classvalue =com.atomikos.icatch.jta.hibernate4.TransactionManagerLookup/> 
< property name =transaction.factory_class
 value =org.hibernate.transaction.JTATransactionFactory/> 
< property name =hibernate.transaction.jta.platformvalue =com.atomikos.icatch.jta.hibernate4.AtomikosPlatform/> 
< property name =hibernate.transaction.coordinator_classvalue =jta/> 
< property name =hibernate.cache.provider_classvalue =org.hibernate.cache.NoCacheProvider/> 
< / properties> 
< / persistence-unit> 
  

当我调试HQLQueryPlan时，我看到它在内部使用的查询翻译器：

  org.hibernate.hql.internal.ast.QueryTranslatorImpl 
  

不知道这是对还是错，或者如何配置它以生成正确的查询。

此应用程序正在运行Tomcat 8.

$ b POJO与Hibernate一起用于映射实体包含geom属性，该属性被定义为：

  @Column（name =geom，columnDefinition =Geometry，nullable = true）
 protected Geometry geom; 
  

解决方案

看起来设置 OGC_STRICT = false 做了诀窍。这告诉Hibernate使用Oracle自己的空间函数直接使用，而不是使用开放地理空间兼容函数，正如我们可以在 OGC合规性设置文档。

实际上，我们已经在 org.hibernatespatial.oracle.OracleSpatial10gDialect.properties 文件中设置了它，但是因为升级后它应该以 org.hibernate.spatial.dialect.oracle.OracleSpatial10gDialect.properties 命名，它不适用于我们。

I am upgrading an application from Hibernate 3.6.10.Final to 5.0.7.Final The main issue I have right now is that when before the Oracle dialect would generate a fast enough query like this :

SELECT * FROM MY_TABLE 
WHERE SDO_RELATE(geom,SDO_GEOMETRY(?,4326),'mask=INSIDE+COVEREDBY') ='TRUE'

Now it will generate something terribly slow:

SELECT * FROM MY_TABLE 
WHERE MDSYS.OGC_WITHIN(MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(geom),MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(?))=1 

This one will not finish on time and raise a transaction timeout :

JTA transaction unexpectedly rolled back (maybe due to a timeout

I can only think that there is something wrong with whatever dialect class it is used in order to translate HQL into proper performant Oracle spatial SQL.

My configuration is as follows.

pom.xml :

    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-core</artifactId>
        <version>5.0.7.Final</version>
    </dependency>
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-entitymanager</artifactId>
        <version>5.0.7.Final</version>
    </dependency>
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-spatial</artifactId>
        <version>5.0.7.Final</version>
    </dependency>

My persistence.xml , where I configure Atomikos (4.0.0M4) as transaction manager.

<persistence-unit name="pers_unit_name" transaction-type="JTA">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<jta-data-source>jta_data_source_name</jta-data-source>
<mapping-file>oracle.hbm.xml</mapping-file>
<class>...</class>
<properties>
        <property name="hibernate.dialect" value="org.hibernate.spatial.dialect.oracle.OracleSpatial10gDialect" />
        <property name="hibernate.spatial.dialect" value="org.hibernate.spatial.dialect.oracle.OracleSpatial10gDialect" />
        <property name="hibernate.spatial.connection_finder" value="org.geolatte.geom.codec.db.oracle.DefaultConnectionFinder" />
        <property name="hibernate.connection.autocommit" value="false" />
        <property name="hibernate.transaction.manager_lookup_class" value="com.atomikos.icatch.jta.hibernate4.TransactionManagerLookup" />
         <property name="transaction.factory_class"
            value="org.hibernate.transaction.JTATransactionFactory" />          
        <property name="hibernate.transaction.jta.platform" value="com.atomikos.icatch.jta.hibernate4.AtomikosPlatform"/> 
        <property name="hibernate.transaction.coordinator_class" value="jta"/>
        <property name="hibernate.cache.provider_class" value="org.hibernate.cache.NoCacheProvider" />
    </properties>
</persistence-unit>

When I debug HQLQueryPlan I see the query translator that it is using internally is :

org.hibernate.hql.internal.ast.QueryTranslatorImpl

Not sure whether this is right or wrong, or how this could be configured to generate the right query.

This application is running on Tomcat 8.

The POJO used with Hibernate to map the entity contains that geom attribute which is defined as :

@Column(name = "geom", columnDefinition="Geometry", nullable = true)
protected Geometry geom;

解决方案

It looks that setting OGC_STRICT=false did the trick. This tells Hibernate to use Oracle's own spatial functions are used directly, instead of using Open Geospatial compliant functions, as we can read in the OGC compliance setting documentation.

Actually, we had already set it up in the org.hibernatespatial.oracle.OracleSpatial10gDialect.properties file, but because after the upgrade it should be named after org.hibernate.spatial.dialect.oracle.OracleSpatial10gDialect.properties , it wouldn't work for us.

这篇关于适用于Oracle Spatial的Hibernate 5 HQL转换程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！