Mybatis study notes

mybatis official website: https://mybatis.org/mybatis-3/zh/getting-started.html

Build environment

Import dependencies and modify filtering rules

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.18</version>
    </dependency>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.4</version>
    </dependency>

	<dependency>
	    <groupId>org.projectlombok</groupId>
	    <artifactId>lombok</artifactId>
	    <version>1.18.12</version>
	    <scope>provided</scope>
	</dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.13</version>
        <scope>test</scope>
    </dependency>
</dependencies>

<build>
    <resources>
        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>false</filtering>
        </resource>
        <resource>
            <directory>src/main/resources</directory>
            <includes>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>false</filtering>
        </resource>
    </resources>
</build>

Create the 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>
    <!--  Set alias of entity class  -->
    <typeAliases>
		
    </typeAliases>

    <!--  Database connection  -->
    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url"
                          value="jdbc:mysql://localhost:3306/angenin?useSSL=false&amp;serverTimezone=Asia/Shanghai"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>

    <!--  register mapper file  -->
    <mappers>

    </mappers>
</configuration>

Solution to address red reporting:

Writing tool classes

public class MybatisUtil {

    private static SqlSessionFactory sqlSessionFactory;
    private static String resource = "mybatis-config.xml";

    static {
        try (InputStream inputStream = Resources.getResourceAsStream(resource)) {
            // Create SqlSessionFactory factory object
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    // Get SqlSession object
    public static SqlSession getSqlSession() {
	    // After passing in true to openSession(), the generated SqlSession will automatically commit the transaction
        return sqlSessionFactory.openSession();
    }

}

Create data table

create table t_users(
	`id` int primary key auto_increment,
	`name` varchar(15) not null,
	`password` varchar(20) not null
);

insert into t_users(name, password)values("111", "111");
insert into t_users(name, password)values("222", "222");

Create user entity class

@Data
@NoArgsConstructor
@AllArgsConstructor
public class User implements Serializable {
    private int id;
    private String name;
    private String password;
}

Create UserMapper interface

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

Create 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.angenin.dao.UserMapper">
    <select id="getUserList" resultType="com.angenin.pojo.User">
        select * from t_users;
    </select>
</mapper>

In mybatis config Register UserMapper in XML

<!--  register mapper file  -->
<mappers>
	<!-- Table of contents by slash/separate -->
    <mapper resource="com/angenin/dao/UserMapper.xml"/>
</mappers>

test

@Test
public void test01() {
    // Get sqlSession object
    SqlSession sqlSession = MybatisUtil.getSqlSession();

    // Get UserMapper object
    UserMapper userDAO = sqlSession.getMapper(UserMapper.class);
    for (User user : userDAO.getUserList()) {
        System.out.println(user);
    }

    // Close the sqlSession object
    sqlSession.close();
}

Directory structure:

If mapper If you want to put the XML file in the resources directory, the directory path you created must be com / Angelin / Dao (Note: the resources directory is separated by oblique bar / directory). For the path of the corresponding class, put the mapper file in this directory. If you only use COM angenin. Dao naming is actually just a layer of directory. You can also see that the generated files are not put together under the target package.

Notes on SqlSessionFactoryBuilder, SqlSessionFactory and SqlSession:

The namespace of the mapper file corresponds to the fully qualified class name of the Dao interface, which is also associated with the mapper through Dao. The id of the select tag corresponds to the method name of the method in the Dao interface, which is associated through id.

CRUD

UserMapper

public interface UserMapper {
	// Get all data
    List<User> getUserList();
	// Get single data
    User getOneUser(int id);
	// Add a new user (you do not need to set the id, and the database is automatically added)
    void saveUser(User user);
	// Modify a user (by id)
    void changeUser(User user);
	// Delete a user
    void removeUser(int id);
}

UserMapper.xml

   <select id="getOneUser" parameterType="int" resultType="com.angenin.pojo.User">
       select * from t_users where id=#{id};
   </select>

    <insert id="saveUser" parameterType="com.angenin.pojo.User">
        insert into t_users(name,password)values(#{name},#{password});
    </insert>

    <update id="changeUser" parameterType="com.angenin.pojo.User">
        update t_users set name=#{name},password=#{password} where id=#{id};
    </update>

    <delete id="removeUser" parameterType="int">
        delete from t_users where id=#{id};
    </delete>

Test:

	// Test query single user
    @Test
    public void test02() {
        // Get sqlSession object
        SqlSession sqlSession = MybatisUtil.getSqlSession();

        // Get UserDAO object
        UserMapper userDAO = sqlSession.getMapper(UserMapper.class);
        System.out.println(userDAO.getOneUser(1));

        // Close the sqlSession object
        sqlSession.close();
    }

	// Test adding a single user
    @Test
    public void test03() {
        // Get sqlSession object
        SqlSession sqlSession = MybatisUtil.getSqlSession();

        // Get UserDAO object
        UserMapper userDAO = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setName("test");
        user.setPassword("test");
        userDAO.saveUser(user);

        // Commit transaction
        sqlSession.commit();

        // Close the sqlSession object
        sqlSession.close();
    }

	// Test and modify individual users
    @Test
    public void test04() {
        // Get sqlSession object
        SqlSession sqlSession = MybatisUtil.getSqlSession();

        // Get UserDAO object
        UserMapper userDAO = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setId(38);
        user.setName("test2");
        user.setPassword("test2");
        userDAO.changeUser(user);

        // Commit transaction
        sqlSession.commit();

        // Close the sqlSession object
        sqlSession.close();
    }

	// Test delete single user
    @Test
    public void test05() {
        // Get sqlSession object
        SqlSession sqlSession = MybatisUtil.getSqlSession();

        // Get UserDAO object
        UserMapper userDAO = sqlSession.getMapper(UserMapper.class);
        userDAO.removeUser(38);

        // Commit transaction
        sqlSession.commit();

        // Close the sqlSession object
        sqlSession.close();
    }

Note: since mybatis turns off automatic submission by default, transactions need to be submitted manually after execution. If the program does not report an error but there is no change in the database, this may be the reason.

#The difference between {} and ${}

#{}: can precompile and use the position #{} in the sql statement? Instead, assign values only when JDBC is executed to prevent sql injection.
${}: String splicing, which directly splices the corresponding values into sql statements, has the problem of sql injection, but when using order by sorting, use ${}.

Map

When we only need to modify some fields in a record, we can use map to modify the specified fields without creating new objects.

UserMapper

    void changeUser2(Map<String, Object> map);

UserMapper.xml

    <update id="changeUser2" parameterType="map">
        update t_users set name=#{username},password=#{userpwd} where id=#{userid};
    </update>

test

    @Test
    public void test06() {
        // Get sqlSession object
        SqlSession sqlSession = MybatisUtil.getSqlSession();

        // Get UserDAO object
        UserMapper userDAO = sqlSession.getMapper(UserMapper.class);
        Map<String, Object> map = new HashMap<>();
        // Change password by id
        map.put("userid", 40);
        map.put("userpwd", "pwd");
        userDAO.changeUser2(map);

        // Commit transaction
        sqlSession.commit();

        // Close the sqlSession object
        sqlSession.close();
    }

mapper's #{} field name finds value through the key in the map. If the type is class, it is the class attribute name.

Fuzzy query

    List<User> getUserLike();
    <select id="getUserLike" parameterType="string" resultType="com.angenin.pojo.User">
        select * from t_users where name like #{value};
    </select>
    @Test
    public void test07() {
        // Get sqlSession object
        SqlSession sqlSession = MybatisUtil.getSqlSession();

        // Get UserDAO object
        UserMapper userDAO = sqlSession.getMapper(UserMapper.class);
        List<User> list = userDAO.getUserLike("%test%");
        for (User user : list) {
            System.out.println(user);
        }

        // Close the sqlSession object
        sqlSession.close();
    }

Core configuration

Import configuration files (properties)

Create dB Properties file to extract the configuration information of the database

db.driver=com.mysql.cj.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/angenin?useSSL=false&serverTimezone=Asia/Shanghai
db.username=root
db.password=123456
    <!--Import external profile-->
    <properties resource="db.properties">
    	<!--If the external configuration file name is not the same as the external configuration file name, you can set the internal configuration file name here-->
        <!--<property name="db.username" value="root"/>-->
        <!--<property name="db.password" value="11111"/>-->
    </properties>

    <!--  Database connection  -->
    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${db.driver}"/>
                <property name="url" value="${db.url}"/>
                <property name="username" value="${db.username}"/>
                <property name="password" value="${db.password}"/>
            </dataSource>
        </environment>
    </environments>

Note: the labels in the configuration file need to be written in a certain order.

properties -> settings -> typeAliases -> typeHandlers -> objectFactory -> objectWrapperFactory -> reflectorFactory -> plugins -> environments -> databaseIdProvider -> mappers

settings

file: https://mybatis.org/mybatis-3/zh/configuration.html#settings

An example of a fully configured settings element is as follows:
mybatis-config.xml

<settings>
  <setting name="cacheEnabled" value="true"/>
  <setting name="lazyLoadingEnabled" value="true"/>
  <setting name="multipleResultSetsEnabled" value="true"/>
  <setting name="useColumnLabel" value="true"/>
  <setting name="useGeneratedKeys" value="false"/>
  <setting name="autoMappingBehavior" value="PARTIAL"/>
  <setting name="autoMappingUnknownColumnBehavior" value="WARNING"/>
  <setting name="defaultExecutorType" value="SIMPLE"/>
  <setting name="defaultStatementTimeout" value="25"/>
  <setting name="defaultFetchSize" value="100"/>
  <setting name="safeRowBoundsEnabled" value="false"/>
  <setting name="mapUnderscoreToCamelCase" value="false"/>
  <setting name="localCacheScope" value="SESSION"/>
  <setting name="jdbcTypeForNull" value="OTHER"/>
  <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>
</settings>

There are many settings. The key is the following three:

  • cacheEnabled: cache. true means enabled.
  • Lazyloading enabled: lazy loading. true is enabled.
  • logImpl: log. Slf4j | log4j | log4j2 | JDK can be set_ LOGGING | COMMONS_ LOGGING | STDOUT_ LOGGING | NO_ LOGGING.

Aliases (typeAliases)

In the mapper file, we are required to use the fully qualified class name, which will make our code look very long. We can set the class alias in the mybatis configuration file, and there is no need to write the fully qualified class name in the mapper.

<!--Set alias of entity class  -->
<typeAliases>
    <typeAlias type="com.angenin.pojo.User" alias="User"/>
</typeAliases>

There is another label package under the typeAliases attribute, which automatically sets the alias by scanning all classes under the specified package. Generally, we use the typeAlias label. The alias is set to uppercase, while the package scanning defaults to lowercase, which makes it easy for us to distinguish which alias is set.

<typeAliases>
    <package name="com.angenin.pojo"/>
</typeAliases>

We can add @ Alias to the entity class under the scanning package to customize the Alias.

@Alias("u")
public class User implements Serializable {}


We just need to remember that the underlined ones are the basic types, and the ones that are not marked are the packaging classes.

Database environments

Multiple environments can be configured in environments. Specify which environment to use through the default attribute.

    <!--  default by test,So it's using id by test Environment  -->
    <environments default="test">
        <environment id="mysql">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                ...
            </dataSource>
        </environment>
        
        <environment id="test">
            <transactionManager type=""></transactionManager>
            <dataSource type=""></dataSource>
        </environment>
    </environments>

Transaction manager

There are two types of transaction managers (that is, type="[JDBC|MANAGED]):

  • JDBC: using JDBC's commit and rollback facilities, it relies on the connection obtained from the data source to manage the transaction scope.
  • MANAGED: this configuration does little. Just understand it.

Data source
There are three built-in data source types (that is, type="[UNPOOLED|POOLED|JNDI]):

  • UNPOOLED: the implementation of this data source will open and close the connection every time it is requested, that is, there is no pool.
  • POOLED: use the concept of "pool" to organize JDBC connection objects, avoiding the initialization and authentication time necessary to create new connection instances.
  • JNDI: in order to be used in containers such as EJB or application server, the container can configure the data source centrally or externally, and then place a data source reference of JNDI context.

You can view the document for specific configuration parameters:
https://mybatis.org/mybatis-3/zh/configuration.html#environments

mappers

mappers have two methods: mapper and package, while mapper has three methods.

url is not recommended.

In addition to resource, all other methods require the interface and mapper files to be in the same package and have the same name

Lifecycle and scope

Different scopes and lifecycle categories are critical, as incorrect use can lead to very serious concurrency problems.

  • SqlSessionFactoryBuilder
    You can reuse SqlSessionFactoryBuilder to create multiple instances of SqlSessionFactory, but it's best not to keep it all the time.

  • SqlSessionFactory
    Once created, it should always exist during the running of the application. There is no reason to discard it or re create another instance. The best practice of using SqlSessionFactory is not to create it repeatedly during the running of the application. Therefore, the best scope of SqlSessionFactory is the application scope. There are many ways to do this. The simplest is to use singleton mode or static singleton mode..

  • SqlSession
    Each thread should have its own SqlSession instance. The instance of SqlSession is not thread safe, so it cannot be shared. Therefore, its best scope is the request or method scope. Each request corresponds to a new SqlSession and is destroyed immediately after use.

Solve the problem of inconsistency between database and Java entity class attribute names

We change the attribute name of the User class from password to pwd, so that the attribute name of our entity class is inconsistent with the field name of the table corresponding to the database. The inconsistency will lead to unsuccessful assignment and the corresponding attribute is null.

terms of settlement:

  1. SQL statements use as

    <select id="getUserList" resultType="User">
        select password as pwd from t_users;
    </select>
    
  2. Encapsulated as a result set map
    You only need to encapsulate inconsistent attributes.

    <resultMap id="UserMap" type="com.angenin.pojo.User">
        <!-- column The field name of the corresponding database table, property corresponding java Attribute name of entity class -->
        <result property="pwd" column="password"/>
    </resultMap>
    
    
    <select id="getUserList" resultMap="UserMap">
        select * from t_users;
    </select>
    

More specific usage of ResultMap will be described below.

journal

logImpl of settings to set:

  • SLF4J
  • LOG4J [Master]
  • LOG4J2
  • JDK_LOGGING
  • COMMONS_LOGGING
  • STDOUT_LOGGING [mastering]
  • NO_LOGGING
  1. Use STDOUT_LOGGING
    Built in, can be used directly.

    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
    
  2. Use LOG4J

    1. The jar package of log4j needs to be imported.
    <dependency>
        <groupId>log4j</groupId>
        <artifactId>log4j</artifactId>
        <version>1.2.17</version>
    </dependency>
    
    1. Create the configuration file log4j for log4j properties
    # Configuration root
    log4j.rootLogger=debug,console
    # Log output to console display
    log4j.appender.console=org.apache.log4j.ConsoleAppender
    log4j.appender.console.Target=System.out
    log4j.appender.console.layout=org.apache.log4j.PatternLayout
    log4j.appender.console.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %-5p %c{1}:%L - %m%n	
    

    Baidu needs more detailed configuration.

    1. Set log to log4j
    <settings>
        <setting name="logImpl" value="LOG4J"/>
    </settings>
    

limit paging

Reduce the amount of data processing.

UserMapper

   // paging
   List<User> getUserLimit(Map<String, Integer> map);

UserMapper.xml

   <select id="getUserLimit" parameterType="map" resultMap="UserMap">
       select * from t_users limit #{startIndex},#{pageSize};
   </select>

test

   @Test
   public void test08() {
       // Get sqlSession object
       SqlSession sqlSession = MybatisUtil.getSqlSession();

       // Get UserDAO object
       UserMapper userDAO = sqlSession.getMapper(UserMapper.class);
       Map<String, Integer> map = new HashMap<>();
       map.put("startIndex", 2);
       map.put("pageSize", 2);
       List<User> list = userDAO.getUserLimit(map);
       for (User user : list) {
           System.out.println(user);
       }

       // Close the sqlSession object
       sqlSession.close();
   }

About the problem that the second parameter of limit is - 1: when the second parameter is - 1, it will be searched from the first parameter to the end. mysql thinks it is a bug and has been repaired. Therefore, the second parameter of limit in mysql higher version cannot be less than 0.

Paging plug-in: PageHelper
The bottom layer of the paging plug-in also uses limit. Those who are interested can learn to use it by referring to the document.

Annotation development

Put UserMapper XML is removed from dao package, then @ Select annotation is added to getUserList method of UserMapper interface, and sql statement is written in the annotation. The usage of addition, deletion and modification is similar.

UserMapper

public interface UserMapper {
	// Query all
    @Select("select * from t_users")
    List<User> getUserList();
	
	// Query single
    @Select("select * from t_users where id=#{id}")
    User getOneUser(@Param("id") int id);

	// newly added
    @Insert("insert into t_users(name,password)values(#{name},#{pwd})")
    void saveUser(User user);

	// modify
    @Update("update t_users set name=#{name}, password=#{pwd} where id=#{id}")
    void changeUser(User user);
    
    // delete
    @Delete("delete from t_users where id=#{id}")
    void removeUser(@Param("id") int id);
}

About @ Param: if there is @ Param, #{} the attribute name corresponds to the attribute name specified in @ Param, and the parameters of basic type or String type need to be added. If the attribute name is specified, the reference type does not need to be added. It can also be omitted if there is only one parameter of basic type or String.

Binding interface

    <mappers>
        <!-- Table of contents by slash/separate -->
<!--        <mapper resource="com/angenin/dao/*Mapper.xml"/>-->
        <mapper class="com.angenin.dao.UserMapper"/>
    </mappers>

Using annotations to map simple statements will make the code more concise, but for slightly more complex statements, Java annotations are not only inadequate, 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.

Bottom layer: dynamic proxy + reflection

resultMap handles many to one

Create teacher table and student table (student table is associated with teacher table through foreign key)

create table teacher(
	`id` int primary key auto_increment,
	`name` varchar(15) default null
);

insert into teacher(`name`)values("Miss Li");

create table student(
	`id` int primary key auto_increment,
	`name` varchar(15) default null,
	`tid` int,
	foreign key(tid) references teacher(id)
);

insert into student(`name`,`tid`)values("Xiao Ming",1);
insert into student(`name`,`tid`)values("Xiao Hong",1);
insert into student(`name`,`tid`)values("Xiao Gang",1);

Create student and teacher entity classes

@Data
public class Student {
    private int id;
    private String name;
    private Teacher teacher;
}
@Data
public class Teacher {
    private int id;
    private String name;
    private List<Student>  students;
}

Nested processing by query

StudentMapper

public interface StudentMapper {
	// Get all students in the student table and display their corresponding teacher names
    List<Student> getStudents();
}

StudentMapper.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.angenin.dao.StudentMapper">
    
    <resultMap id="StudentTeacher" type="Student">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <!--Complex attributes need to be handled separately, association: Object, collection: aggregate-->
        <association property="teacher" javaType="Teacher" column="tid" select="getTeacher"/>
    </resultMap>
    
    <select id="getStudents" resultMap="StudentTeacher">
        select * from student;
    </select>
    <!--It doesn't matter to report red-->
    <select id="getTeacher" resultType="Teacher">
        select * from teacher where id=#{tid};
    </select>
</mapper>

mybatis-config.xml

<typeAliases>
    <package name="com.angenin.pojo"/>
</typeAliases>

<mappers>
    <mapper resource="com/angenin/dao/StudentMapper.xml"/>
</mappers>

test

@Test
public void test09() {
    SqlSession sqlSession = MybatisUtil.getSqlSession();

    StudentMapper studentDAO = sqlSession.getMapper(StudentMapper.class);
    List<Student> students = studentDAO.getStudents();
    for (Student student : students) {
        System.out.println(student);
    }

    sqlSession.close();
}

Nested processing according to results

StudentMapper

List<Student> getStudents2();

StudentMapper.xml

    <resultMap id="StudentTeacher2" type="Student">
        <!--Data table field name correspondence as alias-->
        <id property="id" column="sid"/>
        <result property="name" column="sname"/>
        <!--The first way to write-->
        <!--Complex attributes need to be handled separately, association: Object, collection: aggregate-->
<!--        <association property="teacher" javaType="Teacher">-->
<!--            <result property="name" column="tname"/>-->
<!--        </association>-->
        <!--The second way to write-->
        <result property="teacher.name" column="tname"/>
    </resultMap>

    <select id="getStudents2" resultMap="StudentTeacher2">
        select s.id as sid,s.name as sname,t.name as tname
        from student as s, teacher as t
        where s.tid=t.id;
    </select>

Officials also recommend using nested processing according to the results.

resultMap handles one to many

TeacherMapper

public interface TeacherMapper {
	// Get the designated Teacher and all the corresponding students through id
    Teacher getTeacher(@Param("tid")int id);
}

TeacherMapper.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.angenin.dao.TeacherMapper">

    <!--Nested query by result-->
    <resultMap id="TeacherStudent" type="Teacher">
        <id property="id" column="tid"/>
        <result property="name" column="tname"/>
        <!--Complex attributes need to be handled separately, association: Object, collection: aggregate-->
        <!--ofType: appoint list Generic properties, javaType Specify type-->
        <collection property="students" ofType="Student">
            <id property="id" column="sid"/>
            <result property="name" column="sname"/>
        </collection>
    </resultMap>
    
    <select id="getTeacher"  resultMap="TeacherStudent">
        select t.id as tid, t.name as tname, s.id as sid, s.name as sname
        from teacher as t, student as s
        where t.id=#{tid} and t.id=s.tid;
    </select>
    
</mapper>

test

@Test
public void test10() {
    SqlSession sqlSession = MybatisUtil.getSqlSession();

    TeacherMapper teacherDAO = sqlSession.getMapper(TeacherMapper.class);
    Teacher teacher = teacherDAO.getTeacher(1);
    System.out.println(teacher);

    sqlSession.close();
}

One to many, many to one summary

association: association (many to one)
collection: collection (one to many)
javaType: Specifies the object type
ofType: Specifies the generic type of the collection

Dynamic SQL

Official documents: https://mybatis.org/mybatis-3/zh/dynamic-sql.html

IF

If there is a name parameter, find the specified user separately; otherwise, find all users.

StudentMapper

List<Student> queryStudentIF(Map<String, Object> map);

StudentMapper.xml

<select id="queryStudentIF" parameterType="map" resultType="Student">
    select * from student where 1=1
    <if test="username != null">
         and name=#{username}
    </if>
</select>

1 = 1 is for the legitimacy of where. If where is put in the if tag, what if there are multiple judgments? An sql statement usually has only one where, so put it outside and use 1 = 1 to ensure the legitimacy of where, which can ensure that we can write multiple if tags.

test

@Test
public void test11() {
    SqlSession sqlSession = MybatisUtil.getSqlSession();

    StudentMapper studentDAO = sqlSession.getMapper(StudentMapper.class);
    Map<String, Object> map = new HashMap<>();
    // Find all without passing parameters
    // Pass parameters, find separately
    //map.put("username", "Xiaogang");

    List<Student> students = studentDAO.queryStudentIF(map);
    for (Student student : students) {
        System.out.println(student);
    }

    sqlSession.close();
}

choose,when,otherwise

The choose structure is similar to the switch in Java. when corresponds to the case of Java and otherwise corresponds to the default of Java.

<select id="queryStudentIF" parameterType="map" resultType="Student">
    select * from student where
    <choose>
        <when test="sid != null">
            and id=#{sid}
        </when>
        <when test="username != null">
            and name=#{username}
        </when>
        <otherwise>
            1=1
        </otherwise>
    </choose>
</select>

The when tag has a return, that is, it stops. It will not judge other when. If all when do not meet the requirements, execute other wise.

where

Look at the following code. There are some problems in this code. If the if does not match, there will be no judgment conditions after the where statement, or if sid is empty but username is not empty, it will become where and ....

<select id="queryStudentIF" parameterType="map" resultType="Student">
    select * from student where
    <if test="sid != null">
        id=#{sid}
    </if>
    <if test="username != null">
        and name=#{username}
    </if>
</select>

The solution can use the above choose or the where tag. The where tag will insert the where keyword only when the sub tag has returned content, and where will judge that if the first statement spliced starts with and or, it will be removed automatically.

<select id="queryStudentIF" parameterType="map" resultType="Student">
    select * from student
    <where>
        <if test="sid != null">
            id=#{sid}
        </if>
        <if test="username != null">
            and name=#{username}
        </if>
    </where>
</select>

It is recommended to use the where tag instead of the where keyword, because the where tag takes effect only when the child tag has returned results, eliminating some if judgment.

trim

If you are not satisfied with where, you can use trim to customize it.

<!--this trim Function and where Like labels, you can modify them yourself-->
<trim prefix="where" prefixOverrides="and |or ">
    <if test="sid != null">
        id=#{sid}
    </if>
    <if test="username != null">
        and name=#{username}
    </if>
</trim>

prefix: the content added at the beginning is like the where keyword added to the where tag.
prefixOverrides: if there are contents to be removed at the beginning of the first splicing statement, the spaces after and and or are required, because there are spaces after the and and or keywords in the sql statement.
suffix: the content added at the end.
suffixOverrides: the content to be removed if it exists at the end of the last splicing statement.

set

The set tag corresponds to the set keyword in the sql statement update. It is used to dynamically update the data according to the content to be modified.

StudentMapper

void changeStudentSET(Map<String, Object> map);

StudentMapper.xml

<update id="changeStudentSET" parameterType="map">
    update student
    <set>
        <!--Multiple if Judge, because there are few table fields, only one is used here-->
        <if test="username != null">name=#{username},</if>
    </set>
    where id=#{sid};
</update>

test

@Test
public void test12() {
    SqlSession sqlSession = MybatisUtil.getSqlSession();

    StudentMapper studentDAO = sqlSession.getMapper(StudentMapper.class);
    Map<String, Object> map = new HashMap<>();
    map.put("sid", "2");
    map.put("username", "Xiaolan");

    studentDAO.changeStudentSET(map);

    sqlSession.commit();
    sqlSession.close();
}

The set tag will remove the redundant comma at the end of the spliced sql statement,.

Simulate the function of set tag with trim tag:

<update id="changeStudentSET" parameterType="map">
    update student
    <trim prefix="set" suffixOverrides=",">
        <if test="username != null">name=#{username},</if>
    </trim>
    where id=#{sid};
</update>

foreach

Use foreach to dynamically splice into select * from student where 1=1 and (id=1 or id=2);

<select id="queryStudentFOREACH" parameterType="map" resultType="Student">
    select * from student
    <where>
        <foreach collection="ids" item="item" index="index" open="and (" separator=" or " close=")">
            id=#{item}
        </foreach>
    </where>
</select>

Collection: traversed collection
item: the element currently traversed
Index: the index currently traversed
open: the fragment spliced to the beginning of the sql statement.
Separator: traverses the separator added in the middle of the element.
close: the fragment spliced to the end of the sql statement.

test

@Test
public void test13() {
    SqlSession sqlSession = MybatisUtil.getSqlSession();

    StudentMapper studentDAO = sqlSession.getMapper(StudentMapper.class);
    Map<String, Object> map = new HashMap<>();
    List<Integer> list = new ArrayList<>();
    list.add(1);
    list.add(2);
    map.put("ids", list);

    List<Student> students = studentDAO.queryStudentFOREACH(map);
    students.forEach(System.out::println);

    sqlSession.close();
}

SQL fragment

Extracting reusable sql fragments is the same as extracting duplicate code in java and encapsulating it into functions, which reduces the amount of code and facilitates reuse. However, it should also be noted that the extracted sql fragments should be as simple as possible. It is best to only put the judgment statement if or choose. If they are too complex, they should be used relatively less.

<sql id="if-sid-username">
    <if test="sid != null">
        id=#{sid}
    </if>
    <if test="username != null">
        and name=#{username}
    </if>
</sql>

<select id="queryStudentIF" parameterType="map" resultType="Student">
    select * from student
    <where>
        <include refid="if-sid-username"/>
    </where>

</select>

cache

Official documents: https://mybatis.org/mybatis-3/zh/sqlmap-xml.html#cache

Caching is to store the queried data in memory. If you do the same query next time, you don't need to get the data from the cache (memory) directly through the database. In this way, you can reduce the interaction with the database, reduce system overhead and improve efficiency. The data that is often queried and not frequently modified is the most suitable for caching.

Mybatis has L1 cache and L2 cache. L1 cache is enabled by default.

All query operations will be saved to the cache, and all addition, deletion and modification operations will refresh the cache, and the cache will not be refreshed automatically (regular refresh).

L1 cache

Level 1 cache: in the same SqlSession, the same query operation is performed multiple times. Only the first operation needs to interact with the database and save it to the cache. The next query operation will directly obtain data from the cache and will not interact with the database. You can view it through the log.

Use SqlSession clearCache(); You can also empty the cache of SqlSession.

The first level cache is a Map.

L2 cache

Add < cache / > to the mapper file to represent that the mapper file uses L2 cache. The default purge policy is LRU (remove objects that have not been used for the longest time), and the cache tag can also be customized.

mybatis-config.xml

<settings>
    <!--It indicates that we want to turn on the global cache without this configuration, because it is also turned on by default, but it is easy for others to know that we use the global cache-->
    <setting name="cacheEnabled" value="true"/>
</settings>

When using the cache, each entity class needs to be serialized (implement the Serializable interface), otherwise an error NotSerializableException will be reported. If you don't want to serialize, set the readOnly attribute to true in the cache tag, but it is recommended that all entity classes be serialized.

Each mapper has a L2 cache. The data after the query operation of each SqlSession will be saved to the L1 cache. After the mapper opens the L2 cache and its SqlSession is submitted or closed, the content in the L1 cache will be saved to the L2 cache. If it is not closed, it will not be saved to the L2 cache.

  • When cache is not turned on
    The same SqlSession and the same mapper use L1 cache
    Different sqlsessions and the same mapper cannot use the cache

  • After the cache is turned on
    The same SqlSession and the same mapper use the L2 cache first. If it does not exist in the L2 cache, then use the L1 cache.
    Different sqlsessions use L2 cache for the same mapper

Different mapper s cannot use caching.

After the L2 cache is enabled, the select query in the mapper file can set the useCache attribute to false, indicating that the current query does not use the cache. In the addition, deletion and modification operation, the flushCache attribute can be set to false, indicating that the current operation does not refresh the cache.

Custom cache

Implement the cache interface, and then reference the type attribute in the cache tag. At present, most of them use redis as the cache.

Learning video: https://www.bilibili.com/video/BV1NE411Q7Nx?p=1

Tags: Java Mybatis SSM

Posted by TheDefender on Fri, 06 May 2022 07:36:45 +0300