MyBatis mapping file related operations

Keywords: Java Session Mybatis xml Apache

I. MyBatis mapping file

1, introduction

The real power of MyBatis lies in its mapping statement and its magic. Because of its extraordinary power, the XML file of mapper is relatively simple. If you compare it with JDBC code with the same function, you will immediately find that nearly 95% of the code is saved. MyBatis is built for SQL and is better than normal methods.

To learn MyBatis, you need to understand how it processes parameters and maps return values.

2. Simple addition, deletion, modification and query

Use a simple addition, deletion, modification and query case to quickly understand the process of using mybatis mapping file.

step1: file structure

step2: full code

[Main documents]
    config/db.properties                   Configuration information for setting up the database
    config/EmpMapper.xml                   sql Mapping files for authoring sql Sentence
    config/mybatis-config.xml              Global profile
    entity.Employee                        Entity class
    mapper.EmpMapper                       Interface class, used to define sql Method
    test.Demo                              Test class, used for adding, deleting, modifying and checking


[config/db.properties ]
jdbc.driver = com.mysql.cj.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/lyh?useUnicode=true&characterEncoding=utf8
jdbc.username = root
jdbc.password = 123456


[config/mybatis-config.xml]
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="config/db.properties"></properties>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <!-- MyBatis It comes with a connection pool. You only need to configure some data of database connection.
                       //Take MySql for example.
                   -->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url"
                          value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <!-- Load SQL Definition file, need to be modified
        //If there are multiple SQL definition files, you can append them with the < mapper / > tag.
          //Use / split if. Exists in the file path
     -->
    <mappers>
        <mapper resource="config/EmpMapper.xml"/>
    </mappers>
</configuration>


[entity.Employee]
package entity;

import java.io.Serializable;

/**
 * Entity class
 */
public class Employee implements Serializable {
    private Integer id;
    private String name;
    private Double salary;
    private Integer age;

    public Employee() {
    }

    public Employee(Integer id, String name, Double salary, Integer age) {
        this.id = id;
        this.name = name;
        this.salary = salary;
        this.age = age;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Double getSalary() {
        return salary;
    }

    public void setSalary(Double salary) {
        this.salary = salary;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", salary=" + salary +
                ", age=" + age +
                '}';
    }
}



[mapper.EmpMapper]
package mapper;

import entity.Employee;
import org.apache.ibatis.annotations.Select;

/**
 * Interface programming defines a series of methods and maps them to the xml file of sql.
 * Where, the return value type can be Integer, Long, Boolean, void, entity class
 */
public interface EmpMapper {
    public Employee getEmpById(Integer id);
    public Long addEmp(Employee emp);
    public Boolean updateEmp(Employee emp);
    public Integer deleteEmpById(Integer id);
}



[config/EmpMapper.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">
<mapper namespace="mapper.EmpMapper">
    <insert id="addEmp">
        INSERT INTO emp(emp.name, emp.salary, emp.age) VALUES (#{name}, #{salary}, #{age})
    </insert>

    <delete id="deleteEmpById">
        DELETE FROM emp WHERE emp.id = #{id}
    </delete>

    <update id="updateEmp">
        UPDATE emp SET emp.salary = #{salary}, emp.age = #{age}, emp.name = #{name} WHERE emp.id = #{id}
    </update>

    <select id="getEmpById" resultType="entity.Employee">
        SELECT * FROM emp WHERE emp.id = #{id}
    </select>
</mapper>


[test.Demo]
package test;

import entity.Employee;
import mapper.EmpMapper;
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 org.junit.Test;

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

public class Demo {

    private static SqlSessionFactory getSqlSessionFactory() throws IOException {
        // step1: read configuration file
        String resource = "config/mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);

        // Step 2: create an object of SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        return sqlSessionFactory;
    }

    @Test
    public void testAddEmp() throws IOException {
        // Get an object of SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = Demo.getSqlSessionFactory();
        // Get an instance of SqlSession
        SqlSession session = sqlSessionFactory.openSession();
        try{
            EmpMapper empMapper = session.getMapper(EmpMapper.class);
            Long result = empMapper.addEmp(new Employee(null,"jarry", 4000.0, 33));
            System.out.println(result);
            // commit is required for addition, deletion and modification
            session.commit();
        }finally {
            // step4: close SqlSession instance
            session.close();
        }
    }

    @Test
    public void testDeleteEmpById() throws IOException {
        // Get an object of SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = Demo.getSqlSessionFactory();
        // Get an instance of SqlSession
        SqlSession session = sqlSessionFactory.openSession();
        try{
            EmpMapper empMapper = session.getMapper(EmpMapper.class);
            Integer result = empMapper.deleteEmpById(5);
            System.out.println(result);
            // commit is required for addition, deletion and modification
            session.commit();
        }finally {
            // step4: close SqlSession instance
            session.close();
        }
    }

    @Test
    public void testUpdateEmp() throws IOException {
        // Get an object of SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = Demo.getSqlSessionFactory();
        // Get an instance of SqlSession
        SqlSession session = sqlSessionFactory.openSession();
        try{
            EmpMapper empMapper = session.getMapper(EmpMapper.class);
            Boolean result = empMapper.updateEmp(new Employee(1,"jarry", 4000.0, 33));
            System.out.println(result);
            // commit is required for addition, deletion and modification
            session.commit();
        }finally {
            // step4: close SqlSession instance
            session.close();
        }
    }

    @Test
    public void testGetEmpById() throws IOException {
        // Get an object of SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = Demo.getSqlSessionFactory();
        // Get an instance of SqlSession
        SqlSession session = sqlSessionFactory.openSession();
        try{
            EmpMapper empMapper = session.getMapper(EmpMapper.class);
            Employee result = empMapper.getEmpById(1);
            System.out.println(result);
            // commit is required for addition, deletion and modification
            session.commit();
        }finally {
            // step4: close SqlSession instance
            session.close();
        }
    }
}

Step 3: Test screenshot
(1) add a piece of data

(2) delete a piece of data

(3) update a piece of data

(4) query a piece of data

3. Return the auto increasing primary key (support the auto increasing database, such as mysql, etc.)

Use generated keys (default is false) and keyProperty (specify primary key) are required.
The getGeneratedKeys method is called internally by mybatis and encapsulated.

[Modify test code]
@Test
public void testAddEmp() throws IOException {
    // Get an object of SqlSessionFactory
    SqlSessionFactory sqlSessionFactory = Demo.getSqlSessionFactory();
    // Get an instance of SqlSession
    SqlSession session = sqlSessionFactory.openSession();
    try{
        EmpMapper empMapper = session.getMapper(EmpMapper.class);
        Employee employee = new Employee(null,"tom", 4000.0, 33);
        System.out.println(employee);
        Long result = empMapper.addEmp(employee);
        System.out.println(employee);
        System.out.println(result);
        // commit is required for addition, deletion and modification
        session.commit();
    }finally {
        // step4: close SqlSession instance
        session.close();
    }
}

The primary key will not be returned automatically.

Add useGeneratedKeys and keyProperty.

[Yes sql Map file to modify]
<insert id="addEmp" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO emp(emp.name, emp.salary, emp.age) VALUES (#{name}, #{salary}, #{age})
</insert>

The primary key is mapped automatically.

4. Return the non auto increasing primary key (use mysql to simulate the non auto increasing database)

Use the selectKey tag to specify.
Create a table (do not use auto increment to define auto increment primary key).

Change the table name of the sql mapping file to emp2. Add data.

<insert id="addEmp">
    INSERT INTO emp2(emp2.id, emp2.name, emp2.salary, emp2.age) VALUES (#{id}, #{name}, #{salary}, #{age})
</insert>

The primary key is not mapped automatically.

With selectKey, you can specify a primary key. resultType is the return value type. order is BEFORE|AFTER (decide when to execute).

<insert id="addEmp">
    <selectKey keyProperty="id" order="BEFORE" resultType="Integer">
        SELECT max(id) + 10 FROM emp2
    </selectKey>
    INSERT INTO emp2(emp2.id, emp2.name, emp2.salary, emp2.age) VALUES (#{id}, #{name}, #{salary}, #{age})
</insert>

Automatically map the primary key.

II. Parameter processing

1. Single parameter

For a single parameter, you can get it directly by using {variable name}.

[mapper.EmpMapper]
package mapper;

import entity.Employee;
import org.apache.ibatis.annotations.Select;

/**
 * Interface programming defines a series of methods and maps them to the xml file of sql.
 * Where, the return value type can be Integer, Long, Boolean, void, entity class
 */
public interface EmpMapper {
    public Employee getEmpById(Integer id);
}


[config/EmpMapper.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">
<mapper namespace="mapper.EmpMapper">
    <select id="getEmpById" resultType="entity.Employee">
        SELECT * FROM emp WHERE emp.id = #{id}
    </select>
</mapper>


[test.Demo]
package test;

import entity.Employee;
import mapper.EmpMapper;
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 org.junit.Test;

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

public class Demo {

    private static SqlSessionFactory getSqlSessionFactory() throws IOException {
        // step1: read configuration file
        String resource = "config/mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);

        // Step 2: create an object of SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        return sqlSessionFactory;
    }

    @Test
    public void testGetEmpById() throws IOException {
        // Get an object of SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = Demo.getSqlSessionFactory();
        // Get an instance of SqlSession
        SqlSession session = sqlSessionFactory.openSession();
        try{
            EmpMapper empMapper = session.getMapper(EmpMapper.class);
            Employee result = empMapper.getEmpById(1);
            System.out.println(result);
            // commit is required for addition, deletion and modification
            session.commit();
        }finally {
            // step4: close SqlSession instance
            session.close();
        }
    }
}

2. Multiple parameters (few)

For multiple parameters, using {variable name} directly throws an exception (you can use @ param with it). It is encapsulated internally as a map collection.

[mapper.EmpMapper]
package mapper;

import entity.Employee;
import org.apache.ibatis.annotations.Select;

/**
 * Interface programming defines a series of methods and maps them to the xml file of sql.
 * Where, the return value type can be Integer, Long, Boolean, void, entity class
 */
public interface EmpMapper {
    public Employee getEmpByIdAndName(Integer id, String name);
}


[config/EmpMapper.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">
<mapper namespace="mapper.EmpMapper">

    <select id="getEmpByIdAndName" resultType="entity.Employee">
        SELECT * FROM emp WHERE emp.id = #{id} AND emp.name = #{name}
    </select>
</mapper>


[test.Demo]
package test;

import entity.Employee;
import mapper.EmpMapper;
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 org.junit.Test;

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

public class Demo {

    private static SqlSessionFactory getSqlSessionFactory() throws IOException {
        // step1: read configuration file
        String resource = "config/mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);

        // Step 2: create an object of SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        return sqlSessionFactory;
    }

    @Test
    public void testGetEmpByIdAndName() throws IOException {
        // Get an object of SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = Demo.getSqlSessionFactory();
        // Get an instance of SqlSession
        SqlSession session = sqlSessionFactory.openSession();
        try{
            EmpMapper empMapper = session.getMapper(EmpMapper.class);
            Employee result = empMapper.getEmpByIdAndName(1, "jack");
            System.out.println(result);
            // commit is required for addition, deletion and modification
            session.commit();
        }finally {
            // step4: close SqlSession instance
            session.close();
        }
    }
}

The parameter name is not correct. Revised as follows

[config/EmpMapper.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">
<mapper namespace="mapper.EmpMapper">

    <select id="getEmpByIdAndName" resultType="entity.Employee">
        SELECT * FROM emp WHERE emp.id = #{param1} AND emp.name = #{param2}
    </select>
</mapper>

If you want to use {id} and so on to get parameters, you can use @ param annotation and custom name.

[mapper.EmpMapper.java]
package mapper;

import entity.Employee;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * Interface programming defines a series of methods and maps them to the xml file of sql.
 * Where, the return value type can be Integer, Long, Boolean, void, entity class
 */
public interface EmpMapper {
    public Employee getEmpByIdAndName(@Param("id") Integer id, @Param("name") String name);
}


[config/EmpMapper.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">
<mapper namespace="mapper.EmpMapper">

    <select id="getEmpByIdAndName" resultType="entity.Employee">
        SELECT * FROM emp WHERE emp.id = #{id} AND emp.name = #{name}
    </select>
</mapper>

3. Multiple parameters (too many)

When there are too many parameters, it is obviously not advisable to define them one by one.
At this point, you can pass a custom class or a map collection.

[mapper.EmpMapper]
package mapper;

import entity.Employee;
import org.apache.ibatis.annotations.Param;

import java.util.Map;

/**
 * Interface programming defines a series of methods and maps them to the xml file of sql.
 * Where, the return value type can be Integer, Long, Boolean, void, entity class
 */
public interface EmpMapper {
    public Employee getEmpByMap(Map<String, Object> map);
}



[config/EmpMapper.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">
<mapper namespace="mapper.EmpMapper">

    <select id="getEmpByMap" resultType="entity.Employee">
        SELECT * FROM emp WHERE emp.id = #{id} AND emp.name = #{name}
    </select>
</mapper>



[test.Demo]
package test;

import entity.Employee;
import mapper.EmpMapper;
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 org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;

public class Demo {

    private static SqlSessionFactory getSqlSessionFactory() throws IOException {
        // step1: read configuration file
        String resource = "config/mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);

        // Step 2: create an object of SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        return sqlSessionFactory;
    }

    @Test
    public void testGetEmpByMap() throws IOException {
        // Get an object of SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = Demo.getSqlSessionFactory();
        // Get an instance of SqlSession
        SqlSession session = sqlSessionFactory.openSession();
        try{
            EmpMapper empMapper = session.getMapper(EmpMapper.class);
            Map<String, Object> map = new HashMap<>();
            map.put("id", 1);
            map.put("name", "jack");
            Employee result = empMapper.getEmpByMap(map);
            System.out.println(result);
            // commit is required for addition, deletion and modification
            session.commit();
        }finally {
            // step4: close SqlSession instance
            session.close();
        }
    }
}

4. For List set

You can use the format {list[0]} or {collection[0]} to get parameters.

[mapper.EmpMapper]
package mapper;

import entity.Employee;
import org.apache.ibatis.annotations.Param;

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

/**
 * Interface programming defines a series of methods and maps them to the xml file of sql.
 * Where, the return value type can be Integer, Long, Boolean, void, entity class
 */
public interface EmpMapper {
    public Employee getEmpByList(List<Integer> list);
}


[config/EmpMapper.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">
<mapper namespace="mapper.EmpMapper">

    <select id="getEmpByList" resultType="entity.Employee">
        SELECT * FROM emp WHERE emp.id = #{collection[0]}
    </select>
</mapper>



[test.Demo]
package test;

import entity.Employee;
import mapper.EmpMapper;
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 org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;


public class Demo {

    private static SqlSessionFactory getSqlSessionFactory() throws IOException {
        // step1: read configuration file
        String resource = "config/mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);

        // Step 2: create an object of SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        return sqlSessionFactory;
    }

    @Test
    public void testGetEmpByList() throws IOException {
        // Get an object of SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = Demo.getSqlSessionFactory();
        // Get an instance of SqlSession
        SqlSession session = sqlSessionFactory.openSession();
        try{
            EmpMapper empMapper = session.getMapper(EmpMapper.class);
            List<Integer> list = new ArrayList<>();
            list.add(1);
            list.add(2);
            list.add(3);
            Employee result = empMapper.getEmpByList(list);
            System.out.println(result);
            // commit is required for addition, deletion and modification
            session.commit();
        }finally {
            // step4: close SqlSession instance
            session.close();
        }
    }
}

[perhaps config/EmpMapper.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">
<mapper namespace="mapper.EmpMapper">

    <select id="getEmpByList" resultType="entity.Employee">
        SELECT * FROM emp WHERE emp.id = #{list[0]}
    </select>
</mapper>

5. The difference between {} and ${}

Use {} or ${} to get the value of the parameter.
But {} is a precompiled way to set parameters into sql statements. Similar to PreparedStatement.
${} is to put the value directly in the sql Statement. Similar to Statement.

3. Return value mapping of select -- resultType

1. The return value type is List.

The < Select > tag defines the query operation.
Where id uniquely identifies sql (with the same name as the method of the interface).
resultType is the return value type. If the returned is a list collection, the returned is a generic rather than a list. For example, if list < Employee >, then resultType = Employee, not resultType = list.

[mapper.EmpMapper]
package mapper;

import entity.Employee;

import java.util.List;

/**
 * Interface programming defines a series of methods and maps them to the xml file of sql.
 * Where, the return value type can be Integer, Long, Boolean, void, entity class
 */
public interface EmpMapper {
    public List<Employee> getAllEmp();
}


[config/EmpMapper.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">
<mapper namespace="mapper.EmpMapper">
    <select id="getAllEmp" resultType="entity.Employee">
        SELECT * FROM emp
    </select>
</mapper>



[test.Demo]
package test;

import entity.Employee;
import mapper.EmpMapper;
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 org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class Demo {

    private static SqlSessionFactory getSqlSessionFactory() throws IOException {
        // step1: read configuration file
        String resource = "config/mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);

        // Step 2: create an object of SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        return sqlSessionFactory;
    }

    @Test
    public void testGetEmpById() throws IOException {
        // Get an object of SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = Demo.getSqlSessionFactory();
        // Get an instance of SqlSession
        SqlSession session = sqlSessionFactory.openSession();
        try{
            EmpMapper empMapper = session.getMapper(EmpMapper.class);
            List<Employee> result = empMapper.getAllEmp();
            for(Employee emp : result){
                System.out.println(emp);
            }
        }finally {
            // step4: close SqlSession instance
            session.close();
        }
    }
}

2. The return value type is map.

If a piece of data is returned as a map, resultType = map.
If more than one piece of data is returned as a map, the resultType is the type of value (you need to use @ map key to specify the key).

[mapper.EmpMapper]
package mapper;

import entity.Employee;
import org.apache.ibatis.annotations.MapKey;

import java.util.Map;

/**
 * Interface programming defines a series of methods and maps them to the xml file of sql.
 * Where, the return value type can be Integer, Long, Boolean, void, entity class
 */
public interface EmpMapper {
    // Encapsulate multiple pieces of data, with each primary key as the key and each data as the value
    @MapKey("id")
    public Map<Integer, Employee> getAllMap();

    // Encapsulate a piece of data, and each field name is the key, and the value is value.
    public Map<String, Object> getEmpMap(Integer id);
}


[config/EmpMapper.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">
<mapper namespace="mapper.EmpMapper">
    <select id="getAllMap" resultType="entity.Employee">
        SELECT * FROM emp
    </select>

    <select id="getEmpMap" resultType="map">
        SELECT * FROM emp WHERE id = #{id}
    </select>
</mapper>


[test.Demo]
package test;

import entity.Employee;
import mapper.EmpMapper;
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 org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;

public class Demo {

    private static SqlSessionFactory getSqlSessionFactory() throws IOException {
        // step1: read configuration file
        String resource = "config/mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);

        // Step 2: create an object of SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        return sqlSessionFactory;
    }

    @Test
    public void testGetAllMap() throws IOException {
        // Get an object of SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = Demo.getSqlSessionFactory();
        // Get an instance of SqlSession
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmpMapper empMapper = session.getMapper(EmpMapper.class);
            Map<Integer, Employee> result = empMapper.getAllMap();
            System.out.println(result);
        } finally {
            // step4: close SqlSession instance
            session.close();
        }
    }

    @Test
    public void testGetEmpMap() throws IOException {
        // Get an object of SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = Demo.getSqlSessionFactory();
        // Get an instance of SqlSession
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmpMapper empMapper = session.getMapper(EmpMapper.class);
            Map<String, Object> result = empMapper.getEmpMap(1);
            System.out.println(result);
        } finally {
            // step4: close SqlSession instance
            session.close();
        }
    }
}

IV. return value mapping of select -- resultMap

1. General mapping

Use resultMap to customize the result set mapping rules. Only one of two can be used when using resultMap and resultType.
For example, change the name field of emp to last_name (but the entity class will not be modified).

[mapper.EmpMapper]
package mapper;

import entity.Employee;

import java.util.Map;

/**
 * Interface programming defines a series of methods and maps them to the xml file of sql.
 * Where, the return value type can be Integer, Long, Boolean, void, entity class
 */
public interface EmpMapper {
    public Employee getEmpById(Integer id);
}


[config/EmpMapper.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">
<mapper namespace="mapper.EmpMapper">
    <select id="getEmpById" resultType="entity.Employee">
        SELECT * FROM emp WHERE id = #{id}
    </select>
</mapper>


[entity.Employee]
package entity;

import java.io.Serializable;

/**
 * Entity class
 */
public class Employee implements Serializable {
    private Integer id;
    private String name;
    private Double salary;
    private Integer age;

    public Employee() {
    }

    public Employee(Integer id, String name, Double salary, Integer age) {
        this.id = id;
        this.name = name;
        this.salary = salary;
        this.age = age;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Double getSalary() {
        return salary;
    }

    public void setSalary(Double salary) {
        this.salary = salary;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", salary=" + salary +
                ", age=" + age +
                '}';
    }
}



[test.Demo]
package test;

import entity.Employee;
import mapper.EmpMapper;
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 org.junit.Test;

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

public class Demo {

    private static SqlSessionFactory getSqlSessionFactory() throws IOException {
        // step1: read configuration file
        String resource = "config/mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);

        // Step 2: create an object of SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        return sqlSessionFactory;
    }

    @Test
    public void testGetEmpByIdAndName() throws IOException {
        // Get an object of SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = Demo.getSqlSessionFactory();
        // Get an instance of SqlSession
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmpMapper empMapper = session.getMapper(EmpMapper.class);
            Employee result = empMapper.getEmpById(1);
            System.out.println(result);
        } finally {
            // step4: close SqlSession instance
            session.close();
        }
    }
}

There is bound to be a problem with the resultType mapping.

Use resultMap to customize the mapping rules, and map the last [name] to name.

[config/EmpMapper.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">
<mapper namespace="mapper.EmpMapper">
    <!--
        Use resultMap to define a custom encapsulation rule.
        id is used to uniquely identify the rule.
        Type specifies the return value type
    -->
    <resultMap id="myEmp" type="entity.Employee">
        <! -- use id tag to specify primary key, column to specify column, property to specify mapped property name -- >
        <id column="id" property="id"></id>
        <! -- result is used to specify a normal column -- >
        <result column="last_name" property="name"></result>
    </resultMap>
    <select id="getEmpById" resultMap="myEmp">
        SELECT * FROM emp WHERE id = #{id}
    </select>
</mapper>

2. Association mapping cascade query

Add a department table and use its primary key as the foreign key of the emp table.

CREATE TABLE department(
    deptId INT(11) PRIMARY KEY AUTO_INCREMENT,
    deptName VARCHAR(255)
);

ALTER TABLE emp ADD COLUMN deptId INT(11);

ALTER TABLE emp ADD CONSTRAINT fk_emp_department
FOREIGN KEY(deptId) REFERENCES department(deptId)

Use resultMap to map the result set of the query to the final displayed result set. That is (column - "property).
You can assign values using cascading properties. Format: A.B

[mapper.EmpMapper]
package mapper;

import entity.Employee;

/**
 * Interface programming defines a series of methods and maps them to the xml file of sql.
 * Where, the return value type can be Integer, Long, Boolean, void, entity class
 */
public interface EmpMapper {
    public Employee getEmpAndDeptById(Integer id);
}


[config/EmpMapper.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">
<mapper namespace="mapper.EmpMapper">
    <resultMap id="myMap" type="entity.Employee">
        <id column="e_id" property="id"></id>
        <result column="e_name" property="name"></result>
        <result column="e_salary" property="salary"></result>
        <result column="e_age" property="age"></result>
        <result column="d_id" property="department.deptId"></result>
        <result column="d_name" property="department.deptName"></result>
    </resultMap>
    <select id="getEmpAndDeptById" resultMap="myMap">
        SELECT e.id e_id, e.last_name e_name, e.age e_age, e.salary e_salary, d.deptId d_id, d.deptName d_name
          FROM emp e, department d
         WHERE e.deptId = d.deptId
           AND e.id = #{id}
    </select>
</mapper>


[entity.Employee.java]
package entity;

import java.io.Serializable;

/**
 * Entity class
 */
public class Employee implements Serializable {
    private Integer id;
    private String name;
    private Double salary;
    private Integer age;
    private Department department;

    public Employee() {
    }

    public Employee(Integer id, String name, Double salary, Integer age) {
        this.id = id;
        this.name = name;
        this.salary = salary;
        this.age = age;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Double getSalary() {
        return salary;
    }

    public void setSalary(Double salary) {
        this.salary = salary;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Department getDepartment() {
        return department;
    }

    public void setDepartment(Department department) {
        this.department = department;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", salary=" + salary +
                ", age=" + age +
                ", department=" + department +
                '}';
    }
}



[entity.Department.java]
package entity;

public class Department {
    private Integer deptId;
    private String deptName;

    public Integer getDeptId() {
        return deptId;
    }

    public void setDeptId(Integer deptId) {
        this.deptId = deptId;
    }

    public String getDeptName() {
        return deptName;
    }

    public void setDeptName(String deptName) {
        this.deptName = deptName;
    }

    @Override
    public String toString() {
        return "Department{" +
                "deptId=" + deptId +
                ", deptName='" + deptName + '\'' +
                '}';
    }
}



[test.Demo]
package test;

import entity.Employee;
import mapper.EmpMapper;
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 org.junit.Test;

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

public class Demo {

    private static SqlSessionFactory getSqlSessionFactory() throws IOException {
        // step1: read configuration file
        String resource = "config/mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);

        // Step 2: create an object of SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        return sqlSessionFactory;
    }

    @Test
    public void testGetEmpByIdAndName() throws IOException {
        // Get an object of SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = Demo.getSqlSessionFactory();
        // Get an instance of SqlSession
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmpMapper empMapper = session.getMapper(EmpMapper.class);
            Employee result = empMapper.getEmpAndDeptById(1);
            System.out.println(result);
        } finally {
            // step4: close SqlSession instance
            session.close();
        }
    }
}

3. association mapping -- associate common objects with association

In addition to cascading attribute assignments, you can use the association tag to assign values to associated objects.
Modify the cascade attribute assignment code in the above example.

[config/EmpMapper.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">
<mapper namespace="mapper.EmpMapper">
    <resultMap id="myMap" type="entity.Employee">
        <id column="e_id" property="id"></id>
        <result column="e_name" property="name"></result>
        <result column="e_salary" property="salary"></result>
        <result column="e_age" property="age"></result>
        <!--
            property Properties used to specify the mapping
            JavaType Use to specify the type of mapping
         -->
        <association property="department" javaType="entity.Department">
            <result column="d_id" property="deptId"></result>
            <result column="d_name" property="deptName"></result>
        </association>
    </resultMap>
    <select id="getEmpAndDeptById" resultMap="myMap">
        SELECT e.id e_id, e.last_name e_name, e.age e_age, e.salary e_salary, d.deptId d_id, d.deptName d_name
          FROM emp e, department d
         WHERE e.deptId = d.deptId
           AND e.id = #{id}
    </select>
</mapper>

4. association mapping step by step query association

Then the above example is modified.
Using the select attribute of association, you can call another sql statement to implement the step-by-step query operation.

[config/EmpMapper.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">
<mapper namespace="mapper.EmpMapper">
    <resultMap id="myMap" type="entity.Employee">
        <id column="id" property="id"></id>
        <result column="last_name" property="name"></result>
        <result column="salary" property="salary"></result>
        <result column="age" property="age"></result>
        <association property="department" select="getDeptById" column="deptId">
        </association>
    </resultMap>
    <select id="getEmpAndDeptById" resultMap="myMap">
        SELECT * FROM emp WHERE id = #{id}
    </select>

    <select id="getDeptById" resultType="entity.Department">
        SELECT * FROM department WHERE deptId = #{deptId}
    </select>
</mapper>

5. Association mapping -- association collection object -- Collection

For example, the mapped object is a list collection.
Use the collection label to define encapsulation rules for collection types. Its property ofType is the generic of the collection, and property is the mapped property name.

[entity.Department]
package entity;

import java.util.List;

public class Department {
    private Integer deptId;
    private String deptName;
    private List<Employee> employeeList;

    public Integer getDeptId() {
        return deptId;
    }

    public void setDeptId(Integer deptId) {
        this.deptId = deptId;
    }

    public String getDeptName() {
        return deptName;
    }

    public void setDeptName(String deptName) {
        this.deptName = deptName;
    }

    public List<Employee> getEmployeeList() {
        return employeeList;
    }

    public void setEmployeeList(List<Employee> employeeList) {
        this.employeeList = employeeList;
    }

    @Override
    public String toString() {
        return "Department{" +
                "deptId=" + deptId +
                ", deptName='" + deptName + '\'' +
                ", employeeList=" + employeeList +
                '}';
    }
}



[mapper.EmpMapper.java]
package mapper;

import entity.Department;

/**
 * Interface programming defines a series of methods and maps them to the xml file of sql.
 * Where, the return value type can be Integer, Long, Boolean, void, entity class
 */
public interface EmpMapper {
    public Department getDeptById(Integer id);
}



[config/EmpMapper.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">
<mapper namespace="mapper.EmpMapper">

    <resultMap id="myMap" type="entity.Department">
        <id column="deptId" property="deptId"></id>
        <result column="deptName" property="deptName"></result>
        <!--
            collection Tags are used to define encapsulation rules for collection types.
            ofType Is a generic of a collection
            property Is the mapped property name
        -->
        <collection property="employeeList" ofType="entity.Employee">
            <id column="e_id" property="id"></id>
            <result column="e_name" property="name"></result>
            <result column="e_salary" property="salary"></result>
            <result column="e_age" property="age"></result>
        </collection>
    </resultMap>
    <select id="getDeptById" resultMap="myMap">
        SELECT d.deptId deptId, d.deptName deptName, e.id e_id, e.last_name e_name, e.age e_age, e.salary e_salary
          FROM  department d
     LEFT JOIN emp e
            ON  e.deptId = d.deptId
         WHERE d.deptId = #{id}
    </select>
</mapper>


[test.Demo]
package test;

import entity.Department;
import entity.Employee;
import mapper.EmpMapper;
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 org.junit.Test;

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

public class Demo {

    private static SqlSessionFactory getSqlSessionFactory() throws IOException {
        // step1: read configuration file
        String resource = "config/mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);

        // Step 2: create an object of SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        return sqlSessionFactory;
    }

    @Test
    public void testGetDeptById() throws IOException {
        // Get an object of SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = Demo.getSqlSessionFactory();
        // Get an instance of SqlSession
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmpMapper empMapper = session.getMapper(EmpMapper.class);
            Department result = empMapper.getDeptById(1);
            System.out.println(result);
            for(Employee employee : result.getEmployeeList()){
                System.out.println(employee);
            }
        } finally {
            // step4: close SqlSession instance
            session.close();
        }
    }
}

6. Association mapping step by step query collection

Slightly modify the code in the above example. Step by step query can be realized.

[config/EmpMapper.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">
<mapper namespace="mapper.EmpMapper">

    <resultMap id="myMap" type="entity.Department">
        <id column="deptId" property="deptId"></id>
        <result column="deptName" property="deptName"></result>
        <collection property="employeeList" select="getEmpByDeptId" column="deptId">
            <id column="e_id" property="id"></id>
            <result column="e_name" property="name"></result>
            <result column="e_salary" property="salary"></result>
            <result column="e_age" property="age"></result>
        </collection>
    </resultMap>
    <select id="getDeptById" resultMap="myMap">
        SELECT * FROM department WHERE deptId = #{id}
    </select>

    <select id="getEmpByDeptId" resultType="entity.Employee">
        SELECT * FROM emp WHERE deptId = #{deptId}
    </select>
</mapper>

Note:
Step by step query operation for collection and association.

[If you want to pass multiple parameters, the format is:]
    column="{key1 = value1, key2 = value2}"
    
[Format in use]
    #{key1}  perhaps #{key2}

7. Association mapping discriminator discriminator

Using discriminator discriminator, you can monitor the value of a column and perform different behaviors according to different values.

For the data with deptId =1, change the Department name to 1. For data with deptId =2, the Department name does not change.

[mapper.EmpMapper]
package mapper;

import entity.Department;

import java.util.List;

/**
 * Interface programming defines a series of methods and maps them to the xml file of sql.
 * Where, the return value type can be Integer, Long, Boolean, void, entity class
 */
public interface EmpMapper {
    public List<Department> getDeptById(Integer id);
}


[config/EmpMapper.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">
<mapper namespace="mapper.EmpMapper">

    <resultMap id="myMap" type="entity.Department">
        <!--
            discriminator Tags are discriminators that perform different behaviors based on the values of a column
            javaType Specifies the type of value for a column
            column Used to specify a column
        -->
        <discriminator javaType="Integer" column="deptId">
            <case value="1" resultType="entity.Department">
                <id column="deptId" property="deptId"></id>
                <id column="deptId" property="deptName"></id>
            </case>
            <case value="2" resultType="entity.Department">
                <id column="deptId" property="deptId"></id>
                <result column="deptName" property="deptName"></result>
            </case>
        </discriminator>
    </resultMap>
    <select id="getDeptById" resultMap="myMap">
        SELECT * FROM department
    </select>
</mapper>


[test.Demo]
package test;

import entity.Department;
import entity.Employee;
import mapper.EmpMapper;
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 org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class Demo {

    private static SqlSessionFactory getSqlSessionFactory() throws IOException {
        // step1: read configuration file
        String resource = "config/mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);

        // Step 2: create an object of SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        return sqlSessionFactory;
    }

    @Test
    public void testGetDeptById() throws IOException {
        // Get an object of SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = Demo.getSqlSessionFactory();
        // Get an instance of SqlSession
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmpMapper empMapper = session.getMapper(EmpMapper.class);
            List<Department> result = empMapper.getDeptById(1);
            System.out.println(result);
        } finally {
            // step4: close SqlSession instance
            session.close();
        }
    }
}

Posted by literom on Mon, 21 Oct 2019 00:45:11 -0700

Programmer Group