1. Hibernate Query

1. Classification of Hibernate retrieval methods

• OID retrieval: based on primary key query, get/load

• Object Navigation Retrieval: Get its associated objects from one object. [Key]

  Category category = session.get(Category.class, 1);
  Set<Product> products = category.getProducts();

• HQL Retrieval: HQL,Hibernate Query Language(Hibernate Query Language, syntax similar to SQL, but object-oriented) [Master]

• QBC Retrieval: QBC, Query By Criteria (Conditional Query, More Object-Oriented Query Method) [Master]

• SQL Retrieval: SQL, Query using native SQL [Understanding]

2.HQL Query

2.1 Overview

HQL: Hibernate Query Language.Is an object-oriented query language (column names in SQL should write attribute names in hql), which is somewhat similar to the SQL query language.

Syntax:

String hql =...

​ Query query = session.createQuery(hql);

• list(); query multiple

• uniqueResult(); query one

  Principle: Change table name to class name, database column to property name in Java

2.2 Basic Query

2.2.1 Query All

• Query all categories

   1 //Query all categories
   2     @Test
   3     public void fun01(){
   4         Session session = HibernateUtils.openSession();
   5         Transaction transaction = session.beginTransaction();
   6         
   7         //String hql = "select c from Category c";
   8         String hql = "from Category";
   9         Query query = session.createQuery(hql);
  10         List<Category> list = query.list();
  11         for (Category category : list) {
  12             System.out.println(category.toString());
  13         }
  14         
  15         transaction.commit();
  16         session.close();
  17     }

2.2.2 Conditional Query

• Greater than, less than, greater than or equal to, greater than or less than the query

   1 //query id Classification greater than 1
   2     @Test
   3     public void fun02(){
   4         Session session = HibernateUtils.openSession();
   5         Transaction transaction = session.beginTransaction();
   6         
   7         /*String hql = "from Category where cid > ?";
   8         Query query = session.createQuery(hql);
   9         query.setInteger(0, 1);*/
  10         
  11         String hql = "from Category where cid > :category id";
  12         Query query = session.createQuery(hql);
  13         query.setInteger("category id", 1);
  14         List<Category> list = query.list();
  15         for (Category category : list) {
  16             System.out.println(category.toString());
  17         }
  18         
  19         transaction.commit();
  20         session.close();
  21     }

• like query

   1 //Query for commodity name inclusion iPhone Commodities
   2     @Test
   3     public void fun03(){
   4         Session session = HibernateUtils.openSession();
   5         Transaction transaction = session.beginTransaction();
   6         
   7         
   8         String hql = "from Product where pname like ?";
   9         Query query = session.createQuery(hql);
  10         query.setString(0, "%iPhone%");
  11         List<Product> list = query.list();
  12         for (Product product: list) {
  13             System.out.println(product.toString());
  14         }
  15         
  16         transaction.commit();
  17         session.close();
  18     }

• between and query

  String hql = "from Product where price between ? and ?";
  Query query = session.createQuery(hql);
  query.setDouble(0, 2000);
  query.setDouble(1, 5000);
  List<Product> list = query.list();

• in query

   1 @Test
   2      //query pid stay(2,8,11,12,100)Commodity Information
   3      public void fun05(){
   4          Session session = HibernateUtils.getCurrentSession();
   5          Transaction transaction = session.beginTransaction();
   6          
   7         //String sql = "select * from t_product where  pid in(?,?,?,?)";
   8          String hql = "from Product where pid in(?,?,?,?)";
   9          Query query = session.createQuery(hql);
  10          //Setting parameters,query
  11          List list = query.setInteger(0, 2).setInteger(1, 8).setInteger(2, 11).setInteger(3, 12).list();
  12          
  13          System.out.println(list.toString());
  14          
  15          transaction.commit();
  16 ​
  17      }

2.3. Aggregate queries

• Number of statistical categories

  String hql = "select count(*) from Category";
  Query query = session.createQuery(hql);
  Long n =  (Long) query.uniqueResult();
  System.out.println(n.intValue());

2.4 Grouped Query

• Count the number of items in each category

  String hql = "select category.cid, count(*) from Product group by category.cid";
  Query query = session.createQuery(hql);
  List<Object[]> list = query.list();
  for (Object[] objects : list) {
      System.out.println(Arrays.toString(objects));
  }

2.5 Sort Query

• Anzhao commodity price (ascending) inquiry for all commodities

  String hql = "from Product order by price asc";
  Query query = session.createQuery(hql);
  List<Product> list = query.list();
  for (Product product : list) {
      System.out.println(product);
  }

2.6. Paging queries

setFirstResult: Sets the subscript to start the query, with a minimum value of 0, where 0 represents the first record

setMaxResults: Sets the number of bars displayed for query results.

• Display the data on the first page, with 4 entries on one page

  String hql = "from Product";
  Query query = session.createQuery(hql);
  query.setFirstResult(0);
  query.setMaxResults(4);
  List<Product> list = query.list();
  for (Product product : list) {
          System.out.println(product);
  }

2.7 Projection Query

Query specified fields

• Query the name and price of a product

  String hql = "select pname, price from Product";
  Query query = session.createQuery(hql);
  List<Object[]> list =  query.list();
  for (Object[] objects : list) {
      System.out.println(Arrays.toString(objects));
  }

3.QBC Query

3.1 Overview

QBC, Query By Criteria

3.2 Common Conditional Queries

• grammar

  //Establish QBC Conditional Query
  Criteria criteria = session.createCriteria(Class);
  //Add Conditions
  criteria.add(Restrictions.api...);
  //Query Multiple
  List list = criteria.list();
  //Or query one
  Object object =  criteria.uniqueResult();

operator	Conditional API	describe
=	Restrictions.eq()	Be equal to
>	Restrictions.gt()	greater than
<	Restrictions.lt()	less than
>=	Restrictions.ge()	Greater than or equal to
<=	Restrictions.le()	Less than or equal to
between	Restrictions.between()	Within a range
like	Restrictions.like()	Fuzzy Query
in	Restrictions.in()	In...
and	Restrictions.and()	also
or	Restrictions.or()	perhaps

 

• Java Code

   1 // Conditional Query
   2     @Test
   3     public void fun01() {
   4         Session session = HibernateUtils.openSession();
   5         Transaction transaction = session.beginTransaction();
   6 ​
   7         Criteria criteria = session.createCriteria(Product.class);
   8         List<Product> list = criteria.add(Restrictions.between("price", 2000.0, 3000.0)).list();
   9         for (Product product : list) {
  10             System.out.println(product.toString());
  11         }
  12         
  13         transaction.commit();
  14         session.close();
  15     }

  //and query; Query commodity names to iP Goods that start with a price greater than 4000
  Criteria criteria = session.createCriteria(Product.class);
  List<Product> list = criteria.add(Restrictions.like("pname", "iP%")).add(Restrictions.gt("price", 4000.0)).list();
  

  //or query;  Query price greater than 3000 or pid=1 Commodities
  Criteria criteria = session.createCriteria(Product.class);
  List<Product> list = criteria.add(Restrictions.or(Restrictions.gt("price", 3000.0)).add(Restrictions.eq("pid", 1))).list();

3.3. Aggregate queries

• grammar

  //Establish QBC Conditional Query
  Criteria criteria = session.createCriteria(Class);
  //Set Aggregation
  criteria.setProjection(Projections.api...);
  //Query Out Results
  long result = (long) criteria.uniqueResult();

  operator	Conditional API	describe
  sum	Projections.sum()	Summation
  count	Projections.count()	count
  max	Projections.max()	Maximum
  min	Projections.min()	minimum value
  avg	Projections.avg()	average value

• Count the number of goods

  Criteria criteria = session.createCriteria(Product.class);
  Object result = criteria.setProjection(Projections.count("pid")).uniqueResult();
  System.out.println("result="+result);

• Polynomial Aggregation

• //Establish QBC Conditional Query
  Criteria criteria = session.createCriteria(Class);
  //Add Multiple Aggregates
  ProjectionList list = Projections.projectionList();
  list.add(Projections.api...);
  ...
  //Set Aggregation
  criteria.setProjection(list);
  //Query Out Results
  Object[] result = (Object[]) criteria.uniqueResult();
  
  //Example
  //Count the number of items in each category  cid count(pid)  Two Aggregation Conditions
  Criteria criteria = session.createCriteria(Product.class);
  ProjectionList projectionList = Projections.projectionList();   
  projectionList.add(Projections.groupProperty("category.cid"));
  projectionList.add(Projections.count("pid"));
  List<Object[] > list = criteria.setProjection(projectionList).list();

3.4 Grouped Query

• Counting the quantity of goods in different categories

  Criteria criteria = session.createCriteria(Product.class);
  ProjectionList projectionList = Projections.projectionList();
  projectionList.add(Projections.count("pid")).add(Projections.groupProperty("category.cid"));
  ​
  List<Object[]> list = criteria.setProjection(projectionList).list();
  for (Object[] objects : list) {
      System.out.println(Arrays.toString(objects));
  }

3.5 Sort Query

• grammar

  //Establish QBC Conditional Query
  Criteria criteria = session.createCriteria(Class);
  //Add Sort
  criteria.addOrder(Order.api...);
  //Query Out Results
  List list = criteria.list();

  operator	Conditional API	describe
  desc	Order.desc()	Descending order
  asc	Order.asc()	Ascending order

3.6. Paging queries

3.7 Offline Conditional Query

Conditional queries have been executed before, but they all create Criteria objects before continuing to append conditions.This looks good when there are fewer submissions.If all of our parameters were passed in by the upper service, it would not look so good.So we wanted to see if we could pack it outside and just give me a general condition.This general condition is to include all the preceding conditions.This is the offline conditional query.This means that you can now encapsulate the condition first and create Criteria out of session.

• grammar

  //Create offline DetachedCriteria object
  DetachedCriteria detachedCriteria = DetachedCriteria.forClass(User.class);
  //Set Conditions
  detachedCriteria.add(Restrictions.like("u_name", "%O%"));
  //To executable state
  Criteria criteria = detachedCriteria.getExecutableCriteria(session);
  
  List<User> list = criteria.list();

4,SQL Query [Understanding]

• Eg

  String sql = "select * from t_product";
  SQLQuery sqlQuery = session.createSQLQuery(sql);
  sqlQuery.addEntity(Product.class);
  ​
  List<Product> list = sqlQuery.list();
          
  for (Product product : list) {
      System.out.println(product.toString());
  }

5. Connection Query

Multi-table Query in 5.1 MySql

5.1.1 Cross-query

• grammar

  select * from A,B

Connection queries within 5.1.2

• Implicit inline join query

   select * from A ,B where A.id = B.id;

• Display inner connection

  select * from A inner join B on A.id = B.id;

5.1.3 External Connection Query

• Left Outer Connection

   select * from A left outer join B on A.id = B.id;

• Right Outer Connection

   select * from A right outer join B on A.id = B.id;

5.2 HQL Connection Query

5.2.1 Category

• Cross Connection

• Internal connection: internal connection, urgent internal connection

• Outer Connection: Left Outer Connection, Right Outer Connection

  line type	grammar
  Internal connection	inner join or join
  Urgent internal connection	inner join fetch or join fetch
  Left Outer Connection	left outer join or left join
  Urgent Left Outer Connection	left outer join fetch or left join fetch
  Right Outer Connection	right outer join or right join
  Urgent Right Outer Connection	right outer join fetch or right join fetch

5.2.2 internal connection

An inner join query can get records of the common parts of both tables.

• Connection within HQL (send three query statements)

  String hql = "from Category c inner join c.products";
  Query query = session.createQuery(hql);
  List<Object[]> list =  query.list();

• HQL Urgent Inline Connection (Send a Query Statement)

  //String hql = "from Category c inner  join fetch c.products";
  String hql = "select distinct c  from Category c inner  join fetch c.products";
  Query query = session.createQuery(hql);
  List<Category> list =  query.list();

5.2.3 External Connection

• HQL Left Outer Connection

  String hql = "from Category c left outer join  c.products";
  List<Object[]> list = session.createQuery(hql).list();

• HQL Urgent Left Outer Connection

  String hql = "from Category c left outer  join fetch c.products";
  List<Category> list = session.createQuery(hql).list();

Summary:

• • Urgent join is Hibernate's unique query method, Urgent inner join has one more keyword fetch than inner join
  • Object[] array in inner join query result
  • Urgently join query results to specific objects.

2. Query optimization

1. Class-level delayed loading

1.1 Description

Lazy Loading is on by default. Normally no modifications are required

session.get(): Non-lazy load method

session.load(): The default is lazy loading

1.2 Invalidating lazy load

Configure lazy="false" on the <class>label of the mapping profile

<class name="com.pri.bean.Category" table="t_category" lazy="false">

2. Lazy loading at association level

2.1 Overview

Whether related objects are immediately queried when the corresponding objects are queried; eg: Query id is categorized as 1 and whether goods under this category are immediately queried

Grab policy: means to grab its associated objects, and has a fetch attribute on the <set> and <many-to-one> tags. fetch controls what type of statement is sent to grab its associated objects

Fetch: fetch policy: control what type of Sql statement is sent Sql statement: that is, query (join) to a table

Lazy: lazy or not lazy

2.2set Lazy Loading

2.2.1 Description

Configure fetch and lazy on the set tag

• fetch: Controls the format of the SQL statement used when querying its associated objects.

  Value of fetch	SQL statement type sent
  select:Default	Send a select statement to query its associated objects.
  join	Send an urgent left outer join query for associated objects.
  subselect	Send a subquery to query its associated objects.

• lazy: Controls whether a delayed loading policy is used when querying its associated objects.

  Value of lazy	Meaning
  True (default)	Default delay loading is used when querying associated objects.
  false	Do not use delayed loading when querying associated objects
  extra	And its laziness. Query related objects in a lazier way than delay loading.

2.2.2 children

The following actions are performed on one side, that is, configuring fetch and lazy on the set tag

• When fetch is select ed

  Value of fetch	Value of lazy	Result
  select	true	Load only when in use (lazy load) Note: Default
  select	false	Load when one party loads (not lazy)
  select	extra	Loading occurs when many parties are in use, and aggregation queries only occur if the operation in use is an aggregation type.

• When fetch is join ed

  Value of fetch	Value of lazy	Result
  join	lazy invalidation (true or false)	fetch uses an urgent left outer join query that queries the results of both tables.lazy fails.

• When fetch is subselect

  Value of fetch	Value of lazy	Result
  subselect	true	Many parties load when in use, sending subquery statements
  subselect	false	Many parties load when one party loads, sending subquery statements
  subselect	extra	Many parties load when one party loads, and if the operation used is aggregate type, only aggregate queries send subquery statements

  Note: When using a Hibernate subquery, you cannot look up one. If Hibernate finds that the value of in() is one, change in to =

2.3many-to-one lazy loading

2.3.1 Description

Configure fetch and lazy on many-to-one Tags

• fetch: Controls the format of the SQL statement used when querying its associated objects.

  Value of fetch	SQL statement type sent
  select:Default	Send a select statement to query its associated objects.
  join	Send an urgent left outer join query for associated objects.

• lazy: Whether delayed loading is used when querying its associated objects.

  Value of lazy	Meaning
  proxy	Default value. Whether the delay is adopted depends on the lazy value on one of the class es.
  false	Do not delay loading its associated objects.
  No-proxy (Understanding)	Do not research whether to use delay

2.3.2 children

The following actions are performed on multiple parties, that is, configuring fetch and lazy on the many-to-one tag

• When select is fetch, ordinary sql statements are sent

  Value of fetch	Value of lazy	Result
  select	proxy	If the lazy value on one party's class is true, add it with one party (lazy loading); if the lazy value on one party's class is false, load it when one party loads (not lazy loading)
  select	false	One party loads when it loads more than one party (not lazily)

• When select is join, there is an urgent left outer join

  Value of fetch	Value of lazy	Result
  join	Invalid	Send left outer join query, not lazy load

3 Mass Grab (Understanding)

• Configure batch-size = num on the set tag

  1 Criteria criteria = session.createCriteria(Category.class);
  2 List<Category> list = criteria.list();
  3 ​
  4 for (Category category : list) {
  5     Set<Product> products = category.getProducts();
  6     for (Product product : products) {
  7         System.out.println(product.toString());
  8     }
  9 }