[Nhib] one-to-many relationship (join query)

[Nhib] one-to-many relationship (join query)
One-to-multiple query native SQL association query queries the information of a customer, and the order information copied by the customer. Copy code 1 // <summary> 2 // query the order information of a customer and the customer. 3 // </summary> 4/ // <param name = "customerID"> </param> 5 // <returns> </returns> 6 public IList <Customer> GetCustomerOrders (Guid customerID) 7 {8 // obtain ISession instance 9 ISession session = NHibernateHelper. getSession (); 10 // instantiate the IQuery interface; Use ISession. createSQLQuery () method. The parameter passed is the return session of SQL query statement 11. createSQLQuery ("select dis Tinct tb_customer. *, tb_order. * from tb_customer "12 +" inner join tb_order on tb_customer.customerid = tb_order.customerid where tb_customer.customerid =: id ") 13. addEntity ("Customer", typeof (Customer) 14. setGuid ("id", customerID) 15. list <Customer> (); 16} copy the code. Note that the real SQL statement is used, and the data table is used, this is different from hql (hql uses the object-oriented concept and uses Object class objects mapped to data tables ). Copy code 1 // <summary> 2 // query customer information and order information by Customer id 3 // </summary> 4 // <param name = "sender "> </param> 5 // <param name =" e "> </param> 6 protected void btnSearchByID_Click (object sender, eventArgs e) 7 {8 Business. customerBusiness customerBusiness = new Business. customerBusiness (); 9 this. rptCustomerList. dataSource = customerBusiness. getCustomerOrders (new Guid ("B0720295-9541-40B3-9994-610066224DB8"); 10 This. rptCustomerList. dataBind (); 11} copies the SQL statement generated by the Code. Because the inner join is used, the test data has two orders for one customer, therefore, there are two data records. HQL associated query uses HQL to query the information of a customer, and the order information copied by the customer. Copy code 1 // <summary> 2 // HQL to query the order information of a customer and the customer. 3 // </summary> 4 /// <param name = "customerID"> </param> 5 // <returns> </returns> 6 public IList <Customer> GetCustomerOrdersByHQL (Guid customerID) 7 {8 // obtain ISession instance 9 ISession session = NHibernateHelper. getSession (); 10 return session. createQuery ("select c from Customer c inner join c. orders where c. customerID =: Id ") 11. setGuid ("id", customerID) 12. list <Customer> (); 13} by copying the code, we can see that the Orders behind inner join is an attribute of the object Customer. This object-oriented method is more in line with our development habits. The generated SQL statement is used to query Criteria API conditions. The information of a customer and the order information of the customer are navigated between associations using CreateCriteria, it is easy to specify constraints between entities. Here the second CreateCriteria () returns a new ICriteria instance and points to the elements of the Orders object. Use the subcreatecriteria statement to query subobjects. This is because the associations between objects have been defined in the ing file. Alternatively, CreateAlias () does not create a new ICriteria instance. Copy code 1 // <summary> 2 // Criteria API queries a customer's information and the customer's order information 3 /// </summary> 4 /// <param name = "customerID"> </param> 5 // <returns> </returns> 6 public IList <Customer> GetCustomerOrdersByCriteria (Guid customerID) 7 {8 // obtain ISession instance 9 ISession session = NHibernateHelper. getSession (); 10 return session. createCriteria (typeof (Customer) 11. createCriteria ("Orders") 12. add (Restrictions. eq ("Customer. custome RID ", customerID) 13. List <Customer> (); 14} copy the code. Note that because the Order and Customer entities both have the CustomerID attribute, you must specify the CustomerID. The results of the generated SQL statement may be repeated in this way. You can use the following method to pre-filter and use the SetResultTransformer (IResultTransformer resultTransformer) method of the ICriteria interface to return the Customer that meets specific conditions. In the preceding example, we use conditional query to check that the SQL statement generated by the SQL statement does not have distinct. In this case, we can use nhib.pdf. the method in the Transform namespace or the nhib.pdf provided by nhib.pdf. criteriaUtil. rootEntity, nhib.pdf. criteriaUtil. distinctRootEntity, nhib.pdf. criteriaUtil. the AliasToEntityMap static method is used to implement pre-filtering. The preceding query should be modified: copy code 1 // <summary> 2 // Criteria API queries a customer's information and the customer's order information 3 /// </summary> 4 /// <param name = "customerID"> </param> 5 // <returns> </returns> 6 public IList <Customer> GetCustomerOrdersByCriteria (Guid customerID) 7 {8 // obtain ISession instance 9 ISession session = NHibernateHelper. getSession (); 10 return session. createCriteria (typeof (Customer) 11. createCriteria ("Orders") 12. add (Restrictions. eq ("Cus Tomer. customerID ", customerID) 13 // pre-filter duplicate results 14. setResultTransformer (new nhib.pdf. transform. distinctRootEntityResultTransformer () 15 // or. setResultTransformer (nhib.pdf. criteriaUtil. distinctRootEntity) 16. list <Customer> (); 17} if the SQL statement generated by the Code is not pre-filtered, the query Order is one. By comparison, you will find that the SQL statements they generate are the same, and the real filtering should be done in the memory. The SetProjection () method can be called to project an application to a query. Nhib.pdf. Criterion. Projections is an instance factory of Projection, which provides many methods. Copy the Code 1 // <summary> 2 // query a customer's information and the customer's order information by projection 3 /// </summary> 4 /// <param name = "customerID"> </param> 5 // <returns> </returns> 6 public IList <Customer> GetCustomerOrdersByProjection (Guid customerID) 7 {8 // obtain ISession instance 9 ISession session = NHibernateHelper. getSession (); 10 IList <Guid> ids = session. createCriteria (typeof (Customer) 11. setProjection (Projections. distinct (12 Projections. projectionLi St () 13. add (Projections. property ("CustomerID") 14) 15) 16. createCriteria ("Orders") 17. add (Restrictions. eq ("Customer. customerID ", customerID) 18. list <Guid> (); 19 return session. createCriteria (typeof (Custome. add (Restrictions. in ("CustomerID", ids. toArray <Guid> () 21. list <Customer> (); 22} copy the code. We can add several projects to the projection List. In this example, I add a CustomerId attribute value to the projection List, the Distinct projection is set for all attribute values in this list. The first sentence is the customer mermerid returned for the order, and the second sentence is based on the returned Cus. TomerId: query the customer list. Achieve the above purpose. In this case, the generated SQL statement contains distinct. We can use projection to easily combine the various methods we need.