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 JDBC Driver classname:

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

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

Connection con = DriverManager.getConnection("jdbc:gigaspaces:url:jini://*/*/mySpace");

You may use the GigaSpaces JDBC driver with remote or embedded space

For more details on the Space URL, refer to the Space URL section.

Example:

Connection conn;
Class.forName("com.j_spaces.jdbc.driver.GDriver").newInstance();
String url = "jdbc:gigaspaces:url:jini://*/*/mySpace";
conn = DriverManager.getConnection(url);
Statement st = conn.createStatement();
String createTable = "CREATE TABLE COFFEES (COF_NAME VARCHAR(32),SUP_ID INTEGER, " + 
                     "PRICE FLOAT,SALES INTEGER,TOTAL INTEGER)";
st.executeUpdate(createTable);

String query = "SELECT COF_NAME, PRICE FROM COFFEES";
st = conn.createStatement();
ResultSet rs = st.executeQuery(query);
while (rs.next()) {
	String s = rs.getString("COF_NAME");
	float n = rs.getFloat("PRICE");
	System.out.println(s + "   " + n);
}

There is no need to deal with JDBC connection polling when using GigaSpaces JDBC driver.

SQL to Java Type Mapping

The GigaSpaces JDBC Driver translates in runtime a Space object into a relational table representation.

• All Java class attributes are translated into their corresponding SQL types.
• Class names are translated into table names.
• Field names are translated into column names.
• Indexed columns are translated into indexed fields. Make sure the btree index is turned on allowing fast processing of bigger than/less than queries. For more details, refer to the Indexing section.

The following information represents the SQL to Java mapping conducted at runtime when a table is created via the JDBC driver.

Supported Features

GigaSpaces JDBC supports the following:

• All Basic SQL statements: SELECT, INSERT, DELETE, UPDATE, CREATE TABLE, DROP TABLE.
• AND/OR operators to join two or more conditions in a WHERE clause.
• Aggregate functions: COUNT, MAX, MIN, SUM, AVG.
• All basic logical operations to create conditions: =, <>, <,>, >=, <=, [NOT] like, is [NOT] null, IN.
• 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.
• Remote and embedded query processes configuration – allows fast access to the space using embedded mode.
• Optimistic locking.
• A statement caching mechanism is provided to speed up statement parsing.
• Meta Data API.
• Connection pool.
• All JDBC basic types including Blob and Clob.
• GROUP BY for multiple columns.
• DISTINCT

Unsupported Features

GigaSpaces JDBC does not support the following:

• The SQL statements: HAVING, VIEW, TRIGGERS, EXISTS, BETWEEN, NOT, CREATE USER, GRANT, REVOKE, SET PASSWORD, CONNECT USER, ON.
• CREATE Database.
• CREATE Index, DROP Index.
• Constraints: NOT NULL, IDENTITY, UNIQUE, PRIMARY KEY, Foreign Key/REFERENCES, NO ACTION, CASCADE, SET NULL, SET DEFAULT, CHECK.
• Batch Processing.
• Set operations: Union, Minus, Union All.
• Aggregate Functions: STDEV, STDEVP, VAR, VARP, FIRST, LAST.
• 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.

Configuration

The JDBC Driver should be configured using the following properties. These should be part of the The Space Component configuration when deployed:

Example:

<os-core:space id="space" url="/.//space" >
    <os-core:properties>
        <props>
            <prop key="space-config.QueryProcessor.transaction_timeout">50000</prop>
            <prop key="space-config.QueryProcessor.date_format">yyyy-MM-dd</prop>
        </props>
    </os-core:properties>
</os-core:space>

Partitioning Support

In order to partition the data and rout operations to the correct partition you should specify for each Table its "routing column". The "routing column" is based on a matching POJO and Routing field decoation you should construct and introduce to the space via the snapshot call prior calling the JDBC API. For details about the Routing Field see the POJO Support - Advanced section.

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

Data Types

date datetime  time float double number decimal boolean integer varchar varchar2 char timestamp long 
clob blob empty_clob() empty_blob() lob true false

Separators and operators

:=  || ; . ROWTYPE ~ < <= >  >= => != <> \(+\) ( ) \* / + - ? \{ \}

JDBC Error Codes

List of JDBC error codes and their descriptions:
100: No (more) data
0: 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