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

note

You can use other database management systems with a JDBC interface. However, Hazelcast offers no guarantees or support for these systems.

note

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:

• SELECT
• INSERT INTO
• UPDATE
• DELETE

Installing the Connector

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

Permissions

Enterprise EditionThis feature requires Hazelcast Enterprise, a licensed edition of the Hazelcast Platform.

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 Edition 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 Community Edition 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.

sql

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

Define a data connection of type JDBC on all members:

yaml

hazelcast:
  data-connection:
    my-mysql-database:
      type: JDBC
      properties:
        jdbcUrl: jdbc:mysql://dummy:3306 (1)
        user: xyz
        password: xyz
      shared: true

1 Configuration details for a MySQL database.

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

Create the mapping.

sql

CREATE MAPPING people
DATA CONNECTION 'mysql-database' (1)

1 The name of the data connection 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
TINYTEXT	VARCHAR
MEDIUMTEXT	VARCHAR
LONGTEXT	VARCHAR
TEXT	VARCHAR
BOOLEAN	BOOLEAN
TINYINT	TINYINT
TINYINT UNSIGNED	SMALLINT
SMALLINT	SMALLINT
SMALLINT UNSIGNED	INT
MEDIUMINT	INT
MEDIUMINT UNSIGNED	INT
INT	INTEGER
INT UNSIGNED	BIGINT
BIGINT	BIGINT
BIGINT UNSIGNED	DECIMAL
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
character	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
datetime2	TIMESTAMP
smalldatetime	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
BINARY_FLOAT	REAL
NUMBER(p,s) where "s+p" is smaller than 16	DOUBLE
BINARY_DOUBLE	DOUBLE
DATE	DATE
TIMESTAMP	TIMESTAMP
TIMESTAMP WITH TIME ZONE	TIMESTAMP WITH TIME ZONE