Hibernate执行Sql

package com.hib.sql.dao;

import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;

import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.ResultTransformer;
import org.hibernate.transform.Transformers;
import org.hibernate.type.Type;

import com.hib.sql.model.HibSqlBean;
import com.hib.sql.model.Students;
import com.hib.sql.model.Subjects;
import com.hib.sql.sessionFactory.HibernateSessionFactory;

/**
 * @ClassName: HibSqkService
 * @Description: 
 * @author zhangl
 * @date 2010-12-28 下午02:56:25
 * @version V1.0
 */
public class HibSqlDao {

	/**
	 * @param args
	 */
	@SuppressWarnings("unchecked")
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		try{
//			testHibSqlObject();
//			testHibSqlOne();
//			testHibSqlTwo();
			testHibSqlCus();
		}catch(Exception e){
			e.printStackTrace();
			//...
		}
	}
	

	/**
	 * 
	 * 结果：
studentsId=4||subjectsId=4||studentsName=Helly||studentsAge=22||subjectsName=语文||subjectsRemark=备注
studentsId=4||subjectsId=14||studentsName=Helly||studentsAge=22||subjectsName=政治||subjectsRemark=备注
studentsId=7||subjectsId=17||studentsName=Micy||studentsAge=22||subjectsName=物理||subjectsRemark=备注
studentsId=7||subjectsId=7||studentsName=Micy||studentsAge=22||subjectsName=语文||subjectsRemark=备注
	 */
	public static void testHibSqlCus() throws Exception{
		String sql = " select sb.subjects_id as subjectsId, sb.subjects_name as subjectsName, " +
					" sb.subjects_remark as subjectsRemark, st.students_id as studentsId, " +
					" st.students_name as studentsName, st.students_age as studentsAge " +
				"  from subjects sb, students st  " +
				" where sb.students_id = st.students_id " +
					" and (st.students_name = ? or st.students_name = ?) ";
		List<Class> listClass = new ArrayList<Class>();
		listClass.add(Subjects.class);
		listClass.add(Students.class);
		Collection<String> parameters = new ArrayList<String>();
		parameters.add("Helly");
		parameters.add("Micy");
		Map<String, Type> scalars = new HashMap<String, Type>();
		scalars.put("subjectsId", Hibernate.LONG);
		scalars.put("subjectsName", Hibernate.STRING);
		scalars.put("subjectsRemark", Hibernate.STRING);
		scalars.put("studentsId", Hibernate.LONG);
		scalars.put("studentsName", Hibernate.STRING);
		scalars.put("studentsAge", Hibernate.LONG);
		List<HibSqlBean> list = hibSqlCus(sql, parameters.toArray(), HibSqlBean.class, scalars);
		System.out.println(sql);
		for(HibSqlBean hb: list){
			System.out.println(hb.toString());
		}
	}
	
	public static <T> List<T> hibSqlCus(String sql, Object[] parameters, Class<T> resultClass,
			Map<String, Type> scalars)
		throws Exception{
		Session session = HibernateSessionFactory.getSession();
		SQLQuery query = session.createSQLQuery(sql);
		ResultTransformer rsTransformer = Transformers.aliasToBean(resultClass);
		query.setResultTransformer(rsTransformer);
		if(scalars != null){
			for(Entry<String, Type> entry: scalars.entrySet()){
				query.addScalar(entry.getKey(), entry.getValue());
			}
		}
		if(parameters != null && parameters.length > 0){
			for(int i = 0; i < parameters.length; i ++){
				query.setParameter(i, parameters[i]);
			}
		}
		return (List<T>)query.list();
	}
	
	
	
	
	/**
	 * 
subjects.subjects_name=语文||students.students_name=Helly
subjects.subjects_name=政治||students.students_name=Helly
subjects.subjects_name=物理||students.students_name=Micy
subjects.subjects_name=语文||students.students_name=Micy
	 * @throws Exception
	 */
	public static void testHibSqlTwo() throws Exception{
		String sql = " select sb.*, st.* from subjects sb, students st  " +
				" where sb.students_id = st.students_id " +
				" and (st.students_name = ? or st.students_name = ?) ";
		List<Class> listClass = new ArrayList<Class>();
		listClass.add(Subjects.class);
		listClass.add(Students.class);
		Collection<String> parameters = new ArrayList<String>();
		parameters.add("Helly");
		parameters.add("Micy");
		List list = hibSql(sql, parameters.toArray(), listClass);
		System.out.println(sql);
		for(Object o: list){
			Object[] obj = (Object[])o;
			Subjects sb = (Subjects)obj[0];
			Students st = (Students)obj[1];
			System.out.println("subjects.subjects_name="+sb.getSubjectsName()+"||"
					+"students.students_name="+st.getStudentsName());
		}
	}
	/**
	 * 结果：
subjects.subjects_name=化学
subjects.subjects_name=政治
subjects.subjects_name=历史
subjects.subjects_name=物理
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public static void testHibSqlOne() throws Exception{
		String sql = " select * from subjects ";
		List<Class> listClass = new ArrayList<Class>();
		listClass.add(Subjects.class);
		List<Subjects> list = hibSql(sql, null, listClass);
		for(Subjects mh: list){
			System.out.println("subjects.subjects_name="+mh.getSubjectsName());
		}
	}
	
	/**
	 * 返回对象组成的List
	 * @param sql
	 * @param parameters
	 * @param listClass
	 * @return
	 * @throws Exception
	 */
	public static List hibSql(String sql, Object[] parameters, List<Class> listClass)
		throws Exception{
		Session session = HibernateSessionFactory.getSession();
		SQLQuery query = session.createSQLQuery(sql);
		for(Class c: listClass){
			query.addEntity(c);
		}
		if(parameters != null && parameters.length > 0){
			for(int i = 0; i < parameters.length; i ++){
				query.setParameter(i, parameters[i]);
			}
		}
		return query.list();
	}
	
	/**
	 * 
	 * @throws Exception
	 * 
subjects.students_name=语文||students.students_name=Tom
subjects.students_name=数学||students.students_name=Tom
subjects.students_name=化学||students.students_name=Tom
	 */
	public static void testHibSqlObject() throws Exception{
		String sql = " select * from subjects sb, students st " +
			" where sb.students_id = st.students_id and st.students_id = ?";
		Collection<Object> parameters = new ArrayList<Object>();
		Long num = 1L;
		parameters.add(num);
		List list = hibSqlObject(sql, parameters.toArray());
		for(Object o: list){
			Object[] obj = (Object[])o;
			System.out.println("subjects.students_name="+obj[1]+"||students.students_name="+obj[5]);
		}
	}
	/**
	 * 返回Object[]组成的List
	 * @param sql
	 * @param parameters
	 * @return
	 * @throws Exception
	 */
	public static List hibSqlObject(String sql, Object[] parameters)
		throws Exception{
		Session session = HibernateSessionFactory.getSession();
		SQLQuery query = session.createSQLQuery(sql);
		if(parameters != null && parameters.length > 0){
			for(int i = 0; i < parameters.length; i ++){
				query.setParameter(i, parameters[i]);
			}
		}
		return query.list();
	}
}

 

 

create table STUDENTS
(
  STUDENTS_ID   NUMBER not null,
  STUDENTS_NAME VARCHAR2(30),
  STUDENTS_AGE  NUMBER
)


create table SUBJECTS
(
  SUBJECTS_ID     NUMBER not null,
  SUBJECTS_NAME   VARCHAR2(30),
  SUBJECTS_REMARK VARCHAR2(30),
  STUDENTS_ID     NUMBER
)