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=falseusername: rootpassword: 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：分隔符

    @Testvoid 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 blogwhere 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;@Autowiredpublic BlogMapperTest(BlogMapper blogMapper) {this.blogMapper = blogMapper;}@Testvoid 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;@Autowiredpublic BlogXMLMapperTest(BlogXMLMapper blogXMLMapper) {this.blogXMLMapper = blogXMLMapper;}@Testvoid 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>