mybatis 的 select

select语句

• 空Mapper

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE mapper  
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"  
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">  
  <!-- namespace 命名空间 此处需要和你的CountryMapper 接口在同一个目录下 并且名字一样 -->
<mapper namespace="com.mzsj.mapper.SysUserRoleMapper">  
    <!-- SQL语句 -->  
    <!-- resultType 需要返回的实体类的别名 -->
</mapper>  

• select语句

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE mapper  
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"  
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- namespace 命名空间 此处需要和你的SysUserMapper 接口在同一个目录下 并且名字一样 -->
<mapper namespace="com.mzsj.mapper.SysUserMapper">
<!-- SQL语句 -->
<resultMap type="com.mzsj.entity.SysUser" id="userMap">
  <id property="id" column="id" />
  <result property="userName" column="user_name" />
  <result property="userPassword" column="user_password" />
  <result property="userEmail" column="user_email" />
  <result property="userInfo" column="user_info" />
  <result property="headImg" column="head_img" jdbcType="BLOB" />
  <result property="createTime" column="create_time" jdbcType="TIMESTAMP" />
</resultMap>

<sql id="userSql">id,user_name,user_password,user_email,user_info,head_img,create_time</sql>

<!-- resultType 需要返回的实体类的别名 -->
<select id="selectById" resultType="SysUser">
   select * from sys_user where id=#{id}
</select>

<select id="selectAll" resultMap="userMap">
    select
    <include refid="userSql"/>
    from sys_user
</select>
</mapper>  

• 一个类嵌套另一个类信息

@ToString
@Data
public class SysRole {
private Long id;
private String roleName;
private Long enabled;
private Long createBy;
private Date createTime;
private SysUser user;
}



/**
* 根据用户查询对应角色2
* @param id
* @return
*/
List<SysRole> selectRolesByUserId2(Long id);

<select id="selectRolesByUserId2" resultType="SysRole">
   SELECT
    r.id,
    r.enabled,
    r.role_name AS roleName,
    r.create_by AS createBy,
    r.create_time AS createTime,
    u.user_name as "user.userName",
    u.user_email as "user.userEmail"
   FROM
        sys_user AS u
        INNER JOIN sys_user_role AS ur ON u.id = ur.user_id
        INNER JOIN sys_role AS r ON r.id = ur.role_id
        WHERE
          u.id = #{id}
</select>

• 注解@Select

@Select({
"select id,role_name roleName,enabled,create_by createBy,create_time createTime from sys_role where id = #{id}" })

SysRole selectById(Long id);

• 注解@Select-@Results

@Results({
   @Result(property="id",column="id",id=true),
   @Result(property="roleName",column="role_name"),
   @Result(property="enabled",column="enabled"),
   @Result(property="createBy",column="create_time"),
   @Result(property="createTime",column="create_time")
})
@Select("select id,role_name,enabled,create_by,create_time from sys_role where id=#{id}")

SysRole selectById2(Long id);