Hibernate DetachedCriteria 查询set 类型的字段

Hibernate DetachedCriteria 查询set 类型的字段

例：
public class Users {
private Set challenges = new HashSet(0);
}
public class Challenge {
private Integer challengeid;
private Users users;
}
User 和Challenge  是一对多的关系
现在想根据 Users  中的challenges.challengeid的某一个属性查询
challenges 是一个属性

<script type="text/javascript"></script>

DetachedCriteria detachedCriteria=DetachedCriteria.forClass("Users");
detachedCriteria.createAlias("challenges","c").add(Restrictions.eq("c.challengeid", "查询值"));


Java代码

1. DetachedCriteria detachedCriteria=DetachedCriteria.forClass("Users");   
2. detachedCriteria.createAlias("challenges","c").add(Restrictions.eq("c.challengeid", "查询值"));  

DetachedCriteria detachedCriteria=DetachedCriteria.forClass("Users");
detachedCriteria.createAlias("challenges","c").add(Restrictions.eq("c.challengeid", "查询值"));

我查询的网页:
http://topic.csdn.net/u/20090616/10/3e8d5f18-7144-4248-85a4-41bf5c4e3903.html

http://stackoverflow.com/questions/1987471/nhibernate-exclude-a-property-from-a-projection-list-select-clause

http://topic.csdn.net/u/20080225/13/9eb2fbf7-117b-41f4-84d6-4b5461780388.html

希望能帮助你

1、Users实体

Java代码

1. public class Users implements java.io.Serializable {   
2.   
3.     // Fields   
4.   
5.     private Long id;   
6.     private String userName;   
7.     private Set challenges = new HashSet(0);   
8.   
9.     ......   
10.   
11. }  

public class Users implements java.io.Serializable {

	// Fields

	private Long id;
	private String userName;
	private Set challenges = new HashSet(0);

    ......

}

表记录：
id  user_name
1 小明
2 小强


2、Challenge实体

Java代码

1. public class Challenge implements java.io.Serializable {   
2.   
3.     // Fields   
4.   
5.     private Long challengeid;   
6.     private Users users;   
7.     private String challengeName;   
8.   
9.     ......   
10.   
11. }  

public class Challenge implements java.io.Serializable {

	// Fields

	private Long challengeid;
	private Users users;
	private String challengeName;

    ......

}

表记录：
id challenge_name user_id
1       挑战1      1
2       挑战2      1
3       挑战3      2


3、根据challengeid查询Challenge对象，再关联出相应的users信息

Java代码

1. // 查询条件   
2. DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Challenge.class);   
3. detachedCriteria.add(Restrictions.eq("challengeid", new Long(1)));   
4. Criteria criteria = detachedCriteria.getExecutableCriteria(session);   
5.   
6. // 查询   
7. List resultList = criteria.list();   
8. if (resultList != null && resultList.size() > 0) {   
9.     for (int i = 0; i < resultList.size(); i++) {   
10.         Challenge challenge = (Challenge) resultList.get(i);   
11.         // 打印出challenge所关联的users信息   
12.         System.out.println(challenge.getUsers().getUserName());   
13.     }   
14. }  

// 查询条件
DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Challenge.class);
detachedCriteria.add(Restrictions.eq("challengeid", new Long(1)));
Criteria criteria = detachedCriteria.getExecutableCriteria(session);

// 查询
List resultList = criteria.list();
if (resultList != null && resultList.size() > 0) {
    for (int i = 0; i < resultList.size(); i++) {
        Challenge challenge = (Challenge) resultList.get(i);
        // 打印出challenge所关联的users信息
        System.out.println(challenge.getUsers().getUserName());
    }
}

结果输出：
小明

4、根据challengeName模糊查询查询Challenge对象，再关联出相应的users信息

Java代码

1. // 查询条件   
2. DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Challenge.class);   
3. detachedCriteria.add(Restrictions.like("challengeName", "%" + "挑战" + "%"));   
4. Criteria criteria = detachedCriteria.getExecutableCriteria(session);   
5.   
6. // 查询   
7. List resultList = criteria.list();   
8. if (resultList != null && resultList.size() > 0) {   
9.     for (int i = 0; i < resultList.size(); i++) {   
10.         Challenge challenge = (Challenge) resultList.get(i);   
11.         // 打印出challenge所关联的users信息   
12.         System.out.println(challenge.getUsers().getUserName());   
13.     }   
14. }  

// 查询条件
DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Challenge.class);
detachedCriteria.add(Restrictions.like("challengeName", "%" + "挑战" + "%"));
Criteria criteria = detachedCriteria.getExecutableCriteria(session);

// 查询
List resultList = criteria.list();
if (resultList != null && resultList.size() > 0) {
    for (int i = 0; i < resultList.size(); i++) {
        Challenge challenge = (Challenge) resultList.get(i);
        // 打印出challenge所关联的users信息
        System.out.println(challenge.getUsers().getUserName());
    }
}

结果输出：
小明
小明
小强

5、根据users查询，查询小明对应的所有Challenge信息

Java代码

1. // 查询条件   
2. DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Users.class);   
3. detachedCriteria.add(Restrictions.eq("userName", "小明"));   
4. Criteria criteria = detachedCriteria.getExecutableCriteria(session);   
5.   
6. // 查询   
7. List resultList = criteria.list();   
8. if (resultList != null && resultList.size() > 0) {   
9.     Set set = ((Users) resultList.get(0)).getChallenges();   
10.     // 循环打印出小明对应的所有Challenge信息   
11.     for (Iterator it = set.iterator(); it.hasNext();) {   
12.         Challenge challenge = (Challenge) it.next();   
13.         System.out.println(challenge.getChallengeName());   
14.     }   
15. }  

// 查询条件
DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Users.class);
detachedCriteria.add(Restrictions.eq("userName", "小明"));
Criteria criteria = detachedCriteria.getExecutableCriteria(session);

// 查询
List resultList = criteria.list();
if (resultList != null && resultList.size() > 0) {
    Set set = ((Users) resultList.get(0)).getChallenges();
    // 循环打印出小明对应的所有Challenge信息
    for (Iterator it = set.iterator(); it.hasNext();) {
        Challenge challenge = (Challenge) it.next();
        System.out.println(challenge.getChallengeName());
    }
}

结果输出：
挑战2
挑战1

Java代码

1. // 查询条件   
2. DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Users.class);   
3. detachedCriteria.createAlias("challenges", "cs");   
4. detachedCriteria.add(Restrictions.like("cs.challengeName", "%" + "挑战" + "%"));   
5. Criteria criteria = detachedCriteria.getExecutableCriteria(session);   
6.   
7. // 查询   
8. List resultList = criteria.list();   
9. if (resultList != null && resultList.size() > 0) {   
10.     for (int i = 0; i < resultList.size(); i++) {   
11.         Users user = (Users) resultList.get(i);   
12.         System.out.println(user.getUserName());   
13.     }   
14. }  

// 查询条件
DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Users.class);
detachedCriteria.createAlias("challenges", "cs");
detachedCriteria.add(Restrictions.like("cs.challengeName", "%" + "挑战" + "%"));
Criteria criteria = detachedCriteria.getExecutableCriteria(session);

// 查询
List resultList = criteria.list();
if (resultList != null && resultList.size() > 0) {
    for (int i = 0; i < resultList.size(); i++) {
        Users user = (Users) resultList.get(i);
        System.out.println(user.getUserName());
    }
}

输出结果：
小明
小明
小强


重复结果可以再过滤，这个应该是你想要的查询方式

7、为了避免resultList中的重复记录, 可以转换为Set集合, Set中存放的记录不允许相同。
以下是对第6的结果进行去重复记录

Java代码

1. Set resultSet = new HashSet();   
2. // 添加以后自动去掉重复记录   
3. resultSet.addAll(resultList);   
4.   
5. // 循环打印出小明对应的所有Challenge信息   
6. for (Iterator it = resultSet.iterator(); it.hasNext();) {   
7.     Users user = (Users) it.next();   
8.     System.out.println(user.getUserName());   
9. }  

10. <hibernate-mapping>
      <class table="table1">
        <set name="table2" table="table2" lazy="true" cascade="all">
          <key column="result_id"/>
          <many-to-many column="group_id"/>
        </set>
      </class>
    </hibernate-mapping>

11. select t2.property1, t2.property2, ... 
        from table1 as t1
        inner join t1.table2 as t2
    select t1.table2.property1, t1.table2.property2, ... from table1 as t1