Hibernate5.2之原生SQL查询

ゝ一纸荒年。 2022-05-23 12:22 246阅读 0赞

Hibernate5和4对原生SQL语句的查询方法是不一样的，今天在网上找了一些实用的例子，给大家分享一下。

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

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

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

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

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

@Test
    public void multiCretiera(){
        String sql = "select * from orders where create_time between ? and ? and order_id like ?";
        Query<Order> 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<Order> list = query.getResultList();
        for(Order o : list){
             System.out.println(o.getOrderId() + "::" + o.getCreateTime());
        }
    }

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

@Test
    public void multiCretiera1(){
        String sql = "select * from orders where order_id like :orderId and create_time between :beginDate and :endDate";
        Query<Order> 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<Order> list = query.getResultList();
        for(Order o : list){
             System.out.println(o.getId() + "::" + o.getOrderId());
        }}

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

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

6，删除操作

@Test
    public void delete(){
        String sql = "delete from orders where id in (:idList)"; 
        Transaction tx = session.beginTransaction();
        List<Integer> list = new ArrayList<Integer>();
        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();
    }

7，获取某一列的值

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

8，获取关联对象的结果集

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

9，多列数据的查询

@Test
    public 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<Object[]> query = session.createNativeQuery(sql);
        List<Object[]> list = query.getResultList();
        for(Object[] o : list){
             System.out.println(o[0] + ";;" + o[1] + ";;" + o[2]);
        }
    }

10，函数查询

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

11，排序

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

12，右连接

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