Hibernate查询（二）
来源：互联网 发布：程序员被骗婚女方 编辑：程序博客网 时间：2024/05/21 10:10
QL语句（二）
package com.zgy.hibernate.model;
import java.util.Date;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.cfg.Configuration;
import org.hibernate.tool.hbm2ddl.SchemaExport;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
public class HibernateQLTest {
private static SessionFactory sf;
@BeforeClass
public static void beforeClass() {
sf = HibernateUtil.getSessionFactory();
}
@AfterClass
public static void afterClass() {
sf.close();
}
@Test
public void testSchemaExport() {
new SchemaExport(new Configuration().configure()).create(false, true);
}
@Test
public void testSave() {
Session session = sf.openSession();
session.beginTransaction();
for(int i=0; i<10; i++) {
Category c = new Category();
c.setName("c" + i);
session.save(c);
}
for(int i=0; i<10; i++) {
Category c = new Category();
c.setId(1);
Topic t = new Topic();
t.setCategory(c);
t.setTitle("t" + i);
t.setCreateDate(new Date());
session.save(t);
}
for(int i=0; i<10; i++) {
Topic t = new Topic();
t.setId(1);
Msg m = new Msg();
m.setCont("m" + i);
m.setTopic(t);
session.save(m);
}
session.getTransaction().commit();
session.close();
}
//is empty and is not empty
@Test
public void testHQL_20() {
testSave();
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("from Topic t where t.msgs is empty");
for(Object o : q.list()) {
Topic t = (Topic)o;
System.out.println(t.getId() + "-" + t.getTitle());
}
session.getTransaction().commit();
session.close();
}
@Test
public void testHQL_21() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("from Topic t where t.title like '%5'");
for(Object o : q.list()) {
Topic t = (Topic)o;
System.out.println(t.getId() + "-" + t.getTitle());
}
session.getTransaction().commit();
session.close();
}
@Test
public void testHQL_22() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("from Topic t where t.title like '_5'");
for(Object o : q.list()) {
Topic t = (Topic)o;
System.out.println(t.getId() + "-" + t.getTitle());
}
session.getTransaction().commit();
session.close();
}
//不重要
@Test
public void testHQL_23() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("select lower(t.title)," +
"upper(t.title)," +
"trim(t.title)," +
"concat(t.title, '***')," +
"length(t.title)" +
" from Topic t ");
for(Object o : q.list()) {
Object[] arr = (Object[])o;
System.out.println(arr[0] + "-" + arr[1] + "-" + arr[2] + "-" + arr[3] + "-" + arr[4] + "-");
}
session.getTransaction().commit();
session.close();
}
@Test
public void testHQL_24() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("select abs(t.id)," +
"sqrt(t.id)," +
"mod(t.id, 2)" +
" from Topic t ");
for(Object o : q.list()) {
Object[] arr = (Object[])o;
System.out.println(arr[0] + "-" + arr[1] + "-" + arr[2] );
}
session.getTransaction().commit();
session.close();
}
@Test
public void testHQL_25() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("select current_date, current_time, current_timestamp, t.id from Topic t");
for(Object o : q.list()) {
Object[] arr = (Object[])o;
System.out.println(arr[0] + " | " + arr[1] + " | " + arr[2] + " | " + arr[3]);
}
session.getTransaction().commit();
session.close();
}
@Test
public void testHQL_26() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("from Topic t where t.createDate < :date");
q.setParameter("date", new Date());
for(Object o : q.list()) {
Topic t = (Topic)o;
System.out.println(t.getTitle());
}
session.getTransaction().commit();
session.close();
}
@Test
public void testHQL_27() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("select t.title, count(*) from Topic t group by t.title") ;
for(Object o : q.list()) {
Object[] arr = (Object[])o;
System.out.println(arr[0] + "|" + arr[1]);
}
session.getTransaction().commit();
session.close();
}
@Test
public void testHQL_28() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("select t.title, count(*) from Topic t group by t.title having count(*) >= 1") ;
for(Object o : q.list()) {
Object[] arr = (Object[])o;
System.out.println(arr[0] + "|" + arr[1]);
}
session.getTransaction().commit();
session.close();
}
@Test
public void testHQL_29() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("from Topic t where t.id < (select avg(t.id) from Topic t)") ;
for(Object o : q.list()) {
Topic t = (Topic)o;
System.out.println(t.getTitle());
}
session.getTransaction().commit();
session.close();
}
@Test
public void testHQL_30() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("from Topic t where t.id < ALL (select t.id from Topic t where mod(t.id, 2)= 0) ") ;
for(Object o : q.list()) {
Topic t = (Topic)o;
System.out.println(t.getTitle());
}
session.getTransaction().commit();
session.close();
}
//用in 可以实现exists的功能
//但是exists执行效率高
@Test
public void testHQL_31() {
Session session = sf.openSession();
session.beginTransaction();// t.id not in (1)
Query q = session.createQuery("from Topic t where not exists (select m.id from Msg m where m.topic.id=t.id)") ;
// Query q = session.createQuery("from Topic t where exists (select m.id from Msg m where m.topic.id=t.id)") ;
for(Object o : q.list()) {
Topic t = (Topic)o;
System.out.println(t.getTitle());
}
session.getTransaction().commit();
session.close();
}
//update and delete
//规范并没有说明是不是要更新persistent object，所以如果要使用，建议在单独的trasaction中执行
@Test
public void testHQL_32() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("update Topic t set t.title = upper(t.title)") ;
q.executeUpdate();
q = session.createQuery("from Topic");
for(Object o : q.list()) {
Topic t = (Topic)o;
System.out.println(t.getTitle());
}
session.createQuery("update Topic t set t.title = lower(t.title)")
.executeUpdate();
session.getTransaction().commit();
session.close();
}
@Test
public void testHQL_33() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.getNamedQuery("topic.selectCertainTopic");
q.setParameter("id", 5);
Topic t = (Topic)q.uniqueResult();
System.out.println(t.getTitle());
session.getTransaction().commit();
session.close();
}
//Native（了解）
@Test
public void testHQL_34() {
Session session = sf.openSession();
session.beginTransaction();
SQLQuery q = session.createSQLQuery("select * from category limit 2,4").addEntity(Category.class);
List<Category> categories = (List<Category>)q.list();
for(Category c : categories) {
System.out.println(c.getName());
}
session.getTransaction().commit();
session.close();
}
@Test
public void testHQL_35() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.getNamedQuery("topic.select2_5Topic");
for(Object o : q.list()) {
Topic t = (Topic)o;
System.out.println(t.getTitle());
}
session.getTransaction().commit();
session.close();
}
public static void main(String[] args) {
beforeClass();
}
}
2.QBC
还是同样的Model层次，使用如下的方法方法进行测试：
（1）编写HibernateUtil.java，创建SessionFactory对象
package com.zgy.hibernate.model;
import org.hibernate.SessionFactory;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;
public class HibernateUtil {
static SessionFactory sessionFactory = buildSessionFactory();
private static SessionFactory buildSessionFactory() {
Configuration configure = new Configuration().configure();
ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder()
.applySettings(configure.getProperties()).build();
return sessionFactory = configure.buildSessionFactory(serviceRegistry);
}
public static SessionFactory getSessionFactory(){
return sessionFactory;
}
}
（2）HibernateQLTest.java用于测试
package com.zgy.hibernate.model;
import java.util.Date;
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.annotations.FetchMode;
import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.criterion.Restrictions;
import org.hibernate.tool.hbm2ddl.SchemaExport;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
public class HibernateQLTest {
private static SessionFactory sf;
@BeforeClass
public static void beforeClass() {
sf = HibernateUtil.getSessionFactory();
}
@AfterClass
public static void afterClass() {
sf.close();
}
@Test
public void testSchemaExport() {
new SchemaExport(new AnnotationConfiguration().configure()).create(false, true);
}
@Test
public void testSave() {
Session session = sf.openSession();
session.beginTransaction();
for(int i=0; i<10; i++) {
Category c = new Category();
c.setName("c" + i);
session.save(c);
}
for(int i=0; i<10; i++) {
Category c = new Category();
c.setId(1);
Topic t = new Topic();
t.setCategory(c);
t.setTitle("t" + i);
t.setCreateDate(new Date());
session.save(t);
}
for(int i=0; i<10; i++) {
Topic t = new Topic();
t.setId(1);
Msg m = new Msg();
m.setCont("m" + i);
m.setTopic(t);
session.save(m);
}
session.getTransaction().commit();
session.close();
}
//is empty and is not empty
@Test
public void testQBC() {
testSave();
Session session = sf.openSession();
session.beginTransaction();
//criterion 标准/准则/约束
Criteria c = session.createCriteria(Topic.class) //相当于HQL语句中的from Topic
.add(Restrictions.gt("id", 2)) //greater than = id > 2
.add(Restrictions.lt("id", 8)) //little than = id < 8
.add(Restrictions.like("title", "t_"))
.createCriteria("category")
.add(Restrictions.between("id", 1, 5)) //category.id >= 3 and category.id <=5
;
//DetachedCriterea
Criteria c2 = session.createCriteria(Msg.class)
.add(Restrictions.gt("id",5))
.add(Restrictions.lt("id", 10))
.add(Restrictions.isNotNull("cont"))
.createCriteria("topic")
.add(Restrictions.idEq(1))
.add(Restrictions.idEq(1))
.createCriteria("category")
.add(Restrictions.idEq(1));
for(Object o : c2.list()) {
Msg m = (Msg)o;
System.out.println(m.getId()+m.getCont());
}
System.out.println("--------------------------------------");
for(Object o : c2.list()) {
Msg m = (Msg)o;
System.out.println("Category"+m.getTopic().getCategory().getId()+"-"+"Topic"+m.getTopic().getId()+"-"+"Msg"+m.getId());
}
session.getTransaction().commit();
session.close();
}
public static void main(String[] args) {
beforeClass();
}
}
查询SQL语句：
Hibernate:
create table Category (
id integer not null auto_increment,
name varchar(255),
primary key (id)
)
Hibernate:
create table Msg (
id integer not null auto_increment,
cont varchar(255),
topic_id integer,
primary key (id)
)
Hibernate:
create table Topic (
id integer not null auto_increment,
createDate date,
title varchar(255),
category_id integer,
primary key (id)
)
Hibernate:
alter table Msg
add constraint FK_87qfcl8orshwjlllfik5deys5
foreign key (topic_id)
references Topic (id)
Hibernate:
alter table Topic
add constraint FK_jqmw7lsw6i6lrdnw07c1id1nt
foreign key (category_id)
references Category (id)
Hibernate:
insert
into
Category
(name)
values
(?)
Hibernate:
insert
into
Category
(name)
values
(?)
Hibernate:
insert
into
Category
(name)
values
(?)
Hibernate:
insert
into
Category
(name)
values
(?)
Hibernate:
insert
into
Category
(name)
values
(?)
Hibernate:
insert
into
Category
(name)
values
(?)
Hibernate:
insert
into
Category
(name)
values
(?)
Hibernate:
insert
into
Category
(name)
values
(?)
Hibernate:
insert
into
Category
(name)
values
(?)
Hibernate:
insert
into
Category
(name)
values
(?)
Hibernate:
insert
into
Topic
(category_id, createDate, title)
values
(?, ?, ?)
Hibernate:
insert
into
Topic
(category_id, createDate, title)
values
(?, ?, ?)
Hibernate:
insert
into
Topic
(category_id, createDate, title)
values
(?, ?, ?)
Hibernate:
insert
into
Topic
(category_id, createDate, title)
values
(?, ?, ?)
Hibernate:
insert
into
Topic
(category_id, createDate, title)
values
(?, ?, ?)
Hibernate:
insert
into
Topic
(category_id, createDate, title)
values
(?, ?, ?)
Hibernate:
insert
into
Topic
(category_id, createDate, title)
values
(?, ?, ?)
Hibernate:
insert
into
Topic
(category_id, createDate, title)
values
(?, ?, ?)
Hibernate:
insert
into
Topic
(category_id, createDate, title)
values
(?, ?, ?)
Hibernate:
insert
into
Topic
(category_id, createDate, title)
values
(?, ?, ?)
Hibernate:
insert
into
Msg
(cont, topic_id)
values
(?, ?)
Hibernate:
insert
into
Msg
(cont, topic_id)
values
(?, ?)
Hibernate:
insert
into
Msg
(cont, topic_id)
values
(?, ?)
Hibernate:
insert
into
Msg
(cont, topic_id)
values
(?, ?)
Hibernate:
insert
into
Msg
(cont, topic_id)
values
(?, ?)
Hibernate:
insert
into
Msg
(cont, topic_id)
values
(?, ?)
Hibernate:
insert
into
Msg
(cont, topic_id)
values
(?, ?)
Hibernate:
insert
into
Msg
(cont, topic_id)
values
(?, ?)
Hibernate:
insert
into
Msg
(cont, topic_id)
values
(?, ?)
Hibernate:
insert
into
Msg
(cont, topic_id)
values
(?, ?)
Hibernate:
select
this_.id as id1_1_2_,
this_.cont as cont2_1_2_,
this_.topic_id as topic_id3_1_2_,
topic1_.id as id1_2_0_,
topic1_.category_id as category4_2_0_,
topic1_.createDate as createDa2_2_0_,
topic1_.title as title3_2_0_,
category2_.id as id1_0_1_,
category2_.name as name2_0_1_
from
Msg this_
inner join
Topic topic1_
on this_.topic_id=topic1_.id
inner join
Category category2_
on topic1_.category_id=category2_.id
where
this_.id>?
and this_.id<?
and this_.cont is not null
and topic1_.id = ?
and topic1_.id = ?
and category2_.id = ?
6m5
7m6
8m7
9m8
--------------------------------------
Hibernate:
select
this_.id as id1_1_2_,
this_.cont as cont2_1_2_,
this_.topic_id as topic_id3_1_2_,
topic1_.id as id1_2_0_,
topic1_.category_id as category4_2_0_,
topic1_.createDate as createDa2_2_0_,
topic1_.title as title3_2_0_,
category2_.id as id1_0_1_,
category2_.name as name2_0_1_
from
Msg this_
inner join
Topic topic1_
on this_.topic_id=topic1_.id
inner join
Category category2_
on topic1_.category_id=category2_.id
where
this_.id>?
and this_.id<?
and this_.cont is not null
and topic1_.id = ?
and topic1_.id = ?
and category2_.id = ?
Category1-Topic1-Msg6
Category1-Topic1-Msg7
Category1-Topic1-Msg8
Category1-Topic1-Msg9
- hibernate查询（二）
- Hibernate查询（二）
- Hibernate(二) Hibernate基本查询
- hibernate分页查询二
- Hibernate (二) 分页查询
- Hibernate查询方式(二)
- Hibernate（二）—Hibernate的一些基本概念和查询
- HQL：Hibernate查询语言(二)
- Hibernate中的数据查询(二)
- Hibernate(九)HQL查询二
- Hibernate实例hql查询【较多不太懂】（二十六）
- Hibernate 的查询语言 HQL 之（二）
- hibernate框架的查询方式QBC（二十）
- hibernate框架的查询方式sql语句（二十一）
- hibernate框架的查询优化（二十三）
- hibernate框架的查询策略（二十四）
- 【Java EE （Struts2 + Spring + Hibernate）开发】 ：Hibernate（二）之【HQL查询|条件查询|SQL查询】
- Hibernate笔记：HQL查询总结（二）——条件查询（转）
- 超实用的JavaScript技巧及最佳实践
- Unity之点击实现点击种植
- 模板方法模式
- Android之ListView原理学习与优化总结
- 连载《一个程序员的成长历程》-14.萌生创业的想法
- Hibernate查询（二）
- 对互联网海量数据实时计算的理解
- bzoj3572: [Hnoi2014]世界树
- Objective - C基础: 第一天 - 8.OC对象与函数
- IOS8下，百度地图无法定位解决办法
- 黑马程序员——OC内存管理
- 分享Java中模拟Ping操作的一个类
- 黑马程序员——JDK5.0新特征
- 线程池
