JDBC

HeavyDB supports JDBC connections.

Resources

Resource

Description

JAR file

$HEAVYAI_PATH/bin/heavyai-jdbc-<major_version>.<minor_version>.<revision>.jar

Code samples

$HEAVYAI_PATH/samples

JDBC driver

ai.heavy.jdbc.HeavyAIDriver

URL

jdbc:heavyai:<host>:[<keyvalue1>:<keyvalue2>:<keyvalue3>...][?<key1=value1>&<key2=value2>&<key3=value3>...]

You can connect securely to a JDBC session by defining the database URL (a colon-separated connection string with optional query string) and the keys/values in DriverManager.getConnection properties.

You can define key values separated by colons in the main part of the connection string , or as key/value pairs separated by an ampersand (&) in an optional query string following the ? character.

jdbc:omnisci:<host_name> must be defined in the main part of the connection string.

Valid keys in the connection URL are the same as those in the properties object. If defined in the main part of the connection string, they must appear in the following order:

  • host_name - Name of the host. Must be defined in the main part of the connection string.

  • port_num - Port number

  • db_name - Name of the database.

  • protocol: <binary|binary_tls|http|https|https_insecure>

    • To connect using a secure connection through binary mode, the HEAVY.AI server must be running with an encrypted main port open.

    • Use binary TLS mode to explicitly connect using SSL with the default Java trust stores.

    • To connect using HTTPS, the HEAVY.AI web server must be running with HTTPS enabled.

    • To create an encrypted connection with no server validation, use HTTPS_INSECURE. Note: Using HTTPS_INSECURE is not recommended in a production environment.

    For more information, see HEAVY.AI server configuration.

  • server_trust_store: trust_store_path - The path to a Java trust store holding the HEAVY.AI server public certificate.

  • server_trust_store_pwd: password - The password to the trust store.

  • pkiauth: true | false - Enable PKI authentication. If true:

    • The client X509 public certificate is supplied to the server for verification. Use the sslcert parameter to supply a file containing the certificate.

    • The user parameter and the password parameters are ignored and should be blank.

  • sslcert certificate-path - Path to the certificate and private key file, in PKCS12 format.

  • sslkey key-path - Path to the SSL key.

  • sslkey_password password - Password for the SSL key defined in sslkey key-path.

  • max_rows max_rows - The maximum number of rows to return.

Examples

You can use the following connection URLs to connect to an HEAVY.AI database called test_DB using port 6274 with max_rows set to 10000.

jdbc:heavyai:localhost:6274:test_DB?max_rows=10000
jdbc:heavyai:localhost:6274?db_name=test_DB&max_rows=10000

If duplicate information is supplied in different sources of connection information, sources have priority as follows:

  1. The main part of the URL.

  2. The query portion of the URL.

  3. The Properties object.

In the following connection URL, the database test_DB would be used, and if logging is turned on, the driver would issue a warning:

jdbc:heayai:localhost:6274?dbname=test_DB&max_rows=10000&db_name=test_WRONGDB

The fields in the "main" colon-separated portion of the URL are interpreted strictly in the order defined in the list of keys: host_name followed by port_num, followed by db_name, and so on. All of these fields can be specified in this portion of the connection string; however to specify a max_rows field this way, all the preceding values must also be specified. Empty fields are not allowed. In this case, consider using a Properties object or a the query string portion of the URL.

You can also provide all options and parameters for a JDBC connection in a Java properties file; for example:

server_trust_store=/tls_certs/server/trust_store_server.jks
server_trust_store_password=jks_truststore_password
pkiauth=true
sslcert=/tls_certs/client1_ca_primary_signed/client1.p12
sslkey_password=ssl_certfile_password
protocol=https

Supported JDBC Methods

Method

Description

Connection class

clearWarnings

Clear all warnings reported for this connection object. After calling clearWarnings, calling getWarnings returns null until a new warning is reported for this connection object.

close

Disconnect the JDBC client session and frees associated resources.

createStatement

Get a new, empty Statement object.

getCatalog

Get the Connection object current catalog name.

getMetaData

Get a DatabaseMetaData object, which contains metadata about the database for which this Connection object represents a connection. The metadata includes information about the database tables, supported SQL grammar, stored procedures, and the capabilities of this connection.

getWarnings

Get the first warning reported by calls on this Connection object.

isClosed

Get the closed status of this Connection object:

  • true = connection closed

  • false = connection valid

isValid

Get the connection status:

  • true = valid connection

  • false = connection closed

prepareStatement

Create a PreparedStatement object. HeavyDB makes no distinction between prepared and directly executed statements and queries.

Driver

acceptsURL

Get the driver determination of whether or not it can open a connection to the URL.

connect

Make a database connection to the specified URL.

getMajorVersion

Get the driver major version number.

getMinorVersion

Get the driver minor version number.

getParentLogger

Get the parent Logger of all Loggers used by this driver.

getPropertyInfo

Get driver property information.

jdbcCompliant

Test if this driver is a JDBC-compliant driver.

Statement

executeQuery

Execute a SELECT query.

executeUpdate

Execute an INSERT or DROP statement.

getMaxFieldSize

Get the maximum number of bytes that can be returned for character and binary column values in a ResultSet object produced by this Statement object.

getMaxRows

Get the maximum number of rows that a ResultSet object produced by this Statement object can contain.

getMetaData

Get a new DatabaseMetaData object.

setMaxFieldSize

Set the limit for the maximum number of bytes that can be returned for character and binary column values in a ResultSet object produced by this Statement object.

setMaxRows

Set the limit for the maximum number of rows that any ResultSet object generated by this Statement object can contain to the given number. Default is 100,000 rows. If you do not specify a LIMIT clause in your query through this Statement object, this setting automatically appends LIMIT maxRows to the query. Setting maxRows to 0 allows an unlimited number of rows to be returned, and does not append a LIMIT clause to the query through this Statement object.

PreparedStatement

addBatch

Add an INSERT statement to a batch.

execute

Execute a prepared query. HeavyDB makes no distinction between prepared and direct query execution.

executeBatch

Execute a batch of queries.

executeUpdate

Create a prepared statement object for batch updates.

set[obj]

Set a dynamic parameter for batch statements. Dynamic parameters are supported with batch inserts only.

setMaxRows

Set the limit for the maximum number of rows that any ResultSet object generated by this Statement object can contain to the given number. Default is 100,000 rows. If you do not specify a LIMIT clause in your query through this Statement object, this setting automatically appends LIMIT maxRows to the query. Setting maxRows to 0 allows an unlimited number of rows to be returned, and does not append a LIMIT clause to the query through this Statement object.

ResultSet

close

Releases this ResultSet's database and JDBC resources immediately.

getBigDecimal

Get a BigDecimal object.

getBoolean

Get a boolean object.

getDate

Get a java.sql.Date object.

getDouble

Get a double object.

getFloat

Get a float object.

getInt

Get an integer object.

getLong

Get a long integer object

getObject

Get a generic Object class representing the column value.

getShort

Get a short integer.

getString

Get a String object.

getTime

Get a java.sql.Time object.

getTimestamp

Get a java.sql.Timestamp object.

ResultSetMetaData

getColumnCount

Get the designated column's table's catalog name.

getColumnDisplaySize

Get the designated column's normal maximum width in characters.

getColumnLabel

Get the designated column's suggested title for use in printouts and displays.

getColumnName

Get the designated column's name.

getColumnType

Get the designated column's SQL type.

getColumnTypeName

Get the designated column's database-specific type name.

getPrecision

Get the designated column's specified column size.

getScale

Get the designated column's number of digits to right of the decimal point.

getSchemaName

Get the designated column's table's schema.

getTableName

Get the designated column's table name.

isAutoIncrement

Get whether the designated column is automatically numbered.

isCaseSensitive

Get whether a column's case matters.

isCurrency

Get whether the designated column is a cash value.

isDefinitelyWritable

Get whether a write on the designated column will definitely succeed.

isNullable

Get the nullability of values in the designated column.

isReadOnly

Get whether the designated column is definitely not writable.

isSearchable

Get whether the designated column can be used in a where clause.

isSigned

Get whether values in the designated column are signed numbers.

isWritable

Get whether it is possible to write to the designated column.

DatabaseMetaData

allTablesAreSelectable

Get whether the current user can use all the tables returned by the method getTables in a SELECT statement.

autoCommitFailureClosesAllResultSets

Get whether a SQLException while autoCommit is true inidcates that all open ResultSets are closed, even ones that are holdable.

dataDefinitionCausesTransactionCommit

Get whether a data definition statement within a transaction forces the transaction to commit.

dataDefinitionIgnoredInTransactions

Get whether this database ignores a data definition statement within a transaction.

doesMaxRowSizeIncludeBlobs

Get whether the return value for the method getMaxRowSize includes the SQL data types LONGVARCHAR and LONGVARBINARY.

generatedKeyAlwaysReturned

Get whether a generated key will always be returned if the column name(s) or index(es) specified for the auto generated key column(s) are valid and the statement succeeds.

getBestRowIdentifier

Get a description of a table's optimal set of columns that uniquely identifies a row.

getCatalogs

Get the catalog names available in this database.

getCatalogSeparator

Get the String that this database uses as the separator between a catalog and table name.

getCatalogTerm

Get the database vendor's preferred term for "catalog".

getColumns

Get a description of table columns available in the specified catalog.

getConnection

Get the connection that produced this metadata object.

getDatabaseMajorVersion

Get the major version number of the underlying database.

getDatabaseMinorVersion

Get the minor version number of the underlying database.

getDatabaseProductName

Get the name of this database product.

getDatabaseProductVersion

Get the version number of this database product.

getDefaultTransactionIsolation

Get this database's default transaction isolation level.

getDriverMajorVersion

Get this JDBC driver's major version number.

getDriverMinorVersion

Get this JDBC driver's minor version number.

getDriverName

Get the name of this JDBC driver.

getDriverVersion

Get the version number of this JDBC driver as a String.

getExtraNameCharacters

Get all the "extra" characters that can be used in unquoted identifier names (those beyond a-z, A-Z, 0-9 and _).

getIdentifierQuote

Get the string used to quote SQL identifiers.

getJDBCMajorVersion

Get the major JDBC version number for this driver.

getJDBCMinorVersion

Get the minor JDBC version number for this driver.

getMaxBinaryLiteralLength

Get the maximum number of hex characters this database allows in an inline binary literal.

getMaxCatalogNameLength

Get the maximum number of characters that this database allows in a catalog name.

getMaxCharLiteralLength

Get the maximum number of characters this database allows for a character literal.

getMaxColumnNameLength

Get the maximum number of characters this database allows for a column name.

getMaxColumnsInGroupBy

Get the maximum number of columns this database allows in a GROUP BY clause.

getMaxColumnsInIndex

Get the maximum number of columns this database allows in an index.

getMaxColumnsInOrderBy

Get the maximum number of columns this database allows in an ORDER BY clause.

getMaxColumnsInSelect

Get the maximum number of columns this database allows in a SELECT list.

getMaxColumnsInTable

Get the maximum number of columns this database allows in a table.

getMaxConnections

Get the maximum number of concurrent connections to this database that are possible.

getMaxCursorNameLength

Get the maximum number of characters that this database allows in a cursor name.

getMaxIndexLength

Get the maximum number of bytes this database allows for an index, including all of the parts of the index.

getMaxProcedureNameLength

Get the maximum number of characters that this database allows in a procedure name.

getMaxRowSize

Get the maximum number of bytes this database allows in a single row.

getMaxSchemaNameLength

Get the maximum number of characters that this database allows in a schema name.

getMaxStatementLength

Get the maximum number of characters this database allows in an SQL statement.

getMaxStatements

Get the maximum number of active statements to this database that can be open at the same time.

getMaxTableNameLength

Get the maximum number of characters this database allows in a table name.

getMaxTablesInSelect

Get the maximum number of tables this database allows in a SELECT statement.

getMaxUserNameLength

Get the maximum number of characters this database allows in a user name.

getNumericFunctions

Get a comma-separated list of math functions available with this database.

getSchemas

Get the schema names available in this database.

getSchemaTerm

Get the database vendor's preferred term for "schema".

getSearch Escape

Get the string that can be used to escape wildcard characters.

getSQLKeywords

Get a comma-separated list of all of this database's SQL keywords that are NOT also SQL:2003 keywords.

getSQLStateType

Indicates whether the SQLSTATE returned by SQLException.getSQLState is X/Open (now known as Open Group) SQL CLI or SQL:2003.

getTablePrivileges

Get a description of the access rights for each table available in a catalog.

getTables

Get a description of the tables available in the given catalog.

getTableTypes

Get the table types available in this database.

getTimeDateFunctions

Get a comma-separated list of the time and date functions available with this database.

getTypeInfo

Get a description of all the data types supported by this database.

getURL

Get the URL for this DBMS.

getUserName

Get the user name as known to this database.

isCatalogAtStart

Get whether a catalog appears at the start of a fully qualified table name.

nullPlusNonNullIsNull

Get whether this database supports concatenations between NULL and non-NULL values being NULL.

nullsAreSortedAtEnd

Get whether NULL values are sorted at the end regardless of sort order.

nullsAreSortedAtStart

Get whether NULL values are sorted at the start regardless of sort order.

nullsAreSortedHigh

Get whether NULL values are sorted high.

nullsAreSortedLow

Get whether NULL values are sorted low.

othersDeletesAreVisible

Get whether deletes made by others are visible.

othersInsertsAreVisible

Get whether inserts made by others are visible.

othersUpdatesAreVisible

Get whether updates made by others are visible.

ownDeletesAreVisible

Get whether a result set's own deletes are visible.

ownInsertsAreVisible

Get whether a result set's own inserts are visible.

ownUpdatesAreVisible

Get whether for the given type of ResultSet object, the result set's own updates are visible.

storesLowerCaseIdentifiers

Get whether this database treats mixed case unquoted SQL identifiers as case insensitive and stores them in lower case.

storesLowerCaseQuotedIdentifiers

Get whether this database treats mixed case quoted SQL identifiers as case insensitive and stores them in lower case.

storesMixedCaseIdentifiers

Get whether this database treats mixed case unquoted SQL identifiers as case insensitive and stores them in mixed case.

storesMixedCaseQuotedIdentifiers

Get whether this database treats mixed case quoted SQL identifiers as case insensitive and stores them in mixed case.

storesUpperCaseIdentifiers

Get whether this database treats mixed case unquoted SQL identifiers as case insensitive and stores them in upper case.

storesUpperCaseQuotedIdentifiers

Get whether this database treats mixed case quoted SQL identifiers as case insensitive and stores them in upper case.

supportsAlterTableWithAddColumn

Get whether this database supports ALTER TABLE with add column.

supportsAlterTableWithDropColumn

Get whether this database supports ALTER TABLE with drop column.

supportsANSI92EntryLevelSQL

Get whether this database supports the ANSI92 entry level SQL grammar.

supportsANSI92FullSQL

Get whether this database supports the ANSI92 full SQL grammar supported.

supportsANSI92IntermediateSQL

Get whether this database supports the ANSI92 intermediate SQL grammar supported.

supportsBatchUpdates

Get whether this database supports batch updates.

supportsCatalogsInDataManipulation

Get whether a catalog name can be used in a data manipulation statement.

supportsCatalogsInIndexDefinitions

Get whether a catalog name can be used in an index definition statement.

supportsCatalogsInPrivilegeDefinitions

Get whether a catalog name can be used in a privilege definition statement.

supportsCatalogsInProcedureCalls

Get whether a catalog name can be used in a procedure call statement.

supportsCatalogsInTableDefinitions

Get whether a catalog name can be used in a table definition statement.

supportsColumnAliasing

Get whether this database supports column aliasing.

supportsConvert

Get whether this database supports the JDBC scalar function CONVERT for the conversion of one JDBC type to another.

supportsConvert

Get whether this database supports the JDBC scalar function CONVERT for conversions between the JDBC types fromType and toType.

supportsCoreSQLGrammar

Get whether this database supports the ODBC Core SQL grammar.

supportsCorrelatedSubqueries

Get whether this database supports correlated subqueries.

supportsDataDefinitionAndDataManipulationTransactions

Get whether this database supports both data definition and data manipulation statements within a transaction.

supportsDataManipulationTransactionsOnly

Get whether this database supports only data manipulation statements within a transaction.

supportsDifferentTableCorrelationNames

Get whether, when table correlation names are supported, they are restricted to being different from the names of the tables.

supportsExpressionsInOrderBy

Get whether this database supports expressions in ORDER BY lists.

supportsExtendedSQLGrammar

Get whether this database supports the ODBC Extended SQL grammar.

supportsFullOuterJoins

Get whether this database supports full nested outer joins.

supportsGroupBy

Get whether this database supports some form of GROUP BY clause.

supportsGroupByBeyondSelect

Get whether this database supports using columns not included in the SELECT statement in a GROUP BY clause provided that all of the columns in the SELECT statement are included in the GROUP BY c

supportsGroupByUnrelated

Get whether this database supports using a column that is not in the SELECT statement in a GROUP BY clause.

supportsIntegrityEnhancementFacility

Get whether this database supports the SQL Integrity Enhancement Facility.

supportsLikeEscapeClause

Get whether this database supports specifying a LIKE escape clause.

supportsLimitedOuterJoins

Get whether this database provides limited support for outer joins.

supportsMinimumSQLGrammar

Get whether this database supports the ODBC Minimum SQL grammar.

supportsMixedCaseIdentifiers

Get whether this database treats mixed case unquoted SQL identifiers as case sensitive and as a result stores them in mixed case.

supportsMixedCaseQuotedIdentifiers

Get whether this database treats mixed case quoted SQL identifiers as case sensitive and as a result stores them in mixed case.

supportsMultipleOpenResults

Get whether it is possible to have multiple ResultSet objects returned from a CallableStatement object simultaneously.

supportsMultipleResultSets

Get whether this database supports getting multiple ResultSet objects from a single call to the method execute.

supportsMultipleTransactions

Get whether this database allows having multiple transactions open at once (on different connections).

supportsNamedParameters

Get whether this database supports named parameters to callable statements.

supportsNonNullableColumns

Get whether columns in this database may be defined as non-nullable.

supportsOpenCursorsAcrossCommit

Get whether this database supports keeping cursors open across commits.

supportsOpenCursorsAcrossRollback

Get whether this database supports keeping cursors open across rollbacks.

supportsOpenStatementsAcrossCommit

Get whether this database supports keeping statements open across commits.

supportsOpenStatementsAcrossRollback

Get whether this database supports keeping statements open across rollbacks.

supportsOrderByUnrelated

Get whether this database supports using a column that is not in the SELECT statement in an ORDER BY clause.

supportsOuterJoins

Get whether this database supports some form of outer join.

supportsPositionedDelete

Get whether this database supports positioned DELETE statements.

supportsPositionedUpdate

Get whether this database supports positioned UPDATE statements.

supportsResultSetConcurrency

Get whether this database supports the given concurrency type in combination with the given result set type.

supportsResultSetHoldability

Get whether this database supports the given result set holdability.

supportsResultSetType

Get whether this database supports the given result set type.

supportsSavepoints

Get whether this database supports savepoints.

supportsSchemasInDataManipulation

Get whether a schema name can be used in a data manipulation statement.

supportsSchemasInIndexDefinitions

Get whether a schema name can be used in an index definition statement.

supportsSchemasInPrivilegeDefinitions

Get whether a schema name can be used in a privilege definition statement.

supportsSchemasInTableDefinitions

Get whether a schema name can be used in a table definition statement.

supportsSelectForUpdate

Get whether this database supports SELECT FOR UPDATE statements.

supportsStatementPooling

Get whether this database supports statement pooling.

supportsStoredFunctionsUsingCallSyntax

Get whether this database supports invoking user-defined or vendor functions using the stored procedure escape syntax

supportsStoredProcedures

Get whether this database supports stored procedure calls that use the stored procedure escape syntax.

supportsSubqueriesInComparisons

Get whether this database supports subqueries in comparison expressions.

supportsSubqueriesInExists

Get whether this database supports subqueries in EXISTS expressions.

supportsSubqueriesInIns

Get whether this database supports subqueries in IN expressions.

supportsSubqueriesInQuantifieds

Get whether this database supports subqueries in quantified expressions.

supportsTableCorrelationNames

Get whether this database supports table correlation names.

supportsTransactionIsolationLevel

Get whether this database supports the given transaction isolation level.

supportsTransactions

Get whether this database supports transactions.

supportsUnion

Get whether this database supports SQL UNION.

supportsUnionAll

Get whether this database supports SQL UNION ALL.

usesLocalFilePerTable

Get whether this database uses a file for each table.

usesLocalFiles

Get whether this database stores tables in a local file.

Unsupported Features

  • Transaction statements

  • Cursors

  • Multiple result sets

  • Domains

  • Rules

  • Database procedures

  • Indexes

  • Keys

  • Constraints

Example

This example uses the JDBC interface to create a flight information database, then reads carrier information from the database.

Sequence

The key steps are:

  1. Set connection parameters and credentials:

    static final String JDBC_DRIVER = "ai.heavy.jdbc.HeavyAIDriver";
    static final String DB_URL = "jdbc::localhost:6274:heavyai";
    static final String USER = "myUserName";
    static final String PASS = "myPassWord";
  2. Register the JDBC driver:

    Class.forName(JDBC_DRIVER);
  3. Open a connection:

    server connection:conn = DriverManager.getConnection(DB_URL, USER, PASS);
  4. Create a table, add data, and query the database:

    stmt = conn.createStatement(); 
    String sql = "CREATE table flights(arr_timestamp timestamp, dep_timestamp timestamp, uniquecarrier varchar(50))";
    stmt.executeUpdate(sql); 
    sql = "insert into flights values('2017-04-23 06:30:0’, '2017-04-23 07:45:00’, 'Southwest’)";
    stmt.executeUpdate(sql);
    sql = "insert into flights values('2017-04-23 06:50:0’, '2017-04-23 09:45:00’, 'American’)";
    stmt.executeUpdate(sql);
    sql = "insert into flights values('2017-04-23 09:30:0’,’ 2017-04-23 12:45:00’, 'United’)";stmt.executeUpdate(sql); 
    sql = "SELECT uniquecarrier from flights";
    ResultSet rs = stmt.executeQuery(sql);
  5. Extract data from the result set:

    while (rs.next()) { 
      String uniquecarrier = rs.getString("uniquecarrier"); 
      System.out.println("uniquecarrier: " + uniquecarrier);
    }

Compile and Run

Dependency components are also required in the classpath.

javac Flights.java

java -cp /opt/heavyai/bin/heavyai-jdbc-6.4.1.jar;. Flights_Demo.java

Source Code

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Flights {
static final String JDBC_DRIVER = "ai.heavy.jdbc.HeavyAIDriver";

static final String USER = "admin";
static final String PASS = "HyperInteractive";

public static void main(String[] args) throws SQLException {
  Connection conn = null;
  Statement stmt = null;
  try {
    Class.forName(JDBC_DRIVER);

    conn = DriverManager.getConnection(DB_URL, USER, PASS);

    stmt = conn.createStatement();
    String sql = "CREATE TABLE if not exists flights_java(arr_timestamp timestamp, dep_timestamp timestamp, uniquecarrier varchar(50))";
    stmt.executeUpdate(sql);
    sql = "insert into flights_java values('2017-04-23 06:30:0', '2017-04-23 07:45:00', 'Southwest')";
    stmt.executeUpdate(sql);
    sql = "insert into flights_java values('2017-04-23 06:50:0', '2017-04-23 09:45:00', 'American')";
    stmt.executeUpdate(sql);
    sql = "insert into flights_java values('2017-04-23 09:30:0','2017-04-23 12:45:00', 'United')";
    stmt.executeUpdate(sql);
    sql = "SELECT uniquecarrier from flights_java";
    ResultSet rs = stmt.executeQuery(sql);

    sql ="delete from SMRY where etl_dttm < DATEADD('DAY', '-4', NOW()) and actv_ind = 'N'";
    stmt.executeUpdate(sql);

    while (rs.next()) {
      String uniquecarrier = rs.getString("uniquecarrier");
      System.out.println("uniquecarrier: " + uniquecarrier);
    }

    rs.close();
    stmt.close();
    conn.close();
  } catch (SQLException se) {
    //Handle errors for JDBC
    se.printStackTrace();
  } catch (Exception e) {
    e.printStackTrace();
    if (stmt != null)
        stmt.close();
    if (conn != null)
        conn.close();
   }
}

Last updated