关于拼接SQL语句sqlMap的使用方法

最新推荐文章于 2022-03-16 20:53:36 发布

转载最新推荐文章于 2022-03-16 20:53:36 发布 · 497 阅读

1 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/person008/p/9377762.html

本文介绍了一种在Java项目中实现动态SQL的方法，并详细解释了如何通过自定义SQL映射来灵活地处理查询条件。此外，还展示了如何在BaseService中实现数据范围的过滤，确保只有符合权限的数据被查询。

1.使用场景：共享部门共享用户的数据权限的公共处理问题，主要还是为了代码中时时获取当前登陆人信息，然后带入SQL语句中拼接查询

2.怎么使用？

1） bean继承了BaseEntity类，该类中有

/**
 * 自定义SQL（SQL标识，SQL内容）
 */
protected Map<String, String> sqlMap;
@JsonIgnore
    @XmlTransient
    public Map<String, String> getSqlMap() {
        if (sqlMap == null){
            sqlMap = Maps.newHashMap();
        }
        return sqlMap;
    }

    public void setSqlMap(Map<String, String> sqlMap) {
        this.sqlMap = sqlMap;
    }

2）XML中如何写？

<select id="findList" resultType="ModelInfo">
        SELECT 
            <include refid="modelInfoColumns"/>
        FROM dm_model a
        <include refid="modelInfoJoins"/>
        <where>
            <if test="name != null and name != ''">
                AND a.name LIKE 
                    <if test="dbName == 'oracle'">'%'||#{name}||'%'</if>
                    <if test="dbName == 'mssql'">'%'+#{name}+'%'</if>
                    <if test="dbName == 'mysql'">concat('%',#{name},'%')</if>
            </if>
            <!-- <if test="classificationId != null and classificationId != ''">
                AND a.classification_id = #{classificationId} 
            </if> -->
            <if test="modelInfoType != null and modelInfoType.id != null and modelInfoType.id != ''">
               AND (s.id = #{modelInfoType.id} OR s.parent_ids LIKE 
                    <if test="dbName == 'oracle'">'%,'||#{modelInfoType.id}||',%')</if>
                    <if test="dbName == 'mssql'">'%,'+#{modelInfoType.id}+',%')</if>
                    <if test="dbName == 'mysql'">CONCAT('%,', #{modelInfoType.id}, ',%'))</if>
            </if>
            <if test="status != null and status != ''">
                AND a.status = #{status}
            </if>
             <!-- 数据范围过滤 -->
            ${sqlMap.dsf}
        </where>
        <choose>
            <when test="page !=null and page.orderBy != null and page.orderBy != ''">
                ORDER BY ${page.orderBy}
            </when>
            <otherwise>
                ORDER BY a.update_date DESC
            </otherwise>
        </choose>
    </select>

3）BaseService中：

private static String sqlString(BaseEntity<?> entity,String visibleDept, String visibleUser, String appProcessState, User user,
            String type) {
        String sqlString = "";
        // 如果是超级管理员，则不过滤数据

//        if ((type==null && !user.isAdmin())||("rest".equals(type) && !user.isAdmin())){
        if ((type==null ||"rest".equals(type) || "km".equals(type)) && !user.isAdmin() && !user.getRoleNames().contains("系统管理员")/*&& !UserUtils.isUserHaveRole(user.getId(), "sysamdin")*/){
            
            sqlString = "and ((case ";
            
            sqlString += " when "+visibleDept+" is not null and "+visibleUser+" is not null and ("+visibleUser+" like '%"+ user.getId() + "%' or "+visibleUser+" like '%,"+ user.getId() + "%'  or "+visibleUser+" like '%"+ user.getId() + ",%') then 1"
                      + " when "+visibleDept+" is not null and "+visibleUser+" is null and ("+visibleDept+" like '%"+ user.getOffice().getId() + "%' or "+visibleDept+" like '%,"+ user.getOffice().getId() + "%'  or "+visibleDept+" like '%"+ user.getOffice().getId() + ",%') then 1"
                      + " when "+visibleDept+" is null and "+visibleUser+" is null then 1"
                      + " else 0 end)=1 "+appProcessState;
            
            if (type == null) {
                sqlString += " OR EXISTS (SELECT 1 FROM sys_user WHERE id='" + user.getId() + "' and id=a.create_by)";
            }else if("km".equals(type)){
                sqlString += " OR EXISTS (SELECT 1 FROM sys_user WHERE id='" + user.getId() + "' and id=a.create_by "+ appProcessState +")";
            }
            sqlString += ")";
            
        }
        return sqlString;
    }
这里需注意，由于sql比较长，要注意and和or的优先级，有时在和mapper中sql拼接完成后并不是自己想要的结果

在自己模块中调用

modelInfo.getSqlMap().put("dsf", visibledataScopeFilter(modelInfo, "a.share_depts",
                    "a.share_users", "and a.status='3'"));

转载于:https://www.cnblogs.com/person008/p/9377762.html