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.
Joe Walnes 
I like it! One quick comment: Order.DECENDING should have an “S” in it.
Well spotted!
Is there any way we can achive the following:
SELECT *
FROM user
WHERE user.name like ‘a%’
OR user.id = 12345
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.
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
Nice, I did something very similar a while back as well,
http://kasparov.skife.org/sqlbuilder.html
Need to look yours over =)
-Brian
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.
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
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
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
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.
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.
“.. WHERE col_a = smthng AND (col_b = smthng_else OR col_c = whatever)”
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.
Another SQL DOM library to make generating SQL selects easier
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
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));