Hibernate SQL查询 addScalar()或addEntity()
- - SQL - 编程语言 - ITeye博客Hibernate除了支持HQL查询外,还支持原生SQL查询. 对原生SQL查询执行的控制是通过SQLQuery接口进行的,通过执行Session.createSQLQuery()获取这个接口. 该接口是Query接口的子接口. 执行SQL查询步骤如下:. 1、获取Hibernate Session对象.
session.createSQLQuery("select * from person_inf").list(); session.createSQLQuery("select id,name,age from person_inf").list();
session.createSQLQuery("select * from person_inf") .addScalar("name",StandardBasicTypes.STRING) .addScalar("age",StandardBasicTypes.INT) .list();
public void scalarQuery(){ Session session = HibernateUtil.getSession(); Transaction tx = session.beginTransaction(); String sql = "select * from person_inf"; List list = session.createSQLQuery(sql). addScalar("person_id",StandardBasicTypes.INTEGER). addScalar("name", StandardBasicTypes.STRING). addScalar("age",StandardBasicTypes.INTEGER).list(); for(Iterator iterator = list.iterator();iterator.hasNext();){ //每个集合元素都是一个数组,数组元素师person_id,person_name,person_age三列值 Object[] objects = (Object[]) iterator.next(); System.out.println("id="+objects[0]); System.out.println("name="+objects[1]); System.out.println("age="+objects[2]); System.out.println("----------------------------"); } tx.commit(); session.close(); }
session.createSQLQuery("select * from perons_inf").addEntity(Person.class).list; session.createSQLQuery("select id,name,age from person_inf").addEntity(Person.class).list();
sess.createSQLQuery("select id, name, age, teacherID from person_inf").addEntity(Person.class).list();
public void entityQuery(){ Session session = HibernateUtil.getSession(); Transaction tx = session.beginTransaction(); String sql = "select * from person_inf"; List list = session.createSQLQuery(sql). addEntity(Person.class). //指定将查询的记录行转换成Person实体 list(); for (Iterator iterator = list.iterator();iterator.hasNext();) { Person person = (Person) iterator.next(); //集合的每个元素都是一个Person对象 System.out.println("name="+person.getName()); System.out.println("age="+person.getAge()); } tx.commit(); session.close(); }
public void multiEntityQuery(){ Session session = HibernateUtil.getSession(); Transaction tx = session.beginTransaction(); String sql = "select p.*,e.* from person_inf as p inner join event_inf as e" + " on p.person_id=e.person_id"; List list = session.createSQLQuery(sql) .addEntity("p",Person.class) .addEntity("e", MyEvent.class) .list(); for(Iterator iterator = list.iterator();iterator.hasNext();){ //每个集合元素都是Person、MyEvent所组成的数组 Object[] objects = (Object[]) iterator.next(); Person person = (Person) objects[0]; MyEvent event = (MyEvent) objects[1]; System.out.println("person_id="+person.getId()+" person_name="+person.getName()+" title="+event.getTitle()); } }
public void joinQuery(){ Session session = HibernateUtil.getSession(); Transaction tx = session.beginTransaction(); String sql = "select p.*,e.* from person_inf as p,event_inf as e where e.person_id=p.person_id"; List list = session.createSQLQuery(sql) .addEntity("p",Person.class) .addJoin("e","p.myEvents") .list(); for(Iterator iterator = list.iterator();iterator.hasNext();){ //每个集合元素都是Person、MyEvent所组成的数组 Object[] objects = (Object[]) iterator.next(); Person person = (Person) objects[0]; MyEvent event = (MyEvent) objects[1]; System.out.println("person_id="+person.getId()+" person_name="+person.getName()+" title="+event.getTitle()); } }上面的程序返回的Person对象,其属性myEvent属性已经完全被初始化,不再需要数据库的额外操作,同时将该属性转换成别名为e的实体。也就是说返回的结果是Person、Event对象数组的列表。
<sql-query name="sqlquery"> <!-- 将p别名转换为Person实体 --> <return alias="p" class="Person" /> <!-- 将e别名转换成Event实体 --> <return alias="e" class="MyEvent" /> <!-- 指定将person_inf表的name属性列作为标量值返回--> <return-scalar column="p.name" type="string"/> select p.*,e.* from person_inf as p,event_inf as e where p.person_id = e.person_id and p.age=:age </sql-query> 使用Session的getNamedQuery即可获得指定命名sql查询。 [java] view plaincopyprint? public void query(){ Session session = HibernateUtil.getSession(); //调用命名查询,直接返回结果 List list = session.getNamedQuery("sqlquery") .setInteger("age", 30).list(); for(Iterator iterator = list.iterator();iterator.hasNext();){ //每个集合元素都是Person、MyEvent所组成的数组 Object[] objects = (Object[]) iterator.next(); Person person = (Person) objects[0]; MyEvent event = (MyEvent) objects[1]; System.out.println("person_id="+person.getId()+" person_name="+person.getName()+" title="+event.getTitle()); } session.close(); } Hibernate允许吧把结果集的映射信息放在<resultset.../>元素定义,这样就可让多个命名查询共有该结果集映射。 [html] view plaincopyprint? <resultset name="person_resultSet"> <return alias="p" class="Person" /> <return-scalar column="p.age" type="int"/> </resultset> 通过为<sql-query.../>元素指定resultset-ref属性,就可以让命名SQL查询使用一个已有的结果集映射了。 [html] view plaincopyprint? <sql-query name="sqlquery" resultset-ref="person_resultSet"> select p.* from person as p </sql-query>
Create procedure select_person() ect * from person_inf;
<sql-query name="callProcedure" callable="true"> <return class="Person"> <!-- 将查询的数据列转换成实体的属性 --> <return-property name="name" column="name"/> <return-property name="age" column="age" /> <return-property name="person_id" column="id" /> </return> </sql-query>