mybatis框架记录

<?xml version="1.0" encoding="UTF-8" ?>

<insert id="addUser"  parameterType="UserBean" >
    insert into user(id,username,password,roleId)values(#{id},#{username},#{password},#{roleId})
</insert>

<delete id="delUser" parameterType="int">
    delete from user where id=#{id}
</delete>

<select id="queryUser" resultType="UserBean" >
    select id,username,password,roleId from user where id=#{id}
</select>

<update id="updUser" >
    update user set username=#{username} where id=#{id}
</update>

<!-- 一对一-->
<resultMap id="UserMap" type="UserBean">
    <id property="id" column="id"/>
    <result column="username" property="username"/>
    <result property="password" column="password"/>
    <result column="roleId" property="roleId"/>
   <association property="role" javaType="RoleBean">
       <id column="roleId" property="roleId"/>
       <result column="rolename" property="rolename"/>
   </association>
</resultMap>
<select id="queryUserAndRoleById" parameterType="int" resultMap="UserMap">
    select id,username,password,user.roleId,role.rolename from user left join role on user.roleId=role.roleId where id=#{id}
</select>

<!--多对一-->
<resultMap id="UserMap1" type="UserBean">
    <id property="id" column="id"/>
    <result column="username" property="username"/>
    <result property="password" column="password"/>
    <result column="roleId" property="roleId"/>
    <association property="role" javaType="RoleBean">
        <id column="roleId" property="roleId"/>
        <result column="rolename" property="rolename"/>
    </association>
</resultMap>
<select id="queryUserAndRoleByRoleId" parameterType="int" resultMap="UserMap1">
    select id,username,password,user.roleId,role.rolename from user left join role on user.roleId=role.roleId where role.roleId=#{roleId}
</select>

<select id="queryUserByIf" resultType="UserBean">
select id,username,password from user where 1=1
<if test="id!=null and id!=''">
    and id=#{id}
</if>

<if test="username!=null and username!=''">
    and username=#{username}
</if>

<select id="queryUserByWhere" resultType="UserBean">
    select id,username,password from user
    <where>
        id=#{id}
    </where>
</select>
<!--when标签  当...执行这个   执行时对所有传进来的数据进行判断，如果传进来一个String类型，那么第二个id判断依然会执行
 这里需要注意。目前建议对同一类型的数据进行判断。
 -->
<select id="queryUserByWhen" resultType="UserBean">
    select id,username,password from user
    <where>
    <choose>
        <when test="id&gt;15">
            and username=#{username}
        </when>
        <when test="id!=null and id&gt;0 &amp; id&lt;1000">
        and id=#{id}
        </when>

    </choose>
    </where>
</select>

<update id="updUserBySet" >
    update user
    <set>
        <if test="username!=null and username!=''">
            username=#{username}
        </if>
    </set>
    where id=99
</update>
<!--prefix 语句前面加东西 suffix语句后面加东西
  prefixOverrides语句前面减东西 suffixOverrides语句后面减东西-->
<update id="updUserBySetAndTrim" >
    update user
    <set>
        <if test="username!=null and username!=''">
            <trim prefix="username" suffix="}">
            =#{username
            </trim>
        </if>
    </set>
    where id=90
</update>
<resultMap id="UserMap2" type="UserBean">
<id property="id" column="id"/>
<result column="username" property="username"/>
<result property="password" column="password"/>
</resultMap>

<select id="QueryUserByBean" resultMap="UserMap2">
    select username,password from user
    <where>
        <bind name="nameplus" value="'%'+name+'%'"/>
        <if test="name!=null &amp;&amp; name.length()&gt;0">
            <trim prefixOverrides="and">
             and username like #{nameplus}
            </trim>
        </if>
    </where>
</select>
<!--select id,username,password from user where id in ( ? - ? - ? - ? - ? - ? - ? - ? - ? - ? - ? - ? - ? - ? - ? - ? - ? )
separator表示的是参数之间的间隔
-->
<select id="queryUserByForeach" parameterType="list" resultType="UserBean">
    select id,username,password from user where id in

        <foreach collection="list" index="0" open="(" close=")" item="item" separator=",">
             #{item}
        </foreach>


</select>

<select id="QueryId" resultType="UserBean">
    select id from user
</select>

<?xml version="1.0" encoding="UTF-8" ?>

<properties resource="jdbc.properties"/>

<!-- 自动生成主键 -->
<settings>
    <setting name="useGeneratedKeys" value="true"/>
</settings>

<!--设置包级 别名-->
<typeAliases>
    <package name="com.openlab.bean"/>
</typeAliases>
<environments default="mysql">
    <environment id="mysql">
        <transactionManager type="JDBC"/>
        <dataSource type="POOLED">
            <property name="driver" value="${driver}"/>
            <property name="url" value="${url}"/>
            <property name="username" value="${username}"/>
            <property name="password" value="${password}"/>
        </dataSource>
    </environment>

    <environment id="mysql1">
        <transactionManager type="JDBC"></transactionManager>

        <dataSource type="POOLED">
            <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
            <property name="url" value="jdbc:mysql://127.0.0.1:3306/user"/>
            <property name="username" value="root"/>
            <property name="password" value="123456"/>
        </dataSource>
    </environment>

</environments>


<mappers>
    <!-- <package  name="com/openlab/mapper"/>-->
       <mapper resource="com/openlab/mapper/UserMapper.xml"/>
       <mapper resource="com/openlab/mapper/RoleMapper.xml"/>
</mappers>