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
SELECT * FROM USER WHERE ADDR = '';
--Suggested writing, use length Functions, support oracle/mysql/postgresql/kingbase And other databases
SELECT * FROM USER WHERE length(ADDR) = 0;

Introduce dependency

In POM XML is configured as follows:

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

<dependency>
	<groupId>com.kingbase8</groupId>
	<artifactId>kingbase8</artifactId>
	<version>8.2.0</version>
	<!-- scope>system</scope>
	<systemPath>${project.basedir}/lib/kingbase8-8.2.0.jar</systemPath-->
</dependency>

Druid database connection pool

Configuration example of Druid gold warehouse V82:

jdbc.driverClassName=com.kingbase8.Driver
jdbc.url=jdbc:kingbase8://127.0.0.1:54321/test
jdbc.username=root
jdbc.password=1qaz!QAZ
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:

jdbc.url=jdbc:kingbase8://127.0.0.1:54321/test?currentSchema=rdp_server

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.io.ByteArrayInputStream;
import java.sql.*;

/**
 * @author:WC
 * @date: 2021/6/4
 * @time: 10:09
 */
public class BlobToStringTypeHandler extends BaseTypeHandler<String> {
    @Override
    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);
    }

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

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

    @Override
    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:

mybatis-plus:
    global-config:
        db-config:
            #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
 */
@Bean
public PaginationInterceptor paginationInterceptor() {
    dbType = dialectType;
    PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
    if("KingbaseES".equals(dialectType)){
        paginationInterceptor.setDialectType(DbType.POSTGRE_SQL.getDb());
    }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;
        } 
    	......
    }

}