JDBC driver for Coherence

Okay this might be a crazy idea but as Coherence is a data source it was worth writing a JDBC driver for it. Of course spending time to write a complete SQL engine would be too much without any real motivation. But a simple proof of concept was on the cards. So here you go:

First thing is the client:

public static void main (String [] args) {
   // -- Create a URL: hostname and port being of the proxy
   String url = "jdbc:coh:@localhost:9099";
   String sql = "SELECT * FROM Positions";
        
   try {
      // -- Load the Coherence Database Driver
      Class.forName("com.ezduck.driver.GridDriver");
      Connection conn = DriverManager.getConnection(url);
      Statement stmt = conn.createStatement();
      stmt.executeQuery(sql);
   } catch (ClassNotFoundException e) {
      e.printStackTrace();
   } catch (SQLException e) {
      e.printStackTrace();
   }
}

Now the Driver:

public class GridDriver implements Driver {

    static {
        try {
            DriverManager.registerDriver(new GridDriver ());
        } catch (SQLException e) {
            // TODO
        }
    }
    // -- "jdbc:coh:@localhost:9099"
    public GridDriver() {
    }

    public Connection connect(String url, Properties info) {
        int hostIndex = url.indexOf("@") + 1;
        int portIndex = url.lastIndexOf(":");
        if (hostIndex < 0 || portIndex < 0) {
            throw new IllegalStateException ("Driver URL incorrect");
        }
        String hostName = url.substring(hostIndex, portIndex);
        String port = url.substring(portIndex + 1);
        return new GridConnection (hostName, port);
    }
    ... 
}

Hmm Now the Connection:

public class GridConnection implements Connection {
    
    public GridConnection(String hostName, String port) {
        ConfigurableCacheFactory factory = CacheFactory.getConfigurableCacheFactory();
        XmlElement cc = new SimpleElement("cache-config");
        
        XmlElement sMap = cc.addElement("caching-scheme-mapping");
        XmlElement cMap  = sMap.addElement ("cache-mapping");
        cMap.addElement("cache-name").setString("*");
        cMap.addElement("scheme-name").setString("extend-dist");
         
        XmlElement rcs = 
            cc.addElement("caching-schemes").addElement("remote-cache-scheme");
        rcs.addElement("scheme-name").setString ("extend-dist");
        rcs.addElement("service-name").setString("ExtendTcpCacheService");
        XmlElement xE = 
            rcs.addElement("initiator-config").addElement("tcp-initiator")
                .addElement("remote-addresses");
        XmlElement rAs = xE.addElement("socket-address");
        XmlElement add = rAs.addElement("address");
        add.setString(hostName);
        XmlElement portId = rAs.addElement("port");
        portId.setString(port);

        // -- Now set the new XmlElement   
        factory.setConfig(cc);
        // -- And of course you need to restart the Cache Service thread
        factory.ensureService("ExtendTcpCacheService").stop();
        factory.ensureService("ExtendTcpCacheService").start();                        
    }

    public Statement createStatement() {
        return new GridStatement();
    }
    
    .......
}

Then the Statement:

public class GridStatement implements Statement {
    
    public GridStatement() {
    }

    public ResultSet executeQuery(String sql) {
        String command = sql.substring(0, sql.indexOf (" "));
        try {
            // -- There must be a way to recognize the SQL Command
            ICommand cObject = (ICommand) Beans.instantiate(
                this.getClass().getClassLoader(), 
                this.getClass().getPackage().getName() + "." + 
                                command.toUpperCase().trim());
            Set set = (Set) cObject.execute (cObject, sql);
            // -- A utility to parse the SQL 
            Map map = SQLUtil.parseSelect(sql);
            String cacheName = (String) map.get ("tables");
            // -- And last the ResultSet
            ResultSet rS = new GridResultSet (set, cacheName);
            
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }
    .......
}

And before the ResultSet, lets see a half cooked SQL SELECT:

public class SELECT implements ICommand {
    String token[];

    // -- Select * From Dist-A where name = 'A';

    public SELECT() {
    }

    public Object execute(Object obj, String command) {
        Map map = SQLUtil.parseSelect(command);
        NamedCache nCache = CacheFactory.getCache((String)map.get("tables"));
        String clauses = (String)map.get("clauses");
        // -- Lets demonstrate the Select * before going crazy
        if (clauses == null) {
            return nCache.entrySet(AlwaysFilter.INSTANCE);
        }
        return null;
    }
}

Last but not least - The ResultSet:

public class GridResultSet implements ResultSet {

    private Iterator iter;
    private Set set;
    private String cacheName;
    private InvocableMap.Entry entry;

    public GridResultSet(Set set, String cacheName) {
        this.set = set;
        this.cacheName = cacheName;
        iter = set.iterator();
    }

    public boolean next() {
        entry = (InvocableMap.Entry) iter.next();
        return iter.hasNext();
    }
    ......
}

So here you go - another weekend project for Coherence!