Published at: 2025-10-30

DB Connector 1.x User Guide

ShareCRM DB Connector Documentation (Legacy Version)

Warning: This page documents the legacy version which is for maintenance only. New customers must use the new DB Connector 2.x version.
DB Connector 2.x User Manual

I. Deployment Instructions

image

Each connector links to one customer database. Only one Integration Platform agent service needs to be deployed in the customer’s server room to manage multiple databases.

The Integration Platform accesses the DB proxy service using the admin-configured agent credentials, while the DB proxy service accesses the actual database using database credentials.

Checklist: - Whether the customer’s server room allows external network access - If the customer’s server room has an access whitelist, verify it includes all ShareCRM egress IPs - Whether the customer’s server room has opened the ports used by the proxy

II. Configuration Screenshots

1. Primary Object

image

2. Sub-object

image

III. Configuration Guide

1. General Configuration

1) Table Primary Key Field: The unique identifier field for the table
2) Date Query Condition Column: Time field used for queries (multiple fields separated by [;] or [,])
3) Query Date Format: Date format for query fields
4) Batch Query SQL: SQL for batch polling queries. If the Primary Object SQL contains a <mark>placeholder</mark> [query_criteria_place_holder], query conditions will replace it; otherwise, conditions will be appended. Adding ORDER BY reduces data omission risk.
5) Primary Key Query SQL: SQL to retrieve primary keys after insertion
6) Insert SQL: Template for inserting records from CRM to intermediate database (use ‘#field_name’ format for values; ‘#pid’ references the primary table’s key in detail records)
7) Update SQL: Template for updating records from CRM to intermediate database (same format rules as Insert SQL)

2. Special Configuration (Temporary Tables)

1) If no [Date Query Condition Column] exists, have customer IT execute this temporary table creation SQL (modify per database type if needed).

Purpose: Without date fields, each read would require full table scans.

The Integration Platform performs timed polling from this temp table while triggering an async thread to read from the main table (only changed records update their timestamps).

First API call reads empty temp table and triggers async data load. Subsequent calls retrieve available data and trigger new async loads.

-- PostgreSQL CREATE TABLE sync_obj_record ( id int8 NOT NULL, obj_name varchar NOT NULL, md_str varchar NOT NULL, data_id varchar NOT NULL, data_body text NOT NULL, "version" int NOT NULL, create_time int8 NOT NULL, update_time int8 NOT NULL, CONSTRAINT sync_obj_record_pk PRIMARY KEY (id) ); CREATE UNIQUE INDEX sync_obj_record_obj_name_idx ON sync_obj_record (obj_name,data_id);

``` – ORACLE CREATE TABLE sync_obj_record ( id NUMBER(19) NOT NULL, obj_name VARCHAR2(255) NOT NULL, md_str VARCHAR2(255) NOT NULL, data_id VARCHAR2(255) NOT NULL, data_body CLOB NOT NULL, version NUMBER(19) NOT NULL, create_time NUMBER(19) NOT NULL, update_time NUMBER(19) NOT NULL, CONSTRAINT sync_obj_record_pk PRIMARY KEY (id) );

CREATE UNIQUE INDEX sync_obj_record_obj_name_idx ON sync_obj_record (obj_name, data_id); ```

``` – SQLServerCREATE TABLE [dbo].[sync_obj_record] ( [id] bigint NOT NULL, [obj_name] varchar(100) COLLATE Chinese_PRC_CI_AS NOT NULL, [md_str] varchar(32) COLLATE Chinese_PRC_CI_AS NOT NULL, [data_id] varchar(100) COLLATE Chinese_PRC_CI_AS NOT NULL, [data_body] text COLLATE Chinese_PRC_CI_AS NOT NULL, [version] int NOT NULL, [create_time] bigint NOT NULL, [update_time] bigint NOT NULL, CONSTRAINT [PK__sync_obj__3213E83F46CC8028] PRIMARY KEY CLUSTERED ([id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY] )
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO

ALTER TABLE [dbo].[sync_obj_record] SET (LOCK_ESCALATION = TABLE) GO

CREATE UNIQUE NONCLUSTERED INDEX [sync_obj_record_u1] ON [dbo].[sync_obj_record] ( [obj_name] ASC, [data_id] ASC ) ```

``` – MySQL CREATE TABLE sync_obj_record ( id INT NOT NULL, obj_name VARCHAR(255) NOT NULL, md_str VARCHAR(255) NOT NULL, data_id VARCHAR(255) NOT NULL, data_body TEXT NOT NULL, version INT NOT NULL, create_time BIGINT NOT NULL, update_time BIGINT NOT NULL, PRIMARY KEY (id) );

CREATE UNIQUE INDEX sync_obj_record_obj_name_idx ON sync_obj_record (obj_name, data_id); ```

IV. Configuration Functionality

1. Single Record Query

image

(1) Primary Object

image

Query primary data ID (primary key value): “123”
Uses: 1 (Primary Key Field) and 4 (Batch Query SQL)
Automatically constructs query condition: [primary_key_field=’123’]
With placeholder: Generates [select * from customer where 1=1 and (id='123') order by create_time]
Without placeholder: Appends condition to query SQL

(2) Sub-object

image

For sub-objects: Retrieves primary key value (‘123’) from primary data, replaces [‘#pid’] in sub-object’s batch query SQL to generate:
[select * from customer_detail where 1=1 and pid='123']

2. Batch Query

image

(1) Primary Object

image

Query time range: Start 1657092910000, End 1657093270000
Uses: 2 (Query Time Field), 3 (Date Format), 4 (Batch Query SQL)
Converts timestamps to: [Start: 2022-07-06 15:35:10, End: 2022-07-06 15:41:10]
Constructs condition:
[update_time>'2022-07-06 15:35:10' and update_time<='2022-07-06 15:41:10' or create_time>'2022-07-06 15:35:10' and create_time<='2022-07-06 15:41:10']
Generates final query (placeholder handling same as single query)

(2) Sub-object

Process identical to single record query - replaces #pid with primary key values

3. Data Creation

image

(1) Primary Object - Insert

image

Data body:
{"masterFieldVal":{"customer_code":"code1"},"detailFieldVals":{"d1obj":[{"customer_detail_code":"detailCode1"}]}}
Uses: 5 (Primary Key Query SQL), 6 (Insert SQL)
Replaces placeholders in insert SQL:
[INSERT INTO customer(customer_code,customer_name) VALUES ( 'code1', null)]

(2) Primary Object - Primary Key Query

image

Generates:
[select id from customer where 1=1 and customer_code='code1']

(3) Sub-object - Insert

image

Generates preliminary SQL:
[INSERT INTO customer_detail (pid,customer_detail_code,customer_detail_name) VALUES ( '#pid', 'detailCode1', null)]

(4) Sub-object - Primary Key Query

image

Generates preliminary SQL:
[select detailId from customer_detail where 1=1 and pid='#pid' and customer_detail_code='detailCode1']

(5) Execution Flow (Non-transactional)

  1. Execute primary insert

  2. Execute primary key query (e.g., returns “1234”)

  3. Replace #pid in sub-object SQLs with “1234”

  4. Execute sub-object insert

  5. Execute sub-object key query

4. Data Update

image

(1) Primary Object - Update

image

Data body:
{"masterFieldVal":{"customer_code":"code1","id":"1234"},"detailFieldVals":{"d1obj":[{"customer_detail_code":"detailCode1","detailId":"12345"}]}}
Uses: 7 (Update SQL)
Generates:
[UPDATE customer SET customer_code = 'code1' WHERE id = '1234']

(2) Sub-object - Update

image

Generates:
[UPDATE customer_detail SET customer_detail_code = 'detailCode1' WHERE detailId = '12345']
Note: New detail records follow creation flow. Deletion not supported.

V. New Version Features

New connectors require agent service v1.1+.

1. Agent Service v1.1

(1) New Features

  • Multi-database support: MySQL, PostgreSQL, Oracle, SQLServer

  • External JDBC driver support (e.g., DM8)

  • Simplified configuration format

  • New service info APIs

  • Multi-SQL query execution support

  • Enhanced error handling

  • Dependency upgrades (security patches)

  • Druid statistics enabled

  • Full environment compatibility

(2) Deployment Guide

Attachments include v1.0 and v1.1 agent JARs

Configuration files:
Two types:
1. Spring config (application-prod.yml in JAR directory):
# application-prod.yml setting: # Directory path (relative/absolute). Default: ./setting dir: D:\project\DBMidProxyServer\setting server: servlet: context-path: /dev

  1. Hutool config (requires db.setting and user.setting in setting.dir):
    # user.setting # Security config userName=admin password=1234qwer

``` #db.setting (Druid config) [db1] url=jdbc:postgresql://localhost:5432/db1?ssl=false username=postgres password=1234qwer queryTimeout=30 initialSize=5 maxActive=20 testWhileIdle=true

[db2] url=jdbc:postgresql://localhost:5432/db2?ssl=false username=postgres password=1234qwer queryTimeout=30 initialSize=5 maxActive=20 testWhileIdle=true

[db3] # mysql url=jdbc:mysql://localhost:3306/db3?useSSL=false&characterEncoding=UTF-8&connectionTimeZone=Asia/Shanghai username=root password=1234qwer queryTimeout=30 initialSize=5 maxActive=20 testWhileIdle=true

[db4] # sqlserver url=jdbc:sqlserver://;serverName=localhost;databaseName=db4;encrypt=true;trustServerCertificate=true username=sa password=8kn0ye66JxO5 queryTimeout=30 initialSize=5 maxActive=20 testWhileIdle=true

[db5] # oracle url=jdbc:oracle:thin:@127.0.0.1:1521:XE username=dbproxy password=1234qwer queryTimeout=30 initialSize=5 maxActive=20 testWhileIdle=true

[dm8] # DM Database (requires separate driver) url=jdbc:dm://localhost:5236?LobMode=1 username=SYSDBA password=SYSDBA001 queryTimeout=30 initialSize=5 maxActive=20 testWhileIdle=true driverJarPath=D:\driver\db-driver\DmJdbcDriver18.jar ```

Run command:
java -jar DBMidProxyServer.jar

2. Integration Platform Configuration

Requires v1.1+ agent service. Legacy config remains functional.

(1) Connection Info

New agent services expose version, data source info, and DB type via API.
image

(2) Query SQL

New offset-free pagination method.
Deep Dive: Pagination Query Optimization
image

Requirements: - Unique date query column
- SQL must select primary key and date fields
- Requires proper indexing (critical for large tables)

Benefits: - Guarantees no data omission unless timestamps are backdated
- No deep pagination with proper indexing

Example:
![image](https://saas.bk-cdn01.com

Submit Feedback
Next
DB Connector 2.x Operations Manual