Hibernate查询功能 hibernate hql使用 hibernate 使用sql 使用Query执行hql,不用find

Hibernate查询功能 hibernate hql使用 hibernate 使用sql 使用Query执行hql,不用find
Hibernate学习笔记19---HQL3（简单属性查询和实体对象查询）
2009/12/01 17:30
------------------------------☆☆☆简单属性查询☆☆☆☆------------------------------------
①，单一属性的查询
public void testquery1(){
Session session = null;
try{
session = HibernateUtils2.singleInstance.getSession();
session.beginTransaction();
//从Student实体中查询name属性,Student是实体类名，name是Student类中的属性名
//返回结果集属性列表，元素类型和实体类中相应的属性类型一致（这里name是String类型，那么List中元素的类型就是String）
List names = session.createQuery("select name from Student").list();
for (Iterator iter = names.iterator();iter.hasNext();) {
System.out.println("StudentName : " + iter.next());
}
}catch(Exception e){
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils2.singleInstance.closeSession(session);
}
}

②，多个属性查询
public void testquery2(){
Session session = null;
try{
session = HibernateUtils2.singleInstance.getSession();
session.beginTransaction();

//笔记HQL1中自定义的带参数的构造方法这里就用上了，
//这样可以查询实体中的对象
List names = session.createQuery("select new Student(id, name) from Student").list();
for (Iterator iter = names.iterator();iter.hasNext();) {
Student student = (Student)iter.next();
System.out.println("StudentId : " + student.getId() + ", StudentName : " + student.getName());
}
}catch(Exception e){
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils2.singleInstance.closeSession(session);
}
}

//也可以这么查，查询指定的多个属性
List names = session.createQuery("select id, name from Student").list();
for (Iterator iter = names.iterator();iter.hasNext();) {
//一条结果集可以看作是一个对象数组，因为数组具体是什么类型难以确定
Object[] obj = (Object[])iter.next();
//对象数组按你指定的顺序（id，name）排列，那么0就是id，1就是name
System.out.println("StudentId : " + obj[0] + ", StudentName : " + obj[1]);
}

------------------------------☆☆☆实体对象查询☆☆☆☆------------------------------------

①，from Student
public void testquery1(){
Session session = null;
try{
session = HibernateUtils2.singleInstance.getSession();
session.beginTransaction();

//返回Studen对象集合，(可以省略select)
List lists = session.createQuery("from Student").list();
System.out.println(lists.size());
for (Iterator iter = lists.iterator();iter.hasNext();) {
Student student = (Student)iter.next();
System.out.println("StudentID : " + student.getId());
System.out.println("StudentName : " + student.getName());
}
}catch(Exception e){
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils2.singleInstance.closeSession(session);
}
}
②，from Student s
//返回Studen对象集合，(可以省略select)
//对于实体对象名，可以使用别名
List lists = session.createQuery("from Student s").list();
System.out.println(lists.size());
for (Iterator iter = lists.iterator();iter.hasNext();) {
Student student = (Student)iter.next();
System.out.println("StudentID2 : " + student.getId());
System.out.println("StudentName2 : " + student.getName());
}
//上下的代码就省略了，和①里的一样

③，from Student as s
//使用别名的时候可以用as，也可以不用像②那样
List lists = session.createQuery("from Student as s").list();
System.out.println(lists.size());
for (Iterator iter = lists.iterator();iter.hasNext();) {
Student student = (Student)iter.next();
System.out.println("StudentID2 : " + student.getId());
System.out.println("StudentName2 : " + student.getName());
}
//上下的代码就省略了，和①里的一样
④，select s from Student as s
//可以使用select，但使用select时，必须使用别名在先，因为select后要跟别名
//而且，不支持select * from 这样的查询
List lists = session.createQuery("select s from Student as s").list();
System.out.println(lists.size());
for (Iterator iter = lists.iterator();iter.hasNext();) {
Student student = (Student)iter.next();
System.out.println("StudentID2 : " + student.getId());
System.out.println("StudentName2 : " + student.getName());
}
//上下的代码就省略了，和①里的一样
------------------------------☆☆☆实体对象查询（ 有关list()和iterator() ）☆☆☆☆------------------------------------
①，Iterator
//除了list()外iterator()也可以保存查询结果，
//不过iterator()会发生1+N问题，1是查询出主键,N即根据主键查询出每一行结果
//这样每一行结果都会发出一条SQL语句，严重影响性能
Iterator iter = session.createQuery("from Student").iterate();
while (iter.hasNext()) {
Student student = (Student)iter.next();
System.out.println("StudentID : " + student.getId());
System.out.println("StudentName : " + student.getName());
}
②，iterator的二次查询会使用1级缓存
//第一次查询用list()，list()会把结果集放入1级缓存
List lists = session.createQuery("from Student").list();
System.out.println(lists.size());
for (Iterator iter = lists.iterator();iter.hasNext();) {
Student student = (Student)iter.next();
System.out.println("StudentID : " + student.getId());
System.out.println("StudentName : " + student.getName());
}

System.out.println("---------------------------------------");

//第二次查询使用iterator()，iterator()会直接到1级缓存中寻找数据
//这样就不会发出1+N个SQL语句了，只发出一个查询主键的即可
Iterator iter2 = session.createQuery("from Student").iterate();
while (iter2.hasNext()) {
Student student = (Student)iter2.next();
System.out.println("StudentID : " + student.getId());
System.out.println("StudentName : " + student.getName());
}

③，list()默认不会到1级缓存中查找数据
//list()会把查询到的结果放到1级缓存中
List lists = session.createQuery("from Student").list();
....省略
//但是list()默认情况下，不会去1级缓存查找数据
List lists2 = session.createQuery("from Student").list();
....省略


查看文章
Hibernate学习笔记19---HQL4（条件查询）
Hibernate查询where条件过滤
用Query执行hql语句，find已经不用了
2009/12/03 10:05
在HQL中也是可以使用where条件语句的，下面看看例子吧：
(实体类和实体类配置文件都沿用HQL1中的文件)

①，普通的where语句（参数含在HQL语句中）
public void testquery1(){
Session session = null;
try{
session = HibernateUtils2.singleInstance.getSession();
session.beginTransaction();

//where条件句也是可以用的
List lists = session.createQuery("select id, name from Student where name like '%1%'").list();
for (Iterator iter = lists.iterator();iter.hasNext();) {
Object[] obj = (Object[])iter.next();
System.out.println("StudentID : " + obj[0]);
System.out.println("StudentName : " + obj[1]);
}
}catch(Exception e){
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils2.singleInstance.closeSession(session);
}
}

②，可以在HQL语句之外指定参数 query.setParameter(数字，参数)
//？用作占位符
//参数的索引是从0开始
Query query = session.createQuery("select id, name from Student where name like ?");
query.setParameter(0, "%1%");
List lists = query.list();
for (Iterator iter = lists.iterator();iter.hasNext();) {
Object[] obj = (Object[])iter.next();
System.out.println("StudentID2 : " + obj[0]);
System.out.println("StudentName2 : " + obj[1]);
}

②-1，上面的例子还可以写成下面这样
//因为createQuery方法返回的也是Query对象，所以可以直接.出Query的setParameter方法
//setParameter也返回Query对象，所以也可以.出Query的list()方法
List lists = session.createQuery("select id, name from Student where name like ?")
.setParameter(0, "%1%")
.list();
for (Iterator iter = lists.iterator();iter.hasNext();) {
Object[] obj = (Object[])iter.next();
System.out.println("StudentID2 : " + obj[0]);
System.out.println("StudentName2 : " + obj[1]);
}


③，还可以这样指定query.setParameter(名称，参数)
//像这样就不用占位符了，直接用参数名代替，：参数名
List lists = session.createQuery("select s.id, s.name from Student s where name like :myname")
.setParameter("myname", "%1%")
.list();
for (Iterator iter = lists.iterator();iter.hasNext();) {
Object[] obj = (Object[])iter.next();
System.out.println("StudentID3 : " + obj[0]);
System.out.println("StudentName3 : " + obj[1]);
}

④，where ... and
public void testquery4(){
Session session = null;
try{
session = HibernateUtils2.singleInstance.getSession();
session.beginTransaction();

//将日期格式化为yyyy-MM的格式，方便后面使用
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM");
//where后可以接and或or语句，这里是and
//找出1班的，创建日期在2009年3月之前的学生
List lists = session.createQuery("select s.id, s.name, s.createDate from Student s where s.name like :name and s.createDate < :createDate")
.setParameter("name", "%クラス1の%")
//format.parse方法是将String转换为Date的唯一方法，我知道的^_^
.setParameter("createDate", format.parse("2009-03"))
.list();
for (Iterator iter = lists.iterator();iter.hasNext();) {
Object[] obj = (Object[])iter.next();
System.out.println("StudentID4 : " + obj[0]);
System.out.println("StudentName4 : " + obj[1]);
System.out.println("StudentCreateDate4 : " + obj[2]);
}

}catch(Exception e){
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils2.singleInstance.closeSession(session);
}
}

⑤，where ... in
//setParameterList，里面可以放集合或对象数组，这样就可以指定一组参数了
//查询学号为1，2，3，4，5的五名学生的信息
List lists = session.createQuery("select s.id, s.name from Student s where s.id in(:myids)")
.setParameterList("myids", new Object[]{1,2,3,4,5})
.list();
for (Iterator iter = lists.iterator();iter.hasNext();) {
Object[] obj = (Object[])iter.next();
System.out.println("StudentID5 : " + obj[0]);
System.out.println("StudentName5 : " + obj[1]);
}

⑤-2，上面的例子还可以写成这样
//使用多个？号占位符,查询学号为10，22，35，48，54 五名学生的信息
List lists = session.createQuery("select s.id, s.name from Student s where s.id in(?,?,?,?,?)")
.setParameter(0, 10)
.setParameter(1, 22)
.setParameter(2, 35)
.setParameter(3, 48)
.setParameter(4, 54)
.list();
for (Iterator iter = lists.iterator();iter.hasNext();) {
Object[] obj = (Object[])iter.next();
System.out.println("StudentID5_2 : " + obj[0]);
System.out.println("StudentName5_2 : " + obj[1]);
}

⑥，可以使用数据库的内置函数 (这里使用的是date_format)
//查找2009-02入学的学生
List lists = session.createQuery("select s.id, s.name, s.createDate from Student s where date_format(s.createDate,'%Y-%m') = ?")
.setParameter(0, "2009-02")
.list();
for (Iterator iter = lists.iterator();iter.hasNext();) {
Object[] obj = (Object[])iter.next();
System.out.println("StudentID6 : " + obj[0]);
System.out.println("StudentName6 : " + obj[1]);
System.out.println("StudentCreateDate6 : " + obj[2]);
}

⑦，可以使用between and
//支持between and
//需要注意的是setParameter(数字,对象)，对象这块是任何类型都可以放的，这里是时间，不过先得从字符串转换才行
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
List lists = session.createQuery("select s.id, s.name, s.createDate from Student s where s.createDate between ? and ?")
//查找从2009-03到2009-05入学的学生
.setParameter(0, format.parse("2009-03-01 00:00:00"))
.setParameter(1, format.parse("2009-05-31 23:59:59"))
.list();
for (Iterator iter = lists.iterator();iter.hasNext();) {
Object[] obj = (Object[])iter.next();
System.out.println("StudentID5 : " + obj[0]);
System.out.println("StudentName5 : " + obj[1]);
System.out.println("StudentCreateDate5 : " + obj[2]);


Hibernate学习笔记19---HQL5(原生SQL，外置命名查询,查询过滤器，分页查询，对象导航查询)
2009/12/04 10:53
①，原生SQL查询 用createSQLQuery，后面就可以跟标准的SQL语句了
public void testquery1(){
Session session = null;
try{
session = HibernateUtils2.singleInstance.getSession();
session.beginTransaction();

//createSQLQuery支持原生标准SQL查询，Hibernate3.0后引入
List lists = (List)session.createSQLQuery("select * from t_student").list();
for (Iterator iter = lists.iterator();iter.hasNext();) {
Object[] obj = (Object[])iter.next();
System.out.println("StudentID : " + obj[0]);
System.out.println("StudentName : " + obj[1]);
System.out.println("StudentCreateDate : " + obj[2]);
System.out.println("StudentClassid : " + obj[3]);
}
}catch(Exception e){
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils2.singleInstance.closeSession(session);
}
}

②，外部命名查询,即把HQL语句写在配置文件中，用到的时候再调用即可
Student.hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="com.ahuzl.hibernate.Student" table="t_student">
<id name="id" column="student_id">
<generator class="native"/>
</id>
<property name="name" column="student_name"/>
<property name="createDate"/>
<many-to-one name="classes" column="classid"/>
</class>
<query name="searchStudent">
<![CDATA[
select s from Student s where s.id < ?
]]>
</query>
</hibernate-mapping>
其实关键就是后面Query那句话,这段语句放在任何一个配置文件中都行，（比如放在Class.hbm.xml中，这里因为是查询Student信息，从逻辑上考虑就放在了Student.hbm.xml中）
<query name="searchStudent">
<![CDATA[
select s from Student s where s.id < ?
]]>
</query>
③，查询过滤器，其实就是在每一句HQL后都加上一个提前设定的条件（比如只让查找某个用户的东西啦）
Student.hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="com.ahuzl.hibernate.Student" table="t_student">
<id name="id" column="student_id">
<generator class="native"/>
</id>
<property name="name" column="student_name"/>
<property name="createDate"/>
<many-to-one name="classes" column="classid"/>
<!-- 定义好了查询过滤器之后，就可以拿来用了。条件里面有大于号小于号的，要用转义字符代替(因为XML会把<>识别为自己的开始结束符号) -->
<filter name="filterTest" condition="student_id < :myid"/>
</class>

<query name="searchStudent">
<![CDATA[
select s from Student s where s.id < ?
]]>
</query>

<!-- 先定义一个查询过滤器 -->
<filter-def name="filterTest">
<filter-param name="myid" type="integer"/>
</filter-def>
</hibernate-mapping>

查询
public void testquery1(){
Session session = null;
try{
session = HibernateUtils2.singleInstance.getSession();
session.beginTransaction();

//先启用过滤器，然后设置参数
session.enableFilter("filterTest").setParameter("myid", 10);
List lists = (List)session.createQuery("from Student").list();
for (Iterator iter = lists.iterator();iter.hasNext();) {
Student student = (Student)iter.next();
System.out.println("StudentID : " + student.getId());
System.out.println("StudentName : " +student.getName());
System.out.println("StudentCreateDate : " + student.getCreateDate());
System.out.println("StudentClassid : " + student.getClasses().getName());
}
}catch(Exception e){
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils2.singleInstance.closeSession(session);
}
}

④，分页查询
//从第0条开始，到第5条结束
List lists = (List)session.createQuery("from Student")
.setFirstResult(0)
.setMaxResults(5)
.list();
for (Iterator iter = lists.iterator();iter.hasNext();) {
Student student = (Student)iter.next();
System.out.println("StudentID : " + student.getId());
System.out.println("StudentName : " +student.getName());
}

⑤，对象导航查询
仔细看看HQL中的这句： s.classes.name 这真是把对象和SQL揉到一起了啊
List lists = (List)session.createQuery("select s.name from Student s where s.classes.name like '%1%'").list();
for (Iterator iter = lists.iterator();iter.hasNext();) {
String name = (String)iter.next();
System.out.println("StudentName : " + name);
}


类别：Hibernate | | 添加到搜藏 | 分享到i贴吧 | 浏览(413) | 评论 (0)

上一篇：Hibernate学习笔记19---HQL4（条... 下一篇：Hibernate学习笔记19---HQL6(内...

Hibernate学习笔记19---HQL6(内连接，左连接，外连接)
2009/12/08 17:21
内连接：把两面共有的部分查出来
左连接：把左边的部分全查出来
右连接：把右边的部分全查出来
/**
* inner join
*/
public void testquery1(){
Session session = null;
try{
session = HibernateUtils2.singleInstance.getSession();
session.beginTransaction();

//可以省略inner，因为只要写了join，默认就是inner内连接
//List lists = (List)session.createQuery("select c.name, s.name from Student s join s.classes c").list();

//写上的话，就是这个样子啦
List lists = (List)session.createQuery("select c.name, s.name from Student s inner join s.classes c").list();
for (Iterator iter = lists.iterator();iter.hasNext();) {
Object[] obj = (Object[])iter.next();
System.out.println(obj[0] + "," + obj[1]);
}
}catch(Exception e){
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils2.singleInstance.closeSession(session);
}
}
/**
* left join
*/
public void testquery2(){
Session session = null;
try{
session = HibernateUtils2.singleInstance.getSession();
session.beginTransaction();

//把没有班级的学生也查出来，注意left join 后接的表有点特殊，是c.student即班级中的student属性，hibernate会依照这个属性帮我们找到相应的表
List lists = (List)session.createQuery("select c.name, s.name from Student s left join c.student s").list();
for (Iterator iter = lists.iterator();iter.hasNext();) {
Object[] obj = (Object[])iter.next();
System.out.println(obj[0] + "," + obj[1]);
}
}catch(Exception e){
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils2.singleInstance.closeSession(session);
}
}
/**
* right join
*/
public void testquery2(){
Session session = null;
try{
session = HibernateUtils2.singleInstance.getSession();
session.beginTransaction();

//把没有学生的班级也查出来，注意right join 后接的也比较特殊，理由同上
List lists = (List)session.createQuery("select c.name, s.name from Student s right join c.student s").list();
for (Iterator iter = lists.iterator();iter.hasNext();) {
Object[] obj = (Object[])iter.next();
System.out.println(obj[0] + "," + obj[1]);
}
}catch(Exception e){
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils2.singleInstance.closeSession(session);
}
}

Hibernate学习笔记19---HQL6(内连接，左连接，外连接)
2009/12/08 17:21
内连接：把两面共有的部分查出来
左连接：把左边的部分全查出来
右连接：把右边的部分全查出来
/**
* inner join
*/
public void testquery1(){
Session session = null;
try{
session = HibernateUtils2.singleInstance.getSession();
session.beginTransaction();

//可以省略inner，因为只要写了join，默认就是inner内连接
//List lists = (List)session.createQuery("select c.name, s.name from Student s join s.classes c").list();

//写上的话，就是这个样子啦
List lists = (List)session.createQuery("select c.name, s.name from Student s inner join s.classes c").list();
for (Iterator iter = lists.iterator();iter.hasNext();) {
Object[] obj = (Object[])iter.next();
System.out.println(obj[0] + "," + obj[1]);
}
}catch(Exception e){
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils2.singleInstance.closeSession(session);
}
}
/**
* left join
*/
public void testquery2(){
Session session = null;
try{
session = HibernateUtils2.singleInstance.getSession();
session.beginTransaction();

//把没有班级的学生也查出来，注意left join 后接的表有点特殊，是c.student即班级中的student属性，hibernate会依照这个属性帮我们找到相应的表
List lists = (List)session.createQuery("select c.name, s.name from Student s left join c.student s").list();
for (Iterator iter = lists.iterator();iter.hasNext();) {
Object[] obj = (Object[])iter.next();
System.out.println(obj[0] + "," + obj[1]);
}
}catch(Exception e){
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils2.singleInstance.closeSession(session);
}
}
/**
* right join
*/
public void testquery2(){
Session session = null;
try{
session = HibernateUtils2.singleInstance.getSession();
session.beginTransaction();

//把没有学生的班级也查出来，注意right join 后接的也比较特殊，理由同上
List lists = (List)session.createQuery("select c.name, s.name from Student s right join c.student s").list();
for (Iterator iter = lists.iterator();iter.hasNext();) {
Object[] obj = (Object[])iter.next();
System.out.println(obj[0] + "," + obj[1]);
}
}catch(Exception e){
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils2.singleInstance.closeSession(session);
}
}