Hibernate提供了對SQL的支援,您可以指定您所要建立的SQL,並將實體類別與資料表格關聯,舉個例子來說,如果您打算使用像以下的SQL語 句:
SELECT * FROM user WHERE age > 20
則您可以如下建立SQL查詢:
// SQL,並指定別名為user
String sql = "select {user.*} from User user where user.age > 20"; Session session = sessionFactory.openSession(); // 建立 SQLQuery SQLQuery sqlQuery = session.createSQLQuery(sql); // 將別名user與實體類User關聯在一起 sqlQuery.addEntity("user", User.class); Iterator iterator = sqlQuery.list().iterator(); while(iterator.hasNext()) { User user = (User) iterator.next(); System.out.println(user.getAge() + "\t" + user.getName()); } session.close();
addEntity()是將實體類別與別名連結在一起的方法,大括號指定要查詢的資料,Hibernate根據所給定的SQL自動生成以下的句子:
select user.id as id0_, user.name as name0_0_, user.age as age0_0_ from User user where user.age > 20
返回的結果則由Hibernate進行封裝為所指定別名關聯之實體類,如此您可以得到使用SQL的彈性,但無需處理繁瑣的ResultSet。
您也可以將SQL語句定義在映射文件中,例如:
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping
PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="onlyfun.caterpillar.User" table="user">
....
</class>
<sql-query name="onlyfun.caterpillar.QueryUser"> <![CDATA[ select {user.*} from User user where user.age > 20 ]]> <return alias="user" class="onlyfun.caterpillar.User"/> </sql-query>
</hibernate-mapping> 定義的時候,使用<return>標籤指定別名與實體類之關聯,配合映射文件中的定義,您可以如下運行Hibernate:
Session session = sessionFactory.openSession(); Query query = session.getNamedQuery("onlyfun.caterpillar.QueryUser"); Iterator iterator = query.list().iterator(); while(iterator.hasNext()) { User user = (User) iterator.next(); System.out.println(user.getAge() + "\t" + user.getName()); } session.close();
也可以設定查詢參數,例如:
.... <sql-query name="onlyfun.caterpillar.QueryUser"> <![CDATA[ select {user.*} from User user where user.age > :age ]]> <return alias="user" class="onlyfun.caterpillar.User"/> </sql-query> ....
使用Hibernate查詢時如下: Session session = sessionFactory.openSession(); Query query = session.getNamedQuery("onlyfun.caterpillar.QueryUser"); query.setInteger("age", 20);
Iterator iterator = query.list().iterator(); while(iterator.hasNext()) { User user = (User) iterator.next(); System.out.println(user.getAge() + "\t" + user.getName()); } session.close();
Hibernate 3的映射文件中新增了<sql-insert>、<sql-update>與< sql-delete>三個標籤,您 可以在這三個標籤中使用SQL自定義您的INSERT、UPDATE、DELETE,也就是儲存、更新、刪除資料時的行為,例如:
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping
PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="onlyfun.caterpillar.User" table="user">
<id name="id" column="id">
<generator class="native"/>
</id>
<property name="name" column="name"/>
<property name="age" column="age"/>
<sql-insert> INSERT INTO user (name, age) VALUES (?, ?) </sql-insert> <sql-update> UPDATE user SET name=?, age=?, WHERE id=? </sql-update> <sql-delete> DELETE FROM user WHERE id=? </sql-delete>
</class>
</hibernate-mapping> ? 對應的順序是映射文件中屬性出現的順序,假設您儲存物件:
session = sessionFactory.openSession(); tx = session.beginTransaction(); session.save(user); tx.commit(); session.close();
則執行結果中顯示的SQL語句會是您自定義的語句,而不是由 Hibernate自動生成的語句:
Hibernate: INSERT INTO user (name, age) VALUES (?, ?) |
|
Hibernate使用sql语句查询总结 public class SqlQueryTest { public static void main (String [] args){ sqlQueryAddScalar(); } static void sqlQueryAddEntity(){ //获取Hibernate Session对象 Session session = HibernateUtil.getSession(); //开始事务 Transaction tx = session.beginTransaction(); //编写SQL语句 String sqlString = "select * from user"; //以SQL语句创建SQLQuery对象 List l = session.createSQLQuery(sqlString) //将查询ss实体关联的User类 .addEntity("ss",User.class) //返回全部的记录集 .list(); //遍历结果集 Iterator it = l.iterator(); while (it.hasNext()){ //因为将查询结果与Student类关联,因此返回的是Student集合 User s = (User)it.next(); String a = s.getName(); System.out.println(a); } //提交事务 tx.commit(); session.close(); } static void sqlQueryAddScalar(){ //返回Object[]的遍历 Session session=HibernateUtil.getSession(); Transaction transaction=session.beginTransaction(); String sql="select * from user"; SQLQuery s=(SQLQuery) session.createSQLQuery(sql); //.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP) s.addScalar("id",Hibernate.INTEGER); //列名, 数据类型 s.addScalar("name",Hibernate.STRING); List list=s.list(); Object[] objects=(Object[])list.get(0); System.out.println(objects[0]); System.out.println(objects[1]); //用MAP SQLQuery ss=(SQLQuery) session.createSQLQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); ss.addScalar("id",Hibernate.INTEGER); ss.addScalar("name",Hibernate.STRING); List list2=ss.list(); Map map = (Map) list2.get(0); System.out.println(map.get("name")); System.out.println(map.get("id")); //用LIST SQLQuery s3=(SQLQuery) session.createSQLQuery(sql).setResultTransformer(Transformers.TO_LIST); s3.addScalar("id",Hibernate.INTEGER); s3.addScalar("name",Hibernate.STRING); List list3=s3.list(); List list4=(List)list3.get(0); System.out.println(list4.get(0)); System.out.println(list4.get(1)); //用自定义的bean SQLQuery e=(SQLQuery) session.createSQLQuery(sql).setResultTransformer(Transformers.aliasToBean(SqlQueryBean.class)); e.addScalar("id",Hibernate.INTEGER); e.addScalar("name",Hibernate.STRING); List r=e.list(); SqlQueryBean sqb=(SqlQueryBean)r.get(0); System.out.println(sqb.getId()); System.out.println(sqb.getName()); } } |