HibernateHQL查询

HQL查询:

HQL查询,查询的基础是类所以Products是类名不是表名！



查询所有的行和列：
from Products



Session s = HibernateSessionFactory.getSession();

String sql = "";

sql = "from PetInfo";

Query q = s.createQuery(sql);
List list = q.list();
Iterator it = list.iterator();

while( it.hasNext() ){
   PetInfo pet = (PetInfo)it.next();
   System.out.println(pet.getPetCute()+"   "+pet.getPetName());
  }
s.close();



查询所有的行和部分的列：
select id,name,demo from Products
当查询所有列时，返回的集合中存储的是实体Bean对象
当查询部分列时，返回的集合中存储的是Object[]



Session s = HibernateSessionFactory.getSession();
String sql = "";

sql = "select petId,petName from PetInfo";

Query q = s.createQuery(sql);

List list = q.list();
Iterator it = list.iterator();

while( it.hasNext() ){
   Object []obj = (Object[])it.next();
   System.out.println(obj[0]+"   "+obj[1]);
  }
s.close();





精确查询：
from Products where name='2'



Session s = HibernateSessionFactory.getSession();

String sql = "";

sql = "from PetInfo where pet_name='aaa'";

Query q = s.createQuery(sql);

List list = q.list();
Iterator it = list.iterator();

while( it.hasNext() ){
   PetInfo pet = (PetInfo)it.next();
   System.out.println(pet.getPetCute()+"   "+pet.getPetName());
  }
s.close();



模糊查询：
from Products where name like '%2%'
"from Products where name like '%" + name + "%'"



Session s = HibernateSessionFactory.getSession();

String sql = "";

sql = "from PetInfo where (petName like '%o%' or petId<=10)";

//sql = "from PetInfo where not (petName like '%o%' or petId<=10)";

Query q = s.createQuery(sql);

List list = q.list();
Iterator it = list.iterator();

while( it.hasNext() ){
   PetInfo pet = (PetInfo)it.next();
   System.out.println(pet.getPetCute()+"   "+pet.getPetName());
  }
s.close();





条件查询：
from Products where id<3



范围查询：
from Products where id>3 and id<8
from Products where id between 3 and 8



子查询：
from Products where id not in(select id from Products where price>5.0)



逻辑语句：
and or not
from Products where id >3 and price<7.0
from Products where name='5' or price>7.0



分页查询：
sql = "from Products";
Query q = s.createQuery(sql);
//分页
q.setFirstResult(3);  //从第几行开始取数据
q.setMaxResults(3);   //总共取多少行数据
List list = q.list();



统计函数的使用：
count,max,avg
select count(pro) from Products pro



Session s = HibernateSessionFactory.getSession();

String sql = "";



sql = "select avg(petId) from PetInfo";

Query q = s.createQuery(sql);

List list = q.list();
Iterator it = list.iterator();

Object obj = q.uniqueResult();
ystem.out.println(((Float)obj).floatValue());
s.close();





查询的结果只有一个值时：
Object obj = q.uniqueResult();

分组，排序：
select count(counts) from Products group by counts
from Products order by price asc/desc



使用占位符:只能在where后面使用，单独使用，不能出现'%?%'
sql = "from Student where name=?";
Query q = s.createQuery(sql);
q.setString(0,"1");