Mybatis动态SQL及ResultMap的使用

MyBatis3-动态sql

mybatis3官网动态sql页面

必须为表中可能为空的列指定 jdbcType

mysql中的内连接及外连接(包括左外连接和右外连接)的用法

ResultMap的简单用法

示例：根据personId从数据库中查询Person的三个字段并赋值给Client返回

实体类

注：基类BaseEntity中是creatBy，createTime，updateBy，updateTime，version，delFlag等字段

//Client实体类
public class Client extends BaseEntity implements Serializable {
    @TableId(type = IdType.ASSIGN_ID)
    private Long clientId;

    @TableField
    private String clientName;

    @TableField
    private Integer clientAge;
}

//Person实体类
public class Person extends BaseEntity implements Serializable {
    @TableId(type = IdType.ASSIGN_ID)
    private Long personId;

    @TableField
    private String personName;

    @TableField
    private Integer personAge;

	@TableField String gender;
	
    @TableField
    private Long phoneNumber;

    @TableField
    private String email;
}

Mapper文件

sql语句功能：返回id等于传入的personId，且年龄大于 30的Person,并取其id，name，age封装成Client返回

<mapper namespace="com.lfq.page.mapper.ClientMapper">
    <resultMap id="personToClient" type="com.lfq.page.pojo.entity.Client">
        <id column="person_id" property="clientId" jdbcType="BIGINT"/>
        <result column="person_name" property="clientName" jdbcType="VARCHAR"/>
        <result column="person_age" property="clientAge" jdbcType="INTEGER"/>
    </resultMap>

    <select id="getClientByPersonId" resultMap="personToClient">
        <bind name="age" value="30"/>
        select person_id,person_name,person_age
        from person p
        left join 
        client c
        on 
        c.client_id = p.person_id and p.del_flag = 0
        where  
        c.client_id = #{person_id} 
        and c.del_flag = 0 
        and c.client_age <![CDATA[>=]]> #{age}
    </select>
</mapper>

Mapper接口

public interface ClientMapper extends BaseMapper<Client> {
    Client getClientByPersonId(Long personId);
}