Java Spring & MyBatis - How can I map "LEVEL" db column in XML file

Question

I am a newbie in Java Spring and My Batis and I have a problem mapping the DB column that is a keyword in Oracle in MyBatis 3.4 XML file.

I am using Java Spring 2.2.11, Oracle 19C, and MyBatis 3.4.0.

Mapper XML file:

<resultMap id="regionResultMap" type="org.idashboard.entity.Region">
    <id column="code" property="code"/>
    <result column="parent_code" property="parentCode"/>
    <result column="ancestors" property="ancestors"/>
    <result column="name" property="name"/>
    <result column="level" property="level"/>
    <result column="sort" property="sort"/>
    <result column="remark" property="remark"/>
</resultMap>

Generated SQL:

SELECT code,
       parent_code,
       ancestors,
       name,
       level,
       sort,
       remark
  FROM region
 WHERE code = '110102';

Error description: [Error] Execution (30: 8): ORA-01788: CONNECT BY clause required in this query block

The problem occurs because I used "LEVEL" column in region table.

How can I solve this issue? And how can I use column names that are the keywords in Oracle?

Thanks in advance!

Shawrup · Accepted Answer · 2020-12-05 04:33:31Z

1

try

SELECT code,
       parent_code,
       ancestors,
       name,
       "level",
       sort,
       remark
  FROM region
 WHERE code = '110102';

In the case where you need to use keywords in query, oracle uses double quotes (") to identify the word as not a keyword. In mysql you will need to use backticks (`) for the same purpose.

edited Dec 5, 2020 at 4:33

answered Dec 4, 2020 at 9:54

Shawrup

2,8142 gold badges15 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mark Rotteveel Over a year ago

Please don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes.

Beqir kuci · Accepted Answer · 2020-12-04 11:21:32Z

1

I think we have two options in oracle

Firstly is the use of alias in Query but in this case doesn't solve anything for you because you mapped it with the name level in xml

Second you can use double quotes (", eg "level"),in MySQL defaults is using backticks (`)

edited Dec 4, 2020 at 11:21

answered Dec 4, 2020 at 9:50

Beqir kuci

931 silver badge5 bronze badges

Collectives™ on Stack Overflow

Java Spring & MyBatis - How can I map "LEVEL" db column in XML file

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related