hibernate的native sql查询

session.createSQLQuery("select * from t_student s").list()session.createSQLQuery("select ID,NAME,SEX from t_student s").list()

List<Object[]> stus = (List<Object[]>)session.createSQLQuery("select * from t_student s")
.addScalar("ID")
.addScalar("NAME")
.setFirstResult(0).setMaxResults(20)
.list();

@Test
public void testSql1()
{
Session session = null;
try
{
session = HibernateUtil.openSession();
/*
* 原生的SQL语句查询，会将t_student表的所有字段查出来，存放的一个Object[]数组当中
* 如果希望转换成实体对象，只需要调用 addEntity(Student.class)即可，这时，会首先匹配
* Student对象里面的属性是否全部查询出来，如果没有，则报错(如果这个类是实体类)
* 注意：如果要调用addEntity方法，这个类必须是实体类，即加了@Entity注解或者在XML中配置了实体类
* 映射关系
*/
List<Student> stus = (List<Student>)session.createSQLQuery("select * from t_student s")
.addEntity(Student.class)
.setFirstResult(0).setMaxResults(20)
.list();
for(Student stu : stus)
{
System.out.println(stu.getName());
}
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
HibernateUtil.close(session);
}
}

List<Object[]> stus = (List<Object[]>)session.createSQLQuery("select stu.*, cla.*, spe.*"
+ " from t_student stu left join t_classroom cla on stu.rid=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(20)
.setParameter(0, "%张%")
.list();

@Test
public void testSql3()
{
Session session = null;
try
{
session = HibernateUtil.openSession();
/**
* 当使用连接查询查询多个对象时，可以通过addEntity("alias", XXX.class)方法来根据
* 数据库表的别名来引入多个实体类，这时如果需要将查询出来的所有的对象分别存入实体类中，
* 只需要在查询出来的对象上添加 {} 号即可，此时就会自动帮我们分类
*/
List<Object[]> stus = (List<Object[]>)session.createSQLQuery("select {stu.*}, {cla.*}, {spe.*}"
+ " from t_student stu left join t_classroom cla on stu.rid=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(20)
.setParameter(0, "%张%")
.list();
for(Object[] obj : stus)
{
Student stu = (Student)obj[0];
Classroom cla = (Classroom)obj[1];
Special spe = (Special)obj[2];
System.out.println(stu.getName() + ", " + cla.getName() + ", " + spe.getName());
}
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
HibernateUtil.close(session);
}
}

public class StudentDTO
{
private int sid; // 学生id
private String sname;// 学生姓名
private String sex;// 学生性别
private String cname;// 班级名
private String spename;// 专业名 public StudentDTO(){} public StudentDTO(int sid, String sname, String sex, String cname,
String spename)
{
super();
this.sid = sid;
this.sname = sname;
this.sex = sex;
this.cname = cname;
this.spename = spename;
}
................
}

@Test
public void testSql4()
{
Session session = null;
try
{
/**
* 对于非Entity实体对象的类，我们如果要保持数据，可以通过定义一个DTO对象
* 然后调用setResultTransformer(Transformers.aliasToBean(StudentDTO.class))方法
* 来返回一个不受管的Bean对象
*/
session = HibernateUtil.openSession();
List<StudentDTO> stus = (List<StudentDTO>)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.rid=cla.id"
+ " left join t_special spe on spe.id=cla.sid where stu.name like ?")
.setResultTransformer(Transformers.aliasToBean(StudentDTO.class))
.setFirstResult(0).setMaxResults(20)
.setParameter(0, "%张%")
.list();
for(StudentDTO std : stus)
{
System.out.println(std.getSname() + ", " + std.getCname() + ", " + std.getSpename());
}
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
HibernateUtil.close(session);
}
}