Hibernate 使用 Annotation 6（各种查询语句）
Java代码1.packagecom.jlee06.QL;
2.
3.importjavax.persistence.Column;
4.importjavax.persistence.Entity;
5.importjavax.persistence.GeneratedValue;
6.importjavax.persistence.GenerationType;
7.importjavax.persistence.Id;
8.importjavax.persistence.Table;
9.
10./**
11.*@authorJLee
12.*板块
13.*/
14.@Entity
15.@Table(name="Category")
16.publicclassCategory{
17.
18.privateintid;
19.privateStringname;
20.
21.@Id
22.@GeneratedValue(strategy=GenerationType.AUTO)
23.publicintgetId(){
24.returnid;
25.}
26.publicvoidsetId(intid){
27.this.id=id;
28.}
29.
30.@Column(name="name")
31.publicStringgetName(){
32.returnname;
33.}
34.publicvoidsetName(Stringname){
35.this.name=name;
36.}
37.
38.}
Msg.java代码：
Java代码1.packagecom.jlee06.QL;
2.
3.importjavax.persistence.CascadeType;
4.importjavax.persistence.Column;
5.importjavax.persistence.Entity;
6.importjavax.persistence.FetchType;
7.importjavax.persistence.GeneratedValue;
8.importjavax.persistence.GenerationType;
9.importjavax.persistence.Id;
10.importjavax.persistence.JoinColumn;
11.importjavax.persistence.ManyToOne;
12.importjavax.persistence.Table;
13.
14.@Entity
15.@Table(name="Msg")
16.publicclassMsg{
17.
18.privateintid;
19.privateStringcont;
20.privateTopictopic;
21.
22.@Id
23.@GeneratedValue(strategy=GenerationType.AUTO)
24.publicintgetId(){
25.returnid;
26.}
27.publicvoidsetId(intid){
28.this.id=id;
29.}
30.
31.@Column(name="cont",length=500)
32.publicStringgetCont(){
33.returncont;
34.}
35.publicvoidsetCont(Stringcont){
36.this.cont=cont;
37.}
38.
39.@ManyToOne(fetch=FetchType.LAZY)
40.@JoinColumn(name="topicId")
41.publicTopicgetTopic(){
42.returntopic;
43.}
44.publicvoidsetTopic(Topictopic){
45.this.topic=topic;
46.}
47.
48.}
Topic.java代码：
Java代码1.packagecom.jlee06.QL;
2.
3.importjava.util.Date;
4.
5.importjavax.persistence.Column;
6.importjavax.persistence.Entity;
7.importjavax.persistence.FetchType;
8.importjavax.persistence.GeneratedValue;
9.importjavax.persistence.GenerationType;
10.importjavax.persistence.Id;
11.importjavax.persistence.JoinColumn;
12.importjavax.persistence.ManyToOne;
13.importjavax.persistence.NamedQueries;
14.importjavax.persistence.NamedQuery;
15.importjavax.persistence.Table;
16.importjavax.persistence.Temporal;
17.importjavax.persistence.TemporalType;
18.
19./**
20.*@authorJLee
21.*主题
22.*/
23.@Entity
24.@Table(name="topic")
25.@NamedQueries({
26.@NamedQuery(name="Topic.selectTopic",query="fromTopictwheret.id=:id"),
27.@NamedQuery(name="Topic.conditionTopic",query="fromTopictwheret.titlelike:title")
28.})
29.//hibernate3.3.2尚未支持
30.//@NamedNativeQueries({
31.//@NamedNativeQuery(name="native_sql_page",query="select*fromtopiclimit2,5")
32.//})
33.publicclassTopic{
34.
35.privateintid;
36.privateStringtitle;
37.privateDatecreateDate;
38.publicDategetCreateDate(){
39.returncreateDate;
40.}
41.
42.@Column(name="createDate")
43.@Temporal(TemporalType.DATE)
44.publicvoidsetCreateDate(DatecreateDate){
45.this.createDate=createDate;
46.}
47.privateCategorycategory;
48.
49.@Id
50.@GeneratedValue(strategy=GenerationType.AUTO)
51.publicintgetId(){
52.returnid;
53.}
54.publicvoidsetId(intid){
55.this.id=id;
56.}
57.
58.@Column(name="title",length=32)
59.publicStringgetTitle(){
60.returntitle;
61.}
62.publicvoidsetTitle(Stringtitle){
63.this.title=title;
64.}
65.
66.@ManyToOne(fetch=FetchType.LAZY)
67.@JoinColumn(name="categoryId")
68.publicCategorygetCategory(){
69.returncategory;
70.}
71.publicvoidsetCategory(Categorycategory){
72.this.category=category;
73.}
74.
75.}
MsgInfo.java代码：
Java代码1.packagecom.jlee06.QL;
2.
3./**
4.*VOValueObject
5.*@authorJLee
6.*查询使用的值对象
7.*/
8.publicclassMsgInfo{
9.
10.privateintid;
11.privateStringcont;
12.privateStringtopicName;
13.privateStringcategoryName;
14.
15.publicMsgInfo(intid,Stringcont,StringtopicName,StringcategoryName){
16.super();
17.this.id=id;
18.this.cont=cont;
19.this.topicName=topicName;
20.this.categoryName=categoryName;
21.}
22.
23.publicintgetId(){
24.returnid;
25.}
26.
27.publicvoidsetId(intid){
28.this.id=id;
29.}
30.
31.publicStringgetCont(){
32.returncont;
33.}
34.
35.publicvoidsetCont(Stringcont){
36.this.cont=cont;
37.}
38.
39.publicStringgetTopicName(){
40.returntopicName;
41.}
42.
43.publicvoidsetTopicName(StringtopicName){
44.this.topicName=topicName;
45.}
46.
47.publicStringgetCategoryName(){
48.returncategoryName;
49.}
50.
51.publicvoidsetCategoryName(StringcategoryName){
52.this.categoryName=categoryName;
53.}
54.
55.}
DataBase.java代码：
Java代码1.packagecom.jlee06.QL;
2.
3.importjava.util.Date;
4.importjava.util.List;
5.
6.importorg.hibernate.Query;
7.importorg.hibernate.SQLQuery;
8.importorg.hibernate.Session;
9.importorg.hibernate.SessionFactory;
10.importorg.hibernate.cfg.AnnotationConfiguration;
11.importorg.hibernate.tool.hbm2ddl.SchemaExport;
12.importorg.junit.AfterClass;
13.importorg.junit.BeforeClass;
14.importorg.junit.Test;
15.
16.publicclassDataBase{
17.
18.privatestaticSessionFactorysf;
19.@BeforeClass
20.publicstaticvoidbeforeClass(){
21.sf=newAnnotationConfiguration().configure().buildSessionFactory();
22.}
23.
24.//@AfterClass
25.//publicvoidafterClass(){
26.//sf.close();
27.//}
28.
29.@Test
30.publicvoidtestSave(){
31.Sessionsession=sf.getCurrentSession();
32.session.beginTransaction();
33.
34.for(inti=0;i<10;i++){
35.Categoryc=newCategory();
36.c.setName("c"+i);
37.session.save(c);
38.}
39.
40.for(inti=0;i<10;i++){
41.Categoryc=newCategory();
42.c.setId(1);
43.Topict=newTopic();
44.t.setCategory(c);
45.t.setTitle("t"+i);
46.t.setCreateDate(newDate());
47.session.save(t);
48.}
49.
50.for(inti=0;i<10;i++){
51.Topict=newTopic();
52.t.setId(1);
53.Msgm=newMsg();
54.m.setCont("m"+i);
55.m.setTopic(t);
56.session.save(m);
57.}
58.
59.session.getTransaction().commit();
60.}
61.
62./**
63.*HQL面向对象查询语句
64.*SQL语句
65.*select
66.category0_.idasid0_,
67.category0_.nameasname0_
68.from
69.Categorycategory0_
70.*/
71.@Test
72.publicvoidtestHQL_01(){
73.Sessionsession=sf.openSession();
74.session.beginTransaction();
75.
76.Queryq=session.createQuery("fromCategory");
77.List<Category>categorys=(List<Category>)q.list();
78.for(Categoryc:categorys){
79.System.out.println(c.getName());
80.}
81.session.getTransaction().commit();
82.session.close();
83.}
84.
85./**
86.*设置Where条件语句
87.*SQL语句
88.*select
89.category0_.idasid0_,
90.category0_.nameasname0_
91.from
92.Categorycategory0_
93.where
94.category0_.name>'c5'
95.*/
96.@Test
97.publicvoidtestHQL_02(){
98.Sessionsession=sf.getCurrentSession();
99.session.beginTransaction();
100.
101.Queryq=session.createQuery("fromCategorycwherec.name>'c5'");
102.List<Category>categorys=(List<Category>)q.list();
103.for(Categoryc:categorys){
104.System.out.println(c.getName());
105.}
106.session.getTransaction().commit();
107.}
108.
109./**
110.*使用orderby排序语句
111.*SQL语句
112.*select
113.category0_.idasid0_,
114.category0_.nameasname0_
115.from
116.Categorycategory0_
117.orderby
118.category0_.namedesc
119.*/
120.@Test
121.publicvoidtestHQL_03(){
122.Sessionsession=sf.getCurrentSession();
123.session.beginTransaction();
124.
125.Queryq=session.createQuery("fromCategorycorderbyc.namedesc");
126.List<Category>categorys=(List<Category>)q.list();
127.for(Categoryc:categorys){
128.System.out.println(c.getName());
129.}
130.session.getTransaction().commit();
131.}
132.
133./**
134.*使用distinct
135.*SQL语句
136.*select
137.distinctcategory0_.idasid0_,
138.category0_.nameasname0_
139.from
140.Categorycategory0_
141.orderby
142.category0_.namedesc
143.*/
144.@Test
145.publicvoidtestHQL_04(){
146.Sessionsession=sf.getCurrentSession();
147.session.beginTransaction();
148.
149.Queryq=session.createQuery("selectdistinctcfromCategorycorderbyc.namedesc");
150.List<Category>categorys=(List<Category>)q.list();
151.for(Categoryc:categorys){
152.System.out.println(c.getName());
153.}
154.session.getTransaction().commit();
155.}
156.
157./**
158.*在SQL语句中设置参数1
159.*select
160.category0_.idasid0_,
161.category0_.nameasname0_
162.from
163.Categorycategory0_
164.where
165.category0_.id>?
166.andcategory0_.id<?
167.*分开设置参数
168.*/
169.@Test
170.publicvoidtestHQL_05(){
171.Sessionsession=sf.getCurrentSession();
172.session.beginTransaction();
173.
174.Queryq=session.createQuery("fromCategorycwherec.id>:minandc.id<:max");
175.q.setParameter("min",2);
176.q.setParameter("max",5);
177.List<Category>categorys=(List<Category>)q.list();
178.for(Categoryc:categorys){
179.System.out.println(c.getId()+""+c.getName());
180.}
181.session.getTransaction().commit();
182.}
183.
184./**
185.*在SQL语句中设置参数2
186.*select
187.category0_.idasid0_,
188.category0_.nameasname0_
189.from
190.Categorycategory0_
191.where
192.category0_.id>?
193.andcategory0_.id<?
194.*级联设置参数
195.*/
196.@Test
197.publicvoidtestHQL_06(){
198.Sessionsession=sf.getCurrentSession();
199.session.beginTransaction();
200.
201.Queryq=session.createQuery("fromCategorycwherec.id>:minandc.id<:max")
202..setInteger("min",2)
203..setInteger("max",5);
204.List<Category>categorys=(List<Category>)q.list();
205.for(Categoryc:categorys){
206.System.out.println(c.getId()+""+c.getName());
207.}
208.session.getTransaction().commit();
209.}
210.
211./**
212.*查询结果进行分页
213.*SQL语句（MySQL分页语句）
214.*select
215.category0_.idasid0_,
216.category0_.nameasname0_
217.from
218.Categorycategory0_
219.orderby
220.category0_.namedesclimit?,
221.?
222.*/
223.@Test
224.publicvoidtestHQL_07(){
225.Sessionsession=sf.getCurrentSession();
226.session.beginTransaction();
227.
228.Queryq=session.createQuery("fromCategorycorderbyc.namedesc");
229.q.setMaxResults(5);
230.q.setFirstResult(3);
231.List<Category>categorys=(List<Category>)q.list();
232.for(Categoryc:categorys){
233.System.out.println(c.getId()+""+c.getName());
234.}
235.session.getTransaction().commit();
236.}
237.
238./**
239.*查询指定字段
240.*SQL语句
241.*select
242.category0_.idascol_0_0_,
243.category0_.nameascol_1_0_
244.from
245.Categorycategory0_
246.where
247.category0_.id>?
248.andcategory0_.id<?
249.*查询结果为数组
250.*@returnObject[]
251.*/
252.@Test
253.publicvoidtestHQL_08(){
254.Sessionsession=sf.getCurrentSession();
255.session.beginTransaction();
256.
257.Queryq=session.createQuery("selectc.id,c.namefromCategorycwherec.id>:minandc.id<:max")
258..setInteger("min",2)
259..setInteger("max",5);
260.List<Object[]>categorys=(List<Object[]>)q.list();
261.for(Object[]o:categorys){
262.System.out.println(o[0]+""+o[1]);
263.}
264.session.getTransaction().commit();
265.}
266.
267./**
268.*两张表关联查询
269.*设置fetchtype为lazy后将不会有第二条SQL语句
270.*SQL语句：
271.*1.fetch=FetchType.LAZY
272.*select
273.topic0_.idasid2_,
274.topic0_.categoryIdascategoryId2_,
275.topic0_.createDateascreateDate2_,
276.topic0_.titleastitle2_
277.from
278.topictopic0_
279.where
280.topic0_.categoryId=1
281.*2.fetch=FetchType.EAGER
282.*select
283.topic0_.idasid2_,
284.topic0_.categoryIdascategoryId2_,
285.topic0_.createDateascreateDate2_,
286.topic0_.titleastitle2_
287.from
288.topictopic0_
289.where
290.topic0_.categoryId=1
291.
292.*select
293.category0_.idasid0_0_,
294.category0_.nameasname0_0_
295.from
296.Categorycategory0_
297.where
298.category0_.id=?
299.*/
300.@Test
301.publicvoidtestHQL_09(){
302.Sessionsession=sf.getCurrentSession();
303.session.beginTransaction();
304.
305.Queryq=session.createQuery("fromTopictwheret.category.id=1");
306.List<Topic>topics=(List<Topic>)q.list();
307.for(Topict:topics){
308.System.out.println(t.getId()+""+t.getTitle());
309.}
310.session.getTransaction().commit();
311.}
312.
313./**
314.*多表关联查询
315.*fetchtype
316.*1LAZYSQL语句
317.*select
318.msg0_.idasid1_,
319.msg0_.contascont1_,
320.msg0_.topicIdastopicId1_
321.from
322.Msgmsg0_,
323.topictopic1_
324.where
325.msg0_.topicId=topic1_.id
326.andtopic1_.categoryId=1
327.*2EAGERSQL
328.*select
329.msg0_.idasid1_,
330.msg0_.contascont1_,
331.msg0_.topicIdastopicId1_
332.from
333.Msgmsg0_,
334.topictopic1_
335.where
336.msg0_.topicId=topic1_.id
337.andtopic1_.categoryId=1
338.
339.select
340.topic0_.idasid2_1_,
341.topic0_.categoryIdascategoryId2_1_,
342.topic0_.createDateascreateDate2_1_,
343.topic0_.titleastitle2_1_,
344.category1_.idasid0_0_,
345.category1_.nameasname0_0_
346.from
347.topictopic0_
348.leftouterjoin
349.Categorycategory1_
350.ontopic0_.categoryId=category1_.id
351.where
352.topic0_.id=?
353.*/
354.@Test
355.publicvoidtestHQL_10(){
356.Sessionsession=sf.getCurrentSession();
357.session.beginTransaction();
358.
359.Queryq=session.createQuery("fromMsgmwherem.topic.category.id=1");
360.List<Msg>msgs=(List<Msg>)q.list();
361.for(Msgm:msgs){
362.System.out.println(m.getId()+""+m.getCont());
363.}
364.session.getTransaction().commit();
365.}
366.
367./**
368.*自定义查询赋予自定义的POJO
369.*使用自定义ValueObject
370.*临时对象VO一定要有一个有参的构造方法
371.*/
372.@Test
373.publicvoidtestHQL_11(){
374.Sessionsession=sf.getCurrentSession();
375.session.beginTransaction();
376.
377.Queryq=session.createQuery("selectnewcom.jlee06.QL.MsgInfo(m.id,m.cont,m.topic.title,m.topic.category.name)fromMsgm");
378.List<MsgInfo>msgInfos=(List<MsgInfo>)q.list();
379.for(MsgInfomi:msgInfos){
380.System.out.println(mi.getId()+""+mi.getCategoryName()+""+mi.getCont()+""+mi.getTopicName());
381.}
382.session.getTransaction().commit();
383.}
384.
385./**
386.*手动测试leftrightjoin
387.*为什么不能直接写Category名，而必须写t.category
388.*因为有可能存在多个成员变量（同一个类），需要指明用哪一个成员变量作为连接条件
389.*/
390.@Test
391.publicvoidtestHQL_12(){
392.Sessionsession=sf.getCurrentSession();
393.session.beginTransaction();
394.
395.Queryq=session.createQuery("selectt.title,c.namefromTopictjoint.categoryc");
396.List<Object[]>os=(List<Object[]>)q.list();
397.for(Object[]o:os){
398.System.out.println(o[0]+""+o[1]);
399.}
400.session.getTransaction().commit();
401.}
402.
403./**
404.*学习使用uniqueResult
405.*select
406.msg0_.idasid1_,
407.msg0_.contascont1_,
408.msg0_.topicIdastopicId1_
409.from
410.Msgmsg0_
411.where
412.msg0_.id=?
413.*返回单独的唯一的结果
414.*/
415.@Test
416.publicvoidtestHQL_13(){
417.Sessionsession=sf.getCurrentSession();
418.session.beginTransaction();
419.
420.Queryq=session.createQuery("fromMsgmwherem=:msg");
421.Msgm=newMsg();
422.m.setId(1);
423.q.setParameter("msg",m);
424.
425.MsgmResult=(Msg)q.uniqueResult();
426.System.out.println(mResult.getId()+""+mResult.getCont());
427.
428.session.getTransaction().commit();
429.}
430.
431./**
432.*集合函数Count
433.*SQL语句
434.*select
435.count(*)ascol_0_0_
436.from
437.Msgmsg0_
438.*/
439.@Test
440.publicvoidtestHQL_14(){
441.Sessionsession=sf.getCurrentSession();
442.session.beginTransaction();
443.
444.Queryq=session.createQuery("selectcount(*)fromMsgm");
445.longcount=(Long)q.uniqueResult();
446.System.out.println(count);
447.
448.session.getTransaction().commit();
449.}
450.
451./**
452.*集合函数maxminavgsum
453.*SQL语句
454.*select
455.max(msg0_.id)ascol_0_0_,
456.min(msg0_.id)ascol_1_0_,
457.avg(msg0_.id)ascol_2_0_,
458.sum(msg0_.id)ascol_3_0_
459.from
460.Msgmsg0_
461.*/
462.@Test
463.publicvoidtestHQL_15(){
464.Sessionsession=sf.getCurrentSession();
465.session.beginTransaction();
466.
467.Queryq=session.createQuery("selectmax(m.id),min(m.id),avg(m.id),sum(m.id)fromMsgm");
468.Object[]o=(Object[])q.uniqueResult();
469.System.out.println(o[0]+""+o[1]+""+o[2]+""+o[3]);
470.session.getTransaction().commit();
471.}
472.
473./**
474.*betweenand
475.*SQL语句
476.*select
477.msg0_.idasid1_,
478.msg0_.contascont1_,
479.msg0_.topicIdastopicId1_
480.from
481.Msgmsg0_
482.where
483.msg0_.idbetween3and8
484.*/
485.@Test
486.publicvoidtestHQL_16(){
487.Sessionsession=sf.getCurrentSession();
488.session.beginTransaction();
489.
490.Queryq=session.createQuery("fromMsgmWherem.idbetween3and8");
491.for(Objecto:q.list()){
492.Msgm=(Msg)o;
493.System.out.println(m.getId()+""+m.getCont());
494.}
495.session.getTransaction().commit();
496.}
497.
498./**
499.*in
500.*SQL语句
501.*select
502.msg0_.idasid1_,
503.msg0_.contascont1_,
504.msg0_.topicIdastopicId1_
505.from
506.Msgmsg0_
507.where
508.msg0_.idin(
509.3,4,5,6
510.)
511.*/
512.@Test
513.publicvoidtestHQL_17(){
514.Sessionsession=sf.getCurrentSession();
515.session.beginTransaction();
516.
517.Queryq=session.createQuery("fromMsgmWherem.idin(3,4,5,6)");
518.for(Objecto:q.list()){
519.Msgm=(Msg)o;
520.System.out.println(m.getId()+""+m.getCont());
521.}
522.session.getTransaction().commit();
523.}
524.
525./**
526.*isnull和isnotnull
527.*select
528.msg0_.idasid1_,
529.msg0_.contascont1_,
530.msg0_.topicIdastopicId1_
531.from
532.Msgmsg0_
533.where
534.msg0_.contisnotnull
535.*/
536.@Test
537.publicvoidtestHQL_18(){
538.Sessionsession=sf.getCurrentSession();
539.session.beginTransaction();
540.
541.Queryq=session.createQuery("fromMsgmWherem.contisnotnull");
542.for(Objecto:q.list()){
543.Msgm=(Msg)o;
544.System.out.println(m.getId()+""+m.getCont());
545.}
546.session.getTransaction().commit();
547.}
548.
549./**
550.*isempty
551.*SQL语句
552.*
553.*/
554.@Test
555.publicvoidtestHQL_19(){
556.Sessionsession=sf.getCurrentSession();
557.session.beginTransaction();
558.
559.Queryq=session.createQuery("fromTopictwheret.msgsisempty");
560.for(Objecto:q.list()){
561.Topict=(Topic)o;
562.System.out.println(t.getId()+""+t.getTitle());
563.}
564.session.getTransaction().commit();
565.}
566.
567./**
568.*like的使用
569.*SQL语句
570.*select
571.topic0_.idasid2_,
572.topic0_.categoryIdascategoryId2_,
573.topic0_.createDateascreateDate2_,
574.topic0_.titleastitle2_
575.from
576.topictopic0_
577.where
578.topic0_.titlelike'%5'
579.*/
580.@Test
581.publicvoidtestHQL_20(){
582.Sessionsession=sf.getCurrentSession();
583.session.beginTransaction();
584.
585.Queryq=session.createQuery("fromTopictwheret.titlelike'%5'");
586.for(Objecto:q.list()){
587.Topict=(Topic)o;
588.System.out.println(t.getId()+""+t.getTitle());
589.}
590.session.getTransaction().commit();
591.}
592.
593./**
594.*函数的使用1
595.*lower,upper,trim,concat,length
596.*SQL语句
597.*select
598.lower(topic0_.title)ascol_0_0_,
599.upper(topic0_.title)ascol_1_0_,
600.trim(topic0_.title)ascol_2_0_,
601.concat(topic0_.title,
602.'*****')ascol_3_0_,
603.length(topic0_.title)ascol_4_0_
604.from
605.topictopic0_
606.*/
607.@Test
608.publicvoidtestHQL_21(){
609.Sessionsession=sf.getCurrentSession();
610.session.beginTransaction();
611.
612.Queryq=session.createQuery("select"+
613."lower(t.title),"+
614."upper(t.title),"+
615."trim(t.title),"+
616."concat(t.title,'*****'),"+
617."length(t.title)"+
618."fromTopict");
619.for(Objectos:q.list()){
620.Object[]o=(Object[])os;
621.System.out.println(o[0]+""+o[1]+""+o[2]+""+o[3]+""+o[4]);
622.}
623.session.getTransaction().commit();
624.}
625.
626./**
627.*函数的使用2
628.*abs,sqrt,mod
629.*SQL语句
630.*select
631.abs(topic0_.id)ascol_0_0_,
632.sqrt(topic0_.id)ascol_1_0_,
633.mod(topic0_.id,
634.2)ascol_2_0_
635.from
636.topictopic0_
637.*/
638.@Test
639.publicvoidtestHQL_22(){
640.Sessionsession=sf.getCurrentSession();
641.session.beginTransaction();
642.
643.Queryq=session.createQuery("select"+
644."abs(t.id),"+
645."sqrt(t.id),"+
646."mod(t.id,2)"+
647."fromTopict");
648.for(Objectos:q.list()){
649.Object[]o=(Object[])os;
650.System.out.println(o[0]+""+o[1]+""+o[2]);
651.}
652.session.getTransaction().commit();
653.}
654.
655./**
656.*时间函数的应用
657.*SQL语句
658.*select
659.current_dateascol_0_0_,
660.current_timeascol_1_0_,
661.current_timestampascol_2_0_,
662.topic0_.idascol_3_0_
663.from
664.topictopic0_
665.*/
666.@Test
667.publicvoidtestHQL_24(){
668.Sessionsession=sf.getCurrentSession();
669.session.beginTransaction();
670.
671.Queryq=session.createQuery("selectcurrent_date,current_time,current_timestamp,t.idfromTopict");
672.for(Objectos:q.list()){
673.Object[]o=(Object[])os;
674.System.out.println(o[0]+""+o[1]+""+o[2]+""+o[3]);
675.}
676.session.getTransaction().commit();
677.}
678.
679./**
680.*日期的比较
681.*SQL语句
682.*select
683.topic0_.idasid2_,
684.topic0_.categoryIdascategoryId2_,
685.topic0_.createDateascreateDate2_,
686.topic0_.titleastitle2_
687.from
688.topictopic0_
689.where
690.topic0_.createDate<?
691.*/
692.@Test
693.publicvoidtestHQL_25(){
694.Sessionsession=sf.getCurrentSession();
695.session.beginTransaction();
696.
697.Queryq=session.createQuery("fromTopictWheret.createDate<:date");
698.q.setParameter("date",newDate());
699.for(Objecto:q.list()){
700.Topict=(Topic)o;
701.System.out.println(t.getId()+""+t.getCreateDate());
702.}
703.session.getTransaction().commit();
704.}
705.
706./**
707.*groupby语句
708.*SQL语句
709.*select
710.topic0_.titleascol_0_0_,
711.count(*)ascol_1_0_
712.from
713.topictopic0_
714.groupby
715.topic0_.title
716.*/
717.@Test
718.publicvoidtestHQL_26(){
719.Sessionsession=sf.getCurrentSession();
720.session.beginTransaction();
721.
722.Queryq=session.createQuery("selectt.title,count(*)fromTopictgroupbyt.title");
723.for(Objecto:q.list()){
724.Object[]arr=(Object[])o;
725.System.out.println(arr[0]+""+arr[1]);
726.}
727.session.getTransaction().commit();
728.}
729.
730./**
731.*groupbyhaving语句
732.*groupby里面出现的字段必须出现在select里面
733.*having中的条件必须是组合函数
734.*SQL语句
735.*select
736.topic0_.titleascol_0_0_,
737.count(*)ascol_1_0_
738.from
739.topictopic0_
740.groupby
741.topic0_.title
742.having
743.count(*)>0
744.*/
745.@Test
746.publicvoidtestHQL_27(){
747.Sessionsession=sf.getCurrentSession();
748.session.beginTransaction();
749.
750.Queryq=session.createQuery("selectt.title,count(*)fromTopictgroupbyt.titlehavingcount(*)>0");
751.for(Objecto:q.list()){
752.Object[]arr=(Object[])o;
753.System.out.println(arr[0]+""+arr[1]);
754.}
755.session.getTransaction().commit();
756.}
757.
758./**
759.*子查询
760.*SQL语句
761.*select
762.topic0_.idasid2_,
763.topic0_.categoryIdascategoryId2_,
764.topic0_.createDateascreateDate2_,
765.topic0_.titleastitle2_
766.from
767.topictopic0_
768.where
769.topic0_.id<(
770.select
771.avg(topic1_.id)
772.from
773.topictopic1_
774.)
775.*/
776.@Test
777.publicvoidtestHQL_28(){
778.Sessionsession=sf.getCurrentSession();
779.session.beginTransaction();
780.
781.Queryq=session.createQuery("fromTopictwheret.id<(selectavg(t.id)fromTopict)");
782.for(Objecto:q.list()){
783.Topict=(Topic)o;
784.System.out.println(t.getId()+""+t.getCreateDate());
785.}
786.session.getTransaction().commit();
787.}
788.
789./**
790.*All函数的使用
791.*SQL语句
792.*select
793.topic0_.idasid2_,
794.topic0_.categoryIdascategoryId2_,
795.topic0_.createDateascreateDate2_,
796.topic0_.titleastitle2_
797.from
798.topictopic0_
799.where
800.topic0_.id<all(
801.select
802.topic1_.id
803.from
804.topictopic1_
805.where
806.mod(topic1_.id,2)=0
807.)
808.
809.*/
810.@Test
811.publicvoidtestHQL_29(){
812.Sessionsession=sf.getCurrentSession();
813.session.beginTransaction();
814.
815.Queryq=session.createQuery("fromTopictwheret.id<ALL(selectt.idfromTopictwheremod(t.id,2)=0)");
816.for(Objecto:q.list()){
817.Topict=(Topic)o;
818.System.out.println(t.getId()+""+t.getCreateDate());
819.}
820.session.getTransaction().commit();
821.}
822.
823./**
824.*exists和notexists
825.*说明：用in可以实现exists的功能
826.*但是exists的效率高
827.*SQL语句
828.*select
829.topic0_.idasid2_,
830.topic0_.categoryIdascategoryId2_,
831.topic0_.createDateascreateDate2_,
832.topic0_.titleastitle2_
833.from
834.topictopic0_
835.where
836.not(exists(select
837.msg1_.id
838.from
839.Msgmsg1_
840.where
841.msg1_.topicId=topic0_.id))
842.*/
843.@Test
844.publicvoidtestHQL_30(){
845.Sessionsession=sf.getCurrentSession();
846.session.beginTransaction();
847.
848.Queryq=session.createQuery("fromTopictwherenotexists(selectm.idfromMsgmwherem.topic.id=t.id)");
849.//Queryq=session.createQuery("fromTopictwhereexists(selectm.idfromMsgmwherem.topic.id=t.id)");
850.for(Objecto:q.list()){
851.Topict=(Topic)o;
852.System.out.println(t.getId()+""+t.getCreateDate());
853.}
854.session.getTransaction().commit();
855.}
856.
857./**
858.*updatedelete
859.*SQL语句
860.*update
861.topic
862.set
863.title=upper(title)
864.*/
865.@Test
866.publicvoidtestHQL_31(){
867.Sessionsession=sf.getCurrentSession();
868.session.beginTransaction();
869.
870.Queryq=session.createQuery("updateTopictsett.title=upper(t.title)");
871.q.executeUpdate();
872.
873.q=session.createQuery("fromTopic");
874.for(Objecto:q.list()){
875.Topict=(Topic)o;
876.System.out.println(t.getTitle());
877.}
878.
879.session.createQuery("updateTopictsett.title=lower(t.title)")
880..executeUpdate();
881.session.getTransaction().commit();
882.}
883.
884./**
885.*命名查询
886.*实现按名字查找的自定义查询
887.*在Entity上面自定义好SQL语句
888.*SQL语句
889.*select
890.topic0_.idasid2_,
891.topic0_.categoryIdascategoryId2_,
892.topic0_.createDateascreateDate2_,
893.topic0_.titleastitle2_
894.from
895.topictopic0_
896.where
897.topic0_.id=?
898.*/
899.@Test
900.publicvoidtestHQL_32(){
901.Sessionsession=sf.getCurrentSession();
902.session.beginTransaction();
903.
904.//Queryq=session.getNamedQuery("Topic.selectTopic");
905.//q.setParameter("id",5);
906.//Topict=(Topic)q.uniqueResult();
907.//System.out.println(t.getId()+""+t.getCreateDate());
908.
909.Queryq=session.getNamedQuery("Topic.conditionTopic");
910.q.setParameter("title","%");
911.for(Objecto:q.list()){
912.Topict=(Topic)o;
913.System.out.println(t.getId()+""+t.getCreateDate());
914.}
915.
916.//Queryq=session.getNamedQuery("native_sql_page");
917.//for(Objecto:q.list()){
918.//Topict=(Topic)o;
919.//System.out.println(t.getId()+""+t.getCreateDate());
920.//}
921.
922.session.getTransaction().commit();
923.}
924.
925./**
926.*原生SQL查询
927.*Hibernate使用SQL语句
928.*SQL语句
929.*select*
930.from
931.categorylimit2,6
932.*/
933.@Test
934.publicvoidtestHQL_33(){
935.Sessionsession=sf.getCurrentSession();
936.session.getTransaction().begin();
937.
938.SQLQueryq=session.createSQLQuery("select*fromcategorylimit2,6").addEntity(Category.class);
939.List<Category>categorys=(List<Category>)q.list();
940.for(Categoryc:categorys){
941.System.out.println(c.getId()+""+c.getName());
942.}
943.
944.session.getTransaction().commit();
945.}
946.
947.
948./**
949.*实现数据分也显示
950.*SQL语句
951.*select
952.topic0_.idasid2_,
953.topic0_.categoryIdascategoryId2_,
954.topic0_.createDateascreateDate2_,
955.topic0_.titleastitle2_
956.from
957.topictopic0_
958.where
959.not(exists(select
960.msg1_.id
961.from
962.Msgmsg1_
963.where
964.msg1_.topicId=topic0_.id))limit?,?
965.*/
966.@Test
967.publicvoidtestHQL_34(){
968.Sessionsession=sf.getCurrentSession();
969.session.beginTransaction();
970.
971.Queryq=session.createQuery("fromTopictwherenotexists(selectm.idfromMsgmwherem.topic.id=t.id)");
972.//Queryq=session.createQuery("fromTopictwhereexists(selectm.idfromMsgmwherem.topic.id=t.id)");
973.q.setFirstResult(10).setMaxResults(20);
974.for(Objecto:q.list()){
975.Topict=(Topic)o;
976.System.out.println(t.getId()+""+t.getCreateDate());
977.}
978.session.getTransaction().commit();
979.}
980.
981.
982.@Test
983.publicvoidtestSchemaExport(){
984.newSchemaExport(newAnnotationConfiguration().configure()).create(false,true);
985.}
986.
987.}
hibernate.cfg.xml文件：
Xml代码1.<?xmlversion='1.0'encoding='utf-8'?>
2.<!DOCTYPEhibernate-configurationPUBLIC
3."-//Hibernate/HibernateConfigurationDTD3.0//EN"
4."http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
5.
6.<hibernate-configuration>
7.
8.<session-factory>
9.
10.<!--Databaseconnectionsettings-->
11.<propertyname="connection.driver_class">com.mysql.jdbc.Driver</property>
12.<propertyname="connection.url">jdbc:mysql://localhost/hibernate</property>
13.<propertyname="connection.username">root</property>
14.<propertyname="connection.password">root</property>
15.
16.<!--JDBCconnectionpool(usethebuilt-in)-->
17.<propertyname="connection.pool_size">100</property>
18.
19.<!--SQLdialect-->
20.<propertyname="dialect">org.hibernate.dialect.MySQLDialect</property>
21.
22.<!--EnableHibernate'sautomaticsessioncontextmanagement-->
23.<propertyname="current_session_context_class">thread</property>
24.
25.<!--Disablethesecond-levelcache-->
26.<propertyname="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
27.
28.<!--EchoallexecutedSQLtostdout-->
29.<propertyname="show_sql">true</property>
30.
31.<propertyname="format_sql">true</property>
32.
33.<!--Dropandre-createthedatabaseschemaonstartup-->
34.<!--<propertyname="hbm2ddl.auto">create</property>-->
35.
36.
37.<mappingclass="com.jlee06.QL.Category"/>
38.<mappingclass="com.jlee06.QL.Msg"/>
39.<mappingclass="com.jlee06.QL.Topic"/>
40.
41.
42.</session-factory>
43.
44.</hibernate-configuration>