当前位置： 代码迷 >> SQL >> Hibernate查询效能 hibernate hql使用 hibernate 使用sql 使用Query执行hql,不用find
  详细解决方案

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

热度：64   发布时间：2016-05-05 13:07:02.0
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 &lt; :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);
    }
}


  相关解决方案

代码迷 - 您身边的软件异常，代码异常，编程异常助手（发现，解决，分享）
Copyright © 2009-2013 DAIMAMI.COM. All rights reserved.