mysql数据库迁移到瀚高的代码适配

最新推荐文章于 2025-12-20 13:10:13 发布

原创最新推荐文章于 2025-12-20 13:10:13 发布 · 230 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql #瀚高

项目使用的springboot+mybatis+vue框架
为了保证一套代码可以同时适配mysql和瀚高数据库，将两种数据库有差异的查询方法，进行兼容性改造。
在druid 配置多数据源的代码中添加判断当前使用的数据库类型。

@Configuration
public class DruidConfig {
    @Bean
    public DruidStatProperties druidStatProperties() {
        return new DruidStatProperties();
    }

    @Bean
    public String databaseType(DataSource dataSource) {
        try (Connection connection = dataSource.getConnection()) {
            String productName = connection.getMetaData().getDatabaseProductName().toLowerCase();
            System.out.println("**************"+productName);
            if (productName.contains("mysql")) {
                return "mysql";
            } else if (productName.contains("highgo") || productName.contains("瀚高")||productName.contains("postgresql")) {
                return "highgo";
            }
            return "unknown";
        } catch (SQLException e) {
            throw new RuntimeException("检测数据库类型失败", e);
        }
    }
    @Bean
    @ConfigurationProperties("spring.datasource.druid.master")
    public DataSource masterDataSource(DruidProperties druidProperties) {
        DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
        return druidProperties.dataSource(dataSource);
    }
...

}

@Component
public class DatabaseContext {

    private static String databaseType;

    @Autowired
    public void setDatabaseType(String dbType) {
        DatabaseContext.databaseType = dbType;
    }

    public static boolean isMysql() {
        return "mysql".equals(databaseType);
    }

    public static boolean isHighGo() {
        return "highgo".equals(databaseType);
    }
}

瀚高的char类型数据会按照长度自动补齐空格，代码中需要统一处理


// 关键：指定映射 Java 类型（String）
@MappedTypes(String.class)
// 关键：指定映射 JDBC 类型（CHAR），必须显式声明
@MappedJdbcTypes(value = JdbcType.CHAR, includeNullJdbcType = true)
public class CharTypeHandler extends BaseTypeHandler<String> {
    // 实现方法不变（getNullableResult 中 trim，setNonNullParameter 直接设值）
    @Override
    public String getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return trim(rs.getString(columnName));
    }

    @Override
    public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return trim(rs.getString(columnIndex));
    }

    @Override
    public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return trim(cs.getString(columnIndex));
    }

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {
        ps.setString(i, parameter);
    }

    private String trim(String value) {
        return value == null ? null : value.trim();
    }
}

在mybatis-config.xml中添加配置

   <typeHandlers>
       <!-- 显式注册，确保生效 -->
       <typeHandler handler="com.xx.framework.handler.CharTypeHandler"/>
   </typeHandlers>

mapper.xml文件中修改


  <choose>
      <when test="@com.vheng.framework.config.DatabaseContext@isMysql()">
          GROUP_CONCAT(dept_type) AS dept_type,
          GROUP_CONCAT(dept_name) AS dept_name
      </when>
      <when test="@com.vheng.framework.config.DatabaseContext@isHighGo()">
          STRING_AGG(dept_type,',' ) AS dept_type,
          STRING_AGG(dept_name,',') AS dept_name
      </when>
  </choose>
  
   <choose>
       <when test="@com.vheng.framework.config.DatabaseContext@isMysql()">
           fee.total_fees - vote.fees jy_fees,
       </when>
       <when test="@com.vheng.framework.config.DatabaseContext@isHighGo()">
           CAST(fee.total_fees AS NUMERIC) - vote.fees jy_fees,
       </when>
   </choose>
   <choose>
       <when test="@com.vheng.framework.config.DatabaseContext@isMysql()">
           t.one_select+t.more_select+t.judge_question as totalNum,
       </when>
       <when test="@com.vheng.framework.config.DatabaseContext@isHighGo()">
           CAST(t.one_select AS INTEGER) + CAST(t.more_select AS INTEGER) + CAST(t.judge_question AS INTEGER) as totalNum,
       </when>
   </choose>
    <choose>
      <when test="@com.vheng.framework.config.DatabaseContext@isMysql()">
          AND tpcp.`status` >= #{startStatus}
      </when>
      <when test="@com.vheng.framework.config.DatabaseContext@isHighGo()">
          AND CAST(tpcp.`status` AS INTEGER)  >= #{startStatus}
      </when>
  </choose>
  <choose>
      <when test="@com.vheng.framework.config.DatabaseContext@isMysql()">
          or find_in_set( #{deptId} , n.ancestors )
      </when>
      <when test="@com.vheng.framework.config.DatabaseContext@isHighGo()">
          OR CAST( #{deptId} AS VARCHAR ) = ANY (STRING_TO_ARRAY(n.ancestors, ','))
      </when>
  </choose>
  <if test="modelTypes != null ">
      AND model_type in
      <choose>
          <when test="@com.vheng.framework.config.DatabaseContext@isMysql()">
              <foreach collection="modelTypes.split(',')" item="item" separator="," open="(" close=")">
                  #{item}
              </foreach>
          </when>
          <when test="@com.vheng.framework.config.DatabaseContext@isHighGo()">
              <foreach collection="modelTypes.split(',')" item="item" separator="," open="(" close=")">
                  CAST(#{item} AS BIGINT)
              </foreach>
          </when>
      </choose>
  </if>
<if test="params.beginTime != null and params.beginTime != ''">				
	<choose>
		<when test="@com.vheng.framework.config.DatabaseContext@isMysql()">
			AND date_format(create_time,'%y%m%d') &gt;= date_format(#{params.beginTime},'%y%m%d')
		</when>
		<when test="@com.vheng.framework.config.DatabaseContext@isHighGo()">
			AND TO_CHAR(create_time,'YYYYMMDD') &gt;= TO_CHAR(CAST(#{params.beginTime} AS TIMESTAMP),'YYYYMMDD')
		</when>
	</choose>
</if>
<if test="startTime != null ">
    <choose>
        <when test="@com.vheng.framework.config.DatabaseContext@isMysql()">
            AND tpsh.collection_time  &gt;= STR_TO_DATE(#{startTime},  '%Y-%m-%d %H:%i:%s.%f')
        </when>
        <when test="@com.vheng.framework.config.DatabaseContext@isHighGo()">
            AND tpsh.collection_time  &gt;= TO_TIMESTAMP(#{startTime},  'YYYY-MM-DD HH24:MI:SS.US')
        </when>
    </choose>
</if>

 <if test="createStartTime != null and createEndTime != null">
     <choose>
         <when test="@com.vheng.framework.config.DatabaseContext@isMysql()">
             AND tpp.create_time &gt;= STR_TO_DATE(#{createStartTime},  '%Y-%m-%d %H:%i:%s.%f')
             AND tpp.create_time &lt;= DATE_ADD(STR_TO_DATE(#{createEndTime},  '%Y-%m-%d %H:%i:%s.%f'), INTERVAL 1 DAY) - INTERVAL 1 SECOND
         </when>
         <when test="@com.vheng.framework.config.DatabaseContext@isHighGo()">
             AND tpp.create_time &gt;= TO_TIMESTAMP(#{createStartTime},  'YYYY-MM-DD HH24:MI:SS.US')
             AND tpp.create_time &lt;= TO_TIMESTAMP(#{createEndTime},  'YYYY-MM-DD HH24:MI:SS.US') + INTERVAL '1 DAY' - INTERVAL '1 SECOND'
         </when>
     </choose>
 </if>
  <choose>
     <when test="@com.vheng.framework.config.DatabaseContext@isMysql()">
          AND (YEAR(create_time) = YEAR(CURDATE())
          or YEAR(update_time) = YEAR(CURDATE()))
      </when>
      <when test="@com.vheng.framework.config.DatabaseContext@isHighGo()">
          AND (
          EXTRACT(YEAR FROM create_time) = EXTRACT(YEAR FROM CURRENT_DATE)
          OR EXTRACT(YEAR FROM update_time) = EXTRACT(YEAR FROM CURRENT_DATE)
          )
      </when>
  </choose>
  <choose>
      <when test="@com.vheng.framework.config.DatabaseContext@isMysql()">
          and YEAR(tmr.meeting_start_date) = #{year}
      </when>
      <when test="@com.vheng.framework.config.DatabaseContext@isHighGo()">
          AND EXTRACT(YEAR FROM tmr.meeting_start_date) = EXTRACT(YEAR FROM TO_DATE(#{year}, 'yyyy-MM-dd'))
      </when>
  </choose>