lift-server/lift-common/src/main/java/cn.com.ty.lift.common/utils/MybatisSqlUtils.java

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
     * @return
     * @description 获取查询通过用户id，获取推送用户信息的接口
     * @date 2020/5/3 11:27 上午
     */
    public String getPushUserInfoByUserId(Map<String, Object> params) {
        Long userId = (Long) params.get("userId");
        StringBuilder sb = getUserInfoOneSql();
        sb.append("and ua.user_id = ").append(userId);
        return sb.toString();
    }

    /**
     * @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 true ");
        return sql;
    }

    /**
     * @param
     * @return
     * @description 获取单个用户信息sql
     * @date 2020/5/4 10:49 上午
     */
    private static StringBuilder getUserInfoOneSql() {
        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("from user_account ua ");
        sql.append("left join user_info ui on ua.user_id = ui.user_id ");
        sql.append("where true ");
        return sql;
    }

}