• hibernateHQL语句


    一、hql

    1. 什么是hql
    HQL是Hibernate Query Language的缩写

    查全部

    2. hql和sql区别/异同
    HQL SQL
    类名/属性 表名/列名
    区分大小写,关键字不区分大小写 不区分大小写
    别名 别名
    ?,从下标0开始计算位置(hibernate5之后不支持) ?,从顺序1开始计算位置
    :命名参数 不支持:命名参数
    面向对象的查询语言 面向结构查询语言

    hqlTest

    public class HqlTest {
    
        Session session=null;
        Transaction trans=null;
        BookDao bookDao=new BookDao();
        
    //    @Before
    //    public void setUp() {
    //        session=SessionFactoryUtils.openSession();
    //        trans=session.beginTransaction();
    //    }
    //    
    //    @After
    //    public void tearDown() {
    //        trans.commit();
    //        SessionFactoryUtils.closeSession();
    //    }
          
        @SuppressWarnings("deprecation")
        
        @Test
        public  void test() {
            String hql="";
            //一.HQL
            //1.HQL之查询返回List<T> 
    //         hql="from Book";
    //        Query<Book> createQuery = session.createQuery(hql, Book.class);
    //        List<Book> list = createQuery.list();
    //        for (Book book : list) {
    //            System.out.println(book);
    //        }
              
            //2.HQL之查询返回单个属性
    //        hql="select b.bookName from Book b";
    //        Query query = session.createQuery(hql);
    //        List list = query.list();
    //        for (Object object : list) {
    //            System.out.println(object);
    //        }
            //3.HQL之查询返回多个属性
    //        hql="select bookId,bookName from Book";
    //        Query createQuery = session.createQuery(hql);
    //        List list = createQuery.list();
    //        for (Object object : list) {
    //            //Object==object[]
    //            System.out.println(Arrays.toString((Object[])object));
    //        }
            //4.HQL之new Map
    //        hql="select new Map(b.bookId as bid, b.bookName as bname) from Book b";
    //        Query query = session.createQuery(hql);
    //        List<Map<String, Object>> list = query.list();
    //        for (Map<String, Object> map : list) {
    //            System.out.println(map);
    //        }
            
    //        5.HQL之new 构造方法
    //        hql="select new Book(b.bookId,b.bookName,b.price) from Book b ";
    //        Query createQuery = session.createQuery(hql);
    //        List list = createQuery.list();
    //        for (Object object : list) {
    //            System.out.println(object);
    //        }
    //        6.HQL之命名参数
            //hql="from Book where price>:min and price<:max";
    //        hql="from Book where bookId in (:ids)";
    //        Query<Book> query = session.createQuery(hql, Book.class);
    //        query.setParameter("min", 40f);
    //        query.setParameter("max", 1000f);
    //        query.setParameterList("ids",new Integer[] {1,2,3});
    //        List<Book> list = query.list();
    //        for (Book book : list) {
    //            System.out.println(book);
    //        }
            
            //7.HQL之聚合函数(count/sum/avg/min/max)
    //        hql="select sum(b.price) as total from Book b";
    //        Query createQuery = session.createQuery(hql);
    //        List list = createQuery.list();
    //        for (Object object : list) {
    //            System.out.println(object);
    //        }
    //        8.HQL之连接查询
    //        hql="select b.bookId,b.bookName,b.price"
    //                + ",c.categoryId,c.categoryName from Book b inner join "
    //                + "b.categroies c";
    //        Query query = session.createQuery(hql);
    //        List list = query.list();
    //        for (Object object : list) {
    //            System.out.println(Arrays.toString((Object[])object));
    //        }
            //        9.HQL之分页查询
    //        int page=1;
    //        int rows=2;
    //        
    //        hql="from Book";
    //        Query<Book> query = session.createQuery(hql, Book.class);
    //        query.setFirstResult((page-1)* rows);
    //        query.setMaxResults(rows);
    //        List<Book> list = query.list();
    //        for (Book book : list) {
    //            System.out.println(book);
    //        }
            
            //二.原生sql
            //1.原生sql查询返回Object[]
            String sql="";
    //         sql="select book_id,book_name,price from t_book_hb";
    //        NativeQuery query = session.createNativeQuery(sql);
    //        List list = query.list();
    //        for (Object object : list) {
    //            System.out.println(Arrays.toString((Object[])object));
    //        }
            
            //2.原生sql返回list<T>
    //        sql="select book_id,book_name,price from t_book_hb";
    //         NativeQuery<Book> query = session.createNativeQuery(sql, Book.class);
    //        List<Book> list = query.list();
    //        for (Book book : list) {
    //            System.out.println(book);
    //        }
            
            //3.原生sql查询返回单个属性
    //        sql="select book_name from t_book_hb";
    //        NativeQuery query = session.createNativeQuery(sql);
    //        List list = query.list();
    //        for (Object object : list) {
    //            System.out.println(object);
    //        }
            
            //4.原生sql查询返回List<Map>,适用于多表联查返回结果
            sql="SELECT *  FROM t_book_hb b,t_book_category_hb bc,t_category_hb c
    " + 
                    "WHERE b.`book_id`=bc.`bid` AND bc.`cid`=c.`category_id`";
            NativeQuery query = session.createNativeQuery(sql);
            
            
            //将结果集转换为Map集合
            query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
            List<Map<String,Object>> list= query.list();
            for (Map<String,Object> map : list) {
            System.out.println(map);
        }
            
            
            
        }
        

    BaseDao

        public Session getSession() {
            return SessionFactoryUtils.openSession();
        }
        
        /**
         * 设置Query的查询参数
         * @param params
         * @param query
         * @return
         */
        public void setParamters(Map<String,Object> params,Query query) {
            if(null==params||0==params.size())
                return;
            
            Set<Entry<String, Object>> set = params.entrySet();
            String name=null;
            Object value=null;
            for (Entry<String, Object> entry : set) {
                name=entry.getKey();
                value=entry.getValue();
                //判断参数是否是Collection,一般用于List/Set集合参数时使用
                if(value instanceof Collection)
                    query.setParameterList(name, (Collection)value);
                //判断参数是否是Object[]
                else if(value instanceof Object[]) 
                    query.setParameterList(name, (Object[])value);
                else 
                    query.setParameter(name, value);
            }
        }
        
        /**
         * 将普通hql语句转换成查询总记录数的hql语句
         * @param hql
         * @return
         */
        public String countSql(String hql) {
            //from Book
            //select * from Book
            int start=hql.toUpperCase().indexOf("FROM");
            return "select count(1) "+hql.substring(start);
        }
        
        /**
         * 查询(支持分页)
         * @param hql       普通hql语句
         * @param params    请求参数
         * @param pageBean  分页对象
         * @return
         */
        @SuppressWarnings("rawtypes")
        public List executeQuery(String hql,Map<String,Object> params,PageBean pageBean) {
            Session session=getSession();
            Query query=null;
            //1.根据满足条件查询总记录数
            if(null!=pageBean&&pageBean.isPagination()) {
                //select count(1) from Book where 
                String countHql=this.countSql(hql);
                query = session.createQuery(countHql);
                this.setParamters(params, query);
            }
            query=session.createQuery(hql);
            //2.根据满足条件查询分页记录
            if(null!=pageBean&&pageBean.isPagination()) {
                query.setFirstResult(pageBean.getStartIndex());
                query.setMaxResults(pageBean.getRows());
            }
            this.setParamters(params, query);
            return query.list();
        }

    BookDao

    public class BookDao extends BaseDao{
        
        public void addBook(Book book) {
            Session session = SessionFactoryUtils.openSession();
            Transaction transaction = session.beginTransaction();
            //保存
            session.save(book);
            transaction.commit();
            SessionFactoryUtils.closeSession();
        }
        
        public Book get(Book book) {
            Session session = SessionFactoryUtils.openSession();
            Transaction transaction = session.beginTransaction();
            //保存
            Book b = session.get(Book.class, book.getBookId());
            if(b!=null) {
                Hibernate.initialize(b.getCategroies());
            }
            transaction.commit();
            SessionFactoryUtils.closeSession();
            return b;
        }
        
        //被控方
        public void delBook(Book book) {
            Session session = SessionFactoryUtils.openSession();
            Transaction transaction = session.beginTransaction();
            Book b = session.get(Book.class, book.getBookId());
            if(null!=b) {
                //解除关联关系
                Set<Category> categroies = b.getCategroies();
                for (Category category : categroies) {
                    b.getCategroies().remove(category);
                }
                session.delete(b);
            }
            
            transaction.commit();
            SessionFactoryUtils.closeSession();
            
        }
        
        
    /***************************通用查询***************************/
        
        
        public List<Book> list1(Book book,PageBean pageBean) {
            Session session = SessionFactoryUtils.openSession();
            Transaction transaction = session.beginTransaction();
            
            //下面代码处理的是book实体类的条件查询
            String bookName= book.getBookName();
            
            //sql语句where后面可以直接写true,而hql不能出现true
            String hql= "from Book where 1=1";
            if(StringUtils.isNotBlank(bookName)) {
                hql +=" and bookName like :bookName";
            }
            Query query=session.createQuery(hql);
            if(StringUtils.isNotBlank(bookName)) {
                query.setParameter("bookName",bookName);
            }
            
            //处理的是分页
            if(pageBean !=null && pageBean.isPagination()) {
                 query.setFirstResult(pageBean.getStartIndex());
                 query.setMaxResults(pageBean.getRows());
            }
             List list = query.list();
             
            
            transaction.commit();
            session.close();
            return list;
        
        
        
    }
    
        public List<Book> list2(Book book, PageBean pageBean) {
            Session session = SessionFactoryUtils.openSession();
            Transaction transaction = session.beginTransaction();
            
    //        下面的代码处理的是book实体类的条件查询
            String bookName = book.getBookName();
            Map<String , Object> map = new HashMap<String, Object>();
    //        sql语句where后面可以直接写true,而hql语句不能出现true
            String hql = "from Book where 1=1";
            if(StringUtils.isNotBlank(bookName)) {
                hql += " and bookName like :bookName";
                map.put("bookName", bookName);
            }
            List list = super.executeQuery(hql, map, pageBean);
            
            transaction.commit();
            session.close();
            return list;
        }
    }

    测试

    @Test
        public void testList1() {
            Book book = new Book();
    //        book.setBookName("T226");
            PageBean pageBean = new PageBean();
            pageBean.setPage(2);
            List<Book> list1 = this.bookDao.list1(book, pageBean);
            for (Book b : list1) {
                System.out.println(b);
            }
        }

     

    @Test
        public void testList2() {
            Book book = new Book();
            book.setBookName("%西游记%");
            PageBean pageBean = new PageBean();
    //        pageBean.setPage(2);
            List<Book> list1 = bookDao.list2(book, pageBean);
            for (Book b : list1) {
                System.out.println(b);
            }
        }

  • 相关阅读:
    hdu1049Climbing Worm
    hdu1290献给杭电五十周年校庆的礼物
    hduNumber Sequence
    VS2012创建项目模板创建vsix扩展组件
    那些热血沸腾的电子竞技背景音乐
    学习总结
    学习总结第六周
    第二次课堂测试1
    学习总结第五周
    一个简单的学生成绩管理系统(用JAVA实现)
  • 原文地址:https://www.cnblogs.com/xmf3628/p/11319343.html
Copyright © 2020-2023  润新知