Hibernate5.2之HQL查询

Hibernate5.2之HQL查询                                                                 

一. 介绍

　　 Hibernate的检索方式分为4种：HQL查询、原生SQL查询、OID查询、QBC查询。本博客将会出系列教程分别来介绍这四种查询方式，那么第一篇文章说什么了，笔者认为当然是HQL的查询。那么是什么是HQL的查询了，HQL的全称是Hibernate Query Language, 是面向对象的查询语言，他和SQL语言有些类似，在Hibernate所提供的各种检索方式中，HQL是使用上最广泛的。按照笔者一贯的风格，直接上代码，本文所用的数据库为Mysql数据库。

二. 数据库的创建

　　 创建orders和customer两张表(即订单、客户表，一个客户可以有多个订单)，customer和orders是一对多的关系，建表语句如下：　

create table customer(
    id int(11) primary key auto_increment,
    name varchar(20),
    phone_number varchar(20)  
);

create table orders(
　　id int(11) primary key auto_increment,
　　order_id varchar(20),
　　create_time datetime,
　　customer_id int(11)
);

       数据库中已有数据以下两张图所示：

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　   customer表                                                                                                                            

                                                    

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　     orders表

        

三. POJO类的创建

public class Customer {
    private int id;
    private String name;
    private String phoneNum;
    private Set<Order> orderSet;
    //setter and getter
}


public class Order {
    private int id;
    private String orderId;
    private Date createTime;
    private Customer customer;
    
    public Order() {}
    
    public Order(int id, String orderId, Date createTime) {
        this.id = id;
        this.orderId = orderId;
        this.createTime = createTime;
    }
    //setter and getter
}

四. hbm文件配置

Customer.hbm.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
    <class name="com.demo.hibernate.one2many.Customer" table="customer">
        <id name="id" type="int">
            <generator class="increment"></generator>
        </id>
        <property name="name" type="string" column="name"></property>
        <property name="phoneNum" type="string" column="phone_number"></property>
        <!-- inverse=true表示由对方去维护关联关系 -->
        <set name="orderSet" inverse="true">
            <key column="customer_id"></key>
            <one-to-many class="com.demo.hibernate.one2many.Order"/>
        </set>
    </class>
</hibernate-mapping>

Order.hbm.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
    <class name="com.demo.hibernate.one2many.Order" table="orders">
        <id name="id" type="int">
            <generator class="increment"></generator>
        </id>
        <property name="orderId" column="order_id" type="string"></property>
        <property name="createTime" column="create_time" type="timestamp"></property>
        <many-to-one name="customer" class="com.demo.hibernate.one2many.Customer" column="customer_id"></many-to-one>
    </class>
</hibernate-mapping>

五. 编写单元测试

　　在编写单元测试之读者需要在hibernate.cfg.xml文件中加入以上两个hbm文件，在此笔者没有给出相应的配置，读者可查询相关资料自行完成配置。如果有问题，请给笔者留言。

5.1 创建工具类

public class SessionUtils {
    //获取SessionFactory
    public static SessionFactory getSessionFactory(){
        StandardServiceRegistry registry = null;
        SessionFactory sessionFactory = null;
        try{
            registry = new StandardServiceRegistryBuilder().configure("hibernate.cfg.xml").build();
            //不指定文件名默认是找hibernate.cfg.xml文件
            //registry = new StandardServiceRegistryBuilder().configure().build(); 
            sessionFactory = new MetadataSources(registry).buildMetadata().buildSessionFactory();
        }catch(Exception ex){
            ex.printStackTrace();
            StandardServiceRegistryBuilder.destroy(registry);
        }
        return sessionFactory;
    }
     
    //打开并返回一个Session 
    public static Session openSession(){
        return getSessionFactory().openSession();
    }
    
    //关闭Session
    public static void closeSession(Session session){
        if(null != session){
            session.close();
        }
    }
}

5.2 编写测试用例

       有关于HQL的查询都会在以下的单元测试类中完成：

public class HibernateTest {
    private Session session;
    
    @Before
    public void openSession(){
        session = SessionUtils.openSession(); //打开会话
    }
    
    @After
    public void closeSession(){
        SessionUtils.closeSession(session);
    }
}

       A.获取所有的Order对象，得到一个List集合

@Test
public void list(){
    String hql = "from Order";
    Query<Order> query = session.createQuery(hql, Order.class);
    List<Order> list = query.getResultList();
    for(Order o : list){
        System.out.println(o.getOrderId() + "::" + o.getCreateTime());
    }
}

     B.获取Order的分页数据，得到一个List集合

@Test
public void pageList(){
    String hql = "from Order";
    Query<Order> query = session.createQuery(hql, Order.class);
    /**
     * setFirstResult() 是设置从第几条开始
     * setMaxResults() 是设置每页多少条数据
     */
    query.setFirstResult(0).setMaxResults(4); 
    List<Order> list = query.getResultList();
    for(Order o : list){
        System.out.println(o.getOrderId() + "::" + o.getCreateTime());
    }
}

    C.多条件查询，返回List集合(第一种形式：索引占位符)

@Test
public void multiCretiera(){
    String hql = "from Order where orderId like ? and createTime between ? and ?";
    Query<Order> query = session.createQuery(hql, Order.class);
    String beginDateStr = "2016-07-26 00:00:00";
    String endDateStr = "2016-07-26 23:59:59";
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
    Date beginDate = null;
    Date endDate = null;
    try {
        beginDate = sdf.parse(beginDateStr);
        endDate = sdf.parse(endDateStr);
    } catch (ParseException e) {
        e.printStackTrace();
    }
    query.setParameter(0, "%D%").setParameter(1, beginDate).setParameter(2, endDate);
    List<Order> list = query.getResultList();
    for(Order o : list){
        System.out.println(o.getOrderId() + "::" + o.getCreateTime());
    }
}

     E.多条件查询，返回List集合(第二种形式：命名占位符)

@Test
public void multiCretiera1(){
    String hql = "from Order where orderId like :orderId and createTime between :beginDate and :endDate";
    Query<Order> query = session.createQuery(hql, Order.class);
    String beginDateStr = "2016-07-26 00:00:00";
    String endDateStr = "2016-07-26 23:59:59";
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
    Date beginDate = null;
    Date endDate = null;
    try {
        beginDate = sdf.parse(beginDateStr);
        endDate = sdf.parse(endDateStr);
    } catch (ParseException e) {
        e.printStackTrace();
    }
    query.setParameter("orderId", "%D%").setParameter("beginDate", beginDate).setParameter("endDate", endDate);
    List<Order> list = query.getResultList();
    for(Order o : list){
         System.out.println(o.getId() + "::" + o.getOrderId());
    }
}

    F.大于条件的查询，使用索引占位符

@Test
public void gt(){
    String hql = "from Order where id > ?";
    Query<Order> query = session.createQuery(hql, Order.class).setParameter(0, 3);
    List<Order> list = query.getResultList();
    for(Order o : list){
        System.out.println(o.getId() + "::" + o.getOrderId());
    }
}

    G.更新操作

@Test
public void update(){
    String hql = "update Order set createTime = ? where id = ?";
    Transaction tx = session.beginTransaction();
    Query<?> query = session.createQuery(hql).setParameter(0, new Date()).setParameter(1, 1);
    int i = query.executeUpdate();
    System.out.println(i);
    tx.commit();
    session.close();
}

     H.删除操作

@Test
public void delete(){
    //in 后面的括号可加可不加
    String hql = "delete from Order where id in (:idList)"; 
    Transaction tx = session.beginTransaction();
    List<Integer> list = new ArrayList<Integer>();
    list.add(1);
    list.add(2);
    Query<?> query = session.createQuery(hql).setParameter("idList", list);
    int i = query.executeUpdate();
    System.out.println(i);
    tx.commit();
    session.close();
}

    I.获取某一列的值

@Test
public void singleValue(){
    String hql = "select orderId from Order";
    Query<String> query = session.createQuery(hql, String.class);
    List<String> list = query.getResultList();
    for(String o : list){
        System.out.println(o);
    }
}

    J.使用对象的方式封装结果数据

@Test
public void getOrdersList(){
    String hql = "select new com.demo.hibernate.one2many.Order(o.id, o.orderId, o.createTime) from Order as o";
    Query<Order> query = session.createQuery(hql, Order.class);
    List<Order> list = query.getResultList();
    for(Order o : list){
        System.out.println(o.getId() + ";;" + o.getOrderId() + ";;");
     }
}

     K.获取关联对象的结果集

@Test
public void getCustomer(){
    String hql = "select o.customer from Order o where o.customer.id = 6";
    Query<Customer> query = session.createQuery(hql, Customer.class);
    List<Customer> list = query.getResultList();
    for(Customer o : list){
        System.out.println(o.getId() + ";;");
    }
}

     L.多列数据的查询

@Test
public void getObjectArray(){
    //String hql = "select o.orderId, o.createTime from Order o";
    //String hql = "select o.customer.name, o.customer.phoneNum, o.orderId, o.createTime from Order o";
    //String hql = "select o.orderId, o.createTime from Order o join o.customer c";
    String hql = "select c.name, c.phoneNum, o.orderId, o.createTime from Order o join o.customer c";
    Query<Object[]> query = session.createQuery(hql, Object[].class);
    List<Object[]> list = query.getResultList();
    for(Object[] o : list){
        System.out.println(o[0] + ";;" + o[1] + ";;");
    }
}

     M.函数查询

@Test
public void functionQuery(){
    String hql = "select max(id), count(*) from Order";
    Query<Object[]> query = session.createQuery(hql, Object[].class);
    Object[] obj = query.getSingleResult();
    System.out.println(obj[0] + "::" + obj[1] + "::");
}

     N.排序

@Test
public void descQuery(){
    String hql = "from Order order by id desc";
    Query<Order> query = session.createQuery(hql, Order.class);
    List<Order> list = query.getResultList();
    for(Order o : list){
        System.out.println(o.getId() + "::" + o.getOrderId());
    }
}

     O.右连接

@Test
public void rightJoin(){
    String hql = "select o.customer from Order o right join o.customer";
    Query<Customer> query = session.createQuery(hql, Customer.class);
    List<Customer> list = query.getResultList();
    for(Customer c : list){
        System.out.println(c.getId());
    }
}