hibernate的查询数据方式之HQL和QBC

单表查询（HQL hibernate Query Language）

1. 查询所有的数据

Query query = session.createQuery("from User");//from 实体类
List<User> list = query.list();

2. 排序查询

Query query = session.createQuery("from User order by user_id");
List<User> list = query.list();

3. 条件查询

Query query = session.createQuery("from User where user_name=?");
query.setParameter(0,"sofency");
List<User> list = query.list();

4. 分页查询(MySQL)

Query query = session.createQuery("from User");
//设置开始位置
query.setFirstResult(0);
query.setMaxResults(2);//每页最多两条数据
List<User> list = query.list();

5. 投影查询 查询部分字段

Query query = session.createQuery("select user_id,user_name from User");//返回的是数组形式
List<Object[]> list = query.list();

6. 聚集函数查询(count(*) max() min(),avg())

Query query = session.createQuery("select count(*) from User");//查询记录数
Object obj = query.uniqueResult();//对象不能直接转换到int类型
Long lobj = (Long) obj;
int  count = (int) lobj;
Query query = session.createQuery("select max(user_age) from User");//查询最大年纪的员工。
List<User> list = query.list();

多表查询（HQL）

1. 内连接

Query query = session.createQuery("from User u inner join u.setRole");//User 表和Role表 User是实体类的名字不是表名
//返回的是以数组的形式   数组元素的类型是对象[User,Role]
List<Object[]> list = query.list();

2. 迫切内连接

Query query = session.createQuery("from User u inner join fetch u.setRole");
List<User> list = query.list();
//返回的是User对象

3. 左外连接

Query query = session.createQuery("from User u left outer join u.setRole");
List<Object[]> list = query.list();
//返回的是数组  数组的元素是对象  右外连接 right outer join

4. 迫切左外连接

Query query = session.createQuery("from User u left outer join fetch u.setRole");
List<User> list = query.list();
//返回的是对象(User)

单表查询（QBC Query By Criteria）

1. 查询所有

Criteria criteria = session.createCriteria(User.class);
List<User> list = criteria.list();

2. 条件查询

Criteria criteria = session.createCriteria(User.class);
criteria.add(Restrictions.eq("user_id",3)).add(Restrictions.eq("user_name","sofency"));//添加约束条件
List<User> list = criteria.list();

3. 排序查询

Criteria criteria = session.createCriteria(User.class);
criteria.addOrder(Order.desc("user_id"));//生x
List<User> list = criteria.list();

4. 分页查询

Criteria criteria = session.createCriteria(User.class);
criteria.setFirstResult(0);
criteria.setMaxResults(2);//每页最多两条数据
List<User> list = criteria.list();

5. 聚集函数查询 (插叙记录数)

Criteria criteria = session.createCriteria(User.class);
criteria.setProjection(Projections.rowCount());
//最大年纪
criteria.setProjection(Projections.max(user_age));
//最小
criteria.setProjection(Projections.min(user_age));
Object obj = criteria.uniqueResult();
Long lobj = (Long) obj;
int count = (int) lobj;

6. 多表查询

Criteria criteria = session.createCriteria(User.class);
criteria.setProjection(Projections.rowCount()).add(Restrictions.eq("Role_desc","普通员工"));//查询身份普通员工的个数