MyBatis detailed tutorial

MyBatis

Directory tree

  • tb_user.sql ```sql create database mybatis; use mybatis;
drop table if exists tb_user;
create table tb_user(
	id int primary key auto_increment,
	username varchar(20),
	password varchar(20),
	gender char(1),
	addr varchar(30)
);
INSERT INTO tb_user VALUES (1, 'zhangsan', '123', 'male', 'Beijing');
INSERT INTO tb_user VALUES (2, 'Li Si', '234', 'female', 'Tianjin');
INSERT INTO tb_user VALUES (3, 'Wang Wu', '11', 'male', 'Xi'an');
INSERT INTO tb_user VALUES (4, 'Zhao Liu', '111', 'female', 'Shanghai');
```
  • pom.xml ```xml <?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion>
    <groupId>com.ruochen</groupId>
    <artifactId>mybatis-demo</artifactId>
    <version>1.0-SNAPSHOT</version>
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
    <dependencies>
        <!--mybatis rely on-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.5</version>
        </dependency>
        <!--mysql drive-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>
        <!--junit unit testing -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13</version>
            <scope>test</scope>
        </dependency>
        <!-- add to slf4j journal api -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.20</version>
        </dependency>
        <!-- add to logback-classic rely on -->
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>1.2.3</version>
        </dependency>
        <!-- add to logback-core rely on -->
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-core</artifactId>
            <version>1.2.3</version>
        </dependency>
        <!--Resolve error reporting: Failed to load class "org.slf4j.impl.StaticLoggerBinder-->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-nop</artifactId>
            <version>1.7.5</version>
        </dependency>
    </dependencies>
</project>
```
  • logback.xml ```xml <?xml version="1.0" encoding="UTF-8"?> <configuration> <!-- CONSOLE: indicates that the current log information can be output to the CONSOLE. --> <appender name="Console" class="ch.qos.logback.core.ConsoleAppender"> <encoder> <pattern>[%level] %blue(%d{HH:mm:ss.SSS}) %cyan([%thread]) %boldGreen(%logger{15}) - %msg %n</pattern> </encoder> </appender>
    <logger name="com.ruochen" level="DEBUG" additivity="false">
        <appender-ref ref="Console"/>
    </logger>
    <!--
      level:Used to set the printing level, regardless of case: TRACE, DEBUG, INFO, WARN, ERROR, ALL and OFF
     ， default debug
      <root>Can contain zero or more<appender-ref>Element to identify the output location, which will be controlled by this log level.
      -->
    <root level="DEBUG">
        <appender-ref ref="Console"/>
    </root>
</configuration>
```
  • mybatis.config.xml ```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"> <!-- Database connection information -- > <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql:///mybatis?useSSl=false"/> <property name="username" value="root"/> <property name="password" value="ruochen666"/> </dataSource> </environment> </environments> <mappers> <!-- Load sql mapping file -- > <mapper resource="com/ruochen/mapper/UserMapper.xml"/> </mappers> </configuration> ```
  • UserMapper.xml ```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
-->
<mapper namespace="com.ruochen.mapper.UserMapper">
    <select id="selectAll" resultType="com.ruochen.pojo.User">
        select * from tb_user;
    </select>
</mapper>
```
  • MyBatisDemo.java ```java package com.ruochen;
import com.ruochen.pojo.User;
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;
import java.util.List;
/**
 * MyBatis Quick start code
 */
public class MyBatisDemo {
    public static void main(String[] args) throws IOException {
        // 1. Load the core configuration file of MyBatis and obtain SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        // 2. Get the SqlSession object and use it to execute sql
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 3. Execute sql statement
        List<User> users = sqlSession.selectList("test.selectAll");
        System.out.println(users);
        // 4. Release resources
        sqlSession.close();
    }
}
```
	[User{id=1, username='zhangsan', password='123', gender='male', addr='Beijing'}, User{id=2, username='Li Si', password='234', gender='female', addr='Tianjin'}, User{id=3, username='Wang Wu', password='11', gender='male', addr='Xi'an'}, User{id=4, username='Zhao Liu', password='111', gender='female', addr='Shanghai'}]

Mapper proxy

  • Define the Mapper interface with the same name as the SQL mapping file, and place the Mapper interface and the SQL mapping file in the same directory
  • Set the namespace property of the SQL mapping file to the fully qualified name of the Mapper interface
  • Define the method in Mapper interface. The method name is the id of the SQL statement in the SQL mapping file, and keep the parameter type consistent with the return value type ``` java package com.ruochen.mapper;
import com.ruochen.pojo.User;
import java.util.List;
public interface UserMapper {
    List<User> selectAll();
}
```
  • Coding test ``` java package com.ruochen;
import com.ruochen.mapper.UserMapper;
import com.ruochen.pojo.User;
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;
import java.util.List;
/**
 * Mapper Agent development
 */
public class MyBatisDemo2 {
    public static void main(String[] args) throws IOException {
        // 1. Load the core configuration file of MyBatis and obtain SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        // 2. Get the SqlSession object and use it to execute sql
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 3. Execute sql statement
        // List<User> users = sqlSession.selectList("test.selectAll");
        // 3.1 get the proxy object of UserMapper interface
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = userMapper.selectAll();
        System.out.println(users);
        // 4. Release resources
        sqlSession.close();
    }
}
```
	[User{id=1, username='zhangsan', password='123', gender='male', addr='Beijing'}, User{id=2, username='Li Si', password='234', gender='female', addr='Tianjin'}, User{id=3, username='Wang Wu', password='11', gender='male', addr='Xi'an'}, User{id=4, username='Zhao Liu', password='111', gender='female', addr='Shanghai'}]

If the Mapper interface name and the SQL mapping file name are the same and in the same directory, you can use package scanning to simplify the loading of SQL mapping files

Case - add, delete, modify and check the configuration file

preparation

  • Database tables: tb_brand.sql ``` sql --Delete tb_brand table drop table if exists tb_brand; --Create tb_brand table create table tb_brand ( --id primary key id int primary key auto_increment, --Brand name brand_name varchar(20), --Enterprise name company_name varchar(20), --Sort field ordered int, --Description information description varchar(100), --Status: 0: Disabled 1: enabled status int ); --Add data insert into tb_brand (brand_name, company_name, ordered, description, status) values ('three squirrels', 'three squirrels Co., Ltd', 5, 'delicious but not hot', 0), ('Huawei', 'Huawei Technology Co., Ltd', 100, 'Huawei is committed to bringing the digital world into everyone, every family and every organization to build an interconnected intelligent world', 1), ('Xiaomi', 'Xiaomi Technology Co., Ltd', 50, 'are you ok', 1);
SELECT * FROM tb_brand;
```
  • Entity class: brand java ``` java package com.ruochen.pojo;
/**
 * brand
 *
 * alt + Left mouse button: edit the whole column
 *
 * In the entity class, it is recommended to use its corresponding wrapper type for the basic data type
 */
public class Brand {
    // id primary key
    private Integer id;
    // Brand name
    private String brandName;
    // Enterprise name
    private String companyName;
    // sort field
    private Integer ordered;
    // Description information
    private String description;
    // Status: 0: Disabled 1: enabled
    private Integer status;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getBrandName() {
        return brandName;
    }
    public void setBrandName(String brandName) {
        this.brandName = brandName;
    }
    public String getCompanyName() {
        return companyName;
    }
    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }
    public Integer getOrdered() {
        return ordered;
    }
    public void setOrdered(Integer ordered) {
        this.ordered = ordered;
    }
    public String getDescription() {
        return description;
    }
    public void setDescription(String description) {
        this.description = description;
    }
    public Integer getStatus() {
        return status;
    }
    public void setStatus(Integer status) {
        this.status = status;
    }
    @Override
    public String toString() {
        return "Brand{" +
                "id=" + id +
                ", brandName='" + brandName + '\'' +
                ", companyName='" + companyName + '\'' +
                ", ordered=" + ordered +
                ", description='" + description + '\'' +
                ", status=" + status +
                '}';
    }
}
```
  • Test case: create a new com. In test ruochen. test. MyBatisTest. java
  • Install MyBatisX plug-in -XML and interface methods jump to each other -Generate statement according to interface method
    Query 1 Query all data
  • Writing interface method: Mapper interface -Parameter: None -Result: list \ < brand > ``` java package com.ruochen.mapper;
import com.ruochen.pojo.Brand;
import java.util.List;
public interface BrandMapper {
    /**
     * Query all
     */
    public List<Brand> selectAll();
}
```to write SQL sentence: SQL Mapping file	```xml
	    <select id="selectAll" resultType="brand">
	        select *
	        from tb_brand;
	    </select>
	```Execution method, test	```java
	    @Test
	    public void TestSelectAll() throws IOException {
	        // 1. Get SqlSessionFactory
	        String resource = "mybatis-config.xml";
	        InputStream inputStream = Resources.getResourceAsStream(resource);
	        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        // 2. Get SqlSession object 
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 3. Get Mapper interface proxy object
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        // 4. Implementation method
        List<Brand> brands = brandMapper.selectAll();
        System.out.println(brands);
        // 5. Release resources
        sqlSession.close();
    }
```

If the field name of the database is different from the attribute name of the entity class, the data cannot be automatically encapsulated

Alias: alias different column names so that the alias is the same as the attribute name of the entity class

-Disadvantages: alias must be defined once for each query

-Solution: you can use sql fragments (disadvantage: inflexible)

resultMap

-Define \ < resultmap > tags

-In the \ < Select > tab, replace the resultType attribute with the resultMap attribute

2. Query by Id

  • Writing interface method: Mapper interface -Parameter: id -Result: Brand ``` java /** *View Details: query by Id */ Brand selectById(int id); ```
  • Writing SQL statements: SQL mapping files ``` xml <select id="selectById" resultMap="brandResultMap"> select * from tb_brand where id = #{id}; </select> ```
  • Execution method, test ``` java @Test public void TestSelectById() throws IOException { //Receive parameters int id = 1;
        // 1. Get SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        // 2. Get SqlSession object
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 3. Get Mapper interface proxy object
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        // 4. Implementation method
        Brand brand = brandMapper.selectById(id);
        System.out.println(brand);
        // 5. Release resources
        sqlSession.close();
    }
```

Parameter placeholder:

-#{}: it will be replaced with?, To prevent SQL injection

-${}: there will be SQL injection problems when splicing SQL

-Time of use:

-When passing parameters: ${}

-Table name or column name is not fixed: ${}

 

Parameter type: parameterType: can be omitted

 

Special character handling:

Escape character: eg: < corresponding\< CDATA area: <! [CDATA [content]] >

3. Multi criteria query
  • Writing interface method: Mapper interface -Parameter: all query criteria -Result: list \ < brand > ``` java /** *Condition query ** receive parameters * 1. Bulk parameter: if there are multiple parameters in the method, you need to use @ Param("SQL parameter placeholder name") * 2. Object parameter: the name of the attribute of the object should be consistent with the name of the parameter placeholder * 3. map set parameters * * @param status * @param companyName * @param brandName * @return */ // List<Brand> selectByCondition(@Param("status") int status, @Param("companyName") String companyName, @Param("brandName") String brandName);
//    List<Brand> selectByCondition(Brand brand);
    List<Brand> selectByCondition(Map map);
```
  • Writing SQL statements: SQL mapping files ``` xml <select id="selectByCondition" resultMap="brandResultMap"> select * from tb_brand where status = #{status} and company_name like #{companyName} and brand_name like #{brandName}; </select> ```
  • Execution method, test ``` java /** *Multi condition query * @throws IOException */ @Test public void TestSelectByCondition() throws IOException { //Receive parameters int status = 1; String companyName = "Huawei"; String brandName = "Huawei";
        // Processing parameters
        companyName = "%" + companyName + "%";
        brandName = "%" + brandName + "%";
        // Encapsulated object
//        Brand brand = new Brand();
//        brand.setStatus(status);
//        brand.setCompanyName(companyName);
//        brand.setBrandName(brandName);
        Map map = new HashMap();
        map.put("status", status);
        map.put("companyName", companyName);
        map.put("brandName", brandName);
        // 1. Get SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        // 2. Get SqlSession object
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 3. Get Mapper interface proxy object
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        // 4. Implementation method
//        List<Brand> brands = brandMapper.selectByCondition(status, companyName, brandName);
//        List<Brand> brands = brandMapper.selectByCondition(brand);
        List<Brand> brands = brandMapper.selectByCondition(map);
        System.out.println(brands);
        // 5. Release resources
        sqlSession.close();
    }
```

If you cannot find the result in Chinese, you can modify jdbc as follows

<property name="url" value="jdbc:mysql:///mybatis?useSSl=false&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>

4. Multi condition query - dynamic condition query

  • Modify the SQL statement ``` xml <!-- Dynamic SQL *if: conditional judgment *test: logical expression *Problem: SQL syntax error when the first condition is not satisfied *The identity where is followed by 1 = 1, and all conditions are followed by and *< where > replace the where keyword --> <select id="selectByCondition" resultMap="brandResultMap"> select * from tb_brand <!-- where --> <where> <if test="status != null"> and status = #{status} </if> <if test="companyName!= null and companyName != ''"> and company_name like #{companyName} </if> <if test="brandName!= null and brandName != ''"> and brand_name like #{brandName}; </if> </where> </select> ```

5. Single condition query - dynamic condition query

  • Mapper interface ``` java /** *Single condition dynamic query * @param brand * @return */ List<Brand> selectByConditionSingle(Brand brand);
```
  • SQL mapping file ``` xml <!-- Single condition query --> <select id="selectByConditionSingle" resultMap="brandResultMap"> select * from tb_brand where <choose><!-- Equivalent to switch -- > <when test="status != null"><!-- Equivalent to case -- > status = #{status} </when> <when test="companyName != null and companyName != ''"><!-- Equivalent to case -- > company_name like #{companyName} </when> <when test="brandName != null and brandName != ''"><!-- Equivalent to case -- > brand_name like #{brandName}; </when> <otherwise> <!-- Similar to default -- > 1 = 1 </otherwise> </choose> </select> ``` Instead of using \ < otherwise >, use the \ < where > tag ```xml <select id="selectByConditionSingle" resultMap="brandResultMap"> select * from tb_brand <where> <choose><!-- Equivalent to switch -- > <when test="status != null"><!-- Equivalent to case -- > status = #{status} </when> <when test="companyName != null and companyName != ''"><!-- Equivalent to case -- > company_name like #{companyName} </when> <when test="brandName != null and brandName != ''"><!-- Equivalent to case -- > brand_name like #{brandName}; </when> </choose> </where> </select> ```
  • Execution method, test ``` java /** *Single condition query * * @throws IOException */ @Test public void TestSelectByConditionSingle() throws IOException { //Receive parameters int status = 1; String companyName = "Huawei"; String brandName = "Huawei";
        // Processing parameters
        companyName = "%" + companyName + "%";
        brandName = "%" + brandName + "%";
        // Encapsulated object
        Brand brand = new Brand();
        brand.setStatus(status);
//        brand.setCompanyName(companyName);
//        brand.setBrandName(brandName);
        // 1. Get SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        // 2. Get SqlSession object
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 3. Get Mapper interface proxy object
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        // 4. Implementation method
        List<Brand> brands = brandMapper.selectByConditionSingle(brand);
        System.out.println(brands);
        // 5. Release resources
        sqlSession.close();
    }
```

add to

  • Writing interface method: Mapper interface -Parameter: all data except id -Result: void ``` java /** *Add * @param brand */ void add(Brand brand); ```
  • Writing SQL statements: SQL mapping files ``` xml <!-- add to --> <insert id="add"> insert into tb_brand (brand_name, company_name, ordered, description, status) values (#{brandName}, #{companyName}, #{ordered}, #{description}, #{status}); </insert> ```
  • Execution method, test ``` java /** *Add * * @throws IOException */ @Test public void TestAdd() throws IOException { //Receive parameters int status = 1; String companyName = "waveguide mobile phone"; String brandName = "waveguide"; String description = "fighter in mobile phone"; int ordered = 100;
        // Processing parameters
        companyName = "%" + companyName + "%";
        brandName = "%" + brandName + "%";
        // Encapsulated object
        Brand brand = new Brand();
        brand.setStatus(status);
        brand.setCompanyName(companyName);
        brand.setBrandName(brandName);
        brand.setDescription(description);
        brand.setOrdered(ordered);
        // 1. Get SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        // 2. Get SqlSession object
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 3. Get Mapper interface proxy object
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        // 4. Implementation method
        brandMapper.add(brand);
        // 5. Release resources
        sqlSession.close();
    }
```

After running, it is found that it has not been added to the database, which comes to the transaction of MyBatis

Add sqlsession commit(); that will do

Or sqlsession sqlsession = sqlsessionfactory openSession(true); Set to auto commit transactions (close transactions)

Add - primary key return

After the primary key value of the data to be added is successfully inserted into the database

SQL mapping file: < insert id = "add" usegeneratedkeys = "true" keyproperty = "Id" >

You can get it directly from the test file: integer id = brand getId();

modify

1. Modify all fields

  • Writing interface method: Mapper interface -Parameters: all data -Result: void ``` java /** *Modification * @param brand * @return */ int update(Brand brand); ```
  • Writing SQL statements: SQL mapping files ``` xml <!-- modify --> <update id="update"> update tb_brand set brand_name = #{brandName}, company_name= #{companyName}, ordered = #{ordered}, description = #{description}, status = #{status} where id = #{id}; </update> ```
  • Execution method, test ``` java /** *Modification * * @throws IOException */ @Test public void TestUpdate() throws IOException { //Receive parameters int status = 1; String companyName = "waveguide mobile phone"; String brandName = "waveguide"; String description = "waveguide mobile phone, fighter in mobile phone"; int ordered = 200; int id = 6;
        // Encapsulated object
        Brand brand = new Brand();
        brand.setStatus(status);
        brand.setCompanyName(companyName);
        brand.setBrandName(brandName);
        brand.setDescription(description);
        brand.setOrdered(ordered);
        brand.setId(id);
        // 1. Get SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        // 2. Get SqlSession object
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 3. Get Mapper interface proxy object
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        // 4. Implementation method
        int count = brandMapper.update(brand);
        System.out.println(count);
        // Commit transaction
         sqlSession.commit();
        // 5. Release resources
        sqlSession.close();
    }
```

2. Modify dynamic fields

  • Write SQL statement: SQL mapping file ` ` xml <!-- Modify - dynamic modification --> <update id="update"> update tb_brand <set> <if test="brandName != null and brandName != ''"> brand_name = #{brandName}, </if> <if test="companyName != null and companyName != ''"> company_name= #{companyName}, </if> <if test="ordered != null"> ordered = #{ordered}, </if> <if test="description != null and description != ''"> description = #{description}, </if> <if test="status != null and stauts != ''"> status = #{status} </if> </set> where id = #{id}; </update> ```Delete 1 Delete a
  • Writing interface method: Mapper interface -Parameter: id -Result: void ``` java /** *Delete by Id * @param id */ void deleteById(int id); ```
  • Writing SQL statements: SQL mapping files ``` xml <!-- Delete - delete a --> <delete id="deleteById"> delete from tb_brand where id = #{id}; </delete> ```
  • Execution method, test ``` java /** *Delete by Id * * @throws IOException */ @Test public void TestDeleteById() throws IOException { //Receive parameters int id = 6;
        // 1. Get SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        // 2. Get SqlSession object
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 3. Get Mapper interface proxy object
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        // 4. Implementation method
        brandMapper.deleteById(id);
        // Commit transaction
        sqlSession.commit();
        // 5. Release resources
        sqlSession.close();
    }
```

2. Batch deletion

  • Writing interface method: Mapper interface -Parameter: id array -Result: void ``` java /** *Batch delete * @param ids */ void deleteByIds(@Param("ids") int[] ids); ```
  • Writing SQL statements: SQL mapping files ``` xml <!-- Batch delete MyBatis will encapsulate the array parameters into a Map collection *Default: array = array *Use the @ Param annotation to change the name of the default key of the Map set -->
    <delete id="deleteByIds">
        delete from tb_brand where id in
        <foreach collection="ids" item="id" separator="," open="(" close=")">
            ${id}
        </foreach>
    </delete>
```
  • Execution method, test ``` java /** *Batch delete * * @throws IOException */ @Test public void TestDeleteByIds() throws IOException { //Receive parameters int[] ids = {7, 8};
        // 1. Get SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        // 2. Get SqlSession object
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 3. Get Mapper interface proxy object
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        // 4. Implementation method
        brandMapper.deleteByIds(ids);
        // Commit transaction
        sqlSession.commit();
        // 5. Release resources
        sqlSession.close();
    }
```

MyBatis parameter passing

  • MyBatis provides the ParamNameResolver class for parameter encapsulation
  • Single parameter -POJO type: used directly. The attribute name is consistent with the parameter placeholder name -Map collection: used directly. The key name is consistent with the parameter placeholder name -Collection: encapsulated as a Map collection. You can use @ Param annotation to replace the default arg key name in the Map collection ``` map.put("arg0", collection set) map.put("collection", collection) ``` -List: encapsulated as a Map set. You can use @ Param annotation to replace the default arg key name in the Map set ``` map.put("arg0", list set) map.put("collection", list collection) map.put("list", list set) ``` -Array: encapsulated as a Map set. You can use @ Param annotation to replace the default arg key name in the Map set ``` map.put("array") map.put("arg0", array) ``` -Other types: direct use
  • Multiple parameters: encapsulated as a Map set. You can use @ Param annotation to replace the default arg key name in the Map set ``` map.put("arg0", parameter value 1) map.put("param1", parameter value 1) map.put("arg1", parameter value 2) map.put("param2", parameter value 2) ---------------@Param("username") map.put("username", parameter value 1) map.put("param1", parameter value 1) map.put("arg1", parameter value 2) map.put("param2", parameter value 2) ```

Comments are added, deleted, modified and checked

  • Query: @ Select
  • Add: @ Insert
  • Modify: @ Update
  • Delete: @ delete
@Select("select * from tb_user where id = #{id}")
public User selectById(int id);

[note]

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.

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

Keywords: MySQL Mybatis

Added by methodman on Tue, 08 Mar 2022 05:05:24 +0200

Popular Keywords

©2024 Programming VIP