can do this with JPA / Hibernate
I am working with postgres and using JPA with Hibernate, in postgres and other DBMS can do this:
SELECT *, function(parameter) AS xyz FROM table WHERE condition
My question is this, the query can display an additional field (xyz), although there is no such column. I can do that with Hibernate JPA.
If you don't have a mapped entity then you will need to use native queries:
Query query = entityManager .createQuery( "SELECT t.*, myfunction(:parameter) FROM table t WHERE t.attr = condition"); query.setParameter("parameter", value); List resultList = query.getResultList();
Otherwise, if you have a mapped entity you can do this with typed queries or the criteria API.
With typed queries:
TypedQuery<Object> query = entityManager .createQuery( "SELECT t.*, FUNC('MyFunction', :parameter) FROM table t WHERE t.attr = condition", Object.class); query.setParameter("parameter", value); List<Object> resultList = query.getResultList();
With criteria API:
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Tuple> query = criteriaBuilder.createTupleQuery(); Root<Table> root = query.from(Table.class); Expression<Long> funcExpr = criteriaBuilder.function("myfunction", Long.class, root.get("parameter")); query.multiselect(query.select(root), funcExpr); List<Tuple> resultList = entityManager.createQuery(query) .getResultList();
By using org.hibernate.SQLQuery, you can create native SQL queries with Hibernate. You then execute these queries the same way you would with a normal Hibernate query. Obviously you're going to lose many of the benefits of letting Hibernate and JPA manage your queries, but if they do not support the feature you want, it may be the only way to do it.
Another solution is to use Formulas, if you invoke your function on some fields of the table, and you always want the result of the function you can map another property in your entity and annotate it with @Formula("myFunction(field1, field2)"). Hibernate will add this to all of your queries which have your entity.