首页 > 代码库 > HQL（Hibernate Query Language）
HQL（Hibernate Query Language）
1. NativeSQL > HQL > EJB QL(JP QL 1.0) > QBC(Query By Criteria) > QBE(Query By Example)
2. 总结：QL应该和导航关系结合，共同为查询提供服务。
以下图为例：
Category:
1 package com.bjsxt.hibernate; 2 3 import javax.persistence.Entity; 4 import javax.persistence.GeneratedValue; 5 import javax.persistence.Id; 6 7 @Entity 8 public class Category { 9 10 private Integer id; 11 12 private String name; 13 14 @Id 15 @GeneratedValue 16 public Integer getId() { 17 return id; 18 } 19 20 public void setId(Integer id) { 21 this.id = id; 22 } 23 24 public String getName() { 25 return name; 26 } 27 28 public void setName(String name) { 29 this.name = name; 30 } 31 }
Topic:
1 package com.bjsxt.hibernate; 2 3 import java.util.Date; 4 5 import javax.persistence.Entity; 6 import javax.persistence.FetchType; 7 import javax.persistence.GeneratedValue; 8 import javax.persistence.Id; 9 import javax.persistence.ManyToOne; 10 11 @Entity 12 public class Topic { 13 14 private Integer id; 15 16 private String title; 17 18 private Category category; 19 20 private Date createDate; 21 22 @Id 23 @GeneratedValue 24 public Integer getId() { 25 return id; 26 } 27 28 public void setId(Integer id) { 29 this.id = id; 30 } 31 32 public String getTitle() { 33 return title; 34 } 35 36 public void setTitle(String title) { 37 this.title = title; 38 } 39 40 @ManyToOne(fetch=FetchType.LAZY) 41 public Category getCategory() { 42 return category; 43 } 44 45 public void setCategory(Category category) { 46 this.category = category; 47 } 48 49 public Date getCreateDate() { 50 return createDate; 51 } 52 53 public void setCreateDate(Date createDate) { 54 this.createDate = createDate; 55 } 56 }
Msg:
1 package com.bjsxt.hibernate; 2 3 import javax.persistence.Entity; 4 import javax.persistence.GeneratedValue; 5 import javax.persistence.Id; 6 import javax.persistence.ManyToOne; 7 8 @Entity 9 public class Msg { 10 11 private Integer id; 12 13 private String cont; 14 15 private Topic topic; 16 17 @Id 18 @GeneratedValue 19 public Integer getId() { 20 return id; 21 } 22 23 public void setId(Integer id) { 24 this.id = id; 25 } 26 27 public String getCont() { 28 return cont; 29 } 30 31 public void setCont(String cont) { 32 this.cont = cont; 33 } 34 35 @ManyToOne 36 public Topic getTopic() { 37 return topic; 38 } 39 40 public void setTopic(Topic topic) { 41 this.topic = topic; 42 } 43 }
MsgInfo:
1 package com.bjsxt.hibernate; 2 3 //VO(Value Object) DTO(Data Transfer Object) 4 public class MsgInfo { 5 6 private Integer id; 7 8 private String cont; 9 10 private String topicName; 11 12 private String categoryName; 13 14 public MsgInfo(Integer id,String cont,String topicName,String categoryName){ 15 this.id = id; 16 this.cont = cont; 17 this.topicName = topicName; 18 this.categoryName = categoryName; 19 } 20 21 public Integer getId() { 22 return id; 23 } 24 25 public void setId(Integer id) { 26 this.id = id; 27 } 28 29 public String getCont() { 30 return cont; 31 } 32 33 public void setCont(String cont) { 34 this.cont = cont; 35 } 36 37 public String getTopicName() { 38 return topicName; 39 } 40 41 public void setTopicName(String topicName) { 42 this.topicName = topicName; 43 } 44 45 public String getCategoryName() { 46 return categoryName; 47 } 48 49 public void setCategoryName(String categoryName) { 50 this.categoryName = categoryName; 51 } 52 }
HibernateQLTest1:
1 package com.bjsxt.hibernate; 2 3 import java.util.Date; 4 import java.util.List; 5 6 import org.hibernate.Query; 7 import org.hibernate.Session; 8 import org.hibernate.SessionFactory; 9 import org.hibernate.cfg.AnnotationConfiguration; 10 import org.hibernate.tool.hbm2ddl.SchemaExport; 11 import org.junit.After; 12 import org.junit.Before; 13 import org.junit.Test; 14 15 public class HibernateQLTest { 16 private static SessionFactory sf = null; 17 18 @Before 19 public void beforeClass(){ 20 // new SchemaExport(new AnnotationConfiguration().configure()).create(false, true); 21 sf = new AnnotationConfiguration().configure().buildSessionFactory(); 22 } 23 24 @After 25 public void afterClass(){ 26 if(sf != null){ 27 sf.close(); 28 } 29 } 30 31 @Test 32 public void testSave(){ 33 34 Session session = sf.getCurrentSession(); 35 session.beginTransaction(); 36 37 for(int i=0; i<10; i++){ 38 Category c = new Category(); 39 c.setName("c" + i); 40 session.save(c); 41 } 42 43 for(int i=0; i<10; i++){ 44 Category c = new Category(); 45 c.setId(1); 46 Topic t = new Topic(); 47 t.setCategory(c); 48 t.setCreateDate(new Date()); 49 t.setTitle("t"+i); 50 session.save(t); 51 } 52 53 for(int i=0; i<10; i++){ 54 Topic t = new Topic(); 55 t.setId(1); 56 Msg m = new Msg(); 57 m.setTopic(t); 58 m.setCont("m" + i); 59 session.save(m); 60 } 61 62 session.getTransaction().commit(); 63 } 64 65 @Test 66 public void testHQL_01(){ 67 Session session = sf.getCurrentSession(); 68 session.beginTransaction(); 69 Query q = (Query) session.createQuery("from Category"); 70 List<Category> categories = (List<Category>)q.list(); 71 for(Category c : categories){ 72 System.out.println(c.getName()); 73 } 74 session.getTransaction().commit(); 75 } 76 77 @Test 78 public void testHQL_02(){ 79 Session session = sf.getCurrentSession(); 80 session.beginTransaction(); 81 Query q = (Query) session.createQuery("from Category c where c.name > ‘c5‘"); 82 List<Category> categories = (List<Category>)q.list(); 83 for(Category c : categories){ 84 System.out.println(c.getName()); 85 } 86 session.getTransaction().commit(); 87 } 88 89 @Test 90 public void testHQL_03(){ 91 Session session = sf.getCurrentSession(); 92 session.beginTransaction(); 93 Query q = (Query) session.createQuery("from Category c order by name desc"); 94 List<Category> categories = (List<Category>)q.list(); 95 for(Category c : categories){ 96 System.out.println(c.getName()); 97 } 98 session.getTransaction().commit(); 99 } 100 101 @Test 102 public void testHQL_04(){ 103 Session session = sf.getCurrentSession(); 104 session.beginTransaction(); 105 Query q = (Query) session.createQuery( 106 "select distinct c from Category c order by name desc");//主键不同 107 List<Category> categories = (List<Category>)q.list(); 108 for(Category c : categories){ 109 System.out.println(c.getName()); 110 } 111 session.getTransaction().commit(); 112 } 113 114 @Test 115 public void testHQL_05(){ 116 Session session = sf.getCurrentSession(); 117 session.beginTransaction(); 118 /*Query q = (Query) session.createQuery( 119 "from Category c where c.id > :min and c.id < :max");//主键不同 120 // q.setParameter("min", 2); 121 // q.setParameter("max", 8); 122 q.setInteger("min", 2); 123 q.setInteger("max", 8);*/ 124 125 Query q = (Query) session.createQuery("from Category c where c.id > :min and c.id < :max") 126 .setInteger("min", 2) 127 .setInteger("max", 8); 128 List<Category> categories = (List<Category>)q.list(); 129 for(Category c : categories){ 130 System.out.println(c.getId() + "-" +c.getName()); 131 } 132 session.getTransaction().commit(); 133 } 134 135 /** 136 * 链式编程 137 */ 138 @Test 139 public void testHQL_06(){ 140 Session session = sf.getCurrentSession(); 141 session.beginTransaction(); 142 Query q = (Query) session.createQuery("from Category c where c.id > ? and c.id < ?") 143 .setInteger(0, 2)//索引从0开始 144 .setInteger(1, 8); 145 List<Category> categories = (List<Category>)q.list(); 146 for(Category c : categories){ 147 System.out.println(c.getId() + "-" +c.getName()); 148 } 149 session.getTransaction().commit(); 150 } 151 152 /** 153 * 分页 154 */ 155 @Test 156 public void testHQL_07(){ 157 Session session = sf.getCurrentSession(); 158 session.beginTransaction(); 159 Query q = (Query) session.createQuery("from Category c order by name desc"); 160 q.setMaxResults(4);//每页4条 161 q.setFirstResult(2);//从第二行开始 162 List<Category> categories = (List<Category>)q.list(); 163 for(Category c : categories){ 164 System.out.println(c.getId() + "-" +c.getName()); 165 } 166 session.getTransaction().commit(); 167 } 168 169 /** 170 * 分页(换一种获取方式) 171 */ 172 @Test 173 public void testHQL_08(){ 174 Session session = sf.getCurrentSession(); 175 session.beginTransaction(); 176 Query q = (Query) session.createQuery("select c.id,c.name from Category c order by name desc"); 177 q.setMaxResults(4);//每页4条 178 q.setFirstResult(2);//从第二行开始 179 List<Object[]> categories = (List<Object[]>)q.list(); 180 for(Object[] c : categories){ 181 System.out.println(c[0] + "-" +c[1]); 182 } 183 session.getTransaction().commit(); 184 } 185 186 /** 187 * 设定 fetch type 为 lazy 后将不会有第二条sql语句 188 */ 189 @Test 190 public void testHQL_09(){ 191 Session session = sf.getCurrentSession(); 192 session.beginTransaction(); 193 Query q = session.createQuery("from Topic t where t.category.id = 1"); 194 List<Topic> topics = (List<Topic>)q.list(); 195 for(Topic t : topics){ 196 System.out.println(t.getId() + "-" + t.getTitle()); 197 // System.out.println(t.getCategory().getName()); 198 } 199 session.getTransaction().commit(); 200 } 201 202 /** 203 * 设定 fetch type 为 lazy 后将不会有第二条sql语句 204 */ 205 @Test 206 public void testHQL_10(){ 207 Session session = sf.getCurrentSession(); 208 session.beginTransaction(); 209 Query q = session.createQuery("from Topic t where t.category.id = 1"); 210 List<Topic> topics = (List<Topic>)q.list(); 211 for(Topic t : topics){ 212 System.out.println(t.getId() + "-" + t.getTitle()); 213 // System.out.println(t.getCategory().getName()); 214 } 215 session.getTransaction().commit(); 216 } 217 218 @Test 219 public void testHQL_11(){ 220 Session session = sf.getCurrentSession(); 221 session.beginTransaction(); 222 Query q = session.createQuery("from Msg m where m.topic.category.id = 1"); 223 for(Object o : q.list()){ 224 Msg m = (Msg)o; 225 System.out.println(m.getCont()); 226 } 227 session.getTransaction().commit(); 228 } 229 230 /** 231 * VO(Value Object) 232 * DTO(Data Transfer Object) 233 */ 234 @Test 235 public void testHQL_12(){ 236 Session session = sf.getCurrentSession(); 237 session.beginTransaction(); 238 Query q = session.createQuery("select new com.bjsxt.hibernate.MsgInfo(m.id, m.cont, m.topic.title, m.topic.category.name) from Msg m"); 239 for(Object o : q.list()){ 240 MsgInfo m = (MsgInfo)o; 241 System.out.println(m.getCont()); 242 } 243 session.getTransaction().commit(); 244 } 245 246 //动手测试left right join 247 //为什么不能直接写 Category 名，而必须写 t.category 248 //因为有可能存在多个成员变量（同一个类），需要指明哪一个成员变量的连接条件来做连接 249 @Test 250 public void testHQL_13(){ 251 Session session = sf.getCurrentSession(); 252 session.beginTransaction(); 253 Query q = session.createQuery("select t.title,c.name from Topic t join t.category c"); 254 List<Object[]> list = q.list(); 255 for(Object[] o : list){ 256 System.out.println(o[0] + "-" + o[1]); 257 } 258 session.getTransaction().commit(); 259 } 260 261 //学习使用 uniqueResult 262 @Test 263 public void testHQL_14(){ 264 Session session = sf.getCurrentSession(); 265 session.beginTransaction(); 266 Query q = session.createQuery("from Msg m where m = :MsgToSearch");//不重要 267 Msg m = new Msg(); 268 m.setId(1); 269 q.setParameter("MsgToSearch", m); 270 271 Msg mResult = (Msg)q.uniqueResult(); 272 System.out.println(mResult.getCont()); 273 274 session.getTransaction().commit(); 275 } 276 277 @Test 278 public void testHQL_15(){ 279 Session session = sf.getCurrentSession(); 280 session.beginTransaction(); 281 Query q = session.createQuery("select count(*) from Msg m"); 282 283 long count = (Long)q.uniqueResult(); 284 System.out.println(count); 285 session.getTransaction().commit(); 286 } 287 288 @Test 289 public void testHQL_16(){ 290 Session session = sf.getCurrentSession(); 291 session.beginTransaction(); 292 Query q = session.createQuery("select max(m.id), min(m.id), avg(m.id), sum(m.id) from Msg m"); 293 294 Object[] o = (Object[])q.uniqueResult(); 295 System.out.println(o[0] + "-" + o[1] + "-" + o[2] + "-" + o[3]); 296 297 session.getTransaction().commit(); 298 } 299 300 @Test 301 public void testHQL_17(){ 302 Session session = sf.getCurrentSession(); 303 session.beginTransaction(); 304 Query q = session.createQuery("from Msg m where m.id between 3 and 5"); 305 306 for(Object o : q.list()){ 307 Msg m = (Msg)o; 308 System.out.println(m.getId() + "-" +m.getCont()); 309 } 310 311 session.getTransaction().commit(); 312 } 313 314 @Test 315 public void testHQL_18(){ 316 Session session = sf.getCurrentSession(); 317 session.beginTransaction(); 318 Query q = session.createQuery("from Msg m where m.id in(3,4,5)"); 319 320 for(Object o : q.list()){ 321 Msg m = (Msg)o; 322 System.out.println(m.getId() + "-" +m.getCont()); 323 } 324 325 session.getTransaction().commit(); 326 } 327 328 @Test 329 public void testHQL_19(){ 330 Session session = sf.getCurrentSession(); 331 session.beginTransaction(); 332 Query q = session.createQuery("from Msg m where m.cont is not null"); 333 334 for(Object o : q.list()){ 335 Msg m = (Msg)o; 336 System.out.println(m.getId() + "-" +m.getCont()); 337 } 338 339 session.getTransaction().commit(); 340 } 341 342 @Test 343 public void testSchemaExport(){ 344 new SchemaExport(new AnnotationConfiguration().configure()).create(false, true); 345 } 346 347 public static void main(String[] args) { 348 // new HibernatTreeTest().testSave(); 349 new HibernateQLTest().beforeClass(); 350 } 351 352 }
HibernateQLTest2:
1 package com.bjsxt.hibernate; 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.After; 13 import org.junit.Before; 14 import org.junit.Test; 15 16 public class HibernateQLTest { 17 private static SessionFactory sf = null; 18 19 @Before 20 public void beforeClass(){ 21 // new SchemaExport(new AnnotationConfiguration().configure()).create(false, true); 22 sf = new AnnotationConfiguration().configure().buildSessionFactory(); 23 } 24 25 @After 26 public void afterClass(){ 27 if(sf != null){ 28 sf.close(); 29 } 30 } 31 32 @Test 33 public void testSave(){ 34 35 Session session = sf.getCurrentSession(); 36 session.beginTransaction(); 37 38 for(int i=0; i<10; i++){ 39 Category c = new Category(); 40 c.setName("c" + i); 41 session.save(c); 42 } 43 44 for(int i=0; i<10; i++){ 45 Category c = new Category(); 46 c.setId(1); 47 Topic t = new Topic(); 48 t.setCategory(c); 49 t.setCreateDate(new Date()); 50 t.setTitle("t"+i); 51 session.save(t); 52 } 53 54 for(int i=0; i<10; i++){ 55 Topic t = new Topic(); 56 t.setId(1); 57 Msg m = new Msg(); 58 m.setTopic(t); 59 m.setCont("m" + i); 60 session.save(m); 61 } 62 63 session.getTransaction().commit(); 64 } 65 66 //is empty and is not empty 67 @Test 68 public void testHQL_20(){ 69 Session session = sf.getCurrentSession(); 70 session.beginTransaction(); 71 Query q = session.createQuery("from Topic t where t.msgs is empty"); 72 73 for(Object o : q.list()){ 74 Topic t = (Topic)o; 75 System.out.println(t.getId() + "-" +t.getTitle()); 76 } 77 78 session.getTransaction().commit(); 79 } 80 81 @Test 82 public void testHQL_21(){ 83 Session session = sf.getCurrentSession(); 84 session.beginTransaction(); 85 Query q = session.createQuery("from Topic t where t.title like ‘%5‘"); 86 87 for(Object o : q.list()){ 88 Topic t = (Topic)o; 89 System.out.println(t.getId() + "-" +t.getTitle()); 90 } 91 92 session.getTransaction().commit(); 93 } 94 95 @Test 96 public void testHQL_22(){ 97 Session session = sf.getCurrentSession(); 98 session.beginTransaction(); 99 Query q = session.createQuery("from Topic t where t.title like ‘_5‘"); 100 101 for(Object o : q.list()){ 102 Topic t = (Topic)o; 103 System.out.println(t.getId() + "-" +t.getTitle()); 104 } 105 106 session.getTransaction().commit(); 107 } 108 109 //不重要 110 @Test 111 public void testHQL_23(){ 112 Session session = sf.getCurrentSession(); 113 session.beginTransaction(); 114 Query q = session.createQuery("select lower(t.title)," 115 + "upper(t.title)," 116 + "trim(t.title)," 117 + "concat(t.title,‘***‘)," 118 + "length(t.title) " 119 + "from Topic t"); 120 121 for(Object o : q.list()){ 122 Object[] arr = (Object[])o; 123 System.out.println(arr[0] + "-" + arr[1] + "-" + arr[2] + "-" + arr[3] + "-" + arr[4]); 124 } 125 126 session.getTransaction().commit(); 127 } 128 129 @Test 130 public void testHQL_24(){ 131 Session session = sf.getCurrentSession(); 132 session.beginTransaction(); 133 Query q = session.createQuery("select abs(t.id)," 134 + "sqrt(t.id)," 135 + "mod(t.id,2) " 136 + "from Topic t"); 137 138 for(Object o : q.list()){ 139 Object[] arr = (Object[])o; 140 System.out.println(arr[0] + "-" + arr[1] + "-" + arr[2]); 141 } 142 143 session.getTransaction().commit(); 144 } 145 146 @Test 147 public void testHQL_25(){ 148 Session session = sf.getCurrentSession(); 149 session.beginTransaction(); 150 Query q = session.createQuery("select current_date,current_time,current_timestamp,t.id from Topic t"); 151 152 for(Object o : q.list()){ 153 Object[] arr = (Object[])o; 154 System.out.println(arr[0] + "|" + arr[1] + "|" + arr[2] + "|" + arr[3]); 155 } 156 157 session.getTransaction().commit(); 158 } 159 160 @Test 161 public void testHQL_26(){ 162 Session session = sf.getCurrentSession(); 163 session.beginTransaction(); 164 Query q = session.createQuery("from Topic t where t.createDate < :date"); 165 q.setParameter("date", new Date()); 166 167 for(Object o : q.list()){ 168 Topic t = (Topic)o; 169 System.out.println(t.getTitle()); 170 } 171 172 session.getTransaction().commit(); 173 } 174 175 @Test 176 public void testHQL_27(){ 177 Session session = sf.getCurrentSession(); 178 session.beginTransaction(); 179 Query q = session.createQuery("select t.title, count(*) from Topic t group by t.title"); 180 181 for(Object o : q.list()){ 182 Object[] arr = (Object[])o; 183 System.out.println(arr[0] + "|" + arr[1]); 184 } 185 186 session.getTransaction().commit(); 187 } 188 189 @Test 190 public void testHQL_28(){ 191 Session session = sf.getCurrentSession(); 192 session.beginTransaction(); 193 Query q = session.createQuery("select t.title, count(*) from Topic t group by t.title having count(*) > 0"); 194 195 for(Object o : q.list()){ 196 Object[] arr = (Object[])o; 197 System.out.println(arr[0] + "|" + arr[1]); 198 } 199 200 session.getTransaction().commit(); 201 } 202 203 @Test 204 public void testHQL_29(){ 205 Session session = sf.getCurrentSession(); 206 session.beginTransaction(); 207 Query q = session.createQuery("from Topic t where t.id < (select avg(t.id) from Topic t)"); 208 209 for(Object o : q.list()){ 210 Topic t = (Topic)o; 211 System.out.println(t.getTitle()); 212 } 213 214 session.getTransaction().commit(); 215 } 216 217 @Test 218 public void testHQL_30(){ 219 Session session = sf.getCurrentSession(); 220 session.beginTransaction(); 221 Query q = session.createQuery("from Topic t where t.id < ALL (select t.id from Topic t where mod(t.id,2) = 0)"); 222 223 for(Object o : q.list()){ 224 Topic t = (Topic)o; 225 System.out.println(t.getTitle()); 226 } 227 228 session.getTransaction().commit(); 229 } 230 231 //用in可以实现 exists 的功能 232 //但是 exists 执行效率高 233 @Test 234 public void testHQL_31(){ 235 Session session = sf.getCurrentSession(); 236 session.beginTransaction(); 237 Query q = session.createQuery("from Topic t where not exists (select m.id from Msg m where m.topic.id = t.id)");//就是沒有回帖的topic 238 239 for(Object o : q.list()){ 240 Topic t = (Topic)o; 241 System.out.println(t.getTitle()); 242 } 243 244 session.getTransaction().commit(); 245 } 246 247 @Test 248 public void testHQL_32(){ 249 Session session = sf.getCurrentSession(); 250 session.beginTransaction(); 251 252 Query q = session.createQuery("update Topic t set t.title = upper(t.title)"); 253 q.executeUpdate(); 254 q = session.createQuery("from Topic"); 255 for(Object o : q.list()){ 256 Topic t = (Topic)o; 257 System.out.println(t.getTitle()); 258 } 259 260 q = session.createQuery("update Topic t set t.title = lower(t.title)"); 261 q.executeUpdate(); 262 263 session.getTransaction().commit(); 264 } 265 266 //不重要 267 @Test 268 public void testHQL_33(){ 269 Session session = sf.getCurrentSession(); 270 session.beginTransaction(); 271 272 Query q = session.getNamedQuery("topic.selectCertainTopic"); 273 q.setParameter("id", 5); 274 Topic t = (Topic)q.uniqueResult(); 275 System.out.println(t.getTitle()); 276 277 session.getTransaction().commit(); 278 } 279 280 //Native(不太重要) 281 @Test 282 public void testHQL_34(){ 283 Session session = sf.getCurrentSession(); 284 session.beginTransaction(); 285 286 SQLQuery q = session.createSQLQuery("select * from category limit 2,4").addEntity(Category.class); 287 List<Category> categories = (List<Category>)q.list(); 288 for(Category c : categories){ 289 System.out.println(c.getName()); 290 } 291 292 session.getTransaction().commit(); 293 } 294 295 @Test 296 public void testSchemaExport(){ 297 new SchemaExport(new AnnotationConfiguration().configure()).create(false, true); 298 } 299 300 public static void main(String[] args) { 301 // new HibernatTreeTest().testSave(); 302 new HibernateQLTest().beforeClass(); 303 } 304 305 }
jar包链接: https://pan.baidu.com/s/1slvwzbn 密码: hwef
代码链接1: https://pan.baidu.com/s/1skCHfU9 密码: ka73
代码链接2: https://pan.baidu.com/s/1qYruCUC 密码: v4eb
HQL（Hibernate Query Language）
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。