栏目、帖子、回复（设置导航ManyToOne便于操作 QBC/QBE语句）02

于 2012-10-06 11:39:44 发布
阅读量94 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： Hibernate 4.1 文章标签： HQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/mohaiyong/article/details/84297559
[align=center][size=medium][color=red][b]栏目、帖子、回复（设置导航ManyToOne便于操作QBC/QBE语句）02[/b][/color][/size][/align]


[size=medium][color=darkred][b]Criteria查询也称QBC查询[/b][/color][/size]：全称为Query By Criteria;它是一种采用面向
对象的方式，封装查询条件，进行查询。

[size=medium][color=darkred][b]CriteriaRestriction常用的限定查询方法方法说明[/b][/color][/size]
Restrictions.Eq（）对应SQL中的“= ”
Restrictions。alleq（）对应Map,使用key/value进行多个相等的值的对比
Restrictions。gt（）对应SQL的大于“> ”;
Restrictions。ge（）对应SQL的大于等于“>= ”
Restrictions。lt（）对应SQL的小于“<”
Restrictions。le（）对应SQL的小于等于“=<”Restrictions。le（）对应SQL的小于等于“=<”
Restrictions。between（）对应SQL的between ；
Restrictions。like（）对应SQL中的like语句可以使用MatchMode 设定like 语句的匹配模式
Restrictions。in（）对应SQL中的in ；
Restrictions。and（）对应SQL中的and
Restrictions。or（）对应SQL中的or
Restrictions。not（）对应SQL中的not


[size=small][color=red][b]一、Category.java[/b][/color][/size] 


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



[size=small][color=red][b]二、Top.java[/b][/color][/size] 


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")
		}
		)
		/*
@NamedNativeQueries(
		{
			@NamedNativeQuery(name="topic.select2_5Topic", query="select * from topic limit 2, 5")
		}
		)
		*/

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

}


[size=small][color=red][b]三、Msg.java[/b][/color][/size] 



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

}



[size=small][color=red][b]四、test.java[/b][/color][/size] 



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.AnnotationConfiguration;
import org.hibernate.tool.hbm2ddl.SchemaExport;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;

public class HibernateQLTest {
	private static SessionFactory sf;

	@BeforeClass
	public static void beforeClass() {
		sf = new AnnotationConfiguration().configure().buildSessionFactory();
	}
	@AfterClass
	public static void afterClass() {
		sf.close();
	}

	@Test
	public void testSchemaExport() {
		new SchemaExport(new AnnotationConfiguration().configure()).create(false, 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.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();
	}

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

	}

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

	}

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

	}
	//不重要
	@Test
	public void testHQL_23() {
		Session session = sf.openSession();
		session.beginTransaction();
		Query q = session.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[] arr = (Object[])o;
			System.out.println(arr[0] + "-" + arr[1] + "-" + arr[2] + "-" + arr[3] + "-" + arr[4] + "-");
		}
		session.getTransaction().commit();
		session.close();

	}

	@Test
	public void testHQL_24() {
		Session session = sf.openSession();
		session.beginTransaction();
		Query q = session.createQuery("select abs(t.id)," +
											 "sqrt(t.id)," +
											 "mod(t.id, 2)" +
											 " from Topic t ");

		for(Object o : q.list()) {
			Object[] arr = (Object[])o;
			System.out.println(arr[0] + "-" + arr[1] + "-" + arr[2] );
		}
		session.getTransaction().commit();
		session.close();

	}

	@Test
	public void testHQL_25() {
		Session session = sf.openSession();
		session.beginTransaction();
		Query q = session.createQuery("select current_date, current_time, current_timestamp, t.id from Topic t");

		for(Object o : q.list()) {
			Object[] arr = (Object[])o;
			System.out.println(arr[0] + " | " + arr[1] + " | " + arr[2] + " | " + arr[3]);
		}
		session.getTransaction().commit();
		session.close();

	}

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

	}

	@Test
	public void testHQL_27() {
		Session session = sf.openSession();
		session.beginTransaction();
		Query q = session.createQuery("select t.title, count(*) from Topic t group by t.title") ;
		for(Object o : q.list()) {
			Object[] arr = (Object[])o;
			System.out.println(arr[0] + "|" + arr[1]);
		}
		session.getTransaction().commit();
		session.close();

	}

	@Test
	public void testHQL_28() {
		Session session = sf.openSession();
		session.beginTransaction();
		Query q = session.createQuery("select t.title, count(*) from Topic t group by t.title having count(*) >= 1") ;
		for(Object o : q.list()) {
			Object[] arr = (Object[])o;
			System.out.println(arr[0] + "|" + arr[1]);
		}
		session.getTransaction().commit();
		session.close();

	}

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

	}

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

	}

	//用in 可以实现exists的功能
	//但是exists执行效率高
	@Test
	public void testHQL_31() {
		Session session = sf.openSession();
		session.beginTransaction();// t.id not in (1)
		Query q = session.createQuery("from Topic t where not exists (select m.id from Msg m where m.topic.id=t.id)") ;
//		Query q = session.createQuery("from Topic t where exists (select m.id from Msg m where m.topic.id=t.id)") ;
		for(Object o : q.list()) {
			Topic t = (Topic)o;
			System.out.println(t.getTitle());
		}
		session.getTransaction().commit();
		session.close();

	}

	//update and delete
	//规范并没有说明是不是要更新persistent object，所以如果要使用，建议在单独的trasaction中执行

	@Test
	public void testHQL_32() {
		Session session = sf.openSession();
		session.beginTransaction();
		Query q = session.createQuery("update Topic t set t.title = upper(t.title)") ;

		q.executeUpdate();
		q = session.createQuery("from Topic");
		for(Object o : q.list()) {
			Topic t = (Topic)o;
			System.out.println(t.getTitle());
		}
		session.createQuery("update Topic t set t.title = lower(t.title)")
			.executeUpdate();
		session.getTransaction().commit();
		session.close();

	}

	//不重要
	@Test
	public void testHQL_33() {
		Session session = sf.openSession();
		session.beginTransaction();
		Query q = session.getNamedQuery("topic.selectCertainTopic");
		q.setParameter("id", 5);
		Topic t = (Topic)q.uniqueResult();
		System.out.println(t.getTitle());
		session.getTransaction().commit();
		session.close();

	}

	//Native（了解）
	@Test
	public void testHQL_34() {
		Session session = sf.openSession();
		session.beginTransaction();
		SQLQuery q = session.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());
		}
		session.getTransaction().commit();
		session.close();

	}

	@Test
	public void testHQL_35() {
		//尚未实现JPA命名的NativeSQL

	}

	public static void main(String[] args) {
		beforeClass();
	}
}
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值