Hibernate —— 原生SQL的实例

package me.ljm.hibernate.test;

import java.util.List;
import java.util.Map;

import me.ljm.hibernate.dto.StudentDto2;
import me.ljm.hibernate.entity.Classroom;
import me.ljm.hibernate.entity.Special;
import me.ljm.hibernate.entity.Student;

import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.junit.Test;

/**
 * 原生SQL操作
 * @author ljm
 *
 */
@SuppressWarnings("unchecked")
public class SqlTest extends AbstractTest {

	/**
	 * 返回数据类型为Entity
	 * 实现方法：addEntity(Student.class)
	 * @throws Exception
	 */
	@Test
	public void testEntity() throws Exception {
		Session session = null;
		try {
			session = getSession();
			session.beginTransaction();
			
			String sql = "select * from t_student where name like ?";
			
			List<Student> stus = session.createSQLQuery(sql)
					.addEntity(Student.class)
					.setParameter(0, "%张%")
					.list();
			for (Student student : stus) {
				System.out.println(student.getName());
			}
			
			session.getTransaction().commit();
		} catch (Exception e) {
			session.getTransaction().rollback();
			e.printStackTrace();
		} finally {
			close(session);
		}
	}
	
	/**
	 * 返回数据类型为Dto(不带关联对象)
	 * 实现方法：
	 * @throws Exception
	 */
	@Test
	public void testDto1() throws Exception {
		Session session = null;
		try {
			session = getSession();
			session.beginTransaction();
			
			String sql = "select stu.id as id,stu.name as name,stu.sex as sex,cla.name as cname,spe.name as spename"
					+ " from t_student stu"
					+ " left join t_classroom cla on stu.cla_id = cla.id"
					+ " left join t_special spe on cla.special_id = spe.id"
					+ " where stu.name like ?";
			
			List<StudentDto2> stus = session.createSQLQuery(sql)
					.setResultTransformer(Transformers.aliasToBean(StudentDto2.class))
					.setParameter(0, "%张%")
					.list();
			for (StudentDto2 student : stus) {
				System.out.println(student);
			}
			
			session.getTransaction().commit();
		} catch (Exception e) {
			session.getTransaction().rollback();
			e.printStackTrace();
		} finally {
			close(session);
		}
	}
	
	/**
	 * 返回数据类型为Object[]
	 * 实现方法：.addEntity("stu", Student.class)
			    .addEntity("cla", Classroom.class)
			    .addEntity("spe", Special.class)
	 * @throws Exception
	 */
	@Test
	public void testObjectArray() throws Exception {
		Session session = null;
		try {
			session = getSession();
			session.beginTransaction();
			
			String sql = "select {stu.*},{cla.*},{spe.*}"
					+ " from t_student stu"
					+ " inner join t_classroom cla on stu.cla_id = cla.id"
					+ " inner join t_special spe on cla.special_id = spe.id"
					+ " where stu.name like ?";
			
			List<Object[]> result = session.createSQLQuery(sql)
					.addEntity("stu", Student.class)
					.addEntity("cla", Classroom.class)
					.addEntity("spe", Special.class)
					.setParameter(0, "%张%")
					.list();
			Student stu = null;
			Classroom cla = null;
			Special spe = null;
			for (Object[] obj : result) {
				stu = (Student) obj[0];
				cla = (Classroom) obj[1];
				spe = (Special) obj[2];
				System.out.println(stu.getName()+","+cla.getName()+","+spe.getName());
			}
			
			session.getTransaction().commit();
		} catch (Exception e) {
			session.getTransaction().rollback();
			e.printStackTrace();
		} finally {
			close(session);
		}
	}
	
	
	/**
	 * 返回数据类型为Map
	 * 实现方法：setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
	 * @throws Exception
	 */
	@Test
	public void testMap() throws Exception {
		Session session = null;
		try {
			session = getSession();
			session.beginTransaction();
			
			String sql = "select spe.name as name, "
					+ " sum(case stu.sex when '男' then 1 else 0 end) as mNum,"
					+ " sum(case stu.sex when '女' then 1 else 0 end) as wNum"
					+ " from t_student stu"
					+ " right join t_classroom cla on cla.id = stu.cla_id"
					+ " right join t_special spe on spe.id = cla.special_id"
					+ " group by spe.id";
			
			List<Map<String, Object>> result =  session.createSQLQuery(sql)
					.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
					.list();
			for (Map<String, Object> map : result) {
				System.out.println(map.get("name")+","+map.get("mNum")+","+map.get("wNum"));
			}
			
			session.getTransaction().commit();
		} catch (Exception e) {
			session.getTransaction().rollback();
			e.printStackTrace();
		} finally {
			close(session);
		}
	}
	
}