Query for HQL statements (Hibernate query Language)
The difference between HQL and SQL statements
SQL: Generic query in a language-relational database
, Structured Query Language, view table and table columns
HQL is a unique query language for hibernate, which is
The query statement for the object, which is the class and the properties in the class
Note: The HQL statement cannot execute the INSERT statement, and the final
The HQL statement that you write translates to the appropriate SQL statement
, and Hibernate's Get/load method can only be used by the primary key
To query
From User u where u.name= ' Zhangsan ' and u.age>20
Update User u set u.name= ' briup ' where u.id=10;
Delete from User u where u.name= ' Briup '
Session.delete
Criteria
Function: Specifies the data in the table that is queried for a class
Object acquisition: Session can be created
Criterion
Function: Represents the condition used when querying, each condition is a
Criterion objects of type eg:id>5, salary=1000
Object acquisition: Created with restrictions
Restrictions
Purpose: To create an object that represents a query condition.
That is, an object of type criterion.
Use a static method inside this class to create a representation
The object of the condition
Advanced Query method
Criteria (interface)
1. Concept
Criterion (interface) is the criteria query.
The criteria provides
Add (Criterion Criterion) method for adding a query condition
2. Create
Examples of criterion can be done by restrictions tool classes to
Created, restrictions provides a number of static methods, such as
Creation of methods such as EQ (equals), GE (greater than equals), between, etc.
Criterion Query criteria
Restrictions.eq =
restrictions.gt >
Restrictions.ge >=
restrictions.lt <
Restrictions.le <=
Restrictions.between between
Restrictions.like like
Restrictions.in in
Restrictions.and and
Restrictions.or or
Restrictions.isnull to determine if NULL, NULL returns TRUE
Restrictions.isnotnull is the opposite of IsNull.
Test Example:
Entity class One:
Package Com.briup.criteria.bean;
public class Husband {
Private long ID;
private String name;
private int age;
private double salary;
Private Wife Wife;
public int getage () {
return age;
}
public void Setage (int.) {
This.age = age;
}
Public double getsalary () {
return salary;
}
public void Setsalary (double salary) {
This.salary = salary;
}
Public long getId () {
return ID;
}
public void SetId (long id) {
This.id = ID;
}
Public String GetName () {
return name;
}
public void SetName (String name) {
THIS.name = name;
}
Public Wife Getwife () {
return wife;
}
public void Setwife (Wife Wife) {
This.wife = wife;
}
}
Entity Class Two:
Package Com.briup.criteria.bean;
public class Wife {
Private long ID;
private String name;
Private Husband Husband;
Public long getId () {
return ID;
}
public void SetId (long id) {
This.id = ID;
}
Public String GetName () {
return name;
}
public void SetName (String name) {
THIS.name = name;
}
Public Husband Gethusband () {
return husband;
}
public void Sethusband (Husband Husband) {
This.husband = husband;
}
}
Configuration file One:
<! DOCTYPE hibernate-mapping Public
"-//hibernate/hibernate Mapping DTD 3.0//en"
"Http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<class name= "Com.briup.criteria.bean.Husband" table= "S_husband" >
<id name= "id" >
<generator class= "Increment"/>
</id>
<property name= "Name"/>
<property name= "Age"/>
<property name= "Salary"/>
<many-to-one name= "wife" class= "Com.briup.criteria.bean.Wife" column= "wife_id" unique= "true" cascade= "all" > </many-to-one>
</class>
Configuration file Two:
<! DOCTYPE hibernate-mapping Public
"-//hibernate/hibernate Mapping DTD 3.0//en"
"Http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<class name= "Com.briup.criteria.bean.Wife" table= "S_wife" >
<id name= "id" >
<generator class= "Increment"/>
</id>
<property name= "Name"/>
<one-to-one name= "husband" class= "Com.briup.criteria.bean.Husband" cascade= "All" fetch= "select" ></ One-to-one>
</class>
Test class:
Package com.briup.criteria.test;
Import Org.hibernate.Criteria;
Import Org.hibernate.Query;
Import org.hibernate.Session;
Import Org.hibernate.SessionFactory;
Import org.hibernate.Transaction;
Import org.hibernate.cfg.Configuration;
Import org.hibernate.criterion.Restrictions;
Import Org.hibernate.tool.hbm2ddl.SchemaExport;
Import Org.junit.After;
Import Org.junit.Before;
Import Org.junit.Test;
Import Com.briup.criteria.bean.Husband;
Import Com.briup.criteria.bean.Wife;
/**
* Advanced Query method Criteria (interface) Test example
* 1. General enquiry;
* 2. Add a conditional query [Where\and\or\order by\between and\ > < = Null\not null\in\like
* 3. Multi-object query and sorting
* 4.HQL Statement Paging
* @author Victor
*
*/
public class Criteria_test {
Private Sessionfactory sessionfactory;
Private session session;
Private Transaction Transaction;
Private configuration Configuration=new configuration ();
@Before
public void before () {
Configuration.configure ();
Sessionfactory=configuration.buildsessionfactory ();
Session=sessionfactory.opensession ();
Transaction=session.begintransaction ();
}
@After
public void after () {
Transaction.commit ();
Session.close ();
}
@Test
public void autocreatetable () {
Schemaexport schemaexport=new schemaexport (configuration);
Schemaexport.create (True, true);
}
@Test
public void Save () {
for (int i=0;i<=8;i++) {
Husband hus=new Husband ();
Hus.setname ("Briup" +i);
Hus.setage (20+i);
Hus.setsalary (2000+ (i*300));
Wife wife=new Wife ();
Wife.setname ("Lili" +i);
Hus.setwife (wife);
Wife.sethusband (HUS);
Session.save (wife);
}
}
@Test
public void Singlesave () {
Husband husband=new Husband ();
Husband.setname ("Quinn");
Husband.setage (35);
Husband.setsalary (3500);
Wife wife=new Wife ();
Wife.setname ("Man Zi");
Husband.setwife (wife);
Wife.sethusband (husband);
Session.save (husband);
Session.save (wife);
}
@Test//Query all data
public void Criteriaselecthus () {
The role of the session:
1 perform additions and deletions and change the operation
2 providing caching capabilities
3 Creating an implementation class for an advanced query interface
Criteria is Interface Eg:select * from S_husband
Criteria Criteria=session.createcriteria (Husband.class);
All results can be queried at this time without any qualification.
/*listfor (Husband h:list) {
Test whether it is lazy loading
System.out.println (H.getwife (). GetName ());
System.out.println (H.getid () + "--" +h.getname () + "--" +h.getage () + "--" +h.getsalary ());
}*/
}
@Test
public void criteraselectwif () {
Criteria Criteria=session.createcriteria (Wife.class);
/*list<wife> list=criteria.list ();
for (Wife w:list) {
System.out.println (W.getid () + ";" +w.getname ());
}*/
}
@Test//Add query criteria
public void Criteriaselect () {
Criteria Criteria=session.createcriteria (Husband.class);
/***
* Criteria interface Criterion Interface Restrictions class
* criterion is the criteria query-------where
* Criterion is also an interface, we want to use this interface implementation class object to represent the query condition
* Add in criteria (query criteria)
* Criteria provides the Add (Criterion Criterion) method for adding query criteria
* The Restrictions tool class can create objects of the criterion interface type, that is, create a query condition object
* There are many static methods in the restrictions class, and the return value of these methods is that we want to query the conditional object
*
* RESTRICTIONS.GT ("id", 2L) The return value of the method is the implementation class object of the Criterion interface
* SELECT * from S_husband where id>2;
* Criteria.add (RESTRICTIONS.GT ("id", 2L));
*
*
* SELECT * from S_husband where salary<=4000;
* Criteria.add (Restrictions.le ("salary", 4000d));
*
* SELECT * from S_husband where name= ' briup1 ';
* Criteria.add (Restrictions.eq ("name", "Briup1"));
*
* SELECT * from S_husband where name is like ' briu% ';
* Criteria.add (Restrictions.like ("name", "briu%"));
*
* SELECT * from S_husband where ID in (1,2,3,4);
* Criteria.add (restrictions.in ("id", new long[]{1l,2l,3l,4l});
*
* SELECT * from S_husband where salary between 4000 and 6000;
* Criteria.add (Restrictions.between ("salary", 4000d,6000d));
* Note: You can also see a value between two dates here.
*
* SELECT * from S_husband where salary is null;
* Criteria.add (Restrictions.isnull ("salary"));
*
*
*/
The table fields of the class and class are different, and the test uses the field in the table or the field in the class.
Criteria.add (RESTRICTIONS.GT ("id", 2L))
. Add (Restrictions.le ("salary", 4000d));
Criteria.add (Restrictions.isnotnull ("salary"));
All results can be queried at this time without any qualification.
/*listfor (Husband h:list) {
Test whether it is lazy loading
System.out.println (H.getwife (). GetName ());
System.out.println (H.getid () + "--" +h.getname () + "--" +h.getage () + "--" +h.getsalary ());
}*/
}
@Test
public void Select () {
Criteria Criteria=session.createcriteria (Husband.class);
. Add (Restrictions.le ("salary", 3800D))
. Add (RESTRICTIONS.GT ("id", 2L));;
. Add (Restrictions.le ("salary", 4000d));
. Add (Restrictions.eq ("name", "Zhou Zhiwei"));
Criteria.add (Restrictions.like ("name", "briu%"));
Criteria.add (Restrictions.isnotnull ("salary"));
Criteria.add (restrictions.in ("id", new long[]{1l,2l,3l});
Criteria.add (Restrictions.between ("salary", 4000d,6000d));
/*listfor (Husband h:list) {
System.out.println (H.getid () + ";" +h.getname () + ";" +h.getage () + ";" +h.getsalary ());
}*/
}
@Test//Add query statements in a row
public void Criteia3 () {
Criteria Criteria=session.createcriteria (Husband.class);
Add conditions in a row, by default adding link conditions by and
SELECT * from S_husband where id>3 and id<7;
Criteria.add (RESTRICTIONS.GT ("id", 2L));
Criteria.add (restrictions.lt ("id", 7L));
The above notation is equivalent to the following notation
The following wording can bring us benefits, chained programming, eg:string in the Replace method. We can always replace
Criteria.add (RESTRICTIONS.GT ("id", 2L))
. Add (restrictions.lt ("id", 7L));
/*listfor (Husband h:list) {
System.out.println (H.getid ());
}*/
}
@Test//conditions for adding or
public void Cretica4 () {
Criteria Criteria=session.createcriteria (Husband.class);
Restrictions.or (condition 1, Condition 2); The return value of the method or the Criterion type Object
SELECT * from S_husband where (id>2 or name= ' Briup1 ')
Criteria.add (Restrictions.or (RESTRICTIONS.GT ("id", 2L), Restrictions.eq ("name", "Briup1"));
Restrictions.disjunction () can be followed by multiple or
Association conditions
SELECT * from S_husband where id>3 and ID id<7 and ... or...or. Or
Criteria.add (Restrictions.disjunction ()
. Add (Restrictions.gt ("Salary", 2000))
. Add (Restrictions.eq ("name", "Briup2"))
. Add (Restrictions.eq ("id", 4L))
). Add (Restrictions.gt ("age", 22));
Criteria.add (RESTRICTIONS.GT ("id", 3L))
. Add (restrictions.lt ("id", 7L))
. Add (Restrictions.disjunction ()
. Add (Restrictions.eq ("name", "Briup1"))
. Add (Restrictions.like ("name", "bri%"))
. Add (restrictions.lt ("salary", 5000D)));
/*listfor (Husband h:list) {
System.out.println (H.getid ());
}*/
}
@Test//Connection query
public void Creteria5 () {
Fetch= "Join"
Criteria Criteria=session.createcriteria (Husband.class);
Query husband based on the connection query wife also found out
The wife here refers to the attribute of husband named wife.
Criteria.createcriteria ("wife");
/*listfor (Husband h:list) {
System.out.println (h);
Test whether it is lazy loading
System.out.println (H.getwife (). GetName ());
System.out.println (H.getid () + "--" +h.getname () + "--" +h.getage () + "--" +h.getsalary () + "--" +h.getwife (). GetId () + ";" +h.getwife (). GetName ());
}*/
}
@Test//Sort
public void Creteria6 () {
SELECT * FROM S_husband
Criteria Criteria=session.createcriteria (Husband.class);
where name= "Briup1";
Criteria.add (Restrictions.eq ("name", "Briup1"));
ORDER BY ID ASC
Criteria.addorder (ORDER.DESC ("id"));
/*criteria.addorder (Order.desc ("salary"));
Listfor (Husband h:list) {
System.out.println (H.getid ());
}*/
}
HQL Paging operations
@Test
public void Divitepage () {
String hql= "from Husband";
Query query=session.createquery (HQL);
From where to start, the default is 0
int a=9;
Limit the number of returned bars
int b=3;
Query.setfirstresult (a);
Query.setmaxresults (b);
/*listfor (Husband h:list) {
System.out.println (H.getid () + "--" +h.getname () + "--" +h.getage () + "--" +h.getsalary ());
}*/
}
}
Hibernate Learning Note 5