JoSQL - SQL for Java Objects

What is JoSQL?

Version 2.2 now available.

JoSQL (SQL for Java Objects) provides the ability for a developer to apply a SQL statement to a collection of Java Objects. JoSQL provides the ability to search, order and group ANY Java objects and should be applied when you want to perform SQL-like queries on a collection of Java Objects.

For example, to find all the HTML files that have been modified in December 2004:

SELECT *
FROM   java.io.File
WHERE  name $LIKE "%.html"
AND    toDate (lastModified) BETWEEN toDate ('01-12-2004') 
AND    toDate ('31-12-2004')

Now to do this in Java code would require the creation of a custom function that will allow the comparison between 2 java.io.File objects. Doesn't sound hard? Probably not, but what if you now wanted to also compare by the length of the file as well? That is a hard-code change that will then require recompilation and testing. However with JoSQL it's as easy as adding:

AND length > 20000

to the end of the query.

JoSQL treats each object as a "row" from a table/view and thus each of the accessible methods/fields from the object are therefore "columns" that can be accessed. JoSQL takes this one step further by allowing the full object-tree to be accessed. Taking java.io.File as an example again, it is possible to perform searches on the last modified time of the "parent" File, thus:

SELECT *
FROM   java.io.File
WHERE  toDate (parentFile.lastModified) BETWEEN toDate ('01-12-2004') 
AND    toDate ('31-12-2004')
AND    parentFile.name = "local"

Quick Start

For those who don't have time to read the manual, here's a quick example to get you started.

// Get a list of java.io.File objects.
List myObjs = getMyObjects ();

// Create a new Query.
Query q = new Query ();

// Parse the SQL you are going to use.
q.parse ("SELECT * FROM java.io.File WHERE name LIKE '%.java'");

// Execute the query.
QueryResults qr = q.execute (myObjs);

// Cycle over the query results.
List res = qr.getResults ();

for (int i = 0; i < res.size (); i++)
{

    File f = (File) res.get (i);

    // Do something with the File.

}

And a more advanced example.

// Get a list of java.io.File objects.
List myObjs = getMyObjects ();

// Create a new Query.
Query q = new Query ();

// Parse the SQL you are going to use, it is assumed here that 
// "myObjs" contains instances of "java.io.File".
q.parse ("SELECT name,length " +
         "FROM   java.io.File " +
         "WHERE  fileExtension (name) = :fileExt " +
         "ORDER BY length DESC, name " +
         "EXECUTE ON RESULTS avg (:_allobjs, length) avgLength");

// Set the bind variable "fileExt".
q.setVariable ("fileExt", "java");

// Execute the query.
QueryResults qr = q.execute (myObjs);
  
// Get the average length, this is a save value, the result
// of executing the call "avg (:_allobjs, length)", it is saved against
// key: "avgLength".
Map saveValues = qr.getSaveValues ();
Number avg = (Number) saveValues.get ("avgLength");

// Cycle over the results.
List res = qr.getResults ();

for (int i = 0; i < res.size (); i++)
{

    // This time there is a List for each row, index 0 holds the name of 
    // the file that matched, index 1 holds the length.
    List r = (List) res.get (i);

    System.out.println ("NAME: " + r.get (0));
    System.out.println ("LENGTH: " + r.get (1) + ", AVG: " + avg);

}

What JoSQL ISN'T

A common misconception about JoSQL is that it is a database tool. It's not, it is merely a way of using SQL to perform data selection, grouping and ordering operations on Java objects. Java currently has no method of performing searching, sorting and grouping without writing far too much code.

JoSQL is designed to bridge the gap. It should not be used to replace a standard RDBMS or even a OODBMS (such as db4o, although I wish they would support SQL queries, then I might consider using it ;), JoSQL has it's place and should be an addition to your toolkit not a replacement for any particular item.