1. Introduction
Lao Tan in the interview of developers, in order to examine their database development ability, often sacrificed my magic weapon, is the University database tutorial in a model: Students choose Class. This pattern is like this:
In this mode, students (Student) and courses (Course) are entities, each with a primary key ID. Exam results (score) are a many-to-many relationship between students and the curriculum.
Based on this model, for the novice, can make some simple query requirements, for skilled, can make some complex query requirements, easy to use.
But the point today is how to use nhibernate to achieve this pattern. And the general many-to-many relationship slightly different is that the relationship with a property, that is, exam results (score).
If there are attributes on many-to-many relationships, we generally extend this relationship to entities, which is to convert a many-to-many relationship into an entity plus two many-to-one relationships.
If there are multiple properties on many-to-many relationships, it is necessary to convert them to an entity, but there are only a few properties (only one in this case) and it is a waste to convert to entities. Because in general, for an entity, we create an artificial primary key for it, a manual primary key, and a sequence. At the time of mapping, this entity naturally has a corresponding class. It's a lot of trouble to think about this whole bunch of things.
The crux of the matter is that conceptually this is a relationship, for the sake of convenience, and to convert it into an entity, this out-of-the-air entity, making the concept complex, very awkward.
Therefore, here to explore, let the relationship back to the relationship, how to use NHibernate to deal with.
2. Mapping
If the attribute field is not score in the relational table score, the student entity can be mapped to such a class:
public class student{public virtual long Id {get; set;} Public virtual string Name {get; set;} Public virtual ilist<course> Courses {get; set;}}
Course is similar.
However, with the attribute score, this property is lost by this mapping. In order to take the score attribute, two entities should be mapped like this:
public class student{public virtual long Id {get; set;} Public virtual string Name {get; set;} Public virtual Idictionary<course, int> Courses {get; set;}}
public class course{public virtual long Id {get; set;} Public virtual string Name {get; set;} Public virtual idictionary<student, int> Students {get; set;}}
The original list becomes a dictionary (Dictionary), the primary key of the dictionary is the element in the original list, and the value is the attribute value on the relationship, that is, the test result.
The corresponding mapping file should also be adjusted, the results are as follows:
... <class name= "Course" table= "Course" > <id name= "id" unsaved-value= "0" > <column name= "id"/ > ... </id> <property name= "name" > <column name= "name"/> </property> <map Name= "Students" table= "Score" lazy= "true" > <key column= "CourseID"/> <index-many-to-many column = "StudentID" class= "Student"/> <element column= "score"/> </map></class>
... <class name= "Student" table= "Student" > <id name= "id" unsaved-value= "0" > <column name= "id" /> ... </id> <property name= "name" > <column name= "name"/> </property> <map Name= "Courses" table= "Score" lazy= "true" > <key column= "StudentID"/> <index-many-to-many column = "CourseID" class= "Course"/> <element column= "score"/> </map></class>
After such a mapping, the attributes in many-to-many relationships are brought into the class, and the entities that are created for the relationship are avoided-no classes like score. The operation of such mapping results is similar to that of regular many-to-many relational mapping, but there are several issues to be aware of.
3. Enquiry
For some simple queries, such as:
- Xiao Ming's achievements in all courses;
- All the students ' grades in the chemistry course
is easier to handle because it only requires filtering on an entity.
If you need to add filter conditions on two entities, such as query Xiao Ming's chemistry class results, it is somewhat complex, and its query statement hql is this:
Select score from Course C, Student s join c.students score where c.name= ' chemistry ' and index (score) = S.id and S.name= ' Xiao Ming '
There is a hql function we seldom use: Index (). This is specifically for map, which is designed to get the index field of the map.
Paradoxically, although we indicate that the key of the map is an object, such as Course.students key is an object of student, but the result of index () is still <index-many-to-many column= "..." class= The value of the column field in the "..."/>. In the above query, index (score), we expect the result to be a student object, but the result is the ID of the object. Therefore, in the query statement, we have to correlate the student s and use S.name to filter.
Even "simple queries", such as querying the results of all of Xiaoming's courses, have a problem to pay attention to. This problem is related to lazy loading.
In this query, we already know that we need to get all the lessons, so we want to preload:
From Student s join fetch s.courses coursewhere s.name= ' xiaoming '
When the results are obtained, if the environment is detached from the query, such as releasing the session, when accessing the course, such as:
S.courses.select (c = c.key.name)
Exceptions are still thrown. Because, the above query does not load the course object in.
It is not yet known how to preload the indexed objects in the map. If necessary, only rely on lazy loading mechanism.
4. Maintenance
In addition to querying, it is also worth noting that when maintaining a relationship, the cascade of the Save-update type is not valid.
The Cascade property provides a lot of convenience for our relationship maintenance. In a many-to-many relationship with regular (without attributes), our maintenance operations can be:
Xiao Ming selected chemistry class:
using (var tx = session. BeginTransaction ()) { var student = getstudent (studentname)?? New Student {Name = studentname, Courses = new list<course> ()}; var course = GetCourse (coursename)?? New Course {Name = coursename, Students = new list<student> ()}; if (!course. Students.contains (Student)) { course. Students.add (student); } Session. Saveorupdate (course); Tx.commit ();}
Of these, getstudent (Studentname) and GetCourse (Coursename) respectively refer to the loading of the corresponding object from the database based on the student name or course name. In the code snippet above, if none is in the database, create a new. After the relationship is maintained, save it.
It is important to note that when saving course, there is no guarantee that student is a persisted object. If the student has not been persisted, the nhibernate will be saved automatically when saved and the relationship maintained and course. Being able to do so relies on the definition of the Cascade attribute on the relationship in course (the end of the third line):
<class name= "Course" table= "Course" > ... <bag name= "Students" table= "Scoure" lazy= "true" cascade= "Save-update" > <key column= "CourseID"/> <many-to-many column= "StudentID" class= "Student"/> </bag> </class>
However, on a many-to-many relationship that contains attributes, this configuration cannot be configured-configured or not, because you want to use a map. If the student has not been saved in the database, we have to first create and persist it (line 7th):
using (var tx = session. BeginTransaction ()) { var student = getstudent (studentname); if (student = = null) { student = new Student {Name = studentname, Courses = new Dictionary<course, int> ()};< C4/>session. Save (student); } var course = GetCourse (coursename)?? New Course {Name = coursename, Students = new dictionary<student, int> ()}; if (course. Students.containskey (Student)) { course. Students[student] = score; } Else { course. Students.add (student, score); } Session. Saveorupdate (course); Tx.commit ();}
Otherwise, just wait for nhibernate to throw an exception.
5. Conclusion
Many-to-many relationships expressed with map/dicitionary are more complex to operate than those expressed by bag/list. But at such a cost, we are willing to bear it.
This is because we value model design more and focus more on conceptual integrity. It is the model that determines the specific implementation, not the other way around, and modifies the design of the model according to the implementation.
Handle many-to-many relationships with attributes with NHibernate