nce in a while something comes along that is so simple, so straightforward, and so obvious that it’s amazing that nobody did it long ago. Take blogging, for example: People have been putting their thoughts into various media for a long time, certainly long before the Web. Give someone the facility to easily put their thoughts online, with a mechanism in RSS that standardizes the format and allows others to access it, and you have a facility to publish your thoughts to the whole world. And thus blogs were born.

JoSQL is like that. But before we get into why, there’s one thing that should be cleared up. Despite the name, JoSQL isn’t a database server, or a toolkit for database access via JDBC, or anything like that. It’s an engine that gives you to access your in-memory objects using SQL. Now, while that doesn’t have the mass market appeal of blogs, to a developer it is such an amazingly useful tool that after using it, you’ll wonder how high-level computing languages such as C# or Java have been around so long without this facility.

To recap: It’s an engine that allows you to use SQL syntax to access your in-memory objects, so, you could, for example, do something like this:

SELECT * from com.devx.mywidgets WHERE text='hello'

This code would operate on your collection of ‘mywidgets’ and return a resultset that references each instance where the text property is ‘hello’.

Think about how useful this can be to your applications. What if you want to skin an application by setting the color property of all your labels to one color and your panels to another color? You can run a query, pull your labels, set their color, run another query, pull your panels, and set their color. Very simple.

Alternatively, how about an application that has a number of objects in memory that represent connected clients, such as what you might find in a chat server? To perform a function such as, ‘Find all clients on my server that are administrators and send them the message x,’ you would have to iterate through each client, check its permissioning bits, and if they match the administrative patterns, you send them the message. JoSQL may not be more efficient than this, but it does make your code a lot cleaner.

In the following sections I’ll walk you through some examples showing how it works and how it can be used in such a scenario.

Getting Started with JoSQL
JoSQL is an open source project that may be downloaded here. At this site, you’ll also find some documentation and examples of how to use it. I found these examples to be a little too light and too high level, leaving you with a lot of figuring left to do (albeit with the aid of the excellent JavaDocs). Despite this, if you use a good IDE that has auto-complete then you can generally figure out a lot of the API for yourself.

To get started, download the JoSQL package and copy the JARS to a library directory. There are two JARs that you need: the JoSQL one (presently called JoSQL1-1.jar) and a third-party dependency called gentlyWeb utilities (gentlyWEB-utils-1.1.jar).

Then, from your Java code, add an import to reference JoSQL like this:

import org.josql.*;

And you’re ready to go.

Accessing Objects using SQL
JoSQL allows you to access your objects using a SQL-like syntax. A simple example is shown here:

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

This SQL is passed to a Query object, which acts on a java.util.List object, or one of its derivatives, such as a Vector or ArrayList. (Click here to see the java.util.List documentation.) ). This action will return a QueryResults object from which you can derive your classes.

As with SQL, you’re not limited to the SELECT * FROM X WHERE Y=Z type syntax. JoSQL runs the full gamut of SQL commands, so you have the typical ‘GROUP BY,’ ‘HAVING,’ ‘ORDER BY,’ and ‘LIMIT’ clauses that you would expect.

You can also parameterize your SQL, passing objects into the code as parameters using JoSQL bind variables. These are parameters that are prefixed with a “:” in the SQL string, and then set using the setVariable method on the query object. For example, take a look at the following query:

    SELECT name,           length    FROM   java.io.File    WHERE  name LIKE '*.java'     AND    lastModified BETWEEN :fromDate AND :toDate

In this case, the fromDate, the toDate, and the path parameters are all bind variables. To set these in code you would need to write:

Query q = new Query();q.parse(strSQL);q.setVariable ("fromDate", sdf.parse ("10-10-2000"));q.setVariable ("toDate", sdf.parse ("10-10-2001"));QueryResults res = q.execute (myFiles);

Putting It in Action
The download that comes with this article includes a very simple console application that demonstrates how JoSQL can be used. It emulates a chat server application, which is a classic example where you have many objects in memory. These chat examples includes some cool advanced features that you can restrict, if desired, to cut back on the coding needed for implementation.

A typical chat server has a number of clients that connect to it, and these clients will have different properties and attributes. Typically, a client-type object will represent the client, exposing properties for that client such as their names and permissions. This object will also have methods for actions that should be performed on the client, such as sending a message to the client, so that any incoming messages from other clients in the same ‘room’ as that client can be dispatched to it from the server. Or to provide a disconnect message, where the server can boot the client after a period of inactivity or for behavior that breaks server policies.

Under this structure, if a server has hundreds of clients connected, the code needed to iterate through the clients can bloat enormously, as there can be little reuse. For example, if you want to write a function to say ‘Hello’ to everyone called ‘Bob’ or something similar, you would generally write a function that takes the pattern to match (in this case ‘Bob’) and the message to send. This function would then iterate through the entire list of in-memory clients, finding ones that match ‘Bob,’ and then invoke their sendMessage functions. Easy enough.

But what if you want to do the same for, say, the room that they are in? In other words, you want to send a message to everybody in room 1, or everybody in rooms 1,3, and 5, or everybody in rooms >17. You would have to write another (similar) function to handle iterating through the rooms, as well as parsing routines to handle the different cases. The result is more code for you to churn out, debug, and support.

Here’s where JoSQL is worth its weight in gold. Consider the above use cases (and take a look at them in the download). To send a message to everyone whose name is like ‘Bob’ with JoSQL you simply write:

Query q = new Query();q.parse("Select * from devx.article.josql.SimClient WHERE          p_ClientName LIKE 'Bob'");QueryResults clClients = q.execute(myList);List test = clClients.getWhereResults();ListIterator iter = test.listIterator();while(iter.hasNext()){  SimClient c = (SimClient) iter.next();  c.SendMessage("Hi Bob");}

In this case, the chat server uses a class called SimClient to model a client. It keeps an ArrayList of connected clients called myList. It constructs a new Query Object and then sets its SQL to select all clients where the p_ClientName property is LIKE ‘Bob’. Note that the SQL LIKE functionality is enough to filter out names beginning with Bob, containing Bob, or ending with Bob. No custom coding is required. It then executes this SQL on the myList ArrayList, to return a list of matching objects. You can then iterate through this list and send a message to everyone in it. Very simple.

Here’s some additional power: consider this function, which allows you to match any property on any of your objects to any value, drastically simplifying your code:

private static void DoAction(String strWHEREVAL, String strLIKEVAL,                               String strWhatToSay)  {      Query q = new Query();    try    {      String strSQL = "Select * from devx.article.josql.SimClient";      if (strWHEREVAL!="")      {          strSQL+=" WHERE " + strWHEREVAL;                }      if(strLIKEVAL!="")      {          strSQL+=" LIKE " + strLIKEVAL;                }      q.parse(strSQL);      QueryResults clClients = q.execute(myList);      List test = clClients.getWhereResults();      ListIterator iter = test.listIterator();      while(iter.hasNext())      {        SimClient c = (SimClient) iter.next();        c.SendMessage(strWhatToSay);      }    }     catch(Exception e)    {      System.out.println(e.toString());    }  }

So now if you want to write routines that perform actions on you objects, you don’t have to write accessor functions for each, you can just call this DoAction function and pass it snippets of SQL to meet the required criteria. So, from your chat server, to send messages to everyone whose name is like ‘Bob’, you can simply use:

DoAction("p_ClientName","'Bob'", "Hi Bob!, This is Bob");

Where p_ClientName is the public property on the client object that you are checking for.

Similarly, you could send a message to everybody in room 1 using:

DoAction("p_CurrentRoom=1","", "Greetings to Room1");

And because this is SQL, it’s very simple to put some pretty complex filters in there such as:

DoAction(   "(p_CurrentRoom>1 AND p_CurrentRoom<=8)", ""    "Greetings to everyone between 1 and 8");

As you can see, using the in-built SQL interpreter to filter your objects can save you a lot of code, and, in many cases allow you to add new features to your application that you wouldn't have wanted to tackle before (because of the volume of code involved).

This is such an innovative and powerful addition to your Java toolbox, that, if you write applications that maintain large numbers of objects, such as the chat example I've discussed, you'll wonder how you ever got on without it. JoSQL is relatively new, having started as an open source project in December 2004. I wouldn't be surprised if Sun were to add this type of functionality to the core Java APIs soon?it's just that good!

JoSQL is a powerful addition to any developer's toolbox. It's not for every application, but should be useful in most cases. Its performance seems ok, though there are no benchmarks available; in some simple tests I did, it performed admirably. It's greatest usefulness is in simplifying your code, allowing you to access your objects using SQL strings instead of coding your own filters. As such, it's amazingly innovative, and has little or no learning curve.

The JoSQL Web site includes thorough documentation on the SQL API, as well as some good JavaDocs on the class libraries themselves. Perhaps the only let-down is in the lack of example applications that will help you to get up and running quickly. The information is all there, however, so a little digging and you'll be JoSQLing away to your heart's content!