Hi there,
I am using hibernate 4.3.5 and need to map a one table database to a multi table model with hibernate mappings. The used database server is mysql.
It works just for most entities with pre-defined simple queries like
Code:
SELECT "fix value" AS value
in <subselect> elements of most entities.
For one entity, I need to split the data that is contained in the single table in multiple columns in a view with less columns and more rows. I got it working with a lot of joins and a very bad performance resulting in service response times of one minute and more. This is a no-go within the project.
One idea is to use parameters because in most situations only a subset (in this example the data from one of the multiple columns) is needed. The table structure looks like this.
Table to map:
Code:
id, timestamp, value1, value2, value3, value4, value5
1, 2014-09-24 16:58, 1, 1.2, 2.3, 4.5, 5.8
The service expects:
Code:
id, timestamp, value, valueId
1, 2014-09-24 16:58, 1, 1
2, 2014-09-24 16:58, 1.2, 2
3, 2014-09-24 16:58, 2.3, 3
4, 2014-09-24 16:58, 4.5, 4
5, 2014-09-24 16:58, 5.8, 5
The subselect I created looks like this:
Code:
SELECT
(t.id * 1e10) + t.valueid AS id,
valueid AS valueid,
t.timestamp AS timestamp,
t.value AS value
FROM
(
SELECT id, timestamp, '1' AS valueid, value1 AS value FROM singletable
UNION
SELECT id, timestamp, '2' AS valueid, value2 AS value FROM singletable
UNION
SELECT id, timestamp, '3' AS valueid, value3 AS value FROM singletable
UNION
SELECT id, timestamp, '4' AS valueid, value4 AS value FROM singletable
UNION
SELECT id, timestamp, '5' AS valueid, value5 AS value FROM singletable
) AS t
The idea is to use something like
Code:
SELECT
(t.id * 1e10) + t.valueid AS id,
valueid AS valueid,
t.timestamp AS timestamp,
t.value AS value
FROM
(
SELECT
id,
timestamp,
CASE :parameter
WHEN 'value1' THEN 1
WHEN 'value2' THEN 2
WHEN 'value3' THEN 3
WHEN 'value4' THEN 4
WHEN 'value5' THEN 5
END AS valueid,
CASE :parameter
WHEN 'value1' THEN value1
WHEN 'value2' THEN value2
WHEN 'value3' THEN value3
WHEN 'value4' THEN value4
WHEN 'value5' THEN value5
END AS value
FROM singletable
) AS t
But this can only work, if I can use the parameter ":parameter" within the subselect element like in <sql-query> elements.
Is this possbile?
If not, do you have any additional idea, how to solve this on the mapping level?
Login
FAQ
Search