Error with query in java that works under database directly-CodePudding

I have this POSTGRESQL query:

select distinct on (a.id) a.id, p.app
from application a inner join package p on a.app_id = p.app_id 
where p.status = 'r'
ORDER BY a.app_id, (regexp_matches(p.app_ver, '^(\d )\.(\d )\.(\d )'))[1]::integer DESC,
          (regexp_matches(p.app_ver, '^(\d )\.(\d )\.(\d )'))[2]::integer DESC,
          (regexp_matches(p.app_ver, '^(\d )\.(\d )\.(\d )'))[3]::integer DESC

And this seems to work perfectly when I run in on the database directly. (Its returning all the packages with latest version) But when I call this query from Java method I get the error And I see from logs that the error is : ERROR: syntax error at or near ":" at character 593 and it seems that this created query translates to

select distinct on (a.id) a.id, p.app
from application a inner join package p on a.app_id = p.app_id 
where p.status = 'r'
ORDER BY a.app_id, (regexp_matches(p.app_ver, '^(\d )\.(\d )\.(\d )'))[1]:integer DESC,
          (regexp_matches(p.app_ver, '^(\d )\.(\d )\.(\d )'))[2]:integer DESC,
          (regexp_matches(p.app_ver, '^(\d )\.(\d )\.(\d )'))[3]:integer DESC

Like its missing one : . The way I pass this query is:

String query = BASE_QUERY  
                "WHERE  p.status = :status ORDER BY a.app_id, (regexp_matches(p.app_ver, '^(\\d )\\.(\\d )\\.(\\d )'))[1]::integer DESC,\n"  
                "          (regexp_matches(p.app_ver, '^(\\d )\\.(\\d )\\.(\\d )'))[2]::integer DESC,\n"  
                "          (regexp_matches(p.app_ver, '^(\\d )\\.(\\d )\\.(\\d )'))[3]::integer DESC";
        List<Application> fullList = entityManager.createNativeQuery(appListQuery, "Application")
            .setParameter("status", PACKAGE_READY.getValue())
            .getResultList();

Also another ERROR from log is the : Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet

Data from table colum app_ver is like:

21.9.0-3-dev
21.9.0-3-dev-03
21.9.0-6
3.0.13-1
3.0.13-1
3.0.13-1
3.0.13-1
21.9.0-2
21.9.0-2
3.0.13-1
21.9.0-2
21.9.0-2
21.9.0-144
3.0.13-1
21.9.0-2
21.9.0-4-devtest
21.9.0-170
21.9.0-170
21.9.0-170
21.9.0-170
21.9.0-170
21.9.0-170
21.9.0-170
21.9.0-170

What could be wrong with this ?

CodePudding user response：

I'm not sure of the details but it seems that hibernate does some processing of your query, probably the : is some special char for it

did you tried to escape it? How to escape colon `:` within a native SQL query in Hibernate?

or put 4 of them given that when you use two in the query being run they become just 1?

CodePudding user response：

Hibernate interprets colons its own way, so you need to write the cast in another form.

This should work:

String query = BASE_QUERY  
                "WHERE  p.status = :status ORDER BY a.app_id, CAST((regexp_matches(p.app_ver, '^(\\d )\\.(\\d )\\.(\\d )'))[1] AS integer) DESC,\n"  
                "          CAST((regexp_matches(p.app_ver, '^(\\d )\\.(\\d )\\.(\\d )'))[2] AS integer) DESC,\n"  
                "          CAST((regexp_matches(p.app_ver, '^(\\d )\\.(\\d )\\.(\\d )'))[3] AS integer) DESC";