Hibernate5.2之原生SQL查询

Hibernate5.2之原生SQL查询

一. 介绍

在上一篇博客中笔者通过代码的形式给各位读者介绍了中最重要的检索方式–HQL查询。在本博文中笔者将向各位读者介绍中的原生SQL查询Hibernate5.2之原生SQL查询，虽为原生SQL查询hibernate 原生sqlhibernate 原生sql，但是笔者认为在针对不同的数据库在分页语句的处理上做的很不错，我们不用去关心使用的是何种的数据库。本博文会沿用《.2之HQL查询》中的POJO类和配置文件，请各位看官在阅读本博文之前请先阅读该文章，本篇文章会将HQL中涉及的相关操作原封不动的全部改为SQL查询hibernate 原生sql，所以在代码上的重复度比较的高，请各位读者勿喷Hibernate5.2之原生SQL查询，那我们就直接上单元测试了。

二. 单元测试

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

@Testpublic void list(){    String sql = "select * from orders";    NativeQuery query = session.createNativeQuery(sql, Order.class);    List list = query.getResultList();    for(Order o : list){         System.out.println(o.getId() + "::" + o.getOrderId());    }}

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

/** * 虽然为原生的SQL查询，但是依然可以使用setFirstResult()和setMaxResults()方法。从而屏蔽了 * 底层数据库的差异性。 */@Testpublic void pageList(){    String sql = "select * from orders";    //setFirstResult()从0开始    Query query = session.createNativeQuery(sql, Order.class).setFirstResult(1).setMaxResults(4);    List list = query.getResultList();    for(Order o : list){         System.out.println(o.getId());    }}

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

@Testpublic void multiCretiera(){    String sql = "select * from orders where create_time between ? and ? and order_id like ?";    Query query = session.createNativeQuery(sql, Order.class);    String beginDateStr = "2016-07-26 00:00:00";    String endDateStr = "2016-07-28 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();    }    //分页从0开始    query.setParameter(0, beginDate).setParameter(1, endDate).setParameter(2, "%D%").setFirstResult(0).setMaxResults(1);    List list = query.getResultList();    for(Order o : list){         System.out.println(o.getOrderId() + "::" + o.getCreateTime());    }}

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

@Testpublic void multiCretiera1(){    String sql = "select * from orders where order_id like :orderId and create_time between :beginDate and :endDate";    Query query = session.createNativeQuery(sql, Order.class);    String beginDateStr = "2016-07-26 00:00:00";    String endDateStr = "2016-07-28 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 list = query.getResultList();    for(Order o : list){         System.out.println(o.getId() + "::" + o.getOrderId());    }
}

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

@Testpublic void gt(){    String sql = "select * from orders where id > ?";    Query query = session.createNativeQuery(sql, Order.class).setParameter(0, 3);    List list = query.getResultList();    for(Order o : list){        System.out.println(o.getId() + "::" + o.getOrderId());    }}

F.删除操作

@Testpublic void delete(){    String sql = "delete from orders where id in (:idList)";     Transaction tx = session.beginTransaction();    List list = new ArrayList();    list.add(1);    list.add(2);    Query query = session.createNativeQuery(sql).setParameter("idList", list);    int i = query.executeUpdate();    System.out.println(i);    tx.commit();    session.close();}

G.获取某一列的值

@Testpublic void singleValue(){    String sql = "select order_id from orders";    Query query = session.createNativeQuery(sql);    List list = query.getResultList();    for(String str : list){        System.out.println(str);    }}

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

@Testpublic void getCustomer(){    String sql = "select c.* from orders o join customer c on o.customer_id = c.id where c.id = 8";    Query query = session.createNativeQuery(sql, Customer.class);    List list = query.getResultList();    for(Customer o : list){        System.out.println(o.getId() + ";;");    }}

I.多列数据的查询

@Testpublic void getObjectArray(){    String sql = "select c.name, c.phone_number, o.order_id, o.create_time from orders o join customer c on o.customer_id = c.id";    Query query = session.createNativeQuery(sql);    List list = query.getResultList();    for(Object[] o : list){         System.out.println(o[0] + ";;" + o[1] + ";;" + o[2]);    }}

J.函数查询

@Testpublic void functionQuery(){    String sql = "select max(id), count(*) from orders";    Query query = session.createNativeQuery(sql);    Object[] obj = query.getSingleResult();    System.out.println(obj[0] + "::" + obj[1]);}

K.排序

@Testpublic void descQuery(){    String sql = "select * from orders order by id desc";    Query query = session.createNativeQuery(sql, Order.class);    List list = query.getResultList();    for(Order o : list){        System.out.println(o.getId() + "::" + o.getOrderId());    }}

L.右连接

@Testpublic void rightJoin(){    String sql = "select c.* from orders o right join customer c on o.customer_id = c.id";    Query query = session.createNativeQuery(sql, Customer.class);    List list = query.getResultList();    for(Customer c : list){        System.out.println(c.getId());    }}