Mybatis (II) -- mapping

1. Syntax of mapping file XXMapper.xml:

<mapper namespace="cn.kgc.mspper.UserMapper">
	<select id=" findCount " ...
		......
	</select>
</mapper>

1.1 namespace: namespace

The name of a namespace must have the same name as an interface

1.2 id: unique identifier in namespace**

The method in the interface corresponds to the SQL statement id in the mapping file one by one

1.3 parameterType: parameter type

(single parameter can be omitted, but multiple parameters cannot)

Parameter type passed in SQL statement

1.4 resultType: return value type

The SQL statement returns the full class name or alias of the value type

1.5 actual combat:

Requirement: perform fuzzy query on user table according to user name

1.6 realization:

(1) Import library smbms_db.sql

(2) Create project, catalog

(3)pom.xml

<dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.20</version>
      <scope>provided</scope>
    </dependency>
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.5.1</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.21</version>
</dependency>

(4) Entity class main/java/cn.kgc.entity/User

package cn.kgc.entity;

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@Getter
@Setter
public class User {
    private Integer id;
    private String userCode;
    private String userName;
    private String userPassword;
    private Integer gender;
    private String birthday;
    private String phone;
    private String address;
    private Integer userRole;
    private String creationDate;
    private Integer modifyBy;
private String modifyDate;
}

(5)resources/ mybatis-config.xml,jdbc.properties
jdbc.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/smbms?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
username=root
password=zjj

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//ibatis.apache.org//DTD Config 3.0//EN"
        "http://ibatis.apache.org/dtd/ibatis-3-config.dtd">
<configuration>
    <properties resource="jdbc.properties" />
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
    <environments default="dev">
        <environment id="dev">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}" />
                <property name="url" value="${url}" />
                <property name="username" value="${username}"/>
                <property name="password" value="${password}" />
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="cn/kgc/mapper/UserMapper.xml"/>
    </mappers>
</configuration>

(6) main/java/cn.kgc.mapper/UserMapper.java

package cn.kgc.mapper;

import cn.kgc.entity.User;
import org.apache.ibatis.annotations.Param;

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

public interface UserMapper {
    //Number of query records
    Integer findCount();
}

(7) resources/ cn.kgc.mapper/UserMapper.xml

Note: cn/kgc/mapper is directly used to create packages in resources

<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.kgc.mapper.UserMapper">
    <select id="findCount" resultType="java.lang.Integer" >
        select count(*) from smbms_user
</select>
</mapper>

(8) main/java/cn.kgc.util/MyBatisUtil

package cn.kgc.util;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

public class MyBatisUtil {
    private static SqlSessionFactory factory = null;

    static {
        try {
            InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
            factory = new SqlSessionFactoryBuilder().build(in);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static SqlSession getSqlSession(){
        SqlSession sqlSession = null;
        if (factory != null){
            sqlSession = factory.openSession();
        }
        return sqlSession;
    }
}

(9) test/java/cn.kgc.test/TestUserMapper.java

package cn.kgc.test;

import cn.kgc.entity.User;
import cn.kgc.mapper.UserMapper;
import cn.kgc.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class TestUserMapper {
    @Test
    public void testFindCount(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        Integer count = sqlSession.getMapper(UserMapper.class).findCount();
        System.out.println("Number of records:"+count);
        sqlSession.close();
}
}

1.7 parameterType basic data type / complex data type

parameterType
(1) Basic data type:
int, String, Date, etc
Only one can be passed in, and the passed in value can be obtained through #{parameter name}

(2) Complex data types:
Java entity class, Map, etc
The passed in value can be obtained by #{attribute name} or #{map's keyName}

1.8 multi parameter actual combat:

Requirement: query by user name fuzzy matching and user role id condition

1.9 the first to third implementations:

(1)UserMapper.java

//1. Query the list by user name and user role
    List<User> findByUser(User user);

//2. Query the list through user mine and user role Map container parameters
    List<User> findByMap(Map<String,Object> map);

//3. Realize multi parameter query by annotation
List<User> findByConditions(@Param("name") String userName,@Param("role") Integer userRole);

(2)UserMapper.xml

<select id="findByUser" resultType="cn.kgc.entity.User" parameterType="cn.kgc.entity.User">
        select * from smbms_user where userName like concat('%',#{userName},'%')
                        and userRole = #{userRole}
    </select>
    <select id="findByMap" resultType="cn.kgc.entity.User" parameterType="java.util.Map">
--                         MyBatis of Aliase Configuration can be omitted cn.kgc.entity
        select * from smbms_user where userName like concat('%',#{userName},'%')
                        and userRole = #{userRole}
    </select>
    <select id="findByConditions" resultType="cn.kgc.entity.User" >
        select * from smbms_user where userName like concat('%',#{name},'%')
                        and userRole = #{role}
    </select>

(3)TestUserMapper

@Test
    public void testFindByUser(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        User user = new User();
        user.setUserName("king");
        user.setUserRole(3);
        List<User> list = sqlSession.getMapper(UserMapper.class).findByUser(user);
        for (User u: list) {
            System.out.println("userName: " + u.getUserName() + "    userPassword: " + u.getUserPassword());
        }
        //There is a 0000000 in the database, which may report wrong dirty data. Remember to correct it
        sqlSession.close();
    }
    @Test
    public void testFindByMap(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        Map<String, Object> map = new HashMap<>();
        map.put("userName","Sun");
        map.put("userRole",3);
        List<User> list = sqlSession.getMapper(UserMapper.class).findByMap(map);
        for (User u: list) {
            System.out.println("userName: " + u.getUserName() + "    userPassword: " + u.getUserPassword());
        }
        sqlSession.close();
    }
    @Test
    public void findByConditions(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        List<User> list = sqlSession.getMapper(UserMapper.class).findByConditions("Sun",3);
        for (User u: list) {
            System.out.println("userName: " + u.getUserName() + "    userPassword: " + u.getUserPassword());
        }
        sqlSession.close();
}

1.10 resultMap describes how to map result sets to Java objects

(interview questions)

**resultType: * * directly indicates the return type
Basic data type
Complex data type

**resultMap: * * reference to external resultMap
Application scenario:
The database field information is inconsistent with the object attribute
Complex joint query, free control mapping results
The two cannot exist at the same time. They are essentially Map data structures

1.11 query user list by user name and role code

(1)User.java

private String userRoleName;

(2)UserMapper.java

//ResultMap actual combat
List<User> FindByResultMap(User user);

(3)UserMapper.xml

<select id="FindByResultMap" parameterType="cn.kgc.entity.User" resultMap="userList">
        select a.id,a.userCode,b.roleName from smbms_user a,smbms_role b where a.userRole = b.id
                        and userName like concat('%',#{userName},'%')
                        and userRole = #{userRole}
--         select a.id,a.userCode,b.roleName from smbms_user a left join smbms_role b on a.userRole = b.id
--                         and userName like concat('%',#{userName},'%')
--                         and userRole = #{userRole}
    </select>
    <resultMap id="userList" type="cn.kgc.entity.User">
        <result property="id" column="id"></result>
        <result property="userCode" column="userCode"></result>
        <result property="userRoleName" column="roleName"></result>
    </resultMap>
<!--    Use alias instead of the above method as The above method is aimed at bar refinement id Because the two are the same-->

(4)TestUserMapper.java

@Test
    public void findByConditions2(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        User user = new User();
        user.setUserName("king");
        user.setUserRole(3);
        List<User> list = sqlSession.getMapper(UserMapper.class).FindByResultMap(user);
        for (User u: list) {
            System.out.println("user id:" + u.getId() + "    User code:" + u.getUserCode() + "    Role name:" + u.getUserRoleName());
        }
        sqlSession.close();
    }

2. Addition, deletion and modification (Mybatis (I) has mentioned)

Key points:
insert, update and delete elements have no resultType attribute
But there must be sqlSession.commit();

(1) Add user table (omitted)

(2) Modify the user table (omitted)

(3) Realize the function of modifying the current user password - @ Param (omitted)

Core idea: more than three parameters are recommended to be encapsulated into objects, and two parameters are recommended to use @ Param

With annotations, you don't have to write parameters

(4) Delete user information according to user id (omitted)

3.resultMap

Core idea

id
Generally, it corresponds to the primary key id of the row in the database. Setting this item can improve the performance of MyBatis

result
Map to a "simple type" property of a JavaBean

association
Map to a "complex type" attribute of a JavaBean, such as a JavaBean class

collection
Map to a "complex type" attribute of a JavaBean, such as a collection

(1) Get user list according to user role id - association

A. Core idea:

association
Complex type associations, one-to-one
Inner nesting
Map a nested JavaBean property

attribute
Property: the property of the entity object that maps the database column
javaType: full Java class name or alias
resultMap: references an external resultMap

Child element
id
result
Property: the property of the entity object that maps the database column
Column: database column name or alias

B. Actual combat:
Using user role to query user list

According to smbms_ userRole in user
To the corresponding smbms_ id corresponding to role
Then find out the corresponding roleName

Role:

package cn.kgc.entity;

import lombok.Getter;
import lombok.Setter;

import java.util.Date;

@Getter
@Setter
public class Role {
    private Integer id;
    private String roleCode;
    private String roleName;
    private Integer createdBy;
    private Date creationDate;
    private Integer modifyBy;
    private Date modifyDate;
}

User

private Role role;

UserMapper

//Using user role to query user list
List<User> findUserRole(Integer userRole);

UserMapper.xml

<select id="findUserRole" parameterType="java.lang.Integer" resultMap="userRoleResult">
        SELECT a.id,a.userCode,b.roleName FROM smbms_user a,smbms_role b
            WHERE a.userRole = b.id AND a.userRole = #{userRole}
    </select>
    <!--    resultMap Map the complex data queried (such as the data in several tables) to a result set.-->
<!--    mapping User Result set of class-->
    <!-- resultMap Finally, map the results to User Up, type Is to specify which one to map to User -->
    <!--     property: Primary key in pojo Property name in -->
    <!--     column: The column name of the primary key in the database -->
    <resultMap id="userRoleResult" type="cn.kgc.entity.User">
        <id property="id" column="id"></id>
        <result property="userCode" column="userCode"></result>
<!--        association Mapping associated objects Role Result set of-->
        <association property="role" javaType="cn.kgc.entity.Role">
            <result property="roleName" column="roleName"></result>
        </association>
    </resultMap>

TestUserMapper

@Test
public void findUserRole() {
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    List<User> userRole = sqlSession.getMapper(UserMapper.class).findUserRole(1);
    for (User u: userRole) {
        System.out.println(
                "Id: " + u.getId() + "    Code: " + u.getUserCode() + "    RoleName:" + u.getRole().getRoleName());
    }
    sqlSession.close();
}

result:

(2) Get the relevant information of the specified user and its address list - collection

A. core idea:

collection
Complex type collection, one to many
Inner nesting
Map a nested result set to a list

attribute
Property: the property of the entity object that maps the database column
ofType: full Java class name or alias (type included in the collection)
resultMap: references an external resultMap

Child element
id
result
Property: the property of the entity object that maps the database column
Column: database column name or alias

B. actual combat:
Collection is used to query the user address collection list

According to smbms_user's id
De corresponding smbms_ userId in address
There may be more than one user address

Address.java

package cn.kgc.entity;

import lombok.Getter;
import lombok.Setter;

import java.util.Date;

@Getter
@Setter
public class Address {
    private Integer id;
    private String contact;
    private String addressDesc;
    private String postCode;
    private String tel;
    private Integer createdBy;
    private Date creationDate;
    private Integer modifyBy;
    private Date modifyDate;
    private Integer userId;
}

User.java

private List<Address> addressList;

UserMapper.java

//Collection is used to query the user address collection list
List<User> findUserAddressListById(@Param("id") Integer id);