关于拼接SQL语句sqlMap的使用方法
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'"));