[plug] [OT] SQL query construction in Java

[James Devenish]

Hi,

It's been a while since I programmed in Java, but will probably resume
some time in the coming months. I recall being very satisfied with
libraries that enabled queries (esp. SQL queries) to be constructed
using Java objects. This is far more elegant than manipulating strings.
A conceptual example:

This simple query:
    SELECT DISTINCT title FROM books WHERE books.title LIKE '%, The'
would arise from the following Java:
    Query query = new Query(myConnection, myDatabase);
    query.addLike(books.title, '%, The');
    query.setDistinct();

It could then made changed into the following query:
    SELECT DISTINCT title FROM books LEFT JOIN loans USING(id) WHERE
    books.title LIKE '%, The' AND loans.status = 'OnLoan'
by appending the following Java:
    query.addLeftJoin(books.ID, loans.ID);
    query.and(loans.status, loans.ON_LOAN);

This is not apparently as flexible as SQL, but you get the idea. I now
get the feeling that the number of Java frameworks has burgeoned to the
point where you could spend a month just 'sampling' all the different
options. So...I know this is a long shot...but does anyone have any
Java-based SQL query-builders that they favour?