1

I have in sql this query:

SELECT 
SUBSTR(report_number, STRPOS(report_number, '/SW/')),
SUBSTR(report_number,STRPOS(report_number, ''), STRPOS(report_number, '/')-1) 
FROM 
do_study
ORDER BY 
SUBSTR(report_number, STRPOS(report_number, '/SW/')),
CAST(SUBSTR(report_number,STRPOS(report_number, ''), STRPOS(report_number, '/')-1)as decimal(6,0)) 

Result:

"/SW/2015";"2"
"/SW/2017";"1"
"/SW/2017";"3"
"/SW/2017";"10"
"/SW/2017";"11"

And it do what I want. Now I need make Order By in Criteria Query. I write this:

List<Order> list = new ArrayList<>();
list.add(cb.asc(
        cb.substring(queryRoot.get(DoStudy_.reportNumber),
                cb.locate(queryRoot.get(DoStudy_.reportNumber), "/SW/")
        )
));
list.add(cb.asc(
                cb.substring(queryRoot.get(DoStudy_.reportNumber),
                        cb.locate(queryRoot.get(DoStudy_.reportNumber), ""),
                        cb.diff(
                               cb.locate(queryRoot.get(DoStudy_.reportNumber), "/"), 1
                        )
                )
 ));

return list;

Result:

"/SW/2015";"2"
"/SW/2017";"1"
"/SW/2017";"10"
"/SW/2017";"11"
"/SW/2017";"3"

I think it interpret second col as string. What should I do to get expected result? I try this:

list.add(cb.asc(
                cb.toInteger((Expression<Integer>)(Expression<?>)cb.substring(queryRoot.get(DoStudy_.reportNumber),
                        cb.locate(queryRoot.get(DoStudy_.reportNumber), ""),
                        cb.diff(
                               cb.locate(queryRoot.get(DoStudy_.reportNumber), "/"), 1
                        )
                )
       )
 ));

But it doesn't change anything. Thank for help.

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
Mateusz
  • 83
  • 9
  • 1
    Possible duplicate of [How to properly cast string to number with JPA2 Criteria API?](http://stackoverflow.com/questions/9394176/how-to-properly-cast-string-to-number-with-jpa2-criteria-api) – pozs Jan 30 '17 at 13:56

0 Answers0