Notes on MyBatis2

Posted by verlen on Tue, 07 Sep 2021 23:01:06 +0200

Important notes about MyBatis2 are in the following Baidu network disk connection. Welcome to learn together
Link: https://pan.baidu.com/s/1zGAtbdQElMTgzUZUSg033w
Extraction code: 2ywn

Here are some easily forgotten points in my learning process to make another record for follow-up review

Step 1: write mybatis configuration file

<?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>

	<!--Import db.properties Configuration file - class Lujin, indicating that this file is in resource below-->
    <properties resource="db.properties"></properties>


	<settings>
        <!--<setting name="logImpl" value="STDOUT_LOGGING"/>    Standard log factory, just use it directly-->
        <setting name="logImpl" value="LOG4J"/>      <!-- It needs to be configured. The specific configuration contents are below -->
    </settings>


    <!--Alias, so resultType,paramType You don't need to write the class path corresponding to the object, just write the first letter of the class in lowercase-->
    <typeAliases>
        <!--Alias a specific entity class-->
        <typeAlias type="com.it.bean.User" alias="User1"></typeAlias>
        <!--Alias all classes in this package. The alias is the first letter of the class name. It can be lowercase or uppercase-->
        <package name="com.it.bean"/>
    </typeAliases>


    <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>
    <!--<environment id="test">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis2?useSSL=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="123"/>
            </dataSource>
        </environment>-->
    </environments>

    <!--Corresponding to each database interface Mapper.xml You need to register in this configuration file-->
    <mappers>
        <!--According to the classpath, when you are with the interface, you should write com/it/dao/UserMapper.xml,Right here resource Let's write it directly-->
        <mapper resource="com/it/dao/UserMapper.xml"/>
        <!--According to the package, all interfaces under the package are mapped to. The requirement is that the configuration file should be the same as the interface name
        It is located in the same directory, so it is either placed with the interface or in the resource Create the same folder below and put it in-->
        <package name="com.it.dao"></package>
        <!--Summary: many interfaces use packages, and no more interfaces are written one by one. After all, this does not have the same name and is not in the same path-->
    </mappers>
    
</configuration>

Step 2: write MyBatis tool class

// Get SqlSession from SqlSessionFactory
public class MyBatisUtil {

    private static SqlSessionFactory sqlSessionFactory;
    
    // It's dead. There must be
    static {
        // Step 1 of using Mybatis: get SqlSessionFactory object
        String resource = "mybatis-config.xml";
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
        } catch (IOException e) {
            e.printStackTrace();
        }
        
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    }
    
    // Get SqlSession
    public static SqlSession getSqlSession(){
    	// Set to true to indicate automatic submission. Manual submission is not required
        SqlSession sqlSession = sqlSessionFactory.openSession(true);    // Remember to turn it off when you run out
        return sqlSession;
    }
}

Step 3: write database interface

This is written according to the specific situation

Step 4: write mapper.xml corresponding to the database interface

It should be noted here that the database interface must be placed in the package under java, and the mapper is used to putting it under resource. At this time, the path of mapper under resource is required to be consistent with that of the interface in java
A mapper interface corresponds to a mapper.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">

<!--namespace Namespace, binding a Mapper Interface, for this mapper Class path of interface-->
<mapper namespace="com.it.dao.UserMapper">

	<resultMap id="UserMap" type="user">
        <!--column Fields in the database, property yes javabean The attributes in are mapped one by one, and only different mappings are needed-->
        <result column="pwd" property="password"></result>
    </resultMap>


    <!--select Query, id Method name in the corresponding interface-->
    <select id="getUserList" resultMap="UserMap">
        select * from user;
    </select>


	<!--id--mapper The name of a method in the interface
		parameterType--Parameter type
			* When only one parameter is passed, it can be written directly
			* When more than one parameter is passed, it can be encapsulated in map Internal, the value is#{key}
			* When the passed parameter is javabean Object, the value is directly#{bean object properties}
			* When the passed parameter is@param Type; otherwise#{id}, @ param must be written when there is more than one unused map
		resultType--return type
			* Basic data type; otherwise_Data type, such as_int
			* Object data type, the first letter is lowercase, such as integer,string
			* Self encapsulated javabean Because the object type is aliased, you can directly write the lowercase class name. Note that this indicates that the queried database attribute name should be the same as javabean The attribute names in should be corresponding and the names should be the same, and different ones will only be injected null,Therefore, if it is different, it should be used resultMap
			* List Type: resultType write List Type of element in
            * Map Type: single record: resultType =map  Multiple records: resultType=Map in value Type of-->
		sql Used in statements#{} to get the parameter value
	-->
    <select id="getUserLike" parameterType="String" resultType="com.it.bean.User">
        select * from user where name like #{value};    
    </select>
  
    <insert id="addUser2" parameterType="Map">
        insert into user(id, name, pwd) values (#{id}, #{name}, #{pwd});
    </insert>
    
    <update id="updateUser" parameterType="com.it.bean.User">
        update user set name = #{name}, pwd = #{pwd} where id=#{id};
    </update>

    <delete id="deleteUserById" parameterType="Integer">
        delete from user where id=#{id};
    </delete>
</mapper>

Step 4: Test

@Test
    public void testGetUserLike(){
        // First get the sqlSession object
        SqlSession sqlSession = MyBatisUtil.getSqlSession();

        // Obtain the corresponding interface object through sqlSession
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.getUserLike("%Lee%");
        for (User user : userList) {
            System.out.println(user);
        }
        
		// All additions, deletions and changes need to be committed
		// sqlSession.commit();

        // Close sqlSession
        sqlSession.close();
    }

supplement

1. Description of possible problems -- Maven static resource filtering problem
This can be solved by adding this code to pom.xml

	<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>

2. @ Param attribute

For all common parameters of the interface, try to write the @ Param parameter, especially when there are multiple parameters, you must write it!
• sometimes, according to business requirements, you can consider using map to pass parameters!
1. Add the @ Param attribute before the parameter of the interface method
2. When writing Sql statements, you can directly take the value set in @ Param without setting the parameter type separately

//Query users by password and name 
User selectUserByNP(@Param("username") String username,@Param("pwd") String pwd); 

 <select id="selectUserByNP" resultType="com.kuang.pojo.User"> 
	select * from user where name = #{username} and pwd = #{pwd}
 </select> 

3. Configure log4j
First, write the log4j configuration file under resource

#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/java.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
Use in program Log4j Output!
//Note the guide package: org.apache.log4j.Logger static Logger logger = Logger.getLogger(MyTest.class);
	@Test 
	public void selectUser() { 
		logger.info("info: get into selectUser method"); 
		logger.debug("debug: get into selectUser method");
		logger.error("error: get into selectUser method"); 
		SqlSession session = MybatisUtils.getSession(); 
		UserMapper mapper = session.getMapper(UserMapper.class);
		List<User> users = mapper.selectUser();
		for (User user: users){
			System.out.println(user); 
		} 
		session.close(); 
	}

4. Because the function of annotation development is not powerful, mybatis is often developed using xml

5. Paging mode: generally, you can directly use the limit tag of mysql or use the pageHelper plug-in to learn by yourself

6. For the processing of many to one relationship, for example, multiple students correspond to a teacher, and the student's javabean has the attributes of the teacher object
The teacher's javabean is more normal than those attributes in the database. The student's does not need the teacher's id and is directly given to a teacher's object

	// A student is associated with a teacher
    private Teacher teacher;

There are two ways to query: nested processing by query and nested processing by result. Here I only talk about nested processing by result

	<select id="getStudents2" resultMap="StudentTeacher2" >
        select s.id sid,
               s.name sname,
               t.id tid,
               t.name tname
        from student s,teacher t where s.tid = t.id
    </select>
    <resultMap id="StudentTeacher2" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <!--We need to deal with the complex type of attribute object separately. Here, the object is used association,For collection collection
  		    property: Which attribute is injected into the entity class
            javaType: hold sql The query result of the statement is encapsulated into an object of a class-->
        <association property="teacher" javaType="teacher">
            <result property="id" column="tid" />
            <result property="name" column="tname"/>
        </association>
    </resultMap>

7. One to many relationship
For example, a teacher corresponds to multiple students. At this time, the student's javabean normally writes the properties in the database, while the teacher's needs a collection of several student objects

	// A teacher has more than one student
    private List<Student> students;
	<select id="getTeacher" resultMap="TeacherStudent" parameterType="integer">
        select t.id tid, t.name tname, s.id sid, s.name sname
        from teacher t, student s where t.id = s.tid and t.id = #{tid};
    </select>
    <resultMap id="TeacherStudent" type="teacher">
        <result property="id" column="tid" />
        <result property="name" column="tname" />
        <!-- • JavaType Is used to specify javabean Type of property in
			 • ofType The specified is mapped to list In collection properties javabean Type of -->
        <collection property="students" ofType="student">
            <result property="id" column="sid" />
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>

Dynamic SQL - Focus

Writing complex SQL statements often requires splicing. If you don't pay attention to splicing SQL, errors may be caused due to the absence of quotation marks, spaces and so on.
So how to solve this problem? This requires the use of mybatis dynamic SQL. Through tags such as if, choose, when, otherwise, trim, where, set and foreach, it can be combined into very flexible SQL statements, which not only improves the accuracy of SQL statements, but also greatly improves the efficiency of developers.

Insert: use UUID to generate random id

public static String getId(){
    return String.valueOf(UUID.randomUUID()).replaceAll("-", "");
}
<select id="queryBlogIF" parameterType="map" resultType="blog">
        select * from blog
        <where>
            <if test="title != null">
                and title = #{title}
            </if>
            <if test="author != null">
                and author = #{author}
            </if>
        </where>
    </select>

    <!--choose Labels are similar to switch,Only one satisfied execution will be selected, one from top to bottom-->
    <select id="queryBlogChoose" resultType="blog" parameterType="map">
        select * from blog
        <where>
            <choose>
                <when test="title != null">
                    and title = #{title}
                </when>
                <when test="author != null">
                    and author = #{author}
                </when>
                <otherwise>
                    and views = #{views}
                </otherwise>
            </choose>
        </where>
    </select>


    <update id="updateBlogBySet" parameterType="map">
        update blog
        <set>
            /*sql Fragment multiplexing*/
            <include refid="if_title_author"></include>
        </set>
        where id = #{id};
    </update>


    <!--definition sql Fragments to improve the reusability of code without where-->
    <sql id="if_title_author">
        <if test="title != null">
            title = #{title},
        </if>
        <if test="author != null">
            author = #{author},
        </if>
    </sql>

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

cache

1. What is Cache?
• temporary data in memory.
• put the data frequently queried by users in the cache (memory), and users do not need to query from the disk (relational database data file) to query the data from the cache, so as to improve the query efficiency and solve the performance problem of high concurrency system.
2. Why cache?
• reduce the number of interactions with the database, reduce system overhead and improve system efficiency.
3. What kind of data can be cached?
• frequently queried and infrequently changed data.

Two levels of cache are defined by default in MyBatis system: L1 cache and L2 cache
 by default, only L1 cache is enabled( SqlSession level cache (also known as local cache). The data queried during the same session with the database will be placed in the local cache.
 L2 cache needs to be manually enabled and configured. It is based on namespace level cache.

Four situations of L1 cache invalidation
The first level cache is a SqlSession level cache, which is always on, and we can't close it;
L1 cache invalidation: the current L1 cache is not used. The effect is that you need to send another query request to the database!
1. Adding, deleting and modifying may change the original data, so the system must refresh the cache
2. sqlSession is different. Our first level cache is based on sqlSession level cache. It must be different if we change sqlSession
3. Sqlsessions are the same, but execute different data. At this time, there is no data in the cache. It must be invalid
4. sqlSession is the same, but manually clear the L1 cache: sqlSession.clearCache();

L2 cache
• L2 cache is also called global cache. The scope of L1 cache is too low, so L2 cache was born
• cache based on namespace level, one namespace corresponds to one L2 cache;
• working mechanism
 when a session queries a piece of data, the data will be placed in the first level cache of the current session;
 if the current session is closed, the L1 cache corresponding to this session will be lost; But what we want is that the session is closed and the data in the L1 cache is saved to the L2 cache;
 new session query information can be obtained from the L2 cache;
 the data found by different mapper s will be placed in their corresponding cache (map);
Use steps
1. Open global cache [mybatis config. XML]

<setting name="cacheEnabled" value="true"/>

2. Configure the secondary cache in each mapper.xml. This configuration is very simple[ xxxMapper.xml]

<cache/>

The detected data will be placed in the L1 cache by default
Only after the session is committed or closed, the data in the L1 cache will be transferred to the L2 cache

Cache order:
1. Let's see if there is any in the L2 cache
2. Let's see if there is any in the L1 cache
3. Query database

Topics: Java Database Mybatis SQL SSM