Asp. Net large project practice (4)-saving and querying our business domain objects with nhib.pdf as many as table join queries (with source code)

The multi-table join query of nhib.pdf is still very good. In addition to the bt report query, the multi-Table query required by the business can basically meet the requirements, and the efficiency is not bad, nhib.pdf multi-Table query is actually relatively simple at the database access layer. Instead, the difficulty lies in how to bind the multi-layer objects to the UI without switching to DTO. The next article will describe the presentation layer in detail (note: the usage and advantages and disadvantages of DTO are not discussed here. The design principle in this system is to avoid using DTO as much as possible ).

We still use the dictionary maintenance example. We need to display a column of "category" in the dictionary project table, that is, the name of the dictionary category to which the dictionary project belongs, this field does not exist in the dictionary item table and can be obtained only when it is associated with the dictionary category table. For example, (because of the actual data of the Project customers, I have changed the figure ...):

 

In the previous article, we changed the public IList <Dictionary> GetPlistByCategoryId (...) method that provides the paging, sorting, and multi-condition combination query functions:

 

Code

 // Now I have paging, sorting, multi-condition query, and multi-table join query, which is almost complete :)
Public IList <Dictionary> GetPlistByCategoryId (string id, int start, int limit, string sort, string dir, List <DataFilter> filters, out long total)
{
Sort = "d." + sort;
Var strFilter = base. GetHqlstrByExtFilter (filters, "d"); // convert filters to HQL strings using the method of the base class just now.

Var query = Session. createQuery (@ "select d from Dictionary as d left join fetch d. category where d. category. id =: Id "// changed here to create a multi-Table query
+ (String. IsNullOrEmpty (strFilter )? String. Empty: "and" + strFilter) // Add the combined query string here.
+ "Order by" + sort + "" + dir)
. SetString ("Id", id)
. SetFirstResult (start)
. SetMaxResults (limit );

Total = Session. CreateQuery (@ "select count (*) from Dictionary as d where d. Category. Id =: Id"
+ (String. IsNullOrEmpty (strFilter )? String. Empty: "and" + strFilter) // do not forget to add it here.
. SetString ("Id", id)
. UniqueResult <long> ();

Return query. List <Dictionary> ();
}

The HQL statement is modified.

Previously var query = Session. CreateQuery (@ "select d from Dictionary as d where d. Category. Id =: Id "...

After the modification, var query = Session. CreateQuery (@ "select d from Dictionary as d left join fetch d. Category where d. Category. Id =: Id "...

Added "left join fetch d. Category" to explain as follows:

A. "left join": The left join keyword of HQL, which is the same as SQL, should be easy to understand.

B. "fetch": The HQL keyword, which is very important. It indicates that the data of the associated class is retrieved at one time without using delayed loading. Because we set lazy = true during Xml configuration (delayed loading of included objects), the default NHibernate does not retrieve the data of the Category attribute under the Dictionary, instead, you can query the database again when you need it. This is a good thing, but when we get out of the set, there will be a terrible situation. A Dictionary data is read once and the database is used to retrieve the Category under the Dictionary, for example, if we paging 15 pieces of data here, 17 SQL statements will be generated (total number of queries per query + dictionary item table per query + dictionary category corresponding to each dictionary item for 15 queries ), terrorism...

Therefore, we use fetch to tell NHibernate that you can retrieve the data at one time without delay loading. This will only generate two SQL statements as before, as shown below:

The total number of queries remains the same as before:

 select count(* ) as col_0_0_
from   INFRA_DICTIONARY dictionary0_
where  (dictionary0_.IsDelete = 0)
and dictionary0_.DICCATEGORY_ID = '48391bb4-471b-4499-899b-cea9748e1a7b' /* :p0 */

 

The SQL statement for the associated query is generated (except for the statement generation between 'select' and 'from', the other statements are similar to those written by US ):

Code

 select *
from   (select   dictionary0_.DICTIONARY_ID  as DICTIONARY1_0_0_,
                 diccategor1_.DICCATEGORY_ID as DICCATEG1_1_1_,
                 dictionary0_.VERSION        as VERSION0_0_,
                 dictionary0_.NAME           as NAME0_0_,
                 dictionary0_.CODE           as CODE0_0_,
                 dictionary0_.INPUT_CODE1    as INPUT5_0_0_,
                 dictionary0_.INPUT_CODE2    as INPUT6_0_0_,
                 dictionary0_.INPUT_CODE3    as INPUT7_0_0_,
                 dictionary0_.INDEX_FIELD    as INDEX8_0_0_,
                 dictionary0_.DESCRIPTION    as DESCRIPT9_0_0_,
                 dictionary0_.CREATETIME     as CREATETIME0_0_,
                 dictionary0_.ISDELETE       as ISDELETE0_0_,
                 dictionary0_.DICCATEGORY_ID as DICCATE12_0_0_,
                 diccategor1_.VERSION        as VERSION1_1_,
                 diccategor1_.NAME           as NAME1_1_,
                 diccategor1_.PARENT_ID      as PARENT4_1_1_,
                 diccategor1_.TREE_CODE      as TREE5_1_1_,
                 diccategor1_.LEAF           as LEAF1_1_,
                 diccategor1_.DESCRIPTION    as DESCRIPT7_1_1_,
                 diccategor1_.CREATETIME     as CREATETIME1_1_,
                 diccategor1_.ISDELETE       as ISDELETE1_1_,
                 diccategor1_.NODE_LEVEL     as NODE10_1_1_
from     INFRA_DICTIONARY dictionary0_
left outer join INFRA_DICCATEGORY diccategor1_
on dictionary0_.DICCATEGORY_ID = diccategor1_.DICCATEGORY_ID
where    (dictionary0_.IsDelete = 0)
and dictionary0_.DICCATEGORY_ID = '48391bb4-471b-4499-899b-cea9748e1a7b' /* :p0 */
order by dictionary0_.INDEX_FIELD desc)
where  rownum <= 15 /* :p1 */

 

Test it in our very earthy test method, and access Dictionary through columns. category. name (add some test data by yourself), you can open the NHProfiler tool to monitor it, and then remove the "fetch" mentioned above, so that you can understand it. The test code is as follows (it is very simple to write and can be understood, and I am not engaged in unit testing in terms of time ):

 

Code

 // Test it
Public ActionResult test3 ()
{
Demo. HIS. Infrastructure. Core. Repositories. IDictionaryRepository r = new Demo. HIS. Infrastructure. Repositories. Data. DictionaryRepositoryImpl ();
Long total;
Var list = r. GetPlistByCategoryId ("48391bb4-471b-4499-899b-cea9748e1a7b", 0, 15, "Index", "desc", null, out total );
String str;
Foreach (var item in list)
{
Str = item. Category. Name; // If You Don't Need fetch, I will access the database every time, haha
}
Return Content ("");
}

 

For example, we can associate multiple tables with complex points:

Code

             var query = Session.CreateQuery(@"select g from GroupedServiceItem g 
                                                left join fetch g.Item i
                                                left join fetch i.ItemCategory 
                                                left join fetch i.MedicalCategory
                                                left join fetch i.FeesCategory
                                                where g.ParentItem.Id=:Id")
                    .SetString("Id", id);

The above code is randomly extracted from the project, which can be understood in general...

 

As mentioned above, we have implemented multi-table queries for nhib.pdf, but the objects we query contain nesting. How can we send this structure to the UI and Ext for data binding? Please refer to the next article...

Source code (the source code of the previous article is also here, do a good job of the database, the whole point of test data, you can run the test as I described above): HISDemo-5.rar