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.
OverviewThe 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.
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 SystemsThe 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 connectionIn 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");
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); }
Getting JDBC connection from a Space ProxyYou 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 APIThe 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: Select FIELD from CLASS group by FIELD The result is then sent into the reducer running at the client side.
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 MappingThe GigaSpaces JDBC Driver translates in runtime a Space object into a relational table representation.
The following information represents the SQL to Java mapping conducted at runtime when a table is created via the JDBC driver.
Supported FeaturesGigaSpaces JDBC supports the following:
Unsupported FeaturesGigaSpaces JDBC does not support the following:
Partitioning SupportIn 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:
Nested Field QueryYou 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 WordsHere is a list of JDBC reserved keywords, data types, separators and operators: KeywordsALTER 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 Typesdate 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 ~ < <= > >= => != <> \(+\) ( ) \* / + - ? \{ \} ConfigurationThe 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> JDBC Error CodesList of JDBC error codes and their descriptions: -101: Can't alter table |
![]() |
GigaSpaces.com - Legal Notice - 3rd Party Licenses - Site Map - API Docs - Forum - Downloads - Blog - White Papers - Contact Tech Writing - Gen. by Atlassian Confluence |