Hibernate-Criteria查询（3）DetachedCriteria构造查询条件、命名查询、原生SQL、调用存储过程(使用JDBC)

package junit.test;

import static org.junit.Assert.*;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import hibch06.dao.HibernateSessionFactory;
import hibch06.entity.Dept;
import hibch06.entity.Emp;

import oracle.jdbc.OracleTypes;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projection;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Property;
import org.hibernate.criterion.Restrictions;
import org.hibernate.jdbc.Work;
import org.junit.Test;



public class Hibch06Test3 {

    /*
     * DetachedCriteria构造查询条件
     */
    @Test
    public void test(){
        //一个求平均工资的查询
        DetachedCriteria avgSal=DetachedCriteria.forClass(Emp.class,"e")
                .setProjection(Property.forName("sal").avg());

        Session session=HibernateSessionFactory.getSession();

        //查询高于平均工资的员工
        List<Emp> list=session.createCriteria(Emp.class)
                .add(Property.forName("sal").gt(avgSal))
                .list();


        for (Emp emp : list) {
            System.out.println(emp.getEmpno()+" "+emp.getEname()+" "+emp.getSal());
        }

    }
    /*
     * 命名查询
     */
    @Test
    public void test2(){
        Session session=HibernateSessionFactory.getSession();
        Query query=session.getNamedQuery("findAll");
        List<Dept> list=query.list();
        System.out.println(list.size());
    }

    @Test
    public void test3(){
        Session session=HibernateSessionFactory.getSession();
        Query query=session.getNamedQuery("findById");
        query.setByte("deptno", new Byte("10"));
        Dept dept=(Dept)query.uniqueResult();
        System.out.println(dept.getDname());
    }
    /*
     * 原生SQL
     */
    @Test
    public void test4(){
        Session session=HibernateSessionFactory.getSession();
        Query query=session.getNamedQuery("findEmpById");
        Emp emp=(Emp)query.setShort("empno", new Short("7788")).uniqueResult();
        System.out.println(emp.getEname());
    }


    @Test
    public void test5(){
        Session session=HibernateSessionFactory.getSession();
        Query query=session.getNamedQuery("selectEmpByJobJoinDept");
        query.setString("job", "MANAGER");
        List<Object[]> list=query.list();
        for (Object[] o : list) {
            Emp emp=(Emp)o[0];
            Dept dept=(Dept)o[1];
            System.out.println(emp.getEname()+" "+dept.getDname());
        }

    }

    /*
     * 调用存储过程(使用JDBC)
     */
    @Test
    public void test6(){
        Session session=HibernateSessionFactory.getSession();
        Connection conn=session.connection();//这个方法已经过时了 java.sql.Connection
        String sql="{call get_dept_dname(?,?)}";//使用占位符
        String ret=null;
        try {
            CallableStatement cs=conn.prepareCall(sql);
            cs.setShort(1, new Short("7788"));
            cs.registerOutParameter(2, OracleTypes.VARCHAR);
            cs.execute();
            ret=cs.getString(2);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        System.out.println(ret);


    }
}