hibernate使用Query进行查询

本文主要探讨hibernate的简单查询,主要是使用Query进行的查询。

1.首先看下annotation的API中关于查询的描述

2.3. 映射查询

2.3.1. 映射EJBQL/HQL查询

使用注解还可以映射EJBQL/HQL查询. @NamedQuery 和@NamedQueries是可使用在类和包上的注解. 但是它们的定义在session factory/entity manager factory范围中是都可见的. 命名式查询通过它的名字和实际的查询字符串来定义.

javax.persistence.NamedQueries(
    @javax.persistence.NamedQuery(name="plane.getAll", query="select p from Plane p")
)
package org.hibernate.test.annotations.query;

...

@Entity
@NamedQuery(name="night.moreRecentThan", query="select n from Night n where n.date >= :date")
public class Night {
    ...
}

public class MyDao {
    doStuff() {
        Query q = s.getNamedQuery("night.moreRecentThan");
        q.setDate( "date", aMonthAgo );
        List results = q.list();
        ...
    }
    ...
}
        

还可以通过定义 QueryHint 数组的hints 属性为查询提供一些hint信息.

下面是目前可以使用的一些Hibernate hint:

表 2.2. Query hints

hintdescription
org.hibernate.cacheable查询是否与二级缓存交互(默认值为false)
org.hibernate.cacheRegion设置缓存区名称 (默认为otherwise)
org.hibernate.timeout查询超时设定
org.hibernate.fetchSize所获取的结果集(resultset)大小
org.hibernate.flushMode本次查询所用的刷新模式
org.hibernate.cacheMode本次查询所用的缓存模式
org.hibernate.readOnly是否将本次查询所加载的实体设为只读(默认为false)
org.hibernate.comment将查询注释添加入所生成的SQL

2.3.2. 映射本地化查询

你还可以映射本地化查询(也就是普通SQL查询). 不过这需要你使用@SqlResultSetMapping注解来描述SQL的resultset的结构 (如果你打算定义多个结果集映射,可是使用@SqlResultSetMappings). @SqlResultSetMapping@NamedQuery@SqlResultSetMapping一样,可以定义在类和包一级. 但是@SqlResultSetMapping的作用域为应用级. 下面我们会看到,@NamedNativeQuery 注解中 resultSetMapping参数值为@SqlResultSetMapping的名字. 结果集映射定义了通过本地化查询返回值和实体的映射. 该实体中的每一个字段都绑定到SQL结果集中的某个列上. 该实体的所有字段包括子类的所有字段以及 关联实体的外键列都必须在SQL查询中有对应的定义. 如果实体中的属性和SQL查询中的列名相同,这种情况下可以不进行定义字段映射.

@NamedNativeQuery(name="night&area", query="select night.id nid, night.night_duration, "
    + " night.night_date, area.id aid, night.area_id, area.name "
    + "from Night night, Area area where night.area_id = area.id", resultSetMapping="joinMapping")
@SqlResultSetMapping(name="joinMapping", entities={
    @EntityResult(entityClass=org.hibernate.test.annotations.query.Night.class, fields = {
        @FieldResult(name="id", column="nid"),
        @FieldResult(name="duration", column="night_duration"),
        @FieldResult(name="date", column="night_date"),
        @FieldResult(name="area", column="area_id"),
        discriminatorColumn="disc"
    }),
    @EntityResult(entityClass=org.hibernate.test.annotations.query.Area.class, fields = {
        @FieldResult(name="id", column="aid"),
        @FieldResult(name="name", column="name")
    })
    }
)

在上面这个例子中,名为night&area的查询 和joinMapping结果集映射对应. 该映射返回两个实体,分别为Night 和Area,其中每个属性都和一个列关联, 列名通过查询获取.下面我们看一个隐式声明属性和列映射关系的例子.

@Entity
@SqlResultSetMapping(name="implicit", entities=@EntityResult(entityClass=org.hibernate.test.annotations.query.SpaceShip.class))
@NamedNativeQuery(name="implicitSample", query="select * from SpaceShip", resultSetMapping="implicit")
public class SpaceShip {
    private String name;
    private String model;
    private double speed;

    @Id
    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Column(name="model_txt")
    public String getModel() {
        return model;
    }

    public void setModel(String model) {
        this.model = model;
    }

    public double getSpeed() {
        return speed;
    }

    public void setSpeed(double speed) {
        this.speed = speed;
    }
}

在这个例子中,我们只需要定义结果集映射中的实体成员. 属性和列名之间的映射借助实体中包含映射信息来完成. 在这个例子中,model属性绑定到model_txt列. 如果和相关实体的关联设计到组合主键, 那么应该使用@FieldResult注解来定义每个外键列. @FieldResult的名字由以下几部分组成: 定义这种关系的属性名字+"."+主键名或主键列或主键属性.

@Entity
@SqlResultSetMapping(name="compositekey",
        entities=@EntityResult(entityClass=org.hibernate.test.annotations.query.SpaceShip.class,
            fields = {
                    @FieldResult(name="name", column = "name"),
                    @FieldResult(name="model", column = "model"),
                    @FieldResult(name="speed", column = "speed"),
                    @FieldResult(name="captain.firstname", column = "firstn"),
                    @FieldResult(name="captain.lastname", column = "lastn"),
                    @FieldResult(name="dimensions.length", column = "length"),
                    @FieldResult(name="dimensions.width", column = "width")
                    }),
        columns = { @ColumnResult(name = "surface"),
                    @ColumnResult(name = "volume") } )

@NamedNativeQuery(name="compositekey",
    query="select name, model, speed, lname as lastn, fname as firstn, length, width, length * width as surface from SpaceShip", 
    resultSetMapping="compositekey")
} )
public class SpaceShip {
    private String name;
    private String model;
    private double speed;
    private Captain captain;
    private Dimensions dimensions;

    @Id
    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @ManyToOne(fetch= FetchType.LAZY)
    @JoinColumns( {
            @JoinColumn(name="fname", referencedColumnName = "firstname"),
            @JoinColumn(name="lname", referencedColumnName = "lastname")
            } )
    public Captain getCaptain() {
        return captain;
    }

    public void setCaptain(Captain captain) {
        this.captain = captain;
    }

    public String getModel() {
        return model;
    }

    public void setModel(String model) {
        this.model = model;
    }

    public double getSpeed() {
        return speed;
    }

    public void setSpeed(double speed) {
        this.speed = speed;
    }

    public Dimensions getDimensions() {
        return dimensions;
    }

    public void setDimensions(Dimensions dimensions) {
        this.dimensions = dimensions;
    }
}

@Entity
@IdClass(Identity.class)
public class Captain implements Serializable {
    private String firstname;
    private String lastname;

    @Id
    public String getFirstname() {
        return firstname;
    }

    public void setFirstname(String firstname) {
        this.firstname = firstname;
    }

    @Id
    public String getLastname() {
        return lastname;
    }

    public void setLastname(String lastname) {
        this.lastname = lastname;
    }
}

注意

观察dimension属性你会发现Hibernate支持用"."符号来表示嵌入式对象. EJB3实现不必支持这个特征,但是我们做到了:-)

如果查询返回的是单个实体,或者你打算使用系统默认的映射, 这种情况下可以不使用resultSetMapping 而是使用resultClass属性:

@NamedNativeQuery(name="implicitSample", query="select * from SpaceShip", 
    resultClass=SpaceShip.class)
public class SpaceShip {

某些本地查询返回的是scalar值,例如报表查询. 你可以通过@ColumnResult将其映射到 @SqlResultsetMapping上. 甚至还可以在同一个本地查询的结果中混合实体和scalar类型(不过这种情况比较少见).

@SqlResultSetMapping(name="scalar", columns=@ColumnResult(name="dimension"))
@NamedNativeQuery(name="scalar", query="select length*width as dimension from SpaceShip", resultSetMapping="scalar")

本地查询中还有另外一个hint属性: org.hibernate.callable. 这个属性的布尔变量值表明这个查询是否是一个存储过程.

2.再来看下xml的API中关于查询的描述

If you do not know the identifiers of the objects you are looking for, you need a query. Hibernate supports an easy-to-use but powerful object oriented query language (HQL). For programmatic query creation, Hibernate supports a sophisticated Criteria and Example query feature (QBC and QBE). You can also express your query in the native SQL of your database, with optional support from Hibernate for result set conversion into objects.

HQL和原生SQL(native SQL)查询要通过为org.hibernate.Query的实例来表达。 这个接口提供了参数绑定、结果集处理以及运行实际查询的方法。 你总是可以通过当前Session获取一个Query对象:

List cats = session.createQuery(
    "from Cat as cat where cat.birthdate < ?")
    .setDate(0, date)
    .list();

List mothers = session.createQuery(
    "select mother from Cat as cat join cat.mother as mother where cat.name = ?")
    .setString(0, name)
    .list();

List kittens = session.createQuery(
    "from Cat as cat where cat.mother = ?")
    .setEntity(0, pk)
    .list();

Cat mother = (Cat) session.createQuery(
    "select cat.mother from Cat as cat where cat = ?")
    .setEntity(0, izi)
    .uniqueResult();]]

Query mothersWithKittens = (Cat) session.createQuery(
    "select mother from Cat as mother left join fetch mother.kittens");
Set uniqueMothers = new HashSet(mothersWithKittens.list());

A query is usually executed by invoking list(). The result of the query will be loaded completely into a collection in memory. Entity instances retrieved by a query are in a persistent state. The uniqueResult() method offers a shortcut if you know your query will only return a single object. Queries that make use of eager fetching of collections usually return duplicates of the root objects, but with their collections initialized. You can filter these duplicates through a Set.

3.查询练习

新建3个类,Category、Topic和Msg,其中Category和Topic是1对多,Topic和Msg是1对多

另外还有1个查询辅助类MsgInfo,注意此类不适用注解

Category

package com.baosight.model;

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

@Entity
public class Category {
	private String id;
	private String name;
	@Id
	@GeneratedValue
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	
}
Topic

package com.baosight.model;

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

import javax.persistence.Entity;
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 String id;
	private String title;
	private Category category;
	private Date createDate;
	private List<Msg> msgs;
	@ManyToOne
	public Category getCategory() {
		return category;
	}
	public void setCategory(Category category) {
		this.category = category;
	}
	@Id
	@GeneratedValue
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public Date getCreateDate() {
		return createDate;
	}
	public void setCreateDate(Date createDate) {
		this.createDate = createDate;
	}
	@OneToMany(mappedBy="topic")
	public List<Msg> getMsgs() {
		return msgs;
	}
	public void setMsgs(List<Msg> msgs) {
		this.msgs = msgs;
	}
	
}
Msg

package com.baosight.model;

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

@Entity
public class Msg {
	private String id;
	private String cont;
	private Topic topic;
	@Id
	@GeneratedValue
	public String getId() {
		return id;
	}
	public void setId(String 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;
	}
	
}

MsgInfo

package com.baosight.model;

public class MsgInfo {
	private String id;
	private String cont;
	private String topicName;
	private String categoryName;
	public MsgInfo(String id, String cont, String topicName, String categoryName) {
		super();
		this.id = id;
		this.cont = cont;
		this.topicName = topicName;
		this.categoryName = categoryName;
	}
	public String getId() {
		return id;
	}
	public void setId(String 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;
	}
}
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">oracle.jdbc.driver.OracleDriver</property>
        <property name="connection.url">jdbc:oracle:thin:@127.0.0.1:1521:orcl</property>
        <property name="connection.username">scott</property>
        <property name="connection.password">tiger</property>

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

        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.Oracle9iDialect</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">update</property> -->

		<mapping class="com.baosight.model.Category"/>
		<mapping class="com.baosight.model.Topic"/>
		<mapping class="com.baosight.model.Msg"/>
    </session-factory>

</hibernate-configuration>


JUnit测试类

package com.baosight.model;

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

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 OrMappingTest {
	private static SessionFactory sessionFactory;
	
	@BeforeClass
	public static void beforeClass() {
		//new SchemaExport(new AnnotationConfiguration().configure()).create(false, true);
		sessionFactory = new AnnotationConfiguration().configure().buildSessionFactory();
	}
	@AfterClass
	public static void afterClass() {
		sessionFactory.close();
	}
	
	@Test
	public void testSave() {
		//插入数据
		Session session = sessionFactory.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.setTitle("t"+i);
			t.setCreateDate(new Date());
			t.setCategory(c);
			session.save(t);
		}
		for(int i=0;i<10;i++){
			Topic t = new Topic();
			t.setId("11");
			Msg m = new Msg();
			m.setCont("m"+i);
			m.setTopic(t);
			session.save(m);
		}
		session.getTransaction().commit();
	}
	@Test
	public void testHQL01() {
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		//查询
		List<Category> list = (List<Category>)session.createQuery("from Category").list();
		for(Category c : list) {
			System.out.println(c.getName());
		}
		session.getTransaction().commit();
	}
	@Test
	public void testHQL02() {
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		//条件查询
		List<Category> list = (List<Category>)session.createQuery("from Category c where c.name>'c5'").list();
		for(Category c : list) {
			System.out.println(c.getName());
		}
		session.getTransaction().commit();
	}
	@Test
	public void testHQL03() {
		//排序等
		String sql = "";
//		sql = "from Category c order by c.name desc";
		sql = "select distinct c from Category c order by c.name desc";
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		List<Category> list = (List<Category>)session.createQuery(sql).list();
		for(Category c : list) {
			System.out.println(c.getName());
		}
		session.getTransaction().commit();
	}
	@Test
	public void testHQL04() {
		//传参查询
		String sql = "";
		sql = "from Category c where c.id>:min and c.id<:max order by c.name desc";
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		Query q = session.createQuery(sql);
		q.setParameter("min", "2");
		q.setParameter("max", "8");
		List<Category> list = (List<Category>)q.list();
		for(Category c : list) {
			System.out.println(c.getId()+"--"+c.getName());
		}
		session.getTransaction().commit();
	}
	@Test
	public void testHQL05() {
		//分页查询
		String sql = "";
		sql = "from Category c order by c.name desc";
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		Query q = session.createQuery(sql);
		q.setMaxResults(4);//每页条数
		q.setFirstResult(2);//开始行号
		List<Category> list = (List<Category>)q.list();
		for(Category c : list) {
			System.out.println(c.getId()+"--"+c.getName());
		}
		session.getTransaction().commit();
	}
	@Test
	public void testHQL06() {
		//查询字段
		String sql = "";
		sql = "select c.id,c.name from Category c order by c.name desc";
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		Query q = session.createQuery(sql);
		List<Object[]> list = (List<Object[]>)q.list();
		for(Object[] c : list) {
			System.out.println(c[0]+"--"+c[1]);
		}
		session.getTransaction().commit();
	}
	@Test
	public void testHQL07() {
		//关联查询
		String sql = "";
		sql = "from Topic t where t.category.id=1";
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		Query q = session.createQuery(sql);
		List<Topic> list = (List<Topic>)q.list();
		for(Topic c : list) {
			System.out.println(c.getId()+"--"+c.getTitle());
//			System.out.println(c.getCategory().getId()+"--"+c.getCategory().getName());
		}
		session.getTransaction().commit();
	}
	@Test
	public void testHQL08() {
		//多级关联查询
		String sql = "";
		sql = "from Msg m where m.topic.category.id=1";
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		Query q = session.createQuery(sql);
		List<Msg> list = (List<Msg>)q.list();
		for(Msg c : list) {
			System.out.println(c.getId()+"--"+c.getCont());
//			System.out.println(c.getTopic().getTitle()+"--"+c.getTopic().getCategory().getName());
		}
		session.getTransaction().commit();
	}
	@Test
	public void testHQL09() {
		//通过辅助类查询
		String sql = "";
		sql = "select new com.baosight.model.MsgInfo(m.id,m.cont,m.topic.title,m.topic.category.name) from Msg m";
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		Query q = session.createQuery(sql);
		List list = q.list();
		for(Object c : list) {
			MsgInfo m = (MsgInfo)c;
			System.out.println(m.getId()+"--"+m.getCont());
			System.out.println(m.getTopicName()+"--"+m.getCategoryName());
		}
		session.getTransaction().commit();
	}
	@Test
	public void testHQL10() {
		//连接查询个别字段
		String sql = "";
		sql = "select m.cont,t.title from Msg m join m.topic t";
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		Query q = session.createQuery(sql);
		List<Object[]> list = (List<Object[]>)q.list();
		for(Object[] c : list) {
			System.out.println(c[0]+"--"+c[1]);
		}
		session.getTransaction().commit();
	}
	@Test
	public void testHQL11() {
		//使用uniqueResult
		String sql = "";
		sql = "from Msg m where m=:msgToSearch";
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		Query q = session.createQuery(sql);
		Msg m = new Msg();
		m.setId("21");
		q.setParameter("msgToSearch", m);
		Msg msg = (Msg) q.uniqueResult();
		System.out.println(msg.getId()+"--"+msg.getCont());
		session.getTransaction().commit();
	}
	@Test
	public void testHQL12() {
		//使用uniqueResult
		String sql = "";
		sql = "select count(*) from Msg m";
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		Query q = session.createQuery(sql);
		Long count = (Long) q.uniqueResult();
		System.out.println(count);
		session.getTransaction().commit();
	}
	@Test
	public void testHQL13() {
		//使用聚合函数
		String sql = "";
		sql = "select max(m.id),min(m.id),avg(m.id),sum(m.id) from Msg m";
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		Query q = session.createQuery(sql);
		Object[] o = (Object[]) q.uniqueResult();
		System.out.println(o[0]+"--"+o[1]+"--"+o[2]+"--"+o[3]);
		session.getTransaction().commit();
	}
	@Test
	public void testHQL14() {
		//between and
		//in
		String sql = "";
//		sql = "from Msg m where m.id between 21 and 24";
//		sql = "from Msg m where m.id in(22,23,24)";
		sql = "from Msg m where m.topic is not null";
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		Query q = session.createQuery(sql);
		List<Msg> list = (List<Msg>)q.list();
		for(Msg c : list) {
			System.out.println(c.getId()+"--"+c.getCont());
//			System.out.println(c.getTopic().getTitle()+"--"+c.getTopic().getCategory().getName());
		}
		session.getTransaction().commit();
	}
	@Test
	public void testHQL15() {
		//is empty
		//like
		String sql = "";
//		sql = "from Topic t where t.msgs is empty";
//		sql = "from Topic t where t.title like '%5'";
		sql = "from Topic t where t.title like '_5'";
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		Query q = session.createQuery(sql);
		List<Topic> list = (List<Topic>)q.list();
		for(Topic c : list) {
			System.out.println(c.getId()+"--"+c.getTitle());
//			System.out.println(c.getTopic().getTitle()+"--"+c.getTopic().getCategory().getName());
		}
		session.getTransaction().commit();
	}
	@Test
	public void testHQL16() {
		//字段函数
		String sql = "";
		sql = "select lower(m.cont),upper(m.cont),trim(m.cont),concat(m.cont,'***'),length(m.cont) from Msg m";
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		Query q = session.createQuery(sql);
		List<Object[]> list = (List<Object[]>)q.list();
		for(Object[] c : list) {
			System.out.println(c[0]+"--"+c[1]+"--"+c[2]+"--"+c[3]+"--"+c[4]);
		}
		session.getTransaction().commit();
	}
	@Test
	public void testHQL17() {
		//运算函数
		String sql = "";
		sql = "select abs(m.id),sqrt(m.id),mod(m.id,2) from Msg m";
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		Query q = session.createQuery(sql);
		List<Object[]> list = (List<Object[]>)q.list();
		for(Object[] c : list) {
			System.out.println(c[0]+"--"+c[1]+"--"+c[2]);
		}
		session.getTransaction().commit();
	}
	@Test
	public void testHQL18() {
		//日期函数
		String sql = "";
		sql = "select current_date,sysdate,current_timestamp,m.id from Msg m";
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		Query q = session.createQuery(sql);
		List<Object[]> list = (List<Object[]>)q.list();
		for(Object[] c : list) {
			System.out.println(c[0]+"--"+c[1]+"--"+c[2]+"--"+c[3]);
		}
		session.getTransaction().commit();
	}
	@Test
	public void testHQL19() {
		//日期参数
		String sql = "";
		sql = "from Topic t where t.createDate<:date";
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		Query q = session.createQuery(sql);
		q.setParameter("date", new Date());
		List<Topic> list = (List<Topic>)q.list();
		for(Topic c : list) {
			System.out.println(c.getId()+"--"+c.getTitle());
//			System.out.println(c.getTopic().getTitle()+"--"+c.getTopic().getCategory().getName());
		}
		session.getTransaction().commit();
	}
	@Test
	public void testHQL20() {
		//分组查询
		String sql = "";
//		sql = "select t.title,count(*) from Topic t group by t.title";
		sql = "select t.title,count(*) from Topic t group by t.title having count(*)>1";
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		Query q = session.createQuery(sql);
		List<Object[]> list = (List<Object[]>)q.list();
		for(Object[] c : list) {
			System.out.println(c[0]+"--"+c[1]);
		}
		session.getTransaction().commit();
	}
	@Test
	public void testHQL21() {
		//子查询
		//exists的使用
		String sql = "";
//		sql = "from Topic t where t.id<(select avg(t1.id) from Topic t1)";
//		sql = "from Topic t where t.id< all(select t1.id from Topic t1 where mod(t1.id,2)=0)";
		sql = "from Topic t where not exists(select m.id from Msg m where m.topic.id=t.id)";
//		sql = "from Topic t where exists(select m.id from Msg m where m.topic.id=t.id)";
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		Query q = session.createQuery(sql);
		List<Topic> list = (List<Topic>)q.list();
		for(Topic c : list) {
			System.out.println(c.getId()+"--"+c.getTitle());
//			System.out.println(c.getTopic().getTitle()+"--"+c.getTopic().getCategory().getName());
		}
		session.getTransaction().commit();
	}
	@Test
	public void testHQL22() {
		//update
		String sql = "";
		sql = "update Topic t set t.title=upper(t.title)";
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		Query q = session.createQuery(sql);
		q.executeUpdate();
		q = session.createQuery("from Topic");
		List<Topic> list = (List<Topic>)q.list();
		for(Topic c : list) {
			System.out.println(c.getId()+"--"+c.getTitle());
//			System.out.println(c.getTopic().getTitle()+"--"+c.getTopic().getCategory().getName());
		}
		session.createQuery("update Topic t set t.title=lower(t.title)").executeUpdate();
		session.getTransaction().commit();
	}
	@Test
	public void testHQL23() {
		//NamedQuery
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		Query q = session.getNamedQuery("topic.selectCertainTopic");
		q.setParameter("id", "15");
		Topic t = (Topic) q.uniqueResult();
		System.out.println(t.getId()+"--"+t.getTitle());
		session.getTransaction().commit();
	}
	@Test
	public void testHQL24() {
		//update
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		SQLQuery q = session.createSQLQuery("select * from category").addEntity(Category.class);
		List<Category> list = (List<Category>)q.list();
		for(Category c : list) {
			System.out.println(c.getId()+"--"+c.getName());
//			System.out.println(c.getTopic().getTitle()+"--"+c.getTopic().getCategory().getName());
		}
		session.getTransaction().commit();
	}
	@Test
	public void testSchemaExport() {
		//建表
		new SchemaExport(new AnnotationConfiguration().configure()).create(false, true);
	}
	
	
	public static void main(String[] args) {
		beforeClass();
	}
}
4.首先执行测试类的testSchemaExport进行建表

4.1注意Topic中的private List<Msg> msgs;是后来测试is empty时添加的,刚开始并没有



4.2执行测试类的testSave插入测试数据

5.首先进行简单查询

5.1执行testHQL01进行单表查询

通过List<Category> list = (List<Category>)session.createQuery("from Category").list();

查询出结果集并进行遍历



5.2执行testHQL02进行单表条件查询

List<Category> list = (List<Category>)session.createQuery("from Category c where c.name>'c5'").list();


5.3执行testHQL03进行单表查询排序和使用distinct

sql = "from Category c order by c.name desc";

sql = "select distinct c from Category c order by c.name desc";


5.4执行testHQL04进行单表查询传入查询参数

使用:xx定义传入参数,使用q.setParameter("xx", Obj);传入参数

sql = "from Category c where c.id>:min and c.id<:max order by c.name desc";

q.setParameter("min", "2");
q.setParameter("max", "8");


5.5执行testHQL05进行单表分页查询

使用q.setMaxResults(xx);设置每页条数;使用q.setFirstResult(xx);设置开始行号

sql = "from Category c order by c.name desc";

q.setMaxResults(4);//每页条数
q.setFirstResult(2);//开始行号


5.6执行testHQL06进行单表字段查询

注意此时查询的结果是Object[]组成的list

sql = "select c.id,c.name from Category c order by c.name desc";

List<Object[]> list = (List<Object[]>)q.list();


6.进行关联查询

6.1多对一关联查询,使用testHQL07

查询语句为sql = "from Topic t where t.category.id=1";这体现了hibernate对象查询的本质,直接通过对象进行关联查询


6.2使用testHQL08进行多级关联

sql = "from Msg m where m.topic.category.id=1";


6.3使用辅助类进行查询,此查询和ibatis里面的查询辅助类相似

sql = "select new com.baosight.model.MsgInfo(m.id,m.cont,m.topic.title,m.topic.category.name) from Msg m";

运行testHQL09


6.4进行连接查询,运行testHQL10

sql = "select m.cont,t.title from Msg m join m.topic t";

注意使用m.topic就指明了二者之间的连接字段


6.5使用uniqueResult,运行testHQL11

查询结果只有1条记录时可以使用类似于Msg msg = (Msg) q.uniqueResult();进行查询

一般适用于通过id进行查询,或者是使用聚合函数查询出单条记录等


6.6使用count查询统计,运行testHQL12

sql = "select count(*) from Msg m";

Long count = (Long) q.uniqueResult();

查询结果是Long


6.7使用聚合函数查询统计,运行testHQL13

sql = "select max(m.id),min(m.id),avg(m.id),sum(m.id) from Msg m";


7.条件查询

7.1使用between and/in/is not  null查询,运行testHQL14

sql = "from Msg m where m.id between 21 and 24";
sql = "from Msg m where m.id in(22,23,24)";
sql = "from Msg m where m.topic is not null";


7.2使用is empty/like查询,运行testHQL15

上面6.7中is null是在多的一方查询1,如果想要在1的一方查询多,需要在1中配置@OneToMany(mappedBy="topic")
public List<Msg> getMsgs() {

查询时可以使用sql = "from Topic t where t.msgs is empty";

sql = "from Topic t where t.title like '%5'";
sql = "from Topic t where t.title like '_5'";


8.使用常用函数进行查询

8.1使用字符串函数查询,运行testHQL16

sql = "select lower(m.cont),upper(m.cont),trim(m.cont),concat(m.cont,'***'),length(m.cont) from Msg m";


8.2使用数学运算函数查询,运行testHQL17

sql = "select abs(m.id),sqrt(m.id),mod(m.id,2) from Msg m";


8.3使用日期函数查询,运行testHQL18

sql = "select current_date,sysdate,current_timestamp,m.id from Msg m";


8.4使用日期函数传参查询,运行testHQL19

sql = "from Topic t where t.createDate<:date";q.setParameter("date", new Date());


9.复杂查询

9.1使用group by和having进行分组查询,运行testHQL20

sql = "select t.title,count(*) from Topic t group by t.title";

sql = "select t.title,count(*) from Topic t group by t.title having count(*)>1";


9.2使用子查询和exists,运行testHQL21

sql = "from Topic t where t.id<(select avg(t1.id) from Topic t1)";

sql = "from Topic t where t.id< all(select t1.id from Topic t1 where mod(t1.id,2)=0)";

sql = "from Topic t where not exists(select m.id from Msg m where m.topic.id=t.id)";

sql = "from Topic t where exists(select m.id from Msg m where m.topic.id=t.id)";


9.3使用executeUpdate进行修改和删除操作等,运行testHQL22

sql = "update Topic t set t.title=upper(t.title)";

q.executeUpdate();


10.其它查询形式

10.1使用NamedQuery进行查询,运行testHQL23

需要先使用annotation进行声明

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

Query q = session.getNamedQuery("topic.selectCertainTopic");


10.2使用NativeSQL进行查询,运行testHQL24

注意,此时使用的不是hibernate的对象查询,二是使用sql进行查询,类似于JDBC

SQLQuery q = session.createSQLQuery("select * from category").addEntity(Category.class);


以上主要探讨了hibernate使用Query进行查询的情况,需要在具体的使用中仔细体会。


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值