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);
Querydsl supports querying on SQL, JPA and JDO backends.
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.
These are some good proprietary libraries to create typesafe SQL queries dynamically
Apart from the above, there is always
Your example in jOOQ:
create.select(DATE, QUOTE)
.from(STOCKMARKET)
.where(CORP.equal(123))
.orderBy(DATE.desc());
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();
}
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.
Apache Empire-db is a relational database abstraction layer and data persistence component that allows developers to take a much more SQL-centric approach in application development than traditional Object-relational mapping frameworks (ORM).
More here : https://empire-db.apache.org/
Quaere
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);
You can use the naskarlab/fluent-query:
https://github.com/naskarlab/fluent-query
Example:
@Test
public void testSelect() {
String expected = "select e0.* from Customer e0";
String actual = new QueryBuilder()
.from(Customer.class)
.to(new NativeSQL())
.sql()
;
Assert.assertEquals(expected, actual);
}
You can see more examples in unit tests of the project:
