Hi
I am fairly new to using the Hibernate and Spring frameworks and have a question on how to map a complicated join in my database.
I am building a telephone number database and have two tables I wish to join using the hbm.xml config files. The two tables are Ranges and Numbers for which one range can have many numbers.
Ranges are stored based on consectutive numbers being within the same status (e.g. allocated to customer). Now because the ranges change in size all the time based on users reserving numbers and sub ranges within larger ranges, it is not feasible to have a regular primary/foreign key relationship as it would mean huge updates in the numbers table everytime a range was fragmented into smaller ranges.
However I could build SQL joins based on the SQL query below.
Code:
SELECT * FROM num.numbers n CROSS JOIN num.ranges AS r WHERE n.prefix = r.prefix AND n.static_bit = r.static_bit AND CAST(n.variable_bit as UNSIGNED) BETWEEN CAST(r.start_variable_bit as UNSIGNED) AND CAST(r.end_variable_bit as UNSIGNED)
Does anyone know of any way of replicating this SQL into a .hbm.xml file?
Thanks
Stuart
Login
FAQ
Search