Hibernate基本查询
Qurey接口:Hibernate专门用来执行HQL语句的查询接口
使用方式:
Query query=session.createQuery("HQL");
query.setParameter("");
List resultList=query.list();</span></span>
HQL(Hibernate Query Language)
功能:
- 条件查询
- 支持投影下旬
- 分页查询
- 连接查询
- 分组查询
- 子查询
- 内置聚合函数
- 支持动态绑定参数
查询实体类的所有实例对象
@Test
public void queryAll() {
Session session=HandleSession.getSession();
Transaction transaction=session.beginTransaction();
try{
String hql="from Student ";</span>
Query query=session.createQuery(hql);// or "select stu from Student as stu"
List<Student> list=(List<Student>) query.list();
for(Student student:list){
System.out.println(student);
}
transaction.commit();
}catch (Exception e){
if(transaction!=null){
transaction.rollback();
}
e.printStackTrace();
}finally {
session.close();
}
}
@Test
public void querySingle(){
Session session=HandleSession.getSession();
Transaction transaction=session.beginTransaction();
try{
String hql="from Student ";
Student student=(Student) session.createQuery(hql).setMaxResults(1).uniqueResult();
System.out.println(student);
transaction.commit();
}catch (Exception e){
if(transaction!=null){
transaction.rollback();
}
e.printStackTrace();
}finally {
session.close();
}
}
投影查询:查询类的几个属性,通过用select语句只选择类的部分属性来实现:
Query query=session.createQuery("SELECT id,name FROM Student");
List list=query.list()
<span style="white-space:pre"> </span>public void queryShadow(){
Session session=HandleSession.getSession();
Transaction transaction=session.beginTransaction();
try{
String hql="select a.name,a.age from Student a";
Query query=session.createQuery(hql);
List<Object[]> list=query.list();
for(Object[] objects:list){
for(Object object:objects)
System.out.println(object);
}
transaction.commit();
}catch (Exception e){
if(transaction!=null){
transaction.rollback();
}
e.printStackTrace();
}finally {
session.close();
}
}
查询实例:添加相应的构造方法
在Student类中添加构造方法:
public Student(String name,int age){
this.name=name;
this.age=age;
}
<span style="white-space:pre"> </span>@Test
public void queryShadow2(){
Session session=HandleSession.getSession();
Transaction transaction=session.beginTransaction();
try{
String hql="select new Student (a.name,a.age) from Student a";
Query query=session.createQuery(hql);
List<Student> list=query.list();
for(Student student:list)
System.out.println(student);
transaction.commit();
}catch (Exception e){
if(transaction!=null){
transaction.rollback();
}
e.printStackTrace();
}finally {
session.close();
}
}
where条件语句:查询符合条件的对象
.号
比较运算符:=,<,>,<=,>=,is null,is not null
范围运算符:in,not in,between and,not between and
模式匹配符:like'模式字符串' 其中可用‘%’代替任意长度字符串,'_'代表单个字符
逻辑运算符:and,not,or
用于集合的运算符:is empty,is not empty
HQL常见函数:
字符串相关:upper(s),lower(s),concat(s1,s2),substring(s,offset,length),length(s)
trim(s),locate(search,s,offset)
数字:abs(n),sort(n),mod(dividend,divisor)
集合:size(c) 返回集合中元素的个数
日期时间:current_date(),current_time(),current_timestamp(),
year(d),moth(d),day(d),hour(d),minute(d),second(d)
distinct:去掉相同的数据项
oder by:desc 按倒序排列,asc按升序排列
参数绑定:where字句中经常需要动态设置查询函数,Hibernate提供了两种绑定参数的方式
按参数名字绑定
按参数位置绑定
@Test
public void queryArguments(){
Session session=HandleSession.getSession();
Transaction transaction=session.beginTransaction();
try{
/*按参数名字绑定
* String hql="select new Student (stu.name,stu.age)from Student as stu where stu.name=? and stu.age=?";
Query query=session.createQuery(hql);
query.setString(0,"wang");
query.setInteger(1,21);
* */
/*按参数名字绑定*/
String hql="select new Student (stu.name,stu.age)from Student as stu where stu.name=:name and stu.age=:age";
Query query=session.createQuery(hql);
query.setString("name","wang");
query.setInteger("age",21);
List<Student> list=query.list();
for(Student student:list)
System.out.println(student);
transaction.commit();
}catch (Exception e){
if(transaction!=null){
transaction.rollback();
}
e.printStackTrace();
}finally {
session.close();
}
}
聚合函数:查询结果作为Long返回
count(),avg(),sum(),max(),min()
@Test
public void queryCount(){
Session session=HandleSession.getSession();
Transaction transaction=session.beginTransaction();
try{
/*String hql="select count(stu.id)from Student as stu";*/
String hql="select max(stu.age)from Student as stu";
Query query=session.createQuery(hql);
List<Student> list=query.list();
System.out.println(list.get(0));
transaction.commit();
}catch (Exception e){
if(transaction!=null){
transaction.rollback();
}
e.printStackTrace();
}finally {
session.close();
}
}
group by:对查询的数据,根据条件进行分组,常与聚合函数配合使用
@Test
public void queryCount(){
Session session=HandleSession.getSession();
Transaction transaction=session.beginTransaction();
try{
//按班级查询每个班的人数
String hql="select count(stu.id),stu.class from Student as stu group by stu.class";
Query query=session.createQuery(hql);
List<Object[]> list=query.list();
for(Object[] objects:list)
for(Object object:objects)
System.out.println(object);
transaction.commit();
}catch (Exception e){
if(transaction!=null){
transaction.rollback();
}
e.printStackTrace();
}finally {
session.close();
}
}
having关键字与group by配合使用,对分组查询后的数据进行过滤
分页查询:Query接口提供了以下两个用于分页显示查询结果的方法
setFirstResult(int firstResult);
setMaxResult(int maxResult);