MyBatis Learning Two, SQL statement mapping file (1) resultmap

The SQL mapping XML file is where all SQL statements are placed. You need to define a workspace, which is generally defined as the path to the corresponding interface class. After you have written the SQL statement mapping file, you need to refer to it in the MyBatis profile mappers tag, for example:

XML code

1. <mappers>
2. <mapper resource= "Com/liming/manager/data/mappers/usermapper.xml"/>
3. <mapper resource= "Com/liming/manager/data/mappers/studentmapper.xml"/>
4. <mapper resource= "Com/liming/manager/data/mappers/classmapper.xml"/>
5. <mapper resource= "Com/liming/manager/data/mappers/teachermapper.xml"/>
6. </mappers>

When the Java interface is under a relative path to the XML file, it may not be declared in the mappers of the MyBatis configuration file.

Some of the primary elements of the SQL mapping XML file are:

1. cache– Configuring the cache for a given pattern
2. cache-ref– referencing a cache from another schema
3. resultmap– This is one of the most complex and powerful elements, which describes how to load objects from a result set
4. sql– a SQL block that can be reused by other statements
5. insert– Map INSERT statement
6. update– Map UPDATE statement
7. delete– Map Deleete Statement
8. Select-Map SELECT statement

2.1 Resultmap

Resultmap is the most important and powerful element in MyBatis. You can save 90% of your code than using the JDBC call result set, or you can do a lot of things that JDBC does not support. In reality, it is possible to write a complex statement that equates to an interaction-like mapping, with thousands of lines of code. The purpose of resultmaps is that such simple statements do not require redundant result mappings, and more complex statements that do not require anything other than the absolutely necessary statements to describe the relationship.

Resultmap property: Type is a Java entity class; ID is the identity of this resultmap.

Resultmap the mappings that can be set:

1. constructor– used to reflect the result to a constructor of a class that is instantiated well

a) idarg–id parameter; Mark the result set as ID for easy global invocation
b) The usual result of arg– reflection to the constructor

2. Id–id results, mark the result set as ID to facilitate global invocation

3. result– reflection to the JavaBean attribute normal results

4. association– of complex types; multiple result composition types

a) nested result mappings– several resultmap own nested associations, or can be referenced to a

5. collection– Complex Type set a collection of complex types

6. The collection of nested result Mappings–resultmap can also be referenced to one of the other

7. discriminator– uses a result value to determine which resultmap to use

A) Case scenario for the result mapping of case– basic values

I. Nested result mappings– A case scenario is itself a result map, so you can include some of the same elements, or you can refer to an external resultmap.

2.1.1 ID, result

The ID, result is the simplest mapping, the ID is the primary key mapping, and the result is the mapping of other basic database table fields to entity class properties.
The simplest example:

XML code

2. <resultmap type= "liming.student.manager.data.model.StudentEntity" id= "Studentresultmap" >
4. <id property= "StudentID" column= "student_id" javatype= "String" jdbctype= "VARCHAR"/>
6. <result property= "Studentname" column= "Student_name" javatype= "String" jdbctype= "VARCHAR"/>
8. <result property= "Studentsex" column= "Student_sex" javatype= "int" jdbctype= "INTEGER"/>
10. <result property= "Studentbirthday" column= "Student_birthday" javatype= "date" jdbctype= "date"/>
12. <result property= "Studentphoto" column= "Student_photo" javatype= "byte[" "jdbctype=" BLOB "typehandler=" Org.apache.ibatis.type.BlobTypeHandler "/>
14. </resultMap>

ID, Result statement property configuration details:

Description
Property	need to map to JavaBean attribute names.
column	Data table column name or a label alias.
Javatype	A full Class name, or is a type alias. If you match a JavaBean, the mybatis will usually detect it on its own. Then, if you are going to map to a hashmap, then you need to specify the purpose Javatype to achieve.
Jdbctype	Data Sheet support The list of types held. This property is useful only for columns that allow nulls when insert,update or delete. JDBC needs this, but MyBatis doesn't need it. If you are directly coding for JDBC, and you have a column that allows nulls, you specify this.
Typehandler	Make Use this property to overwrite the type processor. This value can be either a full class name or a type alias.

Supported JDBC Types
For future references, MyBatis supports the following JDBC types, through the Jdbctype enumeration:
Bit,float,char,timestamp,other,undefined,tinyint,real,varchar,binary,blob,nvarchar,smallint,double,longvarchar , Varbinary,clob,nchar,integer,numeric,date,longvarbinary,boolean,nclob,bigint,decimal,time,null,cursor

2.1.2 Constructor

When we use ID, result, we need to define the properties of the Java entity class to map to the fields of the database table. This time is implemented using JavaBean. Of course, we can also use the constructor of the entity class to implement the mapping of the value, which is assigned by constructing the order of the method parameter's writing.
Limited functionality with CONSTRUCOTR (for example, using collection cascading queries).
The functions implemented with ID and result can be changed to:

XML code

1. <resultmap type= "studententity" id= "Studentresultmap" >
2. <constructor>
3. <idarg javatype= "String" column= "student_id"/>
4. <arg javatype= "String" column= "Student_name"/>
5. <arg javatype= "String" column= "Student_sex"/>
6. <arg javatype= "Date" column= "Student_birthday"/>
7. </constructor>
8. </resultMap>

Of course, we need to define how the Studententity entity class is constructed:

Java code

1. Public studententity (String StudentID, String studentname, String studentsex, Date studentbirthday) {
2. This.studentid = StudentID;
3. This.studentname = Studentname;
4. This.studentsex = Studentsex;
5. This.studentbirthday = Studentbirthday;
6. }

2.1.3 Association Union

Union elements are used to handle "one-to-one" relationships. You need to specify the properties of the mapped Java entity class, the Javatype of the property (usually MyBatis you will recognize). The column name of the corresponding database table. If you want to overwrite the value of the returned result, you need to specify Typehandler.
Different situations need to tell mybatis how to load a union. MyBatis can be loaded in two ways:

1. Select: Executes an additional mapped SQL statement that returns a Java entity type. more flexible;
2. Resultsmap: Use a nested result map to process the result set of a join query and map it to a Java entity type.

For example, a class corresponds to a class teacher.
First of all, define the class teacher's attributes:

Java code

1. Private Teacherentity teacherentity;

2.1.3.1 using Select for federation

Example: Class entity class has the property of a class teacher, through the union in a class entity, at the same time mapping out the class teacher entity.

This allows you to directly reuse the query defined in the Teachermapper.xml file teacher The SELECT statement based on its ID. And do not need to modify the written SQL statement, only need to modify resultmap directly.

Classmapper.xml file part of the content:

XML code

2. <resultmap type= "classentity" id= "Classresultmap" >
4. <id property= "ClassID" column= "class_id"/>
6. <result property= "ClassName" column= "class_name"/>
8. <result property= "Classyear" column= "Class_year"/>
10. <association property= "teacherentity" column= "teacher_id" select= "Getteacher"/>
12. </resultMap>
16. <select id= "Getclassbyid" parametertype= "String" resultmap= "Classresultmap" >
18. SELECT * from Class_tbl CT
20. WHERE CT. class_id = #{classid};
22. </select>

Teachermapper.xml file part of the content:

XML code

2. <resultmap type= "teacherentity" id= "Teacherresultmap" >
4. <id property= "Teacherid" column= "teacher_id"/>
6. <result property= "TeacherName" column= "Teacher_name"/>
8. <result property= "Teachersex" column= "Teacher_sex"/>
10. <result property= "Teacherbirthday" column= "Teacher_birthday"/>
12. <result property= "workdate" column= "Work_date"/>
14. <result property= "Professional" column= "professional"/>
16. </resultMap>
20. <select id= "Getteacher" parametertype= "String" resultmap= "Teacherresultmap" >
22. SELECT *
24. From Teacher_tbl TT
26. WHERE TT. teacher_id = #{teacherid}
28. </select>

2.1.3.2 using Resultmap for federated

The same function as above, query class, while the head of the finder. To add resultmap (defined in the teacher XML file) to association, new write SQL (query class table left JOIN teacher table), no teacher select.

Modify the contents of the Classmapper.xml file section:

XML code

2. <resultmap type= "classentity" id= "Classresultmap" >
4. <id property= "ClassID" column= "class_id"/>
6. <result property= "ClassName" column= "class_name"/>
8. <result property= "Classyear" column= "Class_year"/>
10. <association property= "teacherentity" column= "teacher_id" resultmap= "Teacherresultmap"/>
12. </resultMap>
16. <select id= "Getclassandteacher" parametertype= "String" resultmap= "Classresultmap" >
18. SELECT *
20. From Class_tbl Ct left joins teacher_tbl TT on Ct. teacher_id = TT. teacher_id
22. WHERE CT. class_id = #{classid};
24. </select>

Please see the teacherresultmap in the Teachermapper.xml file section above.

2.1.4 Collection Gathering

The aggregation element is used to handle a "one-to-many" relationship. You need to specify the properties of the mapped Java entity class, the Javatype of the property (typically ArrayList), the type of the object in the list OfType (Java entity Class), and the column name of the corresponding database table;
Different situations need to tell mybatis how to load a cluster. MyBatis can be loaded in two ways:

1. Select: Executes an additional mapped SQL statement that returns a Java entity type. more flexible;
2. Resultsmap: Use a nested result map to process the result set of a join query and map it to a Java entity type.

For example, a class has multiple students.
First define the student list attribute in the class:

Java code

1. Private list<studententity> studentlist;

2.1.4.1 using Select to achieve aggregation

The usage and union are very similar, the difference is that this is a one-to-many, so the general mapping is a list. So here you need to define Javatype as ArrayList, and you need to define the type OfType of the object in the list, and the statement name of the select that must be set (note that the query student's SELECT statement condition must be a foreign key classid).

Classmapper.xml file part of the content:

XML code

2. <resultmap type= "classentity" id= "Classresultmap" >
4. <id property= "ClassID" column= "class_id"/>
6. <result property= "ClassName" column= "class_name"/>
8. <result property= "Classyear" column= "Class_year"/>
10. <association property= "teacherentity" column= "teacher_id" select= "Getteacher"/>
12. <collection property= "studentlist" column= "class_id" javatype= "ArrayList" oftype= "studententity" select= " Getstudentbyclassid "/>
14. </resultMap>
18. <select id= "Getclassbyid" parametertype= "String" resultmap= "Classresultmap" >
20. SELECT * from Class_tbl CT
22. WHERE CT. class_id = #{classid};
24. </select>

Studentmapper.xml file part of the content:

XML code

2. <!--Java Properties, the mapping between database table fields is defined--
4. <resultmap type= "studententity" id= "Studentresultmap" >
6. <id property= "StudentID" column= "student_id"/>
8. <result property= "Studentname" column= "Student_name"/>
10. <result property= "Studentsex" column= "Student_sex"/>
12. <result property= "Studentbirthday" column= "Student_birthday"/>
14. </resultMap>
18. <!--Query the student list, based on class ID--
20. <select id= "Getstudentbyclassid" parametertype= "String" resultmap= "Studentresultmap" >
22. <include refid= "Selectstudentall"/>
24. WHERE ST. class_id = #{classid}
26. </select>

2.1.4.2 using Resultmap for aggregation

With Resultmap, you need to rewrite a sql,left join student table.

XML code

2. <resultmap type= "classentity" id= "Classresultmap" >
4. <id property= "ClassID" column= "class_id"/>
6. <result property= "ClassName" column= "class_name"/>
8. <result property= "Classyear" column= "Class_year"/>
10. <association property= "teacherentity" column= "teacher_id" resultmap= "Teacherresultmap"/>
12. <collection property= "studentlist" column= "class_id" javatype= "ArrayList" oftype= "studententity" resultMap= " Studentresultmap "/>
14. </resultMap>
18. <select id= "getclassandteacherstudent" parametertype= "String" resultmap= "Classresultmap" >
20. SELECT *
22. From Class_tbl CT
24. Left JOIN student_tbl ST
26. On CT. class_id = ST. class_id
28. Left JOIN teacher_tbl TT
30. On CT. teacher_id = TT. teacher_id
32. WHERE CT. class_id = #{classid};
34. </select>

Please see the teacherresultmap in the Teachermapper.xml file section above. Studentresultmap See the Studentmapper.xml file section above for details.

2.1.5discriminator discriminator

Sometimes a single database query might return a number of different (but hopefully somewhat associative) result sets of data types. Discriminator elements are designed to handle this situation, as well as inheritance hierarchies that include classes. The discriminator is very easy to understand because it behaves much like a switch statement in the Java language.

The definition discriminator specifies the column and Javatype properties. The column is where MyBatis looks for comparison values. Javatype is the appropriate type to be used to ensure equivalence testing (although strings are useful in many cases).

The following example maps the ClassID property only when ClassID is 20000001.

XML code

2. <resultmap type= "liming.student.manager.data.model.StudentEntity" id= "Resultmap_studententity_discriminator" >
4. <id property= "StudentID" column= "student_id" javatype= "String" jdbctype= "VARCHAR"/>
6. <result property= "Studentname" column= "Student_name" javatype= "String" jdbctype= "VARCHAR"/>
8. <result property= "Studentsex" column= "Student_sex" javatype= "int" jdbctype= "INTEGER"/>
10. <result property= "Studentbirthday" column= "Student_birthday" javatype= "date" jdbctype= "date"/>
12. <result property= "Studentphoto" column= "Student_photo" javatype= "byte[" "jdbctype=" BLOB "typehandler=" Org.apache.ibatis.type.BlobTypeHandler "/>
14. <result property= "Placeid" column= "place_id" javatype= "String" jdbctype= "VARCHAR"/>
16. <discriminator column= "class_id" javatype= "String" jdbctype= "VARCHAR" >
18. <case value= "20000001" resulttype= "liming.student.manager.data.model.StudentEntity" >
20. <result property= "ClassId" column= "class_id" javatype= "String" jdbctype= "VARCHAR"/>
22. </case>
24. </discriminator>
26. </resultMap>

MyBatis Learning Two, SQL statement mapping file (1) resultmap