Hibernate 运用 Annotation 6（各种查询语句）

2012-09-03

Hibernate 使用 Annotation 6（各种查询语句）Java代码 1.package com.jlee06.QL 2. 3.import javax.persis

Hibernate 使用 Annotation 6（各种查询语句）
Java代码 1.package com.jlee06.QL;
2.
3.import javax.persistence.Column;
4.import javax.persistence.Entity;
5.import javax.persistence.GeneratedValue;
6.import javax.persistence.GenerationType;
7.import javax.persistence.Id;
8.import javax.persistence.Table;
9.
10./**
11. * @author JLee
12. * 板块
13. */
14.@Entity
15.@Table(name="Category")
16.public class Category {
17.
18. private int id ;
19. private String name ;
20.
21. @Id
22. @GeneratedValue(strategy=GenerationType.AUTO)
23. public int getId() {
24. return id;
25. }
26. public void setId(int id) {
27. this.id = id;
28. }
29.
30. @Column(name="name")
31. public String getName() {
32. return name;
33. }
34. public void setName(String name) {
35. this.name = name;
36. }
37.
38.}

Msg.java代码：
Java代码 1.package com.jlee06.QL;
2.
3.import javax.persistence.CascadeType;
4.import javax.persistence.Column;
5.import javax.persistence.Entity;
6.import javax.persistence.FetchType;
7.import javax.persistence.GeneratedValue;
8.import javax.persistence.GenerationType;
9.import javax.persistence.Id;
10.import javax.persistence.JoinColumn;
11.import javax.persistence.ManyToOne;
12.import javax.persistence.Table;
13.
14.@Entity
15.@Table(name="Msg")
16.public class Msg {
17.
18. private int id ;
19. private String cont ;
20. private Topic topic ;
21.
22. @Id
23. @GeneratedValue(strategy=GenerationType.AUTO)
24. public int getId() {
25. return id;
26. }
27. public void setId(int id) {
28. this.id = id;
29. }
30.
31. @Column(name="cont" , length=500)
32. public String getCont() {
33. return cont;
34. }
35. public void setCont(String cont) {
36. this.cont = cont;
37. }
38.
39. @ManyToOne(fetch=FetchType.LAZY)
40. @JoinColumn(name="topicId")
41. public Topic getTopic() {
42. return topic;
43. }
44. public void setTopic(Topic topic) {
45. this.topic = topic;
46. }
47.
48.}

Topic.java代码：
Java代码 1.package com.jlee06.QL;
2.
3.import java.util.Date;
4.
5.import javax.persistence.Column;
6.import javax.persistence.Entity;
7.import javax.persistence.FetchType;
8.import javax.persistence.GeneratedValue;
9.import javax.persistence.GenerationType;
10.import javax.persistence.Id;
11.import javax.persistence.JoinColumn;
12.import javax.persistence.ManyToOne;
13.import javax.persistence.NamedQueries;
14.import javax.persistence.NamedQuery;
15.import javax.persistence.Table;
16.import javax.persistence.Temporal;
17.import javax.persistence.TemporalType;
18.
19./**
20. * @author JLee
21. * 主题
22. */
23.@Entity
24.@Table(name="topic")
25.@NamedQueries({
26. @NamedQuery(name="Topic.selectTopic" , query="from Topic t where t.id = :id ") ,
27. @NamedQuery(name="Topic.conditionTopic" , query="from Topic t where t.title like :title ")
28.})
29.//hibernate3.3.2尚未支持
30.//@NamedNativeQueries({
31.// @NamedNativeQuery(name="native_sql_page" , query="select * from topic limit 2,5")
32.//})
33.public class Topic {
34.
35. private int id ;
36. private String title ;
37. private Date createDate ;
38. public Date getCreateDate() {
39. return createDate;
40. }
41.
42. @Column(name="createDate")
43. @Temporal(TemporalType.DATE)
44. public void setCreateDate(Date createDate) {
45. this.createDate = createDate;
46. }
47. private Category category ;
48.
49. @Id
50. @GeneratedValue(strategy=GenerationType.AUTO)
51. public int getId() {
52. return id;
53. }
54. public void setId(int id) {
55. this.id = id;
56. }
57.
58. @Column(name="title" , length=32)
59. public String getTitle() {
60. return title;
61. }
62. public void setTitle(String title) {
63. this.title = title;
64. }
65.
66. @ManyToOne(fetch=FetchType.LAZY)
67. @JoinColumn(name="categoryId")
68. public Category getCategory() {
69. return category;
70. }
71. public void setCategory(Category category) {
72. this.category = category;
73. }
74.
75.}

MsgInfo.java代码：
Java代码 1.package com.jlee06.QL;
2.
3./**
4. * VO Value Object
5. * @author JLee
6. * 查询使用的值对象
7. */
8.public class MsgInfo {
9.
10. private int id ;
11. private String cont ;
12. private String topicName ;
13. private String categoryName ;
14.
15. public MsgInfo(int id, String cont, String topicName, String categoryName) {
16. super();
17. this.id = id;
18. this.cont = cont;
19. this.topicName = topicName;
20. this.categoryName = categoryName;
21. }
22.
23. public int getId() {
24. return id;
25. }
26.
27. public void setId(int id) {
28. this.id = id;
29. }
30.
31. public String getCont() {
32. return cont;
33. }
34.
35. public void setCont(String cont) {
36. this.cont = cont;
37. }
38.
39. public String getTopicName() {
40. return topicName;
41. }
42.
43. public void setTopicName(String topicName) {
44. this.topicName = topicName;
45. }
46.
47. public String getCategoryName() {
48. return categoryName;
49. }
50.
51. public void setCategoryName(String categoryName) {
52. this.categoryName = categoryName;
53. }
54.
55.}

DataBase.java代码：
Java代码 1.package com.jlee06.QL;
2.
3.import java.util.Date;
4.import java.util.List;
5.
6.import org.hibernate.Query;
7.import org.hibernate.SQLQuery;
8.import org.hibernate.Session;
9.import org.hibernate.SessionFactory;
10.import org.hibernate.cfg.AnnotationConfiguration;
11.import org.hibernate.tool.hbm2ddl.SchemaExport;
12.import org.junit.AfterClass;
13.import org.junit.BeforeClass;
14.import org.junit.Test;
15.
16.public class DataBase {
17.
18. private static SessionFactory sf ;
19. @BeforeClass
20. public static void beforeClass(){
21. sf = new AnnotationConfiguration().configure().buildSessionFactory() ;
22. }
23.
24. //@AfterClass
25. //public void afterClass(){
26. // sf.close() ;
27. //}
28.
29. @Test
30. public void testSave() {
31. Session session = sf.getCurrentSession() ;
32. session.beginTransaction();
33.
34. for(int i=0; i<10; i++) {
35. Category c = new Category();
36. c.setName("c" + i);
37. session.save(c);
38. }
39.
40. for(int i=0; i<10; i++) {
41. Category c = new Category();
42. c.setId(1);
43. Topic t = new Topic();
44. t.setCategory(c);
45. t.setTitle("t" + i);
46. t.setCreateDate(new Date());
47. session.save(t);
48. }
49.
50. for(int i=0; i<10; i++) {
51. Topic t = new Topic();
52. t.setId(1);
53. Msg m = new Msg();
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_.id as id0_,
67. category0_.name as name0_
68. from
69. Category category0_
70. */
71. @Test
72. public void testHQL_01(){
73. Session session = sf.openSession() ;
74. session.beginTransaction() ;
75.
76. Query q = session.createQuery("from Category") ;
77. List<Category> categorys = (List<Category>)q.list() ;
78. for(Category c : 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_.id as id0_,
90. category0_.name as name0_
91. from
92. Category category0_
93. where
94. category0_.name>'c5'
95. */
96. @Test
97. public void testHQL_02(){
98. Session session = sf.getCurrentSession() ;
99. session.beginTransaction() ;
100.
101. Query q = session.createQuery("from Category c where c.name > 'c5' ") ;
102. List<Category> categorys = (List<Category>)q.list() ;
103. for(Category c : categorys){
104. System.out.println(c.getName());
105. }
106. session.getTransaction().commit() ;
107. }
108.
109. /**
110. * 使用 order by 排序语句
111. * SQL 语句
112. * select
113. category0_.id as id0_,
114. category0_.name as name0_
115. from
116. Category category0_
117. order by
118. category0_.name desc
119. */
120. @Test
121. public void testHQL_03(){
122. Session session = sf.getCurrentSession() ;
123. session.beginTransaction() ;
124.
125. Query q = session.createQuery("from Category c order by c.name desc ") ;
126. List<Category> categorys = (List<Category>)q.list() ;
127. for(Category c : categorys){
128. System.out.println(c.getName());
129. }
130. session.getTransaction().commit() ;
131. }
132.
133. /**
134. * 使用 distinct
135. * SQL语句
136. * select
137. distinct category0_.id as id0_,
138. category0_.name as name0_
139. from
140. Category category0_
141. order by
142. category0_.name desc
143. */
144. @Test
145. public void testHQL_04(){
146. Session session = sf.getCurrentSession() ;
147. session.beginTransaction() ;
148.
149. Query q = session.createQuery("select distinct c from Category c order by c.name desc ") ;
150. List<Category> categorys = (List<Category>)q.list() ;
151. for(Category c : categorys){
152. System.out.println(c.getName());
153. }
154. session.getTransaction().commit() ;
155. }
156.
157. /**
158. * 在SQL语句中设置参数1
159. * select
160. category0_.id as id0_,
161. category0_.name as name0_
162. from
163. Category category0_
164. where
165. category0_.id>?
166. and category0_.id<?
167. * 分开设置参数
168. */
169. @Test
170. public void testHQL_05(){
171. Session session = sf.getCurrentSession() ;
172. session.beginTransaction() ;
173.
174. Query q = session.createQuery("from Category c where c.id > :min and c.id < :max ") ;
175. q.setParameter("min", 2) ;
176. q.setParameter("max", 5) ;
177. List<Category> categorys = (List<Category>)q.list() ;
178. for(Category c : categorys){
179. System.out.println(c.getId()+" "+c.getName());
180. }
181. session.getTransaction().commit() ;
182. }
183.
184. /**
185. * 在SQL语句中设置参数2
186. * select
187. category0_.id as id0_,
188. category0_.name as name0_
189. from
190. Category category0_
191. where
192. category0_.id>?
193. and category0_.id<?
194. * 级联设置参数
195. */
196. @Test
197. public void testHQL_06(){
198. Session session = sf.getCurrentSession() ;
199. session.beginTransaction() ;
200.
201. Query q = session.createQuery("from Category c where c.id > :min and c.id < :max ")
202. .setInteger("min", 2)
203. .setInteger("max", 5) ;
204. List<Category> categorys = (List<Category>)q.list() ;
205. for(Category c : 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_.id as id0_,
216. category0_.name as name0_
217. from
218. Category category0_
219. order by
220. category0_.name desc limit ?,
221. ?
222. */
223. @Test
224. public void testHQL_07(){
225. Session session = sf.getCurrentSession() ;
226. session.beginTransaction() ;
227.
228. Query q = session.createQuery("from Category c order by c.name desc ") ;
229. q.setMaxResults(5) ;
230. q.setFirstResult(3) ;
231. List<Category> categorys = (List<Category>)q.list() ;
232. for(Category c : categorys){
233. System.out.println(c.getId()+" "+c.getName());
234. }
235. session.getTransaction().commit() ;
236. }
237.
238. /**
239. * 查询指定字段
240. * SQL 语句
241. * select
242. category0_.id as col_0_0_,
243. category0_.name as col_1_0_
244. from
245. Category category0_
246. where
247. category0_.id>?
248. and category0_.id<?
249. * 查询结果为数组
250. * @return Object[]
251. */
252. @Test
253. public void testHQL_08(){
254. Session session = sf.getCurrentSession() ;
255. session.beginTransaction() ;
256.
257. Query q = session.createQuery("select c.id , c.name from Category c where c.id > :min and c.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. * 设置 fetch type 为 lazy 后将不会有第二条SQL语句
270. * SQL语句：
271. * 1.fetch=FetchType.LAZY
272. * select
273. topic0_.id as id2_,
274. topic0_.categoryId as categoryId2_,
275. topic0_.createDate as createDate2_,
276. topic0_.title as title2_
277. from
278. topic topic0_
279. where
280. topic0_.categoryId=1
281. *2.fetch=FetchType.EAGER
282. * select
283. topic0_.id as id2_,
284. topic0_.categoryId as categoryId2_,
285. topic0_.createDate as createDate2_,
286. topic0_.title as title2_
287. from
288. topic topic0_
289. where
290. topic0_.categoryId=1
291.
292. * select
293. category0_.id as id0_0_,
294. category0_.name as name0_0_
295. from
296. Category category0_
297. where
298. category0_.id=?
299. */
300. @Test
301. public void testHQL_09(){
302. Session session = sf.getCurrentSession() ;
303. session.beginTransaction() ;
304.
305. Query q = session.createQuery("from Topic t where t.category .id = 1 ") ;
306. List<Topic> topics = (List<Topic>)q.list() ;
307. for(Topic t : topics){
308. System.out.println(t.getId()+" "+t.getTitle());
309. }
310. session.getTransaction().commit() ;
311. }
312.
313. /**
314. * 多表关联查询
315. * fetch type
316. * 1 LAZY SQL语句
317. * select
318. msg0_.id as id1_,
319. msg0_.cont as cont1_,
320. msg0_.topicId as topicId1_
321. from
322. Msg msg0_,
323. topic topic1_
324. where
325. msg0_.topicId=topic1_.id
326. and topic1_.categoryId=1
327. * 2 EAGER SQL
328. * select
329. msg0_.id as id1_,
330. msg0_.cont as cont1_,
331. msg0_.topicId as topicId1_
332. from
333. Msg msg0_,
334. topic topic1_
335. where
336. msg0_.topicId=topic1_.id
337. and topic1_.categoryId=1
338.
339. select
340. topic0_.id as id2_1_,
341. topic0_.categoryId as categoryId2_1_,
342. topic0_.createDate as createDate2_1_,
343. topic0_.title as title2_1_,
344. category1_.id as id0_0_,
345. category1_.name as name0_0_
346. from
347. topic topic0_
348. left outer join
349. Category category1_
350. on topic0_.categoryId=category1_.id
351. where
352. topic0_.id=?
353. */
354. @Test
355. public void testHQL_10(){
356. Session session = sf.getCurrentSession() ;
357. session.beginTransaction() ;
358.
359. Query q = session.createQuery("from Msg m where m.topic.category .id = 1 ") ;
360. List<Msg> msgs = (List<Msg>)q.list() ;
361. for(Msg m : msgs){
362. System.out.println(m.getId()+" "+m.getCont());
363. }
364. session.getTransaction().commit() ;
365. }
366.
367. /**
368. * 自定义查询赋予自定义的POJO
369. * 使用自定义 Value Object
370. * 临时对象VO一定要有一个有参的构造方法
371. */
372. @Test
373. public void testHQL_11(){
374. Session session = sf.getCurrentSession() ;
375. session.beginTransaction() ;
376.
377. Query q = session.createQuery("select new com.jlee06.QL.MsgInfo (m.id , m.cont , m.topic.title , m.topic.category.name ) from Msg m ") ;
378. List<MsgInfo> msgInfos = (List<MsgInfo>)q.list() ;
379. for(MsgInfo mi : msgInfos){
380. System.out.println(mi.getId()+" "+mi.getCategoryName()+" "+mi.getCont()+" "+mi.getTopicName());
381. }
382. session.getTransaction().commit() ;
383. }
384.
385. /**
386. * 手动测试left right join
387. * 为什么不能直接写Category名，而必须写t.category
388. * 因为有可能存在多个成员变量（同一个类），需要指明用哪一个成员变量作为连接条件
389. */
390. @Test
391. public void testHQL_12(){
392. Session session = sf.getCurrentSession() ;
393. session.beginTransaction() ;
394.
395. Query q = session.createQuery("select t.title , c.name from Topic t join t.category c ") ;
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_.id as id1_,
407. msg0_.cont as cont1_,
408. msg0_.topicId as topicId1_
409. from
410. Msg msg0_
411. where
412. msg0_.id=?
413. * 返回单独的唯一的结果
414. */
415. @Test
416. public void testHQL_13(){
417. Session session = sf.getCurrentSession() ;
418. session.beginTransaction() ;
419.
420. Query q = session.createQuery("from Msg m where m= :msg ") ;
421. Msg m = new Msg() ;
422. m.setId(1) ;
423. q.setParameter("msg", m) ;
424.
425. Msg mResult = (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(*) as col_0_0_
436. from
437. Msg msg0_
438. */
439. @Test
440. public void testHQL_14(){
441. Session session = sf.getCurrentSession() ;
442. session.beginTransaction() ;
443.
444. Query q = session.createQuery("select count(*) from Msg m ") ;
445. long count = (Long)q.uniqueResult() ;
446. System.out.println(count);
447.
448. session.getTransaction().commit() ;
449. }
450.
451. /**
452. * 集合函数 max min avg sum
453. * SQL语句
454. * select
455. max(msg0_.id) as col_0_0_,
456. min(msg0_.id) as col_1_0_,
457. avg(msg0_.id) as col_2_0_,
458. sum(msg0_.id) as col_3_0_
459. from
460. Msg msg0_
461. */
462. @Test
463. public void testHQL_15(){
464. Session session = sf.getCurrentSession() ;
465. session.beginTransaction() ;
466.
467. Query q = session.createQuery("select max(m.id) , min(m.id) , avg(m.id) , sum(m.id) from Msg m ") ;
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. * between and
475. * SQL语句
476. * select
477. msg0_.id as id1_,
478. msg0_.cont as cont1_,
479. msg0_.topicId as topicId1_
480. from
481. Msg msg0_
482. where
483. msg0_.id between 3 and 8
484. */
485. @Test
486. public void testHQL_16(){
487. Session session = sf.getCurrentSession() ;
488. session.beginTransaction() ;
489.
490. Query q = session.createQuery("from Msg m Where m.id between 3 and 8 ") ;
491. for(Object o : q.list() ){
492. Msg m = (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_.id as id1_,
503. msg0_.cont as cont1_,
504. msg0_.topicId as topicId1_
505. from
506. Msg msg0_
507. where
508. msg0_.id in (
509. 3 , 4 , 5 , 6
510. )
511. */
512. @Test
513. public void testHQL_17(){
514. Session session = sf.getCurrentSession() ;
515. session.beginTransaction() ;
516.
517. Query q = session.createQuery("from Msg m Where m.id in (3,4,5,6) ") ;
518. for(Object o : q.list() ){
519. Msg m = (Msg)o ;
520. System.out.println(m.getId()+" "+m.getCont());
521. }
522. session.getTransaction().commit() ;
523. }
524.
525. /**
526. * is null 和 is not null
527. * select
528. msg0_.id as id1_,
529. msg0_.cont as cont1_,
530. msg0_.topicId as topicId1_
531. from
532. Msg msg0_
533. where
534. msg0_.cont is not null
535. */
536. @Test
537. public void testHQL_18(){
538. Session session = sf.getCurrentSession() ;
539. session.beginTransaction() ;
540.
541. Query q = session.createQuery("from Msg m Where m.cont is not null ") ;
542. for(Object o : q.list() ){
543. Msg m = (Msg)o ;
544. System.out.println(m.getId()+" "+m.getCont());
545. }
546. session.getTransaction().commit() ;
547. }
548.
549. /**
550. * is empty
551. * SQL 语句
552. *
553. */
554. @Test
555. public void testHQL_19(){
556. Session session = sf.getCurrentSession() ;
557. session.beginTransaction() ;
558.
559. Query q = session.createQuery("from Topic t where t.msgs is empty ") ;
560. for(Object o : q.list() ){
561. Topic t = (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_.id as id2_,
572. topic0_.categoryId as categoryId2_,
573. topic0_.createDate as createDate2_,
574. topic0_.title as title2_
575. from
576. topic topic0_
577. where
578. topic0_.title like '%5'
579. */
580. @Test
581. public void testHQL_20(){
582. Session session = sf.getCurrentSession() ;
583. session.beginTransaction() ;
584.
585. Query q = session.createQuery("from Topic t where t.title like '%5' ") ;
586. for(Object o : q.list() ){
587. Topic t = (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) as col_0_0_,
599. upper(topic0_.title) as col_1_0_,
600. trim(topic0_.title) as col_2_0_,
601. concat(topic0_.title,
602. '*****') as col_3_0_,
603. length(topic0_.title) as col_4_0_
604. from
605. topic topic0_
606. */
607. @Test
608. public void testHQL_21(){
609. Session session = sf.getCurrentSession() ;
610. session.beginTransaction() ;
611.
612. Query q = session.createQuery("select " +
613. " lower(t.title) ," +
614. " upper(t.title) ," +
615. " trim(t.title) ," +
616. " concat(t.title , '*****') , " +
617. " length(t.title)" +
618. " from Topic t ") ;
619. for(Object os : 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) as col_0_0_,
632. sqrt(topic0_.id) as col_1_0_,
633. mod(topic0_.id,
634. 2) as col_2_0_
635. from
636. topic topic0_
637. */
638. @Test
639. public void testHQL_22(){
640. Session session = sf.getCurrentSession() ;
641. session.beginTransaction() ;
642.
643. Query q = session.createQuery("select " +
644. " abs(t.id) ," +
645. " sqrt(t.id) ," +
646. " mod(t.id , 2) " +
647. " from Topic t ") ;
648. for(Object os : 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_date as col_0_0_,
660. current_time as col_1_0_,
661. current_timestamp as col_2_0_,
662. topic0_.id as col_3_0_
663. from
664. topic topic0_
665. */
666. @Test
667. public void testHQL_24(){
668. Session session = sf.getCurrentSession() ;
669. session.beginTransaction() ;
670.
671. Query q = session.createQuery("select current_date , current_time , current_timestamp , t.id from Topic t ") ;
672. for(Object os : 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_.id as id2_,
684. topic0_.categoryId as categoryId2_,
685. topic0_.createDate as createDate2_,
686. topic0_.title as title2_
687. from
688. topic topic0_
689. where
690. topic0_.createDate<?
691. */
692. @Test
693. public void testHQL_25(){
694. Session session = sf.getCurrentSession() ;
695. session.beginTransaction() ;
696.
697. Query q = session.createQuery("from Topic t Where t.createDate < :date ") ;
698. q.setParameter("date", new Date()) ;
699. for(Object o : q.list() ){
700. Topic t = (Topic)o ;
701. System.out.println(t.getId()+" "+t.getCreateDate());
702. }
703. session.getTransaction().commit() ;
704. }
705.
706. /**
707. * group by 语句
708. * SQL 语句
709. * select
710. topic0_.title as col_0_0_,
711. count(*) as col_1_0_
712. from
713. topic topic0_
714. group by
715. topic0_.title
716. */
717. @Test
718. public void testHQL_26(){
719. Session session = sf.getCurrentSession() ;
720. session.beginTransaction() ;
721.
722. Query q = session.createQuery("select t.title , count(*) from Topic t group by t.title ") ;
723. for(Object o : q.list() ){
724. Object[] arr = (Object[])o ;
725. System.out.println(arr[0]+" "+arr[1]);
726. }
727. session.getTransaction().commit() ;
728. }
729.
730. /**
731. * group by having 语句
732. * group by 里面出现的字段必须出现在 select 里面
733. * having 中的条件必须是组合函数
734. * SQL 语句
735. * select
736. topic0_.title as col_0_0_,
737. count(*) as col_1_0_
738. from
739. topic topic0_
740. group by
741. topic0_.title
742. having
743. count(*)>0
744. */
745. @Test
746. public void testHQL_27(){
747. Session session = sf.getCurrentSession() ;
748. session.beginTransaction() ;
749.
750. Query q = session.createQuery("select t.title , count(*) from Topic t group by t.title having count(*) > 0 ") ;
751. for(Object o : 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_.id as id2_,
763. topic0_.categoryId as categoryId2_,
764. topic0_.createDate as createDate2_,
765. topic0_.title as title2_
766. from
767. topic topic0_
768. where
769. topic0_.id<(
770. select
771. avg(topic1_.id)
772. from
773. topic topic1_
774. )
775. */
776. @Test
777. public void testHQL_28(){
778. Session session = sf.getCurrentSession() ;
779. session.beginTransaction() ;
780.
781. Query q = session.createQuery("from Topic t where t.id < (select avg(t.id) from Topic t ) ") ;
782. for(Object o : q.list() ){
783. Topic t = (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_.id as id2_,
794. topic0_.categoryId as categoryId2_,
795. topic0_.createDate as createDate2_,
796. topic0_.title as title2_
797. from
798. topic topic0_
799. where
800. topic0_.id<all (
801. select
802. topic1_.id
803. from
804. topic topic1_
805. where
806. mod(topic1_.id, 2)=0
807. )
808.
809. */
810. @Test
811. public void testHQL_29(){
812. Session session = sf.getCurrentSession() ;
813. session.beginTransaction() ;
814.
815. Query q = session.createQuery("from Topic t where t.id < ALL (select t.id from Topic t where mod(t.id , 2) = 0 ) ") ;
816. for(Object o : q.list() ){
817. Topic t = (Topic)o ;
818. System.out.println(t.getId() +" "+t.getCreateDate());
819. }
820. session.getTransaction().commit() ;
821. }
822.
823. /**
824. * exists 和 not exists
825. * 说明：用 in 可以实现 exists 的功能
826. * 但是exists的效率高
827. * SQL 语句
828. * select
829. topic0_.id as id2_,
830. topic0_.categoryId as categoryId2_,
831. topic0_.createDate as createDate2_,
832. topic0_.title as title2_
833. from
834. topic topic0_
835. where
836. not (exists (select
837. msg1_.id
838. from
839. Msg msg1_
840. where
841. msg1_.topicId=topic0_.id))
842. */
843. @Test
844. public void testHQL_30(){
845. Session session = sf.getCurrentSession() ;
846. session.beginTransaction() ;
847.
848. Query q = session.createQuery("from Topic t where not exists (select m.id from Msg m where m.topic.id = t.id ) ") ;
849.// Query q = session.createQuery("from Topic t where exists (select m.id from Msg m where m.topic.id = t.id ) ") ;
850. for(Object o : q.list() ){
851. Topic t = (Topic)o ;
852. System.out.println(t.getId() +" "+t.getCreateDate());
853. }
854. session.getTransaction().commit() ;
855. }
856.
857. /**
858. * update delete
859. * SQL 语句
860. * update
861. topic
862. set
863. title=upper(title)
864. */
865. @Test
866. public void testHQL_31(){
867. Session session = sf.getCurrentSession() ;
868. session.beginTransaction() ;
869.
870. Query q = session.createQuery("update Topic t set t.title = upper(t.title) ") ;
871. q.executeUpdate() ;
872.
873. q = session.createQuery("from Topic ");
874. for(Object o : q.list()){
875. Topic t = (Topic) o ;
876. System.out.println(t.getTitle());
877. }
878.
879. session.createQuery("update Topic t set t.title = lower(t.title) ")
880. .executeUpdate() ;
881. session.getTransaction().commit() ;
882. }
883.
884. /**
885. * 命名查询
886. * 实现按名字查找的自定义查询
887. * 在 Entity 上面自定义好 SQL 语句
888. * SQL 语句
889. * select
890. topic0_.id as id2_,
891. topic0_.categoryId as categoryId2_,
892. topic0_.createDate as createDate2_,
893. topic0_.title as title2_
894. from
895. topic topic0_
896. where
897. topic0_.id=?
898. */
899. @Test
900. public void testHQL_32(){
901. Session session = sf.getCurrentSession() ;
902. session.beginTransaction() ;
903.
904.// Query q = session.getNamedQuery("Topic.selectTopic") ;
905.// q.setParameter("id", 5) ;
906.// Topic t = (Topic)q.uniqueResult() ;
907.// System.out.println(t.getId()+" "+t.getCreateDate());
908.
909. Query q = session.getNamedQuery("Topic.conditionTopic") ;
910. q.setParameter("title", "%") ;
911. for(Object o : q.list()){
912. Topic t = (Topic)o ;
913. System.out.println(t.getId()+" "+t.getCreateDate());
914. }
915.
916.// Query q = session.getNamedQuery("native_sql_page") ;
917.// for(Object o : q.list()){
918.// Topic t = (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. category limit 2, 6
932. */
933. @Test
934. public void testHQL_33(){
935. Session session = sf.getCurrentSession() ;
936. session.getTransaction().begin() ;
937.
938. SQLQuery q = session.createSQLQuery("select * from category limit 2,6").addEntity(Category.class);
939. List<Category> categorys = (List<Category>)q.list() ;
940. for(Category c : 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_.id as id2_,
953. topic0_.categoryId as categoryId2_,
954. topic0_.createDate as createDate2_,
955. topic0_.title as title2_
956. from
957. topic topic0_
958. where
959. not (exists (select
960. msg1_.id
961. from
962. Msg msg1_
963. where
964. msg1_.topicId=topic0_.id)) limit ?, ?
965. */
966. @Test
967. public void testHQL_34(){
968. Session session = sf.getCurrentSession() ;
969. session.beginTransaction() ;
970.
971. Query q = session.createQuery("from Topic t where not exists (select m.id from Msg m where m.topic.id = t.id ) ") ;
972. // Query q = session.createQuery("from Topic t where exists (select m.id from Msg m where m.topic.id = t.id ) ") ;
973. q.setFirstResult(10).setMaxResults(20) ;
974. for(Object o : q.list() ){
975. Topic t = (Topic)o ;
976. System.out.println(t.getId() +" "+t.getCreateDate());
977. }
978. session.getTransaction().commit() ;
979. }
980.
981.
982. @Test
983. public void testSchemaExport(){
984. new SchemaExport(new AnnotationConfiguration().configure()).create(false, true);
985. }
986.
987.}

hibernate.cfg.xml文件：
Xml代码 1.<?xml version='1.0' encoding='utf-8'?>
2.<!DOCTYPE hibernate-configuration PUBLIC
3. "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
4. "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
5.
6.<hibernate-configuration>
7.
8. <session-factory>
9.
10. 
11. <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
12. <property name="connection.url">jdbc:mysql://localhost/hibernate</property>
13. <property name="connection.username">root</property>
14. <property name="connection.password">root</property>
15.
16. 
17. <property name="connection.pool_size">100</property>
18.
19. 
20. <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
21.
22. 
23. <property name="current_session_context_class">thread</property>
24.
25. 
26. <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
27.
28. 
29. <property name="show_sql">true</property>
30.
31. <property name="format_sql">true</property>
32.
33. 
34. 
35.
36.
37. <mapping class="com.jlee06.QL.Category"/>
38. <mapping class="com.jlee06.QL.Msg"/>
39. <mapping class="com.jlee06.QL.Topic"/>
40.
41.
42. </session-factory>
43.
44.</hibernate-configuration>

热点排行

软件架构设计

Hibernate 运用 Annotation 6（各种查询语句）