Hibernate(十二) 实现原生态sql查询

 hibernate使用原生态sql查询

  

 @Test
	public void test01(){
    	 Session session =null;
		 try {
			  session=HibernateUtil.openSession();
			  List<Student> stu=session.createSQLQuery("select * from t_student where name like ?")
					    .addEntity(Student.class)//设置返回结果集中存放的内容
					    .setParameter(0,"%张%")//hibernate设置参数是从0开始的，jdbc是从1
					    .setFirstResult(0)  //分页
					    .setMaxResults(5)
				        .list();
		    for(Student s:stu){
		    	System.out.println(s.getName());
		    }
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(session!=null) session.close();
		}
	}
    
    @Test
   	public void test02(){
       	 Session session =null;
   		 try {
   			  session=HibernateUtil.openSession();
   			  //查询出三个关联的对象   注意：stu.*,cla.*,spe.* 直接这么写会以第一个为标准，当取相同属性时，就会取第一个对象的值   {stu.*},{cla.*},{spe.*}这样就会区分
			List<Object[]> stus=session.createSQLQuery("select {stu.*},{cla.*},{spe.*} from" +
					" t_student stu left join t_classroom cla on(stu.cid=cla.id) left join t_special spe on(spe.id=cla.sid) where stu.name like ?")
					.addEntity("stu", Student.class)
					.addEntity("cla", ClassRoom.class)
					.addEntity("spe", Special.class)
					.setFirstResult(0).setMaxResults(10)
					.setParameter(0, "%张%")
   				    .list();
			List<StuDto> stuDtos=new ArrayList<StuDto>();  
   		    for(Object[] s:stus){
   		    	Student stu=(Student) s[0];
   		    	ClassRoom cla=(ClassRoom) s[1];
   		    	Special spe=(Special) s[2];
   		        stuDtos.add(new StuDto(stu,cla,spe));
   		    	System.out.println(stu.getName()+","+cla.getName()+spe.getName());
   		    }
   			
   		} catch (Exception e) {
   			e.printStackTrace();
   		}finally{
   			if(session!=null) session.close();
   		}
   	}
    @Test
   	public void test03(){
       	 Session session =null;
   		 try {
   			  session=HibernateUtil.openSession();
   			  //查询出三个关联的具体内容 
			List<StudentDto> sdto=session.createSQLQuery("select stu.id as sid,stu.name as sname, stu.sex as sex,cla.name as cname,spe.name as spename from" +
					" t_student stu left join t_classroom cla on(stu.cid=cla.id) left join t_special spe on(spe.id=cla.sid) where stu.name like ?")
					.setResultTransformer(Transformers.aliasToBean(StudentDto.class))//使用转换器
					.setFirstResult(0).setMaxResults(10)
					.setParameter(0, "%张%")
   				    .list();
			  for(StudentDto sd:sdto){
				  System.out.println(sdto);
			  }
   			
   		} catch (Exception e) {
   			e.printStackTrace();
   		}finally{
   			if(session!=null) session.close();
   		}
   	}

    上面代码使用带的两个dto

    StudentDto.java

 

/**
 * DTO对象没有存储的意义，仅仅是用来进行数据的传输的
 * @author Administrator
 *
 */
public class StudentDto {
	private int sid;
	private String sname;
	private String sex;
	private String cname;
	private String spename;
  省略get,set
}

 

 StuDto.java

 

public class StuDto {

	 private Student stu;
	 private ClassRoom cla;
	 private Special spe;
  省略get,set
}