Adding a group to the sleep criteria - query without projecting

Question

I have a Criteria-Query that joins a second table B to select entities from table A. The problem is that this query returns several entities from table A several times. But I need the results to be different.

Using Criteria.DISTINCT_ROOT_ENTITY is useless because it filters out multiple occurrences after executing SQL-Query. Therefore, when I limit my results to 20 images, I only end up with 4, although there are more entries that match my query.

In pure SQL, I can simply add “GROUP BY ID” to the query, and everything is fine, because the join of table B is used only to select entities from table A. But using the Criteria-API I can’t do this. The only way to add "GROUP BY" is to use Projections. But then I get scalar values, not a real instance of my class. Using SQL constraints does not work either, because sleep mode adds "1 = 1" after my "GROUP BY" class .: (

Any ideas?

Answer 1

Have you tried using something like this?

     ICriteria criteria = dc.GetExecutableCriteria(RepositoryInstance.Session)
                                .SetProjection(
                                    Projections.Distinct(Projections.ProjectionList() 
                                        .Add(Projections.Property("Prop1"),"Prop1")
                                        .Add(Projections.Property("Prop2"),"Prop2")
                                        .Add(Projections.Property("Prop3"),"Prop3")
                                        .Add(Projections.Property("Prop4"),"Prop4")));
result = criteria.List();

You can dynamically add properties through class reflection.

This creates SQl as follows: select distinct prop1,prop2,prop3,prop4 from yourClass

DetachedCriteria dc, .

Answer 2

: , , , , , , , bean. ( ). project bean .

, . , , Projection.

select p.* FROM parent p INNER JOIN child c ON p.id_parent=c.id_father
WHERE c.child_name like '%?%' AND p.parent_name like '%?%' 
group by p.id_parent

Java , p.* Parent, bean, , , - , :)

, Child.class Parent.class, .

Criteria c = session.createCriteria(Child.class,"c");// starting from Child
    c.add(Restrictions.like("childName",   "abc", MatchMode.ANYWHERE));
    c.createAlias("parent", "p"); //remember parent is an attribute in Child.class
    c.add(Restrictions.like("p.parentName",   "xyz", MatchMode.ANYWHERE));
    c.setProjection( Projections.projectionList().add(Projections.groupProperty("parent"))); //projecting parent which is an attribute of Child.class

    List<Parent> result = c.list(); //get the result
    for (Parent p: result) {
        System.out.println(p);
    }

, Entity bean.

package com.mazhar.beans;

import static javax.persistence.GenerationType.IDENTITY;

import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

@Entity
@Table(name = "parent")
public class Parent {
    private Integer idParent;
    private String parentName;
    private List<Child> childs;

    @Id
    @GeneratedValue(strategy = IDENTITY)
    @Column(name = "id_parent")
    public Integer getIdParent() {
        return idParent;
    }
    public void setIdParent(Integer idParent) {
        this.idParent = idParent;
    }

    @Column(name = "parent_name")
    public String getParentName() {
        return parentName;
    }
    public void setParentName(String parentName) {
        this.parentName = parentName;
    }

    @OneToMany(fetch=FetchType.LAZY, mappedBy="parent", cascade=CascadeType.ALL)
    public List<Child> getChilds() {
        return childs;
    }
    public void setChilds(List<Child> childs) {
        this.childs = childs;
    }

}

package com.mazhar.beans;

import static javax.persistence.GenerationType.IDENTITY;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

@Entity
@Table(name = "child")
public class Child {
    private Integer idChild;
    private String childName;
    private Parent parent; //this actually we projected in criteria query.

    @Id
    @GeneratedValue(strategy = IDENTITY)
    @Column(name = "id_city", unique = true, nullable = false)
    public Integer getIdChild() {
        return idChild;
    }

    public void setIdChild(Integer idChild) {
        this.idChild = idChild;
    }

    @Column(name = "city_name", nullable = false)
    public String getChildName() {
        return childName;
    }

    public void setChildName(String cName) {
        this.childName = cName;
    }

    @ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JoinColumn(name = "id_father")
    public Parent getParent() {
        return parent;
    }

    public void setParent(Parent parent) {
        this.parent = parent;
    }
}

Answer 3

SQL-, Hibernate . , , HQL , , GROUP BY.

. .

, hbm.xml:

<sql-query name="exampleQuery">
<query-param name="param" type="int"/>
<return alias="x" class="foo.bar.X"/>
<return alias="y" class="foo.bar.Y"/>
    <![CDATA[
        select {x.*}, {y.*}
        from XEntity x
        inner join YEntity y on y.xId = x.id
        where y.value = :param
    ]]>
</sql-query>

{x.} {y.} X Y

Answer 4

, , , , Oracle, , Oracle .

, , . MySQL, , .

, , groupProperty , . . , .