<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.example.mapper.UserMapper"><!-- 结果映射 --><resultMap id="UserResultMap" type="com.example.entity.User"><id property="id" column="id"/><result property="username" column="username"/><result property="email" column="email"/><result property="age" column="age"/><result property="status" column="status"/><result property="createTime" column="create_time"/></resultMap><!-- 公共字段：SELECT 字段列表 --><sql id="columns">id, username, email, age, status, create_time</sql><!-- 公共字段：INSERT 列名 --><sql id="insertColumns">username, email, age, status, create_time</sql><!-- 公共字段：INSERT 值 --><sql id="insertValues">#{username, jdbcType=VARCHAR},#{email, jdbcType=VARCHAR},#{age, jdbcType=INTEGER},#{status, jdbcType=TINYINT},#{createTime, jdbcType=TIMESTAMP}</sql><!-- 公共字段：UPDATE set 片段 --><sql id="updateSet"><set><if test="username != null">username = #{username, jdbcType=VARCHAR},</if><if test="email != null">email = #{email, jdbcType=VARCHAR},</if><if test="age != null">age = #{age, jdbcType=INTEGER},</if><if test="status != null">status = #{status, jdbcType=TINYINT},</if><if test="createTime != null">create_time = #{createTime, jdbcType=TIMESTAMP},</if></set></sql><!-- 公共条件：WHERE 动态条件（含防转义） --><sql id="whereCondition"><where><!-- 使用 < 和 > 转义简单比较 --><if test="status != null">AND status = #{status, jdbcType=TINYINT}</if><if test="username != null and username != ''">AND username LIKE CONCAT('%', #{username, jdbcType=VARCHAR}, '%')</if><if test="email != null and email != ''">AND email = #{email, jdbcType=VARCHAR}</if><!-- 使用 >= 和 <= 转义 --><if test="age != null">AND age >= #{age, jdbcType=INTEGER}</if><!-- 使用 CDATA 包裹复杂时间范围条件 --><if test="startTime != null or endTime != null">AND <![CDATA[create_time >= #{startTime, jdbcType=TIMESTAMP}AND create_time <= #{endTime, jdbcType=TIMESTAMP}]]></if><!-- 使用 OGNL 逻辑判断 + 转义 --><if test="score != null and score gt 90">AND score > 90</if><if test="score != null and score le 60">AND score <= 60</if></where></sql><!-- 公共分页 --><sql id="limitClause"><if test="offset != null and limit != null">LIMIT #{offset, jdbcType=INTEGER}, #{limit, jdbcType=INTEGER}</if></sql><!-- 查询所有用户 --><select id="selectAll" resultMap="UserResultMap">SELECT<include refid="columns"/>FROM userWHERE status = #{value, jdbcType=TINYINT}ORDER BY id DESC</select><!-- 根据 ID 查询 --><select id="selectById" parameterType="java.lang.Long" resultMap="UserResultMap">SELECT<include refid="columns"/>FROM userWHERE id = #{id, jdbcType=BIGINT}</select><!-- 条件查询 --><select id="selectByCondition" parameterType="java.util.Map" resultMap="UserResultMap">SELECT<include refid="columns"/>FROM user<include refid="whereCondition"/>ORDER BY create_time DESC<include refid="limitClause"/></select><!-- 查询总数 --><select id="countByCondition" parameterType="java.util.Map" resultType="int">SELECT COUNT(*)FROM user<include refid="whereCondition"/></select><!-- 插入用户 --><insert id="insertUser" parameterType="com.example.entity.User">INSERT INTO user (<include refid="insertColumns"/>) VALUES (<include refid="insertValues"/>)</insert><!-- 更新用户 --><update id="updateUser" parameterType="com.example.entity.User">UPDATE user<include refid="updateSet"/>WHERE id = #{id, jdbcType=BIGINT}</update><!-- 删除用户 --><delete id="deleteUserById" parameterType="java.lang.Long">DELETE FROM userWHERE id = #{id, jdbcType=BIGINT}</delete></mapper>
<resultMap> 有嵌套对象
public class User {private Long id;private String username;private Address address; // 嵌套对象// getter 和 setter
}public class Address {private String street;private String city;private String zipCode;// getter 和 setter
}
<resultMap id="UserWithAddressResultMap" type="com.example.entity.User"><id property="id" column="id"/><result property="username" column="username"/><!-- 嵌套对象：来自 addresses 表 --><association property="address" javaType="com.example.entity.Address"><id property="id" column="address_id"/> <!-- address 的主键 --><result property="street" column="street"/><result property="city" column="city"/><result property="zipCode" column="zip_code"/></association>
</resultMap><select id="selectUserWithAddress" resultMap="UserWithAddressResultMap">SELECT u.id,u.username,u.address_id,a.street,a.city,a.zip_codeFROM users uLEFT JOIN addresses a ON u.address_id = a.idWHERE u.id = #{id}
</select>不用写<resultMap id="UserResultMap" type="com.example.entity.User">
<select id="selectUser" resultType="com.example.entity.User">SELECTid,username,email,age,status,create_time AS createTimeFROM users
</select>循环sql
<select id="selectUsersByIds" resultType="com.example.entity.User">SELECT id, username, email, create_time AS createTimeFROM usersWHERE id IN<foreach item="id" index="index" collection="list" open="(" separator="," close=")">#{id}</foreach>
</select>
<insert id="batchInsert">INSERT INTO users (username, email, create_time)VALUES<foreach collection="list" item="user" separator=",">(#{user.username}, #{user.email}, #{user.createTime})</foreach>
</insert><if>：条件判断
<select id="selectUsers" resultType="User">SELECT id, username, email, create_time AS createTimeFROM usersWHERE 1=1<if test="username != null and username != ''">AND username LIKE CONCAT('%', #{username}, '%')</if><if test="email != null and email != ''">AND email = #{email}</if><if test="age != null">AND age >= #{age}</if>
</select><where>：智能 WHERE（自动处理 AND/OR）
<select id="selectUsers" resultType="User">SELECT id, username, email, create_time AS createTimeFROM users<where><if test="username != null and username != ''">AND username LIKE CONCAT('%', #{username}, '%')</if><if test="status == 1">AND status = 1</if><if test="minAge != null">AND age >= #{minAge}</if></where>
</select><set>：用于 UPDATE，自动处理逗号 (自动去掉最后一个逗号)
<update id="updateUser">UPDATE users<set><if test="username != null">username = #{username},</if><if test="email != null">email = #{email},</if><if test="age != null">age = #{age},</if><if test="status != null">status = #{status}</if></set>WHERE id = #{id}
</update><choose>、<when>、<otherwise>：类似 Java 的 switch
<select id="selectUsersByCondition" resultType="User">SELECT id, username, email, statusFROM users<where><choose><when test="type == 'admin'">AND status = 1 AND username LIKE 'admin%'</when><when test="type == 'active'">AND status = 1</when><otherwise>AND status IN (0, 1)</otherwise></choose></where>
</select>补充：mybatis-plus 配置
spring:
datasource:
url: jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
mybatis-plus:
mapper-locations: classpath*:mapper/*.xml
type-aliases-package: com.example.entity
configuration:
map-underscore-to-camel-case: true
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl # 打印 SQL
)
