> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ocient.com/llms.txt
> Use this file to discover all available pages before exploring further.
# JDBC Manual
export const JVM = "JVM®";
export const OKTA = "OKTA®";
export const Spark = "Apache® Spark™";
export const Ocient = "Ocient®";
export const JVM = "JVM®";
export const Windows = "Windows®";
export const Spark = "Apache® Spark™";
export const OKTA = "OKTA®";
export const Ocient = "Ocient®";
export const Maven = "Apache® Maven™";
export const macos = "macOS®";
export const Linux = "Linux®";
export const Ceph = "Red Hat® Ceph®";
export const Amazon = "Amazon®";
export const Java = "Java®";
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](/data-extract-tool).
For a list of commands available in the JDBC CLI, see [Commands Supported by the Ocient JDBC CLI Program](/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](/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](/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 {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](/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 Shell theme={null}
java -classpath com.ocient.cli.CLI [ []]
```
This example runs JDBC version 2.10.
```shell Shell theme={null}
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 Shell theme={null}
Username: admin@system
Password: admin
```
The interface changes to the Ocient CLI.
```shell Shell theme={null}
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 Shell theme={null}
Ocient> connect to jdbc:ocient://10.10.1.1:4050/system;
```
The CLI responds with a connection message.
```shell Shell theme={null}
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.
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 Shell theme={null}
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. |
| `performance` | Enables different levels of query result output for measuring benchmarks by using different performance options. For details, see the [PERFORMANCE](/commands-supported-by-the-ocient-jdbc-cli-program#performance) command. | `off` |
| `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` |
| `timing` | Enables or disables reporting the execution time of each query. For details, see the [TIMING](/commands-supported-by-the-ocient-jdbc-cli-program#timing) command. | `off` |
For supported commands, see [Commands Supported by the Ocient JDBC CLI Program](/commands-supported-by-the-ocient-jdbc-cli-program).
#### Run a Transaction in the JDBC CLI
The Ocient JDBC CLI supports multi-statement transactions using SQL statements. To begin a transaction, execute the `SET AUTOCOMMIT OFF` SQL statement, and then end the transaction with the `COMMIT` or `ROLLBACK` statements. A query within the transaction reads uncommitted rows that the same connection has inserted. This example disables autocommit, inserts two rows into the `public.txn_demo` table, counts the rows in the table, and commits the transaction. Next, the example inserts two more rows in the table and counts the rows. Then, the example runs a `ROLLBACK` command to roll back the changes, and counts the rows again.
```sql SQL theme={null}
Ocient> SET AUTOCOMMIT OFF;
Ocient> INSERT INTO public.txn_demo VALUES (1, 'alpha');
Ocient> INSERT INTO public.txn_demo VALUES (2, 'beta');
Ocient> SELECT count(*) AS n_in_txn FROM public.txn_demo;
Ocient> COMMIT;
Ocient> INSERT INTO public.txn_demo VALUES (3, 'gamma');
Ocient> INSERT INTO public.txn_demo VALUES (4, 'delta');
Ocient> SELECT count(*) AS n_in_txn FROM public.txn_demo;
Ocient> ROLLBACK;
Ocient> SELECT count(*) FROM public.txn_demo;
```
### 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 Java theme={null}
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](/commands-supported-by-the-ocient-jdbc-cli-program#connect) 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 Shell theme={null}
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 Java theme={null}
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`. |
| `bulkLoadLoaderNodesCacheSeconds` | The number of seconds to cache the list of active Loader Nodes the system discovers during bulk load. When you set this parameter to a value greater than `0`, the driver caches the Loader Node list for the specified duration and forwards the same value as a server-side `CACHE_MAX_TIME` hint on the lookup query. This cache is {JVM}-wide and is not specific to individual connections. Do not enable this parameter if your application connects to multiple Ocient clusters from the same JVM. The default value is `0` (disabled). |
| `bulkLoadMode` | The bulk load transport mode. Set this parameter to `ssh` to stage data using SSH/SFTP to Loader Nodes, or set it to `s3` to stage data in an S3-compatible object store. The default value is `ssh`. |
| `bulkLoadPollIntervalSeconds` | The number of seconds to wait between polling the `sys.pipelines` system catalog table for load status. The default value is `2`. |
| `bulkLoadS3AccessKeyId` | The access key identifier for authenticating to the S3 endpoint. This parameter is required when you set the `bulkLoadMode` parameter to `s3`. |
| `bulkLoadS3ApiCallTimeoutMs` | The timeout, in milliseconds, for individual S3 API calls. The default value is `120000` (two minutes). |
| `bulkLoadS3Bucket` | The S3 bucket name for staging bulk load data files. This parameter is required when you set the `bulkLoadMode` parameter to `s3`. |
| `bulkLoadS3EnablePathStyleAccess` | If you set this parameter to `true`, the driver uses path-style access for S3 requests (e.g., `http://endpoint/bucket/key` instead of `http://bucket.endpoint/key`). This parameter is required for S3-compatible services such as MinIO or Ceph. The default value is `true`. |
| `bulkLoadS3Endpoint` | The S3-compatible endpoint URL for bulk load staging (e.g., `https://s3.us-east-1.amazonaws.com`). This parameter is required when you set the `bulkLoadMode` parameter to `s3`. |
| `bulkLoadS3MultipartPartSize` | The size, in bytes, of each part in a multipart upload. The minimum value is `5242880` (5 MiB). The default value is `10485760` (10 MiB). |
| `bulkLoadS3MultipartThreshold` | The size, in bytes, above which the driver uses multipart upload instead of a single PUT request. The default value is `10485760` (10 MiB). |
| `bulkLoadS3Prefix` | The key prefix for staged objects in S3. The default value is `ocient-bulk-load/`. |
| `bulkLoadS3Region` | The AWS region for the S3 endpoint. The default value is `us-east-1`. |
| `bulkLoadS3SecretAccessKey` | The secret access key for authenticating to the S3 endpoint. This parameter is required when you set the `bulkLoadMode` parameter to `s3`. |
| `bulkLoadS3UploadConcurrency` | The number of data chunks to upload to S3 in parallel. Increasing this value can improve staging throughput for large batches. The default value is `1`. |
| `bulkLoadSshChannelTimeoutSeconds` | The number of seconds to wait for an SFTP channel to open on an established SSH connection during bulk load. If the channel does not open within this duration, the driver throws an error. The default value is `30`. |
| `bulkLoadSshConnectTimeoutSeconds` | The number of seconds to wait for the SSH connection and authentication to a Loader Node during bulk load. If the system does not establish a connection within this duration, the driver throws an error. The default value is `10`. |
| `bulkLoadSshKeyPath` | The absolute path to the password-less private SSH key file. The default value is `~/.ssh/id_rsa`. |
| `bulkLoadSshHostKeyVerification` | Controls how the JDBC driver verifies an SSH server host key for bulk load connections.
Supported values are:
`acceptAll` (default) — The driver accepts all host keys. The connector does not check the server host key against the `known_hosts` file and does not output warning logs.
`strict` — The driver accepts only hosts with keys present in the `known_hosts` file (see the `bulkLoadSshKnownHostsPath` parameter). The driver rejects unknown hosts or hosts with changed keys.
`acceptNew` — The driver accepts new hosts and adds their keys to the `known_hosts` file, but rejects hosts with keys changed from the data in `known_hosts`. |
| `bulkLoadSshKnownHostsPath` | The path to the `known_hosts` file, which contains SSH verification keys. The system uses this path only if the `bulkLoadSshHostKeyVerification` parameter is set to `strict` or `acceptNew`.
The default path is `~/.ssh/known_hosts`. |
| `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`. |
| `bulkLoadUseAtomicPipeline` | If you set this parameter to `true`, bulk load uses an atomic pipeline that creates, starts, and monitors the pipeline in a single blocking operation `(CREATE TRANSACTIONAL PIPELINE ... START FOREGROUND)`. This mode requires the Ocient System version 27.1 or later.
Set this parameter to `false` to use the legacy create, start, monitor, and drop functionality when connecting to older servers. The default value is `true`. |
| `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 [CREATE SSO INTEGRATION](/cluster-and-node-management#create-sso-integration). |
| `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. |
| `maxRowsPerInsertBatch` | The maximum number of rows to combine into a single multi-row `INSERT` SQL statement when executing a batch load. If the total number of rows in a batch group exceeds this limit, the driver splits the group into multiple sub-statements.
The default value is 128,000 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 parameter. 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 [SET ADJUSTFACTOR](/commands-supported-by-the-ocient-jdbc-cli-program#set-adjustfactor). |
| `priorityAdjustTime` | The default frequency to adjust the query priority. For details, see [SET ADJUSTTIME](/commands-supported-by-the-ocient-jdbc-cli-program#set-adjusttime). |
| `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 {Spark}-specific JDBC behavior intended to improve compatibility with the Ocient Spark connector. For details, see [JDBC Spark Connector](/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 parameter specifies the port that the SSO authorization uses.
If unspecified, the default value is `7050`. |
| `ssoOAuthFlow` | This parameter 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 parameter 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 parameter 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. |
### JVM System Properties
The Ocient JDBC driver supports {JVM} system properties that control driver-wide behavior. Unlike [connection properties](#supported-jdbc-connection-properties), JVM system properties apply globally to all connections within the JVM. To set JVM properties, use the `-D` flag from the `java` command line. The system reads these properties once when the driver initializes and cannot change them at runtime.
**Example**
This example launches the JDBC CLI with the memory throttle disabled. There is no space between `-D` and the property name.
```shell Shell theme={null}
java -Dcom.ocient.jdbc.rs.disable-memory-throttle=true \
-classpath ocient-jdbc4-jar-with-dependencies.jar com.ocient.cli.CLI
```
#### Supported JVM System Properties
| **Property** | **Type** | **Default** | **Description** |
| -------------------------------------------- | -------- | ----------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `com.ocient.jdbc.rs.disable-memory-throttle` | Boolean | `false` | Controls the heap-based result-set fetch throttle.
When you set this property to `false` (default), the driver pauses new `FETCH_DATA` requests when the projected heap usage exceeds 90% of `Runtime.maxMemory()`, waiting for memory to free before resuming. This action prevents the `OutOfMemoryError` error in most cases.
When you set it to `true`, the driver does not throttle on heap usage. If memory is genuinely exhausted, the JVM throws the `OutOfMemoryError` error instead of the driver waiting. |
JVM system properties are not part of the JDBC connection URL or the `Properties` object the system passes to `DriverManager.getConnection()`. You must set them at the `java` command line.
Set the `com.ocient.jdbc.rs.disable-memory-throttle` property to `true` only when you have verified that your application has sufficient heap headroom or when the throttle interferes with the expected workload behavior. Disabling the throttle can cause the `OutOfMemoryError` error if the JVM heap is exhausted.
### 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 stages the batch data in the [JSON Lines](https://jsonlines.org/) format and loads it through a temporary, system-generated Ocient pipeline. The driver supports two transport modes for staging data.
* **SSH/SFTP (default)** — The driver opens an SSH connection to a Loader Node and writes JSON data files directly to the node file system using SFTP.
* **S3** — The driver uploads JSON data files to an S3-compatible bucket. The Ocient System then reads the staged files from S3 through standard pipeline mechanics. This mode eliminates the requirement for SSH access between the client and the Loader Nodes.
Set the `bulkLoadMode` connection parameter to select the transport mode. The default value is `ssh`.
#### Configuration
Follow these steps to configure bulk loading in your JDBC driver.
**Enable Bulk Loading**
The JDBC driver disables bulk loading by default. To enable it, you must meet these conditions.
* Set the `enableBulkLoad` connection parameter 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](#set-up-parameterized-insert-statements).
* The total number of rows in the batch group meets or exceeds the `bulkLoadThreshold` parameter.
For recommended configuration settings for {Spark} workloads, see [Bulk Loading Best Practices](/jdbc-spark-connector#bulk-loading-best-practices).
**Choose a Transport Mode**
Set the `bulkLoadMode` connection parameter to select how the driver stages data for the pipeline. The supported values are `ssh` (default) and `s3`.
**SSH Mode Configuration**
SSH mode requires passwordless SSH access from the client to all Loader Nodes in the cluster. To point the driver to your private SSH key file, set the `bulkLoadSshKeyPath` connection parameter. The client application must be able to read this file.
The driver automatically discovers available Loader Nodes by querying the [sys.nodes](/system-catalog#sys-nodes) and [sys.service\_roles](/system-catalog#sys-service_roles) system catalog tables.
**S3 Mode Configuration**
The S3 mode stages data in an S3-compatible object store (such as {Amazon} S3 or {Ceph}) and does not require SSH access to the Loader Nodes. When you select the S3 mode, you must provide the following required connection parameters.
| **Parameter** | **Description** |
| --------------------------- | ---------------------------------------------------------------------------- |
| `bulkLoadS3Endpoint` | The S3-compatible endpoint URL (e.g., `https://s3.us-east-1.amazonaws.com`). |
| `bulkLoadS3Bucket` | The S3 bucket name for staging data files. |
| `bulkLoadS3AccessKeyId` | The access key identifier for authenticating to the S3 endpoint. |
| `bulkLoadS3SecretAccessKey` | The secret access key for authenticating to the S3 endpoint. |
You can also set optional S3 parameters to control the region, key prefix, path-style access, multipart upload behavior, upload concurrency, and API call timeout. For the full list of S3 parameters and their defaults, see the [Supported JDBC Connection Properties](#supported-jdbc-connection-properties) table.
The driver uploads JSON data chunks to the staging bucket, generates a `CREATE TRANSACTIONAL PIPELINE SOURCE S3` SQL statement that points the Ocient System to the staged objects, and monitors the pipeline to completion. After the pipeline finishes, the driver deletes the staged objects from S3 and drops the pipeline. On failure, this cleanup occurs only when you set the `bulkLoadCleanupOnFailure` parameter to `true` (the default).
The S3 access key identifier and secret access key are embedded in the pipeline DDL so that the Ocient System can read the staged objects. The pipeline is transactional and the driver drops it immediately after use. The Spark connector redacts credentials from its logs.
**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.
#### 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 Java theme={null}
String sql = "INSERT INTO customers (id, name, status) VALUES (?, ?, ?)";
PreparedStatement ps = conn.prepareStatement(sql);
```
Bind parameter values by position.
```java Java theme={null}
ps.setLong(1, 123L); // First ?
ps.setString(2, "Alice"); // Second ?
ps.setString(3, "ACTIVE"); // Third ?
```
Execute a single insert.
```java Java theme={null}
ps.executeUpdate();
```
Or, add multiple rows as a batch.
```java Java theme={null}
// 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 Java theme={null}
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](/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 Java theme={null}
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](/jdbc-classes-and-methods).
### Run a Transaction Using the Ocient JDBC Driver
To run a multi-statement transaction with the Ocient JDBC driver in a Java program, disable the autocommit mode using `setAutoCommit(false)`, and then call the `commit()` or `rollback()` methods. For transactions, autocommit mode is on by default. Ensure that you execute SQL statements that are supported by transactions. Otherwise, the database throws an error. For a list of supported statements, see [Transactions](/transactions). This example code connects to a sample database and creates the `public.txn_demo` table. Then, the code inserts two rows and displays the row count in the table. The code commits the INSERT statements and re-runs the row count. This code inserts two more rows and displays a row count. Then, the example rolls back the transaction and displays a row count.
Finally, the code enables the autocommit mode.
```java Java theme={null}
import java.sql.*;
import java.util.Properties;
public class TxnApi {
static void show(Connection c, String label) throws SQLException {
try (Statement s = c.createStatement();
ResultSet rs = s.executeQuery("SELECT count(*) AS n FROM public.txn_demo")) {
rs.next();
System.out.println(label + " -> row count = " + rs.getInt("n"));
}
}
public static void main(String[] args) throws Exception {
String url = "jdbc:ocient://db.example.com:4050/salesdb";
Properties p = new Properties();
p.setProperty("user", "admin@system");
p.setProperty("password", "admin");
p.setProperty("tls", "unverified");
try (Connection c = DriverManager.getConnection(url, p)) {
// DDL must run outside a transaction (autocommit on)
try (Statement s = c.createStatement()) {
s.execute("DROP TABLE IF EXISTS public.txn_demo");
s.execute("CREATE TABLE public.txn_demo (id INT, v VARCHAR(64))");
}
// Transaction 1: multiple inserts and read-your-writes, then commit
c.setAutoCommit(false);
try (Statement s = c.createStatement()) {
s.executeUpdate("INSERT INTO public.txn_demo VALUES (1, 'alpha')");
s.executeUpdate("INSERT INTO public.txn_demo VALUES (2, 'beta')");
show(c, "T1 in-txn (read-your-writes, expect 2)");
}
c.commit();
show(c, "T1 after COMMIT (expect 2)");
// Transaction 2: inserts, then rollback
try (Statement s = c.createStatement()) {
s.executeUpdate("INSERT INTO public.txn_demo VALUES (3, 'gamma')");
s.executeUpdate("INSERT INTO public.txn_demo VALUES (4, 'delta')");
show(c, "T2 in-txn (expect 4)");
}
c.rollback();
show(c, "T2 after ROLLBACK (expect 2)");
c.setAutoCommit(true);
}
}
}
```
## Related Links
[Connect Using JDBC](/connect-using-jdbc)
[Data Extract Tool](/data-extract-tool)
[Commands Supported by the Ocient JDBC CLI Program](/commands-supported-by-the-ocient-jdbc-cli-program)
[JDBC Classes and Methods](/jdbc-classes-and-methods).
***
*Linux® is the registered trademark of Linus Torvalds in the U.S. and other countries.*