> ## 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

> Index pipeline stage configuration specifications

export const schema = {
  "type": "object",
  "title": "JDBC Lookup",
  "description": "Connect to a database and lookup one or more values and inject them into the context",
  "required": ["driver", "connection", "username", "password", "preparedStatement"],
  "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"]
    },
    "driver": {
      "type": "string",
      "title": "JDBC Driver",
      "description": "The fully qualified class name of the JDBC Driver to use."
    },
    "connection": {
      "type": "string",
      "title": "Connection URI",
      "description": "The connection string for the database."
    },
    "username": {
      "type": "string",
      "title": "Username",
      "description": "The username to connect to the database."
    },
    "password": {
      "type": "string",
      "title": "Password",
      "description": "The password to connect to the database.",
      "hints": ["secret"]
    },
    "preparedStatement": {
      "type": "string",
      "title": "SQL Prepared Statement",
      "description": "The SQL Prepared Statement to execute when bound with values.",
      "hints": ["lengthy"]
    },
    "rows": {
      "type": "integer",
      "title": "Rows",
      "description": "The number of rows to return.  -1 for all rows (be wary of memory usage for this).",
      "default": -1,
      "hints": ["advanced"]
    },
    "fetchSize": {
      "type": "integer",
      "title": "Fetch Size",
      "description": "The JDBC Fetch Size to use.  If -1, use the driver default.",
      "default": -1,
      "hints": ["advanced"]
    },
    "join": {
      "type": "boolean",
      "title": "Join With Document",
      "description": "If true, the results will be added on to the document using the prefix key and the row id, else the results will be put in the pipeline context using",
      "default": true,
      "hints": ["advanced"]
    },
    "preparedStatementKeys": {
      "type": "array",
      "title": "Prepared Statement Keys",
      "description": "The keys in the Pipeline Context to use to map request attributes into the prepared statement.  These must map to the '?'s in your prepared statement.  They must also be able to be resolved as the first parameter of that name in a request.",
      "hints": ["advanced"],
      "items": {
        "type": "string"
      }
    },
    "prefix": {
      "type": "string",
      "title": "Result Prefix Key",
      "description": "The string to use as a prefix for all values.",
      "hints": ["advanced"]
    },
    "validationQueryTimeout": {
      "type": "integer",
      "title": "Validation Query Timeout",
      "description": "The validation query timeout, the amount of time, in seconds, that connection validation will wait for a response from the database when executing a validation query.  Use a value less than or equal to 0 for no timeout.",
      "default": -1,
      "hints": ["advanced"]
    }
  },
  "category": "Document Filtering and Enrichment",
  "categoryPriority": 8,
  "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 <div>
      {description && <p>{formatDescription(description)}</p>}

      {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: [<><span className="text-stone-400 dark:text-stone-500">API property: </span>{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 <ParamField {...fieldProps}>
            {prop.description && <p>{formatDescription(prop.description)}</p>}

            {isComplexDefault && <div className="flex">
                <p>
                  <strong>Default:</strong>
                </p>
                <pre className="!my-0">
                  <code>
                    {JSON.stringify(rawDefault, null, 2)}
                  </code>
                </pre>
              </div>}

            {isArrayOfObjects && <div className="flex">
              <p>
                <strong>Object attributes:</strong>
              </p>
              <pre className="!my-0">
                <code>
                  {'{\n'}
                  {Object.entries(prop.items.properties).map(([iname, iprop]) => <>
                      {`  ${iname}`}
                      {prop.items?.required?.includes(iname) && <span style={{
      color: 'red'
    }}> required</span>}
                      {`: {\n    display name: ${sanitize(iprop.title || '')}\n    type: ${iprop.type}\n  }\n`}
                    </>)}
                  {'}'}
                </code>
              </pre>
              </div>}

            {isObject && <Expandable title="properties">
                <SchemaParamFields schema={{
      properties: prop.properties,
      required: prop.required
    }} />
              </Expandable>}
          </ParamField>;
  })}
    </div>;
};

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 <div className="lw-template-container">
      <Card title={title} icon={icon}>
        {isLoaded && <span dangerouslySetInnerHTML={{
    __html: `<lw-template id="a029c1a9-28be-427e-b0e1-5d918920246a"></lw-template
            >`
  }} />}
        <Link href={linkHref} className="agent-studio-link text-left text-gray-600 gap-2 dark:text-gray-400 text-sm font-medium flex flex-row items-center hover:text-primary dark:hover:text-primary-light group-hover:text-primary group-hover:dark:text-primary-light">Powered by Lucidworks Agent Studio</Link>
      </Card>
    </div>;
};

[localhost link]: http://localhost:3000/docs/lucidworks-search/09-developer-documentation/config-specs/index-pipeline-stages/jdbc-index

[mintlify link]: https://doc.lucidworks.com/docs/lucidworks-search/09-developer-documentation/config-specs/index-pipeline-stages/jdbc-index

[old doc.lw link]: https://doc.lucidworks.com/managed-fusion/5.9/efq5no

The JDBC Index Stage is used to connect to a database, lookup one or more values, and then inject them into the context.
The properties for setting stage are identical to the [JDBC Query Stage](/docs/lucidworks-search/09-developer-documentation/config-specs/query-pipeline-stages/jdbc-lookup), except for the "rows" property, which defaults to **-1** for the index stage (which returns all rows in the database), and defaults to **10** returned rows for the query stage.

<Note>
  You must first upload a JDBC Driver to Lucidworks Search.
</Note>

<Accordion title="Upload a JDBC Driver to Lucidworks Search">
  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.

  Lucidworks Search stores JDBC drivers in the blob store. You can upload a driver using the Lucidworks Search UI or the Blob Store API. Learn more about the [JDBC V2 connector](/docs/fusion-connectors/connectors/jdbc-v2).

  <LwTemplate />

  ## How to upload a JDBC driver using the Fusion UI

  {/* // tag::upload[] */}

  1. In the Lucidworks Search UI, navigate to **System** > **Blobs**.

  2. Click **Add**.

  3. Select **JDBC Driver**.\
     The "New 'JDBC Driver' Upload" panel appears.
       <img src="https://mintcdn.com/lucidworks/5yWZ-KtZuBe4Y_Fg/assets/images/4.0/blobstore_add_jdbcdriver1.png?fit=max&auto=format&n=5yWZ-KtZuBe4Y_Fg&q=85&s=b7d0a7b57734a2219cb107db40b0f210" alt="Uploading a connector" style={{ width: "450px" }} width="1041" height="686" data-path="assets/images/4.0/blobstore_add_jdbcdriver1.png" />

  4. Click **Choose File** and select the .jar file from your file system.
       <img src="https://mintcdn.com/lucidworks/5yWZ-KtZuBe4Y_Fg/assets/images/4.0/blobstore_add_jdbcdriver2.png?fit=max&auto=format&n=5yWZ-KtZuBe4Y_Fg&q=85&s=e722ae92551c19068ce783d6e71798b4" alt="Uploading connector" width="2455" height="1012" data-path="assets/images/4.0/blobstore_add_jdbcdriver2.png" />

  5. Click **Upload**.\
     The new driver’s blob manifest appears.
       <img src="https://mintcdn.com/lucidworks/5yWZ-KtZuBe4Y_Fg/assets/images/4.0/blobstore_add_jdbcdriver3.png?fit=max&auto=format&n=5yWZ-KtZuBe4Y_Fg&q=85&s=f3c48edcb769b7df7cd300dc449e249f" alt="Uploaded connector" width="2432" height="1009" data-path="assets/images/4.0/blobstore_add_jdbcdriver3.png" />

  From this screen you can also delete or replace the driver.

  {/* // end::upload[] */}

  ## How to install a JDBC driver using the API

  1. Upload the JAR file to Lucidworks Search’s blob store using the [`/blobs/{id}` endpoint](/api-reference/blobs/get-blob-store-service-status).

     Specify an arbitrary blob ID, and a `resourceType` value of `plugin:connector`, as in this example:

     ```bash theme={"dark"}
     curl -u USERNAME:PASSWORD -H "content-type:application/java-archive" -H "content-length:707261" -X PUT --data-binary @postgresql-42.0.0.jar https://EXAMPLE_COMPANY.b.lucidworks.cloud/api/blobs/mydriver?resourceType=driver:jdbc
     ```

     Success response:

     ```json theme={"dark"}
     {
       "name" : "mydriver",
       "contentType" : "application/java-archive",
       "size" : 707261,
       "modifiedTime" : "2017-06-09T19:00:48.919Z",
       "version" : 0,
       "md5" : "c67163ca764bfe632f28229c142131b5",
       "metadata" : {
         "subtype" : "driver:jdbc",
         "drivers" : "org.postgresql.Driver",
         "resourceType" : "driver:jdbc"
       }
     }
     ```

     Lucidworks Search automatically publishes the event to the cluster, and the listeners perform the driver installation process on each node.

     <Tip>   If the blob ID is identical to an existing one, the old driver will be uninstalled and the new driver will installed in its place. To get the list of existing blob IDs, run: `curl -u USERNAME:PASSWORD https://FUSION_HOST:FUSION_PORT/api/blobs`</Tip>
  2. To verify the uploaded driver, run:

     ```bash theme={"dark"}
     curl -u USERNAME:PASSWORD https://EXAMPLE_COMPANY.b.lucidworks.cloud/api/blobs/BLOB_ID/manifest
     ```

     Where the `BLOB_ID` is the name specified during upload, such as "mydriver" above. A success response looks like this:

     ```json theme={"dark"}
     {
       "name" : "mydriver",
       "contentType" : "application/java-archive",
       "size" : 707261,
       "modifiedTime" : "2017-06-09T19:05:17.897Z",
       "version" : 1569755095787110400,
       "md5" : "c67163ca764bfe632f28229c142131b5",
       "metadata" : {
         "subtype" : "driver:jdbc",
         "drivers" : "org.postgresql.Driver",
         "resourceType" : "driver:jdbc"
       }
     }
     ```
</Accordion>

## Example

An example of a JDBC Index Stage setup

*Upload stage config via POST to Lucidworks Search REST API endpoint `api/index-stages/instances`*

```bash wrap  theme={"dark"}
curl -u USERNAME:PASSWORD -X POST -H "Content-Type: application/json" -d '{"id": "jdbc-index-test","type":"jdbc-index-lookup","driver":"postgresql-9.3-1101-jdbc4.jar","connection":"jdbc:postgresql:database","username":"user","password":"password1","preparedStatement":"select ID as id from DATABASE;"}' http://EXAMPLE_COMPANY.lucidworks.cloud/api/index-stages/instances
```

<Note>
  Replace `EXAMPLE_COMPANY` with the name provided by your Lucidworks representative.
</Note>

*Response*

```json wrap  theme={"dark"}
{
  "type" : "jdbc-index-lookup",
  "id" : "jdbc-index-test",
  "driver" : "postgresql-9.3-1101-jdbc4.jar",
  "connection" : "jdbc:postgresql:database",
  "username" : "user",
  "password" : "password1",
  "preparedStatement" : "select ID as id from DATABASE;",
  "fetchSize" : -1,
  "join" : true,
  "rows" : -1,
  "skip" : false,
  "label" : "jdbc-index-lookup",
  "type" : "jdbc-index-lookup"
}
```

## Configuration

<Tip>
  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.
</Tip>

<SchemaParamFields schema={schema} />
