mybatis Mapper文件标签使用

太过爱你忘了你带给我的痛 2022-04-23 04:42 175阅读 0赞

#### 类型别名, ####

写在总配置文件中,注意节点元素的顺序或位置

#### 显示mybatis的日志信息 ####

导入jar包 log4j-1.2.16.jar  
拷贝log4j.properties到src的根目录中

#### sql语句块: ####

用sql标签定义一段sql通过include 引用进来

<sql id="select_user">
            select 
    		    id,
    		    username name,
    		    userpassword password
    	    from t_user 
        </sql> 
    
    
    
       <include refid="select_user"/>

例如：

<select id="findUserById"
    	        parameterType="java.lang.Integer" 
    	        resultType="com.tarena.entity.User">
    		<include refid="select_user"></include>
    	    where id = #{id}
    	</select>

#### resultMap节点 ####

<resultMap type="User" id="userMap">
    	    	<id property="id" column="id"/>
    	    	<result property="name" column="username" />
    	        <result property="password" column="userpassword"/>
    	    </resultMap>
    	      
    		<select id="findUserById"
    		        parameterType="java.lang.Integer" 
    		        resultMap="userMap">
    			<include refid="select_user"></include>
    		    where id = #{id}
    		</select>

#### 动态sql语句 ####

##### if标签: #####

<select id="findUsersByIf"
    	        parameterType="User"
    	        resultMap="userMap">
    		<include refid="select_user"></include>
    		where userage=20
    		<if test="name != null">
    		    and username=#{name}
    		</if>  
    		<if test="address !=null">
    		    and useraddress=#{address}
    		</if>      
    	</select>

##### choose when otherwise标签: #####

<select id="findUsersByChoose"
    		        parameterType="java.util.Map"
    		        resultMap="userMap">
    		    <include refid="select_user"></include>        
    		    where userage=20
    		    <choose>
    		    	<when test="uname !=null">
    		    	   and username like #{uname}
    		    	</when>
    		    	<when test="uaddress !=null">
    		    	   and useraddress like #{uaddress}
    		    	</when>
    		    	<otherwise>
    		    	   and username like '%a%' and useraddress like '%北京%'
    		    	</otherwise>
    		    </choose>
    		</select>

##### Where标签 #####

<select id="findUsersByWhere"
    	        parameterType="java.util.HashMap"
    	        resultMap="userMap">
    		<include refid="select_user"></include>
    		<where>
    		    <if test="uname != null">
    		        username like #{uname}
    			</if>  
    			<if test="uaddress !=null">
    			    and useraddress like #{uaddress}
    			</if>  
    		</where>
    	</select>

##### set 标签:只能用于更新操作 #####

<update id="updateUserBySet" 
    		        parameterType="java.util.Map">
    			update t_user
    				<set>
    				   <if test="uname!=null">
    				   	  username=#{uname},
    				   </if>  
    				   <if test="uaddress !=null">
    				      useraddress=#{uaddress}
    				   </if>
    			    </set>
    		    where id=#{id} 
    		</update>

##### trim标签(可以替换where标签和set标签) #####

<select id="findUsersByTrim1"
    	        parameterType="java.util.Map"
    	        resultMap="userMap">
    		<include refid="select_user"></include>
    		<trim prefix="WHERE" prefixOverrides="AND|OR ">
    		    <if test="uname != null">
    		        username like #{uname}
    			</if>  
    			<if test="uaddress !=null">
    			    and useraddress like #{uaddress}
    			</if>  
    		</trim>		        
    	</select>
    	
    	<update id="updateUserByTrim2" 
    	        parameterType="java.util.Map">
    		update t_user
    			<trim prefix="SET" suffixOverrides=",">
    			   <if test="uname!=null">
    			   	  username=#{uname},
    			   </if>  
    			   <if test="uaddress !=null">
    			      useraddress=#{uaddress}
    			   </if>
    		    </trim>
    	    where id=#{id} 
    	</update>

##### foreach标签:循环 #####

<delete id="deleteUserByForeach"
    		        parameterType="java.util.ArrayList">
    		     delete from t_user
    		     where id in   
    		     <foreach  collection="list"
    		               item="id"
    		               open="("
    		               close=")"
    		               separator=","
    		               index="index">
    		          #{id}
    		     </foreach>
    		</delete>

## mybatis多表查询: ##

mybatis中只有  
对一关联  
对多关联

<resultMap type="User" id="userMap_orm">
        	<id property="id" column="id"/>
        	<result property="name" column="username" />
            <result property="password" column="userpassword"/>
            <result property="age" column="userage"/>
            <result property="address" column="useraddress"/>
            <association property="group" 
                         javaType="com.arno.entity.Group">
            	<id property="id" column="gid"/>
            	<result property="name" column="groupname"/>
            	<result property="loc" column="grouploc"/>        
            </association>
        </resultMap>
    	<select id="findUserById_ORM"
    	        parameterType="java.lang.Integer"
    	        resultMap="userMap_orm">
    		select 
    		    u.id,
    		    u.username,
    		    u.userpassword,
    		    u.userage,
    		    u.useraddress,
    		    g.id gid,
    		    g.groupname,
    		    g.grouploc
    		 from (
    		          select * from t_user where id=#{uid}
    		      ) u    
    		 left join t_group g  on u.group_id=g.id   
    	</select>
    	
    	<resultMap type="com.tarena.entity.Group" id="groupMap_orm">
    		<id property="id" column="gid"/>
            <result property="name" column="groupname"/>
            <result property="loc" column="grouploc"/>
            <collection property="users"
                        ofType="com.arno.entity.User"
                        javaType="java.util.List">
            	<id property="id" column="uid"/>
        	    <result property="name" column="username" />
                <result property="password" column="userpassword"/>
                <result property="age" column="userage"/>
                <result property="address" column="useraddress"/>
            </collection>
    	</resultMap>
    	<select id="findGroupById_ORM"
    	        parameterType="java.lang.Integer"
    	        resultMap="groupMap_orm">
    		select 
    		    g.id gid,
    		    g.groupname,
    		    g.grouploc,
    		    u.id uid,
    		    u.username,
    		    u.userpassword,
    		    u.userage,
    		    u.useraddress       
    	     from (
    	             select * from t_group where id=#{gid}
    	          ) g
    	     left join t_user u on u.group_id=g.id
    	</select>