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.

Answers


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.


Need Your Help

Toggle widget stops working after installing any new app

android android-widget

I'm designing a toggle widget that mutes and unmutes the media volume. It works great until I install any new app... Then it just stops working. There is no force close or anything, it just doesn't...

React: Can't get correct width of element after render

javascript iframe width reactjs

I'm trying to setup a Marquee in React if a piece of text is greater than its container but I can't get the correct width of the container, even after the component has rendered.