HQL 简单使用二

HQL的简单使用2

1、 Category类

package com.edu.hpu;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

@Entity
public class Category {

	private int id;
	private String name;
	
	@Id
	@GeneratedValue
	public 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;
	}
}

2、 Msg类

package com.edu.hpu;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;

@Entity
public class Msg {

	private int id;
	private String cont;
	private Topic topic;
	
	@Id
	@GeneratedValue
	public 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;
	}
}

3、 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;

@Entity
public class Topic {

	private int id;
	private String title;
	private Date date;
	private Category category;
	private Set<Msg> msgs = new HashSet<Msg>();
	
	@Id
	@GeneratedValue
	public 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;
	}
}

4、 测试类

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;
	
	@BeforeClass
	public static void beforeClass() {
		Configuration conf = new Configuration().configure();
		ServiceRegistry sr = new ServiceRegistryBuilder().applySettings(conf.getProperties()).buildServiceRegistry();
		sf = conf.buildSessionFactory(sr);
	}
	
	@Test
	public void testExport() {
		new SchemaExport(new Configuration().configure()).create(true ,true);
	}
	
	@Test
	public 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();
	}
	
	@Test
	public 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 % 代表零个或多个
	@Test
	public 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 % 代表一个或多个
	@Test
	public 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();
	}
	
	@Test
	public 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();
	}
	
	@Test
	public 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();
	}
	
	@Test
	public 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();
	}

	@Test
	public 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();
	}
	
	@Test
	public 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();
	}
	
	@Test
	public 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 执行效率高
	@Test
	public 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();
	}
	
	@Test
	public 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();
	}
	
	@Test
	public 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();
	}
	
	@AfterClass
	public static void afterClass() {
		sf.close();
	}
}