Hibernate实战——使用HQL查询

我就是我 2021-07-24 11:14 534阅读 0赞

**一 配置文件**

<?xml version="1.0" encoding="GBK"?>
    
    <!DOCTYPE hibernate-configuration PUBLIC
         "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
         "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
    
    <hibernate-configuration>
         <session-factory>
               
               <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">32147</property>
               
               <property  name="hibernate.c3p0.max_size">20</property>
               
               <property name="hibernate.c3p0.min_size">1</property>
               
               <property  name="hibernate.c3p0.timeout">5000</property>
               
               <property  name="hibernate.c3p0.max_statements">100</property>
               <property  name="hibernate.c3p0.idle_test_period">3000</property>
               <property  name="hibernate.c3p0.acquire_increment">2</property>
               <property  name="hibernate.c3p0.validate">true</property>
               
               <property  name="dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>
               
               <property name="hbm2ddl.auto">update</property>
               
               <property name="show_sql">true</property>
               
               <property name="hibernate.format_sql">true</property>
               
               <mapping class="org.crazyit.app.domain.Person"/>
               <mapping class="org.crazyit.app.domain.MyEvent"/>
         </session-factory>
    </hibernate-configuration>

**二 PO**

1 Person

package org.crazyit.app.domain;
    
    import java.util.*;
    
    import javax.persistence.*;
    
    @Entity
    @Table(name = "person_inf")
    public class Person
    {
        // 定义标识属性
        @Id @Column(name = "person_id")
        @GeneratedValue(strategy=GenerationType.IDENTITY)
        private Integer id;
        // 定义Person实例的name成员变量
        private String name;
        // 定义Person实例的age成员变量
        private int age;
        // 定义该Person实体关联的所有MyEvent实体
        @ManyToMany(targetEntity=MyEvent.class)
        @JoinTable(name = "person_event" ,
            joinColumns = @JoinColumn(name = "person_id"
                , referencedColumnName="person_id"),
            inverseJoinColumns = @JoinColumn(name = "event_id"
                , referencedColumnName="event_id")
        )
        private Set<MyEvent> myEvents
            = new HashSet<>();
        // 定义一个集合属性
        // 集合属性，保留该对象关联的学校
        @ElementCollection(targetClass=String.class)
        @CollectionTable(name="person_email_inf",
            joinColumns=@JoinColumn(name="person_id" , nullable=false))
        @Column(name="email_detail" , nullable=false)
        private Set<String> emails
            = new HashSet<>();
    
        // id的setter和getter方法
        public void setId(Integer id)
        {
            this.id = id;
        }
        public Integer getId()
        {
            return this.id;
        }
    
        // name的setter和getter方法
        public void setName(String name)
        {
            this.name = name;
        }
        public String getName()
        {
            return this.name;
        }
    
        // age的setter和getter方法
        public void setAge(int age)
        {
            this.age = age;
        }
        public int getAge()
        {
            return this.age;
        }
    
        // myEvents的setter和getter方法
        public void setMyEvents(Set<MyEvent> myEvents)
        {
            this.myEvents = myEvents;
        }
        public Set<MyEvent> getMyEvents()
        {
            return this.myEvents;
        }
    
        // emails的setter和getter方法
        public void setEmails(Set<String> emails)
        {
            this.emails = emails;
        }
        public Set<String> getEmails()
        {
            return this.emails;
        }
    
    }

2 MyEvent

package org.crazyit.app.domain;
    
    import java.util.*;
    
    import javax.persistence.*;
    
    @Entity
    @Table(name="event_inf")
    public class MyEvent
    {
        // 定义标识属性
        @Id @Column(name="event_id")
        @GeneratedValue(strategy=GenerationType.IDENTITY)
        private Integer id;
        // 定义事件名称的成员变量
        private String title;
        // 定义事件发生时间的成员变量
        private Date happenDate;
        // 定义该MyEvent实体关联的所有Person实体
        @ManyToMany(targetEntity=Person.class , mappedBy="myEvents")
        private Set<Person> actors
            = new HashSet<>();
    
        // id的setter和getter方法
        public void setId(Integer id)
        {
            this.id = id;
        }
        public Integer getId()
        {
            return this.id;
        }
    
        // title的setter和getter方法
        public void setTitle(String title)
        {
            this.title = title;
        }
        public String getTitle()
        {
            return this.title;
        }
    
        // happenDate的setter和getter方法
        public void setHappenDate(Date happenDate)
        {
            this.happenDate = happenDate;
        }
        public Date getHappenDate()
        {
            return this.happenDate;
        }
    
        // actors的setter和getter方法
        public void setActors(Set<Person> actors)
        {
            this.actors = actors;
        }
        public Set<Person> getActors()
        {
            return this.actors;
        }
    }

**三 测试**

1 工具类

package lee;
    
    import org.hibernate.*;
    import org.hibernate.cfg.*;
    import org.hibernate.service.*;
    import org.hibernate.boot.registry.*;
    
    public class HibernateUtil
    {
        public static final SessionFactory sessionFactory;
    
        static
        {
            try
            {
                // 使用默认的hibernate.cfg.xml配置文件创建Configuration实例
                Configuration cfg = new Configuration()
                    .configure();
                // 以Configuration实例来创建SessionFactory实例
                ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder()
                    .applySettings(cfg.getProperties()).build();
                sessionFactory = cfg.buildSessionFactory(serviceRegistry);
            }
            catch (Throwable ex)
            {
                System.err.println("Initial SessionFactory creation failed." + ex);
                throw new ExceptionInInitializerError(ex);
            }
        }
    
        // ThreadLocal可以隔离多个线程的数据共享，因此不再需要对线程同步
        public static final ThreadLocal<Session> session
            = new ThreadLocal<Session>();
    
        public static Session currentSession()
            throws HibernateException
        {
            Session s = session.get();
            // 如果该线程还没有Session,则创建一个新的Session
            if (s == null)
            {
                s = sessionFactory.openSession();
                // 将获得的Session变量存储在ThreadLocal变量session里
                session.set(s);
            }
            return s;
        }
    
        public static void closeSession()
            throws HibernateException
        {
            Session s = session.get();
            if (s != null)
                s.close();
            session.set(null);
        }
    }

2 测试类

package lee;
    import org.hibernate.Transaction;
    import org.hibernate.Session;
    
    import java.text.DateFormat;
    import java.text.SimpleDateFormat;
    
    import java.util.*;
    
    import org.crazyit.app.domain.*;
    
    public class HqlQuery
    {
        public static void main(String[] args)
            throws Exception
        {
            HqlQuery mgr = new HqlQuery();
            // 调用查询方法
            mgr.findPersons();
            // 调用第二个查询方法
            mgr.findPersonsByHappenDate();
            // 调用第二个查询方法
            mgr.findPersonProperty();
        }
        // 第一个查询方法
        private void findPersons()
        {
            // 获得Hibernate Session
            Session sess = HibernateUtil.currentSession();
            // 开始事务
            Transaction tx = sess.beginTransaction();
            // 以HQL语句创建Query对象.
            List pl = sess.createQuery("select distinct p from Person p "
                + "join p.myEvents where title = :eventTitle")
                // 执行setString()方法为HQL语句的参数赋值
                .setString("eventTitle" , "很普通的事情")
                // Query调用list()方法获取查询的全部实例
                .list();
            // 遍历查询的全部结果
            for(Object ele : pl)
            {
                Person p = (Person)ele;
                System.out.println(p.getName());
            }
            // 提交事务
            tx.commit();
            HibernateUtil.closeSession();
        }
        // 第二个查询方法
        private void findPersonsByHappenDate()throws Exception
        {
            // 获得Hibernate Session对象
            Session sess = HibernateUtil.currentSession();
            Transaction tx = sess.beginTransaction();
            // 解析出Date对象
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            Date start = sdf.parse("2005-01-01");
            System.out.println("系统开始通过日期查找人" + start);
            // 通过Session的createQuery方法创建Query对象
            List pl = sess.createQuery("select distinct p from Person p "
                + "inner join p.myEvents event where event.happenDate "
                + "between :firstDate and :endDate")
                // 设置参数
                .setDate("firstDate" , start)
                .setDate("endDate" , new Date())
                // 返回结果集
                .list();
            // 遍历结果集
            for (Object ele : pl)
            {
                Person p = (Person)ele;
                System.out.println(p.getName());
            }
            tx.commit();
            HibernateUtil.closeSession();
        }
        // 第三个查询方法：查询属性
        private void findPersonProperty()
        {
            // 获得Hibernate Session
            Session sess = HibernateUtil.currentSession();
            // 开始事务
            Transaction tx = sess.beginTransaction();
            // 以HQL语句创建Query对象.
            List pl = sess.createQuery("select distinct p.id,  p.name , p.age "
                + "from Person p join p.myEvents")
                // Query调用list()方法访问查询得到的全部属性
                .list();
            // 遍历查询的全部结果
            for (Object ele : pl)
            {
                Object[] objs = (Object[])ele;
                System.out.println(java.util.Arrays.toString(objs));
            }
            // 提交事务
            tx.commit();
            HibernateUtil.closeSession();
        }
    }

**四 测试SQL脚本**

drop database if exists hibernate;
    
    create database hibernate;
    
    use hibernate;
    
    CREATE TABLE event_inf (
      event_id int(11) NOT NULL auto_increment,
      happenDate date default NULL,
      title varchar(255) default NULL,
      PRIMARY KEY  (event_id)
    );
    
    INSERT INTO event_inf VALUES
    (1,'2004-10-03','很高兴的事情'),
    (2,'2005-10-03','很普通的事情'),
    (3,'2004-10-04','疯狂Java筹备中'),
    (4,'2005-10-05','疯狂Java开始培训');
    
    CREATE TABLE person_inf (
      person_id int(11) NOT NULL auto_increment,
      name varchar(255) default NULL,
      age int(11) default NULL,
      PRIMARY KEY  (person_id)
    );
    
    
    INSERT INTO person_inf VALUES
    (1,'crazyit.org',30),
    (2,'老朱',30);
    
    
    CREATE TABLE person_email_inf (
      person_id int(11) NOT NULL,
      email_detail varchar(255) default NULL,
      KEY FKECD3B632CC53FFDC (person_id),
      FOREIGN KEY (person_id) REFERENCES person_inf (person_id)
    );
    
    INSERT INTO person_email_inf VALUES
    (1,'crazyit@crazyit.org'),
    (1,'crazyit@fkit.org'),
    (2,'dddd@163.com'),
    (2,'vvvvvv@163.com');
    
    CREATE TABLE person_event (
      person_id int(11) NOT NULL,
      event_id int(11) NOT NULL,
      PRIMARY KEY  (person_id,event_id),
      KEY FKECD7DD30273C5F2C (event_id),
      KEY FKECD7DD30CC53FFDC (person_id),
      FOREIGN KEY (person_id) REFERENCES person_inf (person_id),
      FOREIGN KEY (event_id) REFERENCES event_inf (event_id)
    );
    
    
    INSERT INTO person_event VALUES
    (1,1),
    (1,2),
    (2,2),
    (2,3),
    (1,4);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 1][]

**五 测试结果**

Hibernate:
        select
            distinct person0_.person_id as person_i1_3_,
            person0_.age as age2_3_,
            person0_.name as name3_3_
        from
            person_inf person0_
        inner join
            person_event myevents1_
                on person0_.person_id=myevents1_.person_id
        inner join
            event_inf myevent2_
                on myevents1_.event_id=myevent2_.event_id
        where
            title=?
    crazyit.org
    老朱
    -----------------------------------------------------------------------------
    系统开始通过日期查找人Sat Jan 01 00:00:00 CST 2005
    Hibernate:
        select
            distinct person0_.person_id as person_i1_3_,
            person0_.age as age2_3_,
            person0_.name as name3_3_
        from
            person_inf person0_
        inner join
            person_event myevents1_
                on person0_.person_id=myevents1_.person_id
        inner join
            event_inf myevent2_
                on myevents1_.event_id=myevent2_.event_id
        where
            myevent2_.happenDate between ? and ?
    crazyit.org
    老朱
    ---------------------------------------------------------------------------------
    Hibernate:
        select
            distinct person0_.person_id as col_0_0_,
            person0_.name as col_1_0_,
            person0_.age as col_2_0_
        from
            person_inf person0_
        inner join
            person_event myevents1_
                on person0_.person_id=myevents1_.person_id
        inner join
            event_inf myevent2_
                on myevents1_.event_id=myevent2_.event_id
    [1, crazyit.org, 30]
    [2, 老朱, 30]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70]: /images/20210724/2e56b8fde19b42a8ac5ce546411692f1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 1]: /images/20210724/f08482f105f045719ecbd2cac41b7dfe.png