We are working on a spring mvc project with hibernate.
When we execute the following code:
try {
HibernateTemplate ht = new HibernateTemplate(sf);
List<Route> r = ht.findByNamedParam("select r from Route r inner join r.carPoolers as carPooler where (( r.owner.id = :userid ) or ( carPooler.user.id = :userid )) AND r.id =:routeID", new String[]{"userid", "routeID"} , new Object[]{ u.getId() , id});
if (r.size() == 1) {
return r.get(0);
} else {
return null;
}
} catch (DataAccessException ex) {
LogFactory.getLog(RouteRepository.class).fatal(ex);
return null;
}
this is the result:
SELECT ROUTE0_.ID AS ID4_, ROUTE0_.ARRIVALTIME AS ARRIVALT2_4_,
ROUTE0_.CAR_ID AS CAR13_4_, ROUTE0_.DATE AS DATE4_,
ROUTE0_.DAYOFWEEK AS DAYOFWEEK4_, ROUTE0_.DEPARTURETIME AS
DEPARTUR5_4_, ROUTE0_.ENDDATE AS ENDDATE4_, ROUTE0_.MESSAGEID
AS MESSAGEID4_, ROUTE0_.OPENSEATS AS OPENSEATS4_,
ROUTE0_.OWNER_ID AS OWNER14_4_, ROUTE0_.ROUTECACHE_ID AS
ROUTECACHE11_4_, ROUTE0_.ROUTEOPTIMIZED AS ROUTEOPT9_4_,
ROUTE0_.START_ID AS START12_4_, ROUTE0_.STOP_ID AS STOP10_4_
FROM ROUTE ROUTE0_ INNER JOIN CARPOOLER CARPOOLERS1_ ON
ROUTE0_.ID=CARPOOLERS1_.ROUTEID
WHERE (route0_.owner_id=? or carpoolers1_.user_id=?) and route0_.id=?
SELECT CAR0_.ID AS ID5_3_, CAR0_.BRAND_ID AS BRAND8_5_3_, CAR0_.CARNAME
AS CARNAME5_3_, CAR0_.CARTYPE AS CARTYPE5_3_, CAR0_.IMAGEURL AS
IMAGEURL5_3_, CAR0_.PRICEKM AS PRICEKM5_3_, CAR0_.SEATS AS
SEATS5_3_, CAR0_.USER_ID AS USER7_5_3_, BRAND1_.ID AS ID6_0_,
BRAND1_.BRANDNAME AS BRANDNAME6_0_, USER2_.ID AS ID0_1_,
USER2_.EMAIL AS EMAIL0_1_, USER2_.FACEBOOKID AS FACEBOOKID0_1_,
USER2_.FIRSTNAME AS FIRSTNAME0_1_, USER2_.GENDER AS GENDER0_1_,
USER2_.IMAGEURL AS IMAGEURL0_1_, USER2_.LANGUAGE_ID AS
LANGUAGE12_0_1_, USER2_.LASTNAME AS LASTNAME0_1_,
USER2_.MOBILEPHONE AS MOBILEPH8_0_1_, USER2_.PASSWORD AS
PASSWORD0_1_, USER2_.SMOKER AS SMOKER0_1_, USER2_.TELEPHONE AS
TELEPHONE0_1_, LANGUAGE3_.ID AS ID9_2_, LANGUAGE3_.LANGUAGE AS
LANGUAGE9_2_, LANGUAGE3_.LANGUAGECODE AS LANGUAGE3_9_2_
FROM CAR CAR0_ LEFT OUTER JOIN BRAND BRAND1_ ON
CAR0_.BRAND_ID=BRAND1_.ID LEFT OUTER JOIN USER USER2_ ON
CAR0_.USER_ID=USER2_.ID LEFT OUTER JOIN LANGUAGE LANGUAGE3_ ON
USER2_.LANGUAGE_ID=LANGUAGE3_.ID
WHERE car0_.id=?
SELECT USER0_.ID AS ID0_1_, USER0_.EMAIL AS EMAIL0_1_,
USER0_.FACEBOOKID AS FACEBOOKID0_1_, USER0_.FIRSTNAME AS
FIRSTNAME0_1_, USER0_.GENDER AS GENDER0_1_, USER0_.IMAGEURL AS
IMAGEURL0_1_, USER0_.LANGUAGE_ID AS LANGUAGE12_0_1_,
USER0_.LASTNAME AS LASTNAME0_1_, USER0_.MOBILEPHONE AS
MOBILEPH8_0_1_, USER0_.PASSWORD AS PASSWORD0_1_, USER0_.SMOKER
AS SMOKER0_1_, USER0_.TELEPHONE AS TELEPHONE0_1_, LANGUAGE1_.ID
AS ID9_0_, LANGUAGE1_.LANGUAGE AS LANGUAGE9_0_,
LANGUAGE1_.LANGUAGECODE AS LANGUAGE3_9_0_
FROM USER USER0_ LEFT OUTER JOIN LANGUAGE LANGUAGE1_ ON
USER0_.LANGUAGE_ID=LANGUAGE1_.ID
WHERE user0_.id=?
SELECT ROUTECACHE0_.ID AS ID7_2_, ROUTECACHE0_.AANTALM AS AANTALM7_2_,
ROUTECACHE0_.AANTALMIN AS AANTALMIN7_2_, ROUTECACHE0_.ACTIVE AS
ACTIVE7_2_, ROUTECACHE0_.JSON AS JSON7_2_,
ROUTECACHE0_.LOCATIONS AS LOCATIONS7_2_,
ROUTECACHE0_.LOCATIONSOPTIMIZED AS LOCATION7_7_2_,
ROUTECACHE0_.ROUTEOPTIMIZED AS ROUTEOPT8_7_2_,
ROUTECACHE0_.START_ID AS START10_7_2_, ROUTECACHE0_.STOP_ID AS
STOP9_7_2_, LOCATION1_.ID AS ID2_0_, LOCATION1_.LANG AS
LANG2_0_, LOCATION1_.LAT AS LAT2_0_, LOCATION1_.NUMBER AS
NUMBER2_0_, LOCATION1_.STREET AS STREET2_0_, LOCATION1_.ZIPCODE
AS ZIPCODE2_0_, LOCATION2_.ID AS ID2_1_, LOCATION2_.LANG AS
LANG2_1_, LOCATION2_.LAT AS LAT2_1_, LOCATION2_.NUMBER AS
NUMBER2_1_, LOCATION2_.STREET AS STREET2_1_, LOCATION2_.ZIPCODE
AS ZIPCODE2_1_
FROM ROUTECACHE ROUTECACHE0_ LEFT OUTER JOIN LOCATION LOCATION1_ ON
ROUTECACHE0_.START_ID=LOCATION1_.ID LEFT OUTER JOIN LOCATION
LOCATION2_ ON ROUTECACHE0_.STOP_ID=LOCATION2_.ID
WHERE routecache0_.id=?
SELECT ROUTECACHE0_.ROUTECACHESPUNTENTUSSEN_ID AS ROUTECAC1_1_,
ROUTECACHE0_.ROUTECACHETUSSENPUNTEN_ID AS ROUTECAC2_1_,
LOCATION1_.ID AS ID2_0_, LOCATION1_.LANG AS LANG2_0_,
LOCATION1_.LAT AS LAT2_0_, LOCATION1_.NUMBER AS NUMBER2_0_,
LOCATION1_.STREET AS STREET2_0_, LOCATION1_.ZIPCODE AS
ZIPCODE2_0_
FROM ROUTECACHE_LOCATION_PUNTEN ROUTECACHE0_ LEFT OUTER JOIN LOCATION
LOCATION1_ ON
ROUTECACHE0_.ROUTECACHETUSSENPUNTEN_ID=LOCATION1_.ID
WHERE routecache0_.routecachesPuntenTussen_id=?
SELECT CARPOOLERS0_.ROUTEID AS ROUTEID5_, CARPOOLERS0_.ID AS ID5_,
CARPOOLERS0_.ID AS ID3_4_, CARPOOLERS0_.APPROVED AS
APPROVED3_4_, CARPOOLERS0_.ONETIME AS ONETIME3_4_,
CARPOOLERS0_.ROUTEID AS ROUTEID3_4_, CARPOOLERS0_.START_ID AS
START5_3_4_, CARPOOLERS0_.STOP_ID AS STOP7_3_4_,
CARPOOLERS0_.USER_ID AS USER6_3_4_, LOCATION1_.ID AS ID2_0_,
LOCATION1_.LANG AS LANG2_0_, LOCATION1_.LAT AS LAT2_0_,
LOCATION1_.NUMBER AS NUMBER2_0_, LOCATION1_.STREET AS
STREET2_0_, LOCATION1_.ZIPCODE AS ZIPCODE2_0_, LOCATION2_.ID AS
ID2_1_, LOCATION2_.LANG AS LANG2_1_, LOCATION2_.LAT AS LAT2_1_,
LOCATION2_.NUMBER AS NUMBER2_1_, LOCATION2_.STREET AS
STREET2_1_, LOCATION2_.ZIPCODE AS ZIPCODE2_1_, USER3_.ID AS
ID0_2_, USER3_.EMAIL AS EMAIL0_2_, USER3_.FACEBOOKID AS
FACEBOOKID0_2_, USER3_.FIRSTNAME AS FIRSTNAME0_2_,
USER3_.GENDER AS GENDER0_2_, USER3_.IMAGEURL AS IMAGEURL0_2_,
USER3_.LANGUAGE_ID AS LANGUAGE12_0_2_, USER3_.LASTNAME AS
LASTNAME0_2_, USER3_.MOBILEPHONE AS MOBILEPH8_0_2_,
USER3_.PASSWORD AS PASSWORD0_2_, USER3_.SMOKER AS SMOKER0_2_,
USER3_.TELEPHONE AS TELEPHONE0_2_, LANGUAGE4_.ID AS ID9_3_,
LANGUAGE4_.LANGUAGE AS LANGUAGE9_3_, LANGUAGE4_.LANGUAGECODE AS
LANGUAGE3_9_3_
FROM CARPOOLER CARPOOLERS0_ LEFT OUTER JOIN LOCATION LOCATION1_ ON
CARPOOLERS0_.START_ID=LOCATION1_.ID LEFT OUTER JOIN LOCATION
LOCATION2_ ON CARPOOLERS0_.STOP_ID=LOCATION2_.ID LEFT OUTER
JOIN USER USER3_ ON CARPOOLERS0_.USER_ID=USER3_.ID LEFT OUTER
JOIN LANGUAGE LANGUAGE4_ ON USER3_.LANGUAGE_ID=LANGUAGE4_.ID
WHERE carpoolers0_.RouteId=?
Problem:
- This takes minimum 460ms.
- We only need the first query for our results.
, ty in advance
