上次适配了一下达梦,然后项目中还需要适配更多数据库,所以根据上次的内容再做修改,上次的链接如下:
这次的情况是:达梦默认是大写,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相关参数,最后执行启动就可以了