Hibernate-HQL-QBC-QBE

例子:

 

Entity->

 

package com.hibernate.model;

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;
 }
 
 

}

 

 

package com.hibernate.model;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.OneToMany;


@Entity

@NamedQueries(
  {
   @NamedQuery(name="topic.selectCertainTopic",query="from Topic t where t.id = :id")
  }
)


public class Topic {
 private int id;
 private String title;
 private Category category;
 private Date createDate;
 private List<Msg> msgs = new ArrayList<Msg>();
 
 
 
 @OneToMany(mappedBy="topic")
 public List<Msg> getMsgs() {
  return msgs;
 }
 public void setMsgs(List<Msg> msgs) {
  this.msgs = msgs;
 }
 public Date getCreateDate() {
  return createDate;
 }
 public void setCreateDate(Date createDate) {
  this.createDate = createDate;
 }
 @ManyToOne(fetch=FetchType.LAZY)
 public Category getCategory() {
  return category;
 }
 @Id
 @GeneratedValue
 public int getId() {
  return id;
 }
 public String getTitle() {
  return title;
 }
 public void setCategory(Category category) {
  this.category = category;
 }
 public void setId(int id) {
  this.id = id;
 }
 public void setTitle(String title) {
  this.title = title;
 }
 

}


 

 

package com.hibernate.model;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
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
 public Topic getTopic() {
  return topic;
 }
 public void setTopic(Topic topic) {
  this.topic = topic;
 }
 
}


 

 

package com.hibernate.model;

public class MsgInfo {
 
 private int id;
 private String topicName;
 private String cont;
 private String categoryName;
 
 public MsgInfo(int id, String cont, String topicName, String categoryName) {
  super();
  this.id = id;
  this.cont = cont;
  this.topicName = topicName;
  this.categoryName = categoryName;
 }

 public String getCategoryName() {
  return categoryName;
 }

 public String getCont() {
  return cont;
 }

 public int getId() {
  return id;
 }

 public String getTopicName() {
  return topicName;
 }

 public void setCategoryName(String categoryName) {
  this.categoryName = categoryName;
 }

 public void setCont(String cont) {
  this.cont = cont;
 }

 public void setId(int id) {
  this.id = id;
 }

 public void setTopicName(String topicName) {
  this.topicName = topicName;
 }
 
 

}


 

 

测试->

 

package com.hibernate.test;

import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.tool.hbm2ddl.SchemaExport;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;

import com.hibernate.model.Category;
import com.hibernate.model.Msg;
import com.hibernate.model.MsgInfo;
import com.hibernate.model.Topic;

public class HQLTest {

 private static SessionFactory sf = null;

 @BeforeClass
 public static void beforeClass() {
  // new SchemaExport(new
  // AnnotationConfiguration().configure()).create(false, true);
  sf = new AnnotationConfiguration().configure().buildSessionFactory();
 }

 @AfterClass
 public static void afterClass() {
  sf.close();
 }

 @Test
 public void testSchemaExport() {
  new SchemaExport(new AnnotationConfiguration().configure()).create(
    false, true);
 }

 public static void main(String[] args) {
  new HQLTest().testSchemaExport();
 }

 // 保存
 @Test
 public void testSave() {
  Session session = sf.getCurrentSession();
  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.setCategory(c);
   t.setTitle("t" + i);
   t.setCreateDate(new Date());
   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();

 }

 @Test
 public void testHQL_01() {
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q = s.createQuery("from Category");
  List<Category> categorys = (List<Category>) q.list();

  for (Category c : categorys) {
   System.out.println(c.getName());
  }

  s.getTransaction().commit();
 }

 @Test
 public void testHQL_02() {
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q = s.createQuery("from Category c where c.name> 'c5'");
  List<Category> categorys = (List<Category>) q.list();

  for (Category c : categorys) {
   System.out.println(c.getName());
  }

  s.getTransaction().commit();
 }

 @Test
 public void testHQL_03() {
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q = s.createQuery("from Category  c order by c.name desc");
  List<Category> categorys = (List<Category>) q.list();

  for (Category c : categorys) {
   System.out.println(c.getName());
  }
  s.getTransaction().commit();
 }

 @Test
 public void testHQL_04() {
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q = s.createQuery("select distinct c from Category c order by c.name desc");
  List<Category> categorys = (List<Category>) q.list();

  for (Category c : categorys) {
   System.out.println(c.getName());
  }
  s.getTransaction().commit();
 }
 
 
 @Test
 public void testHQL_05(){
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q = s.createQuery("from Category c  where c.id > :min and c.id < :max")
     .setInteger("min", 2)
     .setInteger("max", 8)
  ;
  
  List<Category> categorys = (List<Category>) q.list();

  for (Category c : categorys) {
   System.out.println(c.getName());
  }
  s.getTransaction().commit();
  
  
 }
 
 
 @Test
 public void testHQL_06(){
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q = s.createQuery("from Category c  where c.id > ? and c.id < ?")
     .setParameter(0, 2)
     .setParameter(1, 8)
  ;
  
  List<Category> categorys = (List<Category>) q.list();

  for (Category c : categorys) {
   System.out.println(c.getName());
  }
  s.getTransaction().commit();
  
 }
 
 
 //分页
 @Test
 public void testHQL_07(){
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q = s.createQuery("from Category c order by  c.name asc");
  q.setMaxResults(4);
  q.setFirstResult(2);
  
  List<Category> categorys = (List<Category>) q.list();
  for (Category c : categorys) {
   System.out.println(c.getId() + "-" + c.getName());
  }
  s.getTransaction().commit();
 }
 
 
 @Test
 public void testHQL_08(){
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q = s.createQuery("select c.id, c.name from Category c order by c.name desc");
  List<Object[]> cs = (List<Object[]>)q.list();
  
  for(Object[] o : cs){
   System.out.println(o[0] + "--"+ o[1]);
  }
  
  s.getTransaction().commit();
 }
 
 //设定fetch type 为lazy后将不会有第二条sql语句
 @Test
 public void testHQL_09(){
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q = s.createQuery("from Topic t where t.category.id = 1");
  List<Topic> topics = (List<Topic>)q.list();
  for(Topic t: topics){
   System.out.println(t.getTitle());
   
  }
  s.getTransaction().commit();
  
 }
 
 @Test
 public void testHQL_011(){
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q = s.createQuery("from Msg m where m.topic.category.id = 1");
  
  for(Object o : q.list()){
   Msg m = (Msg)o;
   System.out.println(m.getCont());
  }
  
  s.getTransaction().commit();
  
 }
 
 //DTO VO
 @Test
 public void testHQL_012(){
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q = s.createQuery("select new com.hibernate.model.MsgInfo(m.id,m.cont,m.topic.title,m.topic.category.name) from Msg m") ;
  
  for(Object o : q.list()){
   MsgInfo m = (MsgInfo)o;
   System.out.println(m.getCont());
  }
  
  s.getTransaction().commit();
 }
 
 
 @Test
 public void testHQL_013(){
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q = s.createQuery("select t.title, c.name from Topic t join t.category c");
  for(Object o :q.list()){
   Object[] m = (Object[])o;
   System.out.println(m[0]+"---"+ m[1]);
  }
  
  s.getTransaction().commit();
  
 }
 
 @Test
 public void testHQL_014(){
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q = s.createQuery("from Msg m where m = :m");
  Msg m = new Msg();
  m.setId(1);
  q.setParameter("m", m);
  Msg mResult = (Msg)q.uniqueResult();
  System.out.println(mResult.getCont());
  s.getTransaction().commit();
 }
 
 
 @Test
 public void testHQL_015(){
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q =  s.createQuery("select count(*) from Msg m");
  long  count = (Long)q.uniqueResult();
  System.out.println(count);
  s.getTransaction().commit();
 }
 
 @Test
 public void testHQL_016(){
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q = s.createQuery("select max(m.id), min(m.id), avg(m.id),sum(m.id) from Msg m");
  
  Object[] o  = (Object[])q.uniqueResult();
  System.out.println(o[0] +"----"+ o[1] + "--" + o[2]);
  
  s.getTransaction().commit();
 }
 
 @Test
 public void testHQL_017(){
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q =  s.createQuery("from Msg m where m.id between 3 and 5");
  
  for(Object o : q.list()){
   Msg m = (Msg)o;
   System.out.println(m.getCont());
  }
  
  s.getTransaction().commit();
 }
 
 
 @Test
 public void testHQL_018(){
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q =  s.createQuery("from Msg m where m.id in(4,5,6)");
  for(Object o : q.list()){
   Msg m = (Msg)o;
   System.out.println(m.getCont());
  }
  s.getTransaction().commit();
 }
 
 @Test
 public void testHQL_020(){
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q = s.createQuery("from Topic t where t.msgs is empty ");
  
  for(Object o : q.list()){
   Topic t = (Topic)o;
   System.out.println(t.getId() + "--" + t.getTitle());
  }
  s.getTransaction().commit();
 }
 
 
 @Test
 public void testHQL_019(){
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q =  s.createQuery("from Msg m where m.cont is not null ");
  for(Object o : q.list()){
   Msg m = (Msg)o;
   System.out.println(m.getCont());
  }
  s.getTransaction().commit();
 }
 
 
 @Test
 public void testHQL_021(){
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q = s.createQuery("from Topic t where t.title like '%5'");
  
  for(Object o : q.list()){
   Topic t = (Topic)o;
   System.out.println(t.getId() + "--" + t.getTitle());
  }
  s.getTransaction().commit();
 }
 
 @Test
 public void testHQL_022(){
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q = s.createQuery("from Topic t where t.title like '_5'");
  
  for(Object o : q.list()){
   Topic t = (Topic)o;
   System.out.println(t.getId() + "--" + t.getTitle());
  }
  s.getTransaction().commit();
 }
 

 @Test
 public void testHQL_023(){
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q = s.createQuery("select lower(t.title), upper(t.title),trim(t.title), " +
    "concat(t.title,'****'),length(t.title) from Topic t");
  
  for(Object o : q.list()){
   Object[] b = (Object[])o;
   System.out.println(b[0]+"--"+b[1]+"--"+b[2]+"--"+b[3]+"--"+b[4]+"--");
  }
  s.getTransaction().commit();
 }
 
 
 @Test
 public void testHQL_024(){
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q = s.createQuery("select abs(t.id), sqrt(t.id),mod(t.id,2) from Topic t");
  
  for(Object o : q.list()){
   Object[] b = (Object[])o;
   System.out.println(b[0]+"--"+b[1]+"--"+b[2]+"--");
  }
  s.getTransaction().commit();
 }
 
 @Test
 public void testHQL_025(){
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q = s.createQuery("select current_date,current_time,current_timestamp,t.id from Topic t");
  
  for(Object o : q.list()){
   Object[] b = (Object[])o;
   System.out.println(b[0]+"--"+b[1]+"--"+b[2]+"--"+b[3]);
  }
  s.getTransaction().commit();
 }
 
 
 @Test
 public void testHQL_026(){
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q = s.createQuery("from Topic t where t.createDate < :date");
  q.setParameter("date", new Date());
  
  for(Object o : q.list()){
   Topic t = (Topic)o;
   System.out.println(t.getTitle());
  }
  s.getTransaction().commit();
 }
 
 
 @Test
 public void testHQL_027(){
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q = s.createQuery("select t.title ,count(*) from Topic t group by t.title");
  
  for(Object o : q.list()){
   Object[] b = (Object[])o;
   System.out.println(b[0]+"--"+b[1]);
  }
  s.getTransaction().commit();
 }
 
 @Test
 public void testHQL_028(){
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q = s.createQuery("select t.title ,count(*) from Topic t group by t.title having count(*) >=1");
  
  for(Object o : q.list()){
   Object[] b = (Object[])o;
   System.out.println(b[0]+"--"+b[1]);
  }
  s.getTransaction().commit();
 }
 

 
 
 @Test
 public void testHQL_029(){
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q = s.createQuery("from Topic t where t.id <(select avg(t.id) from Topic t)");
  
  for(Object o : q.list()){
   Topic t = (Topic)o;
   System.out.println(t.getTitle());
  }
  s.getTransaction().commit();
 }
 
 
 
 @Test
 public void testHQL_030(){
  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 )");
  
  for(Object o : q.list()){
   Topic t = (Topic)o;
   System.out.println(t.getTitle());
  }
  s.getTransaction().commit();
 }
 
 
 @Test
 public void testHQL_031(){
  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 = t.id )");
  
  for(Object o : q.list()){
   Topic t = (Topic)o;
   System.out.println(t.getTitle());
  }
  s.getTransaction().commit();
 }
 
 
 @Test
 public void testHQL_032(){
  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");
  
  for(Object o : q.list()){
   Topic t = (Topic)o;
   System.out.println(t.getTitle());
  }
  
  s.createQuery("update Topic t set t.title = lower(t.title)").executeUpdate();
  
  s.getTransaction().commit();
 }
 
 @Test
 public void testHQL_033(){
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q = s.getNamedQuery("topic.selectCertainTopic"); 
  q.setParameter("id", 5);
  
  Topic t = (Topic)q.uniqueResult();
  System.out.println(t.getTitle());
  
  
  
  s.getTransaction().commit();
 }
 
 
 //NativeSQL
 @Test
 public void testHQL_034(){
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q = s.createSQLQuery("select * from category limit 2,4").addEntity(Category.class);
  
  List<Category> categories = (List<Category>)q.list();
  for(Category c : categories) {
   System.out.println(c.getName());
  }
  
  
  
  s.getTransaction().commit();
 }
 
 
 

}

 

 

 

 

 

//QBC*********************************************
	
	@Test
	public void testQBC(){
		Session s = sf.getCurrentSession();
		s.beginTransaction();
		Criteria c = s.createCriteria(Topic.class)
		.add(Restrictions.gt("id", 2))
		.add(Restrictions.lt("id", 8))
		.add(Restrictions.like("title", "t_"));
		
		for(Object o: c.list()){
			Topic t = (Topic)o;
			System.out.println(t.getTitle());
		}
		s.getTransaction().commit();
		
	}
	
	
	//QBE************************************************
	

	@Test
	public void testQBE(){
		Session s = sf.getCurrentSession();
		s.beginTransaction();
		Topic tExample  = new Topic();
		tExample.setTitle("T_");
		Example e = Example.create(tExample ).ignoreCase().enableLike();
		
		Criteria c  = s.createCriteria(Topic.class)
						.add(Restrictions.gt("id", 2))
						.add(Restrictions.lt("id", 8))
						.add(e);
		
		for(Object o : c.list()) {
			Topic t = (Topic)o;
			System.out.println(t.getId() + "-" + t.getTitle());
		}
		
		s.getTransaction().commit();
		
	}