Official Chinese document of Mybatis3

1, Build process

Overall project structure:

1. Maven dependency

<!--jdbc to configure-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.24</version>
</dependency>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.6</version>
</dependency>

2. Configuration file 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>
  <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">
        <property name="driver" value="${driver}"/>
        <property name="url" value="${url}"/>
        <property name="username" value="${username}"/>
        <property name="password" value="${password}"/>
      </dataSource>
    </environment>
  </environments>
  <mappers>
    <mapper resource="org/mybatis/example/ExampleMapper.xml"/>
  </mappers>
</configuration>

3. Encapsulating the MybatisUtils utility class

package com.zhanc.utils;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

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

/**
 * @ClassName MybatisUtils
 * @Author Zhanc
 * @Version 1.0
 * @Date 2021/10/10 22:00
 * @Description sqlSessionFactory
 **/
public class MybatisUtils {

    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            String res="mybatis-config.xml";
            InputStream inp= Resources.getResourceAsStream(res);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inp);
        }catch (IOException e){
            e.printStackTrace();
        }

    }

    public static SqlSession getSession(){
        return sqlSessionFactory.openSession();
    }
}

4. Other

• Basic pojo object: User.class
• Personally, an interface in the dao package corresponds to a map configuration file (of course, you can choose to use annotations, which will be explained later)

2, CURD

1. Interface (UserMapper.class):

public interface UserMapper {
    List<User> getUserList();

    User getUserById(int id);

    List<User> getUserListByLimit(Map<String,Integer> map);

    List<User> getUserListByRowBounds();

    List<User> getUserListLike(String str);

    int addUser(User user);

    int delUser(int id);

    int updateUser(User user);

}

2. Configuration file (UserMapper.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="com.zhanc.dao.UserMapper">
    <select id="getUserList" resultType="com.zhanc.pojo.User">
        select * from tbl_temp
    </select>

    <select id="getUserById" parameterType="int" resultType="com.zhanc.pojo.User">
        select * from tbl_temp where id = #{id}
    </select>

    <select id="getUserListByLimit" parameterType="map" resultType="com.zhanc.pojo.User">
        select * from tbl_temp limit #{startIndex},#{pageSize}
    </select>

    <select id="getUserListByRowBounds" resultType="com.zhanc.pojo.User">
        select * from tbl_temp
    </select>

    <select id="getUserListLike" parameterType="String" resultType="com.zhanc.pojo.User">
        select * from tbl_temp where username like #{str}
    </select>

    <insert id="addUser" useGeneratedKeys="true" keyProperty="id" parameterType="com.zhanc.pojo.User" >
        insert into tbl_temp(username,password,dept_id) values(#{username},#{password},#{dept_id})
    </insert>

    <update id="updateUser" parameterType="com.zhanc.pojo.User" useGeneratedKeys="true" keyProperty="id">
        update tbl_temp set username=#{username},password=#{password} where id=#{id}
    </update>

    <delete id="delUser" parameterType="int">
        delete from tbl_temp where id = #{id}
    </delete>
</mapper>

3. Usage example (Junit is used for testing here)

@Test
public void getUserListTest() {
    SqlSession session = MybatisUtils.getSession();

    //Mode 1 (recommended)
    UserMapper userMapper = session.getMapper(UserMapper.class);
    List<User> userList = userMapper.getUserList();

    //Mode II
    //List<User> users = session.selectList("com.zhanc.dao.UserDao.getUserList");

    for (User user : userList) {
        System.out.println(user);
    }
    session.close();
}

3, Use of annotations

1. Writing method

public interface UserMapper {
    @Select("select * from tbl_temp")
    List<User> getUserList();

    @Select("select * from tbl_temp where id = #{id}")
    User getUserById(int id);
}

2. Configuration (mybatis config)

<mappers>
    <!--Note that the attribute here is class-->
    <mapper class="com.zhanc.dao.UserMapper"/>
</mappers>

3. Attention

• You can use annotations without xml configuration. Of course, you can also use annotations and xml at the same time, depending on your choice and your project

• Annotation is very convenient, but it will be very confusing in the face of complex statements

  Using annotations to map simple statements will make the code more concise, but for slightly more complex statements, Java annotations will not only fail, but also make your already complex SQL statements more chaotic. Therefore, if you need to do some very complex operations, it is best to use XML to map statements.

  It is entirely up to you and your team to choose how to configure the mapping and whether you think you should unify the form of mapping statement definition. In other words, never stick to one way. You can easily migrate and switch between annotation based and XML based statement mapping.

  ——From Mybatis official website

4, Use of Map

1. Interface writing method (UserMapper.class)

public interface UserMapper {
    List<User> getUserListByLimit(Map<String,Integer> map);
}

2. Configuration file (UserMapper.xml)

<mapper namespace="com.zhanc.dao.UserMapper">
    <select id="getUserListByLimit" parameterType="map" resultType="com.zhanc.pojo.User">
        select * from tbl_temp limit #{startIndex},#{pageSize}
    </select>
</mapper>

5, Fuzzy query

1. Interface writing method (UserMapper.class)

public interface UserMapper {
    List<User> getUserListLike(String str);
}

2. Configuration file (UserMapper.xml)

<mapper namespace="com.zhanc.dao.UserMapper">
    <select id="getUserListLike" parameterType="String" resultType="com.zhanc.pojo.User">
        select * from tbl_temp where username like #{str}
    </select>
</mapper>

6, Log factory

The log factories supported by Mybatis include SLF4J, Apache Commons Logging, Log4j 2, Log4j, and JDK logging

Configuration file (mybatis config. XML):

<configuration>
  <settings>
    ...
    <setting name="logImpl" value="LOG4J"/>
    ...
  </settings>
</configuration>

The optional value s here are: SLF4J, LOG4J, LOG4J2 and JDK_LOGGING,COMMONS_LOGGING,STDOUT_LOGGING,NO_LOGGING

Here I only choose two of them to demonstrate: STDOUT_LOGGING and LOG4J

1,STDOUT_LOGGING

It is a standard log factory. You don't need to import additional jar packages or configure Maven

Add < setting name = "logimpl" value = "stdout_logging" / > in the settings node of the configuration file (mybatis config. XML) and run it again

2,LOG4J

Log4j is an open source project of Apache. You need to import additional jar packages or configure Maven

• Maven dependency:

  <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.17</version>
  </dependency>
  

• Write the log.properties file (copy directly if you don't understand it):

  #Output the log information with the level of DEBUG to the two destinations of console and file. The definitions of console and file are in the following code
  log4j.rootLogger=DEBUG,console,file
  
  #Settings related to console output
  log4j.appender.console = org.apache.log4j.ConsoleAppender
  log4j.appender.console.Target = System.out
  log4j.appender.console.Threshold=DEBUG
  log4j.appender.console.layout = org.apache.log4j.PatternLayout
  log4j.appender.console.layout.ConversionPattern=[%c]-%m%n
  
  #Settings related to file output
  log4j.appender.file = org.apache.log4j.RollingFileAppender
  log4j.appender.file.File=./log/com.zhanc.log
  log4j.appender.file.MaxFileSize=10mb
  log4j.appender.file.Threshold=DEBUG
  log4j.appender.file.layout=org.apache.log4j.PatternLayout
  log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n
  
  #Log output level
  log4j.logger.org.mybatis=DEBUG
  log4j.logger.java.sql=DEBUG
  log4j.logger.java.sql.Statement=DEBUG
  log4j.logger.java.sql.ResultSet=DEBUG
  log4j.logger.java.sql.PreparedStatement=DEBUG
  

7, Pagination

1. Use SQL statement limit paging

• Interface writing method (UserMapper.class)

public interface UserMapper {
    List<User> getUserListByLimit(Map<String,Integer> map);
}

• Configuration file (UserMapper.xml)

<mapper namespace="com.zhanc.dao.UserMapper">
    <select id="getUserListByLimit" parameterType="map" resultType="com.zhanc.pojo.User">
        select * from tbl_temp limit #{startIndex},#{pageSize}
    </select>
</mapper>

• Use example:

@Test
public void getUserListByLimitTest() {
    SqlSession session = MybatisUtils.getSession();
    UserMapper userDao = session.getMapper(UserMapper.class);
    HashMap<String, Integer> map = new HashMap<>();
    map.put("startIndex", 1);
    map.put("pageSize", 3);
    List<User> userList = userDao.getUserListByLimit(map);
    for (User user : userList) {
        System.out.println(user);
    }
    session.close();
}

2. Use rowborders to turn pages (not recommended officially)

• Interface writing method (UserMapper.class)

public interface UserMapper {
    List<User> getUserListByRowBounds();
}

• Configuration file (UserMapper.xml)

<mapper namespace="com.zhanc.dao.UserMapper">
    <select id="getUserListByRowBounds" resultType="com.zhanc.pojo.User">
        select * from tbl_temp
    </select>
</mapper>

• Use example

@Test
public void getUserListByRowBoundsTest() {
    SqlSession sqlSession = MybatisUtils.getSession();

    RowBounds rowBounds = new RowBounds(1, 2);

    List<User> userList = sqlSession.selectList("com.com.zhanc.dao.UserDao.getUserListByRowBounds", null, rowBounds);

    for (User user : userList) {
        System.out.println(user);
    }
    sqlSession.close();
}