Announcement Announcement Module
Collapse
No announcement yet.
Problem with roo finder addon Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Problem with roo finder addon

    Hi,
    I'm using finder to generate searc code in my application. When I generate a finder with 'like', generated code raise an error:

    Code generated by roo:

    public static TypedQuery<Usuario> Usuario.findUsuariosByApellidoLike(String apellido) {
    if (apellido == null || apellido.length() == 0) throw new IllegalArgumentException("The apellido argument is required");
    apellido = apellido.replace('*', '%');
    if (apellido.charAt(0) != '%') {
    apellido = "%" + apellido;
    }
    if (apellido.charAt(apellido.length() - 1) != '%') {
    apellido = apellido + "%";
    }
    EntityManager em = Usuario.entityManager();
    TypedQuery<Usuario> q = em.createQuery("SELECT o FROM Usuario AS o WHERE LOWER(o.apellido) LIKE LOWER(:apellido)", Usuario.class);
    q.setParameter("apellido", apellido.toLowerCase());
    return q;
    }
    the LOWER wrapping the parameter ':apellido' is not understood. if I take off that LOWER function this method work, but with LOWER function on ':apellido' this method raise an exception.

    Thanks in advance.

  • #2
    That's very odd; one LOWER works but the other one causes an error? What database are you using, and what's the stacktrace? (Don't forget to wrap it in CODE tags when posting).

    FYI, I've tried to replicate this using Roo 1.1.4 and the HYPERSONIC_IN_MEMORY database, and it works fine.

    Comment


    • #3
      Hi Andrew,
      Sorry by the time in response, but I've been out because of tech trainning. It seems that DB2 has problem with LOWER if it receive a named parameter. Below I pasted some of the stack error, and the debug information generated by hibernate.
      Thanks for your help.

      Code:
      2011-06-14 12:15:01,351 [tomcat-http--6] DEBUG org.hibernate.hql.ast.QueryTranslatorImpl - HQL: SELECT o FROM ar.gov.gba.ed.genesis.model.Usuario AS o WHERE LOWER(o.apellido) LIKE LOWER(:apellido)
      2011-06-14 12:15:01,351 [tomcat-http--6] DEBUG org.hibernate.hql.ast.QueryTranslatorImpl - SQL: select usuario0_.id as id3_, usuario0_.apellido as apellido3_, usuario0_.password as password3_, usuario0_.dni as dni3_, usuario0_.log as log3_, usuario0_.nombres as nombres3_, usuario0_.oficina as oficina3_, usuario0_.username as username3_ from dge.user usuario0_ where lower(usuario0_.apellido) like lower(?)
      2011-06-14 12:15:01,351 [tomcat-http--6] DEBUG org.hibernate.hql.ast.ErrorCounter - throwQueryException() : no errors
      2011-06-14 12:15:01,398 [tomcat-http--6] DEBUG org.hibernate.jdbc.AbstractBatcher - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
      2011-06-14 12:15:01,398 [tomcat-http--6] DEBUG org.hibernate.jdbc.ConnectionManager - opening JDBC connection
      2011-06-14 12:15:01,398 [tomcat-http--6] DEBUG org.hibernate.SQL - select usuario0_.id as id3_, usuario0_.apellido as apellido3_, usuario0_.password as password3_, usuario0_.dni as dni3_, usuario0_.log as log3_, usuario0_.nombres as nombres3_, usuario0_.oficina as oficina3_, usuario0_.username as username3_ from dge.user usuario0_ where lower(usuario0_.apellido) like lower(?)
      2011-06-14 12:15:01,476 [tomcat-http--6] DEBUG org.hibernate.jdbc.AbstractBatcher - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
      2011-06-14 12:15:01,476 [tomcat-http--6] DEBUG org.hibernate.jdbc.ConnectionManager - aggressively releasing JDBC connection
      2011-06-14 12:15:01,476 [tomcat-http--6] DEBUG org.hibernate.jdbc.ConnectionManager - releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
      2011-06-14 12:15:01,476 [tomcat-http--6] DEBUG org.hibernate.util.JDBCExceptionReporter - could not execute query [select usuario0_.id as id3_, usuario0_.apellido as apellido3_, usuario0_.password as password3_, usuario0_.dni as dni3_, usuario0_.log as log3_, usuario0_.nombres as nombres3_, usuario0_.oficina as oficina3_, usuario0_.username as username3_ from dge.user usuario0_ where lower(usuario0_.apellido) like lower(?)]
      com.ibm.db2.jcc.b.nm: DB2 SQL Error: SQLCODE=-418, SQLSTATE=42610, SQLERRMC=null, DRIVER=3.50.152
      	at com.ibm.db2.jcc.b.wc.a(wc.java:579)
      	at com.ibm.db2.jcc.b.wc.a(wc.java:57)
      	at com.ibm.db2.jcc.b.wc.a(wc.java:126)
      	at com.ibm.db2.jcc.b.tk.c(tk.java:1901)
      	at com.ibm.db2.jcc.b.tk.d(tk.java:1889)
      	at com.ibm.db2.jcc.b.tk.a(tk.java:1416)
      	at com.ibm.db2.jcc.t4.db.g(db.java:138)
      	at com.ibm.db2.jcc.t4.db.a(db.java:38)
      	at com.ibm.db2.jcc.t4.t.a(t.java:32)
      	at com.ibm.db2.jcc.t4.sb.h(sb.java:141)
      	at com.ibm.db2.jcc.b.tk.N(tk.java:1387)
      	at com.ibm.db2.jcc.b.uk.Kb(uk.java:2461)
      	at com.ibm.db2.jcc.b.uk.e(uk.java:3101)
      	at com.ibm.db2.jcc.b.uk.yb(uk.java:535)
      	at com.ibm.db2.jcc.b.uk.executeQuery(uk.java:509)
      	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
      	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
      	at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
      	at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
      	at org.hibernate.loader.Loader.doQuery(Loader.java:802)
      	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
      	at org.hibernate.loader.Loader.doList(Loader.java:2533)
      	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
      	at org.hibernate.loader.Loader.list(Loader.java:2271)
      	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:452)
      	at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
      	at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
      	at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1268)
      	at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
      	at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:246)
      	at ar.gov.gba.ed.genesis.web.UsuarioController.findUsuariosByDocumentoEqualsOrApellidoLikeAndNombresLike(UsuarioController.java:25)
      	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      	at java.lang.reflect.Method.invoke(Method.java:597)
      	at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)
      	at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:426)
      	at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:414)
      	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790)
      	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)
      	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)
      	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:549)
      	at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
      	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
      	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
      	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
      	at org.springframework.orm.jpa.support.OpenEntityManagerInViewFilter.doFilterInternal(OpenEntityManagerInViewFilter.java:113)
      	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
      	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
      	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
      	at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77)
      	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
      	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
      	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
      	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
      	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
      	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
      	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
      	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
      	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
      	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
      	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
      	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
      	at com.springsource.insight.collection.tcserver.request.HttpRequestOperationCollectionValve.invoke(HttpRequestOperationCollectionValve.java:64)
      	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
      	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:857)
      	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
      	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:409)
      	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
      	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
      	at java.lang.Thread.run(Thread.java:662)
      2011-06-14 12:15:01,476 [tomcat-http--6] WARN  org.hibernate.util.JDBCExceptionReporter - SQL Error: -418, SQLState: 42610
      2011-06-14 12:15:01,476 [tomcat-http--6] ERROR org.hibernate.util.JDBCExceptionReporter - DB2 SQL Error: SQLCODE=-418, SQLSTATE=42610, SQLERRMC=null, DRIVER=3.50.152
      2011-06-14 12:15:01,476 [tomcat-http--6] WARN  org.hibernate.util.JDBCExceptionReporter - SQL Error: -727, SQLState: 56098
      2011-06-14 12:15:01,476 [tomcat-http--6] ERROR org.hibernate.util.JDBCExceptionReporter - DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-418;42610;, DRIVER=3.50.152
      2011-06-14 12:15:01,476 [tomcat-http--6] WARN  org.hibernate.util.JDBCExceptionReporter - SQL Error: -727, SQLState: 56098
      2011-06-14 12:15:01,476 [tomcat-http--6] ERROR org.hibernate.util.JDBCExceptionReporter - DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-418;42610;, DRIVER=3.50.152
      ...
      ...


      Originally posted by Andrew Swan View Post
      That's very odd; one LOWER works but the other one causes an error? What database are you using, and what's the stacktrace? (Don't forget to wrap it in CODE tags when posting).

      FYI, I've tried to replicate this using Roo 1.1.4 and the HYPERSONIC_IN_MEMORY database, and it works fine.

      Comment


      • #4
        Try pushing in that finder and modifying the query so that it casts that variable, as described here:

        http://dbaspot.com/ibm-db2/109946-pr...unction-2.html

        If it works, could you please log a JIRA issue to the effect that Roo generates invalid JPQL for DB2?

        Comment


        • #5
          I had the same problem.

          My environment is DB2 OS/390 that will be used from an IBM WebSphere Application Server (WAS) 6.1, but for now, I'm testing from Windows, using a STS 2.6.1.RELEASE with Roo 1.1.4.RELEASE [rev f787ce7].

          The application was built using HIBERNATE and DB2 whose dependencies were changed manually, since there is no OS/390 Hibernate dialect as far as I know.

          With a similar case, I obtained three errors:

          ERROR org.hibernate.util.JDBCExceptionReporter - DB2 SQL Error: SQLCODE=-418, SQLSTATE=42610, SQLERRMC=null, DRIVER=3.53.95 (Message Text:Use of parameter marker not valid)
          ERROR org.hibernate.util.JDBCExceptionReporter - DB2 SQL Error: SQLCODE=-516, SQLSTATE=26501, SQLERRMC=null, DRIVER=3.53.95 (Message Text:Prepared statement &2 not found)
          ERROR org.hibernate.util.JDBCExceptionReporter - DB2 SQL Error: SQLCODE=-514, SQLSTATE=26501, SQLERRMC=SQL_CURLH200C1, DRIVER=3.53.95 (Message Text:Prepared statement &2 not found.)

          It seems my database is providing a good detail of the errors

          However, the actual query works if I launch it manually: try to do that with the one that you write down above. Just substitute the ? with an appropriate String, as '%APELLIDO%'.

          In fact, I used the value that I found when debugging the problem. So I didn't know if it was a Roo problem or a JPA-Hibernate lack of the proper dialect.

          So I pushed in that finder as Andrew suggested, but I made the lower case using Java, as you it appears in your code (I thing that in my .aj generated code there wasn't any lower case, but I'm not sure) AND I just delete the second LOWER within the HSQL:

          TypedQuery<Usuario> q = em.createQuery("SELECT o FROM Usuario AS o WHERE LOWER(o.apellido) LIKE :apellido", Usuario.class);

          Regarding the Andrew's proposal, I don't know how to write this Query, since I'm not creating the prepared Statement by myself, I tried this:

          TypedQuery<Usuario> q = em.createQuery("SELECT o FROM Usuario AS o WHERE LOWER(o.apellido) LIKE LOWER(CAST(:apellido AS CHAR(50)))", Usuario.class);

          But I obtained these errors:
          ERROR org.hibernate.hql.PARSER - line 1:121: expecting CLOSE, found '('
          WARN org.hibernate.hql.ast.HqlParser - processEqualityExpression() : No expression to process!

          The solution of avoiding the LOWER in the HSQL variable worked for me, so it isn't worthy to investigate what's wrong with the query with the CAST.

          I hope it helps mgpisano to continue with his project, and I'm sorry for not helping Roo to improve.

          Comment


          • #6
            According to Andrew

            I've been trying and I've found the solution.
            Below I show the originally generated code by Roo and how it should be when using Hibernate and DB2

            Code:
            The code generate by Roo is: 
            TypedQuery<Usuario> q = em.createQuery("SELECT o FROM Usuario AS o WHERE LOWER(o.apellido) LIKE LOWER(:apellido)", Usuario.class);
            
            
            and it should be:
            
            
            TypedQuery<Usuario> q = em.createQuery("SELECT o FROM Usuario AS o WHERE LOWER(o.apellido) LIKE LOWER(CAST(:apellido as string))", Usuario.class);
            
            the cast must be done using hibernate types (if you are using hibernate, i.e. string)
            I'll follow Andrews advice and log it as Jira Issue.

            I hope, this get resolved soon.

            Thanks all.






            Originally posted by jbbarquero View Post
            I had the same problem.

            My environment is DB2 OS/390 that will be used from an IBM WebSphere Application Server (WAS) 6.1, but for now, I'm testing from Windows, using a STS 2.6.1.RELEASE with Roo 1.1.4.RELEASE [rev f787ce7].

            The application was built using HIBERNATE and DB2 whose dependencies were changed manually, since there is no OS/390 Hibernate dialect as far as I know.

            With a similar case, I obtained three errors:

            ERROR org.hibernate.util.JDBCExceptionReporter - DB2 SQL Error: SQLCODE=-418, SQLSTATE=42610, SQLERRMC=null, DRIVER=3.53.95 (Message Text:Use of parameter marker not valid)
            ERROR org.hibernate.util.JDBCExceptionReporter - DB2 SQL Error: SQLCODE=-516, SQLSTATE=26501, SQLERRMC=null, DRIVER=3.53.95 (Message Text:Prepared statement &2 not found)
            ERROR org.hibernate.util.JDBCExceptionReporter - DB2 SQL Error: SQLCODE=-514, SQLSTATE=26501, SQLERRMC=SQL_CURLH200C1, DRIVER=3.53.95 (Message Text:Prepared statement &2 not found.)

            It seems my database is providing a good detail of the errors

            However, the actual query works if I launch it manually: try to do that with the one that you write down above. Just substitute the ? with an appropriate String, as '%APELLIDO%'.

            In fact, I used the value that I found when debugging the problem. So I didn't know if it was a Roo problem or a JPA-Hibernate lack of the proper dialect.

            So I pushed in that finder as Andrew suggested, but I made the lower case using Java, as you it appears in your code (I thing that in my .aj generated code there wasn't any lower case, but I'm not sure) AND I just delete the second LOWER within the HSQL:

            TypedQuery<Usuario> q = em.createQuery("SELECT o FROM Usuario AS o WHERE LOWER(o.apellido) LIKE :apellido", Usuario.class);

            Regarding the Andrew's proposal, I don't know how to write this Query, since I'm not creating the prepared Statement by myself, I tried this:

            TypedQuery<Usuario> q = em.createQuery("SELECT o FROM Usuario AS o WHERE LOWER(o.apellido) LIKE LOWER(CAST(:apellido AS CHAR(50)))", Usuario.class);

            But I obtained these errors:
            ERROR org.hibernate.hql.PARSER - line 1:121: expecting CLOSE, found '('
            WARN org.hibernate.hql.ast.HqlParser - processEqualityExpression() : No expression to process!

            The solution of avoiding the LOWER in the HSQL variable worked for me, so it isn't worthy to investigate what's wrong with the query with the CAST.

            I hope it helps mgpisano to continue with his project, and I'm sorry for not helping Roo to improve.

            Comment


            • #7
              Originally posted by mgpisano View Post
              A
              [...]
              Code:
              [...]
              
              
              TypedQuery<Usuario> q = em.createQuery("SELECT o FROM Usuario AS o WHERE LOWER(o.apellido) LIKE LOWER(CAST(:apellido as string))", Usuario.class);
              
              the cast must be done using hibernate types (if you are using hibernate, i.e. string)
              [...]
              Good job!

              I made the typical mistake: I tried to write raw SQL in a JPA/Hibernate Query.

              Can you write down the link to the JIRA issue?

              Thanks

              Comment


              • #8
                The link to JIRA Issue is :

                https://jira.springsource.org/browse/ROO-2513

                I hope Roo people fix it.

                See you!


                Originally posted by jbbarquero View Post
                Good job!

                I made the typical mistake: I tried to write raw SQL in a JPA/Hibernate Query.

                Can you write down the link to the JIRA issue?

                Thanks

                Comment


                • #9
                  At least, it has a vote (and a watcher)

                  Comment

                  Working...
                  X