Hibernate 使用 Annotation 6（各种查询语句）

Category.java代码：

package com.jlee06.QL;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

/**
 * @author JLee
 * 板块
 */
@Entity
@Table(name="Category")
public class Category {

	private int id ;
	private String name ;
	
	@Id
	@GeneratedValue(strategy=GenerationType.AUTO)
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	
	@Column(name="name")
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	
}

 

 

Msg.java代码：

package com.jlee06.QL;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

@Entity
@Table(name="Msg")
public class Msg {

	private int id ;
	private String cont ;
	private Topic topic ;

	@Id
	@GeneratedValue(strategy=GenerationType.AUTO)
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	
	@Column(name="cont" , length=500)
	public String getCont() {
		return cont;
	}
	public void setCont(String cont) {
		this.cont = cont;
	}
	
	@ManyToOne(fetch=FetchType.LAZY)
	@JoinColumn(name="topicId")
	public Topic getTopic() {
		return topic;
	}
	public void setTopic(Topic topic) {
		this.topic = topic;
	}
	
}

 

 

Topic.java代码：

package com.jlee06.QL;

import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

/**
 * @author JLee
 * 主题
 */
@Entity
@Table(name="topic")
@NamedQueries({
	@NamedQuery(name="Topic.selectTopic" , query="from Topic t where t.id = :id ") , 
	@NamedQuery(name="Topic.conditionTopic" , query="from Topic t where t.title like :title ")
})
//hibernate3.3.2尚未支持
//@NamedNativeQueries({
//	@NamedNativeQuery(name="native_sql_page" , query="select * from topic limit 2,5")
//})
public class Topic {
	
	private int id ;
	private String title ;
	private Date createDate ;
	public Date getCreateDate() {
		return createDate;
	}
	
	@Column(name="createDate")
	@Temporal(TemporalType.DATE)
	public void setCreateDate(Date createDate) {
		this.createDate = createDate;
	}
	private Category category ;

	@Id
	@GeneratedValue(strategy=GenerationType.AUTO)
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	
	@Column(name="title" , length=32)
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	
	@ManyToOne(fetch=FetchType.LAZY)
	@JoinColumn(name="categoryId")
	public Category getCategory() {
		return category;
	}
	public void setCategory(Category category) {
		this.category = category;
	}
	
}

 

 

MsgInfo.java代码：

package com.jlee06.QL;

/**
 * VO Value Object
 * @author JLee
 *	查询使用的 值对象
 */
public class MsgInfo {

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

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

 

 

DataBase.java代码：

package com.jlee06.QL;

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 DataBase {

	private static SessionFactory sf ;
	@BeforeClass
	public static void beforeClass(){
		sf = new AnnotationConfiguration().configure().buildSessionFactory() ;
	}
	
	//@AfterClass
	//public void afterClass(){
	//	sf.close() ;
	//}
	
	@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.setCont("m" + i);
			m.setTopic(t);
			session.save(m);
		}
		
		session.getTransaction().commit();
	}
	
	/**
	 * HQL面向对象查询语句
	 * SQL语句
	 * 		select
		        category0_.id as id0_,
		        category0_.name as name0_ 
		    from
		        Category category0_
	 */
	@Test
	public void testHQL_01(){
		Session session = sf.openSession() ;
		session.beginTransaction() ;
		
		Query q = session.createQuery("from Category") ;
		List<Category> categorys = (List<Category>)q.list() ;
		for(Category c : categorys){
			System.out.println(c.getName());
		}
		session.getTransaction().commit() ;
		session.close() ;
	}
	
	/**
	 * 设置 Where 条件语句
	 * SQL 语句
	 * 		select
		        category0_.id as id0_,
		        category0_.name as name0_ 
		    from
		        Category category0_ 
		    where
		        category0_.name>'c5'
	 */
	@Test
	public void testHQL_02(){
		Session session = sf.getCurrentSession() ;
		session.beginTransaction() ;
		
		Query q = session.createQuery("from Category c where c.name > 'c5' ") ;
		List<Category> categorys = (List<Category>)q.list() ;
		for(Category c : categorys){
			System.out.println(c.getName());
		}
		session.getTransaction().commit() ;
	}
	
	/**
	 * 使用 order by 排序语句
	 * SQL 语句
	 * 		select
		        category0_.id as id0_,
		        category0_.name as name0_ 
		    from
		        Category category0_ 
		    order by
		        category0_.name desc
	 */
	@Test
	public void testHQL_03(){
		Session session = sf.getCurrentSession() ;
		session.beginTransaction() ;
		
		Query q = session.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());
		}
		session.getTransaction().commit() ;
	}
	
	/**
	 * 使用 distinct 
	 * SQL语句
	 * 			select
			        distinct category0_.id as id0_,
			        category0_.name as name0_ 
			    from
			        Category category0_ 
			    order by
			        category0_.name desc
	 */
	@Test
	public void testHQL_04(){
		Session session = sf.getCurrentSession() ;
		session.beginTransaction() ;
		
		Query q = session.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());
		}
		session.getTransaction().commit() ;
	}
	
	/**
	 * 在SQL语句中设置参数1
	 * 		select
		        category0_.id as id0_,
		        category0_.name as name0_ 
		    from
		        Category category0_ 
		    where
		        category0_.id>? 
		        and category0_.id<?
	 *	分开设置参数
	 */
	@Test
	public void testHQL_05(){
		Session session = sf.getCurrentSession() ;
		session.beginTransaction() ;
		
		Query q = session.createQuery("from Category c where c.id > :min and c.id < :max ") ;
		q.setParameter("min", 2) ;
		q.setParameter("max", 5) ;
		List<Category> categorys = (List<Category>)q.list() ;
		for(Category c : categorys){
			System.out.println(c.getId()+"     "+c.getName());
		}
		session.getTransaction().commit() ;
	}
	
	/**
	 * 在SQL语句中设置参数2
	 * 		select
		        category0_.id as id0_,
		        category0_.name as name0_ 
		    from
		        Category category0_ 
		    where
		        category0_.id>? 
		        and category0_.id<?
	 *	级联设置参数
	 */
	@Test
	public void testHQL_06(){
		Session session = sf.getCurrentSession() ;
		session.beginTransaction() ;
		
		Query q = session.createQuery("from Category c where c.id > :min and c.id < :max ")
				.setInteger("min", 2)
				.setInteger("max", 5) ;
		List<Category> categorys = (List<Category>)q.list() ;
		for(Category c : categorys){
			System.out.println(c.getId()+"     "+c.getName());
		}
		session.getTransaction().commit() ;
	}
	
	/**
	 * 查询结果 进行 分页
	 * SQL语句（MySQL分页语句）
	 * 		select
		        category0_.id as id0_,
		        category0_.name as name0_ 
		    from
		        Category category0_ 
		    order by
		        category0_.name desc limit ?,
		        ?
	 */
	@Test
	public void testHQL_07(){
		Session session = sf.getCurrentSession() ;
		session.beginTransaction() ;
		
		Query q = session.createQuery("from Category c order by c.name desc ") ;
		q.setMaxResults(5) ;
		q.setFirstResult(3) ;
		List<Category> categorys = (List<Category>)q.list() ;
		for(Category c : categorys){
			System.out.println(c.getId()+"     "+c.getName());
		}
		session.getTransaction().commit() ;
	}
	
	/**
	 * 查询指定字段
	 * SQL 语句
	 * 		select
		        category0_.id as col_0_0_,
		        category0_.name as col_1_0_ 
		    from
		        Category category0_ 
		    where
		        category0_.id>? 
		        and category0_.id<?
	 *	查询结果为 数组
	 *	@return Object[]
	 */
	@Test
	public void testHQL_08(){
		Session session = sf.getCurrentSession() ;
		session.beginTransaction() ;
		
		Query q = session.createQuery("select c.id , c.name from Category c where c.id > :min and c.id < :max ")
				.setInteger("min", 2)
				.setInteger("max", 5) ;
		List<Object[]> categorys = (List<Object[]>)q.list() ;
		for(Object[] o : categorys){
			System.out.println(o[0]+"     "+o[1]);
		}
		session.getTransaction().commit() ;
	}
	
	/**
	 * 两张表关联查询
	 * 设置 fetch type 为 lazy 后将不会有第二条SQL语句
	 * SQL语句：
	 * 1.fetch=FetchType.LAZY
	 * 			select
			        topic0_.id as id2_,
			        topic0_.categoryId as categoryId2_,
			        topic0_.createDate as createDate2_,
			        topic0_.title as title2_ 
			    from
			        topic topic0_ 
			    where
			        topic0_.categoryId=1
	 *2.fetch=FetchType.EAGER
	 *		select
		        topic0_.id as id2_,
		        topic0_.categoryId as categoryId2_,
		        topic0_.createDate as createDate2_,
		        topic0_.title as title2_ 
		    from
		        topic topic0_ 
		    where
		        topic0_.categoryId=1
        
	 *		select
		        category0_.id as id0_0_,
		        category0_.name as name0_0_ 
		    from
		        Category category0_ 
		    where
		        category0_.id=?
	 */
	@Test
	public void testHQL_09(){
		Session session = sf.getCurrentSession() ;
		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.getId()+"     "+t.getTitle());
		}
		session.getTransaction().commit() ;
	}
	
	/**
	 * 多表关联查询
	 * fetch type
	 * 1 LAZY SQL语句
	 * 		select
		        msg0_.id as id1_,
		        msg0_.cont as cont1_,
		        msg0_.topicId as topicId1_ 
		    from
		        Msg msg0_,
		        topic topic1_ 
		    where
		        msg0_.topicId=topic1_.id 
		        and topic1_.categoryId=1
	 *	2 EAGER SQL
	 *		select
		        msg0_.id as id1_,
		        msg0_.cont as cont1_,
		        msg0_.topicId as topicId1_ 
		    from
		        Msg msg0_,
		        topic topic1_ 
		    where
		        msg0_.topicId=topic1_.id 
		        and topic1_.categoryId=1
		        
		  	select
		        topic0_.id as id2_1_,
		        topic0_.categoryId as categoryId2_1_,
		        topic0_.createDate as createDate2_1_,
		        topic0_.title as title2_1_,
		        category1_.id as id0_0_,
		        category1_.name as name0_0_ 
		    from
		        topic topic0_ 
		    left outer join
		        Category category1_ 
		            on topic0_.categoryId=category1_.id 
		    where
		        topic0_.id=?
	 */
	@Test
	public void testHQL_10(){
		Session session = sf.getCurrentSession() ;
		session.beginTransaction() ;
		
		Query q = session.createQuery("from Msg m where m.topic.category .id = 1 ") ;
		List<Msg> msgs = (List<Msg>)q.list() ;
		for(Msg m : msgs){
			System.out.println(m.getId()+"     "+m.getCont());
		}
		session.getTransaction().commit() ;
	}
	
	/**
	 * 自定义查询 赋予自定义的POJO
	 * 使用自定义 Value Object
	 * 临时对象VO一定要有 一个有参的构造方法
	 */
	@Test
	public void testHQL_11(){
		Session session = sf.getCurrentSession() ;
		session.beginTransaction() ;
		
		Query q = session.createQuery("select new com.jlee06.QL.MsgInfo (m.id , m.cont , m.topic.title , m.topic.category.name ) from Msg m ") ;
		List<MsgInfo> msgInfos = (List<MsgInfo>)q.list() ;
		for(MsgInfo mi : msgInfos){
			System.out.println(mi.getId()+"     "+mi.getCategoryName()+"   "+mi.getCont()+"   "+mi.getTopicName());
		}
		session.getTransaction().commit() ;
	}
	
	/**
	 * 手动测试left right join
	 * 为什么不能直接写Category名，而必须写t.category
	 * 因为有可能存在多个成员变量（同一个类），需要指明用哪一个成员变量作为连接条件
	 */
	@Test
	public void testHQL_12(){
		Session session = sf.getCurrentSession() ;
		session.beginTransaction() ;
		
		Query q = session.createQuery("select t.title , c.name from Topic t join t.category c ") ;
		List<Object[]> os = (List<Object[]>)q.list() ;
		for(Object[] o : os){
			System.out.println(o[0]+"     "+o[1]);
		}
		session.getTransaction().commit() ;
	}
	
	/**
	 * 学习使用 uniqueResult
	 * 		select
		        msg0_.id as id1_,
		        msg0_.cont as cont1_,
		        msg0_.topicId as topicId1_ 
		    from
		        Msg msg0_ 
		    where
		        msg0_.id=?
	 *	返回单独的唯一的结果
	 */
	@Test
	public void testHQL_13(){
		Session session = sf.getCurrentSession() ;
		session.beginTransaction() ;
		
		Query q = session.createQuery("from Msg m where m= :msg ") ;
		Msg m = new Msg() ;
		m.setId(1) ;
		q.setParameter("msg", m) ;
		
		Msg mResult = (Msg)q.uniqueResult() ;
		System.out.println(mResult.getId()+"   "+mResult.getCont());
		
		session.getTransaction().commit() ;
	}
	
	/**
	 * 集合函数 Count
	 * SQL语句
	 * 	    select
		        count(*) as col_0_0_ 
		    from
		        Msg msg0_
	 */
	@Test
	public void testHQL_14(){
		Session session = sf.getCurrentSession() ;
		session.beginTransaction() ;
		
		Query q = session.createQuery("select count(*) from Msg m ") ;
		long count = (Long)q.uniqueResult() ;
		System.out.println(count);
		
		session.getTransaction().commit() ;
	}
	
	/**
	 * 集合函数 max min avg sum
	 * SQL语句
	 * 	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_
	 */
	@Test
	public void testHQL_15(){
		Session session = sf.getCurrentSession() ;
		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() ;
	}
	
	/**
	 * between  and
	 * SQL语句
	 * 	select
	        msg0_.id as id1_,
	        msg0_.cont as cont1_,
	        msg0_.topicId as topicId1_ 
	    from
	        Msg msg0_ 
	    where
	        msg0_.id between 3 and 8
	 */
	@Test
	public void testHQL_16(){
		Session session = sf.getCurrentSession() ;
		session.beginTransaction() ;
		
		Query q = session.createQuery("from Msg m Where m.id between 3 and 8 ") ;
		for(Object o : q.list() ){
			Msg m = (Msg)o ;
			System.out.println(m.getId()+"   "+m.getCont());
		}
		session.getTransaction().commit() ;
	}
	
	/**
	 * in
	 * SQL 语句
	 *     select
		        msg0_.id as id1_,
		        msg0_.cont as cont1_,
		        msg0_.topicId as topicId1_ 
		    from
		        Msg msg0_ 
		    where
		        msg0_.id in (
		            3 , 4 , 5 , 6
		        )
	 */
	@Test
	public void testHQL_17(){
		Session session = sf.getCurrentSession() ;
		session.beginTransaction() ;
		
		Query q = session.createQuery("from Msg m Where m.id in (3,4,5,6) ") ;
		for(Object o : q.list() ){
			Msg m = (Msg)o ;
			System.out.println(m.getId()+"   "+m.getCont());
		}
		session.getTransaction().commit() ;
	}
	
	/**
	 * is null 和 is not null
	 *     select
		        msg0_.id as id1_,
		        msg0_.cont as cont1_,
		        msg0_.topicId as topicId1_ 
		    from
		        Msg msg0_ 
		    where
		        msg0_.cont is not null
	 */
	@Test
	public void testHQL_18(){
		Session session = sf.getCurrentSession() ;
		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() ;
	}
	
	/**
	 * is empty
	 * SQL 语句
	 * 
	 */
	@Test
	public void testHQL_19(){
		Session session = sf.getCurrentSession() ;
		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() ;
	}
	
	/**
	 *	like 的使用
	 *SQL语句
	 *     select
		        topic0_.id as id2_,
		        topic0_.categoryId as categoryId2_,
		        topic0_.createDate as createDate2_,
		        topic0_.title as title2_ 
		    from
		        topic topic0_ 
		    where
		        topic0_.title like '%5'
	 */
	@Test
	public void testHQL_20(){
		Session session = sf.getCurrentSession() ;
		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() ;
	}
	
	/**
	 * 函数的使用1
	 * lower , upper , trim , concat , length 
	 * SQL语句
	 * select
	        lower(topic0_.title) as col_0_0_,
	        upper(topic0_.title) as col_1_0_,
	        trim(topic0_.title) as col_2_0_,
	        concat(topic0_.title,
	        '*****') as col_3_0_,
	        length(topic0_.title) as col_4_0_ 
	    from
	        topic topic0_
	 */
	@Test
	public void testHQL_21(){
		Session session = sf.getCurrentSession() ;
		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 os : q.list() ){
			Object[] o = (Object[])os ;
			System.out.println(o[0] +"   "+ o[1] +"   "+ o[2] +"   "+ o[3] +"   "+ o[4]);
		}
		session.getTransaction().commit() ;
	}
	
	/**
	 * 函数的使用2
	 * abs , sqrt , mod 
	 * SQL 语句
	 * 		select
		        abs(topic0_.id) as col_0_0_,
		        sqrt(topic0_.id) as col_1_0_,
		        mod(topic0_.id,
		        2) as col_2_0_ 
		    from
		        topic topic0_
	 */
	@Test
	public void testHQL_22(){
		Session session = sf.getCurrentSession() ;
		session.beginTransaction() ;
		
		Query q = session.createQuery("select " +
				" abs(t.id) ," +
				" sqrt(t.id) ," +
				" mod(t.id , 2) " +
				" from Topic t ") ;
		for(Object os : q.list() ){
			Object[] o = (Object[])os ;
			System.out.println(o[0] +"   "+ o[1] +"   "+ o[2] );
		}
		session.getTransaction().commit() ;
	}
	
	/**
	 * 时间函数的应用
	 * SQL 语句
	 * select
	        current_date as col_0_0_,
	        current_time as col_1_0_,
	        current_timestamp as col_2_0_,
	        topic0_.id as col_3_0_ 
	    from
	        topic topic0_
	 */
	@Test
	public void testHQL_24(){
		Session session = sf.getCurrentSession() ;
		session.beginTransaction() ;
		
		Query q = session.createQuery("select current_date , current_time , current_timestamp , t.id  from Topic t ") ;
		for(Object os : q.list() ){
			Object[] o = (Object[])os ;
			System.out.println(o[0] +"   "+ o[1] +"   "+ o[2] +"   "+o[3]);
		}
		session.getTransaction().commit() ;
	}
	
	/**
	 * 日期的比较
	 * SQL语句
	 *     select
		        topic0_.id as id2_,
		        topic0_.categoryId as categoryId2_,
		        topic0_.createDate as createDate2_,
		        topic0_.title as title2_ 
		    from
		        topic topic0_ 
		    where
		        topic0_.createDate<?
	 */
	@Test
	public void testHQL_25(){
		Session session = sf.getCurrentSession() ;
		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.getId()+"    "+t.getCreateDate());
		}
		session.getTransaction().commit() ;
	}
	
	/**
	 * group by 语句
	 * SQL 语句
	 *     select
		        topic0_.title as col_0_0_,
		        count(*) as col_1_0_ 
		    from
		        topic topic0_ 
		    group by
		        topic0_.title
	 */
	@Test
	public void testHQL_26(){
		Session session = sf.getCurrentSession() ;
		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() ;
	}

	/**
	 * group by having 语句
	 * group by 里面出现的 字段 必须出现在 select 里面
	 * having 中的条件必须是 组合函数
	 * SQL 语句
	 *         select
			        topic0_.title as col_0_0_,
			        count(*) as col_1_0_ 
			    from
			        topic topic0_ 
			    group by
			        topic0_.title 
			    having
			        count(*)>0
	 */
	@Test
	public void testHQL_27(){
		Session session = sf.getCurrentSession() ;
		session.beginTransaction() ;
		
		Query q = session.createQuery("select t.title , count(*) from Topic t group by t.title having count(*) > 0 ") ;
		for(Object o : q.list() ){
			Object[] arr = (Object[])o ;
			System.out.println(arr[0]+"    "+arr[1]);
		}
		session.getTransaction().commit() ;
	}
	
	/**
	 * 子查询
	 * SQL 语句
	 *     select
		        topic0_.id as id2_,
		        topic0_.categoryId as categoryId2_,
		        topic0_.createDate as createDate2_,
		        topic0_.title as title2_ 
		    from
		        topic topic0_ 
		    where
		        topic0_.id<(
		            select
		                avg(topic1_.id) 
		            from
		                topic topic1_
		        )
	 */
	@Test
	public void testHQL_28(){
		Session session = sf.getCurrentSession() ;
		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.getId() +"   "+t.getCreateDate());
		}
		session.getTransaction().commit() ;
	}

	/**
	 * All 函数的使用
	 * SQL 语句
	 *     select
		        topic0_.id as id2_,
		        topic0_.categoryId as categoryId2_,
		        topic0_.createDate as createDate2_,
		        topic0_.title as title2_ 
		    from
		        topic topic0_ 
		    where
		        topic0_.id<all (
		            select
		                topic1_.id 
		            from
		                topic topic1_ 
		            where
		                mod(topic1_.id, 2)=0
		        )
	 */
	@Test
	public void testHQL_29(){
		Session session = sf.getCurrentSession() ;
		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.getId() +"   "+t.getCreateDate());
		}
		session.getTransaction().commit() ;
	}

	/**
	 * exists 和 not exists
	 * 说明： 用 in 可以实现 exists 的功能
	 * 				但是exists的效率高
	 * SQL 语句
	 *     select
		        topic0_.id as id2_,
		        topic0_.categoryId as categoryId2_,
		        topic0_.createDate as createDate2_,
		        topic0_.title as title2_ 
		    from
		        topic topic0_ 
		    where
		        not (exists (select
		            msg1_.id 
		        from
		            Msg msg1_ 
		        where
		            msg1_.topicId=topic0_.id))
	 */
	@Test
	public void testHQL_30(){
		Session session = sf.getCurrentSession() ;
		session.beginTransaction() ;
		
		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.getId() +"   "+t.getCreateDate());
		}
		session.getTransaction().commit() ;
	}

	/**
	 * update  delete
	 * SQL 语句
	 *     update
		        topic 
		    set
		        title=upper(title) 
	 */
	@Test
	public void testHQL_31(){
		Session session = sf.getCurrentSession() ;
		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() ;
	}
	
	/**
	 * 命名查询
	 * 实现 按名字查找的自定义查询
	 * 在 Entity 上面自定义好 SQL 语句
	 * SQL 语句
	 *     select
		        topic0_.id as id2_,
		        topic0_.categoryId as categoryId2_,
		        topic0_.createDate as createDate2_,
		        topic0_.title as title2_ 
		    from
		        topic topic0_ 
		    where
		        topic0_.id=?
	 */
	@Test
	public void testHQL_32(){
		Session session = sf.getCurrentSession() ;
		session.beginTransaction() ;
		
//		Query q = session.getNamedQuery("Topic.selectTopic") ;
//		q.setParameter("id", 5) ;
//		Topic t = (Topic)q.uniqueResult() ;
//		System.out.println(t.getId()+"   "+t.getCreateDate());
		
		Query q = session.getNamedQuery("Topic.conditionTopic") ;
		q.setParameter("title", "%") ;
		for(Object o : q.list()){
			Topic t = (Topic)o ;
			System.out.println(t.getId()+"   "+t.getCreateDate());
		}
		
//		Query q = session.getNamedQuery("native_sql_page") ;
//		for(Object o : q.list()){
//			Topic t = (Topic)o ;
//			System.out.println(t.getId()+"   "+t.getCreateDate());
//		}
		
		session.getTransaction().commit() ;
	}
	
	/**
	 * 原生SQL查询
	 * Hibernate使用SQL语句
	 * SQL语句
	 *     select   * 
			from
			category limit 2, 6
	 */
	@Test
	public void testHQL_33(){
		Session session = sf.getCurrentSession() ;
		session.getTransaction().begin() ;
		
		SQLQuery q = session.createSQLQuery("select * from category limit 2,6").addEntity(Category.class);
		List<Category> categorys = (List<Category>)q.list() ;
		for(Category c : categorys){
			System.out.println(c.getId()+"   "+c.getName());
		}
		
		session.getTransaction().commit() ;
	}

 
 	/**
  	* 实现数据分也显示
  	* SQL语句
   	*     select
          		topic0_.id as id2_,
          		topic0_.categoryId as categoryId2_,
          		topic0_.createDate as createDate2_,
          		topic0_.title as title2_ 
      	      from
          		topic topic0_ 
      	      where
          		not (exists (select
              			msg1_.id 
          		    from
              			Msg msg1_ 
          		    where
              			msg1_.topicId=topic0_.id)) limit ?, ?
  	*/
 	@Test
 	public void testHQL_34(){
  	Session session = sf.getCurrentSession() ;
  	session.beginTransaction() ;
  
  	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 ) ") ;
  	q.setFirstResult(10).setMaxResults(20) ;
  	for(Object o : q.list() ){
   	Topic t = (Topic)o ;
   	System.out.println(t.getId() +"   "+t.getCreateDate());
  	}
  	session.getTransaction().commit() ;
 	}

 
  
  	/**
   	* 根据日期时间进行查询
  	* SQL语句：  
  	* select
          		topic0_.id as id2_,
          		topic0_.categoryId as categoryId2_,
          		topic0_.createDate as createDate2_,
          		topic0_.title as title2_ 
      	from
          		topic topic0_ 
      	where
          		topic0_.createDate=? limit ?
   	*/
  	@Test
  	public void testHQL_35(){
   		Session session = sf.getCurrentSession() ;
   		session.beginTransaction() ;
   
   		/**
    		* 这种方式适用于 datetime 和 date 的两种字段格式
    		*/
   		String today = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()); 
   		Query q = session.createQuery("from Topic t where t.createDate = :today ") ;
   		try {
    			q.setDate("today", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(today))  ;
   		} catch (ParseException e) {
    			e.printStackTrace();
   		}
   
   		/**
    		* 这种方式适只用于 datetime 的字段格式
    		*/
 		//  Query q = session.createQuery("from Topic t where t.createDate between ? and ? ") ;
 		//  SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
 		//  try {
 		//        q.setParameter(0, sdf.parse("2011-02-12 00:00:00")) ;
 		//        q.setParameter(1, sdf.parse("2011-02-12 23:59:59"));
 		//  } catch (Exception e) {
 		//        e.printStackTrace();
 		//  }
 
   
   		q.setFirstResult(0).setMaxResults(20) ;
   		for(Object o : q.list() ){
    			Topic t = (Topic)o ;
    			System.out.println(t.getId() +"   "+t.getCreateDate());
   		}
   		session.getTransaction().commit() ;
  	} 	

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

 

 

hibernate.cfg.xml文件：

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

    <session-factory>

        <!-- Database connection settings -->
        <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="connection.url">jdbc:mysql://localhost/hibernate</property>
        <property name="connection.username">root</property>
        <property name="connection.password">root</property>

        <!-- JDBC connection pool (use the built-in) -->
        <property name="connection.pool_size">100</property>

        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.MySQLDialect</property>

        <!-- Enable Hibernate's automatic session context management -->
        <property name="current_session_context_class">thread</property>

        <!-- Disable the second-level cache  -->
        <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
		
        <!-- Echo all executed SQL to stdout -->
        <property name="show_sql">true</property>

        <property name="format_sql">true</property>
		
        <!-- Drop and re-create the database schema on startup -->
        <!--<property name="hbm2ddl.auto">create</property>-->


	<mapping class="com.jlee06.QL.Category"/>
	<mapping class="com.jlee06.QL.Msg"/>
	<mapping class="com.jlee06.QL.Topic"/>


    </session-factory>

</hibernate-configuration>

 

评论

1 楼 zgpinguo 2012-04-07