JDBC V1 Driver - In-Memory Data Grid

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.

This JDBC is intended for very high performance simple queries. While JDBC V3 is a bit heavier it does offer full SQL 99 coverage.

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

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. The SQL support is limited, therefore this path should be taken with caution.

Getting the GigaSpaces JDBC V1 Connection

In order to get the 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");

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) INDEX,SUP_ID INTEGER INDEX, " +
                     "PRICE FLOAT INDEX,SALES INTEGER INDEX,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);
}

Embedding the Query Processor Within the Application

By default, the Query Processor is running server side. It is possible to set the Query Processor to run embedded within the application by passing a parameter to the JDBC driver:

Class.forName("com.j_spaces.jdbc.driver.GDriver").newInstance();
String url = "jdbc:gigaspaces:url:jini://*/*/mySpace";
Properties properties = new Properties();
properties.put("com.gs.embeddedQP.enabled", "true");
conn = DriverManager.getConnection(url, properties);

Transaction Support

The GigaSpaces JDBC Driver supports the following transaction managers:

• Local Transaction Manager
• Distributed embedded Jini Transaction Manager (default)
• Lookup Distributed Transaction Manager

The transaction manager type can be configured via JDBC's connection properties (there are additional properties for lookup distributed tx manager):

Transaction Manager Type Configuration Example:

Class.forName("com.j_spaces.jdbc.driver.GDriver");
Properties props = new Properties();
props.put("gs.tx_manager_type", "distributed");
Connection conn = DriverManager.getConnection("jdbc:gigaspaces:url:jini://*/*/mySpace", props);

Getting the JDBC V1 Connection from a Space Proxy

You can get an GigaSpaces JDBC connection from a Space proxy using the com.j_spaces.jdbc.driver.GConnection. See the following example:

IJSpace gsSpaceProxy;  //your space proxy. You can get it using GigaSpace.getSpace()
GConnection connection = GConnection.getInstance(gsSpaceProxy);
connection.setUseSingleSpace(true); //false = cluster, true=single

The setUseSingleSpace method allows you to get a JDBC connection that encapsulates a clustered proxy or to an embedded Space proxy.

You can also use the following GConnection method to set the user and password for a secured Space:

public static Connection getInstance(IJSpace space, String username, String password)
    throws SQLException

Combining the Space API with the JDBC API

The following example uses 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 send the JDBC query to be executed within the Space. This approach scales very well when the Space has multiple partitions, eliminating the need to retrieve the actual Space objects from the Space to evaluate the query. Retrieving objects from the Space involves 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:

Step 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 on the client side.

Step 2. The DistributedTask.reduce method running on the client side aggregates the results from all the partitions and sorts the final set.

public class JDBCTask implements DistributedTask<String[], String[]>{

    public JDBCTask(String queryStr){
        this.queryStr=queryStr;
    }

    @TaskGigaSpace
    transient GigaSpace gigaspace;
    transient Statement stmt;
    transient PreparedStatement perstmt;
    transient Connection con;
    String queryStr;

    public String[] execute() throws Exception{

        Connection con = getConnection();
        stmt = con.createStatement();
        ArrayList<String> result = new ArrayList<String> ();
        ResultSet rs =stmt.executeQuery(queryStr);
        while (rs.next()) {
            result.add(rs.getString(1));
        }
        stmt.close();
        con.close();
        String resultArr [] = new String[result.size()] ;
        resultArr = result.toArray(resultArr);
        return resultArr;
    }

    public String[] reduce(List<AsyncResult<String[]>> results) throws Exception {
        Iterator<AsyncResult<String[]>> iter = results.iterator();
        String[] result_ = null;
        HashSet<String> result = new HashSet<String>();
        while (iter.hasNext())
        {
            result_ = iter.next().getResult();
            for (int i=0;i<result_.length;i++)
            {
                result.add(result_[i])  ;
            }

        }

        String[] fullResult = new String[result.size()];
        fullResult = result.toArray(fullResult);
        Arrays.sort(fullResult);
        return fullResult;
    }

    public Connection getConnection()
    {
        GConnection connection = null;
        try {
            Class.forName("com.j_spaces.jdbc.driver.GDriver");

        } catch(java.lang.ClassNotFoundException e) {
            System.err.print("ClassNotFoundException: ");
            System.err.println(e.getMessage());
        }

        try {
            connection = GConnection.getInstance(gigaspace.getSpace());
            connection.setUseSingleSpace(true); //false = cluster, true=single

        } catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }

        return connection;
    }
}

GigaSpace gigapace =new GigaSpaceConfigurer(new SpaceProxyConfigurer("mySpace")).gigaSpace();
AsyncFuture<String[]> result= gigapace.execute(new JDBCTask("select str from " +MyClass.class.getName() + " group by str"));
String[] result_str = result.get();
System.out.println("The Result:" + Arrays.asList(result_str));

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 (clears the data but does not drop the schema).
• AND/OR operators to join two or more conditions in a WHERE clause.
• NOT IN and NOT NULL
• Aggregate functions: COUNT, MAX, MIN, SUM, AVG.
• All basic logical operations to create conditions: =, <>, <,>, >=, <=, [NOT] like, is [NOT] null, IN, BETWEEN.
• 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 (inner left Join) - Join supports the selection of multiple tables. It 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.
• GROUP BY for multiple columns.
• DISTINCT (although not when used with functions or aggregations)
• Column aliases.

Connection conn;
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT ID AS Identifier, NAME AS FullName FROM PERSON WHERE Identifier = 210");

• 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.
• Remote and embedded query processes configuration – allows fast access to the space using embedded mode.
• Increment a field in an UPDATE statement.

Connection conn;
Statement stmt = conn.createStatement();
int result = stmt.executeUpdate("UPDATE PERSON SET VERSION = VERSION + 1 WHERE ID = 10000");

• A statement caching mechanism is provided to speed up statement parsing.
• Support SYSTABLES.
• Connection pool.
• All JDBC basic types including Blob and Clob.

Regular Expression

GigaSpaces supports query using regular expression. You may use like or rlike expressions with your JDBC V1 queries.

When using the SQL like operator you may use the following: % - match any string of any length (including zero length) _ - match on a single character

Querying the Space using the Java Regular Expression provides more options than the SQL like operator. The Query syntax is done using the rlike operator.

When you search for Space objects with String fields that include a single quote, your query should use Parameterized Query. In the following example, we are searching for all Data objects that include the value today's with their myTextField:

PreparedStatement st = con.prepareStatement("select id,text,text2 from MyData WHERE text rlike ?");
st.setString(1, "today\u0027s.*");
ResultSet rs = st.executeQuery();

Text Search

GigaSpaces products have full text search capability, leveraging the Lucene search engine library supporting the Lucene Query Parser Syntax except Fields.

Text search queries are available through the text: extension:

    Connection connection = GConnection.getInstance(gigaSpace.getSpace());

    // search 'java'
    String sql = "SELECT * from NewsArticle where title text:match ?";
    PreparedStatement pst = connection.prepareStatement(sql);
    pst.setObject(1, "java");
        
    ResultSet executeQuery = pst.executeQuery();
        
/....        

Indexing

It is highly recommended to use indexes on relevant properties to increase performance when using equality, bigger/less than, BETWEEN, IN, LIKE, NOT LIKE, and IS NULL statements. For more information, see Indexing. The above supported query features can leverage indexes except for the is NOT null and NOT INstatements.

Partitioning Support

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

1. A POJO with a @SpaceRouting field can be sent to the space via the snapshot call prior to calling the JDBC V1 API.
2. Create the table through JDBC; the first index as part of the CREATE TABLE statement will be the routing field.
3. 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 V1 select statement nested fields. These could be a Map type fields or user-defined data type fields within the Space object. See the following example of a Space class with a nested Map and a nested object fields. Both are indexed:

public class MyData implements Serializable{

    public MyData(){}
    String data1;
    String data2;
    // getter and setter methods
}

@SpaceClass
public class MyClass {
    public MyClass (){}
    Integer num;
    String str;
    Integer id;
    HashMap<String, MyData> map; // a map within the space object
    MyData data; // an object within the space object

    @SpaceIndex (type=SpaceIndexType.EQUAL)
    public Integer getNum() {
        return num;
    }
    public void setNum(Integer num) {
        this.num = num;
    }

    @SpaceIndex (type=SpaceIndexType.EQUAL)
    public String getStr() {
        return str;
    }
    public void setStr(String str) {
        this.str = str;
    }

    @SpaceId (autoGenerate = false)
    @SpaceRouting
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }

    @SpaceIndexes({@SpaceIndex(path="key1" , type = SpaceIndexType.EQUAL),
            @SpaceIndex(path="key2" , type = SpaceIndexType.EQUAL)})
    public HashMap<String, MyData> getMap() {
        return map;
    }
    public void setMap(HashMap<String, MyData> map) {
        this.map = map;
    }

    @SpaceIndexes({@SpaceIndex(path="data1" , type = SpaceIndexType.EQUAL),
            @SpaceIndex(path="data2" , type = SpaceIndexType.EQUAL)})
    public MyData getData() {
        return data;
    }
    public void setData(MyData data) {
        this.data = data;
    }
}

The following is an example for a JDBC V1 query call you can use with the above Space object. Both the nested Map and nested object fields are used with the JDBC V1 query below:

String queryString =
"select uid,
map.key1.data1, map.key1.data2,
map.key2.data1, map.key2.data2,
data.data1, data.data2,str
from
org.test.MyClass where
map.key1.data1='aa' and
map.key1.data2='bb' and
map.key2.data1='cc' and
map.key2.data2='dd' and
data.data1 = 'ee' and
data.data1 = 'ff' and
str='ABCD'";
stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(queryString );
while (rs.next()) {
    ...
}

Unsupported Features

The GigaSpaces JDBC V1 Driver does not support the following:

• The SQL statements: HAVING, VIEW, TRIGGERS, EXISTS, BETWEEN in collections, 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.
• 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, however the Aggregators functionality can be used.
• Using a non constant right-hand side comparison operator. This can be implemented via Custom Aggregation.
• LEFT [OUTER] JOIN
• RIGHT [OUTER] JOIN
• [INNER] JOIN
• Statement::setFetchSize()

Joins with JDBC V1

When using Joins it is preferable to use JDBC V3 as this ensures that the Joins are performed more efficiently. In addition, more Join options are supported with JDBC V3.

JDBC V1 Reserved Words

This section lists the JDBC V1 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 RLIKE 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 ~ < <= >  >= => != <> \(+\) ( ) \* / + - ? \{ \}

Configuration

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

Example:

<os-core:embedded-space  id="space" space-name="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:embedded-space>

JDBC V1 Error Codes

The following is a list of JDBC V1 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