关于Mybatis的一些随笔

<!DOCTYPE mapper
 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!DOCTYPE configuration
 PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
 "http://mybatis.org/dtd/mybatis-3-config.dtd">

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
 PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
 "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    
    <settings>
        <!-- 设置resultMap的自动映射级别 -->
        <setting name="autoMappingBehavior" value="FULL"/>
        <!-- 设置全局性延迟加载 -->
        <setting name="lazyLoadingEnabled" value="false"/>
    </settings>
    
    <!-- 设置实体类别名 -->
    <typeAliases>
        <package name="cn.xin.pojo" />
    </typeAliases>
    
    
</configuration>

<?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="cn.xin.dao.user.UserMapper">
     <select id="queryLogin" resultType="User">
         select * from smbms_user where userCode=#{userCode}
     </select>
     
     <select id="userCount" resultType="int">
         select count(*) from smbms_user,smbms_role 
         where smbms_user.userRole=smbms_role.id
         <if test="userName!=null and userName!=''">
             and smbms_user.userName=#{userName}
         </if>
         <if test="roleName!=null and roleName!=''">
             and smbms_role.roleName=#{roleName}
         </if>
     </select>
     
     <select id="getUserList" resultMap="userlist">
         select * from smbms_user,smbms_role where smbms_user.userRole=smbms_role.id
         <trim suffixOverrides="and">
             <if test="userName!=null and userName!=''">
                 and smbms_user.userName=#{userName}
             </if>
             <if test="roleName!=null and roleName!=''">
                 and smbms_role.roleName=#{roleName}
             </if>
         </trim>
         order by smbms_user.id limit #{from},#{pageSize}
     </select>
     <resultMap type="UserList" id="userlist">
         <id property="id" column="id"/>
         <result property="userCode" column="userCode"/>
         <association property="role" javaType="Role">
             <id property="id" column="r_id"/>
             <result property="roleName" column="roleName"/>
         </association>
     </resultMap>
     
     <insert id="addUser" parameterType="User">
         insert into smbms_user(userCode,userName,userPassword,gender,birthday,phone,address,userRole,idPicPath,workPicPath)
         value(#{userCode},#{userName},#{userPassword},#{gender},#{birthday},#{phone},#{address},#{userRole},#{idPicPath},#{workPicPath}) 
     </insert>
     
     <select id="getUserById" resultType="User">
         select * from smbms_user where id=#{id};
     </select>
     
     <update id="modifyUserById" parameterType="User">
         update smbms_user 
         set userName=#{userName},gender=#{gender},birthday=#{birthday},phone=#{phone},address=#{address},userRole=#{userRole}
         where id=#{id}
     </update>
     
     <select id="queryUserById" parameterType="int" resultType="User">
         select * from smbms_user where id=#{id}
     </select>
     
     <delete id="delUserById" parameterType="int">
         delete from smbms_user where id=#{id}
     </delete>
     
     <select id="getUserByuserCode" >
         select * from smbms_user where userCode=#{userCode}
     </select>
     
 </mapper>

1.多参数入参：
　　Mapper接口参数：(@Param("id")Integer id,@Param("password")String pwd)；Mapper.xml无需填写parameterType
2.@Param注解：
　　Mybatis的参数类型为Map，使用@Param注解的参数，会记录指定的参数名为key；否则默认为"param"+序号作为Map的key，这种情况可能会引起sql语句中获取不到#{参数名}，从而报错
3.resultMap的作用：
　　描述数据库结果集合对象的对应关系，属性名与字段名不同的匹配、association处理一对一关联关系、collection处理一对多关联关系、自动映射级别：NONE：禁止自动匹配，PARTIAL（默认）：自动匹配所有属性，有内部嵌套的除外，FULL：自动匹配所有属性，包括内部嵌套
4.动态SQL：
　　if+where、if+trim、if+set、foreach、choose(when/otherwise)、where 1=1、分页：oreder by id limit #{start}},#{count}（查询结果根据id排序，从start开始，每次显示5个查询结果）
　foreach:迭代对象为数组，collection=array、迭代对象为List，collection=list、迭代对象为Map，collection=key

select * from tb_member where id in <foreach collection="array" item="id" open="(" separator="," close=")">#{id}</foreach>