* 

ps :

I. retrieval method of navigation object graph

Customer c=session.get(Customer.class,2);
c.getOrders().size();

• Through mapping in Hibernate, the associated persistent object information can be obtained by navigation during hibernate operation.

II. OID search method

session.get(Customer.class,3);
session.load(Order.class,1);

• In Hibernate, get/load method is used to query the specified object, and OID is used to query.

Three: QBC

• QBC(query by criteria), which is a more object-oriented retrieval method.
• QBC steps:
  1. Get a Criteria object through Session: session.createCriteria()
  2. Setting condition: the criterian instance, whose acquisition can provide static state through Restrictions class.
     • Add method of Criteria is used to add query Criteria
  3. Call list to query: criterfia.list()

(I) basic search

//Basic search
@Test
public void test1(){
	//Query all customers
	Session session=HibernateUtils.openSession();
	session.beginTransaction();
	
	//1. Get a Criteria object
	Criteria criteria=session.createCriteria(Customer.class);
	
	//2. Call the list method
	List<Customer> list = criteria.list();
	
	System.out.println(list);
	
	session.getTransaction().commit();
	session.close();
	
}

(II) sorting and searching

//Sort query
@Test
public void test2(){
	// Query order information and sort by order price
	Session session = HibernateUtils.openSession();
	session.beginTransaction();
	
	Criteria criteria=session.createCriteria(Order.class);
	//Specified sorting
	//Criteria. Addorder (org. Hibernate. Criteria. Order. Desc ("money"); / / descending
	criteria.addOrder(org.hibernate.criterion.Order.asc("money"));//Descending order
	
	List<Order> list = criteria.list();
	System.out.println(list);
	
	session.getTransaction().commit();
	session.close();
}

• Note that the Order used in the parameters of the criteria.addOrder() method is an object in hibernate

(III) condition retrieval (key points, other frameworks can be written)

• Restrictions: restrictions, constraints. For adding static conditions
  • and(): add multiple static conditions
  • gt():>
  • lt():<
  • ge():>=
  • le():<=
  • eq():==

//Conditional search
@Test
public void test3(){
	Session session = HibernateUtils.openSession();
	session.beginTransaction();
	
	//1. Query the customer whose name is Zhang: Zhang_
	Criteria criteria=session.createCriteria(Customer.class);
	Criterion like=Restrictions.like("name","Zhang_");//Other conditionslt
	
	criteria.add(like);//Adding conditions
	Customer c=(Customer) criteria.uniqueResult();
	System.out.println(c);
	
	//2. Query if the order price is above 1050, and its customer is Zhang
	Criteria cri=session.createCriteria(Order.class);
	SimpleExpression gt = Restrictions.gt("money",1005d);
	SimpleExpression eq = Restrictions.eq("c", c);
	LogicalExpression and = Restrictions.and(gt,eq);
	cri.add(and);
	//List orders = cri.add(Restrictions.and(Restrictions.gt("money", 1050d),Restrictions.eq("c", c))).list();
	List<Customer> list = cri.list();
	System.out.println(list);
	session.getTransaction().commit();
	session.close();
}

(IV) page search

// Paging search
@Test
public void test4() {
	Session session = HibernateUtils.openSession();
	session.beginTransaction();

	Criteria criteria = session.createCriteria(Order.class);
	criteria.setFirstResult((2 - 1) * 6);
	criteria.setMaxResults(6);
	List<Order> list = criteria.list();

	System.out.println(list);

	session.getTransaction().commit();
	session.close();
}

(V) statistical group retrieval (very important, for unconventional operation)

• count
• sum
• avg
• max
• min

//Statistical retrieval
@Test
public void test5(){
	Session session = HibernateUtils.openSession();
	session.beginTransaction();
	
	Criteria criteria=session.createCriteria(Order.class);
	//Object obj = criteria. Setprojection (projects. Rowcount()). Uniqueresult(); / / count total row count(*)
	//System.out.println(obj);
	
	//2. Total price of orders - group statistics of customers
	//Object obj = criteria. Setprojection (projects. Sum ("money")). Uniqueresult(); / / statistics total amount
	//System.out.println(obj);
	
	//3. Display the total amount of customer orders by customer group
	criteria.setProjection(Projections.projectionList().add(Projections.sum("money")).add(Projections.groupProperty("c")));
	List<Object[]> list = criteria.list();
	System.out.println(list);
	for (Object[] objects : list) {
		for (Object obj : objects) {
			System.out.println(obj);
		}
	}
	
	session.getTransaction().commit();
	session.close();
}

(VI) offline condition retrieval

• Offline: the condition is transferred to the dao layer after the web layer is encapsulated, not the retrieval condition directly
• DetachedCriteria

//Offline condition retrieval
@Test
public void test6(){
	//1. Get a DetachedCriteria
	DetachedCriteria dc=DetachedCriteria.forClass(Customer.class);
	dc.add(Restrictions.like("name", "Zhang_"));
	
	//2. Generate Criteria to execute the operation
	Session session = HibernateUtils.openSession();
	session.beginTransaction();
	Criteria criteria = dc.getExecutableCriteria(session);
	List<Customer> list = criteria.list();
	
	System.out.println(list);
	
	session.getTransaction().commit();
	session.close();
}

IV. local SQL

(I) single table operation

// Test execution of local sql
@Test
public void test1() {
	Session session = HibernateUtils.openSession();
	session.beginTransaction();

	SQLQuery sqlQuery = session.createSQLQuery("select * from t_customer");
	// List<Object[]> list = sqlQuery.list();
	sqlQuery.addEntity(Customer.class);
	List list = sqlQuery.list();
	System.out.println(list);

	session.getTransaction().commit();
	session.close();

}

• Local sql also supports named queries.
• You can define sql statements in hbm files or use annotations.

<sql-query name="">
	select * from t_customer
</sql-query>

• Local named sql annotation definition

@NamedNativeQuery(name="findCustomer",query="select * from t_customer")

• If the named sql is executed, an exception will occur

// Test local sql named query
@Test
public void test2() {
	Session session = HibernateUtils.openSession();
	session.beginTransaction();

	Query query = session.getNamedQuery("findCustomer");
	
	List list = query.list();
	
	System.out.println(list);

	session.getTransaction().commit();
	session.close();

}

ExceptionInInitializerError
	* Initialization error
Pure native scalar queries are not yet suppored
	* Local statement not supported

• The reason for the problem: hibernate does not know how to encapsulate the results after executing select * from t_customer.

@SqlResultSetMapping(
	name="customerSetMapping",
	entities={
		@EntityResult(
			entityClass=Customer.class,
			fields={
				@FieldResult(name="id",column="id"),
				@FieldResult(name="name",column="name")
			}
		)
	}
)
@NamedNativeQuery(
    name="findCustomer",
    query="select * from t_customer",
    resultSetMapping="customerSetMapping"
)

(II) multi meter operation

1. Inner join on

SELECT
	*
FROM
	t_customer AS c
INNER JOIN t_order AS o ON c.id = o.c_customer_id;

• It can only get associated data by using internal connection.

2. Implicit inner connection

• Use "comma" to separate tables, and use "WHERE" to eliminate Cartesian product

SELECT
	*
FROM
	t_customer AS c,
	t_order o
WHERE
	c.id = o.c_customer_id;

3. external connection

• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• OUTER can be omitted

SELECT
	*
FROM
	t_customer c
LEFT OUTER JOIN t_order o ON c.id = o.c_customer_id;