123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596 |
- package cn.com.ty.lift.common.utils;
- import java.util.List;
- import java.util.Map;
- /**
- * @author huangyuan
- * @date 2020/4/18
- * @description mybatis @select 自定义sql 处理工具
- */
- public class MybatisSqlUtils {
- /**
- * @param params 查询参数
- * @return 拼接的sql语句
- * @description 通过给定参数拼接sql - 获取当前公司指定角色的用户信息
- * @date 2020/4/18 6:27 下午
- */
- @SuppressWarnings("unchecked")
- public String getPushUserInfoByRoleCodesSql(Map<String, Object> params) {
- List<String> roleCodeList = (List<String>) params.get("roleCodeList");
- Long companyId = (Long) params.get("companyId");
- //获取通用语句
- StringBuilder sql = getPushUserInfoSql();
- if (roleCodeList != null && roleCodeList.size() > 0) {
- sql.append("and r.code in (");
- //拼接list语句
- spliceList(roleCodeList, sql);
- }
- sql.append("and ur.company_id=").append(companyId);
- System.out.println(sql.toString());
- return sql.toString();
- }
- /**
- * @param params 查询参数
- * @return 拼接的sql语句
- * @description 通过给定参数拼接sql - 获取当前公司指定角色的用户信息
- * @date 2020/4/18 6:27 下午
- */
- @SuppressWarnings("unchecked")
- public String getPushUserInfoByIdsSql(Map<String, Object> params) {
- List<Long> userIdList = (List<Long>) params.get("userIdList");
- Long companyId = (Long) params.get("companyId");
- //获取通用语句
- StringBuilder sql = getPushUserInfoSql();
- if (userIdList != null && userIdList.size() > 0) {
- sql.append("and ua.user_id in (");
- //拼接list语句
- spliceList(userIdList, sql);
- }
- sql.append("and ur.company_id=").append(companyId);
- System.out.println(sql);
- return sql.toString();
- }
- /**
- * @param list 要拼接的list
- * @description 拼接语句
- * @date 2020/4/18 6:37 下午
- */
- private static void spliceList(List<?> list, StringBuilder sql) {
- for (int i = 0; i < list.size(); i++) {
- sql.append("'").append(list.get(i));
- if (i == list.size() - 1) {
- sql.append("')");
- } else {
- sql.append("',");
- }
- }
- }
- /**
- * @return 通用语句
- * @description 获取查询通用语句
- * @date 2020/4/18 6:51 下午
- */
- private static StringBuilder getPushUserInfoSql() {
- StringBuilder sql = new StringBuilder();
- sql.append("select ui.user_id as userId, ");
- sql.append("ui.name as userName, ");
- sql.append("ua.mobile as mobile, ");
- sql.append("ua.device_model as deviceModel, ");
- sql.append("ua.device_flag as deviceFlag, ");
- sql.append("r.name as roleName from ");
- sql.append("user_account ua ");
- sql.append("left join user_info ui on ua.user_id = ui.user_id ");
- sql.append("left join user_role ur on ua.user_id = ur.user_id ");
- sql.append("left join role r on r.id = ur.role_id ");
- sql.append("where 1 = 1 ");
- return sql;
- }
- }
|