Detailed Java MyBatis framework SQL statement mapping part

  ClassMapper.xml

<resultMap type="ClassEntity" id="classResultMap"> 
  <id property="classID" column="CLASS_ID" /> 
  <result property="className" column="CLASS_NAME" /> 
  <result property="classYear" column="CLASS_YEAR" /> 
  <association property="teacherEntity" column="TEACHER_ID" select="getTeacher"/> 
</resultMap> 
 
<select id="getClassByID" parameterType="String" resultMap="classResultMap"> 
  SELECT * FROM CLASS_TBL CT 
  WHERE CT.CLASS_ID = #{classID}; 
</select> 

  TeacherMapper.xml

<resultMap type="TeacherEntity" id="teacherResultMap"> 
  <id property="teacherID" column="TEACHER_ID" /> 
  <result property="teacherName" column="TEACHER_NAME" /> 
  <result property="teacherSex" column="TEACHER_SEX" /> 
  <result property="teacherBirthday" column="TEACHER_BIRTHDAY"/> 
  <result property="workDate" column="WORK_DATE"/> 
  <result property="professional" column="PROFESSIONAL"/> 
</resultMap> 
 
<select id="getTeacher" parameterType="String" resultMap="teacherResultMap"> 
  SELECT * 
   FROM TEACHER_TBL TT 
   WHERE TT.TEACHER_ID = #{teacherID} 
</select> 

1.1.3.2 USES resultMap for federation
  and the above same function, query the class, at the same time query the head teacher. resultMap (as defined in xml of teacher) should be added to association, sql should be added to sql (refer to left join teacher table), select of teacher is not needed.

  modifies ClassMapper.xml:

<resultMap type="ClassEntity" id="classResultMap"> 
  <id property="classID" column="CLASS_ID" /> 
  <result property="className" column="CLASS_NAME" /> 
  <result property="classYear" column="CLASS_YEAR" /> 
  <association property="teacherEntity" column="TEACHER_ID" resultMap="teacherResultMap"/> 
</resultMap> 
 
<select id="getClassAndTeacher" parameterType="String" resultMap="classResultMap"> 
  SELECT * 
   FROM CLASS_TBL CT LEFT JOIN TEACHER_TBL TT ON CT.TEACHER_ID = TT.TEACHER_ID 
   WHERE CT.CLASS_ID = #{classID}; 
</select> 

For teacherResultMap, please see the TeacherMapper.xml file section above.

1.1.4 collection aggregation
The aggregation element is used to handle "1 to many" relationships. You need to specify the attributes of the mapped Java entity class, javaType of the attribute (1 is ArrayList); ofType (Java entity class); 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: an SQL statement that executes one other mapping returns one Java entity type. More flexible;
(2). resultsMap: a nested result map is used to process the query result set by join, mapped to Java entity type.
For example, a class has more than one student.
First, define the student list attribute in the class:
private List < StudentEntity > studentList;  
1.1.4.1 USES select for aggregation
The usage of   is very similar to that of union, except that this is 1 to many, so 1 maps to a list. So you need to define javaType as ArrayList, you also need to define the type of object in the list, ofType, and the statement name of select that you must set (note that the select statement condition for the query student here must be foreign key classID).
ClassMapper.xml

<resultMap type="ClassEntity" id="classResultMap"> 
  <id property="classID" column="CLASS_ID" /> 
  <result property="className" column="CLASS_NAME" /> 
  <result property="classYear" column="CLASS_YEAR" /> 
  <association property="teacherEntity" column="TEACHER_ID" select="getTeacher"/> 
  <collection property="studentList" column="CLASS_ID" javaType="ArrayList" ofType="StudentEntity" select="getStudentByClassID"/> 
</resultMap> 
 
<select id="getClassByID" parameterType="String" resultMap="classResultMap"> 
  SELECT * FROM CLASS_TBL CT 
  WHERE CT.CLASS_ID = #{classID}; 
</select> 

StudentMapper.xml

<!-- java Properties, mapping definitions between database table fields  --> 
<resultMap type="StudentEntity" id="studentResultMap"> 
  <id property="studentID" column="STUDENT_ID" /> 
  <result property="studentName" column="STUDENT_NAME" /> 
  <result property="studentSex" column="STUDENT_SEX" /> 
  <result property="studentBirthday" column="STUDENT_BIRTHDAY" /> 
</resultMap> 
 
<!--  Query students list According to the class id --> 
<select id="getStudentByClassID" parameterType="String" resultMap="studentResultMap"> 
  <include refid="selectStudentAll" /> 
  WHERE ST.CLASS_ID = #{classID} 
</select> 

1.1.4.2 aggregation using resultMap
  USES resultMap to rewrite one sql, left join student table.

<resultMap type="liming.student.manager.data.model.StudentEntity" id="studentResultMap"> 
  <id property="studentId"    column="STUDENT_ID" javaType="String" jdbcType="VARCHAR"/> 
  <result property="studentName"    column="STUDENT_NAME" javaType="String" jdbcType="VARCHAR"/> 
  <result property="studentSex"    column="STUDENT_SEX" javaType="int" jdbcType="INTEGER"/> 
  <result property="studentBirthday"  column="STUDENT_BIRTHDAY" javaType="Date" jdbcType="DATE"/> 
  <result property="studentPhoto" column="STUDENT_PHOTO" javaType="byte[]" jdbcType="BLOB" typeHandler="org.apache.ibatis.type.BlobTypeHandler" /> 
</resultMap> 

 
For teacherResultMap, see the TeacherMapper.xml file section above. See studentResultMap in the StudentMapper.xml file section above.
1.1.5discriminator discriminator
Sometimes a single database query may return many different (but hopefully related) result sets of data types. The discriminator element is designed to handle this situation, as well as the inheritance hierarchy that includes the class. The discriminator is easy to understand because it behaves much like the switch statement in the Java language.
The definition discriminator specifies the column and javaType attributes. The column is where MyBatis looks for the comparison value. JavaType is the appropriate type that needs to be used to ensure the equivalence test (although strings can be useful in many cases).
The following example maps the classId attribute when classId is 20000001.

<resultMap type="liming.student.manager.data.model.StudentEntity" id="resultMap_studentEntity_discriminator"> 
  <id property="studentId"    column="STUDENT_ID" javaType="String" jdbcType="VARCHAR"/> 
  <result property="studentName"    column="STUDENT_NAME" javaType="String" jdbcType="VARCHAR"/> 
  <result property="studentSex"    column="STUDENT_SEX" javaType="int" jdbcType="INTEGER"/> 
  <result property="studentBirthday"  column="STUDENT_BIRTHDAY" javaType="Date" jdbcType="DATE"/> 
  <result property="studentPhoto" column="STUDENT_PHOTO" javaType="byte[]" jdbcType="BLOB" typeHandler="org.apache.ibatis.type.BlobTypeHandler" /> 
  <result property="placeId"      column="PLACE_ID" javaType="String" jdbcType="VARCHAR"/> 
  <discriminator column="CLASS_ID" javaType="String" jdbcType="VARCHAR"> 
    <case value="20000001" resultType="liming.student.manager.data.model.StudentEntity" > 
      <result property="classId" column="CLASS_ID" javaType="String" jdbcType="VARCHAR"/> 
    </case> 
  </discriminator> 
</resultMap> 

2. Add, delete, change, check, parameters, cache

2.1 select
1 select element is very simple. Such as:

<!--  Query students according to id --> 
<select id="getStudent" parameterType="String" resultMap="studentResultMap"> 
  SELECT ST.STUDENT_ID, 
        ST.STUDENT_NAME, 
        ST.STUDENT_SEX, 
        ST.STUDENT_BIRTHDAY, 
        ST.CLASS_ID 
     FROM STUDENT_TBL ST 
     WHERE ST.STUDENT_ID = #{studentID} 
</select> 

This statement is called 'getStudent', has an String parameter, and returns an object of type StudentEntity.
Note that the parameter is identified as: #{studentID}.
 
select statement properties configuration details:

2.2 insert
  1 simple insert statement:

<resultMap type="liming.student.manager.data.model.StudentEntity" id="studentResultMap"> 
  <id property="studentId"    column="STUDENT_ID" javaType="String" jdbcType="VARCHAR"/> 
  <result property="studentName"    column="STUDENT_NAME" javaType="String" jdbcType="VARCHAR"/> 
  <result property="studentSex"    column="STUDENT_SEX" javaType="int" jdbcType="INTEGER"/> 
  <result property="studentBirthday"  column="STUDENT_BIRTHDAY" javaType="Date" jdbcType="DATE"/> 
  <result property="studentPhoto" column="STUDENT_PHOTO" javaType="byte[]" jdbcType="BLOB" typeHandler="org.apache.ibatis.type.BlobTypeHandler" /> 
</resultMap> 

insert can be used to automatically generate the primary key policy supported by the database, set useGeneratedKeys= "true", then set keyProperty to the corresponding column, and you are done. For example, StudentEntity above USES auto-generated to generate the primary key for the id column. The following example USES mysql database nextval('student') as a custom function to generate 1 key.

<resultMap type="liming.student.manager.data.model.StudentEntity" id="studentResultMap"> 
  <id property="studentId"    column="STUDENT_ID" javaType="String" jdbcType="VARCHAR"/> 
  <result property="studentName"    column="STUDENT_NAME" javaType="String" jdbcType="VARCHAR"/> 
  <result property="studentSex"    column="STUDENT_SEX" javaType="int" jdbcType="INTEGER"/> 
  <result property="studentBirthday"  column="STUDENT_BIRTHDAY" javaType="Date" jdbcType="DATE"/> 
  <result property="studentPhoto" column="STUDENT_PHOTO" javaType="byte[]" jdbcType="BLOB" typeHandler="org.apache.ibatis.type.BlobTypeHandler" /> 
</resultMap> 

2.3 update, delete
1 simple update:

<resultMap type="liming.student.manager.data.model.StudentEntity" id="studentResultMap"> 
  <id property="studentId"    column="STUDENT_ID" javaType="String" jdbcType="VARCHAR"/> 
  <result property="studentName"    column="STUDENT_NAME" javaType="String" jdbcType="VARCHAR"/> 
  <result property="studentSex"    column="STUDENT_SEX" javaType="int" jdbcType="INTEGER"/> 
  <result property="studentBirthday"  column="STUDENT_BIRTHDAY" javaType="Date" jdbcType="DATE"/> 
  <result property="studentPhoto" column="STUDENT_PHOTO" javaType="byte[]" jdbcType="BLOB" typeHandler="org.apache.ibatis.type.BlobTypeHandler" /> 
</resultMap> 

  1 simple delete:

<resultMap type="liming.student.manager.data.model.StudentEntity" id="studentResultMap"> 
  <id property="studentId"    column="STUDENT_ID" javaType="String" jdbcType="VARCHAR"/> 
  <result property="studentName"    column="STUDENT_NAME" javaType="String" jdbcType="VARCHAR"/> 
  <result property="studentSex"    column="STUDENT_SEX" javaType="int" jdbcType="INTEGER"/> 
  <result property="studentBirthday"  column="STUDENT_BIRTHDAY" javaType="Date" jdbcType="DATE"/> 
  <result property="studentPhoto" column="STUDENT_PHOTO" javaType="byte[]" jdbcType="BLOB" typeHandler="org.apache.ibatis.type.BlobTypeHandler" /> 
</resultMap> 

 
update, delete statement property configuration details:

<!--  reuse sql statements   The query student Table all fields  --> 
<sql id="selectStudentAll"> 
    SELECT ST.STUDENT_ID, 
          ST.STUDENT_NAME, 
          ST.STUDENT_SEX, 
          ST.STUDENT_BIRTHDAY, 
          ST.CLASS_ID 
       FROM STUDENT_TBL ST 
</sql> 

In this way, the direct reference can be used in select statement. Change the above select statement to:

<!--  Query students according to id --> 
<select id="getStudent" parameterType="String" resultMap="studentResultMap"> 
  <include refid="selectStudentAll"/> 
      WHERE ST.STUDENT_ID = #{studentID}  
</select> 

  2.5 parameters
                                     
            basic data types, String, int, date, etc.
              but with the basic data type, you can only provide one parameter, so you need to use Java entity class or Map type as the parameter type. Its properties can be obtained directly from #{}.
2.5.1 basic type parameters
  retrieves the list of students according to the time of enrollment:

<resultMap type="liming.student.manager.data.model.StudentEntity" id="studentResultMap"> 
  <id property="studentId"    column="STUDENT_ID" javaType="String" jdbcType="VARCHAR"/> 
  <result property="studentName"    column="STUDENT_NAME" javaType="String" jdbcType="VARCHAR"/> 
  <result property="studentSex"    column="STUDENT_SEX" javaType="int" jdbcType="INTEGER"/> 
  <result property="studentBirthday"  column="STUDENT_BIRTHDAY" javaType="Date" jdbcType="DATE"/> 
  <result property="studentPhoto" column="STUDENT_PHOTO" javaType="byte[]" jdbcType="BLOB" typeHandler="org.apache.ibatis.type.BlobTypeHandler" /> 
</resultMap> 

List<StudentEntity> studentList = studentMapper.getStudentListByClassYear(StringUtil.parse("2007-9-1")); 
for (StudentEntity entityTemp : studentList) { 
  System.out.println(entityTemp.toString()); 
} 

2.5.2 Java entity type parameter
  retrieves a list of students by name and gender. Use entity class as parameter:

<!--  Query students list . like The name, = Gender, parameters entity type  --> 
<select id="getStudentListWhereEntity" parameterType="StudentEntity" resultMap="studentResultMap"> 
  SELECT * from STUDENT_TBL ST 
    WHERE ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%') 
     AND ST.STUDENT_SEX = #{studentSex} 
</select> 

StudentEntity entity = new StudentEntity(); 
entity.setStudentName(" li "); 
entity.setStudentSex(" male "); 
List<StudentEntity> studentList = studentMapper.getStudentListWhereEntity(entity); 
for (StudentEntity entityTemp : studentList) { 
  System.out.println(entityTemp.toString()); 
} 

2.5.3 Map parameters
retrieves student lists by name and gender. Use Map as parameter:

<!--  Query students list . = Gender, parameters map type  --> 
<select id="getStudentListWhereMap" parameterType="Map" resultMap="studentResultMap"> 
  SELECT * from STUDENT_TBL ST 
   WHERE ST.STUDENT_SEX = #{sex} 
     AND ST.STUDENT_SEX = #{sex} 
</select> 

Map<String, String> map = new HashMap<String, String>(); 
map.put("sex", " female "); 
map.put("name", " li "); 
List<StudentEntity> studentList = studentMapper.getStudentListWhereMap(map); 
for (StudentEntity entityTemp : studentList) { 
  System.out.println(entityTemp.toString()); 
} 

Multi-parameter implementation of 2.5.4
If wants to pass in more than one parameter, you need to add the @Param annotation to the interface's parameters. An example is given:
Interface writing:

public List<StudentEntity> getStudentListWhereParam(@Param(value = "name") String name, @Param(value = "sex") String sex, @Param(value = "birthday") Date birthdar, @Param(value = "classEntity") ClassEntity classEntity); 

 
SQL writing:

<!--  Query students list . like The name, = Gender, = Birthday, = Class, multi-parameter mode  --> 
<select id="getStudentListWhereParam" resultMap="studentResultMap"> 
  SELECT * from STUDENT_TBL ST 
  <where> 
    <if test="name!=null and name!='' "> 
      ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{name}),'%') 
    </if> 
    <if test="sex!= null and sex!= '' "> 
      AND ST.STUDENT_SEX = #{sex} 
    </if> 
    <if test="birthday!=null"> 
      AND ST.STUDENT_BIRTHDAY = #{birthday} 
    </if> 
    <if test="classEntity!=null and classEntity.classID !=null and classEntity.classID!='' "> 
      AND ST.CLASS_ID = #{classEntity.classID} 
    </if> 
  </where> 
</select>

Make enquiries:

List<StudentEntity> studentList = studentMapper.getStudentListWhereParam("", "",StringUtil.parse("1985-05-28"), classMapper.getClassByID("20000002")); 
for (StudentEntity entityTemp : studentList) { 
  System.out.println(entityTemp.toString()); 
} 

2.5.5 string substitution method
By default, using the #{} syntax causes MyBatis to generate PreparedStatement properties and to use PreparedStatement parameters (=?). To set the value safely. Try to make these fast and safe, and use them often. But sometimes you might want to simply substitute the unchanged string into the SQL statement. For example, for ORDER BY, you might use ORDER BY ${columnName} but MyBatis does not modify or circumvent this string.
Note: it is not safe to receive and apply one user's input to an unchanged statement in this way. This allows the user to insert broken code, so either ask the field not to allow the customer to enter it, or you check its validity directly.
2.6 cache cache

<cache eviction="FIFO" flushInterval="60000" size="512" readOnly="true"> 
</cache>