HQL statement
In Hibernate, although simple primary key query statements can be generated directly by Hibernate using the API, complex query statements need our own handwriting. Hibernate provides us with an HQL statement to use. HQL is the abbreviation of Hibernate Query Language, HQL is a statement close to SQL, but the object we query is no longer a table of the database, andIs a persistent class.
- Full Check Statement
/**
* Full check operation
*/
@Test
public void test1() {
//Get session from custom tool class, see Note 1 for details
Session session = HibernateUtil.openSession();
//Open Transaction
Transaction transaction = session.beginTransaction();
/**
* Create Query Statement
* A full check means that all the data in the table is found
* The HQL statement used is "from class name", where User is the class name of the persistent class, note that it is not a table name
* Can't use select *
*/
Query query = session.createQuery("from User");
/**
* Gets all the data, returns the list collection, which is the object of the persistent class
*/
List<User> list = query.list();
/**
* Traverse Print Each Data
*/
for (User user : list) {
System.out.println(user);
}
//Submit Transaction
transaction.commit();
//close resource
session.close();
}
- Single Column Query
/**
* Single Column Find Operation
*/
@Test
public void test2() {
//Get session from custom tool class, see Note 1 for details
Session session = HibernateUtil.openSession();
//Open Transaction
Transaction transaction = session.beginTransaction();
/**
* Create Query Statement
* Single column lookup means that all data in a column of a table is found
* The HQL statement used is "select class attribute name from class name".
* Here User is the class name of the persistent class, note that it is not a table name, uname is the property name in the class
*/
Query query = session.createQuery("select uname from User");
/**
* Gets all the data, returns the list collection, which places the Object object
*/
List<Object> list = query.list();
/**
* Traverse Print Each Data
*/
for (Object object : list) {
System.out.println(object);
}
//Submit Transaction
transaction.commit();
//close resource
session.close();
}
- Multi-Column Query
/**
* Multi-column lookup operation
*/
@Test
public void test3() {
//Get session from custom tool class, see Note 1 for details
Session session = HibernateUtil.openSession();
//Open Transaction
Transaction transaction = session.beginTransaction();
/**
* Create Query Statement
* Multi-column lookup means that all data in a table with multiple columns is found
* The HQL statement used is "select class attribute name, class attribute name from class name".
* Here User is the class name of the persistent class, note that it is not a table name, uid,uname is the property name in the class
*/
Query query = session.createQuery("select uid,uname from User");
/**
* Gets all the data, returns the list collection, which places the Object array object
*/
List<Object[]> list = query.list();
/**
* Traverse Print Each Data
*/
for (Object[] object : list) {
System.out.println(Arrays.toString(object));
}
//Submit Transaction
transaction.commit();
//close resource
session.close();
}
- Conditional Query
/**
* Conditional lookup operation
*/
@Test
public void test4() {
//Get session from custom tool class, see Note 1 for details
Session session = HibernateUtil.openSession();
//Open Transaction
Transaction transaction = session.beginTransaction();
/**
* Create Query Statement
* Conditional lookup means that all eligible data in a table is found
* The HQL statement used is the "from class name where +condition", which can be used here?placeholder
* Here User is the class name of the persistent class, note that it is not a table name, the condition is used like a SQL statement
*/
Query query = session.createQuery("from User where uname like ?");
//Set the value of the placeholder, starting at 0
query.setParameter(0, "Small%");
/**
* Gets all the data, returns the list collection, which places the user object
*/
List<User> list = query.list();
/**
* Traverse Print Each Data
*/
for (User user : list) {
System.out.println(user);
}
//Submit Transaction
transaction.commit();
//close resource
session.close();
}
- Paging Query
/**
* Paging Find Operation
*/
@Test
public void test5() {
//Get session from custom tool class, see Note 1 for details
Session session = HibernateUtil.openSession();
//Open Transaction
Transaction transaction = session.beginTransaction();
/**
* Create Query Statement
* Limit is not used because it is possible to use a database other than mysql and limit does not work
*/
Query query = session.createQuery("from User");
//Set the starting point to check from which data
query.setFirstResult(1);
//Set up several pieces of data for each query
query.setMaxResults(2);
/**
* Gets all the data, returns the list collection, which places the user object
*/
List<User> list = query.list();
/**
* Traverse Print Each Data
*/
for (User user : list) {
System.out.println(user);
}
//Submit Transaction
transaction.commit();
//close resource
session.close();
}
- Projection Query
/**
* Projection Find Operation
*/
@Test
public void test6() {
//Get session from custom tool class, see Note 1 for details
Session session = HibernateUtil.openSession();
//Open Transaction
Transaction transaction = session.beginTransaction();
/**
* Create Query Statement
* Projection lookup means that all data in one or more columns of a table is retrieved and encapsulated as a persisted object to be returned
* HQL Statement: select new class name (one or more class property names) from User
* Note that depending on which columns of attributes to encapsulate, there must be corresponding construction methods in the persistent class, and empty constructs must be preserved
*/
Query query = session.createQuery("select new User(uid,uname) from User");
/**
* Gets all the data, returns the list collection, which places the user object
*/
List<User> list = query.list();
/**
* Traverse Print Each Data
*/
for (User user : list) {
System.out.println(user);
}
//Submit Transaction
transaction.commit();
//close resource
session.close();
}
- Aggregate queries
/**
* Aggregate function operation
*/
@Test
public void test7() {
//Get session from custom tool class, see Note 1 for details
Session session = HibernateUtil.openSession();
//Open Transaction
Transaction transaction = session.beginTransaction();
/**
* Create Query Statement
* The HQL statement used is the "select aggregate function from class name", where User is the class name of the persistent class, note that it is not a table name
* Can't use select *
*/
Query query = session.createQuery("select count(*) from User");
/**
* Notice that aggregate functions usually return a numeric value, where uniqueResult is used to return Object
*/
Object object = query.uniqueResult();
/**
* Traverse Print Each Data
*/
System.out.println(object);
//Submit Transaction
transaction.commit();
//close resource
session.close();
}
- Sort Query
/**
* Sort Query Operation
*/
@Test
public void test8() {
//Get session from custom tool class, see Note 1 for details
Session session = HibernateUtil.openSession();
//Open Transaction
Transaction transaction = session.beginTransaction();
/**
* Create Query Statement
* The HQL statement used is "from class name order by attribute name desc/asc", where User is the class name of the persistent class, note that it is not a table name
* Can't use select *
*/
Query query = session.createQuery("from User order by uid asc");
/**
* Gets all the data, returns the list collection, which places the user object
*/
List<User> list = query.list();
/**
* Traverse Print Each Data
*/
for (User user : list) {
System.out.println(user);
}
//Submit Transaction
transaction.commit();
//close resource
session.close();
}