KingbaseES V8适配mybatisPlus问题处理--——level关键字做字段名

问题概述

kingbase中关键字做表名、字段名时，在查询语句中会优先解析为关键字，想要解析为字段名需要加双引号；而在mybatisPlus插件中，类似Hibernate，sql语句由框架自动生成，默认生成的sql是不会对字段名加双引号的；因此会导致查询可能报错。

示例程序

示例表：

CREATE TABLE USER1
(
	id BIGINT NOT NULL ,
	level VARCHAR(30) NULL DEFAULT NULL ,
	age INT NULL DEFAULT NULL ,
	email VARCHAR(50) NULL DEFAULT NULL ,
	PRIMARY KEY (id)
);

INSERT INTO "USER1" (id, "LEVEL", age, email) VALUES
(1, '10', 18, 'test1@baomidou.com'),
(2, '20', 20, 'test2@baomidou.com'),
(3, '30', 28, 'test3@baomidou.com'),
(4, '40', 21, 'test4@baomidou.com'),
(5, '50', 24, 'test5@baomidou.com');

Note: level为kingbase关键字，在层次查询中，level伪列代表当前元组所在的层；level做字段名时，sql语句中需要加双引号，才可被解析为字段名；

mybatisPlus示例程序

实体类:

package com.example.demo1.entity;

import lombok.Data;

@Data
public class User1 {
    private Long id;
    private String level;
    private Integer age;
    private String email;
}

接口映射：

package com.example.demo1.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.demo1.entity.User1;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;

@Component(value ="user1Mapper")
public interface User1Mapper extends BaseMapper<User1> {
}

application.yml：

spring:
  datasource:
    driver-class-name: com.kingbase8.Driver
    schema: classpath:db/schema-h2.sql
    data: classpath:db/data-h2.sql
    url: jdbc:kingbase8://***.***.***.***:54321/TEST1
    username: SYSTEM
    password: ******

测试程序：

@RunWith(SpringRunner.class)
@SpringBootTest
@MapperScan("com.example.demo1.mapper")
class Demo1ApplicationTests {

	@Autowired(required = false)
	private User1Mapper user1Mapper;

	@Test
	public void testSelect() {
		System.out.println(("----- selectAll method test ------"));
		List<User1> user1List = user1Mapper.selectList(null);
		Assert.assertEquals(5, user1List.size());
		user1List.forEach(System.out::println);
	}

}

以上程序如果没有level字段，运行是正常的；但是出现level关键字做字段名的情况下，会报错：

org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: com.kingbase8.util.KSQLException: ERROR: CONNECT BY clause required in this query block
  Position: 12
### The error may exist in com/example/demo1/mapper/User1Mapper.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT  id,level,age,email  FROM user1
### Cause: com.kingbase8.util.KSQLException: ERROR: CONNECT BY clause required in this query block
  Position: 12
; bad SQL grammar []; nested exception is com.kingbase8.util.KSQLException: ERROR: CONNECT BY clause required in this query block
  Position: 12

以上报错信息可以看到框架生成的sql为：
SQL: SELECT id,level,age,email FROM user1
此处，level未加双引号，会被解析为level伪列，引起程序结果异常。

问题处理

kingbase中查询语句出现关键字做字段名，需要将字段名大写并外加双引号；mybatisPlus可以自动生成sql，也支持传统的mybatis方式指定映射的sql语句。映射接口可做如下修改，例：

@Component(value ="user1Mapper")
public interface User1Mapper extends BaseMapper<User1> {
    @Select("SELECT id,\"LEVEL\",age,email FROM user1")
    List<User1> selectList();
}

以上改动主要是人工指定select List映射的sql，把level关键字大写并外加双引号。
修改完成后，再次测试程序，运行正常。sql中出现其它kingbase关键字可做类似处理。

【更多人大金仓数据库信息，详见https://help.kingbase.com.cn/】