Hello,
With my current project I have the following situation to map:
We have two databases, one containing requests and (due to a legacy database landscape which I cannot change)
another one containing data to those requests. Now to provide the data to the application in a transparent way I'm
trying to map the data as a virtual view using a subselect and a plain SQL query that employs Postgres'
dblink
module to access the remote database. Basically I'm doing a remote join. (Pretty strange I know, but seems to be the only option)
Now my problem is: I have mapped the
data property as a
lazy many-to-one association to
DataLink.
However, when I query only for
'Request' objects the SQL statement for
'DataLink' is executed n-times (for each request found). This is exactly what I want to avoid
since the remote query is very expensive. I just want the remote query executed when I actually access the DataLink data. So a query just for the requests should yield in one SQL statement in my opinion. Is this because of the
<subselect>?
Ideas anyone?
Hibernate version: 3.2.5
Mapping documents:
Code:
<class name="Request">
<id name="id">
<generator class="sequence">
<param name="sequence">request_id_seq</param>
</generator>
</id>
<version name="version" access="field" type="long" column="hibernate_version" />
<property name="productId" type="string" length="50" not-null="true" />
<property name="productName" type="string" length="255" not-null="true" />
<many-to-one name="store" class="Store" column="store_id" insert="false" update="false" not-null="true" />
<many-to-one name="user" class="StoreUser" column="user_id" insert="false" update="false" />
<many-to-one name="data" column="requestNumber" class="DataLink" insert="false" update="false" not-found="ignore" lazy="proxy"/>
</class>
Code:
<class name="DataLink" mutable="false" lazy="true">
<subselect>
select requestNumber,
subjectId,
from dblink('host=127.0.0.1
dbname=dbname
user=someuser
password=pass',
'select req.code,
subj.subj_id,
from subject subj
join request req on subj.subj_id = req.subj_id')
returns (requestnumber bigint,
subjectid character varying(255)
)
</subselect>
<id name="requestNumber" column="requestnumber" type="long" access="field" />
<property name="subjectId" type="string" access="field" length="38" not-null="true" />
</class>
none
Name and version of the database you are using:
PostgreSQL 8.2
The generated SQL (show_sql=true):
select
requestnumber as requestn1_0_0_,
subjectid as subjectId0_0_,
from dblink('host=127.0.0.1
dbname=dbname
user=someuser
password=pass',
'select req.code,
subj.subj_id,
from subject subj
join request req on subj.subj_id = req.subj_id')
returns (requestnumber bigint,
subjectid character varying(255)
)
above one displayed n times[quote][/quote]
Login
FAQ
Search