I'm working on an application where 1 java object has a series of read-only derived properties. The object is a phone number; so in addition to the usual properties you'd expect a phone number to have (country dial code, npa number, exchange number and station) it also has some properties that deal with geographic location. In other words, based on the format of the phone number, I'm using a series of database tables to figure out where in the world that phone number is.
I have a fairly lengthy but efficient sql query that allows me to do this. The problem I'm now having is telling Hibernate that my DID object (a DID is a phone number) has some additional properties that aren't present in the main table but instead need to be calculated at query time.
I understand that Hibernate allows you to stuff custom sql inside a formula for specific properties. I don't want to use this because I have about 12 derived properties (city, country, state, country, toll free, etc). If I need to copy the geo-location logic inside each formula, Hibernate would have run the whole query to figure out where a phone numbers is 12 times and that wouldn't be very efficient.
My initial thought was that I could simply use custom sql inside the Hibernate mapping file to tell Hibernate how to load an object and set the derived properties to be insert=false and update=false; like so:
<hibernate-mapping>
<class name="com.acmecorp.didmanager.shared.model.DID" table="tpn_did_pool" catalog="agile">
<id name="id" type="java.lang.Long">
<column name="id"/>
<generator class="identity" />
</id>
<many-to-one name="account" class="com.acmecorp.didmanager.shared.model.Account" fetch="join">
<column name="account_id"/>
</many-to-one>
<many-to-one name="csa" class="com.acmecorp.didmanager.shared.model.CSA" fetch="join">
<column name="csa_id"/>
</many-to-one>
<many-to-one name="carrier" class="com.acmecorp.didmanager.shared.model.Carrier" fetch="join">
<column name="carrier_id"/>
</many-to-one>
<property name="countryDialCode" type="int" not-null="true">
<column name="country_code"/>
</property>
<property name="npa" type="string" not-null="true">
<column name="npa"/>
</property>
<property name="nxx" type="string" not-null="true">
<column name="nxx"/>
</property>
<property name="station" type="string" not-null="true">
<column name="station"/>
</property>
<property name="dateReserved" type="java.lang.Long">
<column name="date_reserved"/>
</property>
<property name="portStatus" type="string">
<column name="port_status"/>
</property>
<property name="cname" type="string">
<column name="cname"/>
</property>
<property name="notes" type="string">
<column name="notes"/>
</property>
<property name="city" lazy="true" insert="false" update="false" type="string"/>
<property name="county" lazy="true" insert="false" update="false" type="string"/>
<property name="stateCode" lazy="true" insert="false" update="false" type="string"/>
<property name="stateName" lazy="true" insert="false" update="false" type="string"/>
<property name="countryCode" lazy="true" insert="false" update="false" type="string"/>
<property name="countryName" lazy="true" insert="false" update="false" type="string"/>
<property name="tollFree" lazy="true" insert="false" update="false" type="boolean"/>
<property name="active" lazy="true" insert="false" update="false" type="boolean"/>
<property name="sacService" lazy="true" insert="false" update="false" type="boolean"/>
<property name="localUse" lazy="true" insert="false" update="false" type="boolean"/>
<loader query-ref="did"/>
</class>
<sql-query name="did">
<return alias="did" class="com.acmecorp.didmanager.shared.model.DID">
<return-property name="id" column="id"/>
<return-property name="account" column="account_id"/>
<return-property name="csa" column="csa_id"/>
<return-property name="carrier" column="carrier_id"/>
<return-property name="countryDialCode" column="country_dial_code"/>
<return-property name="npa" column="npa"/>
<return-property name="nxx" column="nxx"/>
<return-property name="station" column="station"/>
<return-property name="dateReserved" column="date_reserved"/>
<return-property name="portStatus" column="port_status"/>
<return-property name="cname" column="cname"/>
<return-property name="notes" column="notes"/>
<return-property name="city" column="city"/>
<return-property name="county" column="county"/>
<return-property name="stateCode" column="state_code"/>
<return-property name="stateName" column="state_name"/>
<return-property name="countryCode" column="country_code"/>
<return-property name="countryName" column="country_name"/>
<return-property name="tollFree" column="toll_free"/>
<return-property name="active" column="active"/>
<return-property name="sacService" column="sac_service"/>
<return-property name="localUse" column="local_use"/>
</return>
select
pool.id,
pool.account_id,
pool.csa_id,
pool.carrier_id,
pool.country_code as country_dial_code,
pool.npa,
pool.nxx,
pool.station,
pool.date_reserved,
pool.port_status,
pool.cname,
pool.notes,
group_concat(DISTINCT loc.city ORDER BY loc.city) as city,
group_concat(DISTINCT loc.county ORDER BY loc.county) as county,
group_concat(DISTINCT state.state_code ORDER BY state.state_code) as state_code,
group_concat(DISTINCT state.state_name ORDER BY state.state_name) as state_name,
group_concat(DISTINCT country.country_code ORDER BY country.country_code) as country_code,
group_concat(DISTINCT country.country_name ORDER BY country.country_name) as country_name,
(CASE WHEN nxx_match.toll_free IS NOT NULL THEN nxx_match.toll_free
WHEN npa_match.toll_free IS NOT NULL THEN npa_match.toll_free
ELSE 0
END) as toll_free,
(CASE WHEN nxx_match.active IS NOT NULL THEN nxx_match.active
WHEN npa_match.active IS NOT NULL THEN npa_match.active
WHEN country_match.active IS NOT NULL THEN country_match.active
ELSE 0
END) as active,
(CASE WHEN nxx_match.sac_service IS NOT NULL THEN nxx_match.sac_service
WHEN npa_match.sac_service IS NOT NULL THEN npa_match.sac_service
END) as sac_service,
(CASE WHEN nxx_match.local_use IS NOT NULL THEN nxx_match.local_use
WHEN npa_match.local_use IS NOT NULL THEN npa_match.local_use
END) as local_use
FROM tpn_did_pool pool
LEFT JOIN tpn_did_location_mapping nxx_match
on pool.country_code = nxx_match.country_dial_code
AND pool.npa = nxx_match.npa
and pool.nxx = nxx_match.nxx
LEFT JOIN tpn_did_location_mapping npa_match
on pool.country_code = npa_match.country_dial_code
AND pool.npa = npa_match.npa
and npa_match.nxx is null
LEFT JOIN tpn_did_location_mapping country_match
on pool.country_code = country_match.country_dial_code
AND country_match.npa is null
AND country_match.nxx is null
LEFT JOIN tpn_did_countries country
ON country.id =
(CASE WHEN nxx_match.country_id IS NOT NULL THEN nxx_match.country_id
WHEN npa_match.country_id IS NOT NULL THEN npa_match.country_id
WHEN country_match.country_id IS NOT NULL THEN country_match.country_id
ELSE null
END)
LEFT JOIN tpn_did_states state
ON state.id =
(CASE WHEN nxx_match.state_id IS NOT NULL THEN nxx_match.state_id
WHEN npa_match.state_id IS NOT NULL THEN npa_match.state_id
ELSE null
END)
LEFT JOIN tpn_did_locations loc
ON loc.id =
(CASE WHEN nxx_match.location_id IS NOT NULL THEN nxx_match.location_id
WHEN npa_match.location_id IS NOT NULL THEN npa_match.location_id
WHEN country_match.location_id IS NOT NULL THEN country_match.location_id
ELSE NULL
END)
WHERE pool.id = ?
GROUP BY
country_dial_code,
npa,
nxx,
station
</sql-query>
</hibernate-mapping>
This works - I can fetch individual DIDs; and I can even create new ones and persist them to the database. The problems begin when I try to search for a DID by using a criteria-driven Hibernate query. As soon as I do that, Hibernate seems to default back to the normal table description and ignore the custom sql; because it starts complaining that the derived properties do not exist in the tpn_did_pool table (and indeed, they don't); i.e.:
I guess I'm confused about the purpose of using custom SQL for loading Hibernate objects - what's the point if you can't query against them?
So how exactly do you solve this problem? This has to be a fairly common requirement: how do you write a Hibernate mapping file for an object where not every property of that object is mapped directly to a column in the database?
Login
FAQ
Search
