HQL/QBC/Native SQL三种查询策略

转载 2015年11月20日 17:02:49

HQL策略: Hibernate Query Language

Java代码
session.createQuery(“FROM Category c where c.name like ‘Laptop%’”);
session.createQuery(“FROM Category c where c.name like ‘Laptop%’”);

QBC策略: Query By Criteria

Java代码
session.createCriteria(Category.class).add(Restrictions.like(“name”, “Laptop%”));
session.createCriteria(Category.class).add(Restrictions.like(“name”, “Laptop%”));

Native SQL策略

Java代码
session.createSQLQuery(“select {c.*} from CATEGORY {c} where NAME like ‘Laptop%’”).
addEntity(“c”,Category.class);
session.createSQLQuery(“select {c.*} from CATEGORY {c} where NAME like ‘Laptop%’”).
addEntity(“c”,Category.class);

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Restrictions.like和Restrictions.equal:
Restrictions.like(“filearea”, key,MatchMode.EXACT)/ 或者默认状态Restrictions.like(“filearea”, key)
等价于:Restrictions.eq (“filearea”, key)

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Session session = getHibernateTemplate().getSessionFactory() .openSession();
Criteria criteria = session.createCriteria(Film.class);
List list = criteria.add(
Restrictions.or(Restrictions.like(“description”, key,MatchMode.ANYWHERE),
Restrictions.or(Restrictions.like(“name”, key,MatchMode.ANYWHERE),
Restrictions.or( Restrictions.like(“direct”, key,MatchMode.ANYWHERE),
Restrictions.or(Restrictions.like(“mainplay”,key,MatchMode.ANYWHERE),
Restrictions.like(“filearea”, key,MatchMode.ANYWHERE)))))).list();
session.close();
return list;

上述这段qbc查询的代码翻译成sql大概是:
select * from Film
where description like ‘%key%’
or name like ‘%key%’
or direct like ‘%key%’
or mainplay like ‘%key%’
or filearea like ‘%key%’

start end anywhere exact是like的匹配方式:
MatchMode.START:字符串在最前面的位置.相当于”like ‘key%’”
MatchMode.END:字符串在最后面的位置.相当于”like ‘%key’”
MatchMode.ANYWHERE:字符串在中间匹配.相当于”like ‘%key%’”
MatchMode.EXACT:字符串精确匹配.相当于”like ‘key’”

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
QBC :Restrictions

分页查询

Java代码

Criteria criteria = session.createCriteria(Category.class) .add(Restrictions.like(“name”, “Laptop%”));
criteria.addOrder(Order.asc(“name”));
criteria.setFirstResult(0);//初始行数
criteria.setMaxResults(20);//每页显示行数
Criteria criteria = session.createCriteria(Category.class) .add(Restrictions.like(“name”, “Laptop%”));
criteria.addOrder(Order.asc(“name”));
criteria.setFirstResult(0);//初始行数
criteria.setMaxResults(20);//每页显示行数

数据过滤

方法 说明
Restrictions.eq () =
Restrictions.allEq() 利用Map来进行多个等于的限制
Restrictions.gt () >
Restrictions.ge () >=
Restrictions.lt () <
Restrictions.le() <=
Restrictions.between() BETWEEN
Restrictions.like() LIKE
Restrictions.in() in
Restrictions.and() and
Restrictions.or() or
Restrictions.sqlRestriction() 用SQL限定查询

(a) 应用限制

Java代码

Criterion emailEq = Restrictions.eq(“email”, “w@163.com”);
Criteria criteria = session.createCriteria(User.class);
criteria.add(emailEq);
User user = (User)criteria.uniqueResult();
Criterion emailEq = Restrictions.eq(“email”, “w@163.com”);
Criteria criteria = session.createCriteria(User.class);
criteria.add(emailEq);
User user = (User)criteria.uniqueResult();

(b) 比较表达式

Java代码

Restrictions.between(“amount”, new BigDecimal(100), new BigDecimal(200));
Restrictions.gt(“amount”, new BigDecimal(100));
Restrictions.in(“email”, emails);//注:emails为集合
Restrictions.isNull(“email”);
Restrictions.isNotNull(“email”);
Restrictions.isEmpty(“bids”);
Restrictions.sizeGe(“bids”, 3);//bids属性大小
Restrictions.between(“amount”, new BigDecimal(100), new BigDecimal(200));
Restrictions.gt(“amount”, new BigDecimal(100));
Restrictions.in(“email”, emails);//注:emails为集合
Restrictions.isNull(“email”);
Restrictions.isNotNull(“email”);
Restrictions.isEmpty(“bids”);
Restrictions.sizeGe(“bids”, 3);//bids属性大小

(c) 字符串匹配

Java代码

Restrictions.like(“email”, “G%”);
Restrictions.like(“email”, “G%”,MatchMode.START);

注:MatchMode分为START,END,ANYWHERE,EXACT四种模式
Restrictions.like(“email”, “G%”).ignoreCase();
Restrictions.like(“email”, “G%”);
Restrictions.like(“email”, “G%”,MatchMode.START);
注:MatchMode分为START,END,ANYWHERE,EXACT四种模式
Restrictions.like(“email”, “G%”).ignoreCase();

(d) 组合表达式和逻辑操作符

Java代码

Restrictions.or(
Restrictions.and(
Restrictions.like(“firstname”, “G%”),
Restrictions.like(“lastname”, “K%”)),
Restrictions.in(“email”,emails));
Restrictions.or(
Restrictions.and(
Restrictions.like(“firstname”, “G%”),
Restrictions.like(“lastname”, “K%”)),
Restrictions.in(“email”,emails));

(e) SQL表达式

Java代码

Restrictions.sqlRestriction(“{alias}.name=’tie’ and {alias}.addr=’dalian’”);
Restrictions.sqlRestriction(“{alias}.name=?”, “tie”, Hibernate.STRING);//姓名为tie的对象
Restrictions.sqlRestriction(“length({alias}.PASSWORD) < ?”,5,Hibernate.INTEGER);
//密码小于5个字符对象
Restrictions.sqlRestriction(“‘100’ >all( select b.AMOUNT FROM BID b ” +
” WHERE b.ITEM_ID = {alias}.ITEM_ID)”);//返回出价不大于100
Restrictions.sqlRestriction(“{alias}.name=’tie’ and {alias}.addr=’dalian’”);
Restrictions.sqlRestriction(“{alias}.name=?”, “tie”, Hibernate.STRING);//姓名为tie的对象
Restrictions.sqlRestriction(“length({alias}.PASSWORD) < ?”,5,Hibernate.INTEGER);
//密码小于5个字符对象
Restrictions.sqlRestriction(“‘100’ >all( select b.AMOUNT FROM BID b ” +
” WHERE b.ITEM_ID = {alias}.ITEM_ID)”);//返回出价不大于100

(f) 子查询
表关联
(a) 隐式关联
隐式关联有两种方法:
1、 Criteria接口的createCriteria()方法:
Java代码

session.createCriteria(Item.class)
.add(Restrictions.like(“description”, “Foo”,MatchMode.ANYWHERE))
.createCriteria(“bids”)
.add(Restrictions.gt(“amount”,new BigDecimal(100)));

session.createCriteria(Item.class)
.createCriteria(“seller”)
.add(Restrictions.like(“email”, “%@”));
session.createCriteria(Item.class)
.add(Restrictions.like(“description”, “Foo”,MatchMode.ANYWHERE))
.createCriteria(“bids”)
.add(Restrictions.gt(“amount”,new BigDecimal(100)));
session.createCriteria(Item.class)
.createCriteria(“seller”)
.add(Restrictions.like(“email”, “%@”));

2、 分配别名:
Java代码

session.createCriteria(Item.class)
.createAlias(“bids”,”b”)
.add(Restrictions.like(“description”, “%Foo%”))
.add(Restrictions.gt(“b.amount”, new BigDecimal(100)));

session.createCriteria(Item.class)
.createAlias(“seller”, “s”)
.add(Restrictions.like(“s.email”,”%@”));
session.createCriteria(Item.class)
.createAlias(“bids”,”b”)
.add(Restrictions.like(“description”, “%Foo%”))
.add(Restrictions.gt(“b.amount”, new BigDecimal(100)));

session.createCriteria(Item.class)
.createAlias(“seller”, “s”)
.add(Restrictions.like(“s.email”,”%@”));

(b) 抓取关联
Java代码

session.createCriteria(Item.class)
.setFetchMode(“bids”,FetchMode.JOIN)
.add(Restrictions.like(“description”, “%Foo%”))
session.createCriteria(Item.class)
.setFetchMode(“bids”,FetchMode.JOIN)
.add(Restrictions.like(“description”, “%Foo%”))

投影/报表查询
(a) 简单投影
Java代码

session.createCriteria(Item.class) .add(Restrictions.gt(“endDate”, new Date()))
.setProjection(Projections.id());//返回单一属性
session.createCriteria(Item.class).setProjection(
Projections.projectionList().add(Projections.id()).
add(Projections.property(“description”)));//返回一个Object[]
session.createCriteria(Item.class)
.add(Restrictions.gt(“endDate”, new Date()))
.setProjection(Projections.id());//返回单一属性
session.createCriteria(Item.class).setProjection(
Projections.projectionList().add(Projections.id()).
add(Projections.property(“description”)));//返回一个Object[]

(b) 统计分组
Java代码

session.createCriteria(Item.class)
.setProjection(Projections.rowCount());

session.createCriteria(Item.class)
.setProjection(Projections.projectionList()
.add(Projections.rowCount())
.add(Projections.sum(“sales”))
.add(Projections.avg(“score”))
);
session.createCriteria(Bid.class)
.createAlias(“bidder”, “u”)
.setProjection(Projections.projectionList()
.add(Property.forName(“u.id”).group())
.add(Property.forName(“u.username”).group())
.add(Property.forName(“id”).count())
.add(Property.forName(“amount”).avg())
);
session.createCriteria(Item.class)
.setProjection(Projections.rowCount());
session.createCriteria(Item.class)
.setProjection(Projections.projectionList()
.add(Projections.rowCount())
.add(Projections.sum(“sales”))
.add(Projections.avg(“score”))
);
session.createCriteria(Bid.class)
.createAlias(“bidder”, “u”)
.setProjection(Projections.projectionList()
.add(Property.forName(“u.id”).group())
.add(Property.forName(“u.username”).group())
.add(Property.forName(“id”).count())
.add(Property.forName(“amount”).avg())
);

(c) SQL投影
Java代码

String sqlFragment = “(select count(*) from Item i where i.item_id = item_id) ”
+ ” as numofitems”;
session.createCriteria(Bid.class).createAlias(“bidder”, “u”)
.setProjection(
Projections.projectionList().add(
Projections.groupProperty(“u.id”)).add(
Projections.groupProperty(“u.username”)).add(
Projections.count(“id”)).add(
Projections.avg(“amount”)).add(
Projections.sqlProjection(sqlFragment,
new String[] { “numofitems” },
new Type[] { Hibernate.LONG }))
);

Hibernate sql操作的三种方法 HQL QBC Native SQL查询

数据查询与检索是Hibernate的一个亮点。Hibernate的数据查询方式最主要有3种,它们是: Hibernate Query Language(HQL) Query By Criteri...
  • u013378306
  • u013378306
  • 2015年09月26日 18:19
  • 1265

Hibernate通常是三种:hql查询,QBC查询和QBE查询:

通常使用的Hibernate通常是三种:hql查询,QBC查询和QBE查询: 1、QBE(Qurey By Example)检索方式 QBE是最简单的,但是功能也是最弱的,QBE的功能不是特别强大,仅...
  • u010741376
  • u010741376
  • 2016年06月08日 16:18
  • 945

Hibernate QBC与HQL优缺点

QBC 全称:Query By Criteria HQL 全称:Hibernate Query Language HQL优点:与sql相近,可读性好,功能强大,效率高。 HQL缺点:字符串形式,...
  • dongkai_it
  • dongkai_it
  • 2015年06月29日 20:53
  • 4879

Java程序员从笨鸟到菜鸟之(六十四)细谈Hibernate(十五)HQL与QBC查询方式详解

首先来看一下,hibernate提供的几种检索方式: 1.导航对象图检索方式 :根据已经加载的对象,导航到其他对象。例如,对于已经加载的Customer对象,调用它的getOrders().iter...
  • csh624366188
  • csh624366188
  • 2012年05月31日 13:42
  • 8975

Hibernate深入理解----Hibernate 检索方式(HQL,QBC,本地SQL)

参考代码下载github:https://github.com/changwensir/java-ee/tree/master/hibernate4 •Hibernate 提供了以下几种检索...
  • oChangWen
  • oChangWen
  • 2016年09月20日 22:47
  • 1528

Hibernate —— HQL与QBC的区别

Hibernate主要有如下几种查询方式: 导航对象图检索方式 根据己经加载的对象,导航到其他对象。   OID检索方式 按照对象的OID来检索对象。   HQL检索方式 使用面向对象的HQL查询语言...
  • u012045597
  • u012045597
  • 2013年11月12日 21:50
  • 2117

hibernate高级查询技术之“HQL和QBC”的区别

作者是一名hibernate初学者,接触到HQL和QBC两种高级数据库查询方式,为了更深刻的理解两种方法的区别,本文收集了一个些概念和大牛们的见解。希望分享给大家。 首先, ...
  • langjian2012
  • langjian2012
  • 2014年09月07日 19:42
  • 1473

Hibernate的HQL,QBC 查询语言(包括多表联合查询join)

引用链接:http://takeme.iteye.com/blog/1722522   参考链接: in和not in用法:http://blog.csdn.net/shiqidide/art...
  • xn_28
  • xn_28
  • 2017年04月24日 03:26
  • 2633

Hibernate的HQL、QBC、QBE查询总结

一、HQL查询HQL的应用最为广泛,是最为常用的,跟SQL语句差不多,区别是: SQL基于表和字段查询,HQL基于类对象和属性查询HQL的功能大概有: 属性查询、分组查询、动态实例查询(比较具备特...
  • jeffleo
  • jeffleo
  • 2017年01月05日 17:02
  • 394

Hibernate的QBC查询

最近项目中需要使用QBC查询,燃火
  • u011487470
  • u011487470
  • 2014年11月24日 15:07
  • 1683
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:HQL/QBC/Native SQL三种查询策略
举报原因:
原因补充:

(最多只允许输入30个字)