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.