Official reference: XML mapper

The real power of MyBatis lies in its statement mapping, which is its magic. Because of its exceptional power, the MAPper’s XML file is relatively simple. If you compare it to JDBC code with the same functionality, you’ll immediately see that nearly 95% of the code is saved. MyBatis aims to reduce usage costs and allow users to focus more on SQL code
The SQL mapping file has only a few top-level elements (listed in the order they should be defined) :

Cache - The cache configuration for this namespace. Cache-ref - References the cache configuration of other namespaces. ResultMap - describes how to load objects from a database result set, and is the most complex and powerful element. SQL - reusable block of statements that can be referenced by other statements. Insert - Mapping insert statements. Update - Mapping update statement. Delete - Mapping delete statement. Select - Mapping query statement.Copy the code

Select element attributes

attribute	describe
id	A unique identifier in a namespace that can be referenced
parameterType	The class-fully qualified name or alias of the argument to this statement
resultType	The fully qualified name or alias of the class that is expected to return a result from this statement. Note that if a collection is returned, it should be set to the type contained in the collection, not the type of the collection itself. Only one resultType and resultMap can be used simultaneously.
resultMap	Named reference to an external resultMap.
flushCache	Setting this to true causes the local and secondary caches to be cleared whenever a statement is called. Default: false
useCache	Setting it to true will cause the results of this statement to be cached by the second level cache. Default: true for select elements.
timeout	This setting is the number of seconds the driver waits for the database to return the result of the request before throwing an exception.
fetchSize	This is a recommended value for drivers to try to make the number of rows returned per batch equal to this value
statementType	The value can be STATEMENT, PREPARED or CALLABLE.
resultSetType	FORWARD_ONLY, SCROLL_SENSITIVE, SCROLL_INSENSITIVE, or DEFAULT (equivalent to unset),
databaseId	If databaseIdProvider is configured, MyBatis will load all statements that do not have a databaseId or match the current databaseId. If both tagged and untagged statements are present, untagged statements are ignored.
resultOrdered	This setting applies only to nested result SELECT statements: if true, nested result sets or groups are assumed to be included, and no references to previous result sets are generated when a primary result row is returned. This keeps memory from running out when retrieving nested result sets. Default value: false.
resultSets	This setting only works with multiple result sets.

Insert, Update, Delete element attributes

For inserts and updates only

attribute	describe
useGeneratedKeys	MyBatis uses JDBC’s getGeneratedKeys method to retrieve primary keys generated internally by the database. Default: false.
keyProperty	Specify a property that uniquely identifies the object. MyBatis sets its value using either the return value of getGeneratedKeys or the selectKey child of the INSERT statement. Default: unset
keyColumn	Set the name of the column in the table where the generated key is generated. In some databases (like PostgreSQL), this is mandatory when the primary key column is not the first column in the table.

Insert a user, but not containUsertheid

<insert id="insertUser" parameterType="user" useGeneratedKeys="true" keyProperty="id">
    insert into user (name, address)
    values (#{name}, #{address});
</insert>
Copy the code

  @Test
  public void insertUserTest(a) {
    try (SqlSession sqlSession = Utils.getSqlSession()) {
      UserMapper mapper = sqlSession.getMapper(UserMapper.class);
      int res = mapper.insertUser(new User(null."libai"."India"));
      if (res > 0)
        System.out.println("Insert successful");
      else
        System.out.println("Insert failed");
      sqlSession.commit(); // Commit transaction, must!}}Copy the code

Insert multiple elements (foreach)

int insertUsers(List<User> users);
Copy the code

<insert id="insertUsers" parameterType="list">
insert into user (name, address) values
<foreach collection="list" item="item" separator=",">
    (#{item.name}, #{item.address})
</foreach>
</insert>
Copy the code

Collection =”list”, indicating that the input parameter type is list; Item =”item” is an alias for traversing each element; Separator =”,” is the separator in the following sentences

  @Test
  public void insertUsersTest(a) {
    try (SqlSession sqlSession = Utils.getSqlSession()) {
      UserMapper mapper = sqlSession.getMapper(UserMapper.class);
      List<User> users = new ArrayList<>();
      users.add(new User(null."Li bai"."China"));
      users.add(new User(null."Wang"."China"));
      users.add(new User(null."Su shi"."China"));
      int res = mapper.insertUsers(users);
      if (res > 0)
        System.out.println("Insert successful");
      else
        System.out.println("Insert failed");
      sqlSession.commit(); // Commit transaction, must!}}Copy the code

Reuse SQL snippets

This element can be used to define reusable SNIPpets of SQL code for use in other statements
Parameters can be determined statically (at load time), and different parameter values can be defined in different include elements

<sql id="allParam">
    select *
    from student
</sql>

<select id="getStudentById" resultType="student" parameterType="_int">
    <include refid="allParam"/>
    where id = ${id};
</select>

<select id="getStudentByName" parameterType="string" resultType="student">
    <include refid="allParam"/>
    where name = ${name};
</select>
Copy the code

<sql id="userColumns"> ${alias}.id,${alias}.username,${alias}.password </sql>
<select id="selectUsers" resultType="map">
  select
    <include refid="userColumns"><property name="alias" value="t1"/></include>.<include refid="userColumns"><property name="alias" value="t2"/></include>
  from some_table t1
    cross join some_table t2
</select>
Copy the code

Five, the parameter

# {}andThe ${}The difference between
- # {}Is a parameter placeholder?, that is, SQL precompilation;The ${}Is string substitution, or SQL concatenation
- # {}Security,The ${}Prone to SQL injection problems.
Enter the field name as a parameter as well

User selectUserByParameter(Map<String, String> map);
Copy the code

<select id="selectUserByParameter" parameterType="map" resultType="user">
    select id, name, address
    from user
    where ${type} = #{content};
</select>
Copy the code

  @Test
  public void selectUserByParameterTest(a) {
    try (SqlSession sqlSession = Utils.getSqlSession()) {
      UserMapper mapper = sqlSession.getMapper(UserMapper.class);
      Map<String, String> map = new HashMap<>();
      //map.put("type", "name");
      //map. Put ("content", "dufu ");
      map.put("type"."id");
      map.put("content"."3"); User user = mapper.selectUserByParameter(map); System.out.println(user); }}Copy the code

This way, just for fun, it’s not safe. Again, SQL injection.

6. ResultMap

When a Java Bean and a table have different field names, there are two solutions: alias and result mapping
Java Bean

// Use Lombok to generate those fixed methods directly
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Person {
  int id;
  String lastName;
  String firstName;
}
Copy the code

Tables of a database

CREATE TABLE person(
        id 			INT 		PRIMARY KEY AUTO_INCREMENT,
        last_name 	VARCHAR(20)	NOT NULL,
        first_name 	VARCHAR(20)	NOT NULL
)ENGINE=INNODB ;
Copy the code

Take the alias

select id, last_name AS lastName, first_name AS firstName
from person;
Copy the code

Results the mapping

<! -- Change to the corresponding interface -->
<mapper namespace="com.du.mybatis.dao.PersonMapper">
    <! -- id is the unique identifier of this resultMap, which is used for subsequent references -->
    <! -- type specifies the class, you can use the alias -->
    <resultMap id="personResultMap" type="person">
        <id property="id" column="id"/>
        <result property="lastName" column="last_name"/>
        <result property="firstName" column="first_name"/>
    </resultMap>
    
    <! -- resultMap is used to select the specified resultMap -->
    <select id="getPersons" resultMap="personResultMap">
        select id, last_name, first_name
        from person;
    </select>
</mapper>
Copy the code

Individual attributes of the resulting map

attribute	describe
constructor	Used to inject results into the constructor when the class is instantiated
id	An ID result; Marking the result as an ID can help improve overall performance
result	Ordinary results injected into fields or JavaBean properties
association	An association of a complex type; Many results will be wrapped in this type
collection	A collection of complex types
discriminator	Use the result value to determine which resultMap to use
case	Result mapping based on certain values

id&result
- The ID and result elements both map the value of a column to an attribute or field of a simple data type (String, int, double, Date, and so on).
- The only difference between the two is that the attribute corresponding to the ID element is marked as an object identifier, which is used when comparing object instances. This can improve overall performance, especially for caching and nested result mapping (also known as join mapping)

Seven, associated

7.1 Nested SELECT Query

Nested Select query: Load the desired complex type by executing another SQL mapping statement (in simple terms, two Select statements nested)

@Data
public class Teacher {
  private String name;
  private int id;
}
Copy the code

@Data
public class Student {
  private int id;
  private Teacher teacher;
  private String name;
}
Copy the code

The database only stores the id of the teacher, but does not store the teacher. So you need to query the teacher by id and return it to the Student

<! Select * from teacher where id = 1;
<select id="getTeacher" resultType="teacher" parameterType="_int">
    select *
    from teacher
    where id = #{id};
</select>
Copy the code

First, a simple SELECT statement is used to query the specified teacher by ID

<! - getStudents resultMap -- -- >
<resultMap id="studentResultMap" type="student">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <! -- column as input, go to getTeacher -->
    <association property="teacher" column="tid" javaType="teacher" select="getTeacher"/>
</resultMap>
Copy the code

In the resultMap, select association is implemented through association. Property is the property name of the Java bean, column is the column name in the database table, javaType is the Java bean of the association mapping, and SELECT is the interface used to query the Java bean

<select id="getStudents" resultMap="studentResultMap">
    select *
    from student;
</select>
Copy the code

Finally, a resultMap is used as the parameter

7.2 Nested result mapping

Nested result mapping: Use nested result mapping to handle duplicate subsets of join results (essentially, two table joins)

<resultMap id="studentResultMap" type="student">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <association property="teacher" javaType="teacher">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
    </association>
</resultMap>
Copy the code

Through the result mapping, the result outputs a Student class. Inside through association, nested query Teacher

<select id="getStudents" resultMap="studentResultMap">
    select *
    from student, teacher
    where student.tid = teacher.id;
</select>
Copy the code

Eight, collections,

Collections, that is, one-to-many, where a Java bean has a property that consists of a collection of other Java beans. It needs to be queried through SQL. The basic idea is the same as above, there are two basic methods: embedded sub-query, linked table query
java bean

@Data
public class Student {
  private int id;
  private String name;
  private int tid;  // The corresponding Teacher id
}
Copy the code

@Data
public class Teacher {
  private String name;
  private int id;
  private List<Student> students;   / / collection
}
Copy the code

8.1 Nested Select queries for collections

<select id="selectStudentsByTId" resultType="Student" parameterType="_int">
    select *
    from student
    where tid = #{tid};
</select>

<select id="selectTeacherById" resultMap="selectTeacherByIdResultMap" parameterType="_int">
    select *
    from teacher
    where id = #{id};
</select>

<resultMap id="selectTeacherByIdResultMap" type="Teacher">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <collection property="students" select="selectStudentsByTId" javaType="ArrayList" ofType="student" column="id"/>
</resultMap>
Copy the code

Basic idea: Find out all the students whose TID is TID, find out the information of the teacher with the specified ID, and map the result through the resultMap.
resultMapIs used to parse SQL to find the result, one – to – one relationship can be directly not written. For collections, you need to usecollectionProperties,propertyIs the property of the Java bean,columnFor the name of a column found in SQL,javaTypeIs the type of the property,ofTypeIs the generic type of the collection,selectIs the query statement for this property.

8.2 Nested result mapping of collections

<select id="selectTeacherById" resultMap="selectTeacherByIdResultMap" parameterType="_int">
select s.id sid, s.NAME sname, t.id t_id, t.name tname
from teacher t,
     student s
where t.id = s.tid
  and t.id = #{id};
</select>

<resultMap id="selectTeacherByIdResultMap" type="teacher">
<result column="tid" property="id"/>
<result column="tname" property="name"/>
<collection property="students" ofType="student">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <result property="tid" column="t_id"/>
</collection>
</resultMap>
Copy the code

The basic idea is: after the result is queried through a linked table, the result is processed. The one-to-one properties can be ignored, not one-to-one, just onecollectionReceive the processing result.collectionIt is also usedresultReceive the results.

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

XML mapper for MyBatis

Select element attributes

Insert, Update, Delete element attributes

Insert multiple elements (foreach)

Reuse SQL snippets

Five, the parameter

6. ResultMap

Seven, associated

7.1 Nested SELECT Query

7.2 Nested result mapping

Eight, collections,

8.1 Nested Select queries for collections

8.2 Nested result mapping of collections

XML mapper for MyBatis

Select element attributes

Insert, Update, Delete element attributes

Insert multiple elements (foreach)

Reuse SQL snippets

Five, the parameter

6. ResultMap

Seven, associated

7.1 Nested SELECT Query

7.2 Nested result mapping

Eight, collections,

8.1 Nested Select queries for collections

8.2 Nested result mapping of collections

Related Posts

August helm3 install | more challenges

The nearest common ancestor of binary search tree (Recursive + Iterative, detailed diagram, Java implementation)

InnoDB lock type and state query