Mapping to JDBC

To query external JDBC data stores, you can create a mapping to them with the JDBC connector.

What is the JDBC Connector

The JDBC connector allows you to connect to any database that supports the JDBC interface.

Supported Databases

The JDBC connector supports the following database management systems:

  • MySQL

  • PostgreSQL

  • Microsoft SQL Server

  • Oracle

You can use other database management systems with a JDBC interface. However, Hazelcast offers no guarantees or support for these systems.
If your table and column names include lower case characters, you must enclose the names in double quotes to prevent Oracle converting the names to upper case. If you do not enclose the table and column names in double quotes, use upper case when referring to them, for example, in Hazelcast mapping.

Supported SQL Statements

The JDBC connector supports only the following statements:

Installing the Connector

The JDBC connector is included in the full and slim distributions of Hazelcast.

Permissions

Enterprise

The JDBC connector does not yet support permissions.

Before you Begin

Before you can create a mapping to an external data store, you must have the following:

  • A JDBC driver that’s compatible with your data store. This driver must be on the classpath of your cluster members:

    • The full distribution of Hazelcast Enterprise comes with a JDBC driver for PostgreSQL data stores only.

    • The slim distribution of Hazelcast does not come with a JDBC driver.

    • The full distribution of Hazelcast Open Source comes with a JDBC driver for MySQL and PostgreSQL data stores.

  • A data store connection that’s configured on all cluster members.

  • Create the database that you’ll use as your external data store.

Creating a JDBC Mapping

The following example creates a mapping to a MySQL database.

In the MySQL database, create a people table.

CREATE TABLE people (id INT PRIMARY KEY, name VARCHAR(100))

Use the CREATE MAPPING statement to tell the JDBC connector the name of your data store configuration.

external-data-store:
  mysql-database: (1)
    class-name: com.hazelcast.datastore.JdbcDataStoreFactory
    properties:
      jdbcUrl: jdbc:mysql://dummy:3306
      username: xyz
      password: xyz
    shared: true
1 Configuration details for a MySQL database.

Create the mapping.

CREATE MAPPING people
TYPE JDBC (1)
OPTIONS (
  'externalDataStoreRef'='mysql-database' (2)
)
1 The name of the connector.
2 The name of the external data store configuration on your members.

Data Type Mapping Between Hazelcast and MySQL

Hazelcast supports a subset of SQL data types. For MySQL data types, see the official MySQL documentation. The following MySQL types are mapped to the respective Hazelcast SQL type:

MySQL Type Hazelcast SQL Type

VARCHAR

VARCHAR

CHAR

VARCHAR

TEXT

VARCHAR

BOOLEAN

BOOLEAN

SMALLINT

SMALLINT

INT

INTEGER

BIGINT

BIGINT

DECIMAL

DECIMAL

FLOAT

REAL

DOUBLE/DOUBLE PRECISION

DOUBLE

DATE

DATE

TIME

TIME

TIMESTAMP

TIMESTAMP

Data Type Mapping Between Hazelcast and PostgreSQL

For PostgreSQL data types, see the official PostgreSQL documentation. The following PostgreSQL types are mapped to the respective Hazelcast SQL type:

PostgreSQL Type Hazelcast SQL Type

varchar

VARCHAR

char

VARCHAR

bpchar

VARCHAR

text

VARCHAR

character varying

VARCHAR

boolean

BOOLEAN

smallint

SMALLINT

integer

INTEGER

bigint

BIGINT

numeric/decimal

DECIMAL

real

REAL

double precision

DOUBLE

date

DATE

time

TIME

timestamp

TIMESTAMP

Data Type Mapping Between Hazelcast and MSSQL

For MSSQL data types, see the official MSSQL documentation. The following MSSQL types are mapped to the respective Hazelcast SQL type:

MSSQL Type Hazelcast SQL Type

varchar

VARCHAR

bit

BOOLEAN

tinyint

TINYINT

smallint

SMALLINT

int

INTEGER

bigint

BIGINT

decimal

DECIMAL

real

REAL

float

DOUBLE

date

DATE

time

TIME

datetime

TIMESTAMP

datetimeoffset

TIMESTAMP WITH TIME ZONE

numeric

DECIMAL

char

VARCHAR

text

VARCHAR

Data Type Mapping Between Hazelcast and Oracle

For Oracle data types see the official Oracle documentation. The following Oracle types are mapped to the respective Hazelcast SQL type. NUMBER(p,s) represents a NUMBER type with a decimal precision of p and a scale of s that is greater than 0. NUMBER(p) has a scale of 0.

Oracle Type Hazelcast SQL Type

VARCHAR2

VARCHAR

from NUMBER(1) to NUMBER(4) inclusive

SMALLINT

from NUMBER(5) to NUMBER(9) inclusive

INTEGER

from NUMBER(10) to NUMBER(18) inclusive

BIGINT

NUMBER(p,s) where "s" is 0 and "p" is bigger than 18 or "s+p" is bigger than 15

DECIMAL

NUMBER(p,s) where "s+p" is smaller than 8

REAL

NUMBER(p,s) where "s+p" is smaller than 16

DOUBLE

DATE

DATE

TIMESTAMP

TIMESTAMP

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH TIME ZONE