MyBatis guidelines for the most comprehensive use

1. Why MyBatis

Our job as programmers boils down to working with data. The original way to use Java to manipulate databases is JDBC.

Let’s take a look at how to operate a database using JDBC:

// 1. Load the configuration file
Properties pro=new Properties();
pro.load(new FileReader("resource/jdbc.properties"));
// 2. Obtain the database connection information from the configuration file
String url=pro.getProperty("url");
String user=pro.getProperty("user");
String password=pro.getProperty("password");
String driver=pro.getProperty("driver");
// 3. Load the database driver
Class.forName(driver);
// 4. Create a database connection
Connection conn = DriverManager.getConnection(url, user, password);
 // 5. SQL statements
String sql = "select * from s_admin where username=? and password=?";
// 3. Create an object to execute SQL
ps = conn.prepareStatement(sql);
/ / 4 to? The assignment
ps.setString(1, username);
ps.setString(2, password);
// 5. Execute SQL
ResultSet rs = ps.executeQuery();
// 6. If the data is queried, the data is returned
if (rs.next()) {
    Admin admin = new Admin();
    admin.setUsername(rs.getString("username"));
    admin.setPassword(rs.getString("password"));
    return admin;
// 7. Otherwise return null
} else {
    return null;
}
Copy the code

After looking at the code above, we found JDBC problems:

1. Each operation creates connection and Statement objects, and then closes and destroys these objects.

2.ResultSet cannot help us complete the automatic conversion between database and entity objects, so we need to manually assign values.

3. The code is redundant, and the business operation is mixed with the database operation, so the development efficiency is too low.

I don’t want to use JDBC anymore. Is there a solution that can help me solve JDBC problems?

Yes, hero of the worldMyBatisOn the colorful clouds came.

2. MyBatis profile

Official website address:

https://mybatis.org/mybatis-3/
Copy the code

MyBatis is a Java-based persistence layer framework that encapsulates JDBC internally.

Developers only need to focus on SQL statements and do not have to deal with the tedious process of loading drivers and creating connections.

MyBatis configures SQL statements either through XML or annotations, then executes the SQL and maps the results to Java objects and returns them.

Noun explanation:

Framework: A framework is a reusable design in a system, which is equivalent to a tool that encapsulates many functions.

Persistence layer: the layer that deals with the database.

3. Environment construction

1. Create databases and tables

We use Navicat to create the database mybatis_demo, and then create the user table.

CREATE TABLE `user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'id'.`name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'name'.`sex` varchar(1) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'gender'.`age` int DEFAULT NULL COMMENT 'age',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;
Copy the code

2. Create a Maven project

File -> New -> Project -> Maven Introduction of depend on

<dependencies>
    <! -- Junit test -->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
        <scope>test</scope>
    </dependency>
    <! -- Mysql driver -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.27</version>
    </dependency>
    <! -- mybatis -->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.3</version>
    </dependency>
</dependencies>
Copy the code

3. Create an entity class

The User class:

public class User {
    private int id;
    private String name;
    private String sex;
    private int age;

    public int getId(a) {
        return id;
    }

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

    public String getName(a) {
        return name;
    }

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

    public String getSex(a) {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public int getAge(a) {
        return age;
    }

    public void setAge(int age) {
        this.age = age; }}Copy the code

4. Create Dao interfaces

public interface UserDao {
    /** * Get all user information *@return* /
    List<User>  getAll(a);
}
Copy the code

5. Create an SQL mapping file

This is a configuration file used by Mybatis to write SQL statements.

It is an XML file, also known as a Mapper file because the SQL statements are contained within the Mapper tag. Typically, one table corresponds to one XML file.

Rule: The file name must be the same as the interface.

UserDao.xml:


      
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xxl.dao.UserDao">

  <select id="getAll" resultType="com.xxl.model.User">
      select * from user
  </select>

</mapper>

Copy the code

6. Create the MyBatis master profile

The master configuration file is also an XML file.

mybatis-config.xml:


      
<! DOCTYPEconfiguration
        PUBLIC "- / / mybatis.org//DTD Config / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    
    <! SQL > select * from mybatis;
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING" />
    </settings>

    <! -- Configure the environment -->
    <environments default="mysql">
        <! Mysql > create mysql
        <environment id="mysql">
            <! -- Transaction type -->
            <transactionManager type="JDBC"></transactionManager>
            <! POOLED -->
            <dataSource type="POOLED">
                <! -- Configure the database connection information -->
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis_demo"/>
                <property name="username" value="root"/>
                <property name="password" value="12345678"/>
            </dataSource>
        </environment>
    </environments>

    <! Mapping configuration file location -->
    <mappers>
        <mapper resource="mapper/UserDao.xml"/>
    </mappers>
</configuration>

Copy the code

7. Write test classes

public class UserTest {

    @Test
    public void testUser(a) throws IOException {
        // 1. Read the configuration file
        InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
        // create SqlSessionFactory
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        // 3. Obtain the SqlSession object
        SqlSession session = factory.openSession();
        // 4. Use SqlSession to create a proxy object for the Dao interface
        UserDao userDao = session.getMapper(UserDao.class);
        // 5. Execute the interface method
        List<User> userList = userDao.getAll();
        userList.forEach(user ->{
            System.out.println("Name:"+user.getName()+", gender:+user.getSex()+", age:+user.getAge()); }); }}Copy the code

8. Execution result9. Complete directory structure

4. MyBatisUtil

Reading configuration files, fetching SQLSession objects, and so on in the above test code are repetitive operations that can be encapsulated in a utility class.

MyBatisUtil:

public class MyBatisUtil {

    / / define SqlSessionFactory
    private static SqlSessionFactory factory;

    // Use static blocks to create SqlSessionFactory only once
    static {
        try {
            // Read the configuration file
            InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
            // Create the SqlSessionFactory object
            factory = new SqlSessionFactoryBuilder().build(in);
        } catch(Exception e) { e.printStackTrace(); }}// Get the SqlSession object
    public static SqlSession getSqlSession(a) {
        SqlSession sqlSession = factory.openSession();
        return sqlSession;
    }

    // Commit the transaction
    public static void commit(SqlSession sqlSession) {
        if (null! = sqlSession) { sqlSession.commit(); } close(); }// Rollback the transaction
    public static void rollBack(SqlSession sqlSession) {
        if (null! = sqlSession) { sqlSession.rollback(); } close(); }/ / close the SqlSession
    public static void close(a) {
        SqlSession sqlSession = getSqlSession();
        if (null! = sqlSession) { sqlSession.close(); }}}Copy the code

5. Add, delete, modify and check

Here we use MyBatisUtil to add, delete, change, and query users.

UserDao:

public interface UserDao {

    // Get all user information
    List<User> getAll(a);

    // Add a user
    boolean add(User user);

    // Modify the user
    boolean update(User user);

    // Delete the user
    boolean delete(int id);
}
Copy the code

UserDao.xml:


      
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xxl.dao.UserDao">

    <select id="getAll" resultType="com.xxl.model.User">
        select * from user
    </select>

    <insert id="add" parameterType="com.xxl.model.User">
        insert into user(name,sex,age) values(#{name},#{sex},#{age})
    </insert>

    <update id="update">
        update user set name = #{name}, sex = #{sex},age = #  {age} where id = #{id}
    </update>

    <delete id="delete">
        delete from user where id = #{id}
    </delete>
</mapper>
Copy the code

1. Query users

@Test
public void testGetAll(a){

        // 1. Obtain the SqlSession object
        SqlSession session = MyBatisUtil.getSqlSession();
        // 2. Use SqlSession to create a proxy object for the Dao interface
        UserDao userDao = session.getMapper(UserDao.class);
        // 3. Execute interface methods
        List<User> userList = userDao.getAll();
        userList.forEach(user ->{
            System.out.println("Name:"+user.getName()+", gender:+user.getSex()+", age:+user.getAge());
        });
        // 4. Close the SqlSession
        MyBatisUtil.close();
}
Copy the code

2. Add users

@Test
public void add(a){
    SqlSession session = null;
    try{
        // 1. Obtain the SqlSession object
        session = MyBatisUtil.getSqlSession();
        // 2. Use SqlSession to create a proxy object for the Dao interface
        UserDao userDao = session.getMapper(UserDao.class);
        // 3. Execute interface methods
        User user = new User();
        user.setName("Zhang Wuji");
        user.setAge(31);
        user.setSex("Male");
        userDao.add(user);
        // 4. Commit transaction and close SqlSession
        MyBatisUtil.commit(session);
    }catch (Exception e){
        e.printStackTrace();
        // Rollback the transactionMyBatisUtil.rollBack(session); }}Copy the code

3. Modify the user

@Test
public void update(a){
    SqlSession session = null;
    try{
        // 1. Obtain the SqlSession object
        session = MyBatisUtil.getSqlSession();
        // 2. Use SqlSession to create a proxy object for the Dao interface
        UserDao userDao = session.getMapper(UserDao.class);
        // 3. Execute interface methods
        User user = new User();
        user.setId(3);
        user.setName("Zhang Wuji");
        user.setAge(31);
        user.setSex("Male");
        userDao.update(user);
        // 4. Commit transaction and close SqlSession
        MyBatisUtil.commit(session);
    }catch (Exception e){
        e.printStackTrace();
        // Rollback the transactionMyBatisUtil.rollBack(session); }}Copy the code

4. Delete the user

@Test
public void delete(a){
    SqlSession session = null;
    try{
        // 1. Obtain the SqlSession object
        session = MyBatisUtil.getSqlSession();
        // 2. Use SqlSession to create a proxy object for the Dao interface
        UserDao userDao = session.getMapper(UserDao.class);
        // 3. Execute interface methods
        userDao.delete(3);
        // 4. Commit transaction and close SqlSession
        MyBatisUtil.commit(session);
    }catch (Exception e){
        e.printStackTrace();
        // Rollback the transactionMyBatisUtil.rollBack(session); }}Copy the code

6. MyBatist Core configuration file

Mybatis -config. XML is the main configuration file of Mybatis. All the configurations are in the Configuration TAB.

It mainly includes: define alias, configure data source, configure mapper file and so on.

1. Define an alias

The previous resultType we set in the mapper file is the full pathname.

<select id="getAll" resultType="com.xxl.model.User">
    select * from user
</select>
Copy the code

To simplify the code, MyBatis allows us to give the full path name a simple alias, usually the class name of the entity class. We can configure mybatis-config.xml like this:

<! -- Configure alias -->
<typeAliases>
    <package name="com.xxl.model"/>
</typeAliases>
Copy the code

Com.xxl.model.User = com.xxl.model.User = com.xxl.model.

<select id="getAll" resultType="user">
    select * from user
</select>
Copy the code

2. Configure the data source

We use the dataSource tag to configure the various parameters for connecting to the database, where the dataSource type indicates that the connection pool is used to configure the dataSource.

<! -- Configure the environment -->
<environments default="mysql">
    <! Mysql > create mysql
    <environment id="mysql">
        <! -- Transaction type -->
        <transactionManager type="JDBC"></transactionManager>
        <! POOLED -->
        <dataSource type="POOLED">
            <! -- Configure the database connection information -->
            <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
            <property name="url" value="jdbc:mysql://localhost:3306/mybatis_demo"/>
            <property name="username" value="root"/>
            <property name="password" value="12345678"/>
        </dataSource>
    </environment>
</environments>
Copy the code

In order to facilitate the maintenance of database connection parameters, we will generally store these parameters in a special file, and MyBatis master configuration file will read the parameter data of database connection from this file.

Create a jdbc.properties file under the Resources directoryIntroduce the jdbc.properties file using the properties tag in the main configuration file

<properties resource="jdbc.properties"/>
Copy the code

Modify the dataSource

<dataSource type="POOLED">
    <! -- Configure the database connection information -->
    <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>
Copy the code

3. Configure a path for searching for the mapper file

 <mappers>
      <mapper resource="mapper/UserDao.xml"/>
  </mappers>
Copy the code

4. The transaction

Mybatis framework is the encapsulation of JDBC, so the transaction control mode of Mybatis framework is actually the COMMIT () and rollback() of JDBC Connection objects.

 <transactionManager type="JDBC" />
Copy the code

Type =”JDBC” : indicates that the JDBC transaction management mechanism is used. However, MyBatis has turned off the automatic submission function by default and changed it to manual submission.

So in the above test code, we had to manually commit the transaction after adding, deleting, and changing it.

Auto-commit transaction

MyBatis can be set to commit transaction automatically if openSession parameter is set to true.

// Read the configuration file
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
// Create the SqlSessionFactory object
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
// Get the SqlSession object
SqlSession sqlSession = factory.openSession(true);
Copy the code

7. Mapper mapping file

After we’ve defined the Dao interface, we’ll also define an implementation class, DaoImpl. The Mybatis framework helps us automatically generate a proxy object for the interface based on mapper.xml.

// Create a proxy object for the Dao interface using SqlSession
UserDao userDao = session.getMapper(UserDao.class);
Copy the code

Then we can call the methods in the interface, so this mapper file is important.

1. Convention format

① The DAO class name must be the same as the namespace in the mapper file.

② The DAO method name must be the same as the ID of the add, delete, change, and query operation in the mapper file.

③ The return value of the DAO method must be the same type as the return value of the operation in the Mapper file.

④ Method in DAOThe parameter typesThe input parameter type must be the same as that in the mapper file.

Note: SQL statements in mapper files do not add semicolons, id cannot be repeated.

2. Encapsulate the output

1) resultType

The result type of the converted ResultSet is obtained after executing the SQL, using the fully qualified name or alias of the type. If a collection is returned, it should be set to the type contained in the collection, not the collection itself. ResultType and resultMap cannot be used together.

A simple type

Interface methods

 String getName(int id);
Copy the code

Mapper file:

<select id="getName" resultType="java.lang.String">
  select name from user where id = #{id}
</select>
Copy the code

Object type

Interface methods

 User getUser(int id);
Copy the code

Mapper file:

<select id="getUser" resultType="user">
  select * from user where id = #{id}
</select>
Copy the code

The Map type

Interface methods

@MapKey("id")
Map<String,Object> getUser(a);
Copy the code

Mapper file:

<select id="getUser" resultType="java.util.Map">
    select * from user
</select>
Copy the code

(2) the resultMap

When column names in database tables are inconsistent with entity class attribute names, resultMap can be used to define mappings between SQL results and Java object attributes.

Such as:

<resultMap id="userMap" type="com.xxl.model.User">
   <id column="s_id" property="id"/>
    <result column="s_name" property="name"/>
    <result column="s_age" property="age"/>
    <result column="s_sex" property="sex"/>
</resultMap>

<select id="getAll" resultMap="userMap">
    select s_id,s_name,s_age,s_sex from user
</select>
Copy the code

3. Fuzzy query

Method 1: The value in {} must be value

<select id="findByName"  resultType="user">
    select * from user where name like '%${value}%'
</select>
Copy the code

Way 2

<select id="findByName"  resultType="user">
    select * from user where name like "%"#{name}"%"
</select>
Copy the code

4. Add/modify processing null values

Mybatis does not allow null values when performing add or modify operations, so in order to make a column value null when adding or modifying, you must add the jdbcType attribute.

Such as:

<insert id="add" >
  insert into user(name,sex,age) values(#{name,jdbcType=VARCHAR},#{sex,jdbcType=INTEGER},#{age,jdbcType=INTEGER})
</insert>
Copy the code

8. MyBatis refs

In Mapper files, we can use #{param} and ${param} to get the parameters passed in the interface.

#{param} is the way to use placeholders, and ${param} is the way to concatenate SQL statements.

Using $runs the risk of SQL injection, so use #{param} in the following examples.

A parameter

#{} : Parameter values can be obtained with any name.

Such as:

<select id="getUser">
    select * from user where id = #{uId}
</select>

<delete id="delete">
    delete from user where id = #{user_id}
</delete>
Copy the code

Multiple parameters

1. Pass multiple parameters

Mybatis will put these parameters into the map set. You can specify the name of the parameter in mapper file by @param. Such as:

Interface:

List<Student> getUser(@Param("userName") String name,@Param("userSex") int age);
Copy the code

Mapper file:

<select id="getUser" resultType="user">
select id,name,sex,age from user where name = #{userName} and age = #{userSex}
</select>
Copy the code

2. Pass objects

The transfer parameter is JavaBean, and the value of the attribute can be obtained by the attribute name.

Interface:

// Modify the user
boolean update(User user);
Copy the code

Mapper file:

<update id="update">
    update user set name = #{name}, sex = #{sex},age = #{age} where id = #{id}
</update>
Copy the code

3. Transfer the Map

When the parameter passed is map, the mapper file uses # {key} to get the parameter value.

Such as:

Map<String,Object> data = new HashMap<String,Object>(); 
data.put("userName"."Do you know, Sir?");
data.put("userAge".21);
Copy the code

Interface:

List<User> getUser(Map<String,Object> map);
Copy the code

Mapper file:

<select id="getUser" resultType="user">
    select * from user where name = #{userName} and age = #{userAge}
</select>
Copy the code

9. The dynamic SQL

Dynamic SQL is one of the powerful features of MyBatis, which is mainly used to solve the uncertain query conditions. It can greatly simplify the operation of assembling SQL.

1. If the tag

The If tag is used to make simple judgments. When the value of test in the tag is true, it concatenates the SQL fragment it contains into the SQL statement in which it resides.

Syntax format:

<if test="Conditions">SQL fragment</if>
Copy the code

Such as:

<select id="getUser" resultType="user">
select id,name,age,sex from user
where 1=1
<if test="name ! = null and name ! = "">
and name = #{name}
</if>
</select>
Copy the code

2. Where the tag

In the example above, if the name parameter exists, we have to add where 1=1 to ensure the SQL statement is correct.

The Where tag is used to solve the problem of the first AND or OR in the Where keyword and condition in an SQL statement.

Such as:

<select id="getUser" resultType="user">
    select id,name,age,sex from user
   <where>
       <if test="name ! = null and name ! = "">
           and name = #{name}
       </if>
   </where>
</select>
Copy the code

3. The trim TAB

The trim tag can add or remove a specified character before or after a conditional SQL statement.

Prefix: Adds the prefix
PrefixOverrides: Removes the prefix
Suffix: add a suffix
SuffixOverrides: removes the suffix

Such as:

<select id="getUser" resultType="user">
    select id,name,age,sex from user
    <trim prefix="where" suffixOverrides="and">
        <if test="name ! = null and name ! = "">
            name = #{name} and
        </if>
    </trim>
</select>
Copy the code

4. Choose (When, otherwise) label

The Choose tag is mainly used for branch determination, similar to the Switch case in Java, which only satisfies one of all branches.

Such as:

<select id="getUser"  resultType="user">
    select id,name,age,sex from user
    <where>
        <choose>
            <when test="id ! = null and id ! = "">
                id = #{id}
            </when>
            <when test="name ! = null and name ! = "">
                name =  #{name}
            </when>
            <otherwise>Sex = 'male'</otherwise>
        </choose>
    </where>
</select>
Copy the code

5. Set the tag

The set tag is used to solve the problem of excessive commas in SQL statements during modification operations.

Such as:

<update id="update">
    update user
    <set>
        <if test="name ! = null and name ! = "">
            name = #{name},
        </if>
        <if test="sex ! = null and sex ! = "">
            sex = #{sex},
        </if>
        <if test="age ! = null and age ! = "">
            age = #{age}
        </if>
    </set>
    where id = #{id}
</update>
Copy the code

6. The foreach tag

The foreach tag is primarily used for loop iteration.

Collection: A collection to iterate over
Item: The element currently iterated out of the collection
Open: start character
Close: indicates the end character
Separator: The separator between elements
Iterating is the List collection: index represents the index of the current element
Iterated Map collection: index represents the key of the current element

Such as:

<select id="getUserList"  resultType="user">
    select id,name,age,sex from user  where  id in
    <foreach collection="ids" item="userId"  open="(" close=")"  separator="," >
        #{userId}
    </foreach>
</select>
Copy the code

7. The SQL TAB

SQL tags are used to extract reusable SQL fragments. They extract the same and frequently used SQL fragments and define them individually for multiple reference.

Such as:

<sql id="BaseSql">
    id,name,age,sex
</sql>

<select id="getUserList" resultType="user">
    select
    <include refid="BaseSql" />
    from user
</select>
Copy the code

10. Annotated development

By observing Mapper files, we found that the core of Mapper files is to do interface mapping with Dao, and then write some SQL statements inside.

Can I write SQL statements in the interface without mapper files? Yes, MyBatsi provides annotated development for us.

1. Modify the master configuration file

Although mapper files are not needed to write, but we need to write SQL statements in the interface, you must let MyBatis know. So you need to tell MyBatis where the annotated interface is.

<! Specify the location of the annotated DAO interface -->
<mappers>
   <package name="com.xxl.dao"/>
</mappers>
Copy the code

Annotated development

public interface UserDao {
    // Get all user information
    @Select("select * from user")
    List<User> getUser(a);

    // Add a user
    @Insert("insert into user(name,sex,age) values(#{name},#{sex},#{age})")
    boolean add(User user);

    // Modify the user
    @Update("update user set name = #{name}, sex = #{sex},age = #{age} where id = #{id}")
    boolean update(User user);

    // Delete the user
    @Delete("delete from user where id = #{id}")
    boolean delete(int id);
}
Copy the code

3. The test

  @Test
  public void testGetAll(a){
      // 1. Obtain the SqlSession object
      SqlSession session = MyBatisUtil.getSqlSession();
      // 2. Use SqlSession to create a proxy object for the Dao interface
      UserDao userDao = session.getMapper(UserDao.class);
      // 3. Execute interface methods
      List<User> userList = userDao.getUser();
      userList.forEach(user ->{
          System.out.println("Name:"+user.getName()+", gender:+user.getSex()+", age:+user.getAge());
      });
      // 4. Close the SqlSession
      MyBatisUtil.close();
  }
Copy the code

Execution Result:

11. Relevance

One to one

For example: one person for one ID card, one ID card for one person. Use association to handle one-to-one relationships in MyBatis mapper file.

Create a table:

A record in table A corresponds to A record in table B. The primary key of A serves as the foreign key of table B. It depends on which table you’re centered on.

The following example uses the primary key (ID) of the user information table as the foreign key (info_id) of the user table.

User table: user

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(20) DEFAULT NULL COMMENT 'name',
  `sex` varchar(1) DEFAULT NULL COMMENT 'gender',
  `age` int(11) DEFAULT NULL COMMENT 'age',
  `info_id` int(11) DEFAULT NULL COMMENT 'User information ID'.PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;
Copy the code

INSERT INTO `mybatis_demo`.`user`(`id`, `name`, `sex`, `age`, `info_id`) VALUES (1.'Joe'.'male'.18.1);
INSERT INTO `mybatis_demo`.`user`(`id`, `name`, `sex`, `age`, `info_id`) VALUES (2.'bill'.'male'.19.2);

Copy the code

User information table: user_info

CREATE TABLE `user_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `number` varchar(20) DEFAULT NULL COMMENT 'Id Card No.',
  `address` varchar(50) DEFAULT NULL COMMENT 'Home Address'.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
Copy the code

INSERT INTO `mybatis_demo`.`user_info`(`id`, `number`, `address`) VALUES (1.'411121200302174025'.'The Bund');
INSERT INTO `mybatis_demo`.`user_info`(`id`, `number`, `address`) VALUES (2.'411121200222154554'.Sanlitun, Beijing);
Copy the code

User entity class: User

public class User {
    private int id;
    private String name;
    private String sex;
    private int age;
    // User information attributes
    private UserInfo userInfo;

    public UserInfo getUserInfo(a) {
        return userInfo;
    }

    public void setUserInfo(UserInfo userInfo) {
        this.userInfo = userInfo;
    }

    public int getId(a) {
        return id;
    }

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

    public String getName(a) {
        return name;
    }

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

    public String getSex(a) {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public int getAge(a) {
        return age;
    }

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

    @Override
    public String toString(a) {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\' ' +
                ", sex='" + sex + '\' ' +
                ", age=" + age +
                '} '; }}Copy the code

User information:

public class UserInfo {
    private int id;
    private String number;
    private String address;

    public int getId(a) {
        return id;
    }

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

    public String getNumber(a) {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }

    public String getAddress(a) {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString(a) {
        return "UserInfo{" +
                "id=" + id +
                ", number='" + number + '\' ' +
                ", address='" + address + '\' ' +
                '} '; }}Copy the code

UserDao:

public interface UserDao {
    /** * Get all user information, including id information *@return* /
    List<User> getAll(a);
}
Copy the code

UserDao.xml:


      
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xxl.dao.UserDao">
    
    <resultMap type="com.xxl.model.User" id="userMap">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="age" property="age"/>
        <result column="sex" property="sex"/>
        <! JavaType: type of relationship property -->
        <association property="userInfo" javaType="com.xxl.model.UserInfo" >
            <id column="id" property="id" />
            <result column="number" property="number"/>
            <result column="address" property="address"/>
        </association>
    </resultMap>

    <select id="getAll" resultMap="userMap">
        select user.id,user.name,user.age,user.sex,
               info.id,info.number,info.address
        from user
        left join user_info info
        on  user.info_id = info.id
    </select>
</mapper>
Copy the code

Test code:

@Test
  public void testUserGetAll(a){
      // 1. Obtain the SqlSession object
      SqlSession session = MyBatisUtil.getSqlSession();
      // 2. Use SqlSession to create a proxy object for the Dao interface
      UserDao userDao = session.getMapper(UserDao.class);
      // 3. Execute interface methods
      List<User> userList = userDao.getAll();
      userList.forEach(user ->{
          System.out.println(user+""+user.getUserInfo());
      });
      // 4. Close the SqlSession
      MyBatisUtil.close();
  }
Copy the code

Test results:

More than a pair of

For example, one department corresponds to multiple employees, and one employee belongs to the same department. Use collection to handle one-to-many relationships in MyBatis mapper file.

Create a table:

One record in table A corresponds to multiple records in table B. The primary key of A serves as the foreign key of table B.

The following example uses the primary key (ID) of the department table as the foreign key (dep_id) of the employee table.

Department table: Department

CREATE TABLE `department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL COMMENT 'Department Name'.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
Copy the code

INSERT INTO `mybatis_demo`.`department`(`id`, `name`) VALUES (1.'研发部');
INSERT INTO `mybatis_demo`.`department`(`id`, `name`) VALUES (2.'Personnel Department');
INSERT INTO `mybatis_demo`.`department`(`id`, `name`) VALUES (3.'Sales Department');
Copy the code

Employee table: Employee

CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL COMMENT 'Name of Employee',
  `age` int(11) DEFAULT NULL COMMENT 'Age of employee',
  `sex` varchar(1) DEFAULT NULL COMMENT 'Gender of employee',
  `dep_id` int(11) DEFAULT NULL COMMENT 'department id'.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;
Copy the code

INSERT INTO `mybatis_demo`.`employee`(`id`, `name`, `age`, `sex`, `dep_id`) VALUES (1.'Zhang Wuji'.21.'male'.1);
INSERT INTO `mybatis_demo`.`employee`(`id`, `name`, `age`, `sex`, `dep_id`) VALUES (2.'Zhou Zhiruo'.19.'woman'.2);
INSERT INTO `mybatis_demo`.`employee`(`id`, `name`, `age`, `sex`, `dep_id`) VALUES (3.'zhao'.19.'woman'.3);
INSERT INTO `mybatis_demo`.`employee`(`id`, `name`, `age`, `sex`, `dep_id`) VALUES (4.'small zhao'.20.'woman'.2);
INSERT INTO `mybatis_demo`.`employee`(`id`, `name`, `age`, `sex`, `dep_id`) VALUES (5.'spider son'.19.'woman'.1);
Copy the code

Department entities:

public class Department {
    private String id;
    private String name;
    private List<Employee> employeeList;

    public String getId(a) {
        return id;
    }

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

    public String getName(a) {
        return name;
    }

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

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

    public void setEmployeeList(List<Employee> employeeList) {
        this.employeeList = employeeList;
    }
    @Override
    public String toString(a) {
        return "Department{" +
                "id='" + id + '\' ' +
                ", name='" + name + '\' ' +
                '} '; }}Copy the code

Employee Entity:

public class Employee {
    private int id;
    private String name;
    private String sex;
    private int age;
    public int getId(a) {
        return id;
    }

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

    public String getName(a) {
        return name;
    }

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

    public String getSex(a) {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public int getAge(a) {
        return age;
    }

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

    @Override
    public String toString(a) {
        return "Employee{" +
                "id=" + id +
                ", name='" + name + '\' ' +
                ", sex='" + sex + '\' ' +
                ", age=" + age +
                '} '; }}Copy the code

DepartmentDao:

public interface DepartmentDao {

    List<Department> getAll(a);
}
Copy the code

DepartmentDao.xml:


      
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xxl.dao.DepartmentDao">

    <resultMap type="com.xxl.model.Department" id="deptMap">
        <id column="depId" property="id"/>
        <result column="depName" property="name"/>
        <! JavaType: ofType: generic type in the collection: full path name of class -->
        <collection property="employeeList" javaType="list" ofType="com.xxl.model.Employee">
            <id column="empId" property="id"/>
            <result  column="empName" property="name"/>
            <result column="age" property="age"/>
            <result column="sex" property="sex"/>
        </collection>
    </resultMap>

    <select id="getAll" resultMap="deptMap">
        select d.id depId,d.name depName,e.id empId,e.name empName,e.age,e.sex
        from department d
        left join employee e
        on d.id  = e.dep_id
    </select>
</mapper>
Copy the code

Test code:

@Test
public void testDepartmentGetAll(a){
    // 1. Obtain the SqlSession object
    SqlSession session = MyBatisUtil.getSqlSession();
    // 2. Use SqlSession to create a proxy object for the Dao interface
    DepartmentDao departmentDao = session.getMapper(DepartmentDao.class);
    // 3. Execute interface methods
    List<Department> departmentList = departmentDao.getAll();
    departmentList.forEach(department ->{
        System.out.println("Department:"+department+"Employee:"+department.getEmployeeList());
    });
    // 4. Close the SqlSession
    MyBatisUtil.close();
}
Copy the code

Test results: Many to many

For example, a student can have more than one course, and a course can belong to more than one student. Many-to-many can be understood as a combination of one-to-many and many-to-one. To achieve many-to-many, there is usually an intermediate table (also known as an associated table), in the form of many-to-many.

Create a table:

Student: Student

CREATE TABLE `student` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(20) DEFAULT NULL COMMENT 'name',
  `sex` varchar(1) DEFAULT NULL COMMENT 'gender',
  `age` int(11) DEFAULT NULL COMMENT 'age'.PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
Copy the code

INSERT INTO `mybatis_demo`.`student`(`id`, `name`, `sex`, `age`) VALUES (1.'Joe'.'male'.18);
INSERT INTO `mybatis_demo`.`student`(`id`, `name`, `sex`, `age`) VALUES (2.'bill'.'woman'.21);
INSERT INTO `mybatis_demo`.`student`(`id`, `name`, `sex`, `age`) VALUES (3.'Cathy'.'male'.19);
Copy the code

A: Course

CREATE TABLE `course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL COMMENT 'Course Name'.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
Copy the code

INSERT INTO `mybatis_demo`.`course`(`id`, `name`) VALUES (1.'Java development');
INSERT INTO `mybatis_demo`.`course`(`id`, `name`) VALUES (2.'Data structure');
INSERT INTO `mybatis_demo`.`course`(`id`, `name`) VALUES (3.'Big Data Development');
INSERT INTO `mybatis_demo`.`course`(`id`, `name`) VALUES (4.'Cloud Life Development');
Copy the code

Student – course association table: student_course

CREATE TABLE `student_course` (
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL.PRIMARY KEY (`student_id`,`course_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Copy the code

INSERT INTO `mybatis_demo`.`student_course`(`student_id`, `course_id`) VALUES (1.2);
INSERT INTO `mybatis_demo`.`student_course`(`student_id`, `course_id`) VALUES (1.3);
INSERT INTO `mybatis_demo`.`student_course`(`student_id`, `course_id`) VALUES (2.1);
INSERT INTO `mybatis_demo`.`student_course`(`student_id`, `course_id`) VALUES (2.2);
INSERT INTO `mybatis_demo`.`student_course`(`student_id`, `course_id`) VALUES (3.3);
INSERT INTO `mybatis_demo`.`student_course`(`student_id`, `course_id`) VALUES (3.4);
Copy the code

Student Entities:

public class Student {
    private int id;
    private String name;
    private String sex;
    private int age;
    private List<Course> courseList;

    public int getId(a) {
        return id;
    }

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

    public String getName(a) {
        return name;
    }

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

    public String getSex(a) {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public int getAge(a) {
        return age;
    }

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

    public List<Course> getCourseList(a) {
        return courseList;
    }

    public void setCourseList(List<Course> courseList) {
        this.courseList = courseList;
    }

    @Override
    public String toString(a) {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\' ' +
                ", sex='" + sex + '\' ' +
                ", age=" + age +
                '} '; }}Copy the code

Course Entities:

public class Course {
    private int id;
    private String name;
    public int getId(a) {
        return id;
    }

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

    public String getName(a) {
        return name;
    }

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

    @Override
    public String toString(a) {
        return "Course{" +
                "id=" + id +
                ", name='" + name + '\' ' +
                '} '; }}Copy the code

StudentDao:

public interface StudentDao {

   List<Student> getAll(a);
}
Copy the code

StudentDao.xml:


      
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xxl.dao.StudentDao">

    <resultMap type="com.xxl.model.Student" id="studentMap">
        <id column="studentId" property="id"/>
        <result column="studentName" property="name"/>
        <result column="age" property="age"/>
        <result column="sex" property="sex"/>
        <! JavaType: ofType: generic type in the collection: full path name of class -->
        <collection property="courseList" javaType="list" ofType="com.xxl.model.Course">
            <id column="courseId" property="id"/>
            <result column="courseName" property="name"/>
        </collection>
    </resultMap>

    <select id="getAll" resultMap="studentMap">select s.id studentId,s.name studentName,s.age,s.sex, c.id courseId,c.name courseName from student s left join student_course sc on s.id = sc.student_id left join course c on  sc.course_id = c.id</select>
</mapper>
Copy the code

Test code:

@Test
  public void testStudentGetAll(a){
      // 1. Obtain the SqlSession object
      SqlSession session = MyBatisUtil.getSqlSession();
      // 2. Use SqlSession to create a proxy object for the Dao interface
      StudentDao studentDao = session.getMapper(StudentDao.class);
      // 3. Execute interface methods
      List<Student> studentList = studentDao.getAll();
      studentList.forEach(student ->{
          System.out.println("Student:"+student+"Course:"+student.getCourseList());
      });
      // 4. Close the SqlSession
      MyBatisUtil.close();
  }
Copy the code

Test results:

12. Paging plug-ins

When we wrote pages, we not only had to customize a Page class, we had to concatenate SQL statements, and finally we had to encapsulate data. His mother opened the door for nausea — it was gross. “

To solve this problem, MyBatis provides a general-purpose pagination tool: PageHelper.

Use steps:

1. Introduce dependencies

<! -- Paging plugin -->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.1.4 ensuring</version>
</dependency>
Copy the code

2. Modify the master configuration file

Before the Environments TAB add:

<plugins>
    <plugin interceptor="com.github.pagehelper.PageInterceptor" />
</plugins>
Copy the code

3. Prepare data4. Test code

  @Test
  public void testGetAll(a){
      // 1. Obtain the SqlSession object
      SqlSession session = MyBatisUtil.getSqlSession();
      // 2. Use SqlSession to create a proxy object for the Dao interface
      UserDao userDao = session.getMapper(UserDao.class);
      // 3. Execute interface methods
      Page page = PageHelper.startPage(2.3);
      List<User> userList = userDao.getUser();
      System.out.println("Current page:"+page.getPageNum());
      System.out.println("Number of items per page:"+page.getPageSize());
      System.out.println("Total number of items:"+page.getTotal());
      System.out.println("Total pages:"+page.getPages());
      System.out.println("-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --");
      userList.forEach(user ->{
          System.out.println("Name:"+user.getName()+", gender:+user.getSex()+", age:+user.getAge());
      });
      // 4. Close the SqlSession
      MyBatisUtil.close();
  }
Copy the code

5. Execution result

MyBatis guidelines for the most comprehensive use

1. Why MyBatis

2. MyBatis profile

3. Environment construction

4. MyBatisUtil

5. Add, delete, modify and check

6. MyBatist Core configuration file

7. Mapper mapping file

8. MyBatis refs

9. The dynamic SQL

10. Annotated development

11. Relevance

12. Paging plug-ins

Related Posts

On the importance of infiltration information collection

CompletableFuture

Introduction to mysql Innodb ACID implementation principle