hibernate——笔记六

排序

分页

投影查询

分组查询

映射文件中定义命名查询语句

package com.test;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;

import com.Util.HibernateSessionFactory;
import com.bean.User;

public class Hql {
	private static SessionFactory sessionFactory = HibernateSessionFactory.getSessionFactory();
	
	public static void show(Query query){
		List<User> list = query.list();
		for(Object obj:list){
			System.out.println(obj.toString());
		}
	}
	
	//简单查询
	public static void query(){
		Session session = sessionFactory.openSession();
		String hql = "from User";
		session.beginTransaction();
		
		Query query = session.createQuery(hql);
		
		show(query);
		
		session.close();
	}
	
	//使用别名
	public static void query1(){
		Session session = sessionFactory.openSession();
		session.beginTransaction();
		String hql = "from User u where u.uid=:id";
		Query query = session.createQuery(hql);
		query.setInteger("id",1);
		show(query);
		session.close();
		
	}
	
	//排序
	public static void query2(){
		Session session = sessionFactory.openSession();
		session.beginTransaction();
		String hql = "from User order by uid desc";
		Query q = session.createQuery(hql);
		show(q);
		session.close();
	}
	
	//分页 pageNum页数 显示第几页
	public static void query3(int pageNum){
		//每页的行数
		int pageRow = 3;
		
		Session session = sessionFactory.openSession();
		session.beginTransaction();
		String hql1 = "select count(*) from User";
		Query q1 = session.createQuery(hql1);
		//总行数
		long Count =  (long) q1.list().get(0); 
		//总页数
		int pageCount = 0;
		if(Count%pageRow==0)
			pageCount = (int) (Count/pageRow);
		else
			pageCount = (int) (Count/pageRow+1);
		//如果要显示的页数大于总页数，显示最后一页
		if(pageNum>pageCount)
			pageNum = pageCount;
		//起始行
		int satrtRow = pageNum*(pageRow-1);
		
		String hql2 = "from User";
		Query q2 = session.createQuery(hql2);
		q2.setFirstResult(satrtRow);
		q2.setMaxResults(pageRow);
		
		show(q2);
		session.close();
	}
	
	//投影查询
	public static void query4(){
		Session session = sessionFactory.openSession();
		session.beginTransaction();
		//第一种方法
		//String hql = "select new com.bean.User(uid,name) from User";
		//Query q = session.createQuery(hql);
		//show(q);
		
		//第二种方法
		//区别在于q.list()获取的是Object[]
		String sql = "select uid,name from User";
		Query q = session.createQuery(sql);
	//	SQLQuery sq = session.createSQLQuery(sql);
	//	List list = sq.list();
		List list = q.list();
		for(Object obj:list){
			Object[] o = (Object[]) obj;
			System.out.println(o[0]+"-----"+o[1]);
		}
		
		session.close();
	}
	
	//分组查询
	public static void query5(){
		Session session = sessionFactory.openSession();
		session.beginTransaction();
		
		String sql = "select name,count(name) from User group by name";
		SQLQuery sq = session.createSQLQuery(sql);
		List list = sq.list();
		for(Object obj: list){
			Object[] item = (Object[]) obj;
			System.out.println(item[0]+"------"+item[1]);
		}
		session.close();
	}
	
	//映射文件中定义命名查询语句
	public static void query6(){
		Session session = sessionFactory.openSession();
		
		Query q = session.getNamedQuery("findUserByName");
		q.setString("name", "%o%");
		show(q);
		session.close();
	}
	
	public static void main(String[] args) {
		//query();
		//query1();
		//query2();
		//query3(1);
		//query4();
		//query5();
		query6();
	}
}

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.bean">
<!-- 
package :默认的包前缀，如果没有指定包名，则默认使用此包名 
 -->
	
	<!-- 
		当表名和类名相同时，可以省略table属性，默认以类名作为表名
	 -->
	<class name="User">
		<id name="uid" column="Uid">
		<!-- 指定ID的增长策略 -->
			<generator class="native"></generator>
		</id>
		<property name="name" column="Name"></property>
		<property name="age" column="Age"></property>
	</class>
	
<query name="findUserByName">
<![CDATA[from User u where u.name like :name]]>
</query>

</hibernate-mapping>