java hibernate 查询_JavaWeb学习：Hibernate查询方式

Hibernate查询方式：

一、OID查询

Hibernate根据对象的OID(主键)进行检索

①、get方法：Customer customer=session.get(Customer.class,1l);

②、load方法：Customer customer=session.load(Customer.class,1l);

二、对象导航检索

Hibernate根据一个已经查询到的对象，获得其关联的对象的一种查询方式。

LinkMan linkMan = session.get(LinkMan.class,1l);

Customer customer=linkMan.getCustomer();

Customer customer= session.get(Customer.class,2l);

Set linkMans = customer.getLinkMans();

三、HQL检索

HQL查询：Hibernate Query Language，Hibernate的查询语言，是一种面向对象的方式的查询语言，语法类似SQL。通过session.createQuery()，用于接收一个HQL进行查询方式。

①、简单查询

@Testpublic voiddemo() {

Session session=HibernateUtils.getCurrentSession();

Transaction transaction=session.beginTransaction();//Query query = session.createQuery("from com.hibernate.domain.Customer");//from Customer(类的全名称，不适表名)

Query query = session.createQuery("from Customer ");

List list =query.list();for(Customer customer : list) {

System.out.println(customer);

}

transaction.commit();

}

②、别名查询

@Testpublic voiddemo2() {

Session session=HibernateUtils.getCurrentSession();

Transaction transaction=session.beginTransaction();

Query query= session.createQuery("select c from Customer c");

List list =query.list();for(Customer customer : list) {

System.out.println(customer);

}

transaction.commit();

}

③、排序查询

@Testpublic voiddemo2() {

Session session=HibernateUtils.getCurrentSession();

Transaction transaction=session.beginTransaction();//默认asc(升序), 可设置desc(降序)

Query query = session.createQuery("from Customer order by cust_id ");

List list =query.list();for(Customer customer : list) {

System.out.println(customer);

}

transaction.commit();

}

④、条件查询

@Testpublic voiddemo2() {

Session session=HibernateUtils.getCurrentSession();

Transaction transaction=session.beginTransaction();//一、条件参数索引

Query query = session.createQuery("from Customer where cust_name=?1 and cust_id=?0");

query.setParameter(1, "zhangsan");

query.setParameter(0, 4l);

List list =query.list();for(Customer customer : list) {

System.out.println(customer);

}

transaction.commit();

}

@Testpublic voiddemo2() {

Session session=HibernateUtils.getCurrentSession();

Transaction transaction=session.beginTransaction();//二、条件参数名

Query query = session.createQuery("from Customer where cust_name=:name and cust_id=:id");

query.setParameter("name", "lisi");

query.setParameter("id", 1l);

List list =query.list();for(Customer customer : list) {

System.out.println(customer);

}

transaction.commit();

}

⑤、投影查询

投影查询：查询对象的某个或某些属性

@Testpublic voiddemo2() {

Session session=HibernateUtils.getCurrentSession();

Transaction transaction=session.beginTransaction();//单个属性//List list = session.createQuery("select c.cust_name from Customer c").list();//for (Object object : list) {//System.out.println(object);//}//多个属性//List list = session.createQuery("select c.cust_id,c.cust_name

from Customer c").list();//for (Object[] objects : list) {//System.out.println(Arrays.toString(objects));//}//多个属性，封装到新的对象中//org.hibernate.PropertyNotFoundException: no appropriate constructor in class:

com.hibernate.domain.Customer//类中没有适当的构造函数

List list = session.createQuery("select new Customer(cust_id,cust_name)

from Customer").list();for(Customer customer : list) {

System.out.println(customer);

}

transaction.commit();

}

类中没有适当的构造函数解决方案：

public classCustomer {publicCustomer() {

}publicCustomer(Long cust_id, String cust_name) {this.cust_id =cust_id;this.cust_name =cust_name;

}

⑥、分页查询

@Testpublic voiddemo2() {

Session session=HibernateUtils.getCurrentSession();

Transaction transaction=session.beginTransaction();

Query query=session.createQuery("from Customer");

query.setFirstResult(3);

query.setMaxResults(2);

List list=query.list();for(Customer customer : list) {

System.out.println(customer);

}

transaction.commit();

}

注意：HQL、QBC两个Demo在实现分页调用setFirstResult和setMaxResults方法一直报java.lang.UnsupportedOperationException: query result offset is not supported的异常

解决方法：修改hibernate方言

org.hibernate.dialect.SQLServer2008Dialect

⑦、分组统计查询

@Testpublic voiddemo2() {

Session session=HibernateUtils.getCurrentSession();

Transaction transaction=session.beginTransaction();//聚合函数的使用：count(),max(),min(),avg(),sum()//Object object=session.createQuery("select count(*) from Customer").uniqueResult();//System.out.println(object);//分组统计

List list=session.createQuery("select cust_source,count(*) from Customer group by cust_source").list();for(Object[] objects : list) {

System.out.println(Arrays.toString(objects));

}

transaction.commit();

}

分组统计加条件：检索统计结果大于2的数据

//分组统计

List list=session.createQuery("select cust_source,count(*) from Customer group by cust_source

having count(*) > 2").list();for(Object[] objects : list) {

System.out.println(Arrays.toString(objects));

}

⑧、多表查询

1、SQL的多表查询

连接查询

交叉连接：笛卡尔积(查询表的乘积)select * from A,B;

内连接             ：inner join (inner 可以省略)  表的交集

隐式内连接：select * from A,B where A.id = B.aid;

显示内连接：select * from A join B on A.id = B.aid;

外连接              :

左外连接:left outer join(outer 可以省略)

select * from A left outer join B on A.id= B.aid;

右外连接:right outer join(outer 可以省略)

select * from A right outer join B on A.id = B.aid;

2、HQL的多表查询

连接查询

交叉连接

内连接：返回值为Object[]数组，

显示内连接

隐式内连接

迫切内连接：相对内连接 inner join fetch关键字，返回值为对象

外连接

左外连接

右外连接

迫切左外连接

@Testpublic voiddemo2() {

Session session=HibernateUtils.getCurrentSession();

Transaction transaction=session.beginTransaction();//内连接//List list=session.createQuery("from Customer c inner join c.linkMans").list();//for (Object[] objects : list) {//System.out.println(Arrays.toString(objects));//}//迫切内连接

List list = session.createQuery("from Customer c inner join fetch c.linkMans").list();for(Customer customer : list) {

System.out.println(customer);

}

transaction.commit();

}

注意：迫切连接(from Customer c inner join fetch c.linkMans)有可能返回对象重复，建议写成(select distinc c from Customer c inner join fetch c.linkMans)

四、QBC检索

QBC查询：Query By Criteria，条件查询。是一种更加面向对象化的查询的方式。

①、简单查询

@Testpublic voiddemo2() {

Session session=HibernateUtils.getCurrentSession();

Transaction transaction=session.beginTransaction();

Criteria criteria=session.createCriteria(Customer.class);

List list=criteria.list();for(Customer customer : list) {

System.out.println(customer);

}

transaction.commit();

}

②、排序查询

@Testpublic voiddemo2() {

Session session=HibernateUtils.getCurrentSession();

Transaction transaction=session.beginTransaction();

Criteria criteria=session.createCriteria(Customer.class);//criteria.addOrder(Order.asc("cust_id"));

criteria.addOrder(Order.desc("cust_id"));

List list=criteria.list();for(Customer customer : list) {

System.out.println(customer);

}

transaction.commit();

}

③、分页查询

@Testpublic voiddemo2() {

Session session=HibernateUtils.getCurrentSession();

Transaction transaction=session.beginTransaction();

Criteria criteria=session.createCriteria(Customer.class);//criteria.addOrder(Order.asc("cust_id"));

criteria.addOrder(Order.desc("cust_id"));

criteria.setFirstResult(3);

criteria.setMaxResults(3);

List list=criteria.list();for(Customer customer : list) {

System.out.println(customer);

}

transaction.commit();

}

④、条件查询

@Testpublic voiddemo12() {

Session session=HibernateUtils.getCurrentSession();

Transaction transaction=session.beginTransaction();

Criteria criteria= session.createCriteria(Customer.class);/*** = eq

* > gt

* >= ge

* < lt

* <= le

* <> ne

* like

* in

* and

* or*/

//and//criteria.add(Restrictions.eq("cust_name", "lisi"));//criteria.add(Restrictions.eq("cust_id", 1l));//or//criteria.add(Restrictions.or(Restrictions.eq("cust_name", "lisi"),//Restrictions.eq("cust_name", "zhangsan")));//多or

Disjunction dis =Restrictions.disjunction();

dis.add(Restrictions.eq("cust_name", "lisi"));

dis.add(Restrictions.eq("cust_name", "zhangsan"));

dis.add(Restrictions.eq("cust_name", "tianqi"));

dis.add(Restrictions.eq("cust_name", "zhaoliu"));

criteria.add(dis);

List list =criteria.list();for(Customer customer : list) {

System.out.println(customer);

}

transaction.commit();

}

⑤、统计查询

@Testpublic voiddemo2() {

Session session=HibernateUtils.getCurrentSession();

Transaction transaction=session.beginTransaction();

Criteria criteria= session.createCriteria(Customer.class);/*** add() 添加条件

* addOrder() 添加排序

* setProjection() 添加聚合函数和group by having*/criteria.setProjection(Projections.rowCount());

Long num=(Long)criteria.uniqueResult();

System.out.println(num);

transaction.commit();

}

⑥、离线查询

@Testpublic voiddemo2() {//创建离线对象DetachedCriteria

DetachedCriteria detachedCriteria=DetachedCriteria.forClass(Customer.class);//设置条件

detachedCriteria.add(Restrictions.like("cust_name", "zh%"));

Session session=HibernateUtils.getCurrentSession();

Transaction transaction=session.beginTransaction();//主要：和session建立关系，并创建Criteria对象

Criteria criteria =detachedCriteria.getExecutableCriteria(session);

List list=criteria.list();for(Customer customer : list) {

System.out.println(customer);

}

transaction.commit();

}

五、SQL检索

①、简单查询结果为Object[]数组

@Testpublic voiddemo12() {

Session session=HibernateUtils.getCurrentSession();

Transaction transaction=session.beginTransaction();

SQLQuery sqlQuery=session.createSQLQuery("select * from cst_customer");

List list=sqlQuery.list();for(Object[] objects : list) {

System.out.println(Arrays.toString(objects));

}

transaction.commit();

}

②、简单查询结果为对象

@Testpublic voiddemo12() {

Session session=HibernateUtils.getCurrentSession();

Transaction transaction=session.beginTransaction();

SQLQuery sqlQuery=session.createSQLQuery("select * from cst_customer");

sqlQuery.addEntity(Customer.class);

List list=sqlQuery.list();for(Customer customer : list) {

System.out.println(customer);

}

transaction.commit();

}