> For clean Markdown of any page, append .md to the page URL. > For a complete documentation index, see https://ocient.ferndocs.com/guides/connection-driver-reference/jdbc-manual/llms.txt. > For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://ocient.ferndocs.com/_mcp/server. # JDBC Manual > Access Ocient JDBC manual for guidelines on connecting and managing data through Java-based applications. The Ocient® JDBC Driver and command-line interface (CLI) enable you to connect to Ocient using a JDBC connection. Ensure that you meet the prerequisites before using the Ocient JDBC Driver. Then, invoke the CLI program, configure options, and connect to a database using the driver. You can also use the data extract tool to extract a result set to delimited files in the target location. For details about data extracting, see [**Data Extract Tool**](/guides/connection-driver-reference/jdbc-manual/data-extract-tool). For a list of commands available in the JDBC CLI, see [**Commands Supported by the Ocient JDBC CLI Program**](/guides/connection-driver-reference/jdbc-manual/commands-supported-by-the-ocient-jdbc-cli-program). ## Prerequisites This software is required for the JDBC driver. | **Software** | **Version** | | --------------------- | ------------------------------------------------------------------- | | Ocient | Use the latest Ocient System version. | | Operating System (OS) | Windows®, Linux®, or macOS®. Use the latest version of each OS. | | Java® | See the [**Version Compatibility**](/guides/version-compatibility). | ## Driver Features The Ocient JDBC connector supports these features as of the current version. | Unicode Support | UTF-8 | | --------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------- | | Isolation Levels | Ocient does not support transactions at this time. | | Data Types | Supports all Ocient [**Data Types**](/guides/sql-reference/data-types). | | Security / Encryption | Uses SSL/TLS to connect to the Ocient System. TLS protocol is available as a [**JDBC Configuration Option**](#jdbc-cli-configuration-options). | ## Invoke the Ocient JDBC CLI Program If your system meets all the necessary prerequisites, you can run the JDBC CLI by using the Ocient JDBC JAR file. To do this, follow these steps. Go to the Ocient Apache® Maven™ [**repository**](https://mvnrepository.com/artifact/com.ocient/ocient-jdbc4) for all JDBC versions. For more information on which version to pick, see the [**Version Compatibility**](/guides/version-compatibility) page. For the JDBC version you want to use, download the JAR file with dependencies. This JAR file follows the format: `ocient-jdbc4--jar-with-dependencies.jar`. Move this JAR file to the directory where your Ocient System is installed. From the shell terminal, run this command to launch the JDBC CLI. ```shell java -classpath com.ocient.cli.CLI [ []] ``` This example runs JDBC version 2.10. ```shell java -classpath ./ocient-jdbc4-2.10-jar-with-dependencies.jar com.ocient.cli.CLI testuser testpassword ``` After launching, the JDBC CLI prompts you to enter your username and password. ```shell Username: admin@system Password: admin ``` The interface changes to the Ocient CLI. ```shell Ocient> _ ``` Connect to your system from the JDBC using a connection string. Assuming the standard port `4050`, a self-signed certificate, and the SQL Node IP address `10.10.1.1`, you can connect to the system database with the following connecting string. ```shell Ocient> connect to jdbc:ocient://10.10.1.1:4050/system; ``` The CLI responds with a connection message. ```shell Connected to jdbc:ocient://10.10.1.1:4050/system Ocient> _ ``` Now that you are connected to your system, you can execute any queries or commands. > **Note** > > For Java version 1.8.0_144, [**download**](https://www.oracle.com/java/technologies/javase-jce8-downloads.html) and install the Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files 8. ### JDBC CLI Configuration Options The JDBC CLI reads a configuration file consisting of key-value pairs located at `~/.ocient-cli-configuration` with this format. ```shell key1=value1 key2=value2 ... keyN=valueN ``` The JDBC CLI supports these options. | **Option** | **Description** | **Default Value** | | ----------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------- | | `cliIdleTimeoutMinutes` | Configures the idle timeout for the CLI. The CLI rejects subsequent commands and exits the process after `cliIdleTimeoutMinutes` minutes of inactivity. | `0` if left unspecified or the configuration file does not exist. | | `printUuid` | Accepts values `on` or `off`. If you set the `printUuid` option to `on`, the system prints the query identifier for each query that you execute in the CLI. | `off` | For supported commands, see [**Commands Supported by the Ocient JDBC CLI Program**](/guides/connection-driver-reference/jdbc-manual/commands-supported-by-the-ocient-jdbc-cli-program). ## Use the Ocient JDBC Driver in Java Programs First, you must load the Ocient driver class with this statement in a Java program using the JDBC driver. ```java Class.forName("com.ocient.jdbc.JDBCDriver"); ``` The driver class is located in the JDBC driver JAR file named `ocient-jdbc4.jar` and must be available in the CLASSPATH defined for the program. ## Connect Using JDBC The Ocient JDBC driver supports connection properties that can be supplied using the [**CONNECT**](/guides/connection-driver-reference/jdbc-manual/commands-supported-by-the-ocient-jdbc-cli-program) command in the JDBC CLI or as a properties object passed in a Java application. **JDBC URL Example** This connection string includes various connection properties that trail the login credentials. ```shell CONNECT TO 'jdbc:ocient://db.example.com:6432/salesdb;user=admin@system;password=admin;logLevel=INFO;networkTimeout=15000;enableBulkLoad=true;bulkLoadThreshold=50000'; ``` **Java DriverManager Example** Alternatively, you can use the DriverManager class to provide connection properties if you are using a JDBC connection as part of a Java application. ```java import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Properties; public class OcientConnectionExample { public static void main(String[] args) throws SQLException { // Base JDBC URL: host, port, and database String url = "jdbc:ocient://db.example.com:6432/salesdb"; // JDBC connection properties Properties props = new Properties(); props.setProperty("user", "analytics_user"); // Ocient username props.setProperty("password", "StrongPassw0rd!"); // Ocient password props.setProperty("logLevel", "INFO"); // Driver logging verbosity props.setProperty("networkTimeout", "15000"); // 15s network timeout (ms) props.setProperty("enableBulkLoad", "true"); // Use bulk load for large batches props.setProperty("bulkLoadThreshold", "50000"); // Min rows for bulk loa try (Connection conn = DriverManager.getConnection(url, props)) { } } } ``` ### Supported JDBC Connection Properties The JDBC driver supports these connection parameters. | **Parameter** | **Description** | | ----------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `bulkLoadChunkSize` | The number of rows to include in each JSON data file (chunk) uploaded. The default value is `60000`. | | `bulkLoadCleanupOnFailure` | If you set this parameter value to `true`, the driver deletes the temporary files and pipelines even if the load fails. The default value is `true`. | | `bulkLoadFailOnError` | If you set this parameter value to `true`, `executeBatch()` fails if any `INSERT` operation fails. If you set this parameter value to `false`, `executeBatch()` falls back to a standard multi-row `INSERT` operation if any INSERT operation fails. The default value is `false`. | | `bulkLoadPollIntervalSeconds` | The number of seconds to wait between polling the `sys.pipelines` system catalog table for load status. The default value is `2`. | | `bulkLoadSshKeyPath` | The absolute path to the password-less private SSH key file. The default value is `~/.ssh/id_rsa`. | | `bulkLoadSshUser` | The SSH username to use when connecting to Loader Nodes. | | `bulkLoadThreshold` | The minimum number of rows in a batch group to trigger a bulk load. The default value is `25000`. | | `defaultSchema` | Default schema. | | `enableBulkLoad` | Enables the high-speed bulk load feature. Set this parameter value to `true` to enable this feature. Otherwise, set this parameter to `false` to leave the feature disabled. The default value is `false`. | | `force` | If set to true (case-sensitive), this parameter disables load-balancing for the connection. | | `handshake` | Specifies the handshake protocol used for the connection. Supported options include: `"CBC", "GCM", "SSO"` `"GCM"` — (Galois/Counter Mode). This is the default encryption and is the recommended password encryption algorithm. `"CBC"` — (Cipher Block Chaining) for password encryption. `"SSO"` — Single Sign-On. | | `identityprovider` | An SSO integration established in the database. For details, see [**Cluster and Node Management**](/guides/sql-reference/data-definition-language-ddl-statement-reference/cluster-and-node-management). | | `logfile` | The filename to use for JDBC tracing. | | `loglevel` | If set to ERROR (warnings and errors only) or DEBUG (verbose tracing) and logfile is also set, JDBC tracing is enabled. This parameter is case-sensitive. | | `longQueryThreshold` | Estimated query runtime in milliseconds before deeper query optimization runs. `0` — Use database server default. `-1` — Never run deeper optimization. | | `maxRows` | Maximum allowed result set size in the number of rows. | | `maxTempDisk` | Maximum allowed temp disk usage as a percentage (0 - 100). | | `maxThreadsPerResultSet` | The maximum number of threads the client uses to fetch rows from the server per Result Set, as defined in the [**official JDBC documentation**](https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html). In this context, a thread represents a logical connection to the database. Applications that create and operate on Statement objects concurrently might find value in setting this property. Defaults to `0`, which effectively creates an unbounded Result Set thread pool. | | `maxTime` | Maximum allowed runtime of a query in seconds before it is canceled on the server. | | `networkTimeout` | Network connection timeout in milliseconds. If unspecified, this defaults to 10000 milliseconds. | | `parallelism` | Limits a query to running on a specified number of cores on each CPU. | | `password` | The password for the user. | | `priority` | Query priority. This sets the priority for queries to run on the server side. If unspecified, this defaults to `1.0`. | | `priorityAdjustFactor` | The default query priority adjustment value. For details, see [**Commands Supported by the Ocient JDBC CLI Program**](/guides/connection-driver-reference/jdbc-manual/commands-supported-by-the-ocient-jdbc-cli-program). | | `priorityAdjustTime` | The default frequency to adjust the query priority. For details, see [**Commands Supported by the Ocient JDBC CLI Program**](/guides/connection-driver-reference/jdbc-manual/commands-supported-by-the-ocient-jdbc-cli-program). | | `serviceClassName` | Specifies the name of the service class to use for the database session. | | `sparkMode` | When you set this parameter to `true`, the driver enables Apache® Spark™‑specific JDBC behavior intended to improve compatibility with the Ocient Spark connector. For details, see [**JDBC Spark Connector**](/guides/connection-driver-reference/jdbc-manual/jdbc-spark-connector). This parameter defaults to `false` if you are connecting directly using the Ocient JDBC driver (e.g., connecting with the `DriverManager` class or CLI). Otherwise, if you are using the Spark connector (catalog or `.format("ocient")`), the default is `true`. | | `ssoNumericAddress` | Specifies the SSO callback URL as `127.0.0.1`. When this value is `false`, the URL is `localhost`. The default value is `false`. | | `ssoOAuthCodeCallbackPort` | If `ssoOAuthFlow=authorizationCode`, this property specifies the port that the SSO authorization uses. If unspecified, the default value is `7050`. | | `ssoOAuthFlow` | This property is only applicable if SSO authorization is enabled. Forces the driver to use either the "authorizationCode" or "deviceGrant" flow to establish a Single Sign-On session. If this property is not provided, the Ocient® System uses the "authorizationCode" flow when a web browser is available to the client and the "deviceGrant" flow when a web browser is not available to the client. | | `ssoDebugMode` | When set to `true`, the system records additional log messages related to SSO. | | `ssoOktaNativeTokenPath` | The path to the OKTA® Native Token (AES-256GCM JWE). The path is relative to your home directory (i.e. '\~/'). | | `ssoSslCallback` | Specifies whether to use HTTPS instead of HTTP for the SSO callback URL. The default value is `false`. | | `ssoTimeoutSeconds` | The number of seconds before the SSO connection request times out. The default value is `60`. | | `statementPooling` | ℹ️ This property has been removed as of Ocient JDBC version 2.104 and later. When set to `ON`, recently used statements are cached. Set to `OFF` when using a third-party application that implements statement pooling | | `tls` | Can be set to `unverified` or `on`. Enables SSL/TLS encryption for the connection. | | `timeoutMillis` | Number of milliseconds before cancellable operations are timed out and killed by the driver. 0 means no timeout. Default: 0 | | `user` | The identifier of the user. | ## JDBC Bulk Loading For very large batches, the Ocient JDBC driver provides a high-speed bulk load path. When you enable bulk loading, the driver bypasses the standard multi-row insert operation and instead streams the batch data directly to the Loader Nodes in the cluster using SSH/SFTP. The data loads in the in-memory [**JSON Lines**](https://jsonlines.org/) format using a temporary, system-generated Ocient pipeline, providing a massive performance boost for large-scale data ingestion. ### Configuration Follow these steps to configure bulk loading in your JDBC driver. **SSH Key Access and Node** JDBC bulk loading does not use password authentication. Instead, you must use SSH access for all Loader Nodes in the cluster. To point the driver to your private SSH key file, use the `bulkLoadSshKeyPath` JDBC connection property. The client application must be able to read this file. The driver automatically discovers available Loader Nodes by querying the [**sys.nodes**](/guides/sql-reference/system-catalog-reference/system-catalog) and [**sys.service_roles**](/guides/sql-reference/system-catalog-reference/system-catalog) system catalog tables. **Data Type Mapping** The JDBC driver supports all standard scalar types. For complex types, Java SQL STRUCT (`java.sql.Struct`) types load as Ocient TUPLE types, and Java ARRAY (`java.sql.Array`) types load as Ocient ARRAY types. **Enable Bulk Loading** The JDBC driver disables bulk loading by default. To enable it, you must meet these conditions: * Set the `enableBulkLoad` connection property to `true`. * Use a parameterized `INSERT` statement that uses placeholders for values, e.g., `INSERT INTO customers (id, name, status) VALUES (?, ?, ?)`. For details, see [**Set Up Parameterized Insert Statements**](). * The total number of rows in the batch group meets or exceeds the `bulkLoadThreshold` property. > **Note** > > For recommended configuration settings for Spark workloads, see [**Bulk Loading Best Practices**](/guides/connection-driver-reference/jdbc-manual/jdbc-spark-connector). ### Set Up Parameterized `INSERT` Statements These steps demonstrate how to use a single parameterized `INSERT` statement using the Java `PreparedStatement` class. The parameterized statement binds different values for each row. Create the `PreparedStatement` object `ps` with `?` placeholders. ```java String sql = "INSERT INTO customers (id, name, status) VALUES (?, ?, ?)"; PreparedStatement ps = conn.prepareStatement(sql); ``` Bind parameter values by position. ```java ps.setLong(1, 123L); // First ? ps.setString(2, "Alice"); // Second ? ps.setString(3, "ACTIVE"); // Third ? ``` Execute a single insert. ```java ps.executeUpdate(); ``` Or, add multiple rows as a batch. ```java // First row ps.setLong(1, 123L); ps.setString(2, "Alice"); ps.setString(3, "ACTIVE"); ps.addBatch(); // Second row ps.setLong(1, 124L); ps.setString(2, "Bob"); ps.setString(3, "INACTIVE"); ps.addBatch(); // Send them together int[] results = ps.executeBatch(); ``` Close the resources. ```java ps.close(); ``` ## Connection Encryption (SSL/TLS) The JDBC driver can use SSL/TLS to connect to Ocient, causing all traffic to be encrypted. Specify the `tls` property on the connect statement to enable TLS support. The `tls` property supports these values. **unverified** Traffic on the connection is encrypted, but no verification is done on the certificate received from the Ocient System. **on** Traffic is encrypted, and the JDBC client must be able to verify the certificate received from the Ocient System. The TLS `on` mode requires that the client knows the **Certificate Authority** that signed the certificate provided by the Ocient System. Typically, this mode requires either that the certificate is signed by a well-known certificate authority, or the Certificate Authority certificate has been imported into the truststore of the Java system. The Java `keytool` utility is used to manipulate a Java truststore. [**Secure Connections Using TLS**](/guides/system-administration/secure-connections-using-tls) discusses how you can configure user-defined certificates for the Ocient System. ## Sample Java Program Using the Ocient JDBC Driver This sample program demonstrates how to utilize the Ocient JDBC driver to establish a connection to a database, construct a prepared SQL statement, execute the query, and iterate through the result set. ```java public class OcientJDBCExample { public static void main(final String args[]) { Class.forName("com.ocient.jdbc.JDBCDriver"); Properties props = new Properties(); props.setProperty("user", "username"); props.setProperty("password", "pwd"); props.setProperty("force", "true"); String url = "jdbc:ocient://192.168.121.82:4050/db"; Connection conn = DriverManager.getConnection(url, props); PreparedStatement pstmt = conn.prepareStatement( "select l_orderkey from tpch.lineitem where l_linenumber = ?"); Pstmt.setInt(1, 4); ResultSet rs = pstmt.executeQuery(); while(rs.next()){ // do something with row } rs.close(); pstmt.close(); conn.close(); return; } } ``` For supported classes and methods, see [**JDBC Classes and Methods**](/guides/connection-driver-reference/jdbc-manual/jdbc-classes-and-methods). # Related Links [**Connect Using JDBC**](/guides/connect-to-ocient/connect-using-jdbc) [**Data Extract Tool**](/guides/connection-driver-reference/jdbc-manual/data-extract-tool) [**Commands Supported by the Ocient JDBC CLI Program**](/guides/connection-driver-reference/jdbc-manual/commands-supported-by-the-ocient-jdbc-cli-program) [**JDBC Classes and Methods**](/guides/connection-driver-reference/jdbc-manual/jdbc-classes-and-methods). --- \*\*Linux®*is the registered trademark of Linus Torvalds in the U.S. and other countries.* > Access Ocient JDBC manual for guidelines on connecting and managing data through Java-based applications.