I'm trying to use the hibernate criteria api to achieve the following sql
Code:
select *
from account_event ae1,event_type et1
where ae1.ACTUAL_EVENT_DT =
(select Max(ae2.ACTUAL_EVENT_DT)
from account_event ae2
where ae2.ACC_ID=ae1.acc_id
)
and ae1.EVENT_TYPE_ID=et1.EVENT_TYPE_ID
and et1.SCHEME_ID = -25
I'm using the following code
Code:
DetachedCriteria subCriteria = DetachedCriteria.forClass(persistenceMapping.getClassToSave(),detachedAlias);
subCriteria.setProjection( Property.forName("actualEventDate").max());
subCriteria.add( Property.forName(detachedAlias + "." + "accountId").eqProperty(mainCriteriaAlias + "." + "accountId") );
Criteria criteria = session.createCriteria(persistenceMapping.getClassToSave(),mainCriteriaAlias);
criteria.add(Subqueries.eq(attribute, subCriteria));
hibernateReflectionUtil.generateUniqueRestrictionsByVo(
session, criteria, persistenceMapping.getClassToSave(), restriction, false, true);
We have a generic way of generating restrictions from a populated object
Code:
hibernateReflectionUtil.generateUniqueRestrictionsByVo
which is used to create the bits of the where clause except for the correlated subquery
This code gives me the following hibernate version of sql
Code:
select
this_.ACC_EVENT_ID as ACC1_4_2_,
this_.EVENT_PTY_ID as EVENT2_4_2_,
this_.ACC_ID as ACC3_4_2_,
this_.ACTUAL_EVENT_DT as ACTUAL4_4_2_,
this_.ACTUAL_EVENT_DT_GRANULARITY as ACTUAL5_4_2_,
this_.EXPECTED_EVENT_DT as EXPECTED6_4_2_,
this_.EXPECTED_EVENT_DT_GRANULARITY as EXPECTED7_4_2_,
this_.EVENT_TYPE_ID as EVENT8_4_2_,
alias3x1_.EVENT_TYPE_ID as EVENT1_14_0_,
alias3x1_.EVENT_TYPE_CODE as EVENT2_14_0_,
alias3x1_.EVENT_TYPE_NAME as EVENT3_14_0_,
alias3x1_.EVENT_TYPE_DESC as EVENT4_14_0_,
alias3x1_.ROW_STATUS_IND as ROW5_14_0_,
alias3x1_.SCHEME_ID as SCHEME6_14_0_,
alias4x2_.SCHEME_ID as SCHEME1_11_1_,
alias4x2_.PARENT_SCHEME_ID as PARENT2_11_1_,
alias4x2_.IMMUTABLE_MEMBERS_FLAG as IMMUTABLE3_11_1_,
alias4x2_.MULT_MEMBERS_ALLOWED_FLAG as MULT4_11_1_,
alias4x2_.SCHEME_DESC as SCHEME5_11_1_,
alias4x2_.SCHEME_NAME as SCHEME6_11_1_,
alias4x2_.SCHEME_CODE as SCHEME7_11_1_
from
ACCOUNT_EVENT this_
inner join
EVENT_TYPE alias3x1_
on this_.EVENT_TYPE_ID=alias3x1_.EVENT_TYPE_ID
inner join
SCHEME alias4x2_
on alias3x1_.SCHEME_ID=alias4x2_.SCHEME_ID
where
? = (
select
max(this0__.ACTUAL_EVENT_DT) as y0_
from
ACCOUNT_EVENT this0__
where
this0__.ACC_ID=this_.ACC_ID
)
and alias4x2_.SCHEME_ID=?
Which looks quite like what I want. Unfortunately it seems hibernate is now trying to convert my dates in the correlated subquery to Strings as I'm getting the following exception
Code:
java.lang.ClassCastException: java.lang.String
at org.hibernate.type.TimestampType.toString(Ljava.lang.Object;)Ljava.lang.String;(TimestampType.java:55)
at org.hibernate.type.NullableType.nullSafeToString(Ljava.lang.Object;)Ljava.lang.String;(NullableType.java:93)
at org.hibernate.type.NullableType.nullSafeSet(Ljava.sql.PreparedStatement;Ljava.lang.Object;I)V(NullableType.java:140)
at org.hibernate.type.NullableType.nullSafeSet(Ljava.sql.PreparedStatement;Ljava.lang.Object;ILorg.hibernate.engine.SessionImplement
or;)V(NullableType.java:116)
at org.hibernate.loader.Loader.bindPositionalParameters(Ljava.sql.PreparedStatement;Lorg.hibernate.engine.QueryParameters;ILorg.hibe
rnate.engine.SessionImplementor;)I(Loader.java:1707)
at org.hibernate.loader.Loader.bindParameterValues(Ljava.sql.PreparedStatement;Lorg.hibernate.engine.QueryParameters;ILorg.hibernate
.engine.SessionImplementor;)I(Loader.java:1678)
at org.hibernate.loader.Loader.prepareQueryStatement(Lorg.hibernate.engine.QueryParameters;ZLorg.hibernate.engine.SessionImplementor
;)Ljava.sql.PreparedStatement;(Loader.java:1563)
at org.hibernate.loader.Loader.doQuery(Lorg.hibernate.engine.SessionImplementor;Lorg.hibernate.engine.QueryParameters;Z)Ljava.util.L
ist;(Loader.java:673)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Lorg.hibernate.engine.SessionImplementor;Lorg.hibernate.engine
.QueryParameters;Z)Ljava.util.List;(Loader.java:236)
at org.hibernate.loader.Loader.doList(Lorg.hibernate.engine.SessionImplementor;Lorg.hibernate.engine.QueryParameters;)Ljava.util.Lis
t;(Loader.java:2220)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Lorg.hibernate.engine.SessionImplementor;Lorg.hibernate.engine.QueryParameters;)
Ljava.util.List;(Loader.java:2104)
at org.hibernate.loader.Loader.list(Lorg.hibernate.engine.SessionImplementor;Lorg.hibernate.engine.QueryParameters;Ljava.util.Set;[L
org.hibernate.type.Type;)Ljava.util.List;(Loader.java:2099)
at org.hibernate.loader.criteria.CriteriaLoader.list(Lorg.hibernate.engine.SessionImplementor;)Ljava.util.List;(CriteriaLoader.java:
94)
at org.hibernate.impl.SessionImpl.list(Lorg.hibernate.impl.CriteriaImpl;)Ljava.util.List;(SessionImpl.java:1569)
at org.hibernate.impl.CriteriaImpl.list()Ljava.util.List;(CriteriaImpl.java:283)
at com.lgim.fmm.persistence.HibernateUtil.listUsingSubqueries(Ljava.lang.Object;Ljava.lang.Object;Ljava.lang.String;Ljava.lang.Strin
g;)Ljava.util.List;(HibernateUtil.java:574)
at com.lgim.fmm.web.processor.BaseUIProcessor.listBySubquery(Ljava.lang.Object;Ljava.lang.Object;Ljava.lang.String;Ljava.lang.String
;)Ljava.util.List;(BaseUIProcessor.java:319)
at com.lgim.fmm.web.processor.account.AccountSearchUIProcessor.processCurrentStateList()V(AccountSearchUIProcessor.java:327)
at com.lgim.fmm.web.processor.account.AccountSearchUIProcessor.processActionRequest(Ljava.lang.Object;Ljava.lang.String;Ljava.lang.S
tring;Ljava.lang.String;Ljava.lang.Integer;Ljava.lang.String;Ljava.lang.String;Ljava.lang.String;Ljava.lang.String;Ljava.lang.String;Ljava.l
ang.String;Ljava.lang.String;)V(AccountSearchUIProcessor.java:134)
at com.lgim.webui2.web.servlet.processor.WebProcessorServlet.process(Ljavax.servlet.http.HttpServletRequest;Ljavax.servlet.http.Http
ServletResponse;)V(WebProcessorServlet.java:162)
at com.lgim.webui2.web.servlet.environment.UserConfirmServlet.confirmUser(Ljavax.servlet.http.HttpServletRequest;Ljavax.servlet.http
.HttpServletResponse;)V(UserConfirmServlet.java:71)
at com.lgim.webui2.web.servlet.environment.UserConfirmServlet.doPost(Ljavax.servlet.http.HttpServletRequest;Ljavax.servlet.http.Http
ServletResponse;)V(UserConfirmServlet.java:50)
at javax.servlet.http.HttpServlet.service(Ljavax.servlet.http.HttpServletRequest;Ljavax.servlet.http.HttpServletResponse;)V(HttpServ
let.java:760)
at javax.servlet.http.HttpServlet.service(Ljavax.servlet.ServletRequest;Ljavax.servlet.ServletResponse;)V(HttpServlet.java:853)
at weblogic.servlet.internal.ServletStubImpl$ServletInvocationAction.run()Ljava.lang.Object;(ServletStubImpl.java:996)
at weblogic.servlet.internal.ServletStubImpl.invokeServlet(Ljavax.servlet.ServletRequest;Ljavax.servlet.ServletResponse;Lweblogic.se
rvlet.internal.FilterChainImpl;)V(ServletStubImpl.java:419)
at weblogic.servlet.internal.ServletStubImpl.invokeServlet(Ljavax.servlet.ServletRequest;Ljavax.servlet.ServletResponse;)V(ServletSt
ubImpl.java:315)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run()Ljava.lang.Object;(WebAppServletContext.java:6452)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(Lweblogic.security.subject.AbstractSubject;Ljava.security.PrivilegedActi
on;)Ljava.lang.Object;(Optimized Method)
at weblogic.security.service.SecurityManager.runAs(Lweblogic.security.acl.internal.AuthenticatedSubject;Lweblogic.security.acl.inter
nal.AuthenticatedSubject;Ljava.security.PrivilegedAction;)Ljava.lang.Object;(Optimized Method)
at weblogic.servlet.internal.WebAppServletContext.invokeServlet(Lweblogic.servlet.internal.ServletRequestImpl;Lweblogic.servlet.inte
rnal.ServletResponseImpl;)V(WebAppServletContext.java:3661)
at weblogic.servlet.internal.ServletRequestImpl.execute(Lweblogic.kernel.ExecuteThread;)V(ServletRequestImpl.java:2630)
This process works if I use a String in the correlated subquery. Currently ACTUAL_EVENT_DATE (an oracle date type) is mapped to java.util.Date. I have also tried mapping it to java.sql.Date and still get the same error.
Any ideas would be greatly appreciated.
Login
FAQ
Search