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 */@Testpublic 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 */@Testpublic 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 */@Testpublic 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 */@Testpublic 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);}}}