MyBatis 动态标签

配置文件示例

#应用程序名称
spring.application.name=configuration
#应用程序端口号
server.port=8080
#数据库连接信息
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/database_name?characterEncoding=utf8&useSSL=false
spring.datasource.username=root
spring.datasource.password=root
#自动驼峰转换
mybatis.configuration.map-underscore-to-camel-case=true

spring:
  application:
    #应用程序名称
    name: configuration
  #数据库连接信息
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/database_name?characterEncoding=utf8&useSSL=false
    username: root
    password: root
#应用程序端口号
server:
  port: 8080
mybatis:
  configuration: 
    map-underscore-to-camel-case: true #自动驼峰转换

CRUD

import com.example.spring_mybatis.model.PersonInfo;
import org.apache.ibatis.annotations.*;

@Mapper
public interface BlogMapper {

    @Select("select * from blog")
    List<PersonInfo> getPersonInfoAll();

    @Insert("insert into blog values (#{id},#{name},#{age})")
    Integer addPerson(PersonInfo person);

    @Update("update blog set name = #{name},age = #{age} where id = #{id}")
    Integer updatePerson(PersonInfo personInfo);

    @Delete("delete from blog where id = #{id}")
    Integer deletePerson(Integer id);
}

@Param

@Mapper
public interface BlogMapper {
    //@Param
    @Update("update blog set name = #{name},age = #{age} where id = #{id}")
    Integer updatePersonInfo(@Param("id") Integer userId,@Param("name") String userName,@Param("age") Integer userAge);
}

动态SQL

动态sql：指在程序运行时根据条件或参数动态生成的sql语句。与静态sql相比，动态sql更具灵活性，适用于需要更具不同条件构建查询的场景。例如，在某些web/app进行账号注册时会出现非必填选项

• mybatis的注解和xml两种方式都能实现动态SQL，但xml较为方便，所以下文使用xml来实现动态SQL

trim 标签

作用：用于自定义字符串截取规则，包含四个属性

• prefix：最终结果添加前缀
• suffix：最终结果添加后缀
• prefixOverrides：去除首部指定内容
• suffixOverrides：去除尾部指定内容

if 标签

作用：用于条件判断，通常在where或set语句中使用，当test表达式的值为true时，包含标签内的SQL片段

    <insert id="addPersonInfo">
        insert into blog
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">
                id,
            </if>
            <if test="name != null">
                name,
            </if>
            <if test="age != null">
                age,
            </if>
        </trim>
        values
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">
                #{id},
            </if>
            <if test="name != null">
                #{name},
            </if>
            <if test="age != null">
                #{age},
            </if>
        </trim>
    </insert>

where 标签

作用：替代SQL中的where关键字，当if条件成立时才会加入sql片段，并自动去掉第一个子句的and / or

<select id="getPersonInfoByNameAndAge">
    select * from blog
    <where>
        <if test="name != null">
            and name = #{name}
        </if>
        <if test="age != null">
            and age = #{age}
        </if>
    </where>
</select>

set 标签

作用：用于update语句。当if条件成立时才会加入SQL片段，并自动去除最后一个子句的逗号、

    <update id="updatePersonInfo">
        update blog
        <set>
            <if test="name != null">
                name = #{name},
            </if>
            <if test="age != null">
                age = #{age},
            </if>
        </set>
        <where>
            and id = #{id}
        </where>
    </update>

foreach标签

作用：用于集合遍历。主要属性：

• collection：集合参数名
• item：当前元素变量名
• open/close：包围符号
• separator：分隔符

    @Test
    void getPersonInfoById() {
        ArrayList<Integer> ids = new ArrayList<>();
        ids.add(1);
        ids.add(2);
        ids.add(3);
        List<PersonInfo> personInfoById = blogXMLMapper.getPersonInfoById(ids);
        System.out.println(personInfoById);
    }

    <select id="getPersonInfoById" resultType="com.example.spring_mybatis.model_blog.PersonInfo">
        select * from blog
        where id in
        <foreach collection="ids" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </select>

include 标签

作用：用于引用SQL片段，通过refid指定要引用的片段id。需配合sql标签使用，实现代码复用

    <sql id="collection">
        id,name,age
    </sql>
    <select id="getPersonInfoAll" resultType="com.example.spring_mybatis.model_blog.PersonInfo">
        select
        <include refid="collection">

        </include>
        from blog
    </select>

主键返回

主键返回：指在数据库插入操作后，自动获取刚插入记录的主键值。在mybatis中使用注解和xml都能获取到返回的主键

注解实现

@Mapper
public interface BlogMapper {
    @Options(useGeneratedKeys = true,keyProperty = "id")
    @Insert("insert into blog values (#{id},#{name},#{age})")
    Integer addPerson(PersonInfo person);
}

@SpringBootTest
@Slf4j
class BlogMapperTest {
    private final BlogMapper blogMapper;

    @Autowired
    public BlogMapperTest(BlogMapper blogMapper) {
        this.blogMapper = blogMapper;
    }
    
    @Test
    void addPerson() {
        PersonInfo personInfo = new PersonInfo(null, "黄忠", 60);
        Integer ret = blogMapper.addPerson(personInfo);
        log.info("添加成功,影响行数:{},返回的主键值:{}",ret.toString(),personInfo.getId());//添加成功,影响行数:1,返回的主键值:6
    }
}

通过xml实现

@SpringBootTest
@Slf4j
class BlogXMLMapperTest {
    private final BlogXMLMapper blogXMLMapper;

    @Autowired
    public BlogXMLMapperTest(BlogXMLMapper blogXMLMapper) {
        this.blogXMLMapper = blogXMLMapper;
    }
    
    @Test
    void addPersonInfo() {
        PersonInfo personInfo = new PersonInfo();
        personInfo.setAge(40);
        personInfo.setName("曹操");
        Integer ret = blogXMLMapper.addPersonInfo(personInfo);
        log.info("添加成功,影响行数:{},返回的主键值:{}",ret.toString(),personInfo.getId());//添加成功,影响行数:1,返回的主键值:7
    }
}

    <insert id="addPersonInfo" useGeneratedKeys="true" keyProperty="id">
        insert into blog
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">
                id,
            </if>
            <if test="name != null">
                name,
            </if>
            <if test="age != null">
                age,
            </if>
        </trim>
        values
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">
                #{id},
            </if>
            <if test="name != null">
                #{name},
            </if>
            <if test="age != null">
                #{age},
            </if>
        </trim>
    </insert>