JDBC Client Driver

JDBC Client Driver interacts with the cluster by means of a client node.

JDBC Client Driver

The JDBC Client Driver connects to the cluster by using a client node connection. You must provide a complete Spring XML configuration as part of the JDBC connection string, and copy all the JAR files mentioned below to the classpath of your application or SQL tool:

All the JARs under {GRIDGAIN_HOME}\libs directory.
All the JARs under {GRIDGAIN_HOME}\ignite-indexing and {GRIDGAIN_HOME}\ignite-spring directories.

The driver itself is more robust, and might not support the latest SQL features of Ignite. However, because it uses the client node connection underneath, it can execute and distribute queries, and aggregate their results directly from the application side.

The JDBC connection URL has the following pattern:

jdbc:ignite:cfg://[<params>@]<config_url>

Where:

<config_url> is required and must represent a valid URL that points to the configuration file for the client node. This node will be started within the Ignite JDBC Client Driver when it (the JDBC driver) tries to establish a connection with the cluster.
<params> is optional and has the following format:

param1=value1:param2=value2:...:paramN=valueN

The name of the driver’s class is org.apache.ignite.IgniteJdbcDriver. For example, here’s how to open a JDBC connection to the Ignite cluster:

// Registering the JDBC driver.
Class.forName("org.apache.ignite.IgniteJdbcDriver");

// Opening JDBC connection (cache name is not specified, which means that we use default cache).
Connection conn = DriverManager.getConnection("jdbc:ignite:cfg://config/ignite-jdbc.xml");

Supported Parameters

Parameter Description Default Value

Parameter	Description	Default Value
`cache`	Cache name. If it is not defined, then the default cache will be used. Note that the cache name is case sensitive.	None.
`nodeId`	ID of node where query will be executed. Useful for querying through local caches.	None.
`local`	Query will be executed only on a local node. Use this parameter with the `nodeId` parameter in order to limit data set by specified node.	`false`
`collocated`	Flag that is used for optimization purposes. Whenever GridGain executes a distributed query, it sends subqueries to individual cluster members. If you know in advance that the elements of your query selection are colocated together on the same node, Ignite can make significant performance and network optimizations.	`false`
`distributedJoins`	Allows use of distributed joins for non-colocated data.	`false`
`streaming`	Turns on bulk data load mode via INSERT statements for this connection. Refer to the Streaming Mode section for more details.	`false`
`streamingAllowOverwrite`	Tells GridGain to overwrite values for existing keys on duplication instead of skipping them. Refer to the Streaming Mode section for more details.	`false`
`streamingFlushFrequency`	Timeout, in milliseconds, that data streamer should use to flush data. By default, the data is flushed on connection close. Refer to the Streaming Mode section for more details.	`0`
`streamingPerNodeBufferSize`	Data streamer’s per node buffer size. Refer to the Streaming Mode section for more details.	`1024`
`streamingPerNodeParallelOperations`	Data streamer’s per node parallel operations number. Refer to the Streaming Mode section for more details.	`16`
`transactionsAllowed`	Presently ACID Transactions are supported, but only at the key-value API level. At the SQL level, GridGain supports atomic, but not transactional consistency. This means that the JDBC driver might throw a `Transactions are not supported` exception if you try to use this functionality. However, in cases when you need transactional syntax to work (even without transactional semantics), e.g. some BI tools might force the transactional behavior, set this parameter to `true` to prevent exceptions from being thrown.	`false`
`multipleStatementsAllowed`	JDBC driver will be able to process multiple SQL statements at a time, returning multiple `ResultSet` objects. If the parameter is disabled, the query with multiple statements fails.	`false`
`lazy`	Lazy query execution. By default, GridGain attempts to fetch the whole query result set to memory and send it to the client. For small and medium result sets, this provides optimal performance and minimizes the duration of internal database locks, thus increasing concurrency. However, if the result set is too big to fit in the available memory, it can lead to excessive GC pauses and even `OutOfMemoryError` errors. Use this flag to tell GridGain to fetch the result set lazily, thus minimizing memory consumption at the cost of a moderate performance hit.	`true`
`skipReducerOnUpdate`	Enables server side update feature. When GridGain executes a DML operation, it first fetches all of the affected intermediate rows for analysis to the query initiator (also known as reducer), and then prepares batches of updated values to be sent to remote nodes. This approach might impact performance and saturate the network if a DML operation has to move many entries over it. Use this flag as a hint for GridGain to perform all intermediate rows analysis and updates "in-place" on the corresponding remote data nodes. Defaults to `false`, meaning that intermediate results will be fetched to the query initiator first.	`false`
`queryMaxMemory`	Maximum amount of memory available to each query executed through the current connection. This parameter overrides the memory quota for queries.	none

cache

Cache name. If it is not defined, then the default cache will be used. Note that the cache name is case sensitive.

None.

nodeId

ID of node where query will be executed. Useful for querying through local caches.

None.

local

Query will be executed only on a local node. Use this parameter with the nodeId parameter in order to limit data set by specified node.

false

collocated

Flag that is used for optimization purposes. Whenever GridGain executes a distributed query, it sends subqueries to individual cluster members. If you know in advance that the elements of your query selection are colocated together on the same node, Ignite can make significant performance and network optimizations.

false

distributedJoins

Allows use of distributed joins for non-colocated data.

false

streaming

Turns on bulk data load mode via INSERT statements for this connection. Refer to the Streaming Mode section for more details.

false

streamingAllowOverwrite

Tells GridGain to overwrite values for existing keys on duplication instead of skipping them. Refer to the Streaming Mode section for more details.

false

streamingFlushFrequency

Timeout, in milliseconds, that data streamer should use to flush data. By default, the data is flushed on connection close. Refer to the Streaming Mode section for more details.

0

streamingPerNodeBufferSize

Data streamer’s per node buffer size. Refer to the Streaming Mode section for more details.

1024

streamingPerNodeParallelOperations

Data streamer’s per node parallel operations number. Refer to the Streaming Mode section for more details.

16

transactionsAllowed

Presently ACID Transactions are supported, but only at the key-value API level. At the SQL level, GridGain supports atomic, but not transactional consistency.

This means that the JDBC driver might throw a Transactions are not supported exception if you try to use this functionality.

However, in cases when you need transactional syntax to work (even without transactional semantics), e.g. some BI tools might force the transactional behavior, set this parameter to true to prevent exceptions from being thrown.

false

multipleStatementsAllowed

JDBC driver will be able to process multiple SQL statements at a time, returning multiple ResultSet objects. If the parameter is disabled, the query with multiple statements fails.

false

lazy

Lazy query execution.

By default, GridGain attempts to fetch the whole query result set to memory and send it to the client. For small and medium result sets, this provides optimal performance and minimizes the duration of internal database locks, thus increasing concurrency.

However, if the result set is too big to fit in the available memory, it can lead to excessive GC pauses and even OutOfMemoryError errors. Use this flag to tell GridGain to fetch the result set lazily, thus minimizing memory consumption at the cost of a moderate performance hit.

true

skipReducerOnUpdate

Enables server side update feature.

When GridGain executes a DML operation, it first fetches all of the affected intermediate rows for analysis to the query initiator (also known as reducer), and then prepares batches of updated values to be sent to remote nodes.

This approach might impact performance and saturate the network if a DML operation has to move many entries over it.

Use this flag as a hint for GridGain to perform all intermediate rows analysis and updates "in-place" on the corresponding remote data nodes.

Defaults to false, meaning that intermediate results will be fetched to the query initiator first.

false

queryMaxMemory

Maximum amount of memory available to each query executed through the current connection. This parameter overrides the memory quota for queries.

none

Streaming Mode

It’s feasible to add data into a cluster in streaming mode (bulk mode) using the JDBC driver. In this mode, the driver instantiates IgniteDataStreamer internally and feeds data to it. To activate this mode, add the streaming parameter set to true to a JDBC connection string:

// Register JDBC driver.
Class.forName("org.apache.ignite.IgniteJdbcDriver");

// Opening connection in the streaming mode.
Connection conn = DriverManager.getConnection("jdbc:ignite:cfg://streaming=true@file:///etc/config/ignite-jdbc.xml");

Presently, streaming mode is supported only for INSERT operations. This is useful in cases when you want to achieve fast data preloading into a cache. The JDBC driver defines multiple connection parameters that affect the behavior of the streaming mode. These parameters are listed in the parameters table above.

The parameters cover almost all of the settings of a general IgniteDataStreamer and allow you to tune the streamer according to your needs. Please refer to the Data Streaming section for more information on how to configure the streamer.

// Register JDBC driver.
   Class.forName("org.apache.ignite.IgniteJdbcDriver");

   // Opening a connection in the streaming mode and time based flushing set.
   Connection conn = DriverManager.getConnection("jdbc:ignite:cfg://streaming=true:streamingFlushFrequency=1000@file:///etc/config/ignite-jdbc.xml");

   PreparedStatement stmt = conn.prepareStatement(
     "INSERT INTO Person(_key, name, age) VALUES(CAST(? as BIGINT), ?, ?)");

   // Adding the data.
   for (int i = 1; i < 100000; i++) {
         // Inserting a Person object with a Long key.
         stmt.setInt(1, i);
         stmt.setString(2, "John Smith");
         stmt.setInt(3, 25);

         stmt.execute();
   }

   conn.close();

   // Beyond this point, all data is guaranteed to be flushed into the cache.

Example

To start processing the data located in the cluster, you need to create a JDBC Connection object using one of the methods below:

// Register JDBC driver.
Class.forName("org.apache.ignite.IgniteJdbcDriver");

// Open JDBC connection (cache name is not specified, which means that we use default cache).
Connection conn = DriverManager.getConnection("jdbc:ignite:cfg://file:///etc/config/ignite-jdbc.xml");

Right after that you can execute your SQL SELECT queries:

// Query names of all people.
ResultSet rs = conn.createStatement().executeQuery("select name from Person");

while (rs.next()) {
    String name = rs.getString(1);
}

// Query people with specific age using prepared statement.
PreparedStatement stmt = conn.prepareStatement("select name, age from Person where age = ?");

stmt.setInt(1, 30);

ResultSet rs = stmt.executeQuery();

while (rs.next()) {
    String name = rs.getString("name");
    int age = rs.getInt("age");
}

You can use DML statements to modify the data.

INSERT

// Insert a Person with a Long key.
PreparedStatement stmt = conn.prepareStatement("INSERT INTO Person(_key, name, age) VALUES(CAST(? as BIGINT), ?, ?)");

stmt.setInt(1, 1);
stmt.setString(2, "John Smith");
stmt.setInt(3, 25);

stmt.execute();

MERGE

// Merge a Person with a Long key.
PreparedStatement stmt = conn.prepareStatement("MERGE INTO Person(_key, name, age) VALUES(CAST(? as BIGINT), ?, ?)");

stmt.setInt(1, 1);
stmt.setString(2, "John Smith");
stmt.setInt(3, 25);

stmt.executeUpdate();

UPDATE

// Update a Person.
conn.createStatement().
  executeUpdate("UPDATE Person SET age = age + 1 WHERE age = 25");

DELETE

conn.createStatement().execute("DELETE FROM Person WHERE age = 25");

© 2024 GridGain Systems, Inc. All Rights Reserved. Privacy Policy | Legal Notices. GridGain® is a registered trademark of GridGain Systems, Inc.
Apache, Apache Ignite, the Apache feather and the Apache Ignite logo are either registered trademarks or trademarks of The Apache Software Foundation.

Last updated on Nov 20, 2024