Summary: GigaSpaces allows applications to connect to the IMDG using a JDBC driver. A GigaSpaces JDBC driver accepts SQL statements, translates them to space operations, and returns standard result sets.

Overview

The GigaSpaces JDBC interface allows database-driven applications to interact with spaces via SQL queries and commands. A query processor transparently translates SQL queries into legal space operations. No integration is required - all you need to do is point the application to the GigaSpaces JDBC driver like any other JDBC driver.

Applications can access the GigaSpaces Data Grid using the JDBC API; data written to the IMDG using the JDBC API can also be accessed using other APIs.

An alternative way of querying the space using SQL syntax is the SQLQuery class. This class allows you to perform SQL queries directly against space objects, without adding O/R mapping complexity.

JDBC support in GigaSpaces is centered around the Space-Based Architecture - its main motivation is to enable more sophisticated querying of the space, beyond the template matching provided by the The GigaSpace Interface.

GigaSpaces is not a full-fledged relational database and it does not support the full SQL92 standard (see JDBC Supported Features). However, the existing SQL support is extremely useful for applications that need to execute queries on a space for real-time queries.

Using Existing SQL Code and Porting to External Systems

The JDBC interface is mostly used to enable access to the space through standard SQL tools and programming interfaces. You can write SQL commands against the space, and the same code will in many (simple) cases be compatible with other SQL implementations.

Porting existing JDBC code to the space is certainly doable, but would require some level of adaptation depending on the specifics of the case and the complexity of the SQL queries. For legacy applications, it may still be easier than porting existing code to leverage the space technology directly. Since the SQL support is limited, this path should be taken with caution, and would normally require close support from GigaSpaces.

Getting the GigaSpaces JDBC connection

In order to get the GigaSpaces JDBC connection you should use the following code to register the JDBC Driver:

Class.forName("com.j_spaces.jdbc.driver.GDriver");

The connection URL should include :jdbc:gigaspaces:url:<Space URL> – e.g.:

Mixing Space API with the JDBC API

The following example using the Space API DistributedTask with the JDBC API. With this example we use map/reduce approach to query the space using the JDBC API, but we send the JDBC query to be executed within the space. This approach scales very well once the space have multiple partitions avoiding the need to retrieve the actual space objects from the space to evaluate the query. Retrieving objects from the space involved network latency and serialization overhead.

With the example below we execute the following query:

Select FIELD from CLASS group by FIELD sort by FIELD

The query is executed in two phases:
1. A DistributedTask is sent to each partition to execute the following JDBC query:

Select FIELD from CLASS group by FIELD

The result is then sent into the reducer running at the client side.
2. The DistributedTask.reduce method running at the client side aggregating the results from all the partitions and sort the final set.

Nested fields query - You may use as part of the select statement nested fields within collections (maps) or objects within the Space object.

Multiple tables select - Starting with XAP 7.0.1 the join feature supports the selection of multiple tables (previous versions supported select with only two tables). The join feature uses the cartesian product of the tables data to form the result set. The join will perform well when having tables with small/medium size (up to 1,000,000 rows).

ORDER BY for multiple columns.

Table aliases – tables are allowed to use aliases throughout the query.

sysdate - a keyword suggesting current time and date.

rownum - a keyword to use in WHERE clauses, setting the number of rows to select.

Select for update – allowing the locking of rows in order to update them later.

The UPDATE statement does not allow the use of an expression or a null value in the SET clause.

Using a constant instead of the column name.

The INSERT statement does not allow the use of an expression in the VALUES clause.

"." used to indicate a double data type.

Using mathematical expressions in the WHERE clause.

Using a sub-query in the FROM clause.

LEFT [OUTER] JOIN

RIGHT [OUTER] JOIN

[INNER] JOIN

When having SELECT count (*) FROM myClass JDBC query – myClass sub classes object count are not taken into consideration when processing the query result. The SELECT count (*) FROM myClass WHERE X=Y and SELECT (*) from myClass do take into consideration myClass sub classes objects when processing the result. Future versions will resolve this inconsistency.
As a workaround, construct a JDBC query that includes a relevant WHERE part.

Partitioning Support

In order to partition the data and rout operations to the correct partition you should specify a "routing column" for each table. The "routing column" is specified through one of three mechanisms:

A POJO with a @SpaceRouting field can be sent to the space via the snapshot call prior to calling the JDBC API.

Create the table through JDBC; the first index as part of the CREATE TABLE statement will be the routing field.

If there is no indexed column, the first column as part of the CREATE TABLE will be the routing field.

Nested Field Query

You may use as part of the JDBC select statement nested fields. These could be Map type fields or user defined data type fields within the Space object. See below example for a space class with a nested Map and a nested object fields. Both are indexed:

JDBC Reserved Words

Here is a list of JDBC reserved keywords, data types, separators and operators:

Keywords

ALTER ADD AND ASC BETWEEN BY CREATE CALL DROP DEFAULT_NULL DESC DISTINCT END FROM GROUP IN IS LIKE MAX MIN NOT
NULL OR ORDER SELECT SUBSTR SUM SYSDATE UPPER WHERE COUNT DELETE EXCEPTION ROWNUM INDEX INSERT INTO SET TABLE
TO_CHAR TO_NUMBER FOR_UPDATE UPDATE UNION VALUES COMMIT ROLLBACK PRIMARY_KEY UID USING

JDBC Error Codes

List of JDBC error codes and their descriptions:100: No (more) data0: Successful Completion

-101: Can't alter table-102: Table <tableName> does not exist-103: Commit/Rollback failed-104: Can't delete row-105: Table does not exist-106: Remote Exception occurred-107: Failed to drop table-108: All values must be set in a Prepared Statement-109: Prepared value already set!-110: Prepared value missing!-111: Invalid data-112: Invalid type for the specified column-113: Unknown columns-114: Unknown table in condition-115: Can't set same value more than once-116: Unknown column for IN condition-117: Unknown execution type-118: Table already exists-119: Wrong data type in SUM function-120: Error in rownum-121: Select failed-122: The selected column does not exist in the selected tables-123: No such column for given alias-124: Order by column should be in select list-125: Must specify the column to return the sum of.-126: All values must be set-127: Wrong type for given column-128: Incorrect number of values to insert-129: Type mismatch in nested query-130: Can't update row!-131: Blob cannot hold null data-132: Command not supported-133: Both parameters should be greater than 1-134: Clob cannot hold null data-135: Can't convert clob to ascii, unsupported encoding-136: Substring out of clob's bounds-137: Error creating connection - Unknown host-138: Error creating connection or reading QP properties-139: Cannot commit an autocommit connection-140: Cannot rollback an autocommit connection-141: The given URL is not supported-142: Prepared statement must contain prepared values-143: Cannot call SELECT with executeUpdate. Use executeQuery instead-144: Cannot set a null object-145: Cannot set object, unknown type-146: Used executeQuery instead of executeUpdate-147: Cannot set a value-148: Cannot represent this value as byte-149: Cannot represent this value as double-150: Cannot represent this value as float-151: Cannot represent this value as int-152: Cannot represent this value as long-153: Cannot represent this value as short-154: Cannot represent this value as boolean-155: Column found in result-156: Cannot represent this value as Blob-157: Cannot represent this value as Clob-158: Cannot represent this value as Date-159: Cannot represent this value as Time-160: Cannot represent this value as Timestamp-161: The next() method must be called at least once-162: Exhausted ResultSet-201: Invalid SQL syntax