1.hql查询
例1:
//创建query对象
String hql="from employee e where e.slary=? and name like ?";
Query query =session.createQuery(hql);
//绑定参数
query.setFloat(0,8000).setString(1,"aa");
//执行查询
List<Employee> list=query.list();
例2:使用参数命名规则
String hql="from employee e where e.salary=:salary and e.name=:name"
Query query=session.createQuery(hql);
query.setFloat("salary",10000).setString("name","xie");
List<Employee> list=query.list();
例3:分页查询
String hql="from employee";
Query query=session.creqteQuery(hql);
int pageNo=3;//我要查第三页的内容
int pageSize=5;//每页显示5条记录
List<Employee> list=query.setFirstResult((pageNo-1)*pageSize).setMaxResult(pageSize).list();
*例4:投影查询*(查询结果只包含实体类的部分属性)
String hql="select new Employee(e.email,e.salary,e.name) from employee e where e.dept=:dept";(前提employee中要有包含email,salary,name的构造函数)
Query query=session.createQuery(hql);
Dept dept=new Dept();
dept.setId(80);
List<Employee> list=query.setEntity("dept",dept).list();
*例5:报表查询*(用于数据分组和统计,比如:group by ,count,val)
String hql="select max(e.salary) from employee e group by dept having e.salary >:salary";
Query query=session.creqteQuery(hql);
query.setFloat("salary",12000).list();
*例6:左外连接和迫切左外连接*(以department和employee为例)
迫切左外连接:String hql=”from Department dept left join fetch dept.employee”;
左外连接:String hql =”from Department dept left join dept.employee”;
区别:迫切左外连接中每个department关联的employee的集合都被初始化,存放的是employee的实体类对象;
左外连接中结果返回的是对象数组类型,有没有初始化看对懒加载的配置情况。
2.hibernate的qbc和本地sql检索
qbc
Qbc查询就是通过hibernate提供的query by criteria来查询对象。这种api封装了sql语句的动态拼接。
例1:
//创建一个criteria对象
Criteria criteria=session.createCriteria(Employee.class);
//添加查询条件
criteria.add(Restrictions.eq(“email”,”123@qq.com”));
criteria.add(Restrictions.gt(“salary”,”500”));
//执行查询
criteria.list();
//查询语句为:
from employee where email=”123@qq.com “ and salary>500
例2:and 和 or的表示 以及统计查询
Criteria criteria=session.createCriteria(Employee.class);
//and
Conjunction conjunction =Restrictions.conjunction();
Conjunction.add(Restrictions.eqProperty(“name”,”xie”));
//or
Disjunction disjunction= Restrictions.disjunction();
disjunction.add(Restrictions.isnull(“email”));
//执行
criteria.list();
//语句:
from employee where name=”xie” or email is not null;
//统计查询(Projection),查询salary最大的employee
criteria.setProjection(Projection.max(“salary”));
//排序
criteria.setOrder(Order.asc(“salary”));
//分页
criteria.setFirstResult((pageNo-1)*pageSize).setMaxResults(pageSize).list();
本地sql查询:完善hql不能覆盖所有的查询特性
例:执行插入操作
String sql=”insert into employee values(?,?)”;
Query qyery=session.createSqlQuery(sql);
query.setInteger(0,10).setString(1,”xie”).executeUpdate();
说明:看视频写了四个笔记,写在csdn算是复习一遍