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
}