XAP71 == "XAP9NET" || XAP71 == "XAP9" || XAP71 == "XAP9NET")

JDBC Driver

Download XAP

Search XAP 7.1
Searching XAP 7.1 Documentation
Browse XAP 7.1
Offline Documentation

Download latest offline documentation in HTML format:
xap-7.1-documentation.zip (19.4MB)

                                                              

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.:

Connection con = DriverManager.getConnection("jdbc:gigaspaces:url:jini://*/*/mySpace");
You may use the GigaSpaces JDBC driver with remote or embedded spaces.

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 pooling when using GigaSpaces JDBC driver.

Getting JDBC connection from a Space Proxy

You can get a GigaSpaces JDBC connection from a space proxy using the com.j_spaces.jdbc.driver.GConnection. For 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

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.

The DistributedTask implementation
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;
	}
}
The Client application
GigaSpace gigapace = 
	new GigaSpaceConfigurer(
		new UrlSpaceConfigurer ("jini://*/*/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.

SQL Type Java Type
VARCHAR, VARCHAR2 java.lang.String
CHAR java.lang.String
DATE java.sql.Date
DATE java.sql.Timestamp
TIME java.sql.Time
FLOAT java.lang.Float
DOUBLE java.lang.Double
BOOLEAN java.lang.Boolean
INTEGER java.lang.Integer
TIMESTAMP java.sql.Timestamp
LONG java.lang.Long
BLOB com.gigaspaces.jdbc.driver.Blob
CLOB com.gigaspaces.jdbc.driver.Clob

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.
  • 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.
  • 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 (although not when used with functions or aggregations)

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.

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:

  1. A POJO with a @SpaceRouting field can be sent to the space via the snapshot call prior to calling the JDBC 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 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:

The Inner Class
public class MyData implements Serializable{
	
	public MyData(){}
	String data1;
	String data2;
	// getter and setter methods
}
The Space Class with the nested Map and custom Field data type
@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.BASIC)
	public Integer getNum() {
		return num;
	}
	public void setNum(Integer num) {
		this.num = num;
	}
	
	@SpaceIndex (type=SpaceIndexType.BASIC)
	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.BASIC),
			@SpaceIndex(path="key2" , type = SpaceIndexType.BASIC)})
	public HashMap<String, MyData> getMap() {
		return map;
	}
	public void setMap(HashMap<String, MyData> map) {
		this.map = map;
	}
	
	@SpaceIndexes({@SpaceIndex(path="data1" , type = SpaceIndexType.BASIC),
			@SpaceIndex(path="data2" , type = SpaceIndexType.BASIC)})
	public MyData getData() {
		return data;
	}
	public void setData(MyData data) {
		this.data = data;
	}
}

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

The JDBC select Statement using nested objects
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()) {
	...
}

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 ~ < <= >  >= => != <> \(+\) ( ) \* / + - ? \{ \}

Configuration

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

Parameter Description Default Value
space-config.QueryProcessor.space_read_lease_time Read timeout. Millisec units. 0
space-config.QueryProcessor.space_write_lease Object lease timeout. Millisec units. 9223372036854775807L
space-config.QueryProcessor.transaction_timeout Millisec unit. Transaction Timeout. 30000
space-config.QueryProcessor.init_jmx Expose Tracing via JMX false
space-config.QueryProcessor.trace_exec_time   false
space-config.QueryProcessor.debug_mode Boolean value. When true show debug information. false
space-config.QueryProcessor.parser_case_sensetivity Boolean value. Determines if Column and Table names are case sensitive. true
space-config.QueryProcessor.auto_commit Boolean value. Auto Commit mode true
space-config.QueryProcessor.date_format   yyyy-MM-dd
space-config.QueryProcessor.datetime_format   yyyy-MM-dd hh:mm:ss
space-config.QueryProcessor.time_format   hh:mm:ss

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>

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

IMPORTANT: This is an old version of GigaSpaces XAP. Click here for the latest version.

Additional resources: XAP Application Server | XAP Data Grid | XAP for Cloud Computing | XAP J2EE Support

Labels

 
(None)