Consolidation environment initialization

summary

Mybatis is an excellent persistence layer framework. The bottom layer realizes the interaction with the database based on JDBC. It is encapsulated and optimized on the basis of JDBC operation. With the help of flexible SQL customization and the mapping of parameters and result sets, it better adapts to the development of current Internet technology. The simple application architecture of mybatis framework is shown in figure-15:

In today's Internet application projects, mybatis framework usually integrates resources by spring framework to realize data interaction as a data layer technology.

Create projects and dependencies

Step 1: create the project 03 springboot mybatis, and then add the mybatis startup dependency.

<dependency>
	<groupId>org.mybatis.spring.boot</groupId>
	<artifactId>mybatis-spring-boot-starter</artifactId>
	<version>2.1.3</version>
</dependency>
 <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
 </dependency>

After we add the mybatis dependency, mybatis will be automatically configured when the spring framework starts. For example, the creation of SqlSessionFactory factory objects.

Step 2: simple configuration and implementation of Mybatis.
If you need to easily configure the mybatis framework, you can open application Properties file, in which basic configuration is performed (optional, not configured temporarily), for example:

mybatis.configuration.default-statement-timeout=30
mybatis.configuration.map-underscore-to-camel-case=true
mybatis.mapper-locations=classpath:/mapper/*/*.xml

Configure the output of sql log in mybatis: (com.cy is the root package of our project)

logging.level.com.cy=DEBUG

Environment test code implementation

Add test classes in src/test/java directory to conduct basic test on mybatis framework integration. The code is as follows:

package com.cy.pj.sys.dao;

@SpringBootTest
public class MyBatisTests {
	   @Autowired
	   private SqlSession sqlSession;
	   @Test
	   public void testGetConnection() {
		   Connection conn=sqlSession.getConnection();
		   System.out.println("connection="+conn);
	   }
}

In the SpringBoot scaffolding project, the Spring framework will create a SqlSessionFactory object based on the underlying configuration of the MyBatis framework, then create a SqlSession through this factory object, and finally inject a SqlSession object into the test class based on the Springku framework. Next, we can realize the session with the database through the SqlSession object. As shown in the figure:

Design and implementation of announcement service

Business description

The integration of MyBatis framework based on SpringBoot scaffold project realizes the operation of notification data.

Pojo class design

Create a SysNotice class to encapsulate announcement (notification) data with this kind of object.

package com.cy.pj.sys.pojo;
public class SysNotice {
    /** Announcement ID */
    private Long id;

    /** Announcement title */
    private String title;

    /** Announcement type (1notice2 announcement) */
    private String type;

    /** Announcement content */
    private String content;

    /** Announcement status (0 normal 1 closed) */
    private String status;

    /** remarks*/
    private String remark;


    /** Creation time */
    private Date createdTime;

    /** Modification time*/
    private Date modifiedTime;

    /** Create user */
    private String createdUser;

     /** Modify user*/
    private String modifiedUser;

    //Add the set/get/toString method yourself

}

Dao interface and method

Step 1: define the data layer interface and service method of the notification service. With this type of object, the interaction with the database is realized based on MyBatis technology.

package com.cy.pj.notice.dao;

@Mapper
public interface SysNoticeDao {

    /**
     * Query announcement information based on conditions
     * @param notice Used to encapsulate query parameters
     * @return Query results based on query criteria
     */
    List<SysNotice> selectNotices(SysNotice notice);

    /**
     * Delete announcement information based on id
     * @param id
     * @return Several lines deleted
     * Note: before jdk8, for interface methods, if there are multiple parameters or the parameters are arrays, it is not allowed
     * If the parameter name is used directly in the sql mapping file, the parameter name needs to be defined through the @ Param annotation, and then
     * In the sql mapping statement, use the name in the @ Param annotation to obtain the parameter data. For arrays, in sql mapping
     * You can also directly use array for receiving.
     */
    int deleteById(@Param("ids") Long... id);//array

    /**
     * Persistent notice object data
     * @param notice (Encapsulates the data to be written)
     * @return Returns the number of rows written.
     */
    int insertNotice(SysNotice notice);

    /**
     * Query notice object based on id
     * @param id Announcement unique id
     * @return Query results based on id
     * Suggestion: simple sql mapping statements can be written directly to interface methods, and complex sql is recommended to be written to xml mapping files
     */
    @Select("select * from sys_notices where id=#{id}")
    SysNotice findById(Long id);

    /**
     * Persistent notice object data
     * @param notice (Encapsulates the data to be updated)
     * @return Returns the number of rows updated.
     */
    int updateNotice(SysNotice notice);

}

Where: @ Mapper is an annotation defined by the mybatis framework to describe the data layer interface (all annotations only play a descriptive role), which is used to tell the spring framework that the implementation of this interface is created by mybatis and store its implementation class object in the spring container. When the system starts, it will scan the package where the startup class is located and the classes in the sub package. If @ Mapper annotation (provided by mybatis) is found on the interface, the bottom layer of the system will create its implementation class based on the interface (with the help of Proxy class in reflection package). Within the implementation class, the bottom layer will realize data access and operation based on sqlsession object.

Step 2: create the SQL mapping file corresponding to the SysNoticeDao interface, named sysnoticemapper xml, which is stored in the resources/mapper/sys directory, and defines the xml file header and root element. The code is as follows:

<?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.cy.pj.sys.dao.SysNoticeDao">
  
</mapper>

Step 3: add the Sql mapping corresponding to the insertNotice method in the mapping file. The key codes are as follows:

    <insert id="insertNotice" parameterType="com.cy.pj.sys.pojo.SysNotice">
        insert into sys_notices
        (title,type,content,status,remark,
         createdTime,modifiedTime,createdUser,modifiedUser)
        values
        (#{title},#{type},#{content},#{status},#{remark},
         now(),now(),#{createdUser},#{modifiedUser})
    </insert>

Step 4: define the SQL mapping corresponding to the update announcement in the mapping file. The key codes are as follows:

   <update id="updateNotice" parameterType="com.cy.pj.sys.pojo.SysNotice">
         update sys_notices
         set title=#{title},
             content=#{content},
             type=#{type},
             status=#{status},
             remark=#{remark},
             modifiedTime=now(),
             modifiedUser=#{modifiedUser}
         where id=#{id}
    </update>

Step 5: add condition based SQL query mapping in the mapping file. The key codes are as follows:

 <select id="selectNotices"
            parameterType="com.cy.pj.sys.pojo.SysNotice"
            resultType="com.cy.pj.sys.pojo.SysNotice">
          select *
          from sys_notices
          <where>
              <if test="type!=null and type!=''">
                 type=#{type}
              </if>
              <if test="title!=null and title!=''">
                  and title like concat("%",#{title},"%")
              </if>
              <if test="modifiedUser!=null and modifiedUser!=''">
                  and  modifiedUser like concat("%",#{modifiedUser},"%")
              </if>
          </where>
          order by createdTime desc
    </select>

Step 6: define the SQL mapping corresponding to deletion in the mapping file. The key codes are as follows:

    <delete id="deleteById"><!--(1,2,3,4,5)-->
         <!--foreach Used to iterate over an array or collection-->
        delete from sys_notices
        <where>
        <if test="ids!=null and ids.length>0">
          id in
         <foreach collection="ids" open="(" close=")" separator="," item="id">
            #{id}
         </foreach>
        </if>
         or 1=2
        </where>
    </delete>

Implementation and analysis of unit test

Step 1: define a test class in the src/java/test directory to test the application of the NoticeDao object.

package com.cy.pj.sys.dao;

@SpringBootTest
public class SysNoticeDaoTests {  
  
    @Autowired
    private SysNoticeDao sysNoticeDao;

	....
}

Step 2: add the unit test method of insert operation in the unit test class. The code is as follows:

@Test
    void testInsertNotice(){
        //Create a SysNotice object that encapsulates the data to be written to the database
        SysNotice notice=new SysNotice();
        notice.setTitle("CGB2011 Closing time");
        notice.setContent("2021/3/20 Formal conclusion");
        notice.setStatus("0");
        notice.setType("1");
        notice.setCreatedUser("tony");
        notice.setModifiedUser("tony");
        //Persist SysNotice objects to the database
        sysNoticeDao.insertNotice(notice);
//The internal implementation of this method will write data to the table through SQLSession.
    }

Step 3: define the unit test method for id based query. The code is as follows:

 @Test
    void testSelectById(){
        SysNotice notice=sysNoticeDao.selectById(1L);
        System.out.println(notice);
    }

Step 4: define the unit test method to perform modification based on id, and the code is as follows:

 @Test
  void testUpdateNotice(){
        //Query notification object based on id
        SysNotice notice=sysNoticeDao.selectById(1L);
        notice.setType("2");
        notice.setContent("2021/07/09 Spring Festival holiday");
        notice.setModifiedUser("json");
        //Persist the updated content to the database
        sysNoticeDao.updateNotice(notice);
    }

Step 5: define the unit test method. The code is as follows:

 @Test
  void testDeleteById(){
        int rows=
        sysNoticeDao.deleteById(1,3,4);
        System.out.println("rows="+rows);
  }

Step 6: define the unit test method based on conditional query passing information. The code is as follows:

@Test
void testSelectNotices(){
    SysNotice notice=new SysNotice();
    notice.setType("1");
    notice.setTitle("school opens");
    notice.setModifiedUser("tony");
    List<SysNotice> list=sysNoticeDao.selectNotices(notice);
    for(SysNotice n:list){
        System.out.println(n);
    }
}

Summary

Analysis of key and difficult points

• Core advantages of MyBatis framework (simple, flexible and powerful)
• MyBatis framework application architecture and core APIs (sqlsessionfactory, sqlsession,...)
• Integration of MyBatis framework in SpringBoot project (refer to the official website)
• MyBatis framework and database session entry and session process
• Application of dynamic SQL in MyBatis framework

FAQ analysis

• What is MyBatis? (persistence layer framework, semi-finished product - only solves the problem of data persistence)
• Why MyBatis? (open source, simple, powerful, flexible, stable,...)
• Mybatis application scenario? (Internet software applications tend to use mybatis for data persistence)
• The MyBatis framework implements the entry object that interacts with the database? SqlSession
• How is the SqlSession object created? SqlSessionFactory
• What specific SqlSession objects do you know? (DefaultSqlSession,SqlSessionTemplate)
• @What is the function of Mapper annotation?
• What does MyBatis do inside the implementation class and its methods created for our Dao interface?

BUG analysis

• BindingException
• BadSqlGrammarException
• SqlSyntaxErrorException
• UnstatisfiedDependencyException
• BeanInstantiationException