JPA Criteria: Convert int to String then select from substring of resulting String

Question

I have a String as parameter (which is in fact a valueOf(an Integer) and want to compare it to a substring of a int value in DB. Here is my code:

ClinicPatients clp = null;

// Get the criteria builder instance from entity manager
final CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();     
// Create criteria query and pass the value object which needs to be populated as result
CriteriaQuery<ClinicPatients> criteriaQuery = cb.createQuery(ClinicPatients.class);     
// Tell to criteria query which tables/entities you want to fetch
final Root<ClinicPatients> rootClp = criteriaQuery.from(ClinicPatients.class);

criteriaQuery.select(rootClp);

Expression<String> e1 = cb.function("CONVERT", String.class, rootClp.get(idCPFieldName));

Predicate p1 = cb.equal(cb.substring(e1, 1, 3), idClinicPatient);
criteriaList.add(p1);

criteriaQuery.where(p1);

// Here entity manager will create actual SQL query out of criteria query
final TypedQuery<ClinicPatients> query = getEntityManager().createQuery(criteriaQuery);

List<ClinicPatients> lClps = (List<ClinicPatients>) query.getResultList();
if(lClps.size() > 0)
    clp = lClps.get(0);

return clp;

I get an error and my guess is that the conversion to String is never done then Hibernate tries to substring some weird value, but i'm not sure of anything at this point. Here is the trace I get:

> DEBUG could not extract ResultSet [n/a] com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an
> error in your SQL syntax; check the manual that corresponds to your
> MySQL server version for the right syntax to use near '), 1, 3)='005''
> at line 1     at
> sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
>   at
> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
>   at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
>   at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
>   at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)     at
> com.mysql.jdbc.Util.getInstance(Util.java:386)    at
> com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)    at
> com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4190)    at
> com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4122)    at
> com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)     at
> com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)  at
> com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2818)   at
> com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2157)
>   at
> com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2324)
>   at
> org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:80)
>   at org.hibernate.loader.Loader.getResultSet(Loader.java:2065)   at
> org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1862)
>   at
> org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1838)
>   at org.hibernate.loader.Loader.doQuery(Loader.java:909)     at
> org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354)
>   at org.hibernate.loader.Loader.doList(Loader.java:2553)     at
> org.hibernate.loader.Loader.doList(Loader.java:2539)  at
> org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2369)    at
> org.hibernate.loader.Loader.list(Loader.java:2364)    at
> org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:496)   at
> org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:387)
>   at
> org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:231)
>   at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1264)   at
> org.hibernate.internal.QueryImpl.list(QueryImpl.java:103)     at
> org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:573)     at
> org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:449)
>   at
> org.hibernate.jpa.criteria.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:67)
>   at
> ca.chronometriq.commons.cmqfacade.ClinicPatientsFacade.findByIDClinicPatientFirstThreeDigitsVerificationNumber(ClinicPatientsFacade.java:150)
>   at
> ca.chronometriq.commons.cmqfacade.ClinicPatientsFacade$$FastClassByCGLIB$$7cc86440.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
> ca.chronometriq.commons.cmqfacade.ClinicPatientsFacade$$EnhancerByCGLIB$$8afa98d9.findByIDClinicPatientFirstThreeDigitsVerificationNumber(<generated>)    at
> ca.chronometriq.webterm.restapi.AdminModule.findByThreeDig(AdminModule.java:1474)
>   at
> ca.chronometriq.webterm.restapi.AdminModule$$FastClassByCGLIB$$2e650668.invoke(<generated>)
>   at
> org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
>   at
> org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:627)
>   at
> ca.chronometriq.webterm.restapi.AdminModule$$EnhancerByCGLIB$$e2195e01.findByThreeDig(<generated>)
>   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)  at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
>   at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>   at java.lang.reflect.Method.invoke(Method.java:606)     at
> com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60)
>   at
> com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$TypeOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:185)
>   at
> com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75)
>   at
> com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:288)
>   at
> com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
>   at
> com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108)
>   at
> com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
>   at
> com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84)
>   at
> com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1469)
>   at
> com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1400)
>   at
> com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1349)
>   at
> com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1339)
>   at
> com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:416)
>   at
> com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:537)
>   at
> com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:699)
>   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:52)
>   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:611)
>   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:409)
>   at
> org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1044)
>   at
> org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)
>   at
> org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1721)
>   at
> org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1679)
>   at
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
>   at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
>   at java.lang.Thread.run(Thread.java:744)

Any idea?

Answer 1

Have you tried using as:

Expression<String> e1 = rootClp.get(idCPFieldName).as(String.class);

There used to be a bug in this functionality, so you might face that if you're using an older version.

Answer 2

Would this work...

Predicate p1 = cb.like(cb.substring(e1, 1, 3), idClinicPatient);

I know sometimes I have trouble with ints and strings in DB but this...

select * from TABLE WHERE SUBSTR( ID_INT_COL, 0, 1 ) = '1'

Seems to work in my local JDBC so I dunno