hibernate与mysql之间的查询总结

hibernate与mysql之间的查询

  //简单的查询;@Testpublic void testHQL_01() {Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("from Category");List<Category> categories = (List<Category>)q.list();for(Category c : categories) {System.out.println(c.getName());}session.getTransaction().commit();session.close();}//条件查询;@Testpublic void testHQL_02() {Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("from Category c where c.name > 'c5'");List<Category> categories = (List<Category>)q.list();for(Category c : categories) {System.out.println(c.getName());}session.getTransaction().commit();session.close();}//排序查询;@Testpublic void testHQL_03() {Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("from Category c order by c.name desc");List<Category> categories = (List<Category>)q.list();for(Category c : categories) {System.out.println(c.getName());}session.getTransaction().commit();session.close();}//去除重复查询;@Testpublic void testHQL_04() {Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("select distinct c from Category c order by c.name desc");List<Category> categories = (List<Category>)q.list();for(Category c : categories) {System.out.println(c.getName());}session.getTransaction().commit();session.close();}//找出位于最小值和最大值之间的查询;@Testpublic void testHQL_05() {Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("from Category c where c.id > :min and c.id < :max");//第一种占位符的设置;/*q.setParameter("min", 2);q.setParameter("max", 8);*/   //第二种占位符的设置q.setInteger("min", 2);q.setInteger("max", 8);/*Query q = session.createQuery("from Category c where c.id > :min and c.id < :max").setInteger("min", 2).setInteger("max", 8);*/List<Category> categories = (List<Category>)q.list();for(Category c : categories) {System.out.println(c.getId() + "-" + c.getName());}session.getTransaction().commit();session.close();}//占位符的设置@Testpublic void testHQL_06() {Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("from Category c where c.id > ? and c.id < ?");q.setParameter(0, 2).setParameter(1, 8);//q.setParameter(1, 8);List<Category> categories = (List<Category>)q.list();for(Category c : categories) {System.out.println(c.getId() + "-" + c.getName());}session.getTransaction().commit();session.close();}//分页@Testpublic void testHQL_07() {Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("from Category c order by c.name desc");//每页显示多少行q.setMaxResults(5);//每页从第几行开始q.setFirstResult(4);List<Category> categories = (List<Category>)q.list();for(Category c : categories) {System.out.println(c.getId() + "-" + c.getName());}session.getTransaction().commit();session.close();}//查询字段放入集合数组@Testpublic void testHQL_08() {Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("select c.id,  c.name from Category c order by c.name desc");List<Object[]> categories = (List<Object[]>)q.list();    //q.list()返回的是以List集合的数组形式,所以遍历List集合的数组中，在数组中o[0]的位置是c.id,o[1]的位置是c.name;for(Object[] o : categories) {System.out.println(o[0] + "-" + o[1]);}session.getTransaction().commit();session.close();}//设定fetch type 为lazy后将不会有第二条sql语句@Testpublic void testHQL_09() {Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("from Topic t where t.category.id = 1");List<Topic> topics = (List<Topic>)q.list();for(Topic t : topics) {System.out.println(t.getTitle());//System.out.println(t.getCategory().getName());}session.getTransaction().commit();session.close();}   //left right join的连接查询//为什么不能直接写Category名，而必须写t.category//因为有可能存在多个成员变量（同一个类），需要指明用哪一个成员变量的连接条件来做连接@Testpublic void testHQL_10() {Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("select t.title, c.name from Topic t join t.category c "); //join Category cfor(Object o : q.list()) {Object[] m = (Object[])o;System.out.println(m[0] + "-" + m[1]);}session.getTransaction().commit();session.close();}//使用uniqueResult@Testpublic void testHQL_11() {Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("from Msg m where m = :MsgToSearch "); Msg m = new Msg();m.setId(1);q.setParameter("MsgToSearch", m);Msg mResult = (Msg)q.uniqueResult();System.out.println(mResult.getCont());session.getTransaction().commit();session.close();}//统计查询@Testpublic void testHQL_12() {Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("select count(*) from Msg m");long count = (Long)q.uniqueResult();System.out.println(count);session.getTransaction().commit();session.close();}//函数查询@Testpublic void testHQL_13() {Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("select max(m.id), min(m.id), avg(m.id), sum(m.id) from Msg m");Object[] o = (Object[])q.uniqueResult();System.out.println(o[0] + "-" + o[1] + "-" + o[2] + "-" + o[3]);session.getTransaction().commit();session.close();}    //between and的查询@Testpublic void testHQL_14() {Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("from Msg m where m.id between 3 and 5");for(Object o : q.list()) {Msg m = (Msg)o;System.out.println(m.getId() + "-" + m.getCont());}session.getTransaction().commit();session.close();}//in的查询@Testpublic void testHQL_15() {Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("from Msg m where m.id in (3,4, 5)");for(Object o : q.list()) {Msg m = (Msg)o;System.out.println(m.getId() + "-" + m.getCont());}session.getTransaction().commit();session.close();}//is null 与 is not null@Testpublic void testHQL_16() {Session session = sf.openSession();session.beginTransaction();Query q = session.createQuery("from Msg m where m.cont is not null");for(Object o : q.list()) {Msg m = (Msg)o;System.out.println(m.getId() + "-" + m.getCont());}session.getTransaction().commit();session.close();}