注解	说明
@Insert	实现新增
@Delete	实现删除
@Update	实现更新
@Select	实现查询
@Result	实现结果集封装
@Results	可以与@Result 一起使用，封装多个结果集
@ResultMap	实现引用@Results 定义的封装
@One	实现一对一结果集封装
@Many	实现一对多结果集封装
@SelectProvider	实现动态 SQL 映射
@CacheNamespace	实现注解二级缓存的使用

CRUD

public interface UserMapper {

    /**
     * 查询所有用户信息
     *
     * @return
     */
    @Select(" SELECT * FROM `user`")
    @Results({
            @Result(id = true, property = "id", column = "id"),
            @Result(property = "username", column = "username"),
            @Result(property = "birthday", column = "birthday"),
            @Result(property = "gender", column = "gender"),
            @Result(property = "address", column = "address"),
    })
    List<User> findAll();

    /**
     * 添加用户信息
     *
     * @param user
     */
    @Insert(" INSERT INTO `user` (username,birthday,gender,address) VALUES (#{username},#{birthday},#{gender},#{address})")
    void saveUser(User user);

    /**
     * 修改用户信息
     *
     * @param user
     */
    @Update(" UPDATE `user` set username = #{username},gender= #{gender} ,address = #{address} WHERE id = #{id} ")
    void updateUser(User user);

    /**
     * 删除用户信息
     *
     * @param id
     */
    @Delete(" DELETE FROM `user` WHERE id = #{id} ")
    void deleteById(Integer id);

}

Mybatis注解实现一对一关联查询

public class User implements Serializable {

    private Integer id;

    private String username;

    private Date birthday;
 
}
 
public interface UserMapper {

    /**
     * 根据id查询用户信息
     *
     * @param id
     * @return
     */
    @Select("SELECT * FROM `user` WHERE id = #{id}")
    User findById(Integer id);

}
 
public class Account implements Serializable {

    private Integer id;

    private Double money;

    /**
     * 一个账户属于一个用户
     */
    private User user;
}

public interface AccountMapper {


    /**
     * 查询所有账户以及所属用户信息
     *
     * @return
     */
    @Select(" SELECT * FROM `account`")
    @Results(value = {
            @Result(id = true, property = "id", column = "id"),
            @Result(property = "money", column = "money"),
            @Result(property = "user", column = "user_id", javaType = User.class, one = @One(select = "com.sunxiaping.mapper.UserMapper.findById",fetchType = FetchType.LAZY))
    })
    List<Account> findAccountWithUser();

}

Mybatis注解实现一对多关联查询

public class User implements Serializable {

    private Integer id;

    private String username;

    private List<Account> accountList;

}
public class Account implements Serializable {

    private Integer id;

    private Double money;
}




public interface AccountMapper {

    @Select("SELECT * FROM `account`")
    @Results({
            @Result(id = true, property = "id", column = "id"),
            @Result(property = "money", column = "money")}
    )
    Account findById(Integer id);
}



public interface UserMapper {

    @Select(" SELECT * FROM `user`")
    @Results({
            @Result(id = true, property = "id", column = "id"),
            @Result(property = "username", column = "username"),
            @Result(property = "birthday", column = "birthday"),
            @Result(property = "gender", column = "gender"),
            @Result(property = "address", column = "address"),
            @Result(property = "accountList", column = "id", many = @Many(select = "com.sunxiaping.mapper.AccountMapper.findById")),
    })
    List<User> findAll();
    
}

in 的使用

 
		@Select("<script>"
		            + "SELECT REALNAME,  file_path filePath ,file_name  FROM tp_uploadfile  WHERE id  IN "
		            + "<foreach item='item' index='index' collection='strList' open='(' separator=',' close=')'>"
		                + "#{item}"
		            + "</foreach>"
		        + "</script>")
		@Results(value = {  @Result(column = "filePath", property = "filePath") ,
							@Result(column = "REALNAME", property = "fileName") ,
							@Result(column = "file_name", property = "fileEncName")
				})
		List<FileBean> getFileList(@Param("strList") String[] res);

• #{}

  • 使用占位符,最终值为 '' 包裹

• ${}

  • 不使用占位符,故而不使用'' 包裹,用在 order by 排序和limit 分页

  • 动态sql

  • 标签

  处理数组

	<delete id="delMyWeibo" parameterType="arrar">
		delete from my_weibo where weibo_id in
		<foreach item="item" index="index" collection="array" open="(" separator="," close=")">
			#{item}
		</foreach>
	</delete>

1. 处理模糊查询

		<if test="reportName != null and reportName !=''">
				AND a.meeting_name like concat ('%',#{reportName},'%')
		</if>

mybatis @param

@Param是 作为Dao层的注解，作用是用于传递参数，从而可以与SQL中的的字段名相对应，一般在2=< 参数 <=5时使用最佳。

2. 原始的方法 当只有一个参数时，没什么好说的，传进去一个值也只有一个参数可以匹配。当存在多个参数时，传进去的值就区分不开了，这时可以考虑用Map，例如接口

public List<Role> findRoleByMap(Map<String, Object> parameter);

• xml文件

 <select id="findRoleByMap" parameterType="map" resultType="role">
    SELECT id,name FROM t_role
    WHERE roleName=#{roleName}
    AND note=#{note}
<select> 

测试文件

RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);
Map<String, Object> parameter = new HashMap<>();
parameter.put("roleName", "剑士");
parameter.put("note", "决战紫禁之巅");
List<Role> roles = roleMapper.findRolesByMap(parameter);

3. 使用@Param 很明显上面的缺点就在于可读性差，每次必须阅读他的键，才能明白其中的作用，并且不能限定其传递的数据类型， 下面是使用@Param的情况，需要将接口改为

public List<Role> findRoleByAnnotation(@Param("roleName") String roleName, @Param("note") String note);
// 这样我们就可以直接传入对应的值了。

// 当然也可以使用Java Bean来传递多个参数，定义一个POJO

public class RoleParam {
    private String roleName;
    private String note;
    /*getter和setter*/
}
// 此时接口就变为

public List<Role> findRoleByBean(RoleParam role);

这样对应的xml文件与1处的区别就在于id和parameterType发生了变化，id对应的方法和parameterType对应该类的权限定名。

而使用更多的场景可能是这样的，对应多个POJO

public List<Role> findRoleByMix(@Param("roleP") RoleParam role, @Param("permissionP") PermissionParam permission);

这样就可以进行如下映射

<select id="findRoleByMix" resultType="role">
    SELECT id,name FROM t_role
    WHERE roleName=#{roleP.roleName}
    AND note=#{rolep.note}
    AND level=#{permissionP.level}
<select>