2 Replies Latest reply on Jul 11, 2012 8:27 PM by 948590

    Eclipselink JPA named query not ignoring white spaces.


      I am using JPA2.0 Eclipselink 2.3.2. I have database field CHAR(12) mapped in entity as a String. I have named query to retrieve entity by passing primary key input parameter.

      In database value stored only with two characters and remaining are spaces because it is CHAR(12).

      Example Table X column id (CHAR12) value "IS "

      Named query looks like this

      @NamedQuery(name = "getById",
      query = "SELECT sec " +
      " FROM TABLE ref " +
      " WHERE ref.id = :idValue "

      list = em.createNamedQuery("getById ")
      .setParameter("idValue ", "IS")

      I am passing parameter string value as a "IS" to the named query but my named query is not returning any list. If I pass with white spaces "IS " it is retrieving.

      I don't want to pad with spaces, I would like to pass only "IS". Even I don't want to use Oracle trim function in my named query. trim (training from t.id) -- I don't want to use this (I know it works with this option)

      If I execute sql query in database it works fine. Select * from Table where id = 'IS'; - it shows the rows

      Why named query not retrieving the results, how to indicate namedquery to ignore char spaces while executing query?

      How to get the result from naedquery without using these two above options. Could someone please help me with this issue?

      Edited by: 945587 on Jul 10, 2012 1:16 PM
        • 1. Re: Eclipselink JPA named query not ignoring white spaces.
          I would recommend you use a VARCHAR not a CHAR.

          It is mostly likely an issue with how the value is bound in your JDBC driver. You could try turning binding off, using the query hint "eclipselink.jdbc.bind-parameters"="false".
          • 2. Re: Eclipselink JPA named query not ignoring white spaces.

            Thanks for Reply. Changing to VARCHAR is ruled out our DB team want to use only CHAR.

            By turning off bind parameters using the query hint on my named query. I am able to get the expected result. It is ignoring the CHAR spaces and retrieving the rows which are expected.

            I am using like this - hints = { @QueryHint(name = QueryHints.BIND_PARAMETERS, value = "false") }

            I could see in below link there will be performance impact if we disable bindparamters.

            The named query which am using will be invoked many times on our application. Do you know is there any way we can avoid performance impact OR any other approach to solve this problem.

            Thanks for you valuable suggestions.

            Edited by: 945587 on Jul 11, 2012 1:26 PM