NativeSQL-------功能最強大

HQL--Hibernate QL

EJB QL (JP QL)---HQL的一個子集

QBC---Query By Criteria

QBE---Query By Example------功能最小

本節主要介紹EJB QL，它是HQL的子集

使用的是：org.hibernate.Query

示例：

Category版塊：id，name

Topic主題：id，title，createDate，category

Msg回帖：id，cont，topic

Topic(多對一)Category

Msg(多對一)Topic

1.建Category、Topic、Msg實體類

2.在hibernate.cfg.xml中添加mapping語句

3.建Junit測試類

3.1 寫測試方法testSchemaExport()，建好表

3.2 寫測試方法testSave()，在每張表里存10條數據，如下：

@Testpublic void testSave() {session session = sf.getCurrentSession();session.beginTransaction();//存入10個版塊for(int i=0;i<10;i++){Category category = new Category();category.setName("c"+i);session.save(category);}//存入10個主題，屬于category_1for(int i=0;i<10;i++){Category category = new Category();category.setId(1);Topic topic = new Topic();topic.setTitle("t"+i);topic.setCteateDate(new Date());topic.setCategory(category);session.save(topic);}//存入10條回帖，屬于topic_1for(int i=0;i<10;i++){Topic topic = new Topic();topic.setId(1);Msg msg = new Msg();msg.setCont("m"+i);msg.setTopic(topic);session.save(msg);}session.getTransaction().commit();}

以下表格依次為category、topic、msg

3.3 寫測試方法testHQL()，進行相關查詢

@Testpublic void testHQL() {Session session = sf.getCurrentSession();session.beginTransaction();//注意，EJBQL是面向對象的查詢語言,Category是實體類的名字，不能寫表名categoryQuery q = session.createQuery("from Category");for(Object o : q.list()){Category c = (Category) o;System.out.PRintln( c.getId()+ "-" +c.getName());}session.getTransaction().commit();}

EJB QL 查詢語句---舉例：

1.以對象的形式取出

Query q = session.createQuery("from Category");Query q = session.createQuery("from Category c where c.name > 'c5'");Query q = session.createQuery("from Category c order by c.name desc");Query q = session.createQuery("select distinct c from Category c order by c.name desc");//這里，冒號是占位符Query q = session.createQuery("from Category c where c.id > :min and c.id < :max");q.setParameter("min", 2);//q.setInteger("max", 8);  //兩種方式皆可//分頁顯示Query q = session.createQuery("from Category c order by c.name desc");q.setMaxResults(4);   //每頁顯示4條q.setFirstResult(2);  //從第2條開始顯示Query q = session.createQuery("from Msg m where m.topic.category.id=1");Query q = session.createQuery("from Msg m where m.id between 3 and 5");Query q = session.createQuery("from Msg m where m.id in (3,4,5)");Query q = session.createQuery("from Msg m where m.cont is not null");Query q = session.createQuery("from Topic t where t.category.id=1");//若測試此語句，需在Topic實體類中設置@OneToMany注解，對MsgQuery q = session.createQuery("from Topic t where t.msgs is empty");Query q = session.createQuery("from Topic t where t.title like '%5'");Query q = session.createQuery("from Topic t where t.title like '_5'");Query q = session.createQuery("from Topic t where t.id < (select avg(t.id) from Topic t)") ;Query q = session.createQuery("from Topic t where t.id < ALL (select t.id from Topic t where mod(t.id, 2)= 0) ");//用in 可以實現exists的功能//但是exists執行效率高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)") ;Query q = session.createQuery("from Topic t where t.id in (select m.id from Msg m where m.topic.id=t.id)") ;session.createQuery("update Topic t set t.title = upper(t.title)").executeUpdate();Query q = session.createQuery("from Topic");

2.以字段的形式取出

Query q = session.createQuery("select c.id, c.name from Category c order by c.name desc");//為什么不能直接寫Category名，而必須寫t.category//因為有可能存在多個成員變量（同一個類），需要指明用哪一個成員變量的連接條件來做連接Query q = session.createQuery("select t.title, c.name from Topic t join t.category c");Query q = session.createQuery("select lower(t.title)," +           "upper(t.title)," +          "trim(t.title)," +          "concat(t.title, '***')," +          "length(t.title)" +          " from Topic t ");Query q = session.createQuery("select abs(t.id)," +         "sqrt(t.id)," +         "mod(t.id, 2)" +         " from Topic t ");Query q = session.createQuery("select current_date, current_time, current_timestamp, t.id from Topic t");Query q = session.createQuery("select t.title, count(*) from Topic t group by t.title") ;Query q = session.createQuery("select t.title, count(*) from Topic t group by t.title having count(*) >= 1") ;

1.以對象的形式取出---打印輸出語句

//可以用List打印輸出List<Category> categories = (List<Category>)q.list();for(Category c : categories){System.out.println(c.getId() + "-" + c.getName());}//List也可以寫成如下形式for(Object o : q.list()){Category c = (Category) o;System.out.println( c.getId()+ "-" +c.getName());}//也可以用iterate打印輸出Iterator<Category> it = (Iterator<Category>) q.iterate();while(it.hasNext()){Category c = it.next();System.out.println(c.getId()+"-"+c.getName());}

2.以字段的形式取出---打印輸出語句   

//List,以數組的形式取出for(Object o : q.list()){Object[] obj = (Object[])o;System.out.println(obj[0]+ "-" + obj[1]);}//List的另一種形式List<Object[]> categories = (List<Object[]>)q.list();for(Object[] o : categories){System.out.println(o[0] + "-" + o[1]);}

3.查詢結果只有一個值時---q.uniqueResult()

Query q = session.createQuery("select count(*) from Msg");//當查詢結果只有一個值的時候用q.uniqueResult()//count(*)返回的是一個Long類型Long count = (Long) q.uniqueResult();System.out.println(count);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]);

1.list() 和 iterate()

Query q = session.createQuery("from Category");List<Category> categories = (List<Category>)q.list();或者：Iterator<Category> it = (Iterator<Category>) q.iterate();區別：1.list取所有2.Iterate先取ID，等用到的時候再根據ID來取對象3.Session中list第二次發出，仍會到數據庫查詢4.Literate第二次，首先找session級緩存

2. Hibernate 1+N問題

對于兩個關聯關系的表Topic(多對一)Category，當獲取Topic對象的時候(Query q = session.createQuery("from Topic");)，除了會發出查詢Topic表的SQL語句，也會發出查詢其關聯的Category表的SQL語句。解決辦法：1.將@ManyToOne注解的fetch屬性設置為LAZY2.將@在Category的@Entity注解下面一行添加@BatchSize(size=5)注解   意思是，獲取Category對象的時候，一次性獲取5條數據。即如果一共有10條數據，那么只會發出兩條SQL語句3.進行左連接，即將查詢語句改為：Query q = session.createQuery("from Topic t left join fetch t.category");

1.什么是緩存：在內存里開辟一塊空間，把本來應該存在硬盤上的東西，放到內存里，將來再讀取的時候，直接從內存里讀，這部分內存就叫緩存。2.一級緩存---session級別的緩存3.二級緩存---sessionFactory級別的緩存，可以跨越session存在4.hibernate.cfg.xml配置<property name="cache.use_second_level_cache">true</property><property name="cache.provider_class">org.hibernate.cache.EhCacheProvider</property>5.load默認使用二級緩存，iterate默認使用二級緩存6.list默認往二級緩存加數據，但是查詢的時候不使用7.如果query用二級緩存，需打開查詢緩存