mybatis的注解开发以及xml的多表查询配置

public interface ClusterMessageMapper {

    // Insert
    @Insert("insert into cluster_manager(cluster_name, cluster_time, cluster_address, cluster_access_user, cluster_access_passwd) " +
            "values(#{clusterName}, #{clusterTime}, #{clusterAddress}, #{clusterAccessUser}, #{clusterAccessPasswd})")
    @Options(useGeneratedKeys = true, keyColumn = "cluster_id", keyProperty = "clusterId")
    public void insertClusterMessage(ClusterMessage clusterMessage);

    // select
    @Select("select * from cluster_manager")
    @Results(
            id = "clusterMessage",
            value = {
                    @Result(column = "cluster_name", property = "clusterName", javaType = String.class, jdbcType = JdbcType.VARCHAR),
                    @Result(column = "cluster_time", property = "clusterTime", javaType = Long.class, jdbcType = JdbcType.BIGINT),
                    @Result(column = "cluster_address", property = "clusterAddress", javaType = String.class, jdbcType = JdbcType.VARCHAR),
                    @Result(column = "cluster_access_user", property = "clusterAccessUser", javaType = String.class, jdbcType = JdbcType.VARCHAR),
                    @Result(column = "cluster_access_passwd", property = "clusterAccessPasswd", javaType = String.class, jdbcType = JdbcType.VARCHAR)
            }
    )
    public List<ClusterMessage> getClusterMessage();

    @Select("select * from cluster_manager")
    @MapKey("clusterId")
    public Map<Integer, ClusterMessage> getClusterMessageMapper();


    @Select("select * from cluster_manager where cluster_id=#{clusterId}")
    @ResultMap("clusterMessage")
    public ClusterMessage getClusterMessageById(@Param("clusterId") int clusterId);


    // update
    @Update("update cluster_manager set cluster_name=#{clusterName} where cluster_id=#{clusterId}")
    public void updateClusterMessage(ClusterMessage clusterMessage);

    // delete
    @Delete("delete from cluster_manager where cluster_id=#{clusterId}")
    public void deleteClusterMessage(@Param("clusterId")int clusterId);
}

详解:

sql 类型主要分成 : select@Select($sql),update@Update(${sql}), insert@Insert($sql),delete(${sql}).

@Select:
@Results 用来设置table信息与bean相关字段的映射关系， 每一个字段的关系使用 @Result控制。

默认情况下对于每一table字段，例如name, 会调用 bean 中的 setName(…). 如果找不到，对于新版本的 mybatis 会报错。
例如上面的 cluster_name 会调用 setCluster_name(). 但是java 中使用的 clusterName,可以通过 Result 注解控制.
@MapKey 此注解应用将查询数据转为 Map<>, 注意的是MapKey()中的id最终调用bean的getId 获取数据，所以需要映射bean字段而不是table.

@Param注解:
@Param注解用于给方法参数起一个名字。以下是笔者总结的使用原则：
在方法只接受一个参数的情况下，可以不使用@Param。
在方法接受多个参数的情况下，建议一定要使用@Param注解给参数命名。
@Insert :
insert 时获取自增主键的方式:

法一:

@Options(useGeneratedKeys = true, keyColumn = "cluster_id", keyProperty = "clusterId")

法二:

@SelectKey(statement="SELECT LAST_INSERT_ID()", keyProperty="clusterId", before=false, resultType=Integer.class)

4. #与$的区别:
   #{} 的作用主要是替换预编译语句(PrepareStatement)中的占位符?:

对于 : INSERT INTO user (name) VALUES (#{name}); ==> INSERT INTO user (name) VALUES (?);

${} 符号的作用是直接进行字符串替换:

对于 :

 INSERT INTO user (name) VALUES ('${name}'); ==> INSERT INTO user (name) VALUES ('tianshozhi');

接口映射（一对一关键配置）

<?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" >
<mapper namespace="com.sunny.dao.IAccountDao">
    <resultMap id="accountMap" type="account">
        <!--封装account表数据-->
        <id property="accountId" column="accountId"></id>
        <result property="uid" column="uid"></result>
        <result property="money" column="money"></result>
        <!--封装映射User表数据，account表与User表1对1关系，配置1对1的映射
            association:用于配置1对1的映射
                属性property：user对象在Account对象中的属性名
                属性javaType：user属性的java对象 类型
                属性column：account表中的外键引用user表
        -->
        <association property="user" javaType="User" column="uid">
            <id property="id" column="id"></id>
            <result property="username" column="username"></result>
            <result property="birthday" column="birthday"></result>
            <result property="sex" column="sex"></result>
            <result property="address" column="address"></result>
        </association>
    </resultMap>
    <select id="findAll" resultMap="accountMap">
        SELECT * FROM account LEFT JOIN USER ON account.uid=USER.id
    </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" >
<mapper namespace="com.sunny.dao.IUserDao">
    <resultMap id="userMap" type="user">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="birthday" column="birthday"></result>
        <result property="sex" column="sex"></result>
        <result property="address" column="address"></result>
        <!--配置1对多关系映射
            property：在User里面的List<Account>的属性名
            column:外键
            ofType:当前account表的java类型
        -->
        <collection property="accountList" ofType="account">
            <id property="accountId" column="accountId"></id>
            <result property="uid" column="uid"></result>
            <result property="money" column="money"></result>
        </collection>
    </resultMap>
    <select id="findAll" resultMap="userMap">
        SELECT * FROM USER LEFT JOIN account on account.uid=user.id
    </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" >
<mapper namespace="com.sunny.dao.IUserDao">
    <resultMap id="userMap" type="user">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="birthday" column="birthday"></result>
        <result property="sex" column="sex"></result>
        <result property="address" column="address"></result>
        <!--配置1对多映射关系
            property：在User里面的List<Account>的属性名=
            ofType:当前account表的java类型
        -->
        <collection property="roleList" ofType="role">
            <id property="id" column="rileId"></id>
            <result property="roleName" column="role_name"></result>
            <result property="roleDesc" column="role_desc"></result>
        </collection>
    </resultMap>
    <select id="findAll" resultMap="userMap">
        SELECT u.*,r.ID roleId,r.role_name,r.role_desc FROM USER u LEFT JOIN  user_role ur ON u.`id`=ur.`UID`
        LEFT JOIN  role r ON ur.`RID`=r.id
    </select>
</mapper>