what is the best way to handle reserved key words of PostgreSQL when using Mybatis

Question

when I created a column like name or password and so on in PostgreSQL 13. The PostgreSQL may seem it as a reserved key word. so the MyBatis SQL would like this:

INSERT INTO apple_server_notification_record (created_time, updated_time, notification_type, auto_renew_product_id, auto_renew_status, auto_renew_status_change_date, auto_renew_status_change_date_ms, auto_renew_status_change_date_pst, environment, `password`, bid, bvrs) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

it will add `` automaticlly in the SQL. But when execute the sql in PostgreSQL 13. show this error:

org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: org.postgresql.util.PSQLException: ERROR: syntax error at or near "`"
  Position: 249
### The error may exist in class path resource [mybatis/mapper/dolphin/AppleServerNotificationRecordMapper.xml]
### The error may involve com.dolphin.soa.post.dao.AppleServerNotificationRecordMapper.insertSelective-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO apple_server_notification_record (created_time, updated_time, notification_type, auto_renew_product_id, auto_renew_status, auto_renew_status_change_date, auto_renew_status_change_date_ms, auto_renew_status_change_date_pst, environment, `password`, bid, bvrs) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
### Cause: org.postgresql.util.PSQLException: ERROR: syntax error at or near "`"
  Position: 249
; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at or near "`"
  Position: 249

I could solved it by change the columns password to apple_auth_password, I known it will fix the problem. But In my code, I must tranform the mapping explicit in receive request from apple. My entity define like this:

private String password;

any good suggestion to solve the problem? what would you do when encount this problem?

You are showing back-ticks here. Are those really single-quote marks that you are seeing? — Basil Bourque, Jul 04 '21 at 07:52
Back ticks are of no use for quoting identifiers in Postgres. They're native to MySQL and MariaDB. For Postgres (and standard SQL) double quotes must be used to quote identifiers. — sticky bit, Jul 04 '21 at 07:59
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS — , Jul 04 '21 at 09:58

score 0 · Answer 1 · answered Jul 15 '21 at 12:33

you could change the auto generator config code config of Mybatis-generator:

  <property name="autoDelimitKeywords" value="true"/>
    <!--
    the beginningDelimiter and endingDelimiter using " by default
    when using PostgreSQL using "
    when using MySQL, we should change to `
     -->
    <property name="beginningDelimiter" value="&quot;"/>
    <property name="endingDelimiter" value="&quot;"/>

what is the best way to handle reserved key words of PostgreSQL when using Mybatis

1 Answers1