open source Java library of SQL utility functions?

Question

I am looking for a SQL utility library that allows me to do things like escaping strings for prefix search using LIKE or programmatically writing the WHERE part of the query by adding terms (with placeholder support).

Answer 1

If you're still thinking about SQL in these low-level terms, I'd say you aren't being object-oriented enough.

Think about your problems in terms of objects. You're still too mired in the primitive level.

Consider better abstractions for persistence on top of your model objects: the DAO pattern, Spring JDBC, iBatis, ORM tools like Hibernate, etc.

Answer 2

A typical ORM framework like hibernate or JPA provides this out of the box.

e.g in hibernate .

from Document doc fetch all properties where lower(doc.name) like 'cats%'

will return Document object where the nqme start with cats.

For parameter queries :

Query q = s.createQuery("from foo Foo as foo where foo.name=:name and foo.size=:size");
q.setProperties(fooBean); // fooBean has getName() and getSize()
List foos = q.list();

It will also save you from a lot of boilerplate to create all the JDBC objects needed and all the error handling.

If you need to stay close to SQL, give iBatis a careful look.

Answer 3

Projects like Quaere, LIQUidFORM, jaQu, JEQUEL (Java Embedded QUEry Language) all offer a fluent interface to write SQL statements and might be what you're looking for. For example, with JEQUEL:

public void testParameterExample() {
    final Sql sql = select(ARTICLE.NAME, ARTICLE.ARTICLE_NO)
            .from(ARTICLE)
            .where(ARTICLE.OID.in(named("article_oid"))).toSql();
    assertEquals("select ARTICLE.NAME, ARTICLE.ARTICLE_NO from ARTICLE where ARTICLE.OID in (:article_oid)", sql.toString());

    final Collection<String> articleDesc = sql.executeOn(dataSource)
            .withParams("article_oid", Arrays.asList(10, 11, 12))
            .mapBeans(new BeanRowMapper<ArticleBean, String>() {
                public String mapBean(final ArticleBean bean) {
                    return bean.getArticleNo() + "/" + bean.getName();
                }
            });
    assertEquals(1, articleDesc.size());
    assertEquals("12345/Foobar", articleDesc.iterator().next());
}

More of them at the bottom of the jaQu webpage.