Precautions for Kingbase database configuration

Usage specification

  • It is strongly recommended that table names and field names be in lowercase or uppercase, and the default case sensitivity of NPC Jincang database
  • Creating functions and stored procedures can be created using Oracle syntax, which is compatible with Oracle syntax.
  • Avoid using keywords to create tables, views, functions, etc., and key references SQL keyword document
  • For GROUP BY query, you need to specify the field in the SELECT. If you do not specify that GROUP BY cannot be used, you can use DISTINCT ON
  • The ROWNUM pseudo column is used for paging. You can refer to Oracle syntax or limit for paging, or postgresql for syntax
  • Support sequence, the usage is the same as ORACLE
  • Empty fields judged as non NULL in the where condition are not supported, as follows:
--Unsupported writing
--Suggested writing, use length Functions, support oracle/mysql/postgresql/kingbase And other databases

Introduce dependency

In POM XML is configured as follows:

The driver package needs to be installed locally or imported through systemPath:

	<!-- scope>system</scope>

Druid database connection pool

Configuration example of Druid gold warehouse V82:

filters= stat,log4j

Common types of filters:
filter:stat for monitoring statistics
Filter for log: log4j
The filter:wall for defending sql injection does not support domestic databases at present

Mode assignment

The public mode is used by default. It is recommended to specify the mode as follows:


rdp_ The server will automatically become uppercase RDP_ SERVER,rdp_ Server and "rdp_server" are equal

Mybatis considerations

  • MyBatis 3 and later versions are required
  • The recommended version is mybatis3.0 2.8,Mybatis3.3.0 and mybatis3 4.5 the official has passed the verification test of version adaptation
  • KingbaseES supports array data types. You need to add ArrayTypeHandler in MyBatis to handle this with special types;
  • KingbaseES supports json data types. In Mybatis, you need to add JsonTypeHandler to handle this special type;
  • KingbaseES supports hstore data type. In Mybatis, you need to add HstoreTypeHandler to handle this special type;
  • KingbaseES supports XML data types. In Mybatis mapping, XML data types need to be marked, such as:
insert into t_xml(value) values ((#{content})::xml)
  • KingbaseES String transfer Blob field type needs to be processed by adding a conversion class. Refer to the following:
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import java.sql.*;

 * @author:WC
 * @date: 2021/6/4
 * @time: 10:09
public class BlobToStringTypeHandler extends BaseTypeHandler<String> {
    public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {
        byte[] bytes = parameter.getBytes();
        ByteArrayInputStream bis = new ByteArrayInputStream(bytes);
        ps.setBinaryStream(i, bis, bytes.length);

    public String getNullableResult(ResultSet rs, String columnName) throws SQLException {
        Blob blob = rs.getBlob(columnName);
        return new String(blob.getBytes(1, (int)blob.length()));

    public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        Blob blob = rs.getBlob(columnIndex);
        return new String(blob.getBytes(1, (int)blob.length()));

    public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        Blob blob = cs.getBlob(columnIndex);
        return new String(blob.getBytes(1, (int)blob.length()));

Mybatis plus configuration

If you use mybatis plus for rapid development, it is recommended to specify the mode. The configuration method is as follows:

            #Primary key type 0: "self increment of database ID", 1: "user input ID",2: "globally unique ID (unique ID of digital type)", 3: "globally unique ID UUID";
      		id-type: NONE #oracle compatible, NPC Jincang recommends to configure it as NONE
            schema: rdp_server #Specify mode

Trigger considerations

If a trigger is used to insert a data self incrementing primary key, idtype cannot be used for annotations in the entity Auto, otherwise the trigger cannot be triggered

@TableId(value = "user_id",type = IdType.NONE)

Paging configuration

Recommendation 1: use v3 Mybatis plus version above 3.0 (including)

Recommendation 2: modify the dialect to ORACLE or POSTGRE_SQL, everything.

 * Paging plug-in
public PaginationInterceptor paginationInterceptor() {
    dbType = dialectType;
    PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
    }else if("MySQL".equals(dialectType)){
    return paginationInterceptor;

The third method is not recommended: rewrite the jdbcutils in the plug-in Java class, which is compatible with Oracle syntax. We can directly return DbType as Oracle or postgresql, as shown below:

public class JdbcUtils {
     * <p>
     * Judge the database type according to the connection address
     * </p>
     * @param jdbcUrl Connection address
     * @return
    public static DbType getDbType(String jdbcUrl) {
        } else if (jdbcUrl.contains(":oracle:")||jdbcUrl.contains(":kingbase8:")) {//Add here
            return DbType.ORACLE;


Keywords: Database

Added by deerly on Thu, 13 Jan 2022 22:00:04 +0200