HQL查询

1.建立实体类

package com.entity;

import java.util.HashSet;
import java.util.Set;

public class Customer {

	 private int id;  
	    private String username;  
	    private int age;  
	    private String sex;  
	    private String city;
	    //用户有多个订单
	    private Set<Order>order=new HashSet<Order>();
		public int getId() {
			return id;
		}
		public void setId(int id) {
			this.id = id;
		}
		public String getUsername() {
			return username;
		}
		public void setUsername(String username) {
			this.username = username;
		}
		public int getAge() {
			return age;
		}
		public void setAge(int age) {
			this.age = age;
		}
		public String getSex() {
			return sex;
		}
		public void setSex(String sex) {
			this.sex = sex;
		}
		public String getCity() {
			return city;
		}
		public void setCity(String city) {
			this.city = city;
		}
		public Customer(){}
		public Customer(String username,int age)
		{
			this.username=username;
			this.age=age;
		}
		
		public Set<Order> getOrder() {
			return order;
		}
		public void setOrder(Set<Order> order) {
			this.order = order;
		}
		@Override
		public String toString() {
			return "Customer [id=" + id + ", username=" + username + ", age="
					+ age + ", sex=" + sex + ", city=" + city + "]";
		}  
	   


}

2.Customer.hbm.xml

<?xml version="1.0"?>  
<!--  
  ~ Hibernate, Relational Persistence for Idiomatic Java  
  ~  
  ~ License: GNU Lesser General Public License (LGPL), version 2.1 or later.  
  ~ See the lgpl.txt file in the root directory or <http://www.gnu.org/licenses/lgpl-2.1.html>.  
  -->  
<!DOCTYPE hibernate-mapping PUBLIC  
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"  
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">  
  
<hibernate-mapping>  
  
     <class name="com.entity.Customer" table="CUSTOMER">  
        <id name="id" column="id"  type="java.lang.Integer"> 
        <generator class="increment"></generator> 
         </id>  
     <property name="username" column="username" type="string" />  
     <property name="age" column="age" type="int" />  
     <property name="sex" column="sex" type="string" />  
     <property name="city" column="city" type="string" />  
     <set name="order">
       <!-- 确定关联的外键列 -->
       <key column="cid"/>
       <!-- 映射到关联类属性 -->
       <one-to-many class="com.entity.Order"/>     
     </set>
    </class>
  
</hibernate-mapping>  

3.hibernate.cfg.xml

<?xml version='1.0' encoding='UTF-8'?>  
  
<!DOCTYPE hibernate-configuration PUBLIC  
          "-//Hibernate/Hibernate Configuration DTD 3.0//EN"  
          "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">  
<!-- Generated by MyEclipse Hibernate Tools.                   -->  
<hibernate-configuration>

<session-factory>
	<property name="hibernate.dialect">
		org.hibernate.dialect.Oracle10gDialect
	</property>
	<property name="hibernate.connection.url">
		jdbc:oracle:thin:@localhost:1521:orcl
	</property>
	<property name="hibernate.connection.username">qxgl</property>
	<property name="hibernate.connection.password">123</property>
	<property name="hibernate.connection.driver_class">
		oracle.jdbc.OracleDriver
	</property>
    <!-- <property name="hibernate.hbm2ddl.auto">update</property> -->
	<mapping resource="com/entity/Customer.hbm.xml" />
	<mapping resource="com/entity/User.hbm.xml" />
	<mapping resource="com/entity/Order.hbm.xml" />
    <mapping resource="com/entity/Student.hbm.xml" />
	<mapping resource="com/entity/Course.hbm.xml" />
</session-factory>
</hibernate-configuration>  

4.CustomerDAO.java

package com.DAO;

import java.util.List;




import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.criterion.Projections;
import org.hibernate.query.Query;
import org.junit.Test;





import com.entity.Customer;
import com.utils.HibernateUtils;

public class CustomerDAO {

	@Test  //根据id查询用户
	public void testQueryCustomererById(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;	
		Session session = null;
		Transaction ts = null;

		
		try {
  		
			session = HibernateUtils.getSession();
			ts = session.beginTransaction();
			
			Query query = session.createQuery("from Customer cus where cus.id=2");
			List customers =  query.list();
			for(int i=0;i<customers.size();i++)
			{
			Customer cus =(Customer)customers.get(i);
			System.out.println("姓名:"+cus.getUsername()+" 年龄:"+cus.getAge()+" 性别:"+cus.getSex()+" 城市:"+cus.getCity());
			}


			
			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		}
	}
	
	@Test  //属性查询
	public void testQueryCustomerName(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;	
		Session session = null;
		Transaction ts = null;

		
		try {
  		
			session = HibernateUtils.getSession();
			ts = session.beginTransaction();
			
			Query query = session.createQuery("select cus.username from Customer cus");
			List cusnames = query.list();

			for(int i=0;i<cusnames.size();i++)
			{
			String name=(String)cusnames.get(i);
			System.out.println(i+"姓名:"+"--"+name);
			}
			
			

			
			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		}
	}

	@Test  //实例化查询
	public void testQueryCustomerMessage(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;	
		Session session = null;
		Transaction ts = null;

		
		try {
  		
			session = HibernateUtils.getSession();
			ts = session.beginTransaction();
			
			Query query = session.createQuery("select new Customer(cus.username,cus.age) from Customer as cus");
			List customers = query.list();

			for(int i=0;i<customers.size();i++)
			{
				Customer cus=(Customer)customers.get(i);
				System.out.println("姓名:"+cus.getUsername()+" 年龄:"+cus.getAge());
			}
			

			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		}
	}
	
	@Test  //统计查询
	public void testQueryCustomerMessageCount(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;	
		Session session = null;
		Transaction ts = null;

		
		try {
  		
			session = HibernateUtils.getSession();
			ts = session.beginTransaction();

			Query query = session.createQuery("select count(*)from Customer");
			 Object count=(Object)query.uniqueResult();
			 System.out.println("共有"+count+"条记录");

			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		}
	}

	@Test  //子查询
	public void testQueryCustomerMessage2(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;	
		Session session = null;
		Transaction ts = null;

		
		try {
  		
			session = HibernateUtils.getSession();
			ts = session.beginTransaction();

			Query query =session.createQuery(
				    "from Customer cus where cus.age>(select  avg(age) from Customer)");
			List ages=query.list();
		    for(int i=0;i<ages.size();i++)
		    {
		     Customer costmers=(Customer)ages.get(i);
		   	 System.out.println(costmers.getUsername()+" "+costmers.getAge()+" "+costmers.getSex()+" "+costmers.getCity());
		    }

			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		}
		
	  

	}

	
	@Test
	public void fenYe()
	{
		SessionFactory sf = null;	
		Session session = null;
		Transaction ts = null;
		
		try {
		System.out.println("分页查询....");
        Customer cus = null;
        session = HibernateUtils.getSession();
		ts = session.beginTransaction();
        Criteria criteria = session.createCriteria(Customer.class);
        List customers=criteria.list();

        criteria.setProjection(Projections.count("id"));
        Number sum=(Number)criteria.uniqueResult();
        System.out.println("用户人数"+sum);
        int pageNumber=3;
        int sum2=sum.intValue();
        int count=sum2/pageNumber+1;
        for(int i=0;i<count;i++) {
            System.out.println("第"+i+"页");
            for (int j = i*pageNumber; j <(i+1)*pageNumber; j++) {
                cus = (Customer) customers.get(j);
                System.out.println(j + " " + cus.getUsername() + " " + cus.getAge()+ " " + cus.getCity());
            }

        }

		ts.commit();
	} catch (HibernateException e) {
		// TODO Auto-generated catch block
		if(ts != null)
		{
			ts.rollback();
		}
		e.printStackTrace();
	}
	}
}

截图：

数据库中customer表

1.根据id查询用户

2.属性查询

3.实例化查询

4.统计查询

5.子查询