Hibernate SQL

1.     SQL

针对于数据库表，表名不区分大小写

1.1.查询

@Test        public void getAll() {               Session session = HibernateUtils.getSession();               // 使用SQL:sql语句。               List<Stu> list = session.createSQLQuery("select * from Stu order by id").addEntity(Stu.class).list();               for (Stu stu : list) {                      System.out.println(stu);               }               session.close();        }        @Test        public void getIdAndName() {               Session session = HibernateUtils.getSession();               // 使用SQL:sql语句。 createSQLQuery() 返回一个List<Objeect[]> 数组，然后将数组中的内容转换成学生对象。               List<Object[]> list = session.createSQLQuery("select id,name from stu order by id").list();               Stu stu =null;               for (int i = 0; i < list.size(); i++) {                      Object[] obj = list.get(i);                      stu = new Stu();                      //     将集合中的数组取出来放到obj数组中。                      stu.setId(Integer.parseInt(obj[0].toString()));                      stu.setName(obj[1].toString());                      System.out.println(stu);               }               session.close();        }

 

1.2.新增

/**         *         * @Description:  说白了executeUpdate().jdbc......         * @author mengqx         * @date   2017年8月31日         */        @Test        public void add() {               Session session = HibernateUtils.getSession();               Transaction tr = session.beginTransaction();               // 使用SQL:sql语句。               Query query = session.createSQLQuery("insert into stu values (?,?,?)").setParameter(0, 111).setParameter(1, "王二小").setParameter(2, "asdfghjkl;'");               int result = 0;               try {                      result = query.executeUpdate();                      tr.commit();               } catch (Exception e) {                      e.printStackTrace();                      tr.rollback();               }finally{                      session.close();               }               System.out.println(result);        }

 

1.3.修改

@Test       public void update() {            Session session = HibernateUtils.getSession();            Transaction tr = session.beginTransaction();            // 使用SQL:sql语句。            Query query = session.createSQLQuery("update stu set name = ? where id = ?").setParameter(0, "胡八一").setParameter(1, 111);            int result = 0;            try {                  result = query.executeUpdate();                  tr.commit();            } catch (Exception e) {                  e.printStackTrace();                  tr.rollback();            }finally{                  session.close();            }            System.out.println(result);       }

 

1.4.删除

@Test        public void delete() {               Session session = HibernateUtils.getSession();               Transaction tr = session.beginTransaction();               // 使用SQL:sql语句。               Query query = session.createSQLQuery("delete from stu where id = ?").setParameter(0, 111);               int result = 0;               try {                      result = query.executeUpdate();                      tr.commit();               } catch (Exception e) {                      e.printStackTrace();                      tr.rollback();               }finally{                      session.close();               }               System.out.println(result);        }

 

1.5.模糊查询

@Test        public void like() {               Session session = HibernateUtils.getSession();               Transaction tr = session.beginTransaction();               // 使用SQL:sql语句。               List<Stu> list = session.createSQLQuery("select * from stu where name like '%' || ? || '%' order by id ").addEntity(Stu.class).setParameter(0, "li").list();               for (Stu stu : list) {                      System.out.println(stu);               }               session.close();        }

 

1.6.分页查询

@Test        public void page() {               Session session = HibernateUtils.getSession();               int pageIndex = 2; // 分页语句从第几条开始查：(pageIndex-1)*pageSize               int pageSize = 3;               List<Stu> list = session.createSQLQuery("select * from stu order by id").addEntity(Stu.class).setFirstResult((pageIndex - 1) * pageSize)                             .setMaxResults(pageSize).list();               for (Stu stu : list) {                      System.out.println(stu);               }               session.close();        }

 

1.7.聚合函数的使用

@Test        public void count() {               Session session = HibernateUtils.getSession();               // 聚合函数查询要使用uniqueResult();该方法返回一个Object。不能强制类型转换               Object object = session.createSQLQuery("select count(*) from stu").uniqueResult();               System.out.println(object);               session.close();        }