One of the solutions is to create a custom GROUP_CONCAT HQL function that is translated to SQL. Idea is to create function: group_concat(name, true, ‘ # ‘, name, ‘DESC’) 1: name of the column for aggregation 2: true\false use DISTINCT or not 3: the separator for concatenation 4: column name for ORDER BY 5: sorting … Read more
think works Criteria criteria = getSession().createCriteria(clazz); Criterion rest1= Restrictions.and(Restrictions.eq(A, “X”), Restrictions.in(“B”, Arrays.asList(“X”,Y))); Criterion rest2= Restrictions.and(Restrictions.eq(A, “Y”), Restrictions.eq(B, “Z”)); criteria.add(Restrictions.or(rest1, rest2));
Use Projections to specify which columns you would like to return. Example SQL Query SELECT user.id, user.name FROM user; Hibernate Alternative Criteria cr = session.createCriteria(User.class) .setProjection(Projections.projectionList() .add(Projections.property(“id”), “id”) .add(Projections.property(“Name”), “Name”)) .setResultTransformer(Transformers.aliasToBean(User.class)); List<User> list = cr.list();
Please refer to this for the example .The main point is to use the groupProperty() , and the related aggregate functions provided by the Projections class. For example : SELECT column_name, max(column_name) , min (column_name) , count(column_name) FROM table_name WHERE column_name > xxxxx GROUP BY column_name Its equivalent criteria object is : List result = … Read more
Yes it is possible. You can use a custom transformer for it: FluentHibernateResultTransformer. You can copy paste code, or add the jar by Maven: fluent-hibernate-core. You need to use Criteria with Projections. Please, don’t forget to specify projection aliases (userName, addressDetails.countryCode) Criteria criteria = session.createCriteria(Customer.class); criteria.createAlias(“addressDetails”, “addressDetails”, JoinType.LEFT_OUTER_JOIN); criteria.setProjection(Projections.projectionList() .add(Projections.property(“userName”).as(“userName”)) .add(Projections.property(“addressDetails.countryCode”) .as(“addressDetails.countryCode”))); List<Customer> customers = … Read more
While similar names, the usage is different. I. Projections.distinct(Projections.property(“id”)); this statement would be translated into SQL Statement. It will be passed to DB Engine and executed as a SQL DISTINCT. See: 17.9. Projections, aggregation and grouping so e.g. this example: List results = session.createCriteria(Cat.class) .setProjection( Projections.projectionList() .add( Projections.distinct(Projections.property(“id”)) ) ) .list(); would seems like: SELECT … Read more
I mostly prefer Criteria Queries for dynamic queries. For example it is much easier to add some ordering dynamically or leave some parts (e.g. restrictions) out depending on some parameter. On the other hand I’m using HQL for static and complex queries, because it’s much easier to understand/read HQL. Also, HQL is a bit more … Read more