Mybatis learning with pit records

Mybatis ResultMap Advanced mapping

Application scenario: If the column name of an SQL query is inconsistent with the attribute name of a POJO, you can use a resultMap to map the column name to the attribute name of a POJO. (If the returned value is a data type such as Interger, use a resultType.)

One to one

Resultmap with < association >

Application scenario: For example, if there is a field orderDetail in order, it is one-to-one and mapped to column in Association, that is, the field in OrderDetail

More than a pair of

Resultmap with < collection >

If the POJO class user has a book attribute, then the user and book belong to a one-to-many relationship.

Tips: Here userBookResultMap inherits from the previous UserResultMap

Many to many

Collection and Association are used together in the same way as above. Note the order in which they are used

Some advanced uses of mybatis:

Dynamic SQL

To use dynamic SQL, write the SQL first and replace it with the corresponding tags. Use <trim> tags to remove AND, or, and, to avoid bugs

(1) < SQL > fragment: if there are many fields that need to be queried, you can write the fields in the tag and use the ID to identify them. When using the tag, you only need to <include>. Such as

(2) <trim> tag:

Use:

• < trim > : prefix = “” : prefix
• Prefixoverride = “AND | OR” : get rid of the first AND OR OR
• Suffixoverride =”,”
• Suffix = “” suffix

Application Scenarios: In the process of dynamic SQL use, need to determine whether the field is empty, then there will be SQL statement error, such as more and,or or lead to some local statements are incorrect, then there will be SQL execution error or can not find the data, need to use the label for filtering (in actual use to pay special attention to, If it takes too long to find a bug…)

(3) where-if statement, set-if statement

select * from user 
        <where>
                <if    test="username ! = null ">
                    username = #{username}
                </if>
        </where>
Copy the code

Note: Generic < WHERE >, this tag will determine if there is a value in the return = statement, insert a WHERE if there is, and if the return value begins with and or or it will be removed. Similarly, the set-if statement does the same thing. These two tags are usually replaced with <trim>

(4) Choose (when, otherise)

Similar to the Switch statement in Java

Use:

<choose>
        <when test="username ! = null and username ! = ""> // Use single quotes without Spaces </when> <otherwise>.... </otherwise> </choose>Copy the code

(5) foreach

<foreach  collection="ids"   item="id"   open="and ("  close=")"  separator="or" >
</foreach>
Copy the code

Description:

• Collection: Specifies the collection properties in the input object
• Item: The object generated by each iteration
• Open: concatenation string to start traversal
• Close: concatenated string at the end
• Separator: Iterates over the string to be concatenated between objects

(6) Bind statement

Using the bind element we can pre-define variables that can then be used in a query statement, such as

<bind name=”un” value=”username + ‘%’ “></bind>

SELECT* FROM user2 WHERE user_name LIKE #{un}

inheritance

Resultmap uses inheritance to reduce a lot of code and is straightforward, as is the corresponding POJO class inheritance

Mybatis the three musketeers

(1) Mybatis – Generater: used to quickly generate POJO, DAO, mapper and mapper.xml

(2) Mybatis – Plugin (need to install) :

1. The DAO layer is associated with the mapper.xml file to quickly jump to the corresponding line of code
2. Automatic completion is supported when editing XML files
3. Check for ID conflicts or attribute conflicts

(3) Mybatis -pagehalper: a dynamic paging and sorting plug-in, the principle is through spring AOP to achieve, this plug-in can execute SQL, the relevant data again

Mybatis level 2 cache

The system has enabled level 1 cache by default. When we obtain a SqlSession object, if we call the same method in SqlSession to query the same data, then the second query will not go to the database.

Manually enable cache: Configure the <cache> node in mapper. XML

<cache eviction="LRU" size=1024 readOnly=true />

The replace into grammar

Replace INTO is similar to insert, except that replace INTO first attempts to insert data into a table. If the table already contains row data (based on the primary key or unique index), replace into first deletes the row data and then inserts new data. Otherwise, insert new data directly.

Note that the table into which data is inserted must have a primary key or unique index! Otherwise, replace into inserts data directly, which results in duplicate data in the table.

Mybatsi annotation mode

Such as:

One-to-one or one-to-many properties @many, @one

@Select("select * from group_info where id = #{groupId}"@results ({// Declare a Result set, using @result (property =) if the Result is not a Java primitive data type"id", column ="id"), //property is the property in the entity class, column is the field in the database @result (property ="authorities", column ="id"JavaType = List. Class, many = @many (select ="com.baicizhan.dao.user.GroupAuthMapper.getGroupAuthorities",
fetchType = FetchType.EAGER))
})Copy the code

@Insert("insert into user_session(xdid, openid, token, wx_session) values(#{xdid}, #{openid}, #{token}, #{wxSession})")
@SelectKey(statement="SELECT 1354427", keyProperty="id", before=false, resultType=int.class)Copy the code

@Select("select * from group_info where id = #{groupId}")
@Results({
@Result(property ="id", column ="id"),
@Result(property ="authorities", column ="id",
javaType = List.class,many =@Many(select ="com.baicizhan.dao.user.GroupAuthMapper.getGroupAuthorities",
fetchType = FetchType.EAGER))
})Copy the code