1、最基本的HQL查询,查询实体集合
String hql = "select o from Order o";
Query query = session.createQuery(hql);
List list = query.list();
2、带条件的HQL查询,查询实体集合
Long price = new Long(3000);
// String hql = "select o from Order o where o.totalPrice > ?";
String hql = "select o from Order o where o.totalPrice > :total";
Query query = session.createQuery(hql);
// query.setLong(0, price.longValue()); //下标从0开始jdbc从1开始
query.setLong("total", price.longValue());
List list = query.list();
3、关联查询
// String hql = "select o from Order o, User u where u.userId=o.userId
// and u.name=?";
String hql = "select o from Order o, User u where o.tblUser = u and u.name=?";
Query query = session.createQuery(hql);
query.setString(0, "t55");
List list = query.list();
5、1对多关联查询
//从查询可以将多的一端查询出来存储到一的一端从而辟免延迟加载
String hql = "select u from User u left outer join fetOrderers";
Query query = session.createQuery(hql);
List list = query.list();
6、统计查询
String hql = "select count(o) from Order o";
Query query = session.createQuery(hql);
Integer count = (Integer)query.uniqueResult();
7、分页查询
String hql = "select o from Order o";
Query query = session.createQuery(hql);
query.setFirstResult(3);//从第几条记录开始获取数据:(页数-1)*每页记录数
query.setMaxResults(3);//每页显示的记录数
List list = query.list();
for (int i = 0; i < list.size(); i++) {
Order order = (Order)list.get(i);
System.out.println(order.getOrderId());
}
8、多返回结果查询
String hql = "select o.totalPrice,u.name from Order o, User u where o.tblUser=u";
//o.tblUser是在Order 对象中的一个属性类型为User
Query query = session.createQuery(hql);
List list = query.list();
for (int i = 0; i < list.size(); i++) {
Object[] objects = (Object[])list.get(i);
System.out.println(objects[1]);
System.out.println(objects[0]);
}
9、使用值对象保存多返回结果查询
1、新建一个TestBean 封装name,和totalPrice两个属性
String hql = "select new com.accp.ch6.entity.vo.TestBean(u.name,o.totalPrice) from Order o, User u where o.tblUser=u";
Query query = session.createQuery(hql);
List list = query.list();
for (int i = 0; i < list.size(); i++) {
TestBean bean = (TestBean)list.get(i);
System.out.println(bean.getName());
System.out.println(bean.getPrice());
}
10、按标准对象查询
Criteria criteria = session.createCriteria(Order.class);
// 添加查询条件
criteria.add(Restrictions.or(Restrictions.ge("totalPrice", new Long(
3000)), Restrictions.le("totalPrice", new Long(2000))));
// criteria.add(Expression.or(Expression.ge("totalPrice", new
// Long(3000)),
// Expression.le("totalPrice", new Long(2000))));
// 追加函数 统计
criteria.setProjection(Projections.count("orderId"));
List list = criteria.list();
System.out.println(list.size());
11、按样例查询:非主键,非null的属性作为查询条件
User user = new User();
user.setName("t");
Criteria criteria = session.createCriteria(User.class);
// enableLike代表模糊查询
criteria.add(Example.create(user).enableLike(MatchMode.ANYWHERE));
List list = criteria.list();
System.out.println(list.size());
注 :将查询查条件封装到实体里面只要实体里的属性不为空就会作为条件 同时主键 和 null不能做为查询条件
12、按原生SQL语句查询
String sql = "select u.* from tbl_user u where u.userId=1";
SQLQuery sqlQuery = session.createSQLQuery(sql);
//告诉它此时list里面包含的是User对象如果不加则包含的是Object
sqlQuery.addEntity(User.class);
List list = sqlQuery.list();
for (int i = 0; i < list.size(); i++) {
User user = (User)list.get(i);
System.out.println(user.getName());
}
注 :此时的 tbl_user 不是实体而是数据库里的表