【hibernate框架】EJBQL第一部分

总结：QL应该和导航关系结合，共同为查询提供服务。导航写全面了，QL语句就可以写的简单。

例子项目：BBS小项目
（EJBQL是HQL的子集，所以直接说是HQL也无可厚非）
Category(版块)：里面有不同的主题，不同的主题有不同的发帖
Topic(主题)，Msg(具体的回复帖子)

Category与Topic之间的关系是一对多(OneToMany)，Topic与Msg的关系也是一对多。(OneToMany)。

具体的类：
Category.java:

package com.bjsxt.hibernate;


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

Topic.java:

package com.bjsxt.hibernate;


import java.util.Date;


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


@Entity
public class Topic {
	private int id;
	private String title;
	private Category category;
	//private Category category2;
	
	private Date createDate;
	
	public Date getCreateDate() {
		return createDate;
	}
	public void setCreateDate(Date createDate) {
		this.createDate = createDate;
	}
	@ManyToOne(fetch=FetchType.LAZY)
	public Category getCategory() {
		return category;
	}
	public void setCategory(Category category) {
		this.category = category;
	}
	@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;
	}
	
}

Msg.java:

package com.bjsxt.hibernate;


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;
	@ManyToOne
	public Topic getTopic() {
		return topic;
	}
	public void setTopic(Topic topic) {
		this.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;
	}
	
}

MsgInfo.java:

package com.bjsxt.hibernate;
public class MsgInfo { //VO DTO Value Object username p1 p2 UserInfo->User->DB
	private int id;
	private String cont;
	private String topicName;
	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 getTopicName() {
		return topicName;
	}
	public void setTopicName(String topicName) {
		this.topicName = topicName;
	}
	public String getCategoryName() {
		return categoryName;
	}
	public void setCategoryName(String categoryName) {
		this.categoryName = categoryName;
	}
	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;
	}
	
}

建表语句：

create table Category (

         id integer not null auto_increment,

         name varchar(255),

         primary key (id)

     )



     create table Msg (

         id integer not null auto_increment,

         cont varchar(255),

         topic_id integer,

         primary key (id)

     )



     create table Topic (

         id integer not null auto_increment,

         createDate datetime,

         title varchar(255),

         category_id integer,

         primary key (id)

     )



     alter table Msg 

         add index FK12F6179FADF6A (topic_id), 

         add constraint FK12F6179FADF6A 

         foreign key (topic_id) 

         references Topic (id)



     alter table Topic 

         add index FK4D3DD0F8F317EEA (category_id), 

         add constraint FK4D3DD0F8F317EEA 

         foreign key (category_id) 

         references Category (id)

具体生成的表:

Category:id<int> name<varchar> 

 Msg:id<int> cont<varchar> topic_id<int>(加外键)

 Topic:id<int> createDate<datetime> title<vachar> category_id<int>(加外键)

先插入一些数据:

@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.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.setCont("m" + i);
			m.setTopic(t);
			session.save(m);
			
		}
		
		session.getTransaction().commit();
		session.close();
	}

存储了10个版块，版块1有10个主题，版块1的主题1有10个帖子。

下面进行查询：

——————————————————————————————————————

@Test
	public void testHQL_01() {
		Session session = sf.openSession();
		session.beginTransaction();
		Query q = session.createQuery("from Category");
		List<Category> categories = (List<Category>)q.list();
		for(Category c : categories) {
			System.out.println(c.getName());
		}
		session.getTransaction().commit();
		session.close();
		
	}

用的是import org.hibernate.Query;

而不是jpa的Query：import javax.persistence.Query;

测试结果：

注意HQL是面向对象的语言，要写类名而不是表名！！！！

Hibernate: 

     select

         category0_.id as id0_,

         category0_.name as name0_ 

     from

         Category category0_

 c0

 c1

 c2

 c3

 c4

 c5

 c6

 c7

 c8

 c9

——————————————————————————————————————

@Test
	public void testHQL_02() {
		Session session = sf.openSession();
		session.beginTransaction();
Query q = session.createQuery("from Category c where c.name > 'c5'");
		List<Category> categories = (List<Category>)q.list();
		for(Category c : categories) {
			System.out.println(c.getName());
		}
		session.getTransaction().commit();
		session.close();
		
	}

测试结果：

因为HQL调试比较麻烦，所以就要观察它输出的sql语句

Hibernate: 

     select

         category0_.id as id0_,

         category0_.name as name0_ 

     from

         Category category0_ 

     where

         category0_.name>'c5'

 c6

 c7

 c8

 c9

——————————————————————————————————————

@Test
	public void testHQL_03() {
		Session session = sf.openSession();
		session.beginTransaction();
		Query q = session.createQuery("from Category c order by c.name desc");
		List<Category> categories = (List<Category>)q.list();
		for(Category c : categories) {
			System.out.println(c.getName());
		}
		session.getTransaction().commit();
		session.close();
		
	}

测试结果：

——————————————————————————————————————

Hibernate: 

     select

         category0_.id as id0_,

         category0_.name as name0_ 

     from

         Category category0_ 

     order by

         category0_.name desc

 c9

 c8

 c7

 c6

 c5

 c4

 c3

 c2

 c1

 c0

@Test
	public void testHQL_04() {
		Session session = sf.openSession();
		session.beginTransaction();
		Query q = session.createQuery("select distinct c from Category c order by c.name desc");
		List<Category> categories = (List<Category>)q.list();
		for(Category c : categories) {
			System.out.println(c.getName());
		}
		session.getTransaction().commit();
		session.close();
		
	}

测试结果：

Hibernate: 

     select

         distinct category0_.id as id0_,

         category0_.name as name0_ 

     from

         Category category0_ 

     order by

         category0_.name desc

 c9

 c8

 c7

 c6

 c5

 c4

 c3

 c2

 c1

 c0

解释：distinct相当于互相之间不会重复，现在说的是对象不会重复（即是主键不同），这里没有变化。

——————————————————————————————————————

@Test
	public void testHQL_05() {
		Session session = sf.openSession();
		session.beginTransaction();


                //1.非链式编程
		/*Query q = session.createQuery("from Category c where c.id > :min and c.id < :max");
		//q.setParameter("min", 2);
		//q.setParameter("max", 8);
		q.setInteger("min", 2);
		q.setInteger("max", 8);*/


                //2.链式编程
		Query q = session.createQuery("from Category c where c.id > :min and c.id < :max")
			.setInteger("min", 2)
			.setInteger("max", 8);
		List<Category> categories = (List<Category>)q.list();
		for(Category c : categories) {
			System.out.println(c.getId() + "-" + c.getName());
		}
		session.getTransaction().commit();
		session.close();
		
	}

解释:Query中的“:”是占位符，由后面的setParameter等方法指定“:”后的参数。

测试结果：

测试2：

Hibernate: 

     select

         category0_.id as id0_,

         category0_.name as name0_ 

     from

         Category category0_ 

     where

         category0_.id>? 

         and category0_.id<?

 3-c2

 4-c3

 5-c4

 6-c5

 7-c6

@Test
	public void testHQL_06() {
		Session session = sf.openSession();
		session.beginTransaction();
		Query q = session.createQuery("from Category c where c.id > ? and c.id < ?");
		q.setParameter(0, 2)
			.setParameter(1, 8);//链式编程
//		q.setParameter(1, 8);
		List<Category> categories = (List<Category>)q.list();
		for(Category c : categories) {
			System.out.println(c.getId() + "-" + c.getName());
		}
		session.getTransaction().commit();
		session.close();
		
	}

结果与上面相同，只不过占位符变成了"?"，用数字指占位符的位置(从0开始)。

——————————————————————————————————————

分页测试：

指定分多少页，每页多少条。

@Test
	public void testHQL_07() {
		Session session = sf.openSession();
		session.beginTransaction();
		Query q = session.createQuery("from Category c order by c.name desc");
		q.setMaxResults(4);//设定最大的结果集，每页4条
		q.setFirstResult(2);//从第二条开始
		List<Category> categories = (List<Category>)q.list();
		for(Category c : categories) {
			System.out.println(c.getId() + "-" + c.getName());
		}
		session.getTransaction().commit();
		session.close();
		
	}

测试结果：

Hibernate: 

     select

         category0_.id as id0_,

         category0_.name as name0_ 

     from

         Category category0_ 

     order by

         category0_.name desc limit ?,

         ?

 8-c7

 7-c6

 6-c5

 5-c4

——————————————————————————————————————

@Test
	public void testHQL_08() {
		Session session = sf.openSession();
		session.beginTransaction();
		Query q = session.createQuery("select c.id,  c.name from Category c order by c.name desc");
		List<Object[]> categories = (List<Object[]>)q.list();
		for(Object[] o : categories) {
                        //这里有自动装箱
			System.out.println(o[0] + "-" + o[1]);
		}
		session.getTransaction().commit();
		session.close();
		
	}

测试结果：

Hibernate: 

     select

         category0_.id as col_0_0_,

         category0_.name as col_1_0_ 

     from

         Category category0_ 

     order by

         category0_.name desc

 10-c9

 9-c8

 8-c7

 7-c6

 6-c5

 5-c4

 4-c3

 3-c2

 2-c1

 1-c0

转换出来之后是一个对象数组。

——————————————————————————————————————

两张表联合查询：

面向对象的导航类型的query语言

//设定fetch type 为lazy后将不会有第二条sql语句(去查询Category)

@Test
	public void testHQL_09() {
		Session session = sf.openSession();
		session.beginTransaction();
		Query q = session.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());
		}
		session.getTransaction().commit();
		session.close();
		
	}

测试结果：

这是著名的"1+n"问题，面试中经常问到。

Hibernate: 

     select

         topic0_.id as id2_,

         topic0_.category_id as category4_2_,

         topic0_.createDate as createDate2_,

         topic0_.title as title2_ 

     from

         Topic topic0_ 

     where

         topic0_.category_id=1

 t0

 t1

 t2

 t3

 t4

 t5

 t6

 t7

 t8

 t9

——————————————————————————————————————

导航之后再导航：

@Test
	public void testHQL_11() {
		Session session = sf.openSession();
		session.beginTransaction();
		Query q = session.createQuery("from Msg m where m.topic.category.id = 1");
		
		for(Object o : q.list()) {
			Msg m = (Msg)o;
			System.out.println(m.getCont());
		}
		session.getTransaction().commit();
		session.close();
		
	}

测试结果：

Hibernate: 

     select

         msg0_.id as id1_,

         msg0_.cont as cont1_,

         msg0_.topic_id as topic3_1_ 

     from

         Msg msg0_ cross 

     join

         Topic topic1_ 

     where

         msg0_.topic_id=topic1_.id 

         and topic1_.category_id=1

 Hibernate: 

     select

         topic0_.id as id2_0_,

         topic0_.category_id as category4_2_0_,

         topic0_.createDate as createDate2_0_,

         topic0_.title as title2_0_ 

     from

         Topic topic0_ 

     where

         topic0_.id=?

 m0

 m1

 m2

 m3

 m4

 m5

 m6

 m7

 m8

 m9

其实就相当于取了版块为1的里面的所有的帖子。他怎么知道哪个和哪个连接呢？那是因为你在实体类中导航了(MangToOne等)。

——————————————————————————————————————

生成了一个自己的类(对象)去接收信息(往往用来生成报表)。

MsgInfo.java:

package com.bjsxt.hibernate;
public class MsgInfo { //VO DTO Value Object username p1 p2 UserInfo->User->DB
	private int id;
	private String cont;
	private String topicName;
	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 getTopicName() {
		return topicName;
	}
	public void setTopicName(String topicName) {
		this.topicName = topicName;
	}
	public String getCategoryName() {
		return categoryName;
	}
	public void setCategoryName(String categoryName) {
		this.categoryName = categoryName;
	}
	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;
	}
	
}

测试代码：

//了解即可
	//VO Value Object(VO对象，用来装临时的值)
	//DTO data transfer object
	@Test
	public void testHQL_12() {
		Session session = sf.openSession();
		session.beginTransaction();
		Query q = session.createQuery("select new com.bjsxt.hibernate.MsgInfo(m.id, m.cont, m.topic.title, m.topic.category.name) from Msg");
		
		for(Object o : q.list()) {
			MsgInfo m = (MsgInfo)o;
			System.out.println(m.getCont());
		}
		session.getTransaction().commit();
		session.close();
		
	}

测试结果：

。。。。。。

——————————————————————————————————————

带关联关系的查询：

//动手测试left right join
	//为什么不能直接写Category名，而必须写t.category
	//因为有可能存在多个成员变量（同一个类），需要指明用哪一个成员变量的连接条件来做连接
	@Test
	public void testHQL_13() {
		Session session = sf.openSession();
		session.beginTransaction();
		Query q = session.createQuery("select t.title, c.name from Topic t join t.category c "); //join Category c
		for(Object o : q.list()) {
			Object[] m = (Object[])o;
			System.out.println(m[0] + "-" + m[1]);
		}
		session.getTransaction().commit();
		session.close();
		
	}

测试结果：

Hibernate: 

     select

         topic0_.title as col_0_0_,

         category1_.name as col_1_0_ 

     from

         Topic topic0_ 

     inner join

         Category category1_ 

             on topic0_.category_id=category1_.id

 t0-c0

 t1-c0

 t2-c0

 t3-c0

 t4-c0

 t5-c0

 t6-c0

 t7-c0

 t8-c0

 t9-c0

——————————————————————————————————————

//学习使用uniqueResult(返回唯一的结果，不要用list去循环了)
	@Test
	public void testHQL_14() {
		Session session = sf.openSession();
		session.beginTransaction();
		Query q = session.createQuery("from Msg m where m = :MsgToSearch "); //不重要
		Msg m = new Msg();
		m.setId(1);
		q.setParameter("MsgToSearch", m);
		
		Msg mResult = (Msg)q.uniqueResult();
		System.out.println(mResult.getCont());
		session.getTransaction().commit();
		session.close();
		
	}

设定一个对象，让HQL语句去找符合这个对象的记录，很像QBE，用的不多

测试结果：

——————————————————————————————————————

Hibernate: 

     select

         msg0_.id as id1_,

         msg0_.cont as cont1_,

         msg0_.topic_id as topic3_1_ 

     from

         Msg msg0_ 

     where

         msg0_.id=?

 Hibernate: //设@ManyToOne(fetch=FetchType.LAZY)就不出这一句了

     select

         topic0_.id as id2_0_,

         topic0_.category_id as category4_2_0_,

         topic0_.createDate as createDate2_0_,

         topic0_.title as title2_0_ 

     from

         Topic topic0_ 

     where

         topic0_.id=?

 m0

@Test
	public void testHQL_15() {
		Session session = sf.openSession();
		session.beginTransaction();
		Query q = session.createQuery("select count(*) from Msg m");
		
		long count = (Long)q.uniqueResult();
		System.out.println(count);
		session.getTransaction().commit();
		session.close();
		
	}

测试结果：

——————————————————————————————————————

Hibernate: 

     select

         count(*) as col_0_0_ 

     from

         Msg msg0_

 10

@Test
	public void testHQL_16() {
		Session session = sf.openSession();
		session.beginTransaction();
		Query q = session.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] + "-" + o[3]);
		session.getTransaction().commit();
		session.close();
		
	}

测试结果：

Hibernate: 

     select

         max(msg0_.id) as col_0_0_,

         min(msg0_.id) as col_1_0_,

         avg(msg0_.id) as col_2_0_,

         sum(msg0_.id) as col_3_0_ 

     from

         Msg msg0_

 10-1-5.5-55

——————————————————————————————————————

@Test
	public void testHQL_17() {
		Session session = sf.openSession();
		session.beginTransaction();
		Query q = session.createQuery("from Msg m where m.id between 3 and 5");
		
		for(Object o : q.list()) {
			Msg m = (Msg)o;
			System.out.println(m.getId() + "-" + m.getCont());
		}
		session.getTransaction().commit();
		session.close();
		
	}

测试结果：

——————————————————————————————————————

Hibernate: 

     select

         msg0_.id as id1_,

         msg0_.cont as cont1_,

         msg0_.topic_id as topic3_1_ 

     from

         Msg msg0_ 

     where

         msg0_.id between 3 and 5

 Hibernate: //设@ManyToOne(fetch=FetchType.LAZY)就不出这一句了

     select

         topic0_.id as id2_0_,

         topic0_.category_id as category4_2_0_,

         topic0_.createDate as createDate2_0_,

         topic0_.title as title2_0_ 

     from

         Topic topic0_ 

     where

         topic0_.id=?

 3-m2

 4-m3

 5-m4

@Test
	public void testHQL_18() {
		Session session = sf.openSession();
		session.beginTransaction();
		Query q = session.createQuery("from Msg m where m.id in (3,4, 5)");
		
		for(Object o : q.list()) {
			Msg m = (Msg)o;
			System.out.println(m.getId() + "-" + m.getCont());
		}
		session.getTransaction().commit();
		session.close();
		
	}

测试结果：

Hibernate: 

     select

         msg0_.id as id1_,

         msg0_.cont as cont1_,

         msg0_.topic_id as topic3_1_ 

     from

         Msg msg0_ 

     where

         msg0_.id in (

             3 , 4 , 5

         )

 Hibernate: //设@ManyToOne(fetch=FetchType.LAZY)就不出这一句了

     select

         topic0_.id as id2_0_,

         topic0_.category_id as category4_2_0_,

         topic0_.createDate as createDate2_0_,

         topic0_.title as title2_0_ 

     from

         Topic topic0_ 

     where

         topic0_.id=?

 3-m2

 4-m3

 5-m4

——————————————————————————————————————

//is null 与 is not null
	@Test
	public void testHQL_19() {
		Session session = sf.openSession();
		session.beginTransaction();
		Query q = session.createQuery("from Msg m where m.cont is not null");
		
		for(Object o : q.list()) {
			Msg m = (Msg)o;
			System.out.println(m.getId() + "-" + m.getCont());
		}
		session.getTransaction().commit();
		session.close();
		
	}

测试结果：

Hibernate: 

     select

         msg0_.id as id1_,

         msg0_.cont as cont1_,

         msg0_.topic_id as topic3_1_ 

     from

         Msg msg0_ 

     where

         msg0_.cont is not null

 Hibernate: //设@ManyToOne(fetch=FetchType.LAZY)就不出这一句了

     select

         topic0_.id as id2_0_,

         topic0_.category_id as category4_2_0_,

         topic0_.createDate as createDate2_0_,

         topic0_.title as title2_0_ 

     from

         Topic topic0_ 

     where

         topic0_.id=?

 1-m0

 2-m1

 3-m2

 4-m3

 5-m4

 6-m5

 7-m6

 8-m7

 9-m8

10-m9

余下的请看《EJBQL第二部分》