DataSource instrumentation

Prerequisites

• You have upgraded SOFABoot to version 3.4.11 or later.

  DataSource instrumentation is disabled by default in SOFABoot versions earlier than 3.4.11 and cannot be enabled through configuration. You must upgrade your SOFABoot version. For more information, see Version Guide.

• You have built a Spring Web project based on SOFABoot.

Import Maven dependencies

Import the Tracer dependency

In your SOFABoot Web project, import the following Tracer dependency:

<dependency>
      <groupId>com.alipay.sofa</groupId>
      <artifactId>tracer-enterprise-sofa-boot-starter</artifactId>
</dependency>

After you add the Tracer dependency, you can add configuration items to the SOFABoot global configuration file to customize the behavior of Tracer.

Import the H2Database dependency

This example uses the H2Database in-memory database for testing purposes. You must import the following dependencies:

<dependency>
      <groupId>com.h2database</groupId>
      <artifactId>h2</artifactId>
      <scope>runtime</scope>
</dependency>

<dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
</dependency>

Import connection pool dependencies

Import the required connection pool dependencies, such as Druid, c3p0, Tomcat, DBCP, and Hikari.

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.0.12</version>
</dependency>
<dependency>
    <groupId>c3p0</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.1.1</version>
</dependency>
<dependency>
    <groupId>org.apache.tomcat</groupId>
    <artifactId>tomcat-jdbc</artifactId>
    <version>8.5.31</version>
</dependency>
<dependency>
    <groupId>commons-dbcp</groupId>
    <artifactId>commons-dbcp</artifactId>
    <version>1.4</version>
</dependency>
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP-java6</artifactId>
    <version>2.3.8</version>
</dependency>

Configure the data source

This example uses HikariCP. Create a Spring configuration file named datasource.xml and add the following content:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
    <!-- Data source pool -->
    <bean id="simpleDataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close" primary="true">
        <property name="driverClassName" value="org.h2.Driver"/>
        <property name="jdbcUrl" value="jdbc:h2:~/test"/>
        <property name="username" value="${username}"/>
        <property name="password" value="${password}"/>
    </bean>
</beans>

Local application configuration

• Required configurations: You must configure an application name for Tracer. Otherwise, the application fails to start. This is a requirement of the SOFABoot framework. The following example shows the configuration:

  spring.application.name=SOFATracerDataSource
  com.alipay.sofa.tracer.datasource.enable=true

  Note

  By default, DataSource instrumentation is disabled in SOFABoot Enterprise Edition. You must enable it through configuration.

• Optional configurations: To run this sample project, you must configure the H2Database properties. To make it easier to view logs, you can also configure the log path. The following example shows the configuration:

  # logging path
    logging.path=./logs
  
  # H2 web console path
    spring.h2.console.path=/h2-console
  # Enable the H2 web console. Default is false.
    spring.h2.console.enabled=true
  # Allow remote access to the H2 web console
    spring.h2.console.settings.web-allow-others=true
  
    spring.datasource.username=sofa
    spring.datasource.password=123456
    spring.datasource.url=jdbc:h2:~/test
    spring.datasource.driver-class-name=org.h2.Driver

Create a REST service

Create a REST service to trigger the execution of SQL statements. This lets you view the Tracer records for the SQL statements. The following REST service triggers a table creation operation.

@RestController
public class SimpleRestController {

    @Autowired
    private DataSource simpleDataSource;

    @RequestMapping("/create")
    public Map<String, Object> create() {
        Map<String, Object> resultMap = new HashMap<String, Object>();
        try {
            Connection cn = simpleDataSource.getConnection();
            Statement st = cn.createStatement();
            st.execute("DROP TABLE IF EXISTS TEST;"
                    + "CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255));");
            resultMap.put("success", true);
            resultMap.put("result", "CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
        } catch (Throwable throwable) {
            resultMap.put("success", false);
            resultMap.put("error", throwable.getMessage());
        }
        return resultMap;
    }

}

Run the project

Import the SOFABoot project into an IDE. After the project compiles, run the `main` method in the project to start the application. After the application starts, you can access localhost:8080/create in a browser to execute the REST service.

View logs

You can view the Tracer logs for SQL execution in ./logs/datasource-client-digest.log and ./logs/datasource-client-stat.log.

• Example datasource-client-digest.log:

  {"time":"2019-09-02 21:31:31.566","local.app":"SOFATracerDataSource","traceId":"0a0fe91d156743109138810017302","spanId":"0.1","span.kind":"client","result.code":"00","current.thread.name":"http-nio-8080-exec-1","time.cost.milliseconds":"15ms","database.name":"test","sql":"DROP TABLE IF EXISTS TEST;
  CREATE TABLE TEST(ID INT PRIMARY KEY%2C NAME VARCHAR(255));","connection.establish.span":"128ms","db.execute.cost":"15ms","database.type":"h2","database.endpoint":"jdbc:h2:~/test:-1","sys.baggage":"","biz.baggage":""}

• Example datasource-client-stat.log:

  {"time":"2019-09-02 21:31:50.435","stat.key":{"local.app":"SOFATracerDataSource","database.name":"test","sql":"DROP TABLE IF EXISTS TEST;
  CREATE TABLE TEST(ID INT PRIMARY KEY%2C NAME VARCHAR(255));"},"count":1,"total.cost.milliseconds":15,"success":"true","load.test":"F"}

Notes

• You must configure an application name when you import SOFATracer. Otherwise, the application fails to start. The property name is spring.application.name.

• SOFATracer is based on the standard JDBC interface and supports automatic instrumentation for connection pools such as DBCP, Druid, c3p0, Tomcat, and HikariCP. In Spring Boot environments, you only need to import the SOFATracer dependency. However, in non-Spring Boot environments, you must also add manual configurations, such as:

  <bean id="smartDataSource" class="com.alipay.sofa.tracer.plugins.datasource.SmartDataSource"init-method="init">
      <property name="delegate" ref="simpleDataSource"/>
      <!-- Application name -->
      <property name="appName" value="yourAppName"/>
      <!-- Database name -->
      <property name="database" value="yourDatabase"/>
      <!-- Database type. MYSQL and ORACLE are supported. -->
      <property name="dbType" value="MYSQL"/>
  </bean>
  
  <bean id="simpleDataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
      <property name="driverClass" value="com.mysql.jdbc.Driver"/>
      <property name="jdbcUrl" value="jdbc:mysql://127.0.0.1/yourdb"/>
      <property name="username" value="${username}"/>
      <property name="password" value="${password}"/>
          ...
  </bean>