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).
4
-
5PreparedStatements are good for most of that, I thought.OMG Ponies– OMG Ponies2010-08-22 19:24:59 +00:00Commented Aug 22, 2010 at 19:24
-
Exactly - there's little of value in the given abstraction as described.duffymo– duffymo2010-08-22 19:31:25 +00:00Commented Aug 22, 2010 at 19:31
-
You're describing Hibernate's Criteria API I think:)extraneon– extraneon2010-08-22 19:33:35 +00:00Commented Aug 22, 2010 at 19:33
-
@OMG Ponies As I said the library should support placeholders so PreparedStatements are assumed @extraneon Yes, like Hibernate's Criteria API but for SQLEduardo– Eduardo2010-08-22 19:43:00 +00:00Commented Aug 22, 2010 at 19:43
Add a comment
|
3 Answers
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.
answered Aug 22, 2010 at 20:01
Pascal Thivent
572k140 gold badges1.1k silver badges1.1k bronze badges
Comments
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.
2 Comments
Eduardo
Yes, like Hibernate's Criteria API but for SQL.
Peter Tillemans
That is exactly what hibernate does : translate to SQL. You can use either the Criteria API or HQL, but in the end it becomes SQL. iBatis allows straight SQL and injects parameters into the queries. It uses prepared statement if available on the target database.
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.
4 Comments
Eduardo
I do not want to use ORM tools
duffymo
Then try another abstraction, like Spring JDBC.
Eduardo
I am already using Spring JDBC but it does not seem to provide any of the tools I am asking about, does it?
duffymo
I don't see where you state that you're using Spring anywhere in your question. Your question seems misplaced, IMO.