HQL 简单使用二
HQL的简单使用2
1、Category类

package com.edu.hpu;import javax.persistence.Entity;import javax.persistence.GeneratedValue;import javax.persistence.Id;@Entitypublic class Category {private int id;private String name;@Id@GeneratedValuepublic int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}}

package com.edu.hpu;import javax.persistence.Entity;import javax.persistence.GeneratedValue;import javax.persistence.Id;import javax.persistence.JoinColumn;import javax.persistence.ManyToOne;@Entitypublic class Msg {private int id;private String cont;private Topic topic;@Id@GeneratedValuepublic int getId() {return id;}public void setId(int id) {this.id = id;}public String getCont() {return cont;}public void setCont(String cont) {this.cont = cont;}@ManyToOne@JoinColumn(name="topic_ID")public Topic getTopic() {return topic;}public void setTopic(Topic topic) {this.topic = topic;}}

package com.edu.hpu;import java.util.Date;import java.util.HashSet;import java.util.Set;import javax.persistence.Entity;import javax.persistence.FetchType;import javax.persistence.GeneratedValue;import javax.persistence.Id;import javax.persistence.JoinColumn;import javax.persistence.ManyToOne;import javax.persistence.OneToMany;@Entitypublic class Topic {private int id;private String title;private Date date;private Category category;private Set<Msg> msgs = new HashSet<Msg>();@Id@GeneratedValuepublic int getId() {return id;}public void setId(int id) {this.id = id;}public String getTitle() {return title;}public void setTitle(String title) {this.title = title;}public Date getDate() {return date;}public void setDate(Date date) {this.date = date;}@ManyToOne(fetch=FetchType.LAZY)@JoinColumn(name="category_ID")public Category getCategory() {return category;}public void setCategory(Category category) {this.category = category;}@OneToMany(mappedBy="topic")public Set<Msg> getMsgs() {return msgs;}public void setMsgs(Set<Msg> msgs) {this.msgs = msgs;}}

package com.edu.hpu;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.Configuration;import org.hibernate.service.ServiceRegistry;import org.hibernate.service.ServiceRegistryBuilder;import org.hibernate.tool.hbm2ddl.SchemaExport;import org.junit.AfterClass;import org.junit.BeforeClass;import org.junit.Test;public class TestClass {private static SessionFactory sf = null;@BeforeClasspublic static void beforeClass() {Configuration conf = new Configuration().configure();ServiceRegistry sr = new ServiceRegistryBuilder().applySettings(conf.getProperties()).buildServiceRegistry();sf = conf.buildSessionFactory(sr);}@Testpublic void testExport() {new SchemaExport(new Configuration().configure()).create(true ,true);}@Testpublic void testSave() {Session session = sf.openSession();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.setTitle("t" + i);t.setCategory(c);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();session.close();}@Testpublic void testHQL_19() {Session s = sf.getCurrentSession();s.beginTransaction();Query q = s.createQuery("from Topic t where t.msgs is empty");List<Topic> ts = q.list();for(Topic t : ts) {System.out.println(t.getTitle());}s.getTransaction().commit();}//like % 代表零个或多个@Testpublic void testHQL_20() {Session s = sf.getCurrentSession();s.beginTransaction();Query q = s.createQuery("from Topic t where t.title like '%5'");List<Topic> ts = q.list();for(Topic t : ts) {System.out.println(t.getTitle());}s.getTransaction().commit();}//like % 代表一个或多个@Testpublic void testHQL_21() {Session s = sf.getCurrentSession();s.beginTransaction();Query q = s.createQuery("from Topic t where t.title like '_5'");List<Topic> ts = q.list();for(Topic t : ts) {System.out.println(t.getTitle());}s.getTransaction().commit();}@Testpublic void testHQL_22() {Session s = sf.getCurrentSession();s.beginTransaction();Query q = s.createQuery("select current_time , current_date , current_timestamp , t.id from Topic t");List<Object[]> objs = (List<Object[]>)q.list();for(Object[] obj : objs) {System.out.println(obj[0] + " " + obj[1] + " " + obj[2] + " " + obj[3]);}s.getTransaction().commit();}@Testpublic void testHQL_23() {Session s = sf.getCurrentSession();s.beginTransaction();Query q = s.createQuery("from Topic t where t.date < :now");q.setParameter("now", new Date());List<Topic> ts = (List<Topic>)q.list();for(Topic t : ts) {System.out.println(t.getId() + " " + t.getTitle());}s.getTransaction().commit();}@Testpublic void testHQL_24() {Session s = sf.getCurrentSession();s.beginTransaction();Query q = s.createQuery("select t.title , count(*) from Topic t group by t.title");List<Object[]> objs = (List<Object[]>)q.list();for(Object[] obj : objs) {System.out.println(obj[0] + " " + obj[1]);}s.getTransaction().commit();}@Testpublic void testHQL_25() {Session s = sf.getCurrentSession();s.beginTransaction();Query q = s.createQuery("select t.title , count(*) from Topic t group by t.title having count(*) >= 1");List<Object[]> objs = (List<Object[]>)q.list();for(Object[] obj : objs) {System.out.println(obj[0] + " " + obj[1]);}s.getTransaction().commit();}@Testpublic void testHQL_26() {Session s = sf.getCurrentSession();s.beginTransaction();Query q = s.createQuery("from Topic t where t.id < (select avg(t.id) from Topic t)");List<Topic> ts = q.list();for(Topic t : ts) {System.out.println(t.getId() + " " + t.getTitle());}s.getTransaction().commit();}@Testpublic void testHQL_27() {Session s = sf.getCurrentSession();s.beginTransaction();Query q = s.createQuery("from Topic t where t.id < ALL (select t.id from Topic t where mod(t.id , 2) = 0)");List<Topic> ts = (List<Topic>)q.list();for(Topic t : ts) {System.out.println(t.getId() + "  " + t.getTitle());}s.getTransaction().commit();}//exists 可以用in来实现，如果exists 执行效率高@Testpublic void testHQL_28() {Session s = sf.getCurrentSession();s.beginTransaction();//Query q = s.createQuery("from Topic t where not exists (select m.id from Msg m where m.topic.id = t.id)");Query q = s.createQuery("from Topic t where t.id in (select t.id from Msg m where m.topic.id != t.id)");List<Topic> ts = (List<Topic>)q.list();for(Topic t : ts) {System.out.println(t.getId() + " " + t.getTitle());}s.getTransaction().commit();}@Testpublic void testHQL_29() {Session s = sf.getCurrentSession();s.beginTransaction();Query q = s.createQuery("update Topic t set t.title = upper(t.title)");q.executeUpdate();q = s.createQuery("from Topic");List<Topic> ts = (List<Topic>)q.list();for(Topic t : ts) {System.out.println(t.getId() + " " + t.getTitle());}s.createQuery("update Topic t set t.title = lower(t.title)" ).executeUpdate();s.getTransaction().commit();}@Testpublic void testHQL_30() {Session s = sf.getCurrentSession();s.beginTransaction();SQLQuery sq = s.createSQLQuery("select * from category limit 2,4").addEntity(Category.class);List<Category> cs = (List<Category>)sq.list();for(Category c : cs) {System.out.println(c.getId() + " " + c.getName());}s.getTransaction().commit();}@AfterClasspublic static void afterClass() {sf.close();}}