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. |
Installing the Connector
The JDBC connector is included in the full and slim distributions of Hazelcast.
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 |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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 |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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 |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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 |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|