Hi,
I have problem with SQLQuery. Something is wrong with mapping when using SQLQuery.
My SQL query looks like:
Code:
SELECT V.*, P.*, COUNT(DISTINCT L.ID) c FROM RETRIVELICENSEREQUEST AS R
INNER JOIN LICENSE AS L ON R.REQUESTEDLICENSE_ID = L.ID
INNER JOIN INSTALLATION AS I ON R.REQUESTERINSTALLATION_ID = I.ID
INNER JOIN PRODUCTVERSION AS V ON I.PRODUCTVERSION_ID = V.ID
INNER JOIN PRODUCT AS P ON V.PRODUCT_ID = P.ID AND P.PRODUCTID = L.PRODUCTID
GROUP BY P.ID, V.ID ORDER BY V.VERSION;
after execution in SQL client I get:
Code:
ID VERSION PRODUCT_ID ID PRODUCTID C
8 2.0.8.113 3 3 6 42
9 2.0.8.114 3 3 6 25
4 5.0.20.714 2 2 1 3
6 5.0.21.715 2 2 1 4
3 5.0.21.716 2 2 1 40
7 5.0.22.717 2 2 1 4
2 5.0.23.719 2 2 1 310
5 6.0.12.971 1 1 11 1
1 6.0.12.972 1 1 11 7
When I try to execute it with hibernate:
Code:
String query = "SELECT V.*, P.*, COUNT(DISTINCT L.ID) c FROM RETRIVELICENSEREQUEST AS R"
+ " INNER JOIN LICENSE AS L ON R.REQUESTEDLICENSE_ID = L.ID"
+ " INNER JOIN INSTALLATION AS I ON R.REQUESTERINSTALLATION_ID = I.ID"
+ " INNER JOIN PRODUCTVERSION AS V ON I.PRODUCTVERSION_ID = V.ID"
+ " INNER JOIN PRODUCT AS P ON V.PRODUCT_ID = P.ID AND P.PRODUCTID = L.PRODUCTID"
+ " GROUP BY P.ID, V.ID ORDER BY V.VERSION;";
SQLQuery sqlQuery = session.createSQLQuery(query);
sqlQuery.addEntity("v", ProductVersion.class);
sqlQuery.addJoin("P", "v.product");
sqlQuery.addScalar("c", StandardBasicTypes.INTEGER);
List<Object[]> result = (List<Object[]>) sqlQuery.list();
I get:
Code:
[{ProductVersion: id=8; version=2.0.8.113; product={Product: id=3; productId=1}}, 42]
[{ProductVersion: id=9; version=2.0.8.114; product={Product: id=3; productId=1}}, 25]
[{ProductVersion: id=4; version=5.0.20.714; product={Product: id=2; productId=1}}, 3]
[{ProductVersion: id=6; version=5.0.21.715; product={Product: id=2; productId=1}}, 4]
[{ProductVersion: id=3; version=5.0.21.716; product={Product: id=2; productId=1}}, 40]
[{ProductVersion: id=7; version=5.0.22.717; product={Product: id=2; productId=1}}, 4]
[{ProductVersion: id=2; version=5.0.23.719; product={Product: id=2; productId=1}}, 310]
[{ProductVersion: id=5; version=6.0.12.971; product={Product: id=1; productId=11}}, 1]
[{ProductVersion: id=1; version=6.0.12.972; product={Product: id=1; productId=11}}, 7]
The problem is that ProductVersion with version=2.0.* should be joined with product = {Product: id=3; productId=
6} but in gets joined
with {Product: id=3; productId=
1} eventhough such record does not exist in DB.
Products table looks like:
Code:
ID PRODUCTID
1 11
2 1
3 6
and product versions table:
Code:
ID VERSION PRODUCT_ID
8 2.0.8.113 3
9 2.0.8.114 3
4 5.0.20.714 2
6 5.0.21.715 2
3 5.0.21.716 2
7 5.0.22.717 2
2 5.0.23.719 2
5 6.0.12.971 1
1 6.0.12.972 1
Can someone tell me please is it me doing something wrong?
I checked it with both Hibernate 3.6.9 Final and 4.1.0 Final
Login
FAQ
Search