Skip to main content

Data Connection

Qiantong Tech9/17/25About 6 minDevelopment Docs

Tip

To meet data source access requirements in different business scenarios, the system provides the "Data Connection" menu. This article explains how to use this feature to configure and extend the required database or API data connections independently.

1. Data Connection - SQL

The DA_DATASOURCE table is mainly used to record data source connection information configured in the system. It includes regular connection parameters such as data source name (DATASOURCE_NAME), type (DATASOURCE_TYPE), IP address (IP), and port (PORT), supporting connection management for most relational databases.

However, in actual applications, especially when connecting to data sources in the big data ecosystem such as Hive, HBase, and Kafka, connection methods are often more complex and required configuration items are more flexible, so fixed fields cannot fully cover them. Therefore, the DATASOURCE_CONFIG field is reserved in the table to store extended parameters as a JSON string, such as username, password, Kerberos authentication information, and Zookeeper address. This field gives the system stronger extensibility and adaptation capability, allowing it to support dynamic access to multiple heterogeneous data sources.

-- ----------------------------
-- Data source table DM sample script
-- ----------------------------
CREATE TABLE "QDATA"."DA_DATASOURCE"
(
    "ID" BIGINT IDENTITY(49, 1) NOT NULL,
    "DATASOURCE_NAME" VARCHAR2(128) NOT NULL,
    "DATASOURCE_TYPE" VARCHAR2(32) NOT NULL,
    "DATASOURCE_CONFIG" VARCHAR2(3072),
    "IP" VARCHAR2(32) NOT NULL,
    "PORT" INTEGER NOT NULL,
    "LIST_COUNT" INTEGER DEFAULT 0,
    "SYNC_COUNT" INTEGER DEFAULT 0,
    "DATA_SIZE" INTEGER DEFAULT 0,
    "DESCRIPTION" VARCHAR2(512),
    "VALID_FLAG" VARCHAR2(1) DEFAULT 1 NOT NULL,
    "CREATE_BY" VARCHAR2(32),
    "CREATOR_ID" BIGINT,
    "CREATE_TIME" DATETIME(6),
    "UPDATE_BY" VARCHAR2(32),
    "UPDATER_ID" BIGINT,
    "UPDATE_TIME" DATETIME(6),
    "REMARK" VARCHAR2(512),
    "DEL_FLAG" VARCHAR(1) DEFAULT '0' NOT NULL,
    NOT CLUSTER PRIMARY KEY("ID")) STORAGE(ON "MAIN", CLUSTERBTR) ;

COMMENT ON TABLE "QDATA"."DA_DATASOURCE" IS 'Data source table';
COMMENT ON COLUMN "QDATA"."DA_DATASOURCE"."CREATE_BY" IS 'Creator';
COMMENT ON COLUMN "QDATA"."DA_DATASOURCE"."CREATE_TIME" IS 'Creation time';
COMMENT ON COLUMN "QDATA"."DA_DATASOURCE"."CREATOR_ID" IS 'Creator ID';
COMMENT ON COLUMN "QDATA"."DA_DATASOURCE"."DATA_SIZE" IS 'Synchronized data size (reserved)';
COMMENT ON COLUMN "QDATA"."DA_DATASOURCE"."DATASOURCE_CONFIG" IS 'Data source configuration (JSON string)';
COMMENT ON COLUMN "QDATA"."DA_DATASOURCE"."DATASOURCE_NAME" IS 'Data source name';
COMMENT ON COLUMN "QDATA"."DA_DATASOURCE"."DATASOURCE_TYPE" IS 'Data source type';
COMMENT ON COLUMN "QDATA"."DA_DATASOURCE"."DESCRIPTION" IS 'Description';
COMMENT ON COLUMN "QDATA"."DA_DATASOURCE"."ID" IS 'ID';
COMMENT ON COLUMN "QDATA"."DA_DATASOURCE"."IP" IS 'IP';
COMMENT ON COLUMN "QDATA"."DA_DATASOURCE"."LIST_COUNT" IS 'Database table count (reserved)';
COMMENT ON COLUMN "QDATA"."DA_DATASOURCE"."PORT" IS 'Port number';
COMMENT ON COLUMN "QDATA"."DA_DATASOURCE"."REMARK" IS 'Remarks';
COMMENT ON COLUMN "QDATA"."DA_DATASOURCE"."SYNC_COUNT" IS 'Synchronized record count (reserved)';
COMMENT ON COLUMN "QDATA"."DA_DATASOURCE"."UPDATE_BY" IS 'Updater';
COMMENT ON COLUMN "QDATA"."DA_DATASOURCE"."UPDATE_TIME" IS 'Update time';
COMMENT ON COLUMN "QDATA"."DA_DATASOURCE"."UPDATER_ID" IS 'Updater ID';
COMMENT ON COLUMN "QDATA"."DA_DATASOURCE"."VALID_FLAG" IS 'Whether valid; 0: invalid, 1: valid';

2. Data Connection - Business Code

The data connection table name DA_DATASOURCE shows that data source connection business code belongs to the qdata-module-da module. In this module, the controller-layer entry class is located at:

tech.qiantong.qdata.module.da.controller.admin.datasource

Specifically, the core controller is DaDatasourceController, which handles data connection API requests, including adding data sources, editing data sources, and testing connections.

If you want to extend data connection business logic, such as adding supported database types, adjusting connection parameter structures, or extending validation logic, you can start from this class. The lower service layer and related layers also need corresponding coordinated changes.

3. Data Connection - Utility Methods

When you go deeper into the service layer of the data connection module, you will find that its underlying JDBC interaction logic is not independently implemented in every business class. Instead, it is uniformly encapsulated and points to a shared utility package.

This shared package is in the qdata-common submodule under the qdata-framework module. The specific path is:

tech.qiantong.qdata.common.database

In this package, the system uniformly encapsulates all database interaction capabilities, including but not limited to:

  • Establishing data source connections, such as DataSourceFactory
  • Selecting an appropriate SQL dialect by data source type, such as DbDialect and DialectFactory
  • Obtaining database structure information, such as DbQuery and DbQueryFactoryBean
  • Executing SQL queries and extracting metadata entities, such as DbTable and DbColumn
  • Supporting caching mechanisms, exception encapsulation, field type definitions, and more

📂 Package structure (core class descriptions):

  • datasource: provides abstract factory and default factory classes for dynamically creating connection objects
  • query: defines query execution logic interfaces and concrete implementations
  • dialect: encapsulates SQL dialect adaptation strategies by database type
  • constants: defines database types, field types, parsing mode enums, and more
  • core: abstract table, field, and pagination objects
  • utils: utility classes such as MD5Util

This design greatly enhances the system's database adaptation capability. To add a new data source type, you only need to implement the corresponding dialect and query factory to seamlessly integrate with the entire data connection module, ensuring extensibility and code decoupling.

4. Extending a New Data Source Type (Development Process)

If you have carefully read the introduction to the shared data connection method system above, you should now have a basic understanding of its overall structure. Next, we use extending a MySQL data source as an example to walk through the complete process of adding a data source type, making it easier to connect other databases such as Hive and ClickHouse later.

1. Add a data source type in the DbType enum

Path: tech.qiantong.qdata.common.database.constants.DbType

/**
 * MYSQL
 */
MYSQL("MySql",
      "MySql database",
      "jdbc:mysql://${host}:${port}/${dbName}?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8",
      "LENGTH",
      "SELECT COUNT(1) FROM {tableName}",
      "SELECT {tableFieldName} FROM {tableName} ORDER BY {orderBy} DESC LIMIT ({pageNo}-1)*{pageSize},{pageSize}");

Each data source enum item includes:

  • Data source identifier
  • Display name
  • JDBC URL template
  • Function for obtaining field length
  • SQL template for querying record count
  • SQL template for paginated queries

2. Create the corresponding Dialect class (dialect parser)

Path: tech.qiantong.qdata.common.database.dialect

Create a class such as MySqlDialect, which must extend the abstract class:

public class MySqlDialect extends AbstractDbDialect {
    // Implement abstract methods from the parent class
}

3. Implement dialect logic

In MySqlDialect, implement the core methods defined by AbstractDbDialect, such as:

  • tables
  • columns
  • buildQuerySqlFields
  • someInternalSqlGenerator

Refer to existing database implementations or the DbQuery interface documentation.

4. Register the new dialect in the dialect factory

Path: DialectRegistry.java
Location: tech.qiantong.qdata.common.database.dialect.DialectRegistry

Register the new dialect in the mapping table in the constructor:

public DialectRegistry() {
    ...
    dialect_enum_map.put(DbType.MYSQL, new MySqlDialect());
    ...
}

Make sure your DbType enum value is consistent with the registration item.

5. At this point, the main work for adding a data source is complete.

When the system handles data source-related functions, such as table creation statement generation, field extraction, and paginated queries, it automatically calls the Dialect class you implemented according to DbType, thereby adapting access to different databases.

5. Additional Extension Notes

1. Special Data Connections (HDFS, HBase, Kafka, etc.)

For HDFS, HBase, Kafka, and other data sources that cannot establish standard connections through JDBC, the access method is slightly different from traditional databases. These data sources require the following special handling in the data connection module:

(1) Configuration storage method

The connection parameters required by these special data sources, such as Zookeeper address, authentication information, and topic name, are stored uniformly in the DATASOURCE_CONFIG field. This field is in JSON format and supports flexible extension.

(2) Build the connection context object (DbQueryProperty)

Before calling the database factory method createDbQuery(), you must construct DbQueryProperty, which includes regular fields such as host address, port, and database name.

For these special data sources, additional connection parameters must be written compatibly into the config field:

DbQueryProperty dbQueryProperty = new DbQueryProperty();
dbQueryProperty.setHost("kafka-host");
dbQueryProperty.setPort(9092);
dbQueryProperty.setConfig(Map.of(
  "security.protocol", "SASL_PLAINTEXT",
  "sasl.mechanism", "PLAIN"
));

(3) Implement custom connection validation logic (Kafka example)

Kafka connection testing requires a custom validConnection method. It no longer establishes a connection through JDBC, but tests through Kafka AdminClient:

@Override
public Boolean validConnection(DataSource dataSource, DbQueryProperty dbQueryProperty) {
    Properties props = new Properties();
    props.put("bootstrap.servers", dbQueryProperty.getHost() + ":" + dbQueryProperty.getPort());
    props.put("default.api.timeout.ms", 10000);
    props.put("request.timeout.ms", 10000);
    props.put("admin.request.timeout.ms", 10000);

    if (dbQueryProperty.getConfig() != null && !dbQueryProperty.getConfig().isEmpty()) {
        dbQueryProperty.getConfig().forEach(props::put);
    }

    String topic = "TEST_TOPIC_" + UUID.randomUUID();
    AdminClient admin = AdminClient.create(props);
    try {
        admin.createTopics(Collections.singleton(new NewTopic(topic, 1, (short) 1))).all().get();
        admin.deleteTopics(Collections.singleton(topic)).all().get();
        return true;
    } catch (Exception e) {
        throw new DataQueryException("Kafka connection failed. Please try again later.");
    } finally {
        try {
            admin.close();
        } catch (Exception e) {
            throw new DataQueryException("Failed to close Kafka connection.");
        }
    }
}

(4) Adaptation for other data source types

For other non-relational data sources such as HDFS and HBase, follow the Kafka approach in the corresponding DbQuery implementation class:

  • Obtain necessary parameters from DbQueryProperty.config
  • Customize connection initialization logic, such as FileSystem and HBaseAdmin
  • Override valid() or other related methods

📝 Summary:
The adaptation principle for special data sources is: JSON-based configuration information, strategy-based connection process, and unified API calls, ensuring all data source types can be registered, initialized, and validated through the unified factory mechanism.

2. Page configuration

At this point, we have completed the backend extension for the new data source type. However, if no configuration is performed on the frontend page, the data source type still cannot be selected. Configure it in the System Management → Dictionary Management menu.

The specific steps are:

  • Open the System Management module → Dictionary Management page
  • Find the dictionary type: datasource_type
  • Add a dictionary value under this dictionary item. The dictionary code must remain consistent with the backend DbType enum name, such as KAFKA or CLICKHOUSE

After configuration is complete, the corresponding type option appears in the add data source form on the frontend page, supporting page linkage and parameter input.

Summary

At this point, the development documentation for the data connection module has been fully introduced, including the module structure, core features, underlying encapsulation logic, and the complete extension process for adding data source types. Through this guide, you can quickly understand and master the overall design philosophy and development method of this module, laying a foundation for later data source adaptation and capability extension.

Last Updated:
qData Open-Source Data Platform
Copyright © 2026 Qiantong Tech