JoSQL User Manual - Introduction

Introduction

There are many Java applications where the use of SQL statements to perform searching/ordering/grouping and selecting of values from Java Objects would be very valuable. However often times the use of a fully-featured SQL database (free or otherwise) is either in-appropriate and/or costly.

It would still be useful to be able to perform SQL like queries on Java Objects however, JoSQL is designed to "fill the gap". JoSQL uses SQL to perform operations on objects that are useful to Java programmers and as such diverges from "normal" RDBMS SQL in some areas, you have been warned!

For example, given a set of java.io.File objects to try and return just a subset of File objects that match certain criteria and sort them generally requires at least 2 "chunks" of code, usually a tailor-made Comparator and a method(s) to "filter" the File objects for the criteria.

JoSQL provides both of those functions (and more!) in a single operation with NO custom-code required from the application developer, thus:

SELECT *
FROM   java.io.File
WHERE  (name LIKE "%.html"
        OR
        name LIKE "%.txt"
        OR
        name LIKE "%.xml"
       )
AND    lastModified BETWEEN toDate('01-1-2004') AND toDate('31-1-2004')
AND    length >= 10000
ORDER BY lastModified DESC, name, length DESC

Then passing this statement to JoSQL:

Query q = new Query ();
q.parse (sqlStatement);

// Find list of "java.io.File" objects
List myFiles = getMyFiles ();
List results = q.execute (myFiles);

The results List would then contain all the java.io.File objects that matched the criteria AND they would be ordered according to the "ORDER BY" clause.

And that was all performed in 3 simple lines of Java!

JoSQL is also designed to get around SQLs aggregate function issues. JoSQL does not have any notion of "aggregate functions", a function is just a function. But it would be very useful to have "aggregate functionality" mixed with standard SQL. This is perfectly possible with JoSQL, i.e.:

/*
 * Find all Files that have a length that is greater than or equal
 * to the average length of ALL files in the set AND
 * the length of the name of the File is greater than or equal
 * to the average length of ALL files in the set.
*/
SELECT *
FROM   java.io.File
WHERE  length >= avg (:_allobjs, length)
AND    length (name) >= avg (:_allobjs, length (name))

There is no easy way to do this in a "normal" RDBMS SQL statement, which is a shame, to perform this kind of query you would first have to determine the avg and max values then make a further call, or perhaps use inner-selects.

Of course, the above would be VERY inefficient for moderately large datasets. However JoSQL has methods to prevent this (and of course it would be nice to have those values available for use AFTER the query has completed).