Skip to content

JDBC Repository Connector

Piergiorgio Lucidi edited this page Oct 19, 2026 · 2 revisions

Relational Database (JDBC) Repository Connector

JDBC 4.2+ Java Spring Boot HikariCP OIS Compliance Claim Check

Module: oc-jdbc-repository-connector
Connector Class: org.opencrawling.jdbc.JdbcRepositoryConnector
Security Mapper: org.opencrawling.jdbc.JdbcSecurityMapper
Crawl Mode Enum: org.opencrawling.jdbc.JdbcCrawlMode
Spring Bean: @Component (activated when spring.opencrawling.connector.type=jdbc or CONNECTOR_TYPE=jdbc)


📌 Overview

The Relational Database (JDBC) Repository Connector provides OpenCrawling with native, high-throughput, enterprise-grade ingestion and security mapping from any JDBC-compliant Relational Database Management System (RDBMS).

Relational databases—such as PostgreSQL, MySQL, MariaDB, Oracle Database, Microsoft SQL Server, IBM DB2, SQLite, DuckDB, H2, and cloud warehouses like Snowflake—house the overwhelming majority of structured and semi-structured enterprise knowledge: ERP entities, CRM cases, support tickets, product catalogs, customer transaction logs, and binary file attachments stored directly within database columns.

By bridging enterprise SQL databases with OpenCrawling's Open Ingestion Standard (OIS), Java 25 Virtual Threads (StructuredTaskScope), Schema Introspection SPI (ConnectorSchema), Spring AI Narrativization Copilot, decoupled Claim-Check streaming, and the Secure Model Context Protocol (MCP) Server, this connector transforms relational data and binary database records into semantically rich, vector-searchable knowledge while strictly enforcing column-based Access Control Lists (ACLs), tenant isolation, and soft-delete change data capture (CDC).

flowchart TD
    subgraph RDBMSSources["Enterprise RDBMS Sources"]
        PG[("PostgreSQL")]
        MySQL[("MySQL / MariaDB")]
        Oracle[("Oracle Database")]
        MSSQL[("Microsoft SQL Server")]
        DB2[("IBM DB2 / SQLite / DuckDB / Snowflake")]
        
        Tables["Tables, Views & Relational Schemas"]
        BLOBs["BLOB / CLOB Binary Columns"]
        HWM["Timestamp / Numeric HWM Columns"]
        
        PG --> Tables
        MySQL --> Tables
        Oracle --> Tables
        MSSQL --> Tables
        DB2 --> Tables
        
        Tables --> BLOBs
        Tables --> HWM
    end

    subgraph Runtime["OpenCrawling Ingestion Runtime (Java 25)"]
        Pool["HikariCP Connection Pool"]
        Connector["JdbcRepositoryConnector - oc-jdbc-repository-connector"]
        Tables <-->|JDBC 4.2+ / Streaming Cursor / fetchSize| Pool
        Pool --> Connector
        
        Connector -.->|getSchema basePath| Copilot["TemplateGenerationCopilot / Spring AI"]
        Copilot -.->|Auto-Generated Mustache Template| Transform["Mustache Narrativization Engine"]

        VThreads["Virtual Threads - StructuredTaskScope.open"]
        Connector --> VThreads
        
        VThreads --> SecMap["JdbcSecurityMapper: User/Group Columns & Tenant SIDs"]
        VThreads --> DualPath{"Record Type?"}
        
        DualPath -->|Tabular Row| DocBuilderTabular["Tabular RAG: Mustache Template or Markdown"]
        DualPath -->|Binary BLOB / CLOB| DocBuilderBlob["BLOB Stream: Magic Byte Sniffing & Claim-Check"]
        
        SecMap --> DocBuilderTabular
        SecMap --> DocBuilderBlob

        DocBuilderTabular -->|OIS Document UPSERT / DELETE| Orchestrator["JobOrchestrator"]
        DocBuilderBlob -->|OIS Document UPSERT| Orchestrator
        
        DocBuilderBlob -.->|doc.contentStream| ClaimCheck["ClaimCheckStore: Local / Ozone / S3"]
        
        Orchestrator -->|Kafka IngestionMessage| IngestionConsumer["IngestionConsumer"]
        IngestionConsumer -->|Read Claim-Check| Tika["Apache Tika Extraction"]
        IngestionConsumer --> Chunk["TokenTextSplitter"]
        Chunk --> Embed["Embedding Generator - Ollama / OpenAI"]
        
        Embed --> Sink1[("PostgreSQL + pgvector")]
        Embed --> Sink2[("Milvus / Qdrant / Vespa")]
        Embed --> Sink3[("OpenSearch 2/3 / Solr / Luxir")]
        Embed --> Sink4[("Apache Iceberg / SeaTunnel")]
        Embed --> MCP["Secure MCP Server - Zero-Trust Enforcement"]
    end

    subgraph AIApps["AI Applications & LLM Agents"]
        MCP -->|Filtered Context with Database Security SIDs| Agent["Enterprise RAG Assistant / Agent"]
    end
Loading

🚀 Key Features

  • Universal RDBMS & Driver Compatibility:
    • Operates across all major enterprise SQL engines via standard JDBC 4.2+ drivers: PostgreSQL, MySQL, MariaDB, Oracle Database, Microsoft SQL Server, IBM DB2, SQLite, DuckDB, H2, and Snowflake.
    • Auto-configures engine connection properties with built-in presets in the Admin UI and dynamic driver class loading.
  • Dual Ingestion Strategies:
    • Table / View Ingestion Mode (TABLE): Ingest entire relational tables or database views with automated column projection, primary key discovery, and schema introspection.
    • Custom SQL Query Mode (QUERY): Ingest arbitrary multi-table SQL queries with JOIN, aggregation, and calculated expressions to construct composite domain objects.
  • Structured Concurrency & Java 25 Virtual Threads:
    • Utilizes Java 25 StructuredTaskScope and ObservabilityTask for windowed concurrent processing.
    • Snapshots JDBC ResultSet records synchronously to guarantee thread safety across single-threaded drivers, then evaluates ACLs, tabular templates, and metadata enrichment concurrently across lightweight virtual threads.
  • Multi-Table Concurrent Scanning:
    • Supports scanning multiple tables concurrently by supplying a comma-separated list of table names (e.g. customers,orders,invoices). Each table is partitioned into a dedicated StructuredTaskScope.Subtask executed in parallel.
  • Deep Integration with Auto-Narrativization Copilot & Tabular RAG:
    • Implements RepositoryConnector.getSchema(basePath) using DatabaseMetaData.getColumns(...) to return schema fields, SQL data types, and column remarks.
    • Connects directly with the OpenCrawling Admin UI and Spring AI TemplateGenerationCopilot to auto-generate natural-language Mustache templates.
    • Case-insensitive Mustache variable resolution (supporting {{column_name}}, {{COLUMN_NAME}}, and {{Column_Name}}).
    • Deterministic Markdown key-value fallback when narrativization is disabled.
  • Dual-Path BLOB / CLOB Streaming & Automatic MIME Sniffing:
    • Automatically detects binary columns (BLOB, BYTEA, RAW, LONGVARBINARY) or maps configured columns (blob-column-name).
    • Employs magic byte sniffing to detect file formats (application/pdf, image/png, image/jpeg, application/zip, image/gif, image/webp).
    • Offloads binary payloads directly into the active ClaimCheckStore (Local, Apache Ozone, AWS S3) via RepositoryDocument.contentStream() without heap exhaustion, routing them through Apache Tika for parsing and text extraction.
    • Injects descriptive fallback metadata when images lack OCR extraction, allowing dense vector search to discover visual assets.
  • Incremental Crawling & High-Water Mark (HWM):
    • Supports stateful delta crawls using High-Water Mark columns:
      • Timestamp HWM: updated_at >= :lastCrawledTime
      • Numeric HWM: id > :lastCrawledId
  • Soft-Delete & Tombstone Generation (DocumentAction.DELETE):
    • Detects soft-delete status columns (e.g., is_deleted = TRUE or status = 'PURGED').
    • Emits OIS deletion tombstones (RepositoryDocument.createTombstone(...)) to automatically purge stale embeddings and outdated knowledge from downstream vector stores.
  • Zero-Trust Security & Row-Level Authorization (ACL) Mapping:
    • Employs JdbcSecurityMapper to map user columns (owner_id, created_by), group columns (department_id, role_group), and tenant IDs into SecurityConfig and PermissionRule.
    • Integrates with the Secure MCP Server to guarantee zero-leakage, identity-filtered RAG inference.
  • Engine-Specific Streaming Optimizations:
    • PostgreSQL: Automatically configures connection.setAutoCommit(false) and fetchSize to invoke server-side cursors and prevent client heap exhaustion.
    • MySQL: Automatically sets streaming cursor fetch (statement.setFetchSize(Integer.MIN_VALUE)).
    • Oracle & SQL Server: Applies forward-only cursor windowing (statement.setFetchSize(1000)).
  • Enterprise Connection Pooling with HikariCP:
    • Fully manages pool lifecycle (maximumPoolSize, minimumIdle, connectionTimeoutMs, idleTimeoutMs, maxLifetimeMs) with automated ping health checks via Connection.isValid(5).

⚙️ Configuration Properties

Configure the JDBC Repository Connector via application.yml or standard environment variables:

Property Key Spring Property Environment Variable Default Description
url spring.opencrawling.connector.jdbc.url JDBC_URL jdbc:h2:mem:opencrawling;DB_CLOSE_DELAY=-1 JDBC connection URL (PostgreSQL, MySQL, Oracle, etc.)
driverClassName spring.opencrawling.connector.jdbc.driver-class-name JDBC_DRIVER_CLASS_NAME "" (auto-detected) Fully qualified JDBC driver class name
username spring.opencrawling.connector.jdbc.username JDBC_USERNAME sa Database authentication username
password spring.opencrawling.connector.jdbc.password JDBC_PASSWORD "" Database authentication password
crawlMode spring.opencrawling.connector.jdbc.mode JDBC_MODE table Ingestion mode: table (table/view crawl) or query (custom SQL)
tableName spring.opencrawling.connector.jdbc.table-name JDBC_TABLE_NAME "" Name of the table or view to crawl in table mode
schemaName spring.opencrawling.connector.jdbc.schema-name JDBC_SCHEMA_NAME "" Optional database schema qualifier (e.g., public, crm)
primaryKeyColumns spring.opencrawling.connector.jdbc.primary-key-columns JDBC_PRIMARY_KEY_COLUMNS id Comma-separated column name(s) forming the document primary key
titleColumn spring.opencrawling.connector.jdbc.title-column JDBC_TITLE_COLUMN title Column used as document title/name
excludedColumns spring.opencrawling.connector.jdbc.excluded-columns JDBC_EXCLUDED_COLUMNS "" Comma-separated columns to exclude from indexing (e.g. passwords)
querySql spring.opencrawling.connector.jdbc.query-sql JDBC_QUERY_SQL "" Custom SQL query string executed when crawlMode=query
blobColumnName spring.opencrawling.connector.jdbc.blob-column-name JDBC_BLOB_COLUMN_NAME "" Column containing binary file data (auto-detected if blank)
fileNameColumn spring.opencrawling.connector.jdbc.file-name-column JDBC_FILE_NAME_COLUMN "" Column specifying the attachment filename (e.g. attachment.pdf)
mimeTypeColumn spring.opencrawling.connector.jdbc.mime-type-column JDBC_MIME_TYPE_COLUMN "" Column specifying the MIME type (e.g. application/pdf)
incrementalEnabled spring.opencrawling.connector.jdbc.incremental-enabled JDBC_INCREMENTAL_ENABLED false Enable High-Water Mark stateful delta crawling
hwmColumn spring.opencrawling.connector.jdbc.hwm-column JDBC_HWM_COLUMN updated_at Column name used for High-Water Mark comparison
hwmType spring.opencrawling.connector.jdbc.hwm-type JDBC_HWM_TYPE timestamp HWM data type: timestamp or numeric
softDeleteEnabled spring.opencrawling.connector.jdbc.soft-delete-enabled JDBC_SOFT_DELETE_ENABLED false Enable soft-delete tombstone emission
softDeleteColumn spring.opencrawling.connector.jdbc.soft-delete-column JDBC_SOFT_DELETE_COLUMN is_deleted Column indicating record deletion status
softDeleteValue spring.opencrawling.connector.jdbc.soft-delete-value JDBC_SOFT_DELETE_VALUE true Value matching a soft-deleted record (e.g. true, PURGED, 1)
securityEnabled spring.opencrawling.connector.jdbc.security-enabled JDBC_SECURITY_ENABLED false Extract and map row security columns to OIS ACLs
userColumns spring.opencrawling.connector.jdbc.user-columns JDBC_USER_COLUMNS "" Comma-separated user identity columns (e.g. owner_id,assignee)
groupColumns spring.opencrawling.connector.jdbc.group-columns JDBC_GROUP_COLUMNS "" Comma-separated group/role columns (e.g. department_id,role)
tenantColumn spring.opencrawling.connector.jdbc.tenant-column JDBC_TENANT_COLUMN "" Column containing tenant identifier for multi-tenant isolation
defaultPermission spring.opencrawling.connector.jdbc.default-permission JDBC_DEFAULT_PERMISSION read Default permission assigned to mapped identities
fetchSize spring.opencrawling.connector.jdbc.fetch-size JDBC_FETCH_SIZE 1000 Server-side JDBC cursor fetch batch size
batchSize spring.opencrawling.connector.jdbc.batch-size JDBC_BATCH_SIZE 100 Structured concurrency batch window size
maxPoolSize spring.opencrawling.connector.jdbc.max-pool-size JDBC_MAX_POOL_SIZE 10 Maximum HikariCP pool connections
minIdle spring.opencrawling.connector.jdbc.min-idle JDBC_MIN_IDLE 2 Minimum HikariCP idle connections
connectionTimeoutMs spring.opencrawling.connector.jdbc.connection-timeout-ms JDBC_CONNECTION_TIMEOUT_MS 30000 HikariCP connection timeout in milliseconds
concurrencyLimit spring.opencrawling.connector.jdbc.concurrency-limit JDBC_CONCURRENCY_LIMIT 64 Concurrency limit for virtual thread forks
multiTableParallelEnabled spring.opencrawling.connector.jdbc.multi-table-parallel-enabled JDBC_MULTI_TABLE_PARALLEL_ENABLED true Concurrently scan comma-separated tables
narrativizationTemplate spring.opencrawling.connector.jdbc.narrativization-template JDBC_NARRATIVIZATION_TEMPLATE "" Optional Mustache template for tabular narrative text generation

📂 SCAN_PATH Parameter Mapping

When initiating crawls via the OpenCrawling Admin UI, REST API, or CLI, the SCAN_PATH parameter dynamically overrides the crawl target:

SCAN_PATH Value Behaviour
support_tickets Crawls the specified table in TABLE mode
crm.customers Crawls table customers within schema crm
customers,orders,invoices Scans all three tables concurrently in parallel via StructuredTaskScope
SELECT * FROM support_tickets WHERE status = 'OPEN' Detected as SQL query (starts with SELECT): automatically executes in QUERY mode
(empty) Uses configured tableName or querySql from connector properties

📋 Document Metadata & Schema Mapping

Each relational record crawled by the JDBC connector is converted into an OIS RepositoryDocument following standard conventions:

Database Column / Expression OIS Field Description
Primary Key column(s) id Unique identifier (e.g. 10482 or composite dept_10:user_4)
titleColumn title and name Human-readable document display title
tableName / target metadata.table_name Source relational table or query name
Primary Key metadata.primary_key Primary key value
updated_at / timestamp column lastModified ISO-8601 instant for incremental crawls and cache invalidation
Binary BLOB stream contentStream Direct stream to Claim-Check store; sets metadata.is_blob=true
Tabular text (Markdown / Mustache) contentStream Natural language text narrative; sets metadata.is_tabular=true
Sniffed or mapped MIME type metadata.mimeType e.g. application/pdf, image/png, text/markdown
Image flag metadata.is_image Set to true when binary matches image magic bytes (PNG, JPEG, GIF, WEBP)
Soft-delete match action DocumentAction.DELETE (tombstone) or DocumentAction.UPSERT
User and group columns security.permissions List of PermissionRule entries (identity, type, permission)
User and group columns security.acl Legacy semicolon-delimited token string (user:alice;group:support)
tenantColumn metadata.tenant_id Stamped tenant identifier for multi-tenant isolation

🎛️ Admin UI Configuration (oc-admin-ui)

The JDBC Repository Connector is fully integrated into the OpenCrawling Admin UI:

  1. Navigate to Connectors $\rightarrow$ New Connector $\rightarrow$ select Relational Database (JDBC).
  2. Select your target engine from the Database Engine Preset dropdown:
    • PostgreSQL: Pre-fills org.postgresql.Driver and jdbc:postgresql://localhost:5432/opencrawling.
    • MySQL / MariaDB: Pre-fills com.mysql.cj.jdbc.Driver and jdbc:mysql://localhost:3306/opencrawling.
    • Oracle Database: Pre-fills oracle.jdbc.OracleDriver and jdbc:oracle:thin:@localhost:1521:xe.
    • Microsoft SQL Server: Pre-fills com.microsoft.sqlserver.jdbc.SQLServerDriver and jdbc:sqlserver://localhost:1433;databaseName=opencrawling.
    • SQLite: Pre-fills org.sqlite.JDBC and jdbc:sqlite:/tmp/opencrawling.db.
    • H2: Pre-fills org.h2.Driver and jdbc:h2:mem:opencrawling;DB_CLOSE_DELAY=-1.
    • Custom JDBC Driver: Lets you enter any third-party driver class and connection URL.
  3. Configure authentication credentials, crawl mode (Table vs Query), table name or SQL editor, and cursor fetch size.
  4. Optionally configure Security & ACL Column Mappings (User columns, Group columns, Tenant column).
  5. Click "Test Connection" to immediately verify database reachability and driver health via ConnectorCheckerService.checkJdbc(...).

🐳 Standalone & Headless Docker Compose Deployment

To execute a headless, automated crawling task targeting a PostgreSQL database, use the pre-configured compose definition:

# docker-compose-decoupled-with-jdbc.yml
services:
  oc-crawler-jdbc:
    build:
      context: .
      dockerfile: docker/Dockerfile.crawler
    container_name: oc-crawler-jdbc
    environment:
      - SPRING_KAFKA_BOOTSTRAP_SERVERS=kafka:9094
      - SPRING_OPENCRAWLING_CRAWL_ON_STARTUP=true
      - SPRING_OPENCRAWLING_REPOSITORY_CONNECTOR_TYPE=jdbc
      - SPRING_OPENCRAWLING_SCAN_PATH=support_tickets
      - SPRING_OPENCRAWLING_CONNECTOR_JDBC_URL=jdbc:postgresql://postgres-source:5432/enterprise_crm
      - SPRING_OPENCRAWLING_CONNECTOR_JDBC_USERNAME=crm_reader
      - SPRING_OPENCRAWLING_CONNECTOR_JDBC_PASSWORD=crm_password
      - SPRING_OPENCRAWLING_CONNECTOR_JDBC_DRIVER_CLASS_NAME=org.postgresql.Driver
      - SPRING_OPENCRAWLING_CONNECTOR_JDBC_TABLE_NAME=support_tickets
      - SPRING_OPENCRAWLING_CONNECTOR_JDBC_PRIMARY_KEY_COLUMNS=id
      - SPRING_OPENCRAWLING_CONNECTOR_JDBC_TITLE_COLUMN=title
      - SPRING_OPENCRAWLING_CONNECTOR_JDBC_SECURITY_ENABLED=true
      - SPRING_OPENCRAWLING_CONNECTOR_JDBC_USER_COLUMNS=owner_id
      - SPRING_OPENCRAWLING_CONNECTOR_JDBC_GROUP_COLUMNS=department_id
      - SPRING_OPENCRAWLING_CONNECTOR_JDBC_SOFT_DELETE_ENABLED=true
      - SPRING_OPENCRAWLING_CONNECTOR_JDBC_SOFT_DELETE_COLUMN=is_deleted
      - SPRING_OPENCRAWLING_CONNECTOR_JDBC_SOFT_DELETE_VALUE=true
      - SPRING_OPENCRAWLING_TRANSFORMATION_CONNECTOR=Ollama local
    volumes:
      - ./oc-runtime/data:/data
    networks:
      - opencrawling-network

🧪 Integration Testing & Verification

OpenCrawling provides two automated bash integration test suites for the JDBC Repository Connector:

1. Standalone Connector Test (scripts/test-jdbc-connector.sh)

Validates database connectivity, schema introspection, tabular RAG, BLOB streaming, soft-delete tombstones, and security ACL mapping across in-memory H2 and an ephemeral PostgreSQL Testcontainer:

# From the repository root
chmod +x scripts/test-jdbc-connector.sh
./scripts/test-jdbc-connector.sh

2. Decoupled Pipeline Test (scripts/test-jdbc-decoupled.sh)

Validates the complete end-to-end decoupled pipeline: $$\text{PostgreSQL Source DB} \longrightarrow \text{oc-crawler (JDBC)} \longrightarrow \text{Kafka} \longrightarrow \text{Ingestion Consumer} \longrightarrow \text{Ollama Embeddings} \longrightarrow \text{pgvector} \longrightarrow \text{MCP Server}$$

# From the repository root
chmod +x scripts/test-jdbc-decoupled.sh
./scripts/test-jdbc-decoupled.sh

The script automatically verifies:

  1. PostgreSQL source database seeding with structured support tickets, soft-deleted records, and binary attachment BLOBs.
  2. Ingestion into Kafka topic opencrawling-raw-documents.
  3. Ingestion consumer text extraction, chunking, and Claim Check offloading.
  4. Generation of vector embeddings via Ollama mxbai-embed-large.
  5. Persistence of chunk embeddings and security ACL tokens into vector_store_1024.
  6. Successful MCP Server zero-trust authorization queries.

🗺️ Documentation


🧪 Testing


🤝 Community

Clone this wiki locally