本章节介绍LightDB多发比对的整体架构及快速入门。
10.8.1. 多发+比对代理模式的架构（1对1伪多发）
10.8.1.1. 架构图
10.8.1.2. 场景说明
该场景主要用于多个微服务同时集成多发代理程序(sql-convert-runtime)后，每个微服务执行sql(DQL/DML)操作时，代理程序会拦截SQL执行， 如果是DML，则代理程序会获取DML执行前预计影响的数据行，和DML执行后实际影响的数据行，并发送到消息队列中；如果是DQL，则代理程序会获取其查询结果集，并发送到消息队列中。 之后，比对服务会以指定的mysql/oracle为基准库，与其他微服务传送到消息队列中的不同信创库的查询结果集进行一一比对，并记录比对差异，以比对报告的形式展示给用户查看。
注意
伪多发场景下，每个微服务配置的JDBC连接中需要包含multiplexTargetDialects属性，值为所有微服务的目标数据库方言类型，多个之间用英文逗号连接。
如伪多发场景为oracle、dm、gaussdb_oracle环境，以dm为例，配置如下：
hs.datasource.default.driver-class-name=com.hundsun.lightdb.unisql.proxy.Driver
hs.datasource.default.url=jdbc:unisql:dm://ip:port/dbname?sourceDialect=oracle&targetDialect=dm&multiplexTargetDialects=oracle,dm,gaussdb_oracle
hs.datasource.default.username=username
hs.datasource.default.password=password
10.8.2. 微服务集成多发代理(war形式，经纪3.0多发比对配置样例)
下面以经纪伪多发 Oracle–>DM的多发比对为例进行展示。
待补充…
10.8.3. 基于JRESCloud3.X开发框架 + mybatis（IDEA开发模式集成为例）
10.8.3.1. 修改统一sql配置文件：${当前工作目录}/config/jrescloud.properties
multi.run.what=2 // 0:不执行自动化和多发 1:执行自动化测试 2:执行多发(关联多发的源库(oracle或者mysql)的jdbcUrl中查询参数mode=MULTIPLEX) 3:执行自动化+多发(关联多发的源库(oracle或者mysql)的jdbcUrl中查询参数mode=MULTIPLEX)
multi.sendCompareService=1 // 是否发送到比对服务(总开关)，默认0不发送到比对服务，取值范围[0,1]
multi.pulsarServiceUrl=pulsar://${IP}:${PORT} // Pulsar服务端地址，格式为:pulsar://<ip>:<port> 样例数据：pulsar://10.20.47.203:6650
multi.topicName=persistent://public/default/${TOPIC} // Pulsar的持久化主题，主题格式：persistent://<tenant>/<namespace>/<topic>，样例数据为：persistent://public/default/recordMonitorJingji
multi.subscriptionName=${SUBSCRIPTION} // 在Pulsar中，订阅（Subscription）是用于消费主题消息的标识符。订阅名称是用来标识不同消费者或消费组在订阅相同主题时的唯一标识。合法字符串即可，比如jingjiTest
10.8.3.2. 模拟数据
CREATE TABLE foo (
city_id NUMBER PRIMARY KEY,
nation VARCHAR2(50),
city_name VARCHAR2(100),
city_sort NUMBER
);
INSERT INTO Foo (city_id, nation, city_name, city_sort) VALUES (1, 'China', 'Beijing', 1);
INSERT INTO Foo (city_id, nation, city_name, city_sort) VALUES (2, 'USA', 'New York', 2);
INSERT INTO Foo (city_id, nation, city_name, city_sort) VALUES (3, 'Japan', 'Tokyo', 3);
INSERT INTO Foo (city_id, nation, city_name, city_sort) VALUES (4, 'China', 'ShangHai', 4);
INSERT INTO Foo (city_id, nation, city_name, city_sort) VALUES (5, 'Canada', 'Toronto', 5);
10.8.3.3. pom依赖
<dependencyManagement>
<dependencies>
<dependency>
<groupId>com.hundsun.jrescloud</groupId>
<artifactId>jrescloud-dependencies</artifactId>
<version>3.0.21.2</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<dependencies>
<dependency>
<groupId>com.hundsun.jrescloud.middleware</groupId>
<artifactId>jrescloud-starter-mybatis</artifactId>
</dependency>
<dependency>
<groupId>com.hundsun.jrescloud</groupId>
<artifactId>jrescloud-starter</artifactId>
<exclusions>
<exclusion>
<artifactId>log4j-slf4j-impl</artifactId>
<groupId>org.apache.logging.log4j</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>com.hundsun.jrescloud</groupId>
<artifactId>jrescloud-starter-rpc-def</artifactId>
</dependency>
<dependency>
<groupId>com.oracle.ojdbc</groupId>
<artifactId>ojdbc8</artifactId>
<version>19.3.0.0</version>
</dependency>
<dependency>
<groupId>cn.easyproject</groupId>
<artifactId>orai18n</artifactId>
<version>12.1.0.2.0</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>io.github.hslightdb</groupId>
<artifactId>ltjdbc</artifactId>
<version>42.2.24-22.4.0.3</version>
</dependency>
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>18.0</version>
</dependency>
<!--sql runtime-->
<dependency>
<groupId>com.hundsun.lightdb</groupId>
<artifactId>sql-convert-runtime</artifactId>
<version>24.2.3-SNAPSHOT</version>
</dependency>
<!--unisql-compare-client-->
<dependency>
<groupId>com.hundsun.lightdb</groupId>
<artifactId>unisql-compare-client</artifactId>
<version>24.2.3-SNAPSHOT</version>
<scope>system</scope>
<systemPath>${project.basedir}/lib/unisql-compare-client-fat-24.2.3.jar</systemPath>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
</dependency>
<dependency>
<groupId>com.github.jnr</groupId>
<artifactId>jnr-ffi</artifactId>
<version>2.2.14</version>
</dependency>
<dependency>
<groupId>com.alibaba.fastjson2</groupId>
<artifactId>fastjson2</artifactId>
<version>2.0.36</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-collections4</artifactId>
<version>4.4</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!--gaussdb_oracle驱动-->
<dependency>
<groupId>com.huawei.opengauss.jdbc</groupId>
<artifactId>gaussdbDriver</artifactId>
<version>3.0.0-htrunk21</version>
</dependency>
<!--tdsql-pg-->
<dependency>
<groupId>com.tencentcloud.tdsql</groupId>
<artifactId>tdsql-pg-connector-java8</artifactId>
<version>1.1.1</version>
</dependency>
</dependencies>
10.8.3.4. 在配置文件src/main/resources/application.properties中配置多数据源
app.name=demo-datasource
app.group=dbtest
server.port=9876
mybatis.mapperLocations=classpath:/Mapper/*.xml
hs.db.enabled=true
#postgres
hs.datasource.default.driver-class-name=org.postgresql.Driver
hs.datasource.default.url=jdbc:postgresql://ip:port/postgres
hs.datasource.default.username=username
hs.datasource.default.password=password
hs.datasource.default.validationQuery=select 1
#mysql
hs.datasource.mysql.driverClassName=com.mysql.cj.jdbc.Driver
hs.datasource.mysql.url=jdbc:mysql://ip:port/unisql?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
hs.datasource.mysql.username=username
hs.datasource.mysql.password=password
#unisql multiplex
hs.datasource.unisql.driverClassName=com.hundsun.lightdb.unisql.proxy.Driver
hs.datasource.unisql.url=jdbc:unisql:oracle:thin:@//ip:port/orcl?sourceDialect=oracle&targetDialect=oracle&mode=MULTIPLEX
hs.datasource.unisql.username=username
hs.datasource.unisql.password=password
# 目标库为 lightdb_oracle 时，打开并修改以下连接信息
multiplex.datasource.lightdb_oracle.url=jdbc:postgresql://IP:PORT/DATABASE?sourceDialect=oracle&targetDialect=lightdb_oracle&options=-c%20search_path=public
multiplex.datasource.lightdb_oracle.username=USERNAME
multiplex.datasource.lightdb_oracle.password=PASSWORD
# 目标库为 gaussdb_oracle 时，打开并修改以下连接信息
multiplex.datasource.gaussdb_oracle.url=jdbc:opengauss://IP:PORT/DATABASE?sourceDialect=oracle&targetDialect=gaussdb_oracle&options=-c%20search_path=public
multiplex.datasource.gaussdb_oracle.username=USERNAME
multiplex.datasource.gaussdb_oracle.password=PASSWORD
10.8.3.5. 使用多数据源注解@EnableCloudDataSource注解用于开启多数据源
/**
* 多数据源注解@EnableCloudDataSource，开启多数据源功能
*/
@EnableCloudDataSource
@CloudApplication // 启动类注解
public class UnisqlMultiDataSourceApplication {
public static void main(String[] args) {
CloudBootstrap.run(UnisqlMultiDataSourceApplication.class, args);
}
}
10.8.3.6. 使用数据源指定注解@TargetDataSource指定当前服务类或服务方法所使用的数据源
@Service
public class TestServiceImpl implements TestService {
@Override
@TargetDataSource("unisql")// 指定数据源unisql，该数据源将会使用统一SQL
public List<Foo> selectFooByUnisql() {
List<Foo> foo = fooMapper.select();
return foo;
}
}
10.8.3.7. 基于mybatis，指定数据源unisql的mapper接口方法select对应的SQL映射文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.demo.middleware.dao.FooMapper">
<resultMap id="result" type="com.demo.middleware.domain.Foo">
<result column="city_id" property="cityId" jdbcType="INTEGER" />
<result column="nation" property="nation" jdbcType="VARCHAR" />
<result column="city_name" property="cityName" jdbcType="VARCHAR" />
<result column="city_sort" property="citySort" jdbcType="VARCHAR" />
</resultMap>
<select id="select" resultMap="result">
SELECT city_id, nation,city_name, city_sort FROM foo
</select>
</mapper>
10.8.3.8. 多发测试成功输出类似如下
2024-10-09 16:28:07.498 INFO 23724 --- [nio-9876-exec-2] c.h.lightdb.unisql.utils.PulsarUtil :
sql驱动拦截，发送sql增量变化到pulsar服务端,开始，
消息体[{"appName":"demo-datasource","databaseName":"uniq_oracle","effectRows":0,"fieldColumnNames":"city_id&&nation&&city_name&&city_sort",
"fieldColumnTypes":"numeric&&varchar&&varchar&&numeric","functionId":"","ip":"","onlyCompare":true,
"prev":[[1,"China","Beijing",1],[2,"USA","New York",2],[3,"Japan","Tokyo",3],[4,"China","ShangHai",4],[5,"Canada","Toronto",5]],
"recordTimestamp":1728548887496,"sourceDialect":"ORACLE","sql":"SELECT city_id, nation,city_name, city_sort FROM foo",
"sqlParameters":"","sqlSequence":1,"sqlType":"SELECT","tableName":"foo","targetDialect":"GAUSSDB_ORACLE",
"traceId":"86e163d7-2bf8-4ec6-92a7-d8ae287d585e","transferSql":"SELECT city_id,nation,city_name,city_sort FROM foo"}]
10.8.4. SpringBoot + JdbcTemplate（IDEA开发模式集成为例）
10.8.4.1. 数据源配置如下：
server.port=8080
mybatis.configuration.map-underscore-to-camel-case=true
spring.datasource.driver-class-name=com.hundsun.lightdb.unisql.proxy.Driver
spring.datasource.url=jdbc:unisql:oracle:thin:@//ip:port/orcl?sourceDialect=oracle&targetDialect=oracle&mode=MULTIPLEX
spring.datasource.username=username
spring.datasource.password=password
logging.level.com.hundsun.lightdb.unisql=info
# 目标库为 lightdb_oracle 时，打开并修改以下连接信息
multiplex.datasource.lightdb_oracle.url=jdbc:postgresql://IP:PORT/DATABASE?sourceDialect=oracle&targetDialect=lightdb_oracle&options=-c%20search_path=public
multiplex.datasource.lightdb_oracle.username=USERNAME
multiplex.datasource.lightdb_oracle.password=PASSWORD
# 目标库为 gaussdb_oracle 时，打开并修改以下连接信息
multiplex.datasource.gaussdb_oracle.url=jdbc:opengauss://IP:PORT/DATABASE?sourceDialect=oracle&targetDialect=gaussdb_oracle&options=-c%20search_path=public
multiplex.datasource.gaussdb_oracle.username=USERNAME
multiplex.datasource.gaussdb_oracle.password=PASSWORD
10.8.4.2. pom依赖：
<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.3.0</version> </dependency> <!--sql runtime--> <dependency> <groupId>com.hundsun.lightdb</groupId> <artifactId>sql-convert-runtime</artifactId> <version>24.2.3-SNAPSHOT</version> <scope>provided</scope> </dependency> <dependency> <groupId>com.hundsun.lightdb</groupId> <artifactId>unisql-compare-client</artifactId> <version>24.2.3-SNAPSHOT</version> <scope>system</scope> <systemPath>${project.basedir}/lib/unisql-compare-client-fat-24.2.3.jar</systemPath> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> <version>3.13.0</version> <!-- 这里可以使用最新版本 --> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.2.11</version> </dependency> <dependency> <groupId>com.github.jnr</groupId> <artifactId>jnr-ffi</artifactId> <version>2.2.14</version> </dependency> <dependency> <groupId>com.alibaba.fastjson2</groupId> <artifactId>fastjson2</artifactId> <version>2.0.36</version> <!-- 这里写上你想使用的版本号 --> </dependency> <dependency> <groupId>com.oracle.database.jdbc</groupId> <artifactId>ojdbc8</artifactId> </dependency> <!--gaussdb_oracle驱动--> <dependency> <groupId>com.huawei.opengauss.jdbc</groupId> <artifactId>gaussdbDriver</artifactId> <version>3.0.0-htrunk21</version> </dependency> <!--tdsql-pg--> <dependency> <groupId>com.tencentcloud.tdsql</groupId> <artifactId>tdsql-pg-connector-java8</artifactId> <version>1.1.1</version> </dependency> <!-- 日志相关 --> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-api</artifactId> <version>1.7.30</version> </dependency> <!-- logback 依赖 --> <dependency> <groupId>ch.qos.logback</groupId> <artifactId>logback-classic</artifactId> <version>1.2.3</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies>
10.8.4.3. 代码如下：
@Autowired
JdbcTemplate jdbcTemplate;
@Test
void testSpringboot() {
// 原生oracle sql语句
String oracleSQL = "select nation, listagg(city_name,',') within group (order by city_sort desc) citys from foo group by nation";
// 返回gaussdb_oracle执行结果
List<Map<String, Object>> list = jdbcTemplate.queryForList(oracleSQL);
}
10.8.4.4. 多发测试成功结果输出类似如下
2024-10-09 17:29:40.697 [main] INFO c.h.lightdb.unisql.utils.PulsarUtil - sql驱动拦截，发送sql增量变化到pulsar服务端,结束，
消息体[{"databaseName":"uniq_oracle","effectRows":0,"fieldColumnNames":"nation&&citys","fieldColumnTypes":"varchar&&text",
"functionId":"","ip":"","onlyCompare":true,"prev":[["Canada","Toronto"],["China","ShangHai,Beijing"],["Japan","Tokyo"],["USA","New York"]],
"recordTimestamp":1728552579224,"sourceDialect":"ORACLE","sql":"select nation, listagg(city_name,',') within group
(order by city_sort desc) citys from foo group by nation","sqlParameters":"","sqlSequence":1,"sqlType":"select",
"tableName":"foo","targetDialect":"GAUSSDB_ORACLE","traceId":"31f0aa49-bcda-495d-88d8-8fb2e4f46707","transferSql":"SELECT nation,
listagg(city_name, ',') WITHIN GROUP (ORDER BY city_sort DESC) AS citys FROM foo GROUP BY nation"}]，messageId=[131641:20:-1]
[{nation=Canada, citys=Token}, {nation=China, citys=ShangHai,Beijing}, {nation=Japan, citys=Tokyo}, {nation=USA, citys=New York}]