# 数据源配置
spring:
  datasource:
	# your_database(数据库名称)、useUnicode(使用unicode字符集)、characterEncoding(编码格式)、useSSL(使用SSL加密)、serverTimezone(时区)
    url: jdbc:mysql://localhost:3306/your_database?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai # 数据库的URL
    username: root # 数据库的用户名
    password: your_password # 数据库的密码
 
# MyBatis配置
mybatis:
  mapper-locations: classpath:mapper/*.xml # mapper文件位置
  type-aliases-package: com.yourpackage.model # 别名包路径
  configuration:
  #数据库表单命名单词之间以`_`分隔，而属性采用驼峰命名法，需要进行转化
    map-underscore-to-camel-case: true # 开启mybatis驼峰命名自动映射
  
# 配置日志级别，便于调试
logging:
  level:
    org.mybatis: debug # debug级别可以输出SQL语句，便于调试

@TableName("users") // 使用MyBatis-Plus的@TableName注解指定表名
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    private Integer id; // 对应数据库表的id字段
    private String username; // 对应数据库表的username字段
    private String email; // 对应数据库表的email字段
}

@Mapper  // 标记MyBatis的映射器接口，创建全局代理实例
@Repository // 标记数据访问层的组件，实现基于接口的依赖注入，并能够自动转换数据库相关的异常
public interface UserMapper {
// 执行sql时，会将`#{...}`替换为`?`，生成预编译sql，会自动设置参数值，性能更高，更安全
    @Select("SELECT * FROM users WHERE id = #{id}") // SQL 查询语句,
    User getUserById(int id);
 
    @Insert("INSERT INTO users (name, age) VALUES (#{name}, #{age})") // SQL 插入语句
    @Options(useGeneratedKeys = true,keyProperty = "id") // 返回生成的主键ID
    int insertUser(User user);
 
}

// src/main/.../repository/
@Mapper
@Repository
public interface UserMapper {
 
    public User getUserById(int id); // 单参数类型可以在xml文件中传入
    
	//使用 `@Param` 注解来指定每个参数的名字，以便于在SQL中引用
	public int updateClassName(@Param("id") int id, @Param("className") String className); // 多参数类型在接口定义处传入
	
	...
}

// src/main/resources/mappers/
<?xml version="1.0" encoding="UTF-8" ?>
<mapper namespace="com.example.demo.repository.UserMapper"> <!-- 接口位置 -->
	<!-- 定义resultMap ，这里假设数据库列名与实体类属性名不一致-->
		<resultMap id="userResultMap" type="User">
		    <result property="id" column="user_id"/>
		    <result property="name" column="user_name"/>
		</resultMap>
		<!-- 使用resultMap ，根据映射关系将 数据库中列的值 传递给 对象中对应属性 -->
		<select id="selectUser" resultMap="userResultMap">
		    SELECT * FROM users WHERE user_id = #{id}
		</select>
 
	<!-- 使用resultType ，确保数据库列名与实体类属性名一致，才能将数据库对应列的值传递给对象相对应的属性-->
    <select id="getUserById" resultType="com.example.demo.entity.User">
        SELECT * FROM users WHERE id = #{id}
    </select>
    
	 <insert id="insertStudent" parameterType="com.fl.boot.entity.Student" useGeneratedKeys="true" keyProperty="id">
        insert into student(id,sname,classId,birthday,email)
        values (#{id},#{sname},#{classId},#{birthday},#{email});
    </insert>
 
	 <delete id="deleteById" parameterType="int">
		 DELETE FROM students WHERE id = #{id}
	 </delete>
 
	<update id="updateClassName" >  
		UPDATE students SET class_name = #{className} WHERE id = #{id};
	 </update>
</mapper>

@Service
@Transactional
public class UserService {
    @Autowired
    private UserMapper userMapper;
 
    public User getUser(int id) {
        return userMapper.getUserById(id);
    }
	
    public int addUser(User user) {
        return userMapper.insertUser(user);
    }
}

mybatis:
  configuration:
    map-underscore-to-camel-case: true
```

<select id="findStudent" resultType="com.fl.boot.entity.Student">
	SELECT * FROM students
	<where>
		<if test="name != null">
			 name LIKE CONCAT('%',#{name},'%')    
		</if>
		<choose>
			<when test="gender != null">
				AND gender = #{gender}
			</when>
			<when test="age != null">
				OR age = #{age}
			</when>
			<when test="className != null ">
				class_name = #{className}
			</when>
			 <otherwise>
				1=1
			</otherwise>
		</choose>
	</where>
</select>

public List<Student> findStudentByIdList(@Param("ids") List<Integer> idList);

<!-- 传入一个 `ids` 列表，查询 `students` 表中所有符合条件的记录 -->
<select id="findStudentByIdList" resultType="com.fl.boot.entity.Student">
	SELECT * FROM students
	WHERE id IN
	<foreach collection="ids" item="id" open="(" close=")" separator=","> 
		#{id}
	</foreach>
</select>

<!-- 根据传入id，动态修改对应学生的属性  -->
<update id="updateStudent" parameterType="com.fl.boot.entity.Student">
  UPDATE students
  <set>
    <if test="name != null">
      name = #{name},
    </if>
    <if test="age != null">
      age = #{age},
    </if>
    <if test="gender">
      gender = #{gender},
    </if>
    <if test="className">
      class_name = #{className},
    </if>
  </set>
  WHERE id = #{id}
</update>

<!-- 使用 trim 代替 where -->
<select id="findStudent" resultType="com.fl.boot.entity.Student">
        SELECT * FROM students
	        <!-- 如果name==null，则SQL片段会以AND开头，会被`preifxOverrides`去除，并会在片段前添加`WHERE` -->
        <trim prefix="WHERE" prefixOverrides="AND |OR">
            <if test="name != null">
                name LIKE CONCAT('%',#{name},'%') 
            </if>
            <if test="gender != null">
                AND gender = #{gender}
            </if>
            <if test="age != null">
                AND age = #{age}
            </if>
            <if test="className != null ">
                AND class_name = #{className}
            </if>
        </trim>
    </select>

<sql id="selectColumns">
	id,name,age,gender,class_name
</sql>
 
<select id="findGirl" resultType="com.fl.boot.entity.Student">
	SELECT <include refid="selectColumns"/>
	FROM students
	WHERE gender = '女'
</select>