1.HQL和Criteria简介
HQL: 与SQL语句很相似
面向对象的查询语言,与SQL不同,HQL中的对象名是区分大小写的(除了JAVA类和属性其他部分不区分大小写);
HQL中查的是对象而不是和表,并且支持多态;HQL主要通过Query来操作,Query的创建方式:
Query q = session.createQuery(hql);
hql 可以是类似下面的形式:
from Person
from User user where user.name=?
from User user where user.name=:name and user.birthday < :birthday
Criteria:
Criteria是一种比HQL更面向对象的查询方式;Criteria的创建方式:
Criteria crit = session.createCriteria(DomainClass.class);
简单属性条件如:
criteria.add(Restrictions.eq(propertyName, value)),
criteria.add(Restrictions.eqProperty(propertyName,otherPropertyName))
2.实体类或属性名与数据库关键字冲突问题的解决办法
例如,在oracle数据库中user是关键字,表名不能使user
方法一:更改表名
<class name="User" table="t_user">
这样就避免了表名冲突
方法二:添加反引号
<class name="User" table="`user`">
这样user就会当做是字符串处理
3.hql的命名参数
setString(int,String)
setString(String,String)
//方法一:hql中使用?
String hql = "from User as user where user.name=?";
Query query = s.createQuery(hql);
query.setString(0, name);
//方法二:使用替代字符
String hql = "from User as user where user.name=:name";
Query query = s.createQuery(hql);
query.setString("name", name);
还有很多其他的setXxx方法,例如setDate()方法等等,这个要根据domain 类的属性的类型而定
4.Query接口的分页方法
分页相关的方法:Projections.rowCount(),criteria.setFirstResult(),criteria.setMaxResults()
//实现分页效果,这个是可移植的,不管使用的那一种数据库,通过配置文件中的 Dialect来完成
query.setFirstResult(0);
query.setMaxResults(10);
两个方法分别得到开始的那个和最多得到多少个
5.get方法和使用Query方式根据id查用户的区别
get方法可以使用缓存中的数据,但是Query方式不会去缓存中找,所以效率有时会很低
Query方式:
User u1 = null;
Query q = s.createQuery("from User where id="+id);
u1 = (User)q.uniqueResult();
get方式:
s = HibernateUtils.getSession();
u = (User)s.get(User.class, id);//默认就是根据id查找
6. 离线查询
DetachedCriteria 它的生成和session没有关系,而Criteria和session有关,Criteria是用DetachedCriteria根据session来生成的
DetachedCriteria 一般用于构造不确定的查询条件,它的生成与要查询的对象有关
例如:根据用户的选项:name,age等等信息来查询
dao中代码:
//根据dc查找
@Override
public User findUserByDC(DetachedCriteria dc) {
User u = null;
Session s = null;
try{
s = HibernateUtils.getSession();
Criteria cri = dc.getExecutableCriteria(s);
List<User> users = cri.list();
for(User user:users){
System.out.println(user.getName());
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(s!=null){
s.close();
}
}
return u;
}
测试方法:
public static void main(String arg[]){
//插入两条数据
User u = new User();
u.setName("yinger");
u.setBirthday(new Date());
User u0 = new User();
u0.setName("hibernate");
u0.setBirthday(new Date());
UserManager um = new UserManager();
um.saveUser(u);
um.saveUser(u0);
String name = request.getParameter("name"); // 应用场景这里通常是有 request 的,这里是没有,代码会报错,请注意
Date birthday = request.getParameter("birthday");
DetachedCriteria dc = DetachedCriteria.forClass(User.class);
if(name!=null){
dc.add(Restrictions.eq("name", name));
}
if(birthday!=null){
dc.add(Restrictions.eq("birthday", birthday));
}
um.findUserByDC(dc);
}
7. N+1次查询和懒加载
N+1次查询:本来查出来的数据只有N条,但是执行了N+1条select语句进行查询
造成N+1查询的情况:
1.用Query.iterate可能会有N+1次查询。
2.懒加载时获取关联对象。
3.如果打开对查询的缓存即使用list也可能有N+1次查询。
测试代码:
public static void main(String[] args) {
add();
add();
System.out.println("-------------");
// ncpp();
iterate();
}
private static void ncpp() {
Session s = HibernateUtils.getSession();
Query q = s.createQuery("from IdCard");
List<IdCard> ics = q.list();
for (IdCard ic : ics) {
System.out.println(ic.getPerson().getName());
s.clear();
}
s.close();
}
private static void iterate() {
Session s = HibernateUtils.getSession();
Query q = s.createQuery("from IdCard");
Iterator<IdCard> it = q.iterate();
while(it.hasNext()) {
System.out.println(it.next().getPerson().getName());
}
s.close();
}
private static void add() {
Person p = new Person();
p.setName("person name");
IdCard card = new IdCard();
card.setLife(new Date());
// p.setIdCard(card); // 外键一对一映射的这种情况下 id_card表中的person_id字段没有值
card.setPerson(p);
Session s = null;
Transaction tr = null;
try {
s = HibernateUtils.getSession();
tr = s.beginTransaction();
s.save(p);
s.save(card);
tr.commit();
} catch (Exception e) {
if (tr != null)
tr.rollback();
} finally {
if (s != null)
s.close();
}
}
测试一:使用 iterate 方法
Query.iterate 可以从缓存中拿到数据
它查询的方式:首先它会从数据库中查询出所有的id,然后根据id在一级缓存和二级缓存中找,如果缓存中有数据,那么就不用访问数据库了
但是如果缓存中没有数据的话,就要根据id在数据库中一个一个的查,这个时候的效率就会很低
测试结果:
log4j:WARN No appenders could be found for logger (org.hibernate.cfg.Environment).
log4j:WARN Please initialize the log4j system properly.
Hibernate: insert into Person (name) values (?)
Hibernate: insert into id_card (life) values (?)
Hibernate: insert into Person (name) values (?)
Hibernate: insert into id_card (life) values (?)
-------------
Hibernate: select idcard0_.id as col_0_0_ from id_card idcard0_
Hibernate: select idcard0_.id as id1_, idcard0_.life as life4_1_, person1_.id as id0_, person1_.name as name3_0_ from id_card idcard0_ inner join Person person1_ on idcard0_.id=person1_.id where idcard0_.id=?
person name
Hibernate: select idcard0_.id as id1_, idcard0_.life as life4_1_, person1_.id as id0_, person1_.name as name3_0_ from id_card idcard0_ inner join Person person1_ on idcard0_.id=person1_.id where idcard0_.id=?
person name
测试二:使用 ncpp 方法
懒加载也可能造成N+1次查询
例如:one-to-one中查询从对象,然后访问从对象对应的主对象的属性值(因为查询从对象时默认是不会查询主对象的,这就是懒加载,但是查询主对象就不会懒加载)
测试结果:
log4j:WARN No appenders could be found for logger (org.hibernate.cfg.Environment).
log4j:WARN Please initialize the log4j system properly.
Hibernate: insert into Person (name) values (?)
Hibernate: insert into id_card (life) values (?)
Hibernate: insert into Person (name) values (?)
Hibernate: insert into id_card (life) values (?)
-------------
Hibernate: select idcard0_.id as col_0_0_ from id_card idcard0_
Hibernate: select idcard0_.id as id1_, idcard0_.life as life4_1_, person1_.id as id0_, person1_.name as name3_0_ from id_card idcard0_ inner join Person person1_ on idcard0_.id=person1_.id where idcard0_.id=?
person name
Hibernate: select idcard0_.id as id1_, idcard0_.life as life4_1_, person1_.id as id0_, person1_.name as name3_0_ from id_card idcard0_ inner join Person person1_ on idcard0_.id=person1_.id where idcard0_.id=?
person name
可以看到,两种方法的测试结果是一样的。
横线下面,共有三条select语句,但是数据库中只有两条idCard数据,这个就是 N+1 次查询
第一条语句查出所有的id,后面的两条根据查出来的id来查person表
解决方法:
1.可以利用二级缓存,也就是说,确定数据已经存入到了二级缓存中时才这么查询,否则效率是很低的
2.改变配置方式,例如改变关联对象的fetch的方式,如果改成 join 就不会有 N+1 次查询了
测试实例:IdCard.hbm.xml中配置
<one-to-one name="person" class="Person" constrained="true" fetch=""/>
测试代码:
public static void main(String[] args) {
add();
add();
System.out.println("-------------");
IdCard card = queryIdCardById(1);
System.out.println(card.getPerson().getName());
}
// 添加信息到数据库中
private static void add() {
Person p = new Person();
p.setName("person name");
IdCard card = new IdCard();
card.setLife(new Date());
// p.setIdCard(card); // 外键一对一映射的这种情况下 id_card表中的person_id字段没有值
card.setPerson(p);
Session s = null;
Transaction tr = null;
try {
s = HibernateUtils.getSession();
tr = s.beginTransaction();
s.save(p);
s.save(card);
tr.commit();
} catch (Exception e) {
if (tr != null)
tr.rollback();
} finally {
if (s != null)
s.close();
}
}
// 根据id查询IdCard
public static IdCard queryIdCardById(int id) {
IdCard card = null;
Session s = null;
try {
s = HibernateUtils.getSession();
card = (IdCard) s.get(IdCard.class, id);
// Hibernate.initialize(card); // 可以使用这个方法来初始化代理对象
System.out.println(card.getPerson().getName());
return card;
} catch (Exception e) {
e.printStackTrace();
} finally {
if (s != null) {
s.close();
}
}
return card;
}
输出结果:
log4j:WARN No appenders could be found for logger (org.hibernate.cfg.Environment).
log4j:WARN Please initialize the log4j system properly.
Hibernate: insert into Person (name) values (?)
Hibernate: insert into id_card (life) values (?)
Hibernate: insert into Person (name) values (?)
Hibernate: insert into id_card (life) values (?)
-------------
Hibernate: select idcard0_.id as id0_, idcard0_.life as life4_0_ from id_card idcard0_ where idcard0_.id=?
Hibernate: select person0_.id as id0_, person0_.name as name3_0_ from Person person0_ where person0_.id=?
person name
person name
查询了 两次,第一次查idcard,第二次查person
如果配置改为:
<one-to-one name="person" class="Person" constrained="true" fetch=""/>
重新测试:
log4j:WARN No appenders could be found for logger (org.hibernate.cfg.Environment).
log4j:WARN Please initialize the log4j system properly.
Hibernate: insert into Person (name) values (?)
Hibernate: insert into id_card (life) values (?)
Hibernate: insert into Person (name) values (?)
Hibernate: insert into id_card (life) values (?)
-------------
Hibernate: select idcard0_.id as id1_, idcard0_.life as life4_1_, person1_.id as id0_, person1_.name as name3_0_ from id_card idcard0_ inner join Person person1_ on idcard0_.id=person1_.id where idcard0_.id=?
person name
person name
查询了一次,不过使用的是内联接 inner join
其他问题:
HQL
1查询多个对象select art, user from Article art, User user where art.author.id=user.id and art.id=:id
这种方式返回的是Object[],Object[0]:article,Object[1]:user。
2分页query.setFirstResult,query.setMaxResults.
查询记录总数 query.iterate(“select count(*) from Person”).next()
3批量更新query.executeUpdate(),可能造成二级缓存有实效数据。
Criteria
1排序Criteria.addOrder(Order.desc(propertyName));
2关联查询criteria.setFetchMode(“propertyName”, FetchMode.SELECT)与映射文件中关联关系的fetch作用一致。
3投影Projections.rowCount(),max(propertyName), avg, groupProperty…
4分页Projections.rowCount(),criteria.setFirstResult(),criteria.setMaxResults()
5DetachedCriteria可在session外创建(在其他层创建比如在Service中创建)然后用getExecutableCriteria(session)方法创建Criteria对象来完成查询。
6Example查询,Example.create(obj);criteria.add(example)。