3. XML mapper

• Create tables: user, shopping cart, goods

DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods` (
  `good_id` int(11) NOT NULL AUTO_INCREMENT,
  `good_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`good_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of goods
-- ----------------------------
BEGIN;
INSERT INTO `goods` VALUES (1, 'jacket');
INSERT INTO `goods` VALUES (2, 'trousers');
INSERT INTO `goods` VALUES (3, 'shoes');
COMMIT;

-- ----------------------------
-- Table structure for shoopingcart
-- ----------------------------
DROP TABLE IF EXISTS `shoopingcart`;
CREATE TABLE `shoopingcart` (
  `cart_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `good_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`cart_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of shoopingcart
-- ----------------------------
BEGIN;
INSERT INTO `shoopingcart` VALUES (1, 1, 1);
INSERT INTO `shoopingcart` VALUES (2, 1, 2);
INSERT INTO `shoopingcart` VALUES (3, 2, 1);
INSERT INTO `shoopingcart` VALUES (4, 3, 2);
INSERT INTO `shoopingcart` VALUES (5, 3, 3);
COMMIT;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `pwd` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of user
-- ----------------------------
BEGIN;
INSERT INTO `user` VALUES (1, 'user1', 'pwd1');
INSERT INTO `user` VALUES (2, 'user2', 'pwd2');
INSERT INTO `user` VALUES (3, 'user3', 'pwd3');
COMMIT;

DROP TABLE IF EXISTS `wallet`;
CREATE TABLE `wallet` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `balance` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of wallet
-- ----------------------------
BEGIN;
INSERT INTO `wallet` VALUES (1, 1, 100.00);
INSERT INTO `wallet` VALUES (2, 2, 200.00);
INSERT INTO `wallet` VALUES (2, 3, 300.00);
COMMIT;

• Entity class:

package com.szm.pojo;

@Data
public class Good {
    private int goodId;
    private String goodName;
}

package com.szm.pojo;

import lombok.Data;

@Data
public class ShoppingCart {

    private int cartId;
    private int userId;
    private int goodId;

}

package com.szm.pojo;

import lombok.Data;


@Data
public class User {
    private int userId;
    private String userName;
    private String userPwd;

}

package com.szm.pojo;

import lombok.Data;

import java.math.BigDecimal;

@Data
public class Wallet {
    private int id;
    private BigDecimal balance;
}

3.1,select

3.1.1. Single table query

• dao interface

package com.szm.mapper;

import com.szm.pojo.User;

import java.util.List;


public interface UserMapper {

    List<User> selectUserByName(String name);

}

• Mapping file

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.szm.mapper.UserMapper">
    <select id="selectUserByName" resultType="user">
        select id as userId,name as userName,pwd as userPwd from user where name =#{name};
    </select>
</mapper>

3.1.2. select tag attribute list

attribute	describe
id	A unique identifier in the namespace that can be used to reference this statement.
parameterType	The fully qualified name or alias of the class that will be passed into the parameters of this statement. This property is optional because MyBatis can infer the parameters of the specific incoming statement through the type handler. The default value is unset.
Parametermap (obsolete)	The property used to reference an external parameterMap is currently obsolete. Please use inline parameter mapping and parameterType property.
resultType	The fully qualified name or alias of the class that you expect to return results 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 can be used between resultType and resultMap at the same time.
resultMap	A named reference to an external resultMap. Result mapping is the most powerful feature of MyBatis. If you understand it thoroughly, many complex mapping problems can be solved. Only one can be used between resultType and resultMap at the same time.
flushCache	When it is set to true, as long as the statement is called, the local cache and L2 cache will be emptied. The default value is false.
useCache	Setting it to true will cause the results of this statement to be cached by the secondary cache. The default value is true for the select element.
timeout	This setting is the number of seconds the driver waits for the database to return the requested result before throwing an exception. The default value is unset (database driven).
fetchSize	This is a recommended value for the driver. Try to make the number of result lines returned by the driver in batch each time equal to this setting value. The default value is unset (drive dependent).
statementType	Optional status, PREPARED or CALLABLE. This will make MyBatis use statement, PreparedStatement or CallableStatement respectively. The default value is PREPARED.
resultSetType	FORWARD_ONLY，SCROLL_ SENSITIVE, SCROLL_ Either intrinsic or DEFAULT (equivalent to unset). The DEFAULT value is unset (dependent on database driver).
databaseId	If the database vendor ID (databaseIdProvider) is configured, MyBatis will load all statements without databaseId or matching the current databaseId; If there are statements with and without, the statements without are ignored.
resultOrdered	This setting is only applicable to nested result select statements: if it is true, it will be assumed that nested result sets or groups are included. When a main result row is returned, there will be no reference to the previous result set. This makes it possible to get nested result sets without running out of memory. Default: false.

3.1.3 parameterType attribute

parameterType is used to declare the input parameters required by this statement. Generally, it does not need to be explicitly defined in xml. mybatis will process them automatically. Several cases of parameter types are listed below

• There are two ways to pass in multiple basic types of parameters:
  • First define a dao interface

package com.szm.mapper;

import com.szm.pojo.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;


public interface UserMapper {
    //Pass in two parameters 
    List<User> selectUser( String userName, String pwd);

}

• The first method is to use * * #{arg0},#{arg1}, or #{param1}, #{param2} * * in the mapping file. It can be used according to the parameter order defined by the dao method** Before mybatis 3.4.2, you can directly use #{0}, #{1}, and then you can only use the two mentioned above**

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.szm.mapper.UserMapper">
    <select id="selectUser" resultType="user" >
        select id as userId,name as userName,pwd as userPwd from user where name =#{arg0} and pwd = #{arg1};
    </select>

</mapper>

• Second, you need to use * * @ Param("parameter alias") to specify the parameter name * *, which can be directly used by the mapping file

package com.szm.mapper;

import com.szm.pojo.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;


public interface UserMapper {

    List<User> selectUser(@Param("name") String userName,@Param("p") String pwd);

}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.szm.mapper.UserMapper">
    <select id="selectUserByName" resultType="user" >
        select id as userId,name as userName,pwd as userPwd from user where name =#{name} and pwd = #{p};
    </select>
</mapper>

• If the passed in parameter contains an object, you need to use the @ Param annotation to specify the name of the parameter

package com.szm.mapper;

import com.szm.pojo.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface UserMapper {

    List<User> selectUserByName(@Param("queryUser")User user);

}

The mapping file is occupied by * * #{name. Parameter name} * *

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.szm.mapper.UserMapper">
    <select id="selectUserByName" resultType="user" >
        select id as userId,name as userName,pwd as userPwd from user where name =#{queryUser.name} and pwd = #{queryUser.pwd};
    </select>
</mapper>

3.1.4,resultType

• resultType official explanation:

The fully qualified name or alias of the class that you expect to return results 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 can be used between resultType and resultMap at the same time

• When using the select tag to query, use resultType to define the return type. If the query returns the built-in type of mybatis, directly use the built-in type alias of java or use the fully qualified path of the type (for the built-in type, see my blog mybatis (II) detailed description of xml configuration method).

• If the returned result is a javaBean object, mybatis corresponds according to the column name of the query result and the javaBean attribute name, so the attribute name of the javaBean object should be exactly the same as the column name of the sql result

• It should be noted that if a collection object is returned, the resultType only needs to define the generic type of the collection,

  Here are some examples to illustrate:

  • If you return the mybatis built-in type:

package com.szm.mapper;

import java.util.List;
import java.util.Map;

public interface UserMapper {
    // Return map type
    List<Map<String,Object>> selectUser(String userName,String pwd);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.szm.mapper.UserMapper">
    <!--Built in types use built-in aliases directly, Map The built-in alias for is map -->
    <select id="selectUser" resultType="map" >
        select id as userId,name as userName,pwd as userPwd from user where name =#{arg0} and pwd = #{arg1};
    </select>

</mapper> 

• What if the returned is mybatis, not a built-in type, but our custom javaBean?

• If it is an ordinary JavaBean object and all of them are basic types, define the fully qualified class path of our custom JavaBean in resultType or define a typeAliases to use aliases (see alias configuration for details) Detailed description of mybatis (II) xml configuration method )I won't repeat it here. What if the custom javaBean object references another javaBean or defines a more complex attribute, isn't mybatis handled automatically?

• We define a dto object, which contains two fields of user and a collection object cartGoods (collection of all goods in the shopping cart):

package com.szm.dto;

import com.szm.pojo.Good;
import lombok.Data;

import java.util.List;

@Data
public class UserInfo {

    private int userId;
    private String userName;
    List<Good> cartGoods;
}

package com.szm.mapper;

import com.szm.dto.UserInfo;

import java.util.List;
import java.util.Map;


public interface UserMapper {
    UserInfo selectUserInfo(int userId);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.szm.mapper.UserMapper">
    <select id="selectUserInfo" resultType="com.szm.dto.UserInfo">
       	select
        a.id as userId,
        a.name as userName,
        c.good_id as goodId,
        c.good_name as goodName
        from (select * from user where id = #{arg0}) a
        left join shoopingcart b on a.id = b.user_id
				left join goods c on b.good_id = c.good_id;
    </select>
</mapper>

We expect that mybatis can automatically help us map the results. The results show that mybatis is not so intelligent. The running results are as follows:

org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 2

	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:80)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:87)
	at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:152)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85)
	at com.sun.proxy.$Proxy6.selectUserInfo(Unknown Source)
	at Test1.test2(Test1.java:48)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
	at org.junit.runners.BlockJUnit4ClassRunner$1.evaluate(BlockJUnit4ClassRunner.java:100)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:103)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:63)
	at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
	at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
	at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
	at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33)
	at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:230)
	at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:58)

Process finished with exit code 255

• According to the official document of mybatis in resultType:

MyBatis will automatically create a ResultMap behind the scenes, and then map the columns to the properties of the JavaBean according to the property name

Therefore, the resultType is actually a resultMap in essence. For complex situations, let's look at the resultMap step by step!

3.1.5,resultMap

3.1.5.1. resultMap internal label list

• There are many rich and easy-to-use function labels for us to use

• Tag list:

Label name	purpose
constructor	It is used to inject the result into the constructor when instantiating the class
id	An ID result; Marking the results as IDS can help improve overall performance
result	Common results injected into fields or JavaBean properties
association	A complex type of Association; Many results will be wrapped in this type, nested result maps - associations can be resultMap elements, or references to other result maps
collection	A collection of complex types
discriminator	Use the result value to determine which resultMap to use

3.1.5.2 constructor and discriminator labels

• constructor and discriminator tags (one-to-one and one to many under other tags will be used, not to mention first):

The definition of resultMap can be understood as defining an object to receive a query result of sql. During initialization and attribute injection of this object, mybatis has agreed on some tags to facilitate us to do some processing

• constructor allows us to inject some fields of the current record in the current result set into our construction method when initializing the returned object, that is, if we define this label, mybatis will call our parametric construction method when initializing the object

• discriminator, if this tag is defined, when mybatis injects the result set, it will judge some fields of the current record line according to the configuration to determine which resultMap the current line uses

  Let's briefly use these two Tags:

• Create a new UserInfo object and a new parameterized construction. Assign the extend attribute in the construction method:

package com.szm.pojo;

import lombok.Data;

import java.util.UUID;


@Data
public class User {
    public User(){

    }
    public User(String extend){
        this.extend = extend+ UUID.randomUUID();

    }
    private int userId;
    private String userName;
    private String userPwd;
    private String extend;
}

dao layer interface

package com.szm.mapper;



import com.szm.dto.UserCart;
import com.szm.dto.UserInfo;
import com.szm.pojo.User;

import java.util.List;


public interface UserMapper {

    List<User> select();
   
}

xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--baseUserMap2 Of query results name and pwd Maps are interchanged -->
<mapper namespace="com.szm.mapper.UserMapper">
    <resultMap id="baseUserMap2" type="user">
        <id property="userId" column="id"/>
        <result property="userName" column="pwd"/>
        <result property="userPwd" column="name"/>
    </resultMap>

    <resultMap id="baseUserMap" type="user">
      <!-- injection name Listed in our constructor-->
        <constructor>
            <idArg column="name" javaType="string"/>
        </constructor>
        <id property="userId" column="id"/>
        <result property="userName" column="name"/>
        <result property="userPwd" column="pwd"/>
      <!--If the current column id Equal to 1, use baseUserMap2 As of the current column resultMap， -->
        <discriminator javaType="int" column="id">
            <case value="1" resultMap="baseUserMap2"></case>
        </discriminator>
    </resultMap>

    <select id="select" resultMap="baseUserMap">
    select * from user;
    </select>
</mapper>

• The expected result is that the name and pwd fields of the record with id=1 are interchanged, and the extend value of other records is name+uuid. The operation results are as follows, which is in line with the expectation:

User(userId=1, userName=pwd1, userPwd=user1, extend=null)
User(userId=2, userName=user2, userPwd=pwd2, extend=user2f1ae73cd-5ced-4778-8f97-ada7c690cdaf)
User(userId=3, userName=user3, userPwd=pwd3, extend=user3e012f90f-75aa-4294-9727-cf8abae884c3)

3.1.5.2 one to one association label

• First define a result object

package com.szm.dto;

import com.szm.pojo.Wallet;
import lombok.Data;


@Data
public class UserInfo {

    private int userId;
    private String userName;
    private String userPwd;
    private Wallet wallet;

}

• Define dao layer interface

package com.szm.mapper;



import com.szm.dto.UserInfo;
import com.szm.pojo.User;

import java.util.List;
import java.util.Map;


public interface UserMapper {
    
    List<UserInfo> selectUser();
}

• You can see that the column name of the sql query result is different from the attribute name of the javaBean object. If you do not want to use as to get the column alias when writing sql, you can use resultMap to configure the mapping for the query result.
• If there are other javaBean object attributes in the javaBean that need to be mapped, they can be defined using the association tag. The configuration is as follows:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.szm.mapper.UserMapper">

    <resultMap id="userMap" type="userInfo">
        <id property="userId" column="id"/>
        <result property="userName" column="name"/>
        <result property="userPwd" column="pwd"/>
        <association property="wallet" >
            <result property="balance" column="balance"></result>
        </association>
    </resultMap>

    <select id="selectUser" resultMap="userMap">
        select a.id,a.name,a.pwd,b.balance from user a
        left join wallet b on a.id = b.user_id
    </select>

</mapper>

• Query results

UserInfo(userId=1, userName=user1, userPwd=pwd1, wallet=Wallet(id=0, balance=100.00))
UserInfo(userId=2, userName=user2, userPwd=pwd2, wallet=Wallet(id=0, balance=200.00))
UserInfo(userId=3, userName=user3, userPwd=pwd3, wallet=Wallet(id=0, balance=300.00))

Process finished with exit code 0

• You can also take out the wallet and define it as a resultMap to facilitate reuse

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.szm.mapper.UserMapper">

    <resultMap id="wallet" type="com.szm.pojo.Wallet">
        <result property="balance" column="balance"></result>
    </resultMap>
    <resultMap id="userMap" type="userInfo">
        <id property="userId" column="id"/>
        <result property="userName" column="name"/>
        <result property="userPwd" column="pwd"/>
        <association property="wallet" resultMap="wallet" />
    </resultMap>

    <select id="selectUser" resultMap="userMap">
        select a.id,a.name,a.pwd,b.balance from user a
        left join wallet b on a.id = b.user_id
  </select>

</mapper>

3.1.5.3. collection tag one to many

• What if the query result object contains a collection type javaBean attribute? For example:

package com.szm.dto;

import com.szm.pojo.Good;

import java.util.List;

public class UserCart {
    private int userId;
    private String userName;
    private List<Good> goods;
}

• dao interface

package com.szm.mapper;



import com.szm.dto.UserCart;
import com.szm.dto.UserInfo;

import java.util.List;


public interface UserMapper {
   
    List<UserCart> selectUserCart();
}

At this point, the collection with result can be easily solved:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.szm.mapper.UserMapper">

    

    <!-- One to many-->
    <resultMap id="cartGood" type="com.szm.pojo.Good">
        <result property="goodName" column="good_name"/>
    </resultMap>

    <resultMap id="userCartMap" type="com.szm.dto.UserCart">
        <id property="userId" column="id"></id>
        <result property="userName" column="name"></result>
        <collection property="goods" resultMap="cartGood"/>
    </resultMap>

    <select id="selectUserCart" resultMap="userCartMap">
       select
        a.id ,
        a.name ,
        c.good_id ,
        c.good_name
        from user a
        left join shoopingcart b on a.id = b.user_id
		left join goods c on b.good_id = c.good_id;
    </select>


</mapper>

• Operation results:

UserCart(userId=1, userName=user1, goods=[Good(goodId=0, goodName=jacket), Good(goodId=0, goodName=trousers)])
UserCart(userId=2, userName=user2, goods=[Good(goodId=0, goodName=jacket)])
UserCart(userId=3, userName=user3, goods=[Good(goodId=0, goodName=trousers), Good(goodId=0, goodName=shoes)])

Process finished with exit code 0

3.1.5.4. resultMap inheritance

• resultMap can also obtain the definition of the parent class through inheritance like extensions in java

<resultMap id="publicUserMap" type="user">
        <id property="userId" column="id"/>
        <result property="userName" column="name"/>
        <result property="userPwd" column="pwd"/>
    </resultMap>
    <!--extends After definition baseUserMap Inherited publicUserMap Definition of -->
    <resultMap id="baseUserMap" type="user" extends="publicUserMap">
        <constructor>
            <idArg column="name" javaType="string"/>
        </constructor>
        <discriminator javaType="int" column="id">
            <case value="1" resultMap="baseUserMap2"></case>
        </discriminator>
    </resultMap>

• The operation results are consistent with those above

3.1.6. sql and include tags

The SQL tag can define some reusable SQL code fragments for use in other statements, and the parameters can also be determined during operation. Based on this feature, more flexible configurations can be made in combination with select

• The include tag can apply the content defined by the sql tag

The details are as follows:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.szm.mapper.UserMapper">
		
    <sql id="userSql" >${userTable}.id,${userTable}.name,${userTable}.pwd</sql>


    <resultMap id="wallet" type="com.szm.pojo.Wallet">
        <result property="balance" column="balance"></result>
    </resultMap>
    <resultMap id="userMap" type="userInfo">
        <id property="userId" column="id"/>
        <result property="userName" column="name"/>
        <result property="userPwd" column="pwd"/>
        <association property="wallet" resultMap="wallet" />
    </resultMap>

    <select id="selectUser" resultMap="userMap">
        select
        <include refid="userSql"><property name="userTable" value="a"/></include>,
        b.balance
        from user a
        left join wallet b on a.id = b.user_id
    </select>


    <resultMap id="cartGood" type="com.szm.pojo.Good">
        <id property="goodId" column="good_id" />
        <result property="goodName" column="good_name"/>
    </resultMap>

    <resultMap id="userCartMap" type="com.szm.dto.UserCart">
        <id property="userId" column="id"></id>
        <result property="userName" column="name"></result>
        <result property="goodId" column="good_id"></result>
        <result property="goodName" column="good_name"></result>

    </resultMap>

    <select id="selectUserCart" resultType="com.szm.dto.UserInfo">
       select
        a.id ,
        a.name ,
        c.good_id ,
        c.good_name
        from (select * from user where id = #{arg0}) a
        left join shoopingcart b on a.id = b.user_id
		left join goods c on b.good_id = c.good_id;
    </select>


</mapper>

3.2. insert, update and delete

The implementation of data change statements insert, update and delete is very close:

• Attributes of Insert, Update, Delete elements

attribute	describe
id	A unique identifier in the namespace that can be used to reference this statement.
parameterType	The fully qualified name or alias of the class that will be passed into the parameters of this statement. This property is optional because MyBatis can infer the parameters of the specific incoming statement through the type handler. The default value is unset.
parameterMap (obsolete)	The property used to reference an external parameterMap is currently obsolete. Please use inline parameter mapping and parameterType property.
flushCache	When it is set to true, as long as the statement is called, the local cache and L2 cache will be emptied. The default value is (for insert, update and delete statements) true.
timeout	This setting is the number of seconds the driver waits for the database to return the requested result before throwing an exception. The default value is unset (database driven).
statementType	Optional status, PREPARED or CALLABLE. This will make MyBatis use statement, PreparedStatement or CallableStatement respectively. The default value is PREPARED.
useGeneratedKeys	(only applicable to insert and update) this will make MyBatis use the getGeneratedKeys method of JDBC to retrieve the primary key generated inside the database (such as the auto increment field of relational database management systems such as MySQL and SQL Server). The default value is false.
keyProperty	(only applicable to insert and update) specify the attribute that can uniquely identify the object. MyBatis will use the return value of getGeneratedKeys or the selectKey sub element of the insert statement to set its value. The default value is unset. If more than one column is generated, multiple attribute names can be separated by commas.
keyColumn	(only applicable to insert and update) set the column name of the generated key value in the table. In some databases (such as PostgreSQL), it must be set when the primary key column is not the first column in the table. If more than one column is generated, multiple attribute names can be separated by commas.
databaseId	If the database vendor ID (databaseIdProvider) is configured, MyBatis will load all statements without databaseId or matching the current databaseId; If there are statements with and without, the statements without are ignored.

example:

 		<insert id="insertUser">
        insert into user(id,name,pwd)
        values(#{id},#{name},#{pwd});
    </insert>
    
    <update id="updateUser">
        update user set name= #{name} where id = #{id}
    </update>
    
    <delete id="deleteUser">
        delete from user where id = #{id}
    </delete>

• These three tags are relatively simple, focusing on several extensions:

3.2.1. Insert auto generated primary key

If your database supports fields that automatically generate primary keys (for example, MySQL and SQL Server, you can set useGeneratedKeys = "true" and then set the keyProperty as the target property. In this way, you can omit the insertion of the primary key id. in fact, if you set the automatic growth of the primary key, you can insert successfully without setting these two properties, but you must set it if you want to obtain the returned primary key id , keyProperty is the property name of the incoming javaBean. Note that the return here is actually the incoming object itself. mybatis does a setId operation on the incoming object at the bottom and returns it

	<insert id="insertUser" useGeneratedKeys="true"  keyProperty="userId">
        insert into user(name,pwd)
        values(#{user.name},#{user.pwd});
    </insert>

3.2.2 batch insertion

If your database also supports multi row insertion, you can also pass in an array or collection and return the automatically generated primary key. Note that the return here is actually the incoming object itself. mybatis does a setId operation on the incoming object at the bottom and returns it

User adds a new constructor

package com.szm.pojo;

import lombok.Data;

import java.util.UUID;

@Data
public class User {
    public User() {

    }

    public User(String extend) {
        this.extend = extend + UUID.randomUUID();

    }

    public User(String name, String pwd) {
        this.userName = name;
        this.userPwd = pwd;
    }

    private int userId;
    private String userName;
    private String userPwd;
    private String extend;
}

dao:

package com.szm.mapper;




import com.szm.pojo.User;

import java.util.List;

public interface UserMapper {

	// Note that what is returned here is actually the incoming object itself. mybatis does a setId operation on the incoming object at the bottom and returns it 
   void batchInsertUser(@Param("users") List<User> users);
}

<insert id="batchInsertUser" useGeneratedKeys="true" keyProperty="userId" >
        insert into user (name, pwd) values
        <foreach item="user" collection="users" separator=",">
            (#{user.userName}, #{user.userPwd})
        </foreach>
</insert>

Test class

 @Test
    public void test4() throws IOException {
        String resource = "mybatis.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = new ArrayList<>();
        users.add(new User("user4","pwd4"));
        users.add(new User("user4","pwd5"));
         mapper.batchInsertUser(users);
        users.stream().forEach((e)->{
            System.out.println(e);
        });
        sqlSession.close();
    }

Operation results:

User(userId=8, userName=user4, userPwd=pwd4, extend=null)
User(userId=9, userName=user4, userPwd=pwd5, extend=null)

3.2.3, selectKey

• In the official document of mybatis, this is used as an alternative to automatically generating the primary key in the database. In essence, it is to define a query result as a query sql of one row and one column. Mybatis will execute this sql before / after the query according to the order you define, and inject the query result into the corresponding attribute of the object you pass in.

  Official example:

  <insert id="insertAuthor">
    <selectKey keyProperty="id" resultType="int" order="BEFORE">
      select CAST(RANDOM()*1000000 as INTEGER) a from SYSIBM.SYSDUMMY1
    </selectKey>
    insert into Author
      (id, username, password, email,bio, favourite_section)
    values
      (#{id}, #{username}, #{password}, #{email}, #{bio}, #{favouriteSection,jdbcType=VARCHAR})
  </insert>