> ## Documentation Index > Fetch the complete documentation index at: https://doc.lucidworks.com/llms.txt > Use this file to discover all available pages before exploring further. # JDBC V1 > The JDBC connector fetches documents from a relational database via SQL queries. export const schema = { "type": "object", "title": "JDBC", "description": "Connector for any JDBC database. This connector requires a JDBC driver to be loaded before creating the datasource.", "required": ["id", "connector", "type", "pipeline", "properties"], "properties": { "category": { "type": "string", "title": "Category", "default": "Database", "hints": ["hidden", "readonly"] }, "id": { "type": "string", "title": "Datasource ID", "description": "Unique name for this datasource.", "minLength": 1, "pattern": "^[a-zA-Z0-9_-]+$" }, "connector": { "type": "string", "title": "Connector Type", "description": "Connector Type.", "hints": ["hidden"], "minLength": 1 }, "type": { "type": "string", "title": "Datasource Type", "description": "Datasource type supported by the selected connector type.", "hints": ["hidden"], "minLength": 1 }, "pipeline": { "type": "string", "title": "Pipeline ID", "description": "Name of an existing index pipeline for processing documents.", "minLength": 1 }, "description": { "type": "string", "title": "Description", "description": "Optional description for this datasource." }, "type_description": { "type": "string", "title": "Type Description", "default": "Connector for any JDBC database. This connector requires a JDBC driver to be loaded before creating the datasource.", "hints": ["hidden", "readonly"] }, "properties": { "type": "object", "title": "Properties", "description": "Datasource configuration properties", "required": ["url", "sql_select_statement"], "properties": { "collection": { "type": "string", "title": "Collection", "description": "Collection documents will be indexed to.", "hints": ["hidden"], "pattern": "^[a-zA-Z0-9_-]+$" }, "db": { "type": "object", "title": "Connector DB", "description": "Type and properties for a ConnectorDB implementation to use with this datasource.", "required": ["type"], "properties": { "type": { "type": "string", "title": "Implementation Class Name", "description": "Fully qualified class name of ConnectorDb implementation.", "default": "com.lucidworks.connectors.db.impl.MapDbConnectorDb", "minLength": 1 }, "inlinks": { "type": "boolean", "title": "Process Inlinks?", "description": "Keep track of incoming links. This negatively impacts performance and size of DB.", "default": false }, "aliases": { "type": "boolean", "title": "Process Aliases?", "description": "Keep track of original URI-s that resolved to the current URI. This negatively impacts performance and size of DB.", "default": false }, "inv_aliases": { "type": "boolean", "title": "Process Inverted Aliases?", "description": "Keep track of target URI-s that the current URI resolves to. This negatively impacts performance and size of DB.", "default": false } }, "hints": ["hidden"] }, "url": { "type": "string", "title": "URL", "description": "A URL to the database, starting with jdbc and vendor name, e.g., 'jdbc:mysql://localhost/test'", "minLength": 1 }, "driver": { "type": "string", "title": "JDBC Driver", "description": "The class name of the JDBC driver to use to connect to the database. Only JDBC4 drivers will appear in this list. If a JDBC3 driver has been uploaded but does not appear in the list, it may be possible to manually enter the class name." }, "manually_uploaded_driver": { "type": "string", "title": "Manually Uploaded JDBC Driver", "description": "If you for whatever reason cannot use the blob store to upload your JDBC driver and needed to load it manually into the connectors apps/libs directory, you can specify the JDBC Driver class name here." }, "username": { "type": "string", "title": "Username", "description": "The username of a database account used for authentication and data access." }, "password": { "type": "string", "title": "Password", "description": "The password of the account used for authentication and data access.", "hints": ["secret"] }, "sql_select_statement": { "type": "string", "title": "SQL Statement", "description": "A SQL SELECT statement to choose the records to be retrieved.", "hints": ["lengthy"] }, "primary_key": { "type": "string", "title": "Primary Key", "description": "The column name of the primary key for the table." }, "nested_queries": { "type": "array", "title": "Nested queries", "description": "A nested query to join data from multiple tables. The nested query will be used with the SQL Statement and must include the primary key with the $ character. For example, 'SELECT tag FROM tag WHERE document_tag.doc_id=$'.", "hints": ["lengthy"], "items": { "type": "string" } }, "clean_in_full_import_mode": { "type": "boolean", "title": "Clean in full import mode", "description": "Clean old records when doing a full import. This will remove all records from the index before re-indexing.", "default": true }, "convert_type": { "type": "boolean", "title": "Convert type", "description": "If checked, the connector will automatically convert the data from a column to the target Solr data-type. Otherwise, the connector will use the field type based on the data type of the column from the JDBC result set.", "default": true }, "fetch_size": { "type": "integer", "title": "JDBC fetch size", "description": "The number of documents to retrieve per batch." }, "max_docs": { "type": "integer", "title": "Max Documents", "description": "The maximum number of documents to crawl. Use -1 to index all documents found.", "default": -1 }, "delta_sql_query": { "type": "string", "title": "Delta SQL Query", "description": "A SQL statement to select the delta (records added or changed) since the last run of the datasource. For example, 'SELECT id FROM customers WHERE last_modified > $'. The $ indicates the date and time of the last import.", "hints": ["code", "code/sql"] }, "delta_import_query": { "type": "string", "title": "Delta Import SQL Query", "description": "A SQL statement to retrieve the delta records. If not defined, the original SQL Statement will be used to retrieve delta records, which may be error prone in the case of complex statements. If that occurs, you can define a secondary SQL statement that will only be used for delta imports.", "hints": ["code", "code/sql"] }, "kerberosAuthenticationProps": { "type": "object", "title": "Kerberos Authentication", "description": "Kerberos Authentication Properties", "properties": { "kerberosServerPrincipal": { "type": "string", "title": "Kerberos Server Principal", "description": "Kerberos Server Principal" } } }, "commit_on_finish": { "type": "boolean", "title": "Solr commit on finish", "description": "Set to true for a request to be sent to Solr after the last batch has been fetched to commit the documents to the index.", "default": true, "hints": ["advanced"] }, "verify_access": { "type": "boolean", "title": "Validate access", "description": "Set to true to require successful connection to the filesystem before saving this datasource.", "default": true, "hints": ["advanced"] }, "initial_mapping": { "type": "object", "title": "Initial field mapping", "description": "Provides mapping of fields before documents are sent to an index pipeline.", "properties": { "skip": { "type": "boolean", "title": "Skip This Stage", "description": "Set to true to skip this stage.", "default": false, "hints": ["advanced"] }, "label": { "type": "string", "title": "Label", "description": "A unique label for this stage.", "hints": ["advanced"], "maxLength": 255 }, "condition": { "type": "string", "title": "Condition", "description": "Define a conditional script that must result in true or false. This can be used to determine if the stage should process or not.", "hints": ["code", "code/javascript", "advanced"] }, "reservedFieldsMappingAllowed": { "type": "boolean", "title": "Allow System Fields Mapping?", "default": false, "hints": ["advanced"] }, "retentionMappings": { "type": "array", "title": "Field Retention", "description": "Fields that should be kept or deleted", "hints": ["advanced"], "items": { "type": "object", "required": ["field"], "properties": { "field": { "type": "string", "title": "Field", "description": "The name of the field to operate on.", "hints": ["advanced"] }, "operation": { "type": "string", "title": "Operation", "description": "The type of operation to perform: keep (default) or delete", "enum": ["keep", "delete"], "default": "keep", "hints": ["advanced"] } } } }, "updateMappings": { "type": "array", "title": "Field Value Updates", "description": "Values that should be added to or set on a field. When a value is added, any values previously on the field will be retained. When a value is set, any values previously on the field will be overwritten.", "hints": ["advanced"], "items": { "type": "object", "required": ["field", "value"], "properties": { "field": { "type": "string", "title": "Field", "description": "The name of the field to operate on.", "hints": ["advanced"] }, "value": { "type": "string", "title": "Value", "description": "The value to add to or set on the field.", "hints": ["advanced"] }, "operation": { "type": "string", "title": "Operation", "description": "The type of operation to perform: add (default) or set.", "enum": ["add", "set"], "default": "add", "hints": ["advanced"] } } } }, "translationMappings": { "type": "array", "title": "Field Translations", "description": "Fields that should be moved or copied to another field. When a field is moved, the values from the source field are moved over to the target field and the source field is removed. When a field is copied, the values from the source field are copied over to the target field and the source field is retained.", "hints": ["advanced"], "items": { "type": "object", "required": ["source", "target"], "properties": { "source": { "type": "string", "title": "Source Field", "description": "The name of the field to operate on.", "hints": ["advanced"] }, "target": { "type": "string", "title": "Target Field", "description": "The name of the target field.", "hints": ["advanced"] }, "operation": { "type": "string", "title": "Operation", "description": "The type of operation to perform: copy (default) or move.", "enum": ["copy", "move"], "default": "copy", "hints": ["advanced"] } } } }, "unmappedRule": { "type": "object", "title": "Unmapped Fields", "description": "Fields not mapped by the above rules. By default, any remaining fields will be kept on the document.", "properties": { "keep": { "type": "boolean", "title": "Keep", "description": "Keep all unmapped fields", "default": true, "hints": ["advanced"] }, "delete": { "type": "boolean", "title": "Delete", "description": "Delete all unmapped fields", "default": false, "hints": ["advanced"] }, "fieldToMoveValuesTo": { "type": "string", "title": "Move", "description": "Move all unmapped field values to this field", "hints": ["advanced"] }, "fieldToCopyValuesTo": { "type": "string", "title": "Copy", "description": "Copy all unmapped field values to this field", "hints": ["advanced"] }, "valueToAddToUnmappedFields": { "type": "string", "title": "Add", "description": "Add this value to all unmapped fields", "hints": ["advanced"] }, "valueToSetOnUnmappedFields": { "type": "string", "title": "Set", "description": "Set this value on all unmapped fields", "hints": ["advanced"] } } } }, "category": "Field Transformation", "categoryPriority": 7, "hints": ["advanced"], "unsafe": false } }, "propertyGroups": [{ "label": "Data Selection", "properties": ["sql_select_statement", "primary_key", "nested_queries", "clean_in_full_import_mode", "fetch_size", "convert_type", "max_docs"] }, { "label": "Delta Queries", "properties": ["delta_sql_query", "delta_import_query"] }, { "label": "Field Mapping", "properties": ["initial_mapping"] }] } }, "category": "Other", "categoryPriority": 1, "unsafe": false }; export const SchemaParamFields = ({schema}) => { const sanitize = str => { if (typeof str !== "string") return str; return str.replace(/^"(.*)"$/s, "$1").replace(/\\/g, "").replace(/"/g, "'"); }; const formatDescription = str => { const s = sanitize(str); return (/[.!?]\)*$/).test(s) ? s : `${s}.`; }; const {description, properties = {}, required: requiredProps = []} = schema; const visibleProps = useMemo(() => Object.entries(properties).filter(([, prop]) => !prop.hints?.includes("hidden")), [properties]); return
{description &&

{formatDescription(description)}

} {visibleProps.map(([name, prop]) => { const isRequired = requiredProps.includes(name); const hasDefault = prop.default !== undefined; const rawDefault = prop.default; const isComplexDefault = hasDefault && (typeof rawDefault === "object" || typeof rawDefault === "string" && (rawDefault.length > 20 || rawDefault.includes('"'))); const fieldProps = { key: name, body: prop.title || name, type: prop.type, ...prop.title && ({ post: [<>API property: {name}] }), ...isRequired && ({ required: true }), ...!isComplexDefault && hasDefault ? { default: sanitize(String(rawDefault)) } : {} }; const isObject = prop.type === "object" && prop.properties; const isArrayOfObjects = prop.type === "array" && prop.items?.type === "object" && prop.items.properties; return {prop.description &&

{formatDescription(prop.description)}

} {isComplexDefault &&

Default: 

                  
                    {JSON.stringify(rawDefault, null, 2)}
} {isArrayOfObjects &&

Object attributes: 

                
                  {'{\n'}
                  {Object.entries(prop.items.properties).map(([iname, iprop]) => <>
                      {`  ${iname}`}
                      {prop.items?.required?.includes(iname) &&  required}
                      {`: {\n    display name: ${sanitize(iprop.title || '')}\n    type: ${iprop.type}\n  }\n`}
                    )}
                  {'}'}
} {isObject && } ; })}
; }; export const LwTemplate = ({title = "Key questions to get you started", icon = "sparkles", cta = "Powered by Agent Studio", linkHref = "https://lucidworks.com/demo/?utm_source=docs&utm_medium=referral&utm_campaign=docs_cta_ai"}) => { const [isLoaded, setIsLoaded] = useState(false); useEffect(() => { const timer = setTimeout(() => { setIsLoaded(true); }, 500); return () => clearTimeout(timer); }, []); return
{isLoaded && ` }} />} Powered by Lucidworks Agent Studio
; }; [localhost link]: http://localhost:3000/docs/fusion-connectors/connectors/v1/jdbc [mintlify link]: https://doc.lucidworks.com/docs/fusion-connectors/connectors/v1/jdbc [old doc.lw link]: https://doc.lucidworks.com/fusion-connectors/83 **Compatible with Fusion version:** 4.0.0 through 5.3.5 The JDBC connector is configured with the SQL statement used to retrieve a resultset from the database. Each row in the result set is treated as a Solr document. This statement is specified as the value of the required property `sql_select_statement`. The column names will be used as Solr document field names; use the SQL "AS" keyword to rename column names as needed. Column and table names should be treated as if they are case-insensitive, even though some databases allow use of mixed case names. All Solr documents must have a unique key, which is the Fusion "id" field. Therefore, the results set must contain a column "id" which can be used as a unique key for the resulting document. ```sql wrap theme={"dark"} SELECT customer_id AS id, * from customers ``` Under the hood, this connector implements the Solr [DataImportHandler (DIH)](https://wiki.apache.org/solr/DataImportHandler) plugin. Deprecation and removal notice This connector is deprecated as of Fusion 5.3 and is removed or expected to be removed as of Fusion 5.4. Use the JDBC V2 connector instead. For more information about deprecations and removals, including possible alternatives, see [Deprecations and Removals](/docs/fusion-connectors/deprecations-and-removals). ## Delta queries Delta queries provide incremental updates to the contents of a collection by indexing only those records in the database which have been changed since the database was last indexed by this connector. The SQL statement is specified as the value of the property `delta_sql_query`. Delta queries select only primary key values, therefore, the query must use the primary key and it must also have a "WHERE" clause which specifies a "last\_modified" condition as follows: ```sql wrap theme={"dark"} SELECT customer_id AS id from customers WHERE last_modified > $ ``` The dollar-sign character '\$' is required; it holds the last successful import time from the database. ## Nested queries Nested queries are used to index information which is stored in the database across a series of tables where the is a one-to-many or many-to-many relationship between them. This statement is specified as the value of the property `nested_queries`. A nested query is used in conjunction with the SQL query specified by the `sql_select_statement` statement. The dollar-sign character '\$' specifies the primary key in the resultset retrieved by the `sql_select_statement` statement. The following example shows the pair of query, nested query statements used to index list of tags assigned to documents: ```sql wrap theme={"dark"} SELECT id FROM document SELECT tag FROM tag INNER JOIN document_tag ON document_tag.tag_id=tag.id WHERE document_tag.doc_id=$ ``` ## Date field types Observe the date field type in the source database. Delta queries compare a date in the source database to the time of the most recent data import. The user selects a date field from their database to use for comparison (for example, a field like `last_updated`). The field type changes the behavior of the comparison: * DATE: It compares the day (for example, 2020-02-20) * TIMESTAMP: It compares both the day and time (for example, 2020-02-20 17:45:45) Note: When using a primary key other than `id`, remove `UUIDUpdateProcessorFactory` from `solrconfig.xml`. Also change the `uniqueKey` value in `schema.xml` to your primary key. ## Learn more The JDBC connector fetches documents from a relational database via SQL queries. Under the hood, this connector implements the Solr [DataImportHandler (DIH)](https://wiki.apache.org/solr/DataImportHandler) plugin. The JDBC connector in Fusion does not automatically discover and index binary data you may have stored in your database (such as PDF files). However, you can configure Fusion to recognize and extract binary data correctly by modifying the datasource configuration file. This file is created when the datasource is first run, and then it is created in `https://FUSION_HOST:FUSION_PORT/data/connectors/lucid.jdbc/datasources/ datasourceID/conf`. The name of the file will include the name of the datasource, as in `dataconfig_datasourceName.xml`. If you are familiar with Solr’s DIH, you will recognize this as a standard `dataconfig.xml` file. Follow these steps to modify the configuration file: 1. Add a `name` attribute for the database containing your binary data to the `dataSource` entry. 2. Set the `convertType` attribute for the `dataSource` to `false`. This prevents Fusion from treating binary data as strings. 3. Add a `FieldStreamDataSource` to stream the binary data to the Tika entity processor. 4. Specify the `dataSource` name in the `root` entity. 5. Add an entity for your `FieldStreamDataSource` using the `TikaEntityProcessor` to take the binary data from the `FieldStreamDataSource`, parse it, and specify a field for storing the processed data. 6. Reload the Solr core to apply your configuration changes. The JDBC connector fetches documents from a relational database via SQL queries. Under the hood, this connector implements the Solr [DataImportHandler (DIH)](https://wiki.apache.org/solr/DataImportHandler) plugin. When using the JDBC connector, it is recommended that you work closely with your database administrator to formulate efficient and robust queries. One source of possible problems is the driver being used. In some cases, indexing may fail due to problems with the driver, in particular older versions of Oracle’s JDBC driver. If you have checked that your connection information is correct and your database is allowing the connection, you may want to research if there are any known bugs with the driver you are using. With Oracle databases, note that column names not enclosed in double-quotes are converted to upper-case, but Solr field names are case sensitive. If your column-to-field mapping is not happening properly, check your SQL statement for any lower-case names not enclosed in double-quotes. Dates can also be problematic. Solr has a different date format than many relational databases. If you want date and time fields to be indexed properly, you may need to convert database dates into the proper format using date/string convert functions. In Oracle this is the `TO_CHAR` function; in Microsoft SQL, this is the `DATEPART` function. In MySQL databases, dates are allowed to be 0-strings, such as `0000-00-00`, which is not acceptable to JDBC. If you have legacy date data you may need to add the query parameter `zeroDateTimeBehavior=convertToNull` to your JDBC request string, as in `jdbc:mysql://localhost/myDatabase?zeroDateTimeBehavior=convertToNull`. This will convert the zero-string dates to null values that can be added to the index. Finally, database timeouts are another problematic area. There are several possible solutions to this, from increasing the timeout in the JDBC request (with `netTimeoutForStreamingResults`), altering the SQL statement to page the results, or dumping the records to CSV and indexing them with another connector. ## Configuration When entering configuration values in the UI, use *unescaped* characters, such as `\t` for the tab character. When entering configuration values in the API, use *escaped* characters, such as `\\t` for the tab character.