RSS

HQL与Native SQL查询

07 Apr

Query接口

1. Query类型与org.hibernate.Criteria很接近,封装了对底层数据库表的查询请求

2. Query接口的常用方法

list()

iterate()

setMaxResults()

setFirstResult()

uniqueResult()

 

Query query = session.createQuery("FROM GuestBook");

query.setMaxResults(20);

query.setFirstResult(20);

List<GuestBook> list = query.list();
这种查询只需要查询语句

 

Query query = session.createQuery("FROM GuestBook");

query.setMaxResults(1);

query.setFirstResult(20);

GuestBook gb = query.uniqueResult();

 

Query query = session.createQuery("FROM GuestBook WHERE id=1000");

GuestBook gb = query.uniqueResult();

 

Query query = session.createQuery("FROM GuestBook");

Iterate<GuestBook> it = query.iterate();

while(it.hasNext()){

GuestBook gb = it.next();

}

这种查询是N+1条语句:1) select id 2)对每一条记录进行查询where id = <1>.id

它使用缓存查询,第一次没有缓存数据,所以全部查询;如果第二次再查询,直接从缓存中查询,提高速度。

 

HQL查询

1. 查询所有持久化对象

Transaction tx = session.beginTransaction();

Query query = session.createQuery("FROM GuestBook");

List list = query.list();

tx.commit();

2. HQL关键字不区分大小写

3. HQL中持久化类自动引入(除非在不同的包里面有同名的POJO,这时,需要使用全部的命名)

4. HQL同时查询多个持久化类

5. HQL中持久化类的继承查询

 

HQL – 投影查询(方法一)

Transaction tx = session.beginTransaction();

Query query = session.createQuery("SELECT title FROM GuestBook");

List<String> list = query.list();

Query query2 = session.createQuery("SELECT id, name, title FROM GuestBook");

List<Object[]> list2 = query2.list();

tx.commit();

 

HQL – 投影查询(方法二) 查询持久化对象的多个属性 (需要提供POJO相应的构造方法 GuestBook(id, name, email)

Transaction tx = session.beginTransaction();

Query query = session.createQuery("SELECT new GuestBook(id, name, email) FROM GuestBook");

List<GuestBook> list = query.list();

tx.commit();

 

HQL – 投影查询(方法三) 查询持久化对象的多个属性 (使用下标访问,Map的下标从0开始)

Transaction tx = session.beginTransaction();

Query query = session.createQuery("SELECT new map(id, name, email) FROM GuestBook");

List<Map> list = query.list();

for(Map map: list){

System.out.println(map.get("0")+"\t" + map.get("1") + "\t" + map.get("2"));

}

tx.commit();

 

使用属性名访问

Transaction tx = session.beginTransaction();

Query query = session.createQuery("SELECT new map(gb.id as id, gb.name as name, gb.email as email) FROM GuestBook as gb");

List<Map> list = query.list();

for(Map map: list){

System.out.println(map.get("id")+"\t" + map.get("name") + "\t" + map.get("email"));

}

tx.commit();

HQL中使用别名

Query query = session.createQuery("SELECT gb.name as name FROM GuestBook as gb");

List<String> list = query.list();

省略as,指定持久化类的别名

Query query = session.createQuery("SELECT gb.name as name FROM GuestBook gb");

List<String> list = query.list();

别名与属性值组成key/value对

Transaction tx = session.beginTransaction();

Query query = session.createQuery("SELECT new map(gb.id as id, gb.name as name, gb.email as email) FROM GuestBook as gb");

List<Map> list = query.list();

for(Map map: list){

System.out.println(map.get("id")+"\t" + map.get("name") + "\t" + map.get("email"));

}

tx.commit();

使用distinct

Transaction tx = session.beginTransaction();

Query query = session.createQuery("SELECT distinct name FROM GuestBook");

List<String> list = query.list();

HQL中的运算符

逻辑运算符 – or, and, not

数学运算 – +, -, *, /

比较运算符 – =, <>, !=, <, >, <=, >=, like, not like, between, not between,  in, not in, is null, is not null, is empty, is not empty, member of, not member of

字符串连接 – ||

分支选择 – case, case … when … then … else … end

集合运算 – some, exists, all, any

 

where子句,限定查询的条件

Transaction tx = session.beginTransaction();

Query query = session.createQuery("FROM GuestBook WHERE name=’Xavier’");

List<GuestBook> list = query.list();

使用between

Query query = session.createQuery("FROM GuestBook WHERE id between 1 and 10");

使用not between

Query query = session.createQuery("FROM GuestBook WHERE id not between 1 and 10");

使用in

Query query = session.createQuery("FROM GuestBook WHERE name in (‘Xavier’, ‘Nate’)");

 

使用not in

Query query = session.createQuery("FROM GuestBook WHERE name not in (‘Xavier’, ‘Nate’)");

使用like

Query query = session.createQuery("FROM GuestBook WHERE name like ‘Xa%’");

 

使用not like

Query query = session.createQuery("FROM GuestBook WHERE name not like ‘Xa%’");

使用and

Query query = session.createQuery("FROM GuestBook WHERE name=’Xavier’ and id<10");

 

使用or

Query query = session.createQuery("FROM GuestBook WHERE name=’Xavier’ or name=’Nate’");

 

使用not

Query query = session.createQuery("FROM GuestBook WHERE not(name=’Xavier’)");

 

使用is null

Query query = session.createQuery("FROM GuestBook WHERE email is null");

使用is not null

Query query = session.createQuery("FROM GuestBook WHERE email is not null");

 

数据排序

Query query = session.createQuery("FROM GuestBook WHERE name like ‘Xa%’ ORDER BY id DESC");

 

多个属性的排序

Query query = session.createQuery("FROM GuestBook ORDER BY name, id desc");

 

使用"?"作为参数占位符

Query query = session.createQuery("FROM GuestBook WHERE id < ?");

query.setInteger(0, 12);

List<GuestBook> list = query.list();

 

使用名称作为参数占位符

Query query = session.createQuery("FROM GuestBook WHERE id <:myid");

query.setParameter("myid", 1000);

List<GuestBook> list = query.list();

使用统计函数

Query query = session.createQuery("SELECT min(price), max(price) FROM Product");

Object[] values = (Object[])query.uniqueResult();

 

在分组中应用统计函数

Query query = session.createQuery("SELECT avg(price), count(*) FROM Product group by category.id");

List<Object[]> list = query.list();

 

使用having

Query query = session.createQuery("SELECT avg(price), count(*) FROM Product GROUP BY price HAVING price>150");

List<Object[]> list = query.list();

 

使用函数

Query query = session.createQuery("SELECT lower(name) FROM GuestBook  WHERE id<100");

List<String> list = query.list();

 

HQL的批量更新

Query query = session.createQuery("UPDATE GuestBook SET name=’Xavier’ WHERE id=1");

int rowCount = query.executeUpdate();

Query query = session.createQuery("UPDATE GuestBook SET name=’Xavier’ WHERE id=?");

query.setInteger(0, 1);

int rowCount = query.executeUpdate();

//批量更新

Query query = session.createQuery("update GuestBook set name=’Xavier’, title=’Drinks’");

int rowCount = query.executeUpdate();

 

HQL的批量删除

Query query = session.createQuery("delete from GuestBook");

int rowCount = query.executeUpdate();

Query query = session.createQuery("delete from GuestBook where id=?");

query.setInteger(0, 1);

int rowCount = query.executeUpdate();

 

HQL中的链接查询

名称

HQL子句

简写形式

实例

交叉连接

from Product p, Category c

内连接

inner join

join

from Category c inner join c.products as p

左外连接

left outer join

left join

from Category c left join c.products as p

右外连接

right outer join

right join

from Category c right join c.products as p

                                                                                                               

 

Table – Category2 – Product4

使用交叉连接

String hql = "FROM Product p, Category c";

Query query  = session.createQuery(hql);

List <Object[]> list = query.list();

for(Object [] value:list){

Category c = (Category)value[0];

Product p = (Product)value[1];

}

 

使用内连接

String hql = "SELECT p FROM p FROM Product p inner join p.category";

Query query  = session.createQuery(hql);

List<Product> list = query.list();

或者

String hql = "SELECT p FROM Product p, Category c WHERE p.category = c";

或者

String hql = "SELECT p FROM Product p, Category c WHERE p.category.id = c.id";

 

QBC中实现内连接

Criteria criteria = session.createCriteria(Product.class).createCriteria("category");

List<Product> list = criteria.list();

 

使用左外连接

String hql = "SELECT FROM Category c LEFT OUTER JOIN c.products ORDER BY c.id";

Query query  = session.createQuery(hql);

List<Category> list = query.list();

 

使用QBC中实现左外连接

Criteria criteria = session.createCriteria(Category.class).setFetchMode("products", FetchMode.JOIN);

List <Category> list = criteria.list();

 

使用右外链接

String hql = "FROM Category c RIGHT OUTER JOIN c.products";

Query query = session.createQuery(hql);

List<Object[]>  list = query.list();

for(Object[] o : list){

Category c = (Category) o[0];

Product = (Product) o[1];

if(c!=null){

System.out.println(c.getId());

}

System.out.println(p.getId());

}

注意Oracle Dialect 9i方言不支持右外链接,但是Oracle 9 Dialect支持使用右外连接。

 

迫切内连接

String hql = "SELECT c FROM Category c join fetch c.products";

Query query = session.createQuery(hql);

List<Category> list = query.list();

for(Category c:list){

System.out.println(c.getName()+"\t");

Set<Product> products = c.getProducts();

for(Product p:products){

System.out.println(p.getName());

}

}

 

迫切左外连接

String hql = "SELECT c FROM Category c left join fetch c.products order by c.id";

Query query = session.createQuery(hql);

List<Category> list = query.list();

 

命名的HQL

Query query = session.getNamedQuery("hibernate.example.vo.selectGuestBooks");

query.setInteger("begin", 1);

query.setInteger("end", 12);

List<GuestBook> list = query.list();

 

对应的映射文件hbm

<query name="hibernate.example.vo.selectGuestBooks">

<![CDATA[SELECT gb FROM GuestBook gb WHERE gb.id BETWEEN :begin AND :end]]>

</query>

 

 

使用SQL查询

String sql = "SELECT * FROM GUESTBOOK";

SQLQuery sqlQuery = session.createSQLQuery(sql);

List<Object[]> list = sqlQuery.list();

 

使用SQL查询

String sql = "SELECT * FROM GUESTBOOK ORDER BY ID DESC";

SQLQuery sqlQuery = session.createSQLQuery(sql);

sqlQuery.addScalar("id", Hibernate.INTERGER);

sqlQuery.addScalar("name", Hibernate.STRING);

sqlQuery.addScalar("email", Hibernate.STRING);

sqlQuery.addScalar("title", Hibernate.STRING);

List<Object[]> list = sqlQuery.list();

 

使用SQL查询,返回持久化对象

String sql = "SELECT * FROM GUESTBOOK";

SQLQuery sqlQuery = session.createSQLQuery(sql);

sqlQuery.addEntity(GuestBook.class);

List<GuestBook> list = sqlQuery.list();

 

使用SQL查询,采用别名方式映射,返回持久化对象

String sql = "SELECT {p.*} FROM PRODUCT p,  CATEGORY c WHERE p.category_id = c.id";

SQLQuery sqlQuery = session.createSQLQuery(sql);

sqlQuery.addEntity("p", Product.class);

List<Product> list = sqlQuery.list();

 

使用SQL查询(投影查询),返回持久化对象

String sql = "SELECT p.id, p.name FROM PRODUCT p, CATEGORY c WHERE p.category_id = c.id";

SQLQuery sqlQuery = session.createSQLQuery(sql);

sqlQuery.addScalar("id", Hibernate.INTEGER).addScalar("name", Hibernate.STRING);

sqlQuery.setResultTransformer(Transformers.aliasToBean(Product.class));

List<Product> list = sqlQuery.list();

 

SQL使用"?"作为参数占位符

String sql = "SELECT {p.*} FROM PRODUCT p, CATEGORY c WHERE p.category_id = c.id AND c.id = ?";

SQLQuery sqlQuery = session.createSQLQuery(sql);

sqlQuery.addEntity("p", Product.class);

sqlQuery.setInteger(0, 2);

//sqlQuery.setParameter(0, new Integer(2));

List<Product> list = sqlQuery.list();

 

SQL使用名称作为参数占位符

String sql = "SELECT {p.*} FROM PRODUCT p, CATEGORY c WHERE p.category_id = c.id AND c.id =:categoryId";

SQLQuery sqlQuery = session.createSQLQuery(sql);

sqlQuery.addEntity("p", Product.class);

sqlQuery.setInteger("categoryId", 2);

List<Product> list = sqlQuery.list();

 

SQL更细记录

String sql = "UPDATE PRODUCT SET NAME=?, PRICE=? WHERE ID=?";

SQLQuery sqlQuery = session.createSQLQuery(sql);

sqlQuery.setString(0, "Xavier");

sqlQuery.setDouble(1, 56.00);

sqlQuery.setInteger(2, 2);

int rowCount = sqlQuery.executeUpdate();

 

SQL删除记录

String sql = "DELETE FROM PRODUCT WHERE ID=:id";

SQLQuery sqlQuery = session.createSQLQuery(sql);

sqlQuery.setInteger("id", 3);

int rowCount = sqlQuery.executeUpdate();

 

命名的SQL

Query query = session.getNamedQuery("hibernate.example.vo.SQLQueryGuestBook");

query.setInteger("id", 1);

List<GuestBook> list = query.list();

 

对应的映射文件hbm

<sql-query name="hibernate.example.vo.SQLQueryGuestBook">

<![CDATA[SELECT {gb.*} FROM GUESTBOOK gb WHERE gb.id=:id]]>

<return alias="gb" class="hibernate.example.vo.GuestBook"/>

</sql-query>

 

调用存储过程

CREATE OR REPLACE PROCEDURE selectGuestBookById

(sp_ref OUT SYS_REFCURSOR, inputId IN guestbook.id%type) AS

BEGIN

OPEN sp ref FOR

select * from guestbook where id = inputId;

END selectGuestBookById;

 

<sql-query name="getGuestBookByIdSp" callable="true">

<return alias="GuestBook" class="hibernate.example.vo.GuestBook"/>

{call selectGuestBookById(?,:inputId)}

</sql-query>

Query query = session.getNamedQuery("getGuestBookByIdSp");

query.setParameter("inputId", 1);

List<GuestBook> list = query.list();

 

 

Hibernate中直接使用JDBC

Hibernate应用中,可以通过Session对象的connection()方法,获取java.sql.Connection对象,执行SQL语句

Transaction tx = session.beginTransaction();

Connection conn = session.connection();

String procedure = "{call updateGuestBookById(?, ?}";

try{

CallableStatement stmt = conn.prepareCall(procedure);

stmt.setInt(1, 1);

stmt.setString(2, "Update subject");

stmt.executeUpdate();

tx.commit();

}catch(SQLException e{

e.printStackTrace();

tx.rollback();

}

 

CREATE OR REPLACE PROCEDURE updateGuestBookById(

inputId IN guestbook.id%type,

newTitle In guestbook.title%type) AS

BEGIN

update guestbook set title = newTitle where id = inputId;

END updateGuestBookById;

注意: Hibernate支持的存储过程只是支持查询数据的存储过程,同时还要有数据返回;如果只是更新的存储过程或者删除数据的存储过程,如果要执行这样的存储过程,需要通过JDBC来操作。Connection对象不需要释放,但是这个对象是存在于Hibernate的事务控制当中,所以只关注它的调用,不许要关闭。

Advertisements
 
Leave a comment

Posted by on 04/07/2011 in HIBERNATE

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: