I have root entity Hostel
and its single association User owner
.
When I fetch Hostel
entity I need to eagerly fetch User owner
, but only owner
's 3 properties : userId,firstName,lastName.
For now my criteria query is :
Criteria criteria = currenSession().createCriteria(Hostel.class);
criteria.add(Restrictions.ge("endDate", Calendar.getInstance()));
if (StringUtils.notNullAndEmpty(country)) {
criteria.add(Restrictions.eq("country", country));
}
Long count = (Long) criteria
.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
.setProjection(Projections.rowCount()).uniqueResult();
criteria.setFetchMode("owner", FetchMode.SELECT);
criteria.addOrder(Order.desc("rating"));
// needed to reset previous rowCount projection
criteria.setProjection(null);
// retrieve owner association
criteria.createAlias("owner", "owner", JoinType.LEFT_OUTER_JOIN)
.setProjection(
Projections.projectionList()
.add(Projections.property("owner.userId"))
.add(Projections.property("owner.firstName"))
.add(Projections.property("owner.lastName")));
criteria.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
Next, I do criteria.list()
and I get sql statement which selects only owner
's 3 properties as specified in projection list. But it doesn't select any property of root Hostel
entity.
Generated query is:
select
owner1_.user_id as y0_,
owner1_.firstName as y1_,
owner1_.lastName as y2_
from
HOSTEL this_
left outer join
USER owner1_
on this_.owner_fk=owner1_.user_id
where
this_.end_date>=?
and this_.country=?
order by
this_.rating desc limit ?
This query doesn't work because it returns five Map
s which are empty. FIve maps are because there are five Hostel
rows that match where condition. I created simple sql query and it works fine so the problem is only here.
How to force hibernate to fetch all properties of root Hostel
entity and only 3 properties of asociated User owner
entity?
EDIT
I tried to use getSessionFactory().getClassMetadata(Hostel.class)
but it gave error about mapping enum in Hostel
. So I fallback to list Hostel
properties manually. For now my criteria query is:
// retrieve owner association
criteria.createAlias("owner", "owner", JoinType.LEFT_OUTER_JOIN);
criteria.setProjection(Projections.projectionList()
.add(Projections.property("hostelId"))
.add(Projections.property("address"))
.add(Projections.property("country"))
.add(Projections.property("region"))
.add(Projections.property("gender"))
.add(Projections.property("owner.userId"))
.add(Projections.property("owner.firstName"))
.add(Projections.property("owner.lastName")));
List<Hostel> hostels = criteria.list();
for (Hostel hostel : hostels) { // at this line I get error java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to com.home.hostme.entity.Hostel
User owner = hostel.getOwner();
System.out.println("owner=" + owner);
}
Note that I removed ALIAS_TO_ENTITY_MAP
result transformer. This generated such mysql query :
select
this_.hostel_id as y0_,
this_.address as y1_,
this_.country as y2_,
this_.region as y3_,
this_.gender as y4_,
owner1_.user_id as y5_,
owner1_.firstName as y6_,
owner1_.lastName as y7_
from
HOSTEL this_
left outer join
USER owner1_
on this_.owner_fk=owner1_.user_id
where
this_.end_date>=?
and this_.country=?
order by
this_.rating desc limit ?
At for-each loop get such an error:
java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to com.home.hostme.entity.Hostel
at com.home.hostme.dao.impl.HostelDaoImpl.findHostelBy(HostelDaoImpl.java:168)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:155)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at com.sun.proxy.$Proxy64.findHostelBy(Unknown Source)
at com.home.hostme.service.HostelService.findHostelBy(HostelService.java:27)
at com.home.hostme.service.HostelService$$FastClassByCGLIB$$74db5b21.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:698)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:631)
at com.home.hostme.service.HostelService$$EnhancerByCGLIB$$7af3bc10.findHostelBy(<generated>)
at com.home.hostme.web.hostel.HostelController.doSearch(HostelController.java:94)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:686)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:936)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:838)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
This error means that I don't have type Hostel
in resulting list hostels
.
I even tried to find out class of elements in resulting list 'hostels' with this:
List hostels = criteria.list();
System.out.println("firstRow.class=" + hostels.get(0).getClass());
It printed:
firstRow.class=class [Ljava.lang.Object;
Then I tried to set ALIAS_TO_ENTITY_MAP
for new ProjectionList, but resulting list 'hostels' was:
[{}, {}, {}, {}, {}]
Five empty maps. Five because there are 5 rows in db(table hostel) matching where clause.
Then I deleted projection list completely and hibernate retrieved 5 hostels and 5 associated User owner
s and owner
's images as expected.
THE PROBLEM is how to stop hibernate retrieve associated Image
entity of associated User owner
. The best would be to fetch only 3 specific props of associated User owner
.
Thank you!
You can do it with a direct query :
Query query = session.createQuery("SELECT hostel, owner.id, owner.firstname, "
+"owner.lastname FROM Hostel hostel LEFT OUTER JOIN hostel.ower AS owner");
List list = query.list();
generates a SQL like :
select hostel0_.id as col_0_0_, user1_.id as col_1_0_, user1_.firstname as col_2_0_, user1_.lastname as col_3_0_, hostel0_.id as id1_0_, hostel0_.name as name2_0_, ..., hostel0_.owner_id as user_id4_0_ from Hostel hostel0_ left outer join User user1_ on user1_.id=hostel0_.owner_id
with all the fields from Hostel and only required fields from User.
The list obtained with criteria.list()
is a List<Object[]>
whose rows are
[ Hostel, Integer, String, String]
You can obtain something using Criteria
, but Criteria
are more strict than queries. I could not find any API that allows to mix entities and fields. So as far as I know, it is not possible to get rows containing an entity (Hostels) and separate fields from an association (owner.userId, owner.firstName, owner.lastName).
The only way I can imagine would be to explicitely list all fields from Hostels :
criteria.createAlias("owner", "owner", JoinType.LEFT_OUTER_JOIN)
.setProjection(
Projections.projectionList()
.add(Projections.property("hostelId"))
.add(Projections.property("country"))
.add(Projections.property("endDate"))
...
... all other properties from Hostel
...
.add(Projections.property("owner.userId"))
.add(Projections.property("owner.firstName"))
.add(Projections.property("owner.lastName")));
You can automate it a little by using Metadata (don't forget the id ...) - note : I use aliased projection only to be able later to use a wrapper class, if you directly use the scalar values, you can safely omit the Projection.alias
:
ProjectionList hostelProj = Projections.projectionList();
String id = sessionFactory.getClassMetadata(Hostel.class)
.getIdentifierPropertyName();
hostelProperties.add(Projections.alias(Projections.property(id),id));
for (String prop: sessionFactory.getClassMetadata(Hostel.class).getPropertyNames()) {
hostelProperties.add(Projections.alias(Projections.property(prop), prop));
}
Criteria criteria = session.createCriteria(Hostel.class);
criteria.createAlias("owner", "owner", JoinType.LEFT_OUTER_JOIN);
criteria.setProjection(
Projections.projectionList()
.add(hostelProj)
.add(Projections.property("owner.id"))
.add(Projections.property("owner.firstName"))
.add(Projections.property("owner.lastName")));
List list = criteria.list();
That way correctly generates
select this_.id as y0_, this_.name as y1_, ..., this_.user_id as y3_, owner1_.id as y4_, owner1_.firstname as y5_, owner1_.lastname as y6_ from hotels this_ left outer join users owner1_ on this_.user_id=owner1_.id
But you will not be able to use criteria.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP)
because the resultset is not exactly the image of the fields from Hostel
(even without the aliases).
In fact the list is a List<Object[]>
with rows containing all individual fields from Hostel
followed by the 3 required fields from owner
.
You will have to add a wrapper class containing a Hostel
and the 3 other fields to make use of an AliasToBeanResultTransformer
and get true Hostel
objects :
public class HostelWrapper {
private Hostel hostel;
private int owner_id;
private String owner_firstName;
private String owner_lastName;
public HostelWrapper() {
hostel = new Hostel();
}
public Hostel getHostel() {
return hostel;
}
public void setId(int id) {
hostel.setId(id);
}
public void setOwner(User owner) {
hostel.setOwner(owner);
}
// other setters for Hostel fields ...
public int getOwner_id() {
return owner_id;
}
public void setOwner_id(Integer owner_id) {
// beware : may be null because of outer join
this.owner_id = (owner_id == null) ? 0 : owner_id;
}
//getters and setters for firstName and lastName ...
}
And then you can successfully write :
criteria.setResultTransformer(new AliasToBeanResultTransformer(HostelWrapper.class));
List<HostelWrapper> hostels = criteria.list();
Hostel hostel = hostels.get(0).getHostel();
String firstName = hostels.get(0).getFirstName();
I could verify that when there is no owner hostel.getOwner()
is null, and when there is one, hostel.getOwner().getId()
is equal to getOwner_id()
and that this access does not generate any extra query. But any access to an other field of hostel.getOwner()
, even firstName
or lastName
generates one because the User
entity was not loaded in session.
The most common usage should be :
for (HostelWrapper hostelw: criteria.list()) {
Hostel hostel = hostelw.getHostel();
// use hostel, hostelw.getOwner_firstName and hostelw.getOwner_lastName
}