CODEWITHSUNDEEP
javasql
Eduardo
Eduardo

Reputation: 2341

open source Java library of SQL utility functions?

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).

Upvotes: 0

Views: 1008

Answers (3)

Pascal Thivent
Pascal Thivent

Reputation: 570645

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.

Upvotes: 2

Peter Tillemans
Peter Tillemans

Reputation: 35351

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.

Upvotes: 1

duffymo
duffymo

Reputation: 309028

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.

Upvotes: 0

Related Questions