Use criteria query and annotation in Hibernate-(Depttest.java)

Depttest.java

Test class:

To create a session first:

Private session session;
	Private Transaction TX;
	@Before public
	void Beforemethod () {
		
		session=new Configuration ()
					. Configure ().
					buildsessionfactory ()
					. Opensession ();
	}

Close session:

@After public
	void Aftermethod () {
		if (session!= null) {
			session.close ()}
	}

To start the test:

/**
* Test Simple query
*/

@Test public
	void TestMethod1 () {
		
		//query All Department
		Criteria Criteria=session.createcriteria (dept.class);
		List<dept> deptlist=criteria.list ();
		for (Dept dept:deptlist) {
			System.out.println ("department Number:" +dept.getdeptno ());
			SYSTEM.OUT.PRINTLN ("department Name:" +dept.getdname ());
			SYSTEM.OUT.PRINTLN ("Department Address:" +dept.getloc ());
		}
	

Department name of the department in Shanxi Luliang

Query department address in Shanxi Luliang department name
	@Test public
	void Test2 () {
		list<dept> Deptlist=session.createcriteria ( Dept.class)
							. Add (Restrictions.eq ("Loc", "Shanxi Luliang")). List ();
		for (Dept dept:deptlist) {
			System.out.println (Dept.getdeptno () + "," +dept.getdname ());
		}
	}

Check employees with a salary above 5000

@Test public
	void Test3 () {
		list<emp> emplist=session
								. Createcriteria (Emp.class)
								. Add ( RESTRICTIONS.GT ("Sal", 5000D)). List ();
		for (Emp emp:emplist) {
			System.out.println (emp.getename () + "," +emp.getsal ());
		}
	}

Query employees who are not part of any department

@Test public
	void Test4 () {
		list<emp> Emplist=session.createcriteria (Emp.class)
				                 . Add ( Restrictions.isnull ("dept")). List ();
		for (Emp emp:emplist) {
			System.out.println (Emp.getename ());
		}
	}

Query position is Clerk's employee
IgnoreCase: Case-insensitive

@Test public
	void Test5 () {
		list<emp> Emplist=session.createcriteria (Emp.class)
				                 . Add ( Restrictions.eq ("Job", "Clerk"). IgnoreCase ())
				                 . List ();
		for (Emp emp:emplist) {
			System.out.println (emp.getename () + "," +emp.getdept (). Getdname ());
		}
	

/**
* In: equals one of the values in the list
* Not in: is not equal to any of the values in the list
* Between value 1 and value 2: is greater than or equal to 1 and is less than the value 2
* Not between value 1 and value 2: less than value 1 or greater than value 2
*/
Query position is analyst or salesman employee

@Test public
	void Test6 () {
		List joblist=new ArrayList ();
		Joblist.add ("salesman");
		Joblist.add ("ANALYST");
		List<emp> Empolist=session.createcriteria (Emp.class)
				                   . Add (restrictions.in ("job", Joblist))
				                   . List () ;
		for (Emp emp:empolist) {
			System.out.println (emp.getename () + "," +emp.getjob ());
		}
	}

Enquiries about employees with a salary of 2000 yuan to 4000 yuan

@Test public
	void Test7 () {
		list<emp> Emplist=session.createcriteria (Emp.class)
				                 . Add ( Restrictions.between ("Sal", 2000D, 4000D))
				                 . List ();
		for (Emp emp:emplist) {
			System.out.println (emp.getename () + "," +emp.getsal ());
		}
	}

/**
* Like: string pattern matching
* Ilike: String wooden thing match, ignore case
*/
Query name includes s position (like)

@Test public
	void Test8 () {
		list<emp> Emplist=session.createcriteria (Emp.class)
				                  . Add ( Restrictions.like ("Job", "%s%"))
				                  . List ();
		for (Emp emp:emplist) {
			System.out.println (Emp.getename ());
		}
	}

Query name includes s position (ilike)

@Test public
		void Test9 () {
			list<emp> Emplist=session.createcriteria (Emp.class)
					                  . Add ( Restrictions.ilike ("Job", "%s%"))
					                  . List ();
			for (Emp emp:emplist) {
				System.out.println (Emp.getename ());
			}
		}

/**
* Matchmode Static constants
* 1.matchmode.strart: Employee name begins with S
* 2.matchmode.end: Employee name ends with s
* 3.matchmode.anywhere Employee Name contains S
* 4.matchmode.exact employee name equals s exact match
*/
Find jobs in positions including s

@Test public
		void Test10 () {
			list<emp> Emplist=session.createcriteria (Emp.class)
					                  . Add ( Restrictions.ilike ("Job", "%s%", Matchmode.anywhere))
					                  . List ();
			for (Emp emp:emplist) {
				System.out.println (emp.getjob ());
			}
		}

/**
* Logical operation
*1.and: Logic and
*2.or: Logical OR
*3.not: Logical Non-
*/
The job title is accountant or Analyert.

@Test public
		void test11 () {
			list<emp> Emplist=session.createcriteria (Emp.class)
					                  . Add ( Restrictions.or (Restrictions.eq ("Job", "Analyert"). IgnoreCase (),
					                       restrictions.eq ("Job", "Accountant"). IgnoreCase ()))
					                  . List ();
			for (Emp emp:emplist) {
				System.out.println (emp.getjob ());
			}
		}

The job title is accountant or Analyert or engineer.

@Test public
			void test12 () {
				list<emp> Emplist=session.createcriteria (Emp.class)
						                       . Add ( Restrictions.disjunction ()
						                	   . Add (Restrictions.eq ("Job", "Analyert"). IgnoreCase ())
						                       . Add (Restrictions.eq (" Job "," Accountant "). IgnoreCase ())
						                       . Add (Restrictions.eq (" Job "," engineer "). IgnoreCase ()).
						                       list ();
				for (Emp emp:emplist) {
					System.out.println (emp.getjob ());
				}
			}

/**
* Collection Operators
* 1. is empty: The collection is empty and contains no elements
* 2. is not empty: Collection not empty
*/
Query for departments without employees

@Test public
			void test13 () {
				list<dept> Deptlist=session.createcriteria (Dept.class)
						                 . Add ( Restrictions.isempty ("Emps"))
						                 . List ();
				for (Dept dept:deptlist) {
					System.out.println (Dept.getdname ());
				}
			}

/**
* Dynamic Query
* Condition See below: (Condition class: Empcondition)
* 1.job is engineer
* 2.sal Greater than 2000
* 3. The entry time is from December 31, 2006 to December 31, 2008
*/

@Test public
		void test14 () {
			SimpleDateFormat format=new simpledateformat ("Yyyy-mm-dd");
			Empcondition empcondition=new empcondition ();
			try {
				Empcondition.setjob ("engineer");
				Empcondition.setsal (2000D);
				String hiredatestart= "2006-12-31";
				String hiredateend= "2008-12-31";
				Date Hiredatestart1=format.parse (hiredatestart);
				Date Hiredateend1=format.parse (hiredateend);
				Empcondition.sethiredatestart (HIREDATESTART1);
				Empcondition.sethiredateend (HIREDATEEND1);
				
			} catch (ParseException e) {
				//TODO auto-generated catch block
				e.printstacktrace ();
			}

To add a condition for a dynamic query

criteria Criteria=session.createcriteria (Emp.class);
			if (Empcondition.getjob ()!=null) {Criteria.add (Restrictions.ilike ("Job", Empcondition.getjob ());
			} if (Empcondition.getsal ()!=null) {Criteria.add (restrictions.gt ("Sal", Empcondition.getsal ()); } if (Empcondition.gethiredatestart ()!=null) {Criteria.add (restrictions.ge ("HireDate"),
			Empcondition.gethiredatestart ())); } if (Empcondition.gethiredateend ()!=null) {Criteria.add (Restrictions.le ("HireDate", Empcondition.gethiredateend ()
			));
///Output             list<emp>emplist=criteria.list ();             for (Emp emp:emplist) {                 system.err.println (emp.getename () + "," +emp.getjob () + "," +emp.getsal () + "," +
Emp.gethiredate ());             }         } 

/**
* Paging Query
*/
Paging Query Department is all employees of salesman
and Payroll Descending
Show total number of pages, total records, current page number

 @Test public void test15 () {Criteria Criteria =session.createcriteria (Emp.class) . Add (Restrictions.eq ("Job", "salesman"). IgnoreCase ()). Setprojection (Projections.count ("Empno
			"));
			Obtain the total record number integer count= (integer) Criteria.uniqueresult ();
			paging int pagesize=2;
			int pageindex=1;
			int totalpage=count%pagesize==0?count/pagesize: (count/pagesize) +1;
					        Criteria=session.createcriteria (Emp.class). Add (Restrictions.eq ("Job", "salesman"). IgnoreCase ()
			. AddOrder (Order.desc ("Sal")); Starts paging, setting from which record to start checking list<emp> emplist=criteria.setfirstresult ((pageIndex-1) *pagesize). Setma
			
			Xresults (pageSize). List ();
			for (Emp emp:emplist) {System.out.println (Emp.getename () + "," +emp.getsal ());
			} System.out.println ("Total pages:" +totalpage);
			SYSTEM.ERR.PRINTLN ("Total number of records:" +count);
		
		System.out.println ("Current page number:" +pageindex); }

/**
* Connection Query
* Criteria inside only the internal connection and urgent to do outside links
*/
Check the names of the researchers in the department including S employees

@Test public
		void Test16 () {
			list<emp> Emplistlist=session.createcriteria (Emp.class)
					                     . Add ( Restrictions.ilike ("Ename", "s", Matchmode.anywhere))
					                     . Createcriteria ("dept")
					                     . Add (Restrictions.eq (" Dname "," IgnoreCase "). ().
					                     list ();
			for (Emp emp:emplistlist) {
				System.out.println (emp.getename () + "," +emp.getdept (). Getdname ());
			}
		
		Query the employee whose name contains s in the department
		@Test public
		void test17 () {
			list<emp> Emplist=session.createcriteria (Emp.class, "E")
					                 . CreateAlias ("dept", "D")
					                 . Add (Restrictions.ilike ("E.ename", "s", Matchmode.anywhere))
					                 . Add ( Restrictions.eq ("D.dname", "the"). IgnoreCase ())
					                 . List ();
			for (Emp emp:emplist) {
				System.out.println (emp.getename () + "," +emp.getdept (). Getdname ());
			}
		

Left OUTER JOIN
Query location in Shanxi Luliang Department and its number
Fetchmode.join Express urgent to do outside link query strategy

	@Test public
		void test18 () {
			list<dept> Deptlist=session.createcriteria (Dept.class, "D")
					                   . Setfetchmode ("Emps", Fetchmode.join)
					                   . Add (Restrictions.eq ("D.loc", "Shanxi Luliang"). IgnoreCase ().
					                   list ();
			for (Dept dept:deptlist) {
				System.out.println (dept.getdname () + "," +dept.getloc () + "," +dept.getemps (). Size ());
		}

Remove Duplicate data

@Test public
		void test19 () {
		list<dept> Deptlist=session.createcriteria (Dept.class, "D")
				                   . Setfetchmode ("Emps", Fetchmode.join)
				                   . Add (Restrictions.eq ("D.loc", "Shanxi Luliang"). IgnoreCase ().
				                   list ();
		Hashset<dept> set=new hashset<dept> (deptlist);
		for (Dept dept:set) {
			System.out.println (dept.getdname () + "," +dept.getloc () + "," +dept.getemps (). Size ());
		}
		
		    

/**
* Projection, grouping, and Detachedcriteria
*/
Query All department names "

@Test public
		void test20 () {
			list<string> List=session.createcriteria (dept.class)
					                  . setprojection (Property.forname ("Dname"))
					                  . List ();
			for (String str:list) {
				System.out.println (str);
			}
		}

Query for two or more two attributes
Check the employee's name and time of entry

@Test public
		void test21 () {
			list<object[]> List=session.createcriteria (emp.class)
					                   . Setprojection (
					                         projections.projectionlist ()
					                         . Add (Property.forname ("ename"))
					                         . Add ( Property.forname ("HireDate"))
					                		  . List ();
			
			For (object[] objects:list) {
				System.out.println (objects[0]+ ", +objects[1]);
			}
					                   
		

/**
* Group
* 1.groupProperty: Grouping
* 2.rowCount (): Statistic record number
* 3.avg: Average
* 4.max: Max value
* 5.min: Minimum value
* 6.count: Count the number of Non-null records in a field
* 7.sum: Sum for a field
*/
To count the average wage, maximum wage, and minimum wage of each department

@Test public
		void test22 () {
			list<object[]> List=session.createcriteria (Emp.class, "E")
					                   . CreateAlias ("E.dept", "D")
					                   . Setprojection (
					                		   projections.projectionlist ()
					                		   . Add ( Projections.groupproperty ("D.dname"))
					                		   . Add (Projections.avg ("E.sal"))
					                		   . Add (Projections.max ("E.sal"))
					                		   . Add (Projections.min ("E.sal"))
					                		   . List ();
			For (object[] objects:list) {
				System.out.println ("department name +objects[0]+", Maximum wage: "+objects[2]+" "+
						", average wage: "+ objects[1]+ ", minimum wage:" +objects[3]);
			}
					                   
		

Using Detachedcriteria
Inquiry department, the name contains "a" staff

@Test public
		void test23 () {
			Detachedcriteria detachedcriteria
			           =detachedcriteria.forclass (Emp.class, "E ")
					           . CreateAlias (" E.dept "," D ")
					           . Add (Restrictions.eq (" D.dname "," the "). IgnoreCase ())
					           . Add ( Restrictions.ilike ("E.ename", "a", Matchmode.anywhere));
			List<emp> List=detachedcriteria.getexecutablecriteria (session). List ();
			for (Emp emp:list) {
				System.out.println (emp.getename () + "," +emp.getdept (). Getdname ());
			}
					                        
		

Check for employees who pay more than the average wage

@Test public
		void Test24 () {
			//average wage
			Detachedcriteria Detachedcriteria=detachedcriteria
					             . Forclass ( Emp.class, "E")
					             . Setprojection (Property.forname ("Sal"). AVG ();
			Salary is greater than average wage
			list<emp> Emplist=session.createcriteria (Emp.class)
					                 . Add (Property.forname ("Sal"). GT ( Detachedcriteria)). List ();
			for (Emp emp:emplist) {
				System.out.println (emp.getename () + "," +emp.getsal ());
			}
			
		}