apollo适配达梦、openGauss、人大金仓、mysql

最新推荐文章于 2024-02-07 17:57:30 发布

JAdroid

最新推荐文章于 2024-02-07 17:57:30 发布

阅读量788

点赞数 8

文章标签： mysql 数据库 spring java

本文链接：https://blog.csdn.net/JAdroid/article/details/134716517

版权

上次适配了一下达梦，然后项目中还需要适配更多数据库，所以根据上次的内容再做修改，上次的链接如下：

apollo切换mysql到达梦数据库-CSDN博客

这次的情况是：达梦默认是大写，openGauss、人大金仓这两个是和mysql表名字段大小写保持一致。

1.POM修改

主要是对于这两个pom文件修改，增加依赖：

       <!-- 达梦数据库 -->
        <dependency>
            <groupId>com.dameng</groupId>
            <artifactId>DmJdbcDriver18</artifactId>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.dameng/DmDialect-for-hibernate5.6 -->
        <dependency>
            <groupId>com.dameng</groupId>
            <artifactId>DmDialect-for-hibernate5.6</artifactId>
        </dependency>
        <!-- 高斯 https://mvnrepository.com/artifact/org.opengauss/opengauss-jdbc -->
        <dependency>
            <groupId>org.opengauss</groupId>
            <artifactId>opengauss-jdbc</artifactId>
        </dependency>
        <!-- 最低可支持JDK1.8 -->
        <dependency>
            <groupId>cn.com.kingbase</groupId>
            <artifactId>kingbase8</artifactId>
        </dependency>

2.修改对应application.yaml文件

修改apollo-common下的application.yaml文件，如下配置：

spring:
  jpa:
    properties:
      hibernate:
        globally_quoted_identifiers: 'true'
        dialect: ${datasource_hibernate_dialect}
        session_factory:
          statement_inspector: com.ctrip.framework.apollo.common.jpa.JpaSqlInterceptor
    hibernate:
      globally_quoted_identifiers: 'true'
      # Naming strategy
      naming:
#        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
        physical-strategy: org.hibernate.boot.model.naming.CamelCaseToUnderscoresNamingStrategy
    show-sql: false
  info:
    # Project information
    git:
      location: classpath:apollo-git.properties
  mvc:
    converters:
      preferred-json-mapper: gson
  datasource:
    driver-class-name: ${spring_datasource_driver:com.mysql.jdbc.Driver}
  # DataSource
#  datasource:
#    hikari:
#      connectionInitSql: set names utf8mb4

# Tomcat configuration
server:
  tomcat:
    accept-count: '5000'
  # Increase tomcat header size for longer GET query parameter, should be n * 1024
  max-http-header-size: '10240'

# Spring Boot 2.x
management:
  endpoints:
    web:
      exposure:
        include: info,health,metrics,prometheus
      base-path: /
logging:
  logback:
    rollingpolicy:
      max-history: '10'
      max-file-size: 50MB

3、修改对应java代码

为了做更多兼容，对于表和字段加了`和没有加的需要做些取舍，下面这些需要加上`这个符号

// com.ctrip.framework.apollo.biz.entity包下：
public class Item extends BaseEntity {

  @Column(name = "`key`", nullable = false)
  private String key;

}


@Table(name = "`Release`")
@SQLDelete(sql = "Update `Release` set IsDeleted = 1, DeletedAt = ROUND(UNIX_TIMESTAMP(NOW(4))*1000) where Id = ?")
@Where(clause = "isDeleted = 0")
public class Release extends BaseEntity {
  @Column(name = "`Comment`", nullable = false)
  private String comment;

}

public class ServerConfig extends BaseEntity {
  @Column(name = "`Key`", nullable = false)
  private String key;

}

// com.ctrip.framework.apollo.portal.entity.po包下
public class ServerConfig extends BaseEntity {
  @NotBlank(message = "ServerConfig.Key cannot be blank")
  @Column(name = "`Key`", nullable = false)
  private String key;
}

下面这些需要去掉`符号

// com.ctrip.framework.apollo.biz.repository包下：

public interface InstanceConfigRepository extends PagingAndSortingRepository<InstanceConfig, Long> {
@Query(
      value = "select b.Id from InstanceConfig a inner join Instance b on b.Id =" +
          " a.InstanceId where a.ConfigAppId = :configAppId and a.ConfigClusterName = " +
          ":clusterName and a.ConfigNamespaceName = :namespaceName and a.DataChange_LastTime " +
          "> :validDate and b.AppId = :instanceAppId",
      countQuery = "select count(1) from InstanceConfig a inner join Instance b on b.id =" +
          " a.InstanceId where a.ConfigAppId = :configAppId and a.ConfigClusterName = " +
          ":clusterName and a.ConfigNamespaceName = :namespaceName and a.DataChange_LastTime " +
          "> :validDate and b.AppId = :instanceAppId",
      nativeQuery = true)
  Page<Object> findInstanceIdsByNamespaceAndInstanceAppId(
      @Param("instanceAppId") String instanceAppId, @Param("configAppId") String configAppId,
      @Param("clusterName") String clusterName, @Param("namespaceName") String namespaceName,
      @Param("validDate") Date validDate, Pageable pageable);
}



// com.ctrip.framework.apollo.portal.spi.configuration 包下

@Configuration
public class AuthConfiguration {
@Bean
    public static JdbcUserDetailsManager jdbcUserDetailsManager(PasswordEncoder passwordEncoder,
        AuthenticationManagerBuilder auth, DataSource datasource) throws Exception {
      JdbcUserDetailsManager jdbcUserDetailsManager = auth.jdbcAuthentication()
          .passwordEncoder(passwordEncoder).dataSource(datasource)
          .usersByUsernameQuery("select Username,Password,Enabled from Users where Username = ?")
          .authoritiesByUsernameQuery(
              "select Username,Authority from Authorities where Username = ?")
          .getUserDetailsService();

      jdbcUserDetailsManager.setUserExistsSql("select Username from Users where Username = ?");
      jdbcUserDetailsManager
          .setCreateUserSql("insert into Users (Username, Password, Enabled) values (?,?,?)");
      jdbcUserDetailsManager
          .setUpdateUserSql("update Users set Password = ?, Enabled = ? where id = (select u.id from (select id from Users where Username = ?) as u)");
      jdbcUserDetailsManager.setDeleteUserSql("delete from Users where id = (select u.id from (select id from Users where Username = ?) as u)");
      jdbcUserDetailsManager
          .setCreateAuthoritySql("insert into Authorities (Username, Authority) values (?,?)");
      jdbcUserDetailsManager
          .setDeleteUserAuthoritiesSql("delete from Authorities where id in (select a.id from (select id from Authorities where Username = ?) as a)");
      jdbcUserDetailsManager
          .setChangePasswordSql("update Users set Password = ? where id = (select u.id from (select id from Users where Username = ?) as u)");

      return jdbcUserDetailsManager;
    }
}

4、重写CamelCaseToUnderscoresNamingStrategy.java类

这个类是hibernate-core包下的，主要是针对schema、table、column等名称的转换，在之后的启动微服务中需要配置上对应的参数，要在这个

package org.hibernate.boot.model.naming;包下，在apollo-common这个模块添加，具体如下：

package org.hibernate.boot.model.naming;

import java.util.Arrays;
import java.util.List;
import java.util.Locale;

import org.hibernate.engine.jdbc.env.spi.JdbcEnvironment;

/**
 * Originally copied from Spring Boot as this strategy is popular there
 * (original name is SpringPhysicalNamingStrategy).
 *
 * @author Phillip Webb
 * @author Madhura Bhave
 */
public class CamelCaseToUnderscoresNamingStrategy implements PhysicalNamingStrategy {

//	private static final List<String> keywords = Arrays.asList("comment", "cluster", "reference", "percent");
	@Override
	public Identifier toPhysicalCatalogName(Identifier name, JdbcEnvironment jdbcEnvironment) {
		return apply( name, jdbcEnvironment );
	}

	@Override
	public Identifier toPhysicalSchemaName(Identifier name, JdbcEnvironment jdbcEnvironment) {
		return apply( name, jdbcEnvironment );
	}

	@Override
	public Identifier toPhysicalTableName(Identifier name, JdbcEnvironment jdbcEnvironment) {
		return apply( name, jdbcEnvironment );
	}

	@Override
	public Identifier toPhysicalSequenceName(Identifier name, JdbcEnvironment jdbcEnvironment) {
		return apply( name, jdbcEnvironment );
	}

	@Override
	public Identifier toPhysicalColumnName(Identifier name, JdbcEnvironment jdbcEnvironment) {
		return apply( name, jdbcEnvironment );
	}

	private Identifier apply(final Identifier name, final JdbcEnvironment jdbcEnvironment) {
		if ( name == null ) {
			return null;
		}
		/*StringBuilder builder = new StringBuilder( name.getText().replace( '.', '_' ) );
		for ( int i = 1; i < builder.length() - 1; i++ ) {
			if ( isUnderscoreRequired( builder.charAt( i - 1 ), builder.charAt( i ), builder.charAt( i + 1 ) ) ) {
				builder.insert( i++, '_' );
			}
		}
		return getIdentifier( builder.toString(), name.isQuoted(), jdbcEnvironment );*/
		return getIdentifier( name.getText().replace( '.', '_' ), name.isQuoted(), jdbcEnvironment );
	}

	/**
	 * Get an identifier for the specified details. By default this method will return an identifier
	 * with the name adapted based on the result of {@link #isCaseInsensitive(JdbcEnvironment)}
	 *
	 * @param name the name of the identifier
	 * @param quoted if the identifier is quoted
	 * @param jdbcEnvironment the JDBC environment
	 *
	 * @return an identifier instance
	 */
	protected Identifier getIdentifier(String name, final boolean quoted, final JdbcEnvironment jdbcEnvironment) {
		if ( isCaseInsensitive( jdbcEnvironment ) ) {
			name = name.toLowerCase( Locale.ROOT );
		}
		// 判断是否达梦，达梦大写
		if (jdbcEnvironment.getDialect().getClass().getName().equalsIgnoreCase("org.hibernate.dialect.DmDialect")) {
			return new Identifier(name.replaceAll("`", "").toUpperCase(), true);
		} else if (jdbcEnvironment.getDialect().getClass().getName().equalsIgnoreCase("org.hibernate.dialect.PostgreSQL92Dialect")) {
			return new Identifier(name.toUpperCase(), false);
		}
		return new Identifier( name, quoted );
	}

	/**
	 * Specify whether the database is case sensitive.
	 *
	 * @param jdbcEnvironment the JDBC environment which can be used to determine case
	 *
	 * @return true if the database is case insensitive sensitivity
	 */
	protected boolean isCaseInsensitive(JdbcEnvironment jdbcEnvironment) {
		return true;
	}

	private boolean isUnderscoreRequired(final char before, final char current, final char after) {
		return Character.isLowerCase( before ) && Character.isUpperCase( current ) && Character.isLowerCase( after );
	}

}

5、增加一个获取spring容器applicationContext的工具类

在apollo-common模块中

package com.ctrip.framework.apollo.common.utils;

import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Component;

/**
 * spring初始化工具类
 */
@Component

public class SpringContextHolder implements ApplicationContextAware {

	public static ApplicationContext applicationContext;

	@Override
	public void setApplicationContext(ApplicationContext context) throws BeansException {
		applicationContext = context;
	}

	public static ApplicationContext getApplicationContext() {
		return applicationContext;
	}

	/**
	 * 根据bean名称获取实例bean对象
	 * @param bean
	 * @param <T>
	 * @return
	 */
	public static <T> T getBean(String bean) {
		return (T) applicationContext.getBean(bean);
	}

	/**
	 * 根据类class获取bean实例对象
	 * @param clazz
	 * @param <T>
	 * @return
	 */
	public static <T> T getBean(Class<T> clazz) {
		return applicationContext.getBean(clazz);
	}

	/**
	 * 获取Environment
	 */
	public static Environment getEnv() {
		return applicationContext.getEnvironment();
	}

	/**
	 * 发布事件
	 * @param event
	 */
	public static void publishEvent(Object event) {
		applicationContext.publishEvent(event);
	}

}

6、增加一个jpa的拦截器，用来拦截并修改SQL

在apollo-common模块中，这个类目前是实现拦截和修改功能，就是自己写的方法逻辑比较简陋，后续有时间优化一下

package com.ctrip.framework.apollo.common.jpa;

import com.ctrip.framework.apollo.common.utils.SpringContextHolder;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.resource.jdbc.spi.StatementInspector;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.core.env.Environment;

/**
 * @package: com.ctrip.framework.apollo.common.jpa
 * @author: xxx
 * @description: TODO
 * @date: 2023/11/27 18:25
 */

public class JpaSqlInterceptor implements StatementInspector {
    private static final Logger logger = LoggerFactory.getLogger(JpaSqlInterceptor.class);


    @Override
    public String inspect(String sql) {
        ApplicationContext applicationContext = SpringContextHolder.getApplicationContext();
//        if (StringUtils.isNotBlank(sql) && sql.contains(replaceStr) && applicationContext != null) {
        if (StringUtils.isNotBlank(sql) && applicationContext != null) {
            Environment environment = applicationContext.getEnvironment();
            // 判断是否为openGauss数据库
            String driver = environment.getProperty("spring.datasource.driver-class-name");
            if (StringUtils.isBlank(driver)) {
                return sql;
            }
            // openGauss kingbase8替换函数
            String replaceStr = "UNIX_TIMESTAMP(NOW(4))";
            if ( sql.contains(replaceStr) && (driver.equalsIgnoreCase("org.opengauss.Driver")|| driver.equalsIgnoreCase("com.kingbase8.Driver"))) {
                logger.info("JpaSqlInterceptor.inspect 替换SQL 源SQL="+sql);
                sql = StringUtils.replace(sql, replaceStr, "extract (epoch from now())");
                logger.info("JpaSqlInterceptor.inspect 替换SQL 目标SQL="+sql);
            }
            // 人大金仓替换函数
            int count = StringUtils.countMatches(sql, "||");
            if (driver.equalsIgnoreCase("com.kingbase8.Driver") && count > 0) {
                logger.info("JpaSqlInterceptor.inspect 人大金仓替换函数替换SQL 源SQL="+sql);
                for (int i = 0; i < count; i++) {
                    sql = getConcatSql(sql);
                }
                logger.info("JpaSqlInterceptor.inspect 人大金仓替换函数替换SQL 目标SQL="+sql);
            }

        }
        return sql;
    }

    public static void main(String[] args) {
        String sql = "select role0_.ID as col_0_0_ from ROLE role0_ where ( role0_.isDeleted = 0) and (role0_.ROLENAME=('Master+'||'eureka') or role0_.ROLENAME like ('ModifyNamespace+'||'eureka'||'+%') or role0_.ROLENAME like ('ReleaseNamespace+'||'eureka'||'+%') or role0_.ROLENAME=('ManageAppMaster+'||'eureka'))";

        int count = StringUtils.countMatches(sql, "||");
        for (int i = 0; i < count; i++) {
            sql = getConcatSql(sql);
        }
        System.out.println(sql);
    }

    private static String getConcatSql(String sql) {
        int flagIdx = sql.indexOf("||");
        if (flagIdx <= 0) {
            return  sql;
        }
        StringBuilder dnf = new StringBuilder();
        for (int i = flagIdx; i > 0 ; i--) {
            char charAt = sql.charAt(i);
            dnf.insert(0, charAt);
            if ("(".equals(String.valueOf(charAt))) {
                break;
            }
        }
        for (int i = flagIdx+1; i < sql.length(); i++) {
            char charAt = sql.charAt(i);
            dnf.append(charAt);
            if (")".equals(String.valueOf(charAt))) {
                break;
            }
        }
        String rep = dnf.toString().replace("||", ",");
        rep = "concat" + rep;
        return StringUtils.replace(sql, dnf.toString(), rep);
    }
}

7、启动参数

在apollo-adminservice apollo-configservice这两个服务启动时添加的VM参数如下：

以下针对于mysql库：


-Ddatasource_hibernate_dialect=org.hibernate.dialect.MySQL57Dialect -Dapollo_profile=github -Dspring.datasource.url=jdbc:mysql://127.0.0.1:43306/APOLLOCONFIGDB_2_1_0?characterEncoding=utf8 -Dspring.datasource.username=root -Dspring.datasource.password=xxx -Dspring.security.eureka.host=127.0.0.1 -Dspring.security.eureka.port=7761 


以下针对于DM库：


-Ddatasource_hibernate_dialect=org.hibernate.dialect.DmDialect -Dspring_datasource_driver=dm.jdbc.driver.DmDriver -Dapollo_profile=github -Dspring.datasource.url=jdbc:dm://127.0.0.1:30236?schema=APOLLOCONFIGDB_2_1_0&characterEncoding=utf8&compatibleMode=mysql&clobAsString=true -Dspring.datasource.username=SYSDBA -Dspring.datasource.password=SYSDBA001 -Dspring.security.eureka.host=127.0.0.1 -Dspring.security.eureka.port=7761  


以下针对于openGauss库：

-Ddatasource_hibernate_dialect=org.hibernate.dialect.PostgreSQL92Dialect -Dspring_datasource_driver=org.opengauss.Driver -Dapollo_profile=github -Dspring.datasource.url=jdbc:opengauss://127.0.0.1:5432/my_db?currentSchema=APOLLOCONFIGDB_2_1_0&characterEncoding=utf8 -Dspring.datasource.username=ga_mason -Dspring.datasource.password=OpenGauss@123 -Dspring.security.eureka.host=127.0.0.1 -Dspring.security.eureka.port=7761  


以下针对于人大金仓库：


-Ddatasource_hibernate_dialect=org.hibernate.dialect.PostgreSQL92Dialect -Dspring_datasource_driver=com.kingbase8.Driver -Dapollo_profile=github -Dspring.datasource.url=jdbc:kingbase8://127.0.0.1:54321/kingbase?currentSchema=APOLLOCONFIGDB_2_1_0&characterEncoding=utf8 -Dspring.datasource.username=system -Dspring.datasource.password=xxx -Dspring.security.eureka.host=127.0.0.1 -Dspring.security.eureka.port=7761

在apollo-portal这个服务启动时添加的VM参数如下：

以下针对于mysql库：

-Ddatasource_hibernate_dialect=org.hibernate.dialect.MySQL57Dialect -Dapollo_profile=github,auth -Ddev_meta=http://localhost:8080/ -Dapollo.meta=http://localhost:8080/ -Dserver.port=8070 -Dspring.datasource.url=jdbc:mysql://127.0.0.185:43306/APOLLOPORTALDB_2_1_0?characterEncoding=utf8 -Dspring.datasource.username=root -Dspring.datasource.password=xxx

以下针对于DM库：

-Ddatasource_hibernate_dialect=org.hibernate.dialect.DmDialect -Dspring_datasource_driver=dm.jdbc.driver.DmDriver -Dapollo_profile=github,auth -Ddev_meta=http://localhost:8080/ -Dapollo.meta=http://localhost:8080/ -Dserver.port=8070 -Dspring.datasource.url=jdbc:dm://127.0.0.185:30236?schema=APOLLOPORTALDB_2_1_0&characterEncoding=utf8&characterEncoding=utf8&compatibleMode=mysql&clobAsString=true -Dspring.datasource.username=SYSDBA -Dspring.datasource.password=SYSDBA001


以下针对于openGauss库：


-Ddatasource_hibernate_dialect=org.hibernate.dialect.PostgreSQL92Dialect -Dspring_datasource_driver=org.opengauss.Driver -Dapollo_profile=github,auth -Ddev_meta=http://localhost:8080/ -Dapollo.meta=http://localhost:8080/ -Dserver.port=8070 -Dspring.datasource.url=jdbc:opengauss://127.0.0.185:5432/my_db?currentSchema=APOLLOPORTALDB_2_1_0&characterEncoding=utf8 -Dspring.datasource.username=ga_mason -Dspring.datasource.password=OpenGauss@123


以下针对于人大金仓库：

-Ddatasource_hibernate_dialect=org.hibernate.dialect.PostgreSQL92Dialect -Dspring_datasource_driver=com.kingbase8.Driver -Dapollo_profile=github,auth -Ddev_meta=http://localhost:8080/ -Dapollo.meta=http://localhost:8080/ -Dserver.port=8070 -Dspring.datasource.url=jdbc:kingbase8://127.0.0.185:54321/kingbase?currentSchema=APOLLOPORTALDB_2_1_0&characterEncoding=utf8 -Dspring.datasource.username=system -Dspring.datasource.password=xxx

如果是用的apollo自带的eureka，则在参数中不需要配置eureka相关参数，最后执行启动就可以了