JDBC Driver
GridGain is shipped with JDBC drivers that allow processing of distributed data using standard SQL statements like SELECT
, INSERT
, UPDATE
or DELETE
directly from the JDBC side.
Presently, there are two drivers supported by GridGain: the lightweight and easy to use JDBC Thin Driver described in this document and JDBC Client Driver that interacts with the cluster by means of a client node.
JDBC Thin Driver
The JDBC Thin driver is a default, lightweight driver provided by GridGain. To start using the driver, just add ignite-core-8.5.13.jar
to your application’s classpath.
The driver connects to one of the cluster nodes and forwards all the queries to it for final execution. The node handles the query distribution and the result’s aggregations. Then the result is sent back to the client application.
The JDBC connection string may be formatted with one of two patterns: URL query
or semicolon
:
// URL query pattern
jdbc:ignite:thin://<hostAndPortRange0>[,<hostAndPortRange1>]...[,<hostAndPortRangeN>][/schema][?<params>]
hostAndPortRange := host[:port_from[..port_to]]
params := param1=value1[¶m2=value2]...[¶mN=valueN]
// Semicolon pattern
jdbc:ignite:thin://<hostAndPortRange0>[,<hostAndPortRange1>]...[,<hostAndPortRangeN>][;schema=<schema_name>][;param1=value1]...[;paramN=valueN]
-
host
is required and defines the host of the cluster node to connect to. -
port_from
is the beginning of the port range to use to open the connection. 10800 is used by default if this parameter is omitted. -
port_to
is optional. It is set to theport_from
value by default if this parameter is omitted. -
schema
is the schema name to access. PUBLIC is used by default. This name should correspond to the SQL ANSI-99 standard. Non-quoted identifiers are not case sensitive. Quoted identifiers are case sensitive. When semicolon format is used, the schema may be defined as a parameter with name schema. -
<params>
are optional.
The name of the driver’s class is org.apache.ignite.IgniteJdbcThinDriver
. For instance, this is how you can open a JDBC connection to the Ignite cluster node listening on IP address 192.168.0.50:
// Register JDBC driver.
Class.forName("org.apache.ignite.IgniteJdbcThinDriver");
// Open the JDBC connection.
Connection conn = DriverManager.getConnection("jdbc:ignite:thin://192.168.0.50");
Parameters
The following table lists all the parameters that are supported by the JDBC connection string:
Parameter | Description | Default Value |
---|---|---|
user |
Username for the SQL Connection. This parameter is required if authentication is enabled on the server. See the Authentication and CREATE user documentation for more details. |
ignite |
|
Password for SQL Connection. Required if authentication is enabled on the server. See the Authentication and CREATE user documentation for more details. |
|
|
Whether to execute distributed joins in the non-collocated mode. |
false |
|
Whether to enforce join order of tables in the query. If set to |
|
|
Set this parameter to |
|
|
Whether the query contains only replicated tables. This is a hint for potentially more effective execution. |
|
|
Whether to close server-side cursors automatically when the last piece of a result set is retrieved. When enabled, calling |
|
|
Socket send buffer size. When set to 0, the OS default will be used. |
|
|
Socket receive buffer size. When set to 0, the OS default will be used. |
|
|
Whether to use |
|
|
Lazy query execution. By default, GridGain attempts to get and load the whole query result set into memory and then 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, then it can lead to excessive GC pauses and even 'OutOfMemoryError’s. Use this flag to tell GridGain to fetch the result set lazily, thus minimizing memory consumption at the cost of a moderate performance hit. |
|
|
Enables server side updates.
When GridGain executes a DML operation, it fetches all the affected intermediate rows and sends them to the query initiator (also known as reducer) for analysis. Then it prepares batches of updated values to be sent to remote nodes.
This approach might impact performance and it can saturate the network if a DML operation has to move many entries over it.
Use this flag to tell GridGain to perform all intermediate row analysis and updates "in-place" on corresponding remote data nodes.
Defaults to |
|
|
Enables SSL connection. Available modes:
|
|
|
Protocol name for secure transport. If not specified, TLS protocol will be used. Protocol implementations supplied by JSEE: |
|
|
The Key manager algorithm to be used to create a key manager. Note that in most cases the default value is sufficient.
Algorithms implementations supplied by JSEE: |
|
|
URL of the client key store file.
This is a mandatory parameter since SSL context cannot be initialized without a key manager.
If |
The value of the |
|
Client key store password. If |
The value of the |
|
Client key store type used in context initialization. If |
The value of the |
|
URL of the trust store file. This is an optional parameter; however, one of these properties must be set: If |
The value of the |
|
Trust store password. If |
The value of the |
|
Trust store type. If |
The value of the |
|
Disables server’s certificate validation. Set to |
|
|
Class name of the custom implementation of the If |
|
Connection String Examples
-
jdbc:ignite:thin://myHost
- connect to myHost on the port 10800 with all defaults. -
jdbc:ignite:thin://myHost:11900
- connect to myHost on custom port 11900 with all defaults. -
jdbc:ignite:thin://myHost:11900;user=ignite;password=ignite
- connect to myHost on custom port 11900 with user credentials for authentication. -
jdbc:ignite:thin://myHost:11900;distributedJoins=true&autoCloseServerCursor=true
- connect to myHost on custom port 11900 with enabled distributed joins and autoCloseServerCursor optimization. -
jdbc:ignite:thin://myHost:11900/myschema;
- connect to myHost on custom port 11900 and access to MYSCHEMA. -
jdbc:ignite:thin://myHost:11900/"MySchema";lazy=false
- connect to myHost on custom port 11900 with disabled lazy query execution and access to MySchema (schema name is case sensitive).
Multiple Endpoints
You can enable automatic failover if a current connection is broken by setting multiple connection endpoints in the connection string. JDBC Driver randomly picks an address from the list to connect to. If the original connection fails, JDBC Driver will select another address from the list until the connection is restored. JDBC Driver stops reconnecting and throws an exception if all the endpoints are unreachable.
The example below shows how to pass three addresses via the connection string:
// Register JDBC Driver.
Class.forName("org.apache.ignite.IgniteJdbcThinDriver");
// Open the JDBC connection passing several connection endpoints.
Connection conn = DriverManager.getConnection(
"jdbc:ignite:thin://192.168.0.50:101, 192.188.5.40:101, 192.168.10.230:101");
Cluster Configuration
In order to accept and process requests from JDBC Thin Driver, a cluster node binds to a local network interface on port 10800 and listens to incoming requests.
Use ClientConnectorConfiguration
, set via IgniteConfiguration
, to change parameters:
IgniteConfiguration cfg = new IgniteConfiguration()
.setClientConnectorConfiguration(new ClientConnectorConfiguration());
<bean id="ignite.cfg" class="org.apache.ignite.configuration.IgniteConfiguration">
<property name="clientConnectorConfiguration">
<bean class="org.apache.ignite.configuration.ClientConnectorConfiguration" />
</property>
</bean>
The following parameters are supported:
Parameter | Description | Default Value |
---|---|---|
|
Host name or IP address to bind to. When set to |
|
|
TCP port to bind to. If the specified port is already in use, GridGain will try to find another available port using the |
|
|
Defines number of ports to try to bind to. E.g. if the port is set to |
|
|
Maximum number of cursors that can be opened simultaneously for a single connection. |
|
|
Number of request-handling threads in the thread pool. |
|
|
Size of the TCP socket send buffer. When set to 0, the system default value is used. |
|
|
Size of the TCP socket receive buffer. When set to 0, the system default value is used. |
|
|
Whether to use |
|
|
Idle timeout for client connections. Clients will be disconnected automatically from the server after remaining idle for the configured timeout. When this parameter is set to zero or a negative value, the idle timeout is disabled. |
|
|
Whether access through JDBC is enabled. |
|
|
Whether access through thin client is enabled. |
|
|
If SSL is enabled, only SSL client connections are allowed. The node allows only one mode of connection: |
|
|
Whether to use SSL context factory from Ignite configuration (see |
|
|
Whether client authentication is required. |
|
|
The class name that implements |
|
Using SSL
JDBC Thin Driver allows you to use SSL socket communication to provide a secure connection between the JDBC driver and the node (includes the initial handshake).
See the ssl*
parameters of the JDBC driver, and ssl*
parameters and useIgniteSslContextFactory
of the ClientConnectorConfiguration
for more detailed information.
The default implementation is based on JSSE, and works through two Java keystore files:
-
sslClientCertificateKeyStoreUrl
- the client certificate keystore holds the keys and certificate for the client. -
sslTrustCertificateKeyStoreUrl
- the trusted certificate keystore contains the certificate information to validate the server’s certificate.
The trusted store is an optional parameter, however one of the following parameters: sslTrustCertificateKeyStoreUrl
or sslTrustAll
must be configured.
If you want to use your own implementation or method to configure the SSLSocketFactory
, you can use JDBC Driver’s sslFactory
parameter. It is a string that must contain the name of the class that implements the interface Factory<SSLSocketFactory>
. The class must be available for JDBC Driver’s class loader.
Ignite DataSource
The DataSource object is used as a deployed object that can be located by logical name via the JNDI naming service. JDBC Driver’s org.apache.ignite.IgniteJdbcThinDataSource
implements a JDBC DataSource interface allowing you to utilize the DataSource interface instead.
In addition to generic DataSource properties, IgniteJdbcThinDataSource
supports all the Ignite-specific properties that can be passed into a JDBC connection string. For instance, the distributedJoins
property can be (re)set via the IgniteJdbcThinDataSource#setDistributedJoins()
method.
Refer to the JavaDocs for more details.
Examples
To start processing the data located in the cluster, you need to create a JDBC Connection object via one of the methods below:
// Open the JDBC connection via DriverManager.
Connection conn = DriverManager.getConnection("jdbc:ignite:thin://192.168.0.50");
// Or open connection via DataSource.
IgniteJdbcThinDataSource ids = new IgniteJdbcThinDataSource();
ids.setUrl("jdbc:ignite:thin://192.168.0.50");
ids.setDistributedJoins(true);
Connection conn2 = ids.getConnection();
Then you can execute SQL SELECT queries of your choice:
// 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 also modify the data via DML statements.
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");
Streaming
JDBC Driver allows streaming data in bulk using the SET
command. See the SET
command documentation for more information.
Error Codes
Ignite JDBC drivers pass error codes in the java.sql.SQLException
class, used to facilitate exception handling on the application side. To get an error code, use the java.sql.SQLException.getSQLState()
method. It returns a string containing the ANSI SQLSTATE error code defined:
// Register JDBC driver.
Class.forName("org.apache.ignite.IgniteJdbcThinDriver");
// Open JDBC connection.
Connection conn = DriverManager.getConnection("jdbc:ignite:thin://127.0.0.1");
PreparedStatement ps;
try {
ps = conn.prepareStatement("INSERT INTO Person(id, name, age) values (1, 'John', 'unparseableString')");
} catch (SQLException e) {
switch (e.getSQLState()) {
case "0700B":
System.out.println("Conversion failure");
break;
case "42000":
System.out.println("Parsing error");
break;
default:
System.out.println("Unprocessed error: " + e.getSQLState());
break;
}
}
The table below lists all the ANSI SQLSTATE error codes currently supported by GridGain. Note that the list may be extended in the future.
Code | Description |
---|---|
0700B |
Conversion failure (for example, a string expression cannot be parsed as a number or a date). |
0700E |
Invalid transaction isolation level. |
08001 |
The driver failed to open a connection to the cluster. |
08003 |
The connection is in the closed state. Happened unexpectedly. |
08004 |
The connection was rejected by the cluster. |
08006 |
I/O error during communication. |
22004 |
Null value not allowed. |
22023 |
Unsupported parameter type. |
23000 |
Data integrity constraint violation. |
24000 |
Invalid result set state. |
0A000 |
Requested operation is not supported. |
40001 |
Concurrent update conflict. See Concurrent Updates. |
42000 |
Query parsing exception. |
50000 |
Internal error.
The code is not defined by ANSI and refers to an Ignite specific error. Refer to the |
© 2020 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.