When using the MyBatis framework, it is inevitable that there will be the compilation of database query statements, so this article hopes to help you.

What is MyBatis Framework?

MyBatis is an excellent persistence layer framework that supports customized SQL, stored procedures and advanced mapping. MyBatis avoids almost all JDBC code and manually setting parameters and retrieving result sets. MyBatis can configure and map native information using simple XML or annotations to map interfaces and Plain Ordinary Java objects (POJOs) to records in the database.

How to use it?

POM file dependencies

<dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> The < version > 2.1.3 < / version > < / dependency >

YML file configuration, which matches the mapping file under resource/mapper/ path.

mybatis:
  mapper-locations: classpath:mapper/*.xml

Bind persistence layer interfaces and entity objects in XML files

<? The 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.flamelephant.fabricmgt.dao.HostMapper"> <resultMap type="com.flamelephant.fabricmgt.entity.po.Host" id="HostMap"> <result property="id" column="id" jdbcType="INTEGER"/> <result property="hostName" column="host_name" jdbcType="VARCHAR"/> <result property="ip" column="ip" jdbcType="VARCHAR"/> <result property="userName" column="user_name" jdbcType="VARCHAR"/> <result property="passWord" column="pass_word" jdbcType="VARCHAR"/> <result property="state" column="state" jdbcType="OTHER"/> <result property="tag" column="tag" jdbcType="VARCHAR"/> <result property="gmtCreated" column="gmt_created" jdbcType="TIMESTAMP"/> <result property="gmtModified" column="gmt_modified" jdbcType="TIMESTAMP"/> </resultMap> </mapper>
  • The namespace attribute in the mapper tag specifies the project path of our persistence layer interface
  • ResultMap is the most powerful element of MyBatis, which can map the complex data queried (such as querying data from several tables) into a result set
  • The type attribute of the ResultMap tag is the project path of the entity object we want to map, and the ID is the unique identity of the ResultMap.

  • The Result label in ResultMap is the binding between the entity and the database table fields, in which the Property attribute is the attribute name of the entity object, the Column is the field name of the database, and the JDBCType is the type of the field.

    SQL compilation of XML mapping files

    Query by entities as filter criteria

<select id="queryAll" resultMap="HostMap"> select id, host_name, ip, user_name, pass_word, state, tag, gmt_created, gmt_modified from host <where> <if test="id ! = null and id ! = ''"> and id = #{id} </if> <if test="hostName ! = null and hostName ! = ''"> and host_name like CONCAT('%', #{hostName}, '%') </if> <if test="ip ! = null and ip ! = ''"> and ip like CONCAT('%', #{ip}, '%') </if> <if test="userName ! = null and userName ! = ''"> and user_name = #{userName} </if> <if test="passWord ! = null and passWord ! = ''"> and pass_word = #{passWord} </if> <if test="state ! = null and state ! = ''"> and state = #{state} </if> <if test="tag ! = null and tag ! = ''"> and tag = #{tag} </if> <if test="gmtCreated ! = null"> and gmt_created = #{gmtCreated} </if> <if test="gmtModified ! = null"> and gmt_modified = #{gmtModified} /if> </where> </select>
  • Id =”queryAll” is the abstract method name of the persistence layer interface

  • ResultMap =” hostMap “Specifies the result set of the resultMap received by the query results.

    Persistence layer interface binding
/** * @Param Host ** @Param Host */ List<Host> queryAll(host host);

Bulk delete by primary key

<! > <delete id=" deletehostByids "parameterType="java.lang.Integer"> delete from host where id in <if test="hostIds ! = null and hostIds.length > 0"> <foreach item="id" collection="hostIds" index="index" open="(" separator="," close=")"> #{id} </foreach> </if> </delete>

The prototype of the above SQL statement is

Delete from host where id in(1,2,3)

Attribute understanding in the foreach tag

  • The Collection property is the received data source
  • Item is each element in the collection
  • Index: Used to indicate the position to which each iteration reaches in the iteration process
  • Open: Indicates where the statement begins
  • Separator: Indicates what symbol is used as the separator for data during iteration
  • Close: means to end with what

Persistence layer interface abstract methods

/** * @hostbyids (@param ("hostIds") Long[] hostIds); ** @hostbyids (@param ("hostIds") Long[] hostIds);

A batch of new

<! > <insert id="addHostList"> insert into host_and_group(host_group_id, host_group_id, host_id) values <foreach collection="hostGroupIdList" item="hostGroupId" index="index" separator=","> (#{hostGroupId}, #{hostId}) </foreach> </insert>

Persistence layer interface methods

** @Param Request * @Return Integer */Integer addHostList(HostandGroupRequest Request);

The element is encapsulated in an object, so if there is a batch increase in the element in this object, it can directly pass an object.