关于Hibernate的查询

package com.layne.hibernate.uuid.test;

import java.util.Iterator;
import java.util.List;

import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.Order;
import org.junit.Test;

import com.layne.hibernate.uuid.beans.Student;
import com.layne.hibernate.uuid.utils.HbnUtils;
public class myTest {

    @Test
    public void test00(){
        Session session = HbnUtils.getSession();
        try {
            session.beginTransaction();
            for(int i=0;i<=10;i++){
                Student student = new Student("n_"+i,15+i,84+i);
                session.save(student);
            }
            session.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            session.getTransaction().rollback();
        }
        
    }
    @Test
    public void test01_HQL(){
        Session session = HbnUtils.getSession();
        try {
            session.beginTransaction();
            String hpl="from Student ";
            List<Student> list = session.createQuery(hpl).list();
            for(Student student:list){
                System.out.println(student);
            }
            session.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            session.getTransaction().rollback();
        }
    }
    @Test
    public void test01_SQL(){
        Session session = HbnUtils.getSession();
        try {
            session.beginTransaction();
            String sql="select tid,tname,tscore,tage from t_student";
            List<Student> list = session.createSQLQuery(sql).addEntity(Student.class).list();
            for(Student student:list){
                System.out.println(student);
            }
            session.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            session.getTransaction().rollback();
        }
    }
    @Test
    public void test01_QBC(){
        Session session = HbnUtils.getSession();
        try {
            session.beginTransaction();
            List<Student> list = session.createCriteria(Student.class).list();
            for(Student student:list){
                System.out.println(student);
            }
            session.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            session.getTransaction().rollback();
        }
    }
    @Test 
    public void test02_HQL(){
        Session session = HbnUtils.getSession();
        try {
            session.beginTransaction();
            String hpl="from Student order by age desc";
            List<Student> list = session.createQuery(hpl).list();
            for(Student student:list){
                System.out.println(student);
            }
            session.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            session.getTransaction().rollback();
        }
    }
    @Test
    public void test02_SQL(){
        Session session = HbnUtils.getSession();
        try {
            session.beginTransaction();
            String sql="select tid,tname,tscore,tage from t_student order by taage desc";
            List<Student> list = session.createSQLQuery(sql).addEntity(Student.class).list();
            for(Student student:list){
                System.out.println(student);
            }
            session.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            session.getTransaction().rollback();
        }
    }
    
    @Test
    public void test02_QBC(){
        Session session = HbnUtils.getSession();
        try {
            session.beginTransaction();
            List<Student> list = session.createCriteria(Student.class).addOrder(Order.desc("age")).list();
            for(Student student:list){
                System.out.println(student);
            }
            session.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            session.getTransaction().rollback();
        }
    }
    @Test
    public void test03_HQL(){
        Session session = HbnUtils.getSession();
        try {
            session.beginTransaction();
            String hpl="from Student where age>? and score<?";
            List<Student> list = session.createQuery(hpl).
                            setInteger(0, 20).
                            setDouble(1, 94).
                            list();
            //HQL查询语句的赋值使用被传值的位置，使用set+【值类型】(位置,值)
            for(Student student:list){
                System.out.println(student);
            }
            
            session.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            session.getTransaction().rollback();
        }
    }
    @Test
    public void test03_2_HQL(){
        Session session = HbnUtils.getSession();
        try {
            session.beginTransaction();
            String hpl="from Student where age>:myage and score<:myscore";
            List<Student> list = session.createQuery(hpl).
                                      setInteger("myage", 20).
                                      setDouble("myscore", 94).
                                      list();
            //HQL查询语句的赋值使用被传值的位置，使用set+【值类型】(位置,值)
            for(Student student:list){
                System.out.println(student);
            }
            
            session.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            session.getTransaction().rollback();
        }
    }
    @Test
    public void test03_3_HQL(){
        Session session = HbnUtils.getSession();
        try {
            session.beginTransaction();
            String hpl="from Student where age>:myage and score<:myscore";
            List<Student> list = session.createQuery(hpl).
                                      setParameter("myage", 20).
                                      setParameter("myscore", 94.0).
                                      list();
            //HQL查询语句的赋值使用被传值的位置，使用set+【值类型】(位置,值)
            for(Student student:list){
                System.out.println(student);
            }
            
            session.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            session.getTransaction().rollback();
        }
    }
    
    
    @Test
    public void test04_SQL(){
        Session session = HbnUtils.getSession();
        try {
            session.beginTransaction();
            String sql="select * from t_student limit ?,?";
            List<Student> list = session.createSQLQuery(sql)
                                         .addEntity(Student.class)
                                         .setInteger(0,4)
                                         .setInteger(1,3)
                                         .list();
            for(Student student:list){
                System.out.println(student);
            }
            session.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            session.getTransaction().rollback();
        }
    }
    
    @Test
    public void test04_HQL(){
        Session session = HbnUtils.getSession();
        try {
            session.beginTransaction();
            String hql="from Student";
            List<Student> list = session.createQuery(hql).
                                           setFirstResult(4).
                                           setMaxResults(3).
                                           list();
            for(Student student:list){
                System.out.println(student);
            }
            session.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            session.getTransaction().rollback();
        }
    }
    @Test
    public void test05_HQL(){
        Session session = HbnUtils.getSession();
        try {
            session.beginTransaction();
            String hql="from Student where name like :myname";
            List<Student> list = session.createQuery(hql).setString("myname", "%张%").list();
            for(Student student:list){
                System.out.println(student);
            }
            session.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            session.getTransaction().rollback();
        }
    }
    @Test//唯一性查询
    public void test06_HQL(){
        Session session = HbnUtils.getSession();
        try {
            session.beginTransaction();
            String hql="from Student where id=:myid";
            Student  student= (Student) session.createQuery(hql).setInteger("myid", 3).uniqueResult();
            System.out.println(student);
        
            session.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            session.getTransaction().rollback();
        }
    }
    @Test//聚合函数查询
    public void test07_HQL(){
        Session session = HbnUtils.getSession();
        try {
            session.beginTransaction();
            String hql="select count(*) from Student";
            Long count = (Long) session.createQuery(hql).uniqueResult();
            System.out.println(count);
            session.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            session.getTransaction().rollback();
        }
    }
    @Test//投影查询
    public void test08_HQL(){
        Session session = HbnUtils.getSession();
        try {
            session.beginTransaction();
            String hql="select new Student(name,age) from Student";
            List<Student> list = session.createQuery(hql).list();
            for(Student student:list){
                System.out.println(student);
            }
            session.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            session.getTransaction().rollback();
        }
    }
    @Test//分组查询 group by age 取的是各个年龄段的第一个
    public void test09_HQL(){
        Session session = HbnUtils.getSession();
        try {
            session.beginTransaction();
            //每个年龄段的第一个
            /*String hql="from Student group by age";
            List<Student> list = session.createQuery(hql).list();
            for(Student student:list){
                System.out.println(student);
            }*/
            //共有几个年龄段
            /*String hql="select age from Student group by age";
            List<Integer> list = session.createQuery(hql).list();
            System.out.println(list);*/
            
            String hql="select age from Student group by age having count(age) >:mycount"; //人数多于1人的分组
            List<Integer> list = session.createQuery(hql).setInteger("mycount", 1).list();
            System.out.println(list);
            session.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            session.getTransaction().rollback();
        }
    }
    
    
    @Test//iterate查询会先使用id查询缓存中是否有查询值 所以可以现用list查询，在有iterate查询
    public void test10_HQL(){
        Session session = HbnUtils.getSession();
        try {
            session.beginTransaction();
            String hql="from Student"; 
            Iterator<Student> it = session.createQuery(hql).iterate();
            while(it.hasNext()){
                Student student=it.next();
                System.out.println(student);
            }
            session.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            session.getTransaction().rollback();
        }
    }

    @Test//使用**。hbn.xml配置文件配置hql语句(命名空间查询)
    public void test11_HQL(){
        Session session = HbnUtils.getSession();
        try {
            session.beginTransaction();
            Student student = (Student) session.getNamedQuery("selectById").setInteger("myid", 2).uniqueResult();
            System.out.println(student);
            session.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            session.getTransaction().rollback();
        }
    }
}