最近项目用上了条件查询,参考了Hibernate文档,整理出一份笔记
创建条件查询实例
1Criteria criteria = session.createCriteria(User.class);
限制结果集
org.hibernate.criterion.Restrictions类 定义获得某些内置Criterion类型的工厂方法
criteria.add(Restrictions.eq("username", "yanshi"));
//输出yanshi 1
// yanshi 2
Restrictions.and()逻辑与
criteria.add(Restrictions.and(Restrictions.like("username", "y%"),Restrictions.ne("id", 1)));
/**//*
yanshi2
7
yanshi3
8
yanshi
9
*/
Restrictions.conjunction()也是逻辑与,但是和and区别一直不太清楚,GOOGLE了一下也没找到明确的说法,最后看了下生成的SQL,个人理解应该是添加一个查询集合,按文档叫法就是逻辑分组
1//criteria.add(Restrictions.like("username", "y%")).add(Restrictions.conjunction().add(Restrictions.ne("id", 1)));
2
3Hibernate:
4 select
5 this_.id as id1_0_,
6 this_.role as role1_0_,
7 this_.username as username1_0_,
8 this_.password as password1_0_
9 from
10 test.user this_
11 where
12 this_.username like ?
13 and (
14 this_.id<>?
15 ) limit ?
16
17//criteria.add(Restrictions.like("username", "y%")).add(Restrictions.conjunction().add(Restrictions.ne("id", 1)));
18
19Hibernate:
20 select
21 this_.id as id1_0_,
22 this_.role as role1_0_,
23 this_.username as username1_0_,
24 this_.password as password1_0_
25 from
26 test.user this_
27 where
28 (
29 this_.username like ?
30 and this_.id<>?
31 ) limit ?
32
33
Restrictions.or()逻辑或
criteria.add(Restrictions.or(Restrictions.like("username", "y%"),Restrictions.eq("id",1)));
/**//*yanshi
1
yanshi2
2
yanshi3
2
yanshi*/
同样Restrictions.disjunction()也是添加一个or查询集合
1//criteria.add(Restrictions.like("username", "y%")).add(Restrictions.disjunction().add(Restrictions.ne("id", 1)).add(Restrictions.in("username",new String[]{"yanshi","yanshi1"})).add(Restrictions.like("username", "yanshi")));
2
3
4Hibernate:
5 select
6 this_.id as id1_0_,
7 this_.role as role1_0_,
8 this_.username as username1_0_,
9 this_.password as password1_0_
10 from
11 test.user this_
12 where
13 this_.username like ?
14 and (
15 this_.id<>?
16 or this_.username in (
17 ?, ?
18 )
19 or this_.username like ?
20 ) limit ?
Restrictions.in() 参数位于列表中的值
criteria.add(Restrictions.in("id",new Integer[]{1,2,3}));
/*
yanshi
1
stone1
2
stone
3
*/
Restrictions.between() value1~value2之间包括本身任意值
criteria.add(Restrictions.between("id", 1, 3));
/*
yanshi
1
stone1
2
stone
3
*/
Restrictions.not() 逻辑非,可以和其他条件组合
criteria.add(Restrictions.not(Restrictions.eq("id",1)));
criteria.add(Restrictions.not(Restrictions.between("id", 1, 3)));
criteria.add(Restrictions.not(Restrictions.in("id",new Integer[]{1,2,3})));
Restrictions最有意思的功能就是可以直接使用SQL
Restrictions.sqlRestriction()
criteria.add(Restrictions.like("username", "yan%")).add(Restrictions.sqlRestriction("{alias}.id=?",1,Hibernate.INTEGER));
//yanshi 1
如果有多个参数,可以传入一个数组
Integer[] agrs=new Integer[]{1,2};
Type[] types=new Type[]{Hibernate.INTEGER,Hibernate.INTEGER};
criteria.add(Restrictions.like("username", "yan%")).add(Restrictions.sqlRestriction("{alias}.id=? or {alias}.id=?",agrs,types));
/**//*
yanshi
1
stone1
2
*/
其他
Restrictions.eq() Restrictions.ne() 等于,不等于
Restrictions.gt() Restrictions.ge() 大于,大于或等于
Restrictions.lt() Restrictions.le() 小于,小于或等于
Restrictions.isnull() Restrictions.isNotnull() 等于空,不等于空
Restrictions.like() 匹配字符串
同样的属性使用多次时候,写Restrictions嫌麻烦,可以用Property
1Property username=Property.forName("username");
2criteria.add(username.like("yansh%"));
关联
关联有两种方式
createCriteria()和crerateAlias()
区别是前一个创建实力,后一个不创建
createCriteria()
1criteria.add(Restrictions.like("username", "yan%")).createCriteria("role").add(Restrictions.eq("type","管理员"));
crerateAlias()
criteria.add(Restrictions.like("username", "yan%")).createAlias("role","r").add(Restrictions.eq("r.type", "管理员"));
crerateAlias() 适用于这样的情况,比如表A 关联表B 和C 需要查询出B.name 等于C.name 的表A的数据
1criteria.createAlias("BTABLE", "b").createAlias("CTABLE", "c").add(Restrictions.eqProperty("b.name", "c.name"));
如果想获得管理对象的数据使用ResultTransformer
1List list=criteria.createAlias("role","r").add(Restrictions.like("username", "yans%"));
2
3criteria.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
4
5Iterator iter=list.iterator();
6 while(iter.hasNext()){
7 Map map=(Map) iter.next();
8 User u=(User) map.get(Criteria.ROOT_ALIAS);
9 System.out.println(u.getUsername()+"aaaa");
10 Role r=(Role) map.get("r");
11 System.out.println(r.getType()+"bbbb");
12 }
13
14/**//*
15yanshiaaaa
16管理员bbbb
17yanshi2aaaa
18普通用户bbbb
19yanshi3aaaa
20普通用户bbbb
21yanshiaaaa
22普通用户bbbb
23
24*/
关联抓取
Criteria criteria = session.createCriteria(User.class).setFetchMode("role", FetchMode.SELECT);
如果多个关联可以用
Criteria criteria = session.createCriteria(User.class).setFetchMode("role", FetchMode.JOIN).setFetchMode("role.xxx",FetchMode.JOIN);
FetchMode.JOIN 和 FetchMode.SELECT 前一个是采用连接解决N+1的问题,SELECT产生大量查询语句........产生N+1 问题
查询示例
1User user = new User();
2Role role = new Role();
3//role.setId(1);
4role.setType("管理员");
5user.setRole(role);
6criteria.add(Example.create(user));
7
8//可以使用关联,但是貌似用ID作为条件没有效果
9criteria.add(Example.create(user)).createCriteria("role").add(Example.create(user.getRole()));
enableLike()
对于字符串可以设置匹配模式
1Example.create(user).enableLike(MatchMode.ANYWHERE)
MatchMode.ANYWHERE 任何位置
MacthMode.END 匹配结尾
MacthMode.START 匹配开头
MatchMode.EXACT 精确匹配
其他的一些设置
excludeNone() 忽略NULL
excludeZeroes()忽略0
excludeProperty("属性名") 忽略指定属性
投影
Projections可以进行一些运算操作
Projections.avg(propertyName)统计指定属性平均数
Projections.count(propertyName)统计指定属性的行数
Projections.max(propertyName) 最大值
Projections.min(propertyName) 最小值
1int totalCount = new Integer(criteria.setProjection(Projections.rowCount()).uniqueResult().toString());
2criteria.setProjection(null);
3criteria.setFirstResult(first);
4criteria.setMaxResults(max);
5criteria.setResultTransform(Criteria.ROOT_ENTITY);
注意 一定要在OrderBy和setFirstResult setMaxResults 之前使用不然返回空,最后还要设置返回类型,不然默认为Object
Projections.distinct()用来处理重复数据
1public List<User> list(int first, int max) {
2 Session session = new Configuration().configure().buildSessionFactory()
3 .openSession();
4 Criteria criteria = session.createCriteria(User.class).setFetchMode("role", FetchMode.JOIN);
5 //取得ID值
6 criteria.setProjection(Projections.distinct(Projections.projectionList().add(Projections.groupProperty("password")).add(Projections.id())));
7 List ids=new ArrayList<Integer>();
8 for (Iterator iter = criteria.list().iterator(); iter.hasNext();) {
9 Object[] values = (Object[]) iter.next();
10 ids.add((Integer)values[1]);
11 }
12 //用in来查询数据
13 criteria.add(Restrictions.in("id",ids));
14 int totalCount = new Integer(criteria.setProjection(
15 Projections.rowCount()).uniqueResult().toString());
16 criteria.setProjection(null);
17 criteria.setFirstResult(first);
18 criteria.setMaxResults(max);
19 criteria.setResultTransformer(Criteria.ROOT_ENTITY);
20 List<User> list = criteria.list();
21 return list;
22 }
需要说明的是
1criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
也是出来重复数据的,但是该方法是在结果集中处理,对分页影响比较大,比如实际上查询出10条数据,重复2条,重复处理后实际显示9条数据,这样和分页的总数就不一致了
投影可以设置别名供其他投影使用
1criteria.setProjection(Projections.groupProperty("username").as("name")).addOrder(Order.desc("name"))
同样可以Property.forName来设置投影
在SESSION创建查询使用DetachedCriteria
1DetachedCriteria c=DetachedCriteria.forClass(User.class);
posted on 2009-12-21 23:13
岩石 阅读(468)
评论(0) 编辑 收藏