criteriaquerytest.java

介绍了hibernate的入门有一些基本常用的事例

			.uniqueResult();
		Object[] result = (Object[])object; 
		
		assertEquals(new Integer(2),result[0]);
		assertEquals(new Long(667),result[1]);
		assertEquals(new Long(101),result[2]);
		assertEquals( 384.0, ( (Double) result[3] ).doubleValue(), 0.01 );
		
		
		List resultWithMaps = s.createCriteria(Enrolment.class)
			.setProjection( Projections.projectionList()
					.add( Projections.property("studentNumber"), "stNumber" )
					.add( Projections.property("courseCode"), "cCode" )
			)
		    .add( Expression.gt( "studentNumber", new Long(665) ) )
		    .add( Expression.lt( "studentNumber", new Long(668) ) )
		    .addOrder( Order.asc("stNumber") )
			.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP)
			.list();
		
		assertEquals(1, resultWithMaps.size());
		Map m1 = (Map) resultWithMaps.get(0);
		
		assertEquals(new Long(667), m1.get("stNumber"));
		assertEquals(course.getCourseCode(), m1.get("cCode"));		

		resultWithMaps = s.createCriteria(Enrolment.class)
			.setProjection( Projections.property("studentNumber").as("stNumber") )
		    .addOrder( Order.desc("stNumber") )
			.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP)
			.list();
		
		assertEquals(2, resultWithMaps.size());
		Map m0 = (Map) resultWithMaps.get(0);
		m1 = (Map) resultWithMaps.get(1);
		
		assertEquals(new Long(101), m1.get("stNumber"));
		assertEquals(new Long(667), m0.get("stNumber"));

	
		List resultWithAliasedBean = s.createCriteria(Enrolment.class)
			.createAlias("student", "st")
			.createAlias("course", "co")
			.setProjection( Projections.projectionList()
					.add( Projections.property("st.name"), "studentName" )
					.add( Projections.property("co.description"), "courseDescription" )
			)
			.addOrder( Order.desc("studentName") )
			.setResultTransformer( new AliasToBeanResultTransformer(StudentDTO.class) )
			.list();
		
		assertEquals(2, resultWithAliasedBean.size());
		
		StudentDTO dto = (StudentDTO) resultWithAliasedBean.get(0);
		assertNotNull(dto.getDescription());
		assertNotNull(dto.getName());
	
		s.createCriteria(Student.class)
			.add( Restrictions.like("name", "Gavin", MatchMode.START) )
			.addOrder( Order.asc("name") )
			.createCriteria("enrolments", "e")
				.addOrder( Order.desc("year") )
				.addOrder( Order.desc("semester") )
			.createCriteria("course","c")
				.addOrder( Order.asc("description") )
				.setProjection( Projections.projectionList()
					.add( Projections.property("this.name") )
					.add( Projections.property("e.year") )
					.add( Projections.property("e.semester") )
					.add( Projections.property("c.courseCode") )
					.add( Projections.property("c.description") )
				)
			.uniqueResult();
			
		Projection p1 = Projections.projectionList()
			.add( Projections.count("studentNumber") )
			.add( Projections.max("studentNumber") )
			.add( Projections.rowCount() );
		
		Projection p2 = Projections.projectionList()
			.add( Projections.min("studentNumber") )
			.add( Projections.avg("studentNumber") )
			.add( Projections.sqlProjection(
					"1 as constOne, count(*) as countStar", 
					new String[] { "constOne", "countStar" }, 
					new Type[] { Hibernate.INTEGER, Hibernate.INTEGER }
			) );
	
		Object[] array = (Object[]) s.createCriteria(Enrolment.class)
			.setProjection( Projections.projectionList().add(p1).add(p2) )
			.uniqueResult();
		
		assertEquals( array.length, 7 );
		
		List list = s.createCriteria(Enrolment.class)
			.createAlias("student", "st")
			.createAlias("course", "co")
			.setProjection( Projections.projectionList()
					.add( Projections.groupProperty("co.courseCode") )
					.add( Projections.count("st.studentNumber").setDistinct() )
					.add( Projections.groupProperty("year") )
			)
			.list();
		
		assertEquals( list.size(), 2 );
		
		Object g = s.createCriteria(Student.class)
			.add( Restrictions.idEq( new Long(667) ) )
			.setFetchMode("enrolments", FetchMode.JOIN)
			//.setFetchMode("enrolments.course", FetchMode.JOIN) //TODO: would love to make that work...
			.uniqueResult();
		assertSame(g, gavin);
		
		s.delete(gavin);
		s.delete(xam);
		s.delete(course);
		
		t.commit();
		s.close();
	}
		
	public void testProjectionsUsingProperty() {
		Session s = openSession();
		Transaction t = s.beginTransaction();
		
		Course course = new Course();
		course.setCourseCode("HIB");
		course.setDescription("Hibernate Training");
		s.save(course);
		
		Student gavin = new Student();
		gavin.setName("Gavin King");
		gavin.setStudentNumber(667);
		s.save(gavin);
		
		Student xam = new Student();
		xam.setName("Max Rydahl Andersen");
		xam.setStudentNumber(101);
		s.save(xam);
		
		Enrolment enrolment = new Enrolment();
		enrolment.setCourse(course);
		enrolment.setCourseCode(course.getCourseCode());
		enrolment.setSemester((short) 1);
		enrolment.setYear((short) 1999);
		enrolment.setStudent(xam);
		enrolment.setStudentNumber(xam.getStudentNumber());
		xam.getEnrolments().add(enrolment);
		s.save(enrolment);
		
		enrolment = new Enrolment();
		enrolment.setCourse(course);
		enrolment.setCourseCode(course.getCourseCode());
		enrolment.setSemester((short) 3);
		enrolment.setYear((short) 1998);
		enrolment.setStudent(gavin);
		enrolment.setStudentNumber(gavin.getStudentNumber());
		gavin.getEnrolments().add(enrolment);
		s.save(enrolment);
		
		s.flush();
		
		Integer count = (Integer) s.createCriteria(Enrolment.class)
			.setProjection( Property.forName("studentNumber").count().setDistinct() )
			.uniqueResult();
		assertEquals(count, new Integer(2));
		
		Object object = s.createCriteria(Enrolment.class)
			.setProjection( Projections.projectionList()
					.add( Property.forName("studentNumber").count() )
					.add( Property.forName("studentNumber").max() )
					.add( Property.forName("studentNumber").min() )
					.add( Property.forName("studentNumber").avg() )
			)
			.uniqueResult();
		Object[] result = (Object[])object; 
		
		assertEquals(new Integer(2),result[0]);
		assertEquals(new Long(667),result[1]);
		assertEquals(new Long(101),result[2]);
		assertEquals(384.0, ( (Double) result[3] ).doubleValue(), 0.01);
		
		
		s.createCriteria(Enrolment.class)
		    .add( Property.forName("studentNumber").gt( new Long(665) ) )
		    .add( Property.forName("studentNumber").lt( new Long(668) ) )
		    .add( Property.forName("courseCode").like("HIB", MatchMode.START) )
		    .add( Property.forName("year").eq( new Short( (short) 1999 ) ) )
		    .addOrder( Property.forName("studentNumber").asc() )
			.uniqueResult();
	
		List resultWithMaps = s.createCriteria(Enrolment.class)
			.setProjection( Projections.projectionList()
					.add( Property.forName("studentNumber").as("stNumber") )
					.add( Property.forName("courseCode").as("cCode") )
			)
		    .add( Property.forName("studentNumber").gt( new Long(665) ) )
		    .add( Property.forName("studentNumber").lt( new Long(668) ) )
		    .addOrder( Property.forName("studentNumber").asc() )
			.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP)
			.list();
		
		assertEquals(1, resultWithMaps.size());
		Map m1 = (Map) resultWithMaps.get(0);
		
		assertEquals(new Long(667), m1.get("stNumber"));
		assertEquals(course.getCourseCode(), m1.get("cCode"));		

		resultWithMaps = s.createCriteria(Enrolment.class)
			.setProjection( Property.forName("studentNumber").as("stNumber") )
		    .addOrder( Order.desc("stNumber") )
			.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP)
			.list();
		
		assertEquals(2, resultWithMaps.size());
		Map m0 = (Map) resultWithMaps.get(0);
		m1 = (Map) resultWithMaps.get(1);
		
		assertEquals(new Long(101), m1.get("stNumber"));
		assertEquals(new Long(667), m0.get("stNumber"));

	
		List resultWithAliasedBean = s.createCriteria(Enrolment.class)
			.createAlias("student", "st")
			.createAlias("course", "co")
			.setProjection( Projections.projectionList()
					.add( Property.forName("st.name").as("studentName") )
					.add( Property.forName("co.description").as("courseDescription") )
			)
			.addOrder( Order.desc("studentName") )
			.setResultTransformer( new AliasToBeanResultTransformer(StudentDTO.class) )
			.list();
		
		assertEquals(2, resultWithAliasedBean.size());
		
		StudentDTO dto = (StudentDTO) resultWithAliasedBean.get(0);
		assertNotNull(dto.getDescription());
		assertNotNull(dto.getName());
	
		s.createCriteria(Student.class)
			.add( Restrictions.like("name", "Gavin", MatchMode.START) )
			.addOrder( Order.asc("name") )
			.createCriteria("enrolments", "e")
				.addOrder( Order.desc("year") )
				.addOrder( Order.desc("semester") )
			.createCriteria("course","c")
				.addOrder( Order.asc("description") )
				.setProjection( Projections.projectionList()
					.add( Property.forName("this.name") )
					.add( Property.forName("e.year") )
					.add( Property.forName("e.semester") )
					.add( Property.forName("c.courseCode") )
					.add( Property.forName("c.description") )
				)
			.uniqueResult();
			
		Projection p1 = Projections.projectionList()
			.add( Property.forName("studentNumber").count() )
			.add( Property.forName("studentNumber").max() )
			.add( Projections.rowCount() );
		
		Projection p2 = Projections.projectionList()
			.add( Property.forName("studentNumber").min() )
			.add( Property.forName("studentNumber").avg() )
			.add( Projections.sqlProjection(
					"1 as constOne, count(*) as countStar", 
					new String[] { "constOne", "countStar" }, 
					new Type[] { Hibernate.INTEGER, Hibernate.INTEGER }
			) );
	
		Object[] array = (Object[]) s.createCriteria(Enrolment.class)
			.setProjection( Projections.projectionList().add(p1).add(p2) )
			.uniqueResult();
		
		assertEquals( array.length, 7 );
		
		List list = s.createCriteria(Enrolment.class)
			.createAlias("student", "st")
			.createAlias("course", "co")
			.setProjection( Projections.projectionList()
					.add( Property.forName("co.courseCode").group() )
					.add( Property.forName("st.studentNumber").count().setDistinct() )
					.add( Property.forName("year").group() )
			)
			.list();
		
		assertEquals( list.size(), 2 );
		
		s.delete(gavin);
		s.delete(xam);
		s.delete(course);
		
		t.commit();
		s.close();
	}
		
	protected String[] getMappings() {
		return new String[] { "criteria/Enrolment.hbm.xml" };
	}

	public static Test suite() {
		return new TestSuite(CriteriaQueryTest.class);
	}

}