Is there any library to represent SQL queries as objects in Java code?

Question

I was wondering if there is any library that can be used to represent SQL queries as objects in Java.

In the code I have plenty of static variables of type java.lang.String that are hand written SQL queries. I would be looking for library having a nice fluent API that allows me to represent the queries as objects rather than strings.

Example:

Query q = select("DATE", "QUOTE")
  .from("STOCKMARKET")
  .where(eq("CORP", "?"))
  .orderBy("DATE", DESC);

Answer 1

Empire-db

http://incubator.apache.org/empire-db/

Quaere

http://xircles.codehaus.org/projects/quaere

Answer 2

http://www.hibernate.org/ Probably most powerfull ORM library for Java. It can do much more then just simple query mapping. So you can easily implement it somwhere else in your application. For your case it can be done somehow like that:

public class LookupCodeName
{
    private String code;
    private String name;

 /*... getter-setters ... */
}

public class someBL {

public List<LookupCodeName> returnSomeEntity() {
      SQLQuery sqlQuery =  (SQLQuery)((HibernateSession)em).getHibernateSession()
                        .createSQLQuery( "SELECT st.name as name, st.code as code FROM someTable st")
                        .addScalar("code")
                        .addScalar("name")
.setResultTransformer(Transformers.aliasToBean(LookupCodeName.class));
    }
return (List<LookupCodeName>)sqlQuery.list();
}

Answer 3

If you don't want to map string queries, then you must annotate your class as entity and bind it with table then you can use hibernate or java persistance. Example will be too complex though. But, at the end your query will transform to something like this:

find list of entities:

 Criteria c = createCreteria(entityManager, StockMarket.class);
    // you can add "where" clause by using c.add(Restrictions); 
   // like this: c.add(Restrictions.ilike("name", "%somename%"); where "name" is your entity's field
     List<StockMarket> smList = c.list();

find object by id:

 StockMarket sm  = entityManager.find(StockMarket.class, id);

Answer 4

Squiggle

Answer 5

Jequel looks pretty nifty: http://www.jequel.de/

It uses a fluent interface, so it's easy to read, almost like natural SQL (from the docs):

SqlString sql = select(ARTICLE.OID)
               .from(ARTICLE, ARTICLE_COLOR)
               .where(ARTICLE.OID.eq(ARTICLE_COLOR.ARTICLE_OID)
               .and(ARTICLE.ARTICLE_NO.is_not(NULL)));

It also supports executing queries against a DataSource with parameters, so it handles creation of parameterized queries as well.

Answer 6

Querydsl supports querying on SQL, JPA and JDO backends : http://source.mysema.com/display/querydsl/Querydsl

The example above becomes :

query.from(stockmarket).where(stockmarket.corp.eq(someVar))
    .orderBy(stockmarket.date.desc())
    .list(stockmarket.date, stockmarket.quote);

Querydsl uses code generation via APT to mirror an SQL schema to Java query types. This way the queries are fully type-safe (or "schema-compliant" with SQL).

I am the maintainer of Querydsl, so this answer is biased.

I published a comparison of Querydsl to other frameworks here.