CODEWITHSUNDEEP
sqloracle-databasemybatis
Woodsman
Woodsman

Reputation: 1179

Query works in SQL Developer but has error in MyBatis

My query runs fine in SQL Developer, but when I copy the same query into the mapper XML file for MyBatis, I get an ORA-00900: invalid SQL statement.

Subset of mapper file:

    <select id="getTotalUniqueUsersByPage">
            resultType="KeyValuePair" 
            parameterType="RequestFilter">
        select KEY as key,sum(single_value) as value from (
            select schcm.title as key
            ,schuc.usage_count AS single_value 
            from usage_count schuc
            inner join content_master schcm
            on schcm.trace_id=schuc.trace_key
            where schcm.pagetype='Page'
         ) 
         GROUP by KEY
         order by value desc
    </select>

### The error occurred while setting parameters
### SQL: resultType="KeyValuePair" parameterType="RequestFilter">         select KEY as key,sum(single_value) as value from (             select schcm.title as key             ,schuc.usage_count AS single_value              from usage_count schuc             inner join content_master schcm             on schcm.trace_id=schuc.trace_key             where schcm.pagetype='Page'                                             )           GROUP by KEY          order by value desc
### Cause: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

For the most part, regular SQL that runs in SQL Developer also runs when directly pasted into a MyBatis mapper file. Well, mostly anyway. I did remember to check for a semicolon at the end, but didn't make that mistake this time. What I'd like is for the MyBatis call to work like the SQL Developer call.

Update: Tried 

   <select id="getTotalUniqueUsersByPage">
            resultType="KeyValuePair" 
           parameterType="RequestFilter">
       select 'SomeTitle' as "Key", 55 as "Value" from dual
    </select>

but that call fails as well with the same "ORA-00900: invalid SQL statement" In deference to comments below, I surrounded the alias names so that the reserved word "Key" should work, and indeed it works find in SQLDeveloper.

Upvotes: 1

Views: 845

Answers (1)

Woodsman
Woodsman

Reputation: 1179

So the problem was not the SQL. The problem was the extra closing > tag in the XML.

 <select id="getTotalUniqueUsersByPage">
            resultType="KeyValuePair" 
           parameterType="RequestFilter">
       select 'SomeTitle' as "Key", 55 as "Value" from dual
    </select>

should be

 <select id="getTotalUniqueUsersByPage"
            resultType="KeyValuePair" 
           parameterType="RequestFilter">
       select 'SomeTitle' as "Key", 55 as "Value" from dual
    </select>

To me, this is an XML validation error, but I'm not sure why it did not show up as such. I'm surprised it made it all the way to Oracle instead of complaining about bad XML instead.

Upvotes: 1

Related Questions