MyBatis 方言支持 - Mysql to 华为高斯数据库(gaussdb)_gaussdb mybatis

1.spring-config-mybatis.xml文件加入方言支持（只列出必要字段）

<!-- 配置方言 -->
<bean name="databaseIdProvider" class="org.apache.ibatis.mapping.VendorDatabaseIdProvider" >
    <property name="properties">
        <map>
            <entry key="MySQL" value="mysql"></entry>
            <entry key="ORACLE" value="oracle"></entry>
            <entry key="Zenith" value="gaussdb"></entry><!-- 高斯数据库 -->
        </map>
    </property>
</bean>
 
 
<bean id="sessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="databaseIdProvider" ref="databaseIdProvider" /> <!-- 配置方言 -->
    <!-- 其他配置 请参见项目代码 -->
</bean>

配置说明：key:数据库类型-mybatis可以根据数据源类型自动识别

                   value:mapper xml文件中用到，后续说明

自动识别数据源类型备注：

                  MyBatis 接口：org.apache.ibatis.mapping.VendorDatabaseIdProvider#getDatabaseProductName

                  实现该接口 java.sql.DatabaseMetaData

                  Mysql实现类：com.mysql.jdbc.DatabaseMetaData#getDatabaseProductName : return "MySQL"

2.Mapper说明

  2.1 因为mapper XML 文件不支持继承，（一个接口中的方法在XML文件中必须有实现-否则启动报错），

       所以采用 Mapper.java 接口类继承的方式

3.Mapper支持 案例说明

先删除 原有数据库驱动（当前都是MySQL）
<dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
 <version>5.1.38</version>
</dependency>

<dependency>
 <groupId>com.huawei.gauss</groupId>
 <artifactId>com.huawei.gauss.jdbc.ZenithDriver</artifactId>
 <version>GaussDB_100_1.0.1.SPC2.B003</version>
</dependency>

<property name="driverClass" value="com.huawei.gauss.jdbc.ZenithDriver"/>
<property name="jdbcUrl" value="jdbc:zenith:@127.0.0.1:3358?useSSL=false"/>
<property name="user" value="jr"/>
<property name="password" value="data****_123"/>

sysdate()

sysdate

public interface TraceDataStoreExtMapper {
 
 
    List<TraceDataStoreWithBLOBs> getList(TraceDataStoreVo record) throws Exception;
 
 
    int insertSelectiveGetKey(TraceDataStoreWithBLOBs record) throws Exception;
 
 
    Integer insertBatch(List<TraceDataStoreWithBLOBs> datas)throws Exception;
 
}

<mapper namespace="com.TraceDataStoreExtMapper">
 
  <resultMap id="BaseResultMap" type="com.TraceDataStoreWithBLOBs" extends="com.TraceDataStoreMapper.ResultMapWithBLOBs">
  </resultMap>
 
  <!--基础查询条件-->
  <sql id="selectList_Count_Where">
    <where>
      AND del_flag=0
      <if test="traceAccount != null">
        AND trace_account = #{traceAccount,jdbcType=VARCHAR}
      </if>
    </where>
  </sql>
 
  <select id="getList" parameterType="com.TraceDataStoreVo" resultMap="BaseResultMap">
    select
    <include refid="com.TraceDataStoreMapper.Base_Column_List" />
    ,
    <include refid="com.TraceDataStoreMapper.Blob_Column_List" />
 
    from zt_trace_data_store
    <include refid="selectList_Count_Where"></include>
    order by create_time desc
  </select>
 
  <insert id="insertSelectiveGetKey" parameterType="com.TraceDataStoreWithBLOBs"  useGeneratedKeys="true" keyProperty="id">
    insert into zt_trace_data_store
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="id != null">
        id,
      </if>
      <if test="traceAccount != null">
        trace_account,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="id != null">
        #{id,jdbcType=BIGINT},
      </if>
      <if test="traceAccount != null">
        #{traceAccount,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>
 
  <insert id="insertBatch"   parameterType="java.util.List"  useGeneratedKeys="true" keyProperty="id">
    INSERT INTO zt_trace_data_store (
      id, trace_account
    )
    VALUES
    <foreach collection="list" item="item" index="index" separator="," >
      (
        #{item.id,jdbcType=BIGINT}, #{item.traceAccount,jdbcType=VARCHAR}
      )
    </foreach>
  </insert>
</mapper>

public interface TraceDataStoreExtDialectMapper extends TraceDataStoreExtMapper {
}

<mapper  namespace="com.TraceDataStoreExtDialectMapper" >
  <!-- 设置方言 MySQL -->
  <insert id="insertSelectiveGetKey" databaseId="mysql" parameterType="com.TraceDataStoreWithBLOBs"  useGeneratedKeys="true" keyProperty="id">
    insert into zt_trace_data_store
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="id != null">
        id,
      </if>
      <if test="traceAccount != null">
        trace_account,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="id != null">
        #{id,jdbcType=BIGINT},
      </if>
      <if test="traceAccount != null">
        #{traceAccount,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>
</mapper>

99.java原生代码调用数据库简单案例（测试）

package com.jd.bt.middle.service;
 
import java.sql.*;
 
public class GaussDBTest {
 
    private static Connection connection = null;
    private static PreparedStatement preState = null;
    private static ResultSet resultSet = null;
 
    private static final String username = "jr";
    private static final String passwd = "data****_123";
 
    private static Connection getConnection() {
        String driver = "com.huawei.gauss.jdbc.ZenithDriver"; //数据库连接描述符。
        String sourceURL = "jdbc:zenith:@127.0.0.1:3358?useSSL=true";
        Connection conn = null;
        try { //加载数据库驱动。
            Class.forName(driver).newInstance();
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        try {
        //创建数据库连接。
        conn = DriverManager.getConnection(sourceURL,username,passwd);
        System.out.println("Connection 成功!\n");
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return conn;
    };
 
    private static void executeSqlQuery(String sql,Object ... args )throws Exception{
        connection = getConnection();
        preState = connection.prepareStatement(sql);
        if(null!=args){
            for(int i=1;i<=args.length;i++){
                Object value = args[i-1];
                if(value instanceof String){
                    preState.setString(i, String.valueOf(value));//1是指sql语句中第一个？,  2是指第一个？的values值
                }else if(value instanceof Integer){
                    preState.setInt(i,Integer.valueOf(value.toString()));
                }else if(value instanceof  Long){
                    preState.setLong(i,Long.valueOf(value.toString()));
                }else{
                    throw new RuntimeException("参数格式异常");
                }
            }
        }
        boolean execute = preState.execute();
        System.out.println("执行结果："+execute);
        resultSet = preState.getResultSet();
    }
 
    private static void printResult(String ... fieldName)throws Exception{
        try{
            System.out.println("\n================打印结果如下================");
            while (resultSet.next()) {
                for(String item : fieldName){
                    String val = resultSet.getString(item);
                    System.out.print(item+":"+ val + "   ");
                }
                System.out.println();  //打印输出结果集
            }
            System.out.println("================打印结果如上================\n");
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            try {
                if (resultSet!=null && !resultSet.isClosed()){
                    resultSet.close();
                }
                if (connection!=null && !connection.isClosed()) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
 
    public static void main(String[] args)throws Exception {
 
        try{
//            executeSql("select * from ADM_TABLESPACES where tablespace_name = ?","SYDB_RESOURCE" );
//            printResult("tablespace_name","datafile_count");
 
//            executeSqlQuery("select * from ZT_TRACE_DATA_STORE where id = ?",1 );
//            printResult("trace_account","sku_id");
 
            Long id = System.currentTimeMillis();
            executeSqlQuery("insert into zt_trace_data_store (id,trace_account,business_type,tache_type,industry_code,sku_id) values (?,?,?,?,?,?)",id,"ofc",3,4,"jdbiz",69000001);
//            printResult("trace_account","sku_id");
 
            executeSqlQuery("select * from ZT_TRACE_DATA_STORE where trace_account = ?", "ofc" );
            printResult("id","trace_account","industry_code");
        }catch(Exception e){
            e.printStackTrace();
        }
    }
}