Supporting oracle and postgis in the same application (trying again - last post cut off)

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

Supporting oracle and postgis in the same application (trying again - last post cut off)

Ronald Lawrence
Hi all,

I have a project where I am developing an application that will
initially be deployed to run against oracle, but should also be able to
run against postgis (or mysql for that matter). To that end, I added
both provider jars to my maven dependencies, and set up some seemingly
simple switches in our database "setup" classes to be able to connect to
either database.  My data objects are using Hibernate annotations. I was
able to get each database to work with my simple junit test case
individually, but am having trouble coming up with a way to support both
at the same time.

What I've found is that the when running against oracle, I have to
supply a "sqltype" by using the following:
   @Column(columnDefinition = "MDSYS.SDO_GEOMETRY")

Obviously this will not work with postgis, and when I run that I find
that I have to set the dialect in the @Type annotation.

I have looked at the code in hibernate-spatial, and I notice that it
seems to be selecting the provider by looking for a file in the
classpath (each provider jar has the file) so it picks whichever
provider happens to fall in the classpath first.

Anyone have any suggestions as to how I can get my app to work without
reverting to some silly build tricks?

Thanks,
Ron
_______________________________________________
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: Supporting oracle and postgis in the same application (trying again - last post cut off)

Karel Maesen
Administrator
Hi Ron,


On 07 May 2009, at 23:56, Ronald Lawrence wrote:
>
> What I've found is that the when running against oracle, I have to
> supply a "sqltype" by using the following:
>    @Column(columnDefinition = "MDSYS.SDO_GEOMETRY")

You only need to set the sqltype annotation if you're using Hibernate  
to generate the schema. If you don't need to do that, the @Type
(type="org.hibernatespatial.GeometryUserType)"  annotation is  
sufficient and works with both Oracle and Postgis.


>
> Obviously this will not work with postgis, and when I run that I find
> that I have to set the dialect in the @Type annotation.
>
> I have looked at the code in hibernate-spatial, and I notice that it
> seems to be selecting the provider by looking for a file in the
> classpath (each provider jar has the file) so it picks whichever
> provider happens to fall in the classpath first.
>
> Anyone have any suggestions as to how I can get my app to work without
> reverting to some silly build tricks?

There is a (currently undocumented) configuration option. If you  
provide an XML file named "hibernate-spatial.cfg.xml" on the class-
path Hibernate Spatial will pick it up and use that as configuration  
information. Here is an example:

<?xml version="1.0" encoding="UTF-8"?>
<hibernate-spatial>
         
<default_dialect>org.hibernatespatial.postgis.PostgisDialect</
default_dialect>
         <precision_model>FIXED</precision_model>
         <precision_model_scale>5</precision_model_scale>
</hibernate-spatial>

The default_dialect is what interests you here. This will select the  
Postgis Dialect even if the Oracle Spatial provider is also present  
and loaded first.


Hope this helps,

Karel

_______________________________________________
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: Supporting oracle and postgis in the same application (trying again - last post cut off)

Ronald Lawrence
Karel,

This does help. Although I do want to use my build to generate the
database DDL, it's not a hard requirement. I will have to hand edit the
Oracle ddl in any case since it requires so much extra setup for spatial
queries to work, with the required insert to user_sdo_geom_metadata
_______________________________________________
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: Supporting oracle and postgis in the same application (trying again - last post cut off)

Farrukh Najmi
Ronald Lawrence wrote:
> Karel,
>
> This does help. Although I do want to use my build to generate the
> database DDL, it's not a hard requirement. I will have to hand edit the
> Oracle ddl in any case since it requires so much extra setup for spatial
> queries to work, with the required insert to user_sdo_geom_metadata
>  

Hi Ronald,

I will likely have to go through a similar experience as you sometime soon.
Any chance you could jot down your experience supporting postgis and Oracle
in a wiki page somewhere while it is still fresh in your mind?

Karel, does the project have a wiki that we could use?

--
Regards,
Farrukh

Web: http://www.wellfleetsoftware.com


_______________________________________________
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: Supporting oracle and postgis in the same application (trying again - last post cut off)

Ronald Lawrence
Sure.  When I get everything working well, I'll post or put on a wiki
the results and lessons learned.

-Ron

Farrukh Najmi wrote:

> Ronald Lawrence wrote:
>  
>> Karel,
>>
>> This does help. Although I do want to use my build to generate the
>> database DDL, it's not a hard requirement. I will have to hand edit the
>> Oracle ddl in any case since it requires so much extra setup for spatial
>> queries to work, with the required insert to user_sdo_geom_metadata
>>  
>>    
>
> Hi Ronald,
>
> I will likely have to go through a similar experience as you sometime soon.
> Any chance you could jot down your experience supporting postgis and Oracle
> in a wiki page somewhere while it is still fresh in your mind?
>
> Karel, does the project have a wiki that we could use?
>
>  

_______________________________________________
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: Supporting oracle and postgis in the same application (trying again - last post cut off)

Karel Maesen
Administrator
In reply to this post by Farrukh Najmi

On 08 May 2009, at 01:03, Farrukh Najmi wrote:

>
> Hi Ronald,
>
> I will likely have to go through a similar experience as you  
> sometime soon.
> Any chance you could jot down your experience supporting postgis  
> and Oracle
> in a wiki page somewhere while it is still fresh in your mind?
>
> Karel, does the project have a wiki that we could use?
>

There is no wiki, and I won't be creating one any time soon. But I'm  
happy to include any (mini-)tutorial or how-to you post here in the  
documentation pages of the project's website.

Regards,

Karel Maesen



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