0

I am using jpa with openjpa and i have table with varchar that represent as int in object.

column: "TRIES_ERR" (varchar)

Entity: 

public class Book implements Serializable{

    int triesErrAsInt;
    String eMail

    @Column(name="TRIES_ERR")
    public int getTriesErrAsInt() {
        return triesErrAsInt;
    }
    public void setTriesErrAsInt(int triesErrAsInt) {
        this.triesErrAsInt = triesErrAsInt;
    }

    @Column(name="EMAIL")
    public String getEMail() {
      return eMail;
    }
    public void setEMail(String string) {
      eMail = string;
    }

}

I try to write a query with order by TRIES_ERR but as int and not as varchar.

I have to use jpql (and not native query)

This is my query that I try and didn't work:

SELECT x 
FROM Book x 
ORDER BY x.triesErrAsInt ASC

returned rows are ordered as varchar and not as integer

I get as result: 12,13,14,2,3 instead 2,3,12,13,14

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Idan
  • 901
  • 4
  • 13
  • 29

1 Answers1

0

use this:

SELECT x from Book x order by cast(x.triesErrAsInt as int) asc
geert3
  • 7,086
  • 1
  • 33
  • 49
  • not working.. getting Exception: "Encountered "by" but expected: ["AVG", "COUNT", "MAX", "MIN", "SUM", ]." – Idan Jan 08 '15 at 14:54
  • i was implying you put the new `order by` after the already present `select x from book x`. Updated the answer to reflect this. – geert3 Jan 08 '15 at 15:05
  • this my Exception: Encountered "cast (" at character 31, but expected: [".", "AVG", "COUNT", "MAX", "MIN", "SUM", ]. (at the first try i wrote by mistake double "by") – Idan Jan 08 '15 at 15:37
  • it's possible your JPA implementation doesn't support it. What are you using (hibernate, eclipselink, what version?) – geert3 Jan 08 '15 at 16:18
  • jpa 1.0. and OpenJPA 1.2.1 – Idan Jan 08 '15 at 19:04
  • 1
    I just had the same problem, it looks like OpenJPA doesn't have CAST, but CONCAT(x.column, '') achieves the same result – Gustavo Ulises Arias Méndez Jul 03 '19 at 15:01
  • working solution using Spring Data and hibernate, see this link for details https://stackoverflow.com/questions/46188918/casting-integer-to-string-in-jpql – David Canós Jul 01 '21 at 08:01