首页 > 代码库 > Mybatis相关SQL操作总结

Mybatis相关SQL操作总结

1、resultMap和resultType等参数以及结果集

  <select id="getApplicationByRoleCode" resultType="platform.appframe.entity.AfaApplication" parameterType="java.lang.String" >
   select distinct   <include refid="Application_Base_Column_List" />
      from afa_application a
      left join afa_auth_function b
    on a.app_id = b.app_id
     where b.party_code =#{roleCode}
  </select>

resultMap:用于操作本map里头的表,即操作在当前映射文件里头的指定的map。

resultType:用于操作其他map里头的表。

parameterType:可以为java.lang.String、也可以为一个对象比如:appframe.entity.AfaAppMenu

parameterMap:比如:appframe.entity.AfaAppMenu

2、map里头没有的参数在mapper里头要定义清楚

    <select id="getMenusByRoleCodes" resultMap="afaAppMenuMap">
        SELECT 
        <include refid="Base_Column_List" /> 
          FROM AFA_APP_MENU 
          WHERE MENU_CODE IN 
                  (SELECT a.FUNC_CODE
                       FROM AFA_APP_FUNCTION A
                      INNER JOIN AFA_AUTH_FUNCTION B
                         ON A.FUNC_CODE = B.FUNC_CODE
                      WHERE B.PARTY_CODE in 
        <foreach collection="roleCodes" item="roleCode" index="index" open="(" separator="," close=")">
              #{roleCode}
        </foreach>
        <if test="appId!=null">
           AND A.APP_ID = #{appId}
        </if>
        )
    </select>

在mapper里头要定义

public interface AfaAppMenuMapper extends Mapper<AfaAppMenu> {    
    public List<AfaAppMenu> getMenusByRoleCodes(@Param("roleCodes")List<String> roleCodes,@Param("appId")String appId);
}

否则会报Parameter ‘roleCodes‘ not found. Available parameters are [1, 0, param1, param2]

3、分页操作

public Page<AfaAppMenu> queryAfaAppMenuPage(@Param("searcher")Searcher searcher,@Param("page")Page<AfaAppMenu> page);

mybatis里头定义:

    <select id="queryAfaAppMenuPage" resultMap="afaAppMenuMap">
        SELECT 
        <include refid="Base_Column_List" />
         FROM AFA_APP_MENU
        <trim prefix="where"  prefixOverrides="and |or">  
            <if test="searcher != null and searcher.__QUERYCON != null">  
                ${searcher.__QUERYCON}
            </if>      
        </trim>
        ORDER BY SORT_NO ASC
    </select>

4、不将实体映射到数据库中

通过@Transient注解,该注解的对象在数据库中实际不存在。

 @Transient
    private String context;
    
    public String getMenuAppId() {
        return menuAppId;
    }

    public void setMenuAppId(String menuAppId) {
        this.menuAppId = menuAppId;
    }
<result column="CONTEXT" jdbcType="VARCHAR" property="context" />

 5、相关语法整理

foreach 语法

方式一:List<String>方式 
<foreach collection="roleCodes" item="roleCode" index="index" open="(" separator="," close=")"> #{roleCode} </foreach>
方式二:List<object>方式
<foreach item="func" index="index" collection="appfunc" open="(" separator="," close=")"> #{func.funcCode} </foreach>

if 语法

添加前后缀:
        <trim prefix="where"  prefixOverrides="and |or">  
            <if test="searcher != null and searcher.__QUERYCON != null">  
                ${searcher.__QUERYCON}
            </if>      
        </trim>

判断等于某个字符串:
        <if test="appId!=‘app_menu_id‘">
           AND A.APP_ID = #{appId}
        </if>

sql定义 

  定义:
<sql id="Application_Base_Column_List"> a.APP_ID,a.APP_NAME,a.APP_TYPE,a.IS_OPEN,a.CONTEXT,a.IP,a.PORT,a.PROTOCOL,a.APP_DESC,a.TENANT_ID,a.LOGOUT_URL,a.LAST_MODIFY_TIME </sql>
使用:
<include refid="Application_Base_Column_List"/>

 like语法

方式一:通过concat添加%:
WHERE FUNC_CODE LIKE CONCAT(%,#{key},%) OR FUNC_NAME LIKE CONCAT(%,#{key},%) 
方式二:通过$添加%:
where
org_name like %${orgName}%

 

Mybatis相关SQL操作总结