关于Mybatis的一些随笔

mapper.xml头文件

<!doctype mapper
 public "-//mybatis.org//dtd mapper 3.0//en"
 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

mybatis配置头文件

<!doctype configuration
 public "-//mybatis.org//dtd config 3.0//en"
 "http://mybatis.org/dtd/mybatis-3-config.dtd">

mybatis配置文件

<?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>

sql映射文件

<?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>

mybatis随笔

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>