1. NativeSQL > HQL > EJB QL(JP QL 1.0) > QBC(Query By Criteria) > QBE(Query By Example)
2. 总结：QL应该和导航关系结合，共同为查询提供服务。
以下图为例：
Category:
package com.bjsxt.hibernate; import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id; @Entity
public class Category { private Integer id; private String name; @Id
@GeneratedValue
public Integer getId() {
return id;
} public void setId(Integer id) {
this.id = id;
} public String getName() {
return name;
} public void setName(String name) {
this.name = name;
}
}
Topic:
package com.bjsxt.hibernate; import java.util.Date; import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToOne; @Entity
public class Topic { private Integer id; private String title; private Category category; private Date createDate; @Id
@GeneratedValue
public Integer getId() {
return id;
} public void setId(Integer id) {
this.id = id;
} public String getTitle() {
return title;
} public void setTitle(String title) {
this.title = title;
} @ManyToOne(fetch=FetchType.LAZY)
public Category getCategory() {
return category;
} public void setCategory(Category category) {
this.category = category;
} public Date getCreateDate() {
return createDate;
} public void setCreateDate(Date createDate) {
this.createDate = createDate;
}
}
Msg:
package com.bjsxt.hibernate; import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToOne; @Entity
public class Msg { private Integer id; private String cont; private Topic topic; @Id
@GeneratedValue
public Integer getId() {
return id;
} public void setId(Integer id) {
this.id = id;
} public String getCont() {
return cont;
} public void setCont(String cont) {
this.cont = cont;
} @ManyToOne
public Topic getTopic() {
return topic;
} public void setTopic(Topic topic) {
this.topic = topic;
}
}
MsgInfo:
package com.bjsxt.hibernate; //VO(Value Object) DTO(Data Transfer Object)
public class MsgInfo { private Integer id; private String cont; private String topicName; private String categoryName; public MsgInfo(Integer id,String cont,String topicName,String categoryName){
this.id = id;
this.cont = cont;
this.topicName = topicName;
this.categoryName = categoryName;
} public Integer getId() {
return id;
} public void setId(Integer id) {
this.id = id;
} public String getCont() {
return cont;
} public void setCont(String cont) {
this.cont = cont;
} public String getTopicName() {
return topicName;
} public void setTopicName(String topicName) {
this.topicName = topicName;
} public String getCategoryName() {
return categoryName;
} public void setCategoryName(String categoryName) {
this.categoryName = categoryName;
}
}
HibernateQLTest1:
package com.bjsxt.hibernate; import java.util.Date;
import java.util.List; import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.tool.hbm2ddl.SchemaExport;
import org.junit.After;
import org.junit.Before;
import org.junit.Test; public class HibernateQLTest {
private static SessionFactory sf = null; @Before
public void beforeClass(){
// new SchemaExport(new AnnotationConfiguration().configure()).create(false, true);
sf = new AnnotationConfiguration().configure().buildSessionFactory();
} @After
public void afterClass(){
if(sf != null){
sf.close();
}
} @Test
public void testSave(){ Session session = sf.getCurrentSession();
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.setCreateDate(new Date());
t.setTitle("t"+i);
session.save(t);
} for(int i=0; i<10; i++){
Topic t = new Topic();
t.setId(1);
Msg m = new Msg();
m.setTopic(t);
m.setCont("m" + i);
session.save(m);
} session.getTransaction().commit();
} @Test
public void testHQL_01(){
Session session = sf.getCurrentSession();
session.beginTransaction();
Query q = (Query) session.createQuery("from Category");
List<Category> categories = (List<Category>)q.list();
for(Category c : categories){
System.out.println(c.getName());
}
session.getTransaction().commit();
} @Test
public void testHQL_02(){
Session session = sf.getCurrentSession();
session.beginTransaction();
Query q = (Query) session.createQuery("from Category c where c.name > 'c5'");
List<Category> categories = (List<Category>)q.list();
for(Category c : categories){
System.out.println(c.getName());
}
session.getTransaction().commit();
} @Test
public void testHQL_03(){
Session session = sf.getCurrentSession();
session.beginTransaction();
Query q = (Query) session.createQuery("from Category c order by name desc");
List<Category> categories = (List<Category>)q.list();
for(Category c : categories){
System.out.println(c.getName());
}
session.getTransaction().commit();
} @Test
public void testHQL_04(){
Session session = sf.getCurrentSession();
session.beginTransaction();
Query q = (Query) session.createQuery(
"select distinct c from Category c order by name desc");//主键不同
List<Category> categories = (List<Category>)q.list();
for(Category c : categories){
System.out.println(c.getName());
}
session.getTransaction().commit();
} @Test
public void testHQL_05(){
Session session = sf.getCurrentSession();
session.beginTransaction();
/*Query q = (Query) session.createQuery(
"from Category c where c.id > :min and c.id < :max");//主键不同
// q.setParameter("min", 2);
// q.setParameter("max", 8);
q.setInteger("min", 2);
q.setInteger("max", 8);*/ Query q = (Query) session.createQuery("from Category c where c.id > :min and c.id < :max")
.setInteger("min", 2)
.setInteger("max", 8);
List<Category> categories = (List<Category>)q.list();
for(Category c : categories){
System.out.println(c.getId() + "-" +c.getName());
}
session.getTransaction().commit();
} /**
* 链式编程
*/
@Test
public void testHQL_06(){
Session session = sf.getCurrentSession();
session.beginTransaction();
Query q = (Query) session.createQuery("from Category c where c.id > ? and c.id < ?")
.setInteger(0, 2)//索引从0开始
.setInteger(1, 8);
List<Category> categories = (List<Category>)q.list();
for(Category c : categories){
System.out.println(c.getId() + "-" +c.getName());
}
session.getTransaction().commit();
} /**
* 分页
*/
@Test
public void testHQL_07(){
Session session = sf.getCurrentSession();
session.beginTransaction();
Query q = (Query) session.createQuery("from Category c order by name desc");
q.setMaxResults(4);//每页4条
q.setFirstResult(2);//从第二行开始
List<Category> categories = (List<Category>)q.list();
for(Category c : categories){
System.out.println(c.getId() + "-" +c.getName());
}
session.getTransaction().commit();
} /**
* 分页(换一种获取方式)
*/
@Test
public void testHQL_08(){
Session session = sf.getCurrentSession();
session.beginTransaction();
Query q = (Query) session.createQuery("select c.id,c.name from Category c order by name desc");
q.setMaxResults(4);//每页4条
q.setFirstResult(2);//从第二行开始
List<Object[]> categories = (List<Object[]>)q.list();
for(Object[] c : categories){
System.out.println(c[0] + "-" +c[1]);
}
session.getTransaction().commit();
} /**
* 设定 fetch type 为 lazy 后将不会有第二条sql语句
*/
@Test
public void testHQL_09(){
Session session = sf.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery("from Topic t where t.category.id = 1");
List<Topic> topics = (List<Topic>)q.list();
for(Topic t : topics){
System.out.println(t.getId() + "-" + t.getTitle());
// System.out.println(t.getCategory().getName());
}
session.getTransaction().commit();
} /**
* 设定 fetch type 为 lazy 后将不会有第二条sql语句
*/
@Test
public void testHQL_10(){
Session session = sf.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery("from Topic t where t.category.id = 1");
List<Topic> topics = (List<Topic>)q.list();
for(Topic t : topics){
System.out.println(t.getId() + "-" + t.getTitle());
// System.out.println(t.getCategory().getName());
}
session.getTransaction().commit();
} @Test
public void testHQL_11(){
Session session = sf.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery("from Msg m where m.topic.category.id = 1");
for(Object o : q.list()){
Msg m = (Msg)o;
System.out.println(m.getCont());
}
session.getTransaction().commit();
} /**
* VO(Value Object)
* DTO(Data Transfer Object)
*/
@Test
public void testHQL_12(){
Session session = sf.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery("select new com.bjsxt.hibernate.MsgInfo(m.id, m.cont, m.topic.title, m.topic.category.name) from Msg m");
for(Object o : q.list()){
MsgInfo m = (MsgInfo)o;
System.out.println(m.getCont());
}
session.getTransaction().commit();
} //动手测试left right join
//为什么不能直接写 Category 名，而必须写 t.category
//因为有可能存在多个成员变量（同一个类），需要指明哪一个成员变量的连接条件来做连接
@Test
public void testHQL_13(){
Session session = sf.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery("select t.title,c.name from Topic t join t.category c");
List<Object[]> list = q.list();
for(Object[] o : list){
System.out.println(o[0] + "-" + o[1]);
}
session.getTransaction().commit();
} //学习使用 uniqueResult
@Test
public void testHQL_14(){
Session session = sf.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery("from Msg m where m = :MsgToSearch");//不重要
Msg m = new Msg();
m.setId(1);
q.setParameter("MsgToSearch", m); Msg mResult = (Msg)q.uniqueResult();
System.out.println(mResult.getCont()); session.getTransaction().commit();
} @Test
public void testHQL_15(){
Session session = sf.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery("select count(*) from Msg m"); long count = (Long)q.uniqueResult();
System.out.println(count);
session.getTransaction().commit();
} @Test
public void testHQL_16(){
Session session = sf.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery("select max(m.id), min(m.id), avg(m.id), sum(m.id) from Msg m"); Object[] o = (Object[])q.uniqueResult();
System.out.println(o[0] + "-" + o[1] + "-" + o[2] + "-" + o[3]); session.getTransaction().commit();
} @Test
public void testHQL_17(){
Session session = sf.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery("from Msg m where m.id between 3 and 5"); for(Object o : q.list()){
Msg m = (Msg)o;
System.out.println(m.getId() + "-" +m.getCont());
} session.getTransaction().commit();
} @Test
public void testHQL_18(){
Session session = sf.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery("from Msg m where m.id in(3,4,5)"); for(Object o : q.list()){
Msg m = (Msg)o;
System.out.println(m.getId() + "-" +m.getCont());
} session.getTransaction().commit();
} @Test
public void testHQL_19(){
Session session = sf.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery("from Msg m where m.cont is not null"); for(Object o : q.list()){
Msg m = (Msg)o;
System.out.println(m.getId() + "-" +m.getCont());
} session.getTransaction().commit();
} @Test
public void testSchemaExport(){
new SchemaExport(new AnnotationConfiguration().configure()).create(false, true);
} public static void main(String[] args) {
// new HibernatTreeTest().testSave();
new HibernateQLTest().beforeClass();
} }
HibernateQLTest2:
package com.bjsxt.hibernate; 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.tool.hbm2ddl.SchemaExport;
import org.junit.After;
import org.junit.Before;
import org.junit.Test; public class HibernateQLTest {
private static SessionFactory sf = null; @Before
public void beforeClass(){
// new SchemaExport(new AnnotationConfiguration().configure()).create(false, true);
sf = new AnnotationConfiguration().configure().buildSessionFactory();
} @After
public void afterClass(){
if(sf != null){
sf.close();
}
} @Test
public void testSave(){ Session session = sf.getCurrentSession();
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.setCreateDate(new Date());
t.setTitle("t"+i);
session.save(t);
} for(int i=0; i<10; i++){
Topic t = new Topic();
t.setId(1);
Msg m = new Msg();
m.setTopic(t);
m.setCont("m" + i);
session.save(m);
} session.getTransaction().commit();
} //is empty and is not empty
@Test
public void testHQL_20(){
Session session = sf.getCurrentSession();
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();
} @Test
public void testHQL_21(){
Session session = sf.getCurrentSession();
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();
} @Test
public void testHQL_22(){
Session session = sf.getCurrentSession();
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();
} //不重要
@Test
public void testHQL_23(){
Session session = sf.getCurrentSession();
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();
} @Test
public void testHQL_24(){
Session session = sf.getCurrentSession();
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();
} @Test
public void testHQL_25(){
Session session = sf.getCurrentSession();
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();
} @Test
public void testHQL_26(){
Session session = sf.getCurrentSession();
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();
} @Test
public void testHQL_27(){
Session session = sf.getCurrentSession();
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();
} @Test
public void testHQL_28(){
Session session = sf.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery("select t.title, count(*) from Topic t group by t.title having count(*) > 0"); for(Object o : q.list()){
Object[] arr = (Object[])o;
System.out.println(arr[0] + "|" + arr[1]);
} session.getTransaction().commit();
} @Test
public void testHQL_29(){
Session session = sf.getCurrentSession();
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();
} @Test
public void testHQL_30(){
Session session = sf.getCurrentSession();
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();
} //用in可以实现 exists 的功能
//但是 exists 执行效率高
@Test
public void testHQL_31(){
Session session = sf.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery("from Topic t where not exists (select m.id from Msg m where m.topic.id = t.id)");//就是沒有回帖的topic for(Object o : q.list()){
Topic t = (Topic)o;
System.out.println(t.getTitle());
} session.getTransaction().commit();
} @Test
public void testHQL_32(){
Session session = sf.getCurrentSession();
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());
} q = session.createQuery("update Topic t set t.title = lower(t.title)");
q.executeUpdate(); session.getTransaction().commit();
} //不重要
@Test
public void testHQL_33(){
Session session = sf.getCurrentSession();
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();
} //Native(不太重要)
@Test
public void testHQL_34(){
Session session = sf.getCurrentSession();
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();
} @Test
public void testSchemaExport(){
new SchemaExport(new AnnotationConfiguration().configure()).create(false, true);
} public static void main(String[] args) {
// new HibernatTreeTest().testSave();
new HibernateQLTest().beforeClass();
} }
jar包链接: https://pan.baidu.com/s/1slvwzbn 密码: hwef
代码链接1: https://pan.baidu.com/s/1skCHfU9 密码: ka73
代码链接2: https://pan.baidu.com/s/1qYruCUC 密码: v4eb