热门搜索
分布式
数据库一体机
云数据库
建设银行
保险核心
密集交易
注册
Zipkin适配达梦数据库
专栏/技术分享/ 文章详情 /

Zipkin适配达梦数据库

noroot 2023/09/18 1199 0 0
摘要

一、背景

因用户需要使用Zipkin追踪数据库调用链，记录整个调用过程的耗时及sql语句信息。查看Zipkin官网暂不适配达梦数据库接口，故作如下适配学习记录。

二、工具说明

DM8 - 达梦数据库

  • 官网/文档：https://eco.dameng.com/
  • 达梦数据库是达梦公司100% 自主研发的大型通用关系型数据库，具备极致兼容性、高可用性、高可靠性和高安全性，是解决我国基础软件领域“卡脖子”痛点的数据库产品，目前已助力 50+ 重点行业实现核心系统升级。

Zipkin

  • 官网/文档：https://github.com/openzipkin/zipkin
  • Zipkin 是一个基于Dapper的分布式链路追踪系统，可以收集分布式场景下的调用链路数据，用于解决服务之间延迟问题。

Brave

Springboot

  • 官网/文档：https://spring.io/projects/spring-boot
  • SpringBoot 是一种基于 Spring 的快速开发应用程序的框架，主要作用是简化 Spring 应用程序的配置和开发，同时提供一系列开箱即用的功能和组件，使开发者可以更加高效地构建和部署应用程序。

三、项目搭建

3.1 环境版本

image.png

3.2 方案一-编写拦截器代码实现

(1)Eclipse新建Springboot项目
image.png

(2)导入依赖的jar包或者新建lib目录
image.png

(3)新建JdbcTemplateConfig.java

package com.example.demo;

import javax.sql.DataSource;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

@Configuration
public class JdbcTemplateConfig {

	@Bean
	public DataSource dataSource() {
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName("dm.jdbc.driver.DmDriver");
		dataSource.setUrl("jdbc:dm://192.168.206.147:5237?user=SYSDBA");
		dataSource.setUsername("SYSDBA");
		dataSource.setPassword("SYSDBA");
		return dataSource;

	}

	@Bean
	public JdbcTemplate jdbcTemplate() {
		return new JdbcTemplate(dataSource());
	}

}

(4)新建RestTemplateConfig.java

package com.example.demo;

import java.nio.charset.Charset;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.http.client.ClientHttpRequestFactory;
import org.springframework.http.converter.StringHttpMessageConverter;
import org.springframework.web.client.RestTemplate;

@Configuration
public class RestTemplateConfig {
	@Bean
	public RestTemplate restTemplate(ClientHttpRequestFactory factory) {
		RestTemplate restTemplate = new RestTemplate(factory);
		// 支持中文编码
		restTemplate.getMessageConverters().set(1, new StringHttpMessageConverter(Charset.forName("UTF-8")));
		return restTemplate;
	}
}

(5)新建ZipkinConfig.java，在此定制需要追踪的tag

package com.example.demo;

import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cloud.sleuth.Span;
import org.springframework.cloud.sleuth.Tracer;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.http.client.ClientHttpRequestFactory;
import org.springframework.http.client.ClientHttpRequestInterceptor;
import org.springframework.http.client.ClientHttpResponse;
import org.springframework.http.client.SimpleClientHttpRequestFactory;
import org.springframework.stereotype.Component;
import org.springframework.web.client.RestTemplate;

@Configuration
public class ZipkinConfig {

	@Autowired
	private Tracer tracer;

	/*
	 * @Autowired private DataSource dataSource;
	 */

	@Bean("zipkinRestTemplate")
	public RestTemplate createRestTemplate() {
		RestTemplate restTemplate = new RestTemplate(simpleClientHttpRequestFactory());
		List<ClientHttpRequestInterceptor> interceptors = restTemplate.getInterceptors();
		interceptors.add(new ClientHttpRequestInterceptor() {
			@Override
			public ClientHttpResponse intercept(org.springframework.http.HttpRequest request, byte[] body,
					org.springframework.http.client.ClientHttpRequestExecution execution) throws java.io.IOException {
				if (tracer != null) {
					request.getHeaders().add("X-B3-TraceId", tracer.currentSpan().context().traceId());
					request.getHeaders().add("X-B3-SpanId", tracer.currentSpan().context().spanId());
				}
				return execution.execute(request, body);
			}
		});
		restTemplate.setInterceptors(interceptors);
		return restTemplate;
	}

	/*
	 * @Bean public JdbcTemplate jdbcTemplate(DataSource dataSource) { return new
	 * JdbcTemplate(dataSource); }
	 */

	@Bean
	public TraceJdbcTemplateAspect traceJdbcTemplateAspect() {
		return new TraceJdbcTemplateAspect();
	}

	@Bean
	public ClientHttpRequestFactory simpleClientHttpRequestFactory() {
		SimpleClientHttpRequestFactory factory = new SimpleClientHttpRequestFactory();
		factory.setReadTimeout(5000);
		factory.setConnectTimeout(5000);
		return factory;
	}

	@Aspect
	@Component
	public static class TraceJdbcTemplateAspect {

		private final static Pattern PATTERN_SELECT = Pattern.compile("\\s*select.+from\\s+([^\\s]+)(\\s+.+)?\\s*",
				Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
		private final static Pattern PATTERN_INSERT = Pattern.compile("\\s*insert\\s+into\\s+([^\\s]+)(\\s+.+)?\\s*",
				Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
		private final static Pattern PATTERN_UPDATE = Pattern.compile("\\s*update\\s+([^\\s]+)(\\s+.+)?\\s*",
				Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
		private final static Pattern PATTERN_DELETE = Pattern.compile("\\s*delete\\s+from\\s+([^\\s]+)(\\s+.+)?\\s*",
				Pattern.CASE_INSENSITIVE | Pattern.DOTALL);

		@Autowired
		private Tracer tracer;

		@Pointcut("execution(* org.springframework.jdbc.core.JdbcTemplate.queryForList*(..)) ||"
				+ "execution(* org.springframework.jdbc.core.JdbcTemplate.update*(..))")
		public void executeQuery() {
		}

		@Around("executeQuery()")
		public Object queryAround(ProceedingJoinPoint pjp) throws Throwable {

			String sql = (String) pjp.getArgs()[0];
			// System.out.println(sql);
			Span span = tracer.nextSpan().name(getOperation(sql));
			// System.out.println(span);
			try {
				long startTime = System.currentTimeMillis();
				Object result = pjp.proceed();
				long endTime = System.currentTimeMillis();

				span.tag("sql", sql);
				// span.tag("dataSource", dataSource.getClass().toString());
				span.tag("time", (endTime - startTime) + "ms");

				return result;
			} catch (Exception e) {
				span.tag("error", e.getMessage());
				throw e;
			} finally {
				span.end();
			}
		}

		private String getOperation(String sql) {
			if (sql != null && !"".equals(sql.trim())) {
				Matcher matcherSelect = PATTERN_SELECT.matcher(sql);
				if (matcherSelect.matches()) {
					return "SELECT FROM " + matcherSelect.group(1).trim();
				}

				Matcher matcherInsert = PATTERN_INSERT.matcher(sql);
				if (matcherInsert.matches()) {
					return "INSERT INTO " + matcherInsert.group(1).trim();
				}

				Matcher matcherUpdate = PATTERN_UPDATE.matcher(sql);
				if (matcherUpdate.matches()) {
					return "UPDATE " + matcherUpdate.group(1).trim();
				}

				Matcher matcherDelete = PATTERN_DELETE.matcher(sql);
				if (matcherDelete.matches()) {
					return "DELETE FROM " + matcherDelete.group(1).trim();
				}
			}
			return "SQL";
		}
	}
}

(6)新建ZipkinDmApplication.java

package com.example.demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.cloud.client.loadbalancer.LoadBalanced;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.web.client.RestTemplate;

@ComponentScan(basePackages = { "com.example.demo" })
//@EnableZipkinServer
//@EnableDiscoveryClient
@SpringBootApplication
public class ZipkinDemoApplication {

	@Bean
	@LoadBalanced
	public RestTemplate restTemplate() {
		return new RestTemplate();
	}

	public static void main(String[] args) {
		SpringApplication.run(ZipkinDemoApplication.class, args);
	}

}

(7)新建ZipkinDmController.java

package com.example.demo;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.web.servlet.ServletComponentScan;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

//@EnableWebMvc
@ServletComponentScan
@RestController
@RequestMapping(value = "/db", method = { RequestMethod.GET, RequestMethod.POST })
public class ZipkinDmController {

//注入 jdbcTemplate 模板对象

	private final JdbcTemplate jdbcTemplate;

	@Autowired
	public ZipkinDmController(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	// 编写测试方法
	// 浏览器访问 http://localhost:8045/db/test1 ，返回sql执行结果。
	@RequestMapping("/test1")
	@ResponseBody

	public String test1() {

		String sql1 = "DELETE FROM test WHERE id=1;COMMIT;";
		jdbcTemplate.execute(sql1);

		String sql2 = "SELECT name FROM test;";
		List<Map<String, Object>> result1 = jdbcTemplate.queryForList(sql2);

		return "Query name result: " + result1.toString();
	}

	// 浏览器访问 http://localhost:8045/db/test2 ，返回sql执行结果。
	@RequestMapping("/test2")
	@ResponseBody
	public String test2() {

		String sql3 = "INSERT INTO test VALUES (4,'FOUR');COMMIT;";
		jdbcTemplate.execute(sql3);

		String sql4 = "UPDATE test SET name='Tom' WHERE id=2;COMMIT;";
		jdbcTemplate.execute(sql4);

		String sql5 = "SELECT name FROM test;";
		List<Map<String, Object>> result2 = jdbcTemplate.queryForList(sql5);

		return "Query name result2: " + result2.toString();
	}
}

(8)修改application.propertites

server.port=8045

spring.datasource.driver-class-name=dm.jdbc.driver.DmDriver
spring.datasource.url=jdbc:dm://192.168.206.147:5237
spring.datasource.username=SYSDBA
spring.datasource.password=SYSDBA

spring.main.allow-bean-definition-overriding=true
spring.application.name=ZIPKIN_DEMO

# zipkin
spring.zipkin.base-url=http://192.168.206.147:9411/
spring.sleuth.sampler.probability=1.0

# logging.level.org.springframework.jdbc.core.JdbcTemplate=DEBUG

(9)修改pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.5.0</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.example</groupId>
	<artifactId>ZIPKIN_DEMO</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>ZIPKIN_DEMO</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>1.8</java.version>
		<spring-cloud.version>2020.0.3</spring-cloud.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</artifactId>
		</dependency>
		<dependency>
   			<groupId>org.springframework.boot</groupId>
   			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.cloud</groupId>
			<artifactId>spring-cloud-sleuth-zipkin</artifactId>
		</dependency>
		<dependency>
    		<groupId>org.springframework.cloud</groupId>
    		<artifactId>spring-cloud-starter-sleuth</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>
	<dependencyManagement>
		<dependencies>
			<dependency>
				<groupId>org.springframework.cloud</groupId>
				<artifactId>spring-cloud-dependencies</artifactId>
				<version>${spring-cloud.version}</version>
				<type>pom</type>
				<scope>import</scope>
			</dependency>
		</dependencies>
	</dependencyManagement>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

3.3 方案二-使用拦截器接口实现

(1)Eclipse新建Springboot项目
image.png

(2)导入依赖的jar包或者新建lib目录
image.png

(3)新建JdbcTemplateConfig.java，主要修改数据库连接串URL配置接口。

package com.example.demo;

import javax.sql.DataSource;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

@Configuration
public class JdbcTemplateConfig {

	@Bean
	/* DM */
	public DataSource dataSource() {
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName("dm.jdbc.driver.DmDriver");
		dataSource.setUrl(
				"jdbc:dm://192.168.206.147:5237/?customFilter=com.dameng.zipkin.DmZipkinCustom&zipkinServiceName=ZIPKIN_DM");
		dataSource.setUsername("SYSDBA");
		dataSource.setPassword("SYSDBA");
		return dataSource;
	}

	@Bean
	public JdbcTemplate jdbcTemplate() {
		return new JdbcTemplate(dataSource());
	}

}

(4)新建ZipkinDmApplication.java

package com.example.demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class ZipkinDmApplication {

	public static void main(String[] args) {
		SpringApplication.run(ZipkinDmApplication.class, args);
	}

}

(5)新建ZipkinDmController.java

package com.example.demo;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.web.servlet.ServletComponentScan;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

//@EnableWebMvc
@ServletComponentScan
@RestController
@RequestMapping(value = "/db", method = { RequestMethod.GET, RequestMethod.POST })
public class ZipkinDmController {

//注入 jdbcTemplate 模板对象

	private final JdbcTemplate jdbcTemplate;

	@Autowired
	public ZipkinDmController(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	// 编写测试方法
	// 浏览器访问 http://localhost:8045/db/test1 ，返回sql执行结果。
	@RequestMapping("/test1")
	@ResponseBody

	public String test1() {

		String sql1 = "DELETE FROM test WHERE id=1;";
		jdbcTemplate.execute(sql1);

		String sql2 = "SELECT name FROM test";
		List<Map<String, Object>> result1 = jdbcTemplate.queryForList(sql2);

		return "Query name result: " + result1.toString();
	}

	// 浏览器访问 http://localhost:8045/db/test2 ，返回sql执行结果。
	@RequestMapping("/test2")
	@ResponseBody
	public String test2() {

		String sql3 = "INSERT INTO test VALUES (4,'FOUR');";
		jdbcTemplate.execute(sql3);

		String sql4 = "UPDATE test SET name='Tom' WHERE id=2;";
		jdbcTemplate.execute(sql4);

		String sql5 = "SELECT name FROM test;";
		List<Map<String, Object>> result2 = jdbcTemplate.queryForList(sql5);

		return "Query name result2: " + result2.toString();
	}

}

(6)修改application.propertites

server.port=8045

spring.datasource.driver-class-name=dm.jdbc.driver.DmDriver
spring.datasource.url=jdbc:dm://192.168.206.147:5237
spring.datasource.username=SYSDBA
spring.datasource.password=SYSDBA

spring.main.allow-bean-definition-overriding=true
spring.application.name=ZIPKIN_DM

# zipkin
spring.zipkin.base-url=http://192.168.206.147:9411/
spring.sleuth.sampler.probability=1.0

# logging.level.org.springframework.jdbc.core.JdbcTemplate=DEBUG

(7)修改pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.5.0</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.example</groupId>
	<artifactId>ZIPKIN_DM</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>ZIPKIN_DM</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>1.8</java.version>
		<spring-cloud.version>2020.0.3</spring-cloud.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</artifactId>
		</dependency>
		<dependency>
   			<groupId>org.springframework.boot</groupId>
   			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.cloud</groupId>
			<artifactId>spring-cloud-sleuth-zipkin</artifactId>
		</dependency>
		<dependency>
    		<groupId>org.springframework.cloud</groupId>
    		<artifactId>spring-cloud-starter-sleuth</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>
	<dependencyManagement>
		<dependencies>
			<dependency>
				<groupId>org.springframework.cloud</groupId>
				<artifactId>spring-cloud-dependencies</artifactId>
				<version>${spring-cloud.version}</version>
				<type>pom</type>
				<scope>import</scope>
			</dependency>
		</dependencies>
	</dependencyManagement>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

四、实现效果

(1)打开zipkin服务

java -jar zipkin-server-2.24.2-exec.jar

image.png

(2)客户端执行测试程序
在JdbcTemplateConfig.java文件中修改url连接串，运行demo。
1695024817466.png

(3)在数据库中创建测试表：

CREATE TABLE test(id int,name char(200)); 
INSERT INTO test VALUES(1,'ONE'),(2,'TWO'),(3,'THREE'); 
COMMIT;
SELECT * FROM test;

1695025172010.png

(4)浏览器访问：http://localhost:8045/db/test1

1695025311152.png

(5)浏览器访问：http://192.168.206.147:9411/zipkin/
Zipkin页面点击 > RUN QUERY 可以看到追踪结果：

1695025352420.png

Zipkin页面点击 > SHOW 可以查看追踪详情：
1695025398868.png

点击子项可以查看到执行的sql语句及耗时：
1695025471081.png

上一篇：
安全版本数据库替换临时（正式）授权
评论
后发表回复

作者

文章

阅读量

获赞

扫一扫
联系客服
logo
logo
logo
鄂Copyright © 达梦国产化应用创新实验室
达梦在线服务平台社区用户管理规范

鄂ICP备18017926号-1