How can someone give the HQL / SQL a better OO approach?

I work with Hibernate and particulary like the Criteria api for the way to compose with request predicates, but in some cases it's not possible to use it so i have to do HQL / SQL.

What i don't like with HQL / SQL query building is mostly because, in addition to not be OO, it's hard to compose with predicates like we can do with Criteria. I don't like the approach to build the complete query String, build that query, and just then add all your query parameters.

Does someone here know tools that permits to simplify the writing and reusability of HQL / SQL queries in Hibernate/JPA?

Actually i've made a small class that permits me to build the query part and the parameter part in the same time. I guess it may not respond to anyone's case but it still work nicely for my simple usecase: i'm mostly doing composition of predicates with AND and OR in the where clause.

import java.util.Collection;
import java.util.HashMap;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;

/**
 * This permits to append to query step by step and give a better lisibility to some queries
 * Works with hql and sql
 * 
 * @author Sebastien Lorber (<i>lorber.sebastien@gmail.com</i>)
 */
public class AppendableQuery  {

    private Map<String,Object> parameters;
    private Map<String,Collection<? extends Object>> parametersLists;
    private StringBuilder queryBuilder;

    /**
     * Creates a new empty query
     */
    public AppendableQuery() {
        parameters = new HashMap<String, Object>();
        parametersLists = new HashMap<String, Collection<? extends Object>>();
        queryBuilder = new StringBuilder();
    }

    /**
     * Append to the query
     * @param querySubstring
     */
    public AppendableQuery append(String querySubstring) {
        queryBuilder.append(querySubstring);
        return this;
    }

    /**
     * Set a parameter to the query
     * @param paramKey
     * @param paramValue
     */
    public AppendableQuery setParameter(String paramKey,Object paramValue) {
        parameters.put(paramKey, paramValue);
        return this;
    }

    /**
     * Set a parameter list to the query
     * @param paramKey
     * @param paramValue
     * @return
     */
    public AppendableQuery setParameterList(String paramKey,Collection<? extends Object> paramValue) {
        parametersLists.put(paramKey, paramValue);
        return this;
    }

    /**
     * Gets an HQL query based on what you filled
     *开发者_Python百科 @param hibernateSession
     * @return SQL query
     */
    public Query getHQLQuery(Session hibernateSession) {
        Query query = hibernateSession.createQuery(queryBuilder.toString());
        for ( Map.Entry<String, Object> entry : parameters.entrySet() ) {
            query.setParameter(entry.getKey(), entry.getValue());
        }
        for ( Map.Entry<String, Collection<? extends Object>> entry : parametersLists.entrySet() ) {
            query.setParameterList(entry.getKey(), entry.getValue());
        }
        return query;
    }

    /**
     * Gets an SQL query based on what you filled
     * @param hibernateSession
     * @return SQL query
     */
    public SQLQuery getSQLQuery(Session hibernateSession) {
        SQLQuery query = hibernateSession.createSQLQuery(queryBuilder.toString());
        for ( Map.Entry<String, Object> entry : parameters.entrySet() ) {
            query.setParameter(entry.getKey(), entry.getValue());
        }
        for ( Map.Entry<String, Collection<? extends Object>> entry : parametersLists.entrySet() ) {
            query.setParameterList(entry.getKey(), entry.getValue());
        }
        return query;
    }

}

What do you think of such a trick? Do you know supported community tools that do almost this kind of thing?

For a more OO style construction you might want to consider using Liquidform or Querydsl. Liquidform is a more lightweight tool requiring no code generation and Querydsl has a slightly heavier overhead, but with more readable and compact syntax. Also I believe that the expressivity of Querydsl is higher.

Rolling out your own JPQL builder is not necessary, there are already proven solutions for that.

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

I think QueryDSL has some means of integration of SQL/JPQL/HQL with JPA.

If Hibernate/JPA is not a must, you might want to have a look at jOOQ, a Java database abstraction framework I wrote. It supports almost all standard and non-standard SQL constructs while still being object oriented.