JDBC Driver 4.2 is Released!

The JDBC API was the first way to connect and execute queries on databases in Java and is still widely used today. To connect to a specific database, you add the relevant driver to the classpath which implements the JDBC API. On the other side, the application code only uses the SQL language: JDBC acts as a façade, shielding the code from the specifics of the database.

JDBC being a standard API means that many libraries, frameworks, and tools (e.g. Spring Data JPA, sqlline, Tableau) use it for integration with a whole lot of different datastores.

Hazelcast JDBC driver leverages the Client API of the Hazelcast JAR, which makes it a Type 2 Driver. For more information about Driver Types, please refer to this tutorial. To download the Hazelcast JDBC driver, visit our Integrations Hub.

Integrating with Spring

As an example let’s try to use Hazelcast JDBC driver with Spring Data JDBC. In a Spring Boot project we will need to add dependencies:

<!-- Hazelcast JDBC Driver -->
<dependency>
    <groupId>com.hazelcast</groupId>
    <artifactId>hazelcast-jdbc</artifactId>
    <version>4.2</version>
</dependency>
<!-- Spring Data JDBC -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>

In our example, we are going to use the Embedded deployment model. Hence, we need to add one additional dependency:

<dependency>
    <groupId>com.hazelcast</groupId>
    <artifactId>hazelcast-sql</artifactId>
    <version>4.2</version>
</dependency>

Finally, add the following lines to your application.properties:

spring.datasource.url=jdbc:hazelcast://localhost:5701
spring.datasource.driver-class-name=com.hazelcast.jdbc.Driver

Before starting the Spring Boot Application, let’s create the file named hazelcast.yaml in the resources with the following configuration:

hazelcast:
  cluster-name: dev

This step is enough for Spring Boot to start an embedded Hazelcast instance. Now, let’s define a bean that will insert some data to the IMap called person:

@Bean
@Order(1)
public CommandLineRunner dataInsert(HazelcastInstance hazelcastInstance) {
  return args -> {
    IMap<Integer, Person> people = hazelcastInstance.getMap("person");
    people.put(1, new Person("John", 23));
    people.put(2, new Person("Mary", 35));
    people.put(3, new Person("Amber", 15));
    people.put(4, new Person("Matthew", 68));
    people.put(5, new Person("Carol", 45));
    people.put(6, new Person("Carolyn", 45));
  };
}

And a bean that will query our IMap using JdbcTemplate:

@Bean
@Order(2)
public CommandLineRunner queryData(JdbcTemplate jdbcTemplate) {
  return args -> {
    List<Map<String, Object>> maps = jdbcTemplate.queryForList("SELECT * FROM person");
    log.info(maps);
    List<Person> allPeople = jdbcTemplate.query("SELECT * FROM person", DataClassRowMapper.newInstance(Person.class));
    log.info(allPeople);
    Integer age = jdbcTemplate.queryForObject("SELECT age FROM person WHERE name = 'Matthew'", Integer.class);
    log.info(age);
    List<Person> person = jdbcTemplate.query("SELECT * FROM person WHERE name = ?", DataClassRowMapper.newInstance(Person.class), "Carolyn");
    log.info(person);
  };
}

Spring JDBC doesn’t recognize Hazelcast as a SQL dialect (yet!), hence we need to implement one:

public class CustomJdbcDialectProvider implements DialectResolver.JdbcDialectProvider {

    @Override
    public Optional<Dialect> getDialect(JdbcOperations operations) {
        return Optional.ofNullable(
                operations.execute((ConnectionCallback<Dialect>) CustomJdbcDialectProvider::getDialect));
    }

    private static Dialect getDialect(Connection connection) throws SQLException {
        DatabaseMetaData metaData = connection.getMetaData();
        String name = metaData.getDatabaseProductName().toLowerCase(Locale.ROOT);
        if (name.contains("hazelcast")) {
            return HazelcastDialect.INSTANCE;
        }
        return null;
    }

    private static class HazelcastDialect implements Dialect {
        private static final HazelcastDialect INSTANCE = new HazelcastDialect();

        @Override
        public LimitClause limit() {
            return new LimitClause() {
                public String getLimit(long limit) {
                    return "LIMIT " + limit;
                }
                public String getOffset(long offset) {
                    return "OFFSET " + offset;
                }
                public String getLimitOffset(long limit, long offset) {
                    return String.format("LIMIT %d OFFSET %d", limit, offset);
                }
                public Position getClausePosition() {
                    return Position.AFTER_ORDER_BY;
                }
            };
        }

        @Override
        public LockClause lock() {
            return null;
        }

        @Override
        public SelectRenderContext getSelectContext() {
            return new SelectRenderContext() {};
        }
    }
}

Now we can start our application and see the results printed on the logs.

Sqlline example

Now, let’s connect to our server using sqlline (please follow the manual for running sqlline tool):

$ ./sqlline -d com.hazelcast.jdbc.Driver -u jdbc:hazelcast://localhost:5701

You will see the clients logs and the prompt that confirms that you are connected to the Hazelcast server:

0: jdbc:hazelcast://localhost:5701>

Let’s execute a simple query:

0: jdbc:hazelcast://localhost:5701> SELECT * FROM person;
+-------+-----+---------+
| __key | age | name    |
+-------+-----+---------+
| 2     | 35  | Mary    |
| 6     | 45  | Carolyn |
| 1     | 23  | John    |
| 5     | 45  | Carol   |
| 4     | 68  | Matthew |
| 3     | 15  | Amber   |
+-------+-----+---------+
6 rows selected (0.004 seconds)

On one side, we were able to leverage Spring Boot and the Hazelcast JDBC driver to load data, and on the other side, we were able to query the data!

What’s next?

JDBC drivers are widely used as a low-level standard for interaction with databases. Even high-level standards, such as JPA and ORMs, use JDBC under the hood. With the addition of the Hazelcast JDBC Driver, you can use any of the above with Hazelcast. Moreover, our Hazelcast SQL features are now even more easily integrated.