This post originated from an RSS feed registered with Agile Buzz by Joe Walnes. Original Post: Easily build complicated SELECT statements with Squiggle Feed Title: Joe's New Jelly Feed URL: http://joe.truemesh.com/blog/index.rdf Feed Description: The musings of a ThoughtWorker obsessed with Agile, XP, maintainability, Java, .NET, Ruby and OpenSource. Mmm'kay? Latest Agile Buzz Posts Latest Agile Buzz Posts by Joe Walnes Latest Posts From Joe's New Jelly

After serving me loyally for four years, I've finally got around to open-sourcing Squiggle - a small Java library for dynamically building complicated SQL SELECT statements.

Sometimes (not often these days) you just need to get your hands dirty and write a beastly SELECT statement. Maybe a persistence layer is deemed overkill for your application, or maybe a persistence layer is struggling with the type of query you want to do. There are times when writing some SQL is the right thing to do.

Here's the blurb from the website:

Squiggle does one thing and only one thing. It generates SELECT statements based on criteria you give it. It's sweet spot is for applications that need to build up complicated queries with criteria that changes at runtime. Ordinarily it can be quite painful to figure out how to build this string. Squiggle takes much of this pain away.

The code for Squiggle is intentionally clean and simple. Rather than provide support for every thing you could ever do with SQL, it provides support for the most common situations and allows you to easily modify the source to suit your needs.

Features

• Concise and intuitive API.
• Simple code, so easy to customize.
• No dependencies on classes outside of JDK1.2.
• Small, lightweight, fast.
• Generates clean SQL designed that is very human readable.
• Supports joins and sub-selects.

Here's a very simple example:

Table people = new Table("people");

SelectQuery select = new SelectQuery(people);

select.addColumn(people, "firstname");
select.addColumn(people, "lastname");

select.addOrder(people, "age", Order.DECENDING);

System.out.println(select);

Which produces:

SELECT
   people.firstname ,
   people.lastname
FROM
    people
ORDER BY
    people.age DESC

Go check out the website and two minute tutorial.