Easily build complicated SELECT statements with Squiggle

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.

• Another SQL DOM library to make generating SQL selects easier

Comments

David Peterson

I like it! One quick comment: Order.DECENDING should have an "S" in it.

Joe Walnes

Well spotted!

Wei Li

Is there any way we can achive the following:

SELECT *
FROM user
WHERE user.name like 'a%'
OR user.id = 12345

Joe Walnes

Yes! Kindof. Out of the box there's no support for the OR statement as the framework has been designed for extensibility.

Here's how you can do it:

Table user = new Table("user");
SelectQuery select = new SelectQuery(user);

select.addColumn(new WildCardColumn(user)); // SELECT *

final Criteria name = new MatchCriteria(user, "name", "LIKE", "a%");
final Criteria id = new MatchCriteria(user, "id", MatchCriteria.EQUALS, 12345);

select.addCriteria(new Criteria() {
public String toString() {
return "(" + name + " OR " + id + ")";
}
});

I shall add support for OR and nested paretheses to the library.

Joe Walnes

Ok, I've just uploaded a new Jar that contains the OR (and nested AND support). And I've fixed the DECENDING typo :).

You can now do:

select.addCriteria(new OR(criteria1, criteria2));

You can also do stuff like:

select.addCriteria(new OR(criteria1, new AND(criteria2, criteria3)));

Example: http://joe.truemesh.com/squiggle/xref/examples/Sample009OrAnd.html

Brian McCallister

Nice, I did something very similar a while back as well,

http://kasparov.skife.org/sqlbuilder.html

Need to look yours over =)

-Brian

Wei Li

Awesome. That is quick. Though I believe SQL statement should be decoupled from code (like what iBatis db layer has done), I think this package is useful in some cases when we need to build SQL based on dynamic criteria.

Geert Bevin

I did something similar too: http://rifers.org/docs/api/com/uwyn/rife/database/queries/Select.html

Seems that we are all suffering from a heavy invented here syndrome ;-)

It also supports Insert, Update, Delete, CreateTable and DropTable and whole collection of nice additional features. Might be handy if you need more than the bare minimals. It also includes a kind of db abstraction layer since it generates different sql according to db-specific sql language templates (postgres, mysql and oracle are currently supported).

Some more info can be found here too:
http://rifers.org/docs/usersguide/ch07.html#sect1_database_displaying_friends

Brian McCallister

I think that this particular tool has been invented thousands of times, which is why I hope one of em catches on and gets maintained, minimalized, factored, and worked over until it is so good it isn't worth inventing again =)

-Brian

Thomas Risberg

Nice and simple. Looks easy enough to extend it. I'm looking at adding some features to use subqueries in the from clause and also in the column list. Are you planning on accepting contributions or do you just want to provide a base that others can extend on their own?

Thomas

Aivars

Just how stupid must be a programmer who can't write a SQL query? Just write it in SQL Navigator/whatever, run it to ensure it works and insert into code. No addittional (buggy) libraries needed, it's even much less typing.

Aivars

It's purpose is for when you have *complicated* criteria...

So how about: ".. WHERE col_a = smthng AND (col_b = smthng_else OR smthng_else = whatever)"? I'm not even talking about "GROUP BY" or "ROLLUP".
I don't see any benefit of using this library, only bugs and more code to maintain. Besides, for a library it is badly documented.

Aivars

".. WHERE col_a = smthng AND (col_b = smthng_else OR col_c = whatever)"

Joe Walnes

Aivars,

Yes, Squiggle is capable of doing that query you specified. Maybe you missed that bit whilst you were reading through the documentation and examples.

Romain

Joe,

I luckily found your library and started to play with it. Just fine, it does what it is supposed. Well done. Of course now that I use it in a practical case, I am facing something unsual where your advice would be helpful.

It has to do with Oracle (9i) and SELECT statement on a DATE. In Oracle, the basic SELECT * from myTable WHERE myDateField='2004-03-30' doesn't work because the default date format is typically 'DD-MMM-YYYY' but this can change depending on the installation of Oracle.

So typically, one would use a built-in to_date() function.

A good final query would be :
SELECT * from myTable WHERE myDateField=to_date('2004-03-30','YYYY-MM-DD').

However I cannot manage to do this with your library because if I use something like :
select.addCriteria(new MatchCriteria(myTable , "myDateField","=","to_date('2004-03-30','YYYY-MM-DD')"))

I get the generated query :
SELECT * from myTable WHERE myDateField='to_date(\'2004-03-30\',\'YYYY-MM-DD\')'

Obviously, the fact that everything is quoted, generates a bad query.

So what do you recommend to address this case of built-in functions ?

Thanks.

Romain

Joe Walnes

Romain,

You can create your own Criteria object. Maybe something like this:

public class DataMatchCriteria extends Criteria {
public DataMatchCriteria(Table table, String col, Date date) {
// .. set fields
}
public void write(Output out) {
out.print(table.getColumn(column));
out.print(" = ");
out.print("to_date('" + date + "')");
}
}

select.addCriteria(new DateMatchCriteria(myTable, "myCol", myDate));

Name:
Email:
URL: