roma-api/src/main/java/com/roma/romaapi/dao/ApiDao.java

package com.roma.romaapi.dao;

import com.roma.romaapi.utils.CommonUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;

@Repository
public class ApiDao {
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    @Autowired
    private CommonUtil commonUtil;

    // 通过code,获取要执行的sql
    public Map<String, Object> adminInfoQueryForMap(String adminName) {
        try {
            Map<String, Object> paramMap = new HashMap<>();
            paramMap.put("admin_name", adminName);
            Map<String, Object> resMap2 = namedParameterJdbcTemplate.queryForMap(
                    "SELECT * FROM `admin_user` where admin_name = :admin_name AND is_enable=1", paramMap);
            return  resMap2;
        } catch (EmptyResultDataAccessException e) {
            Map<String, Object> resultMap = new HashMap<>();
            resultMap.put("sysErrorCode", "500");

            return resultMap;
        }
    }

    // 通过用户id,获取用户信息
    public Map<String, Object> userInfoByIdQueryForMap(String userID) {
        try {
            Map<String, Object> paramMap = new HashMap<>();
            paramMap.put("admin_id", userID);
            Map<String, Object> resMap2 = namedParameterJdbcTemplate.queryForMap(
                    "SELECT * FROM `admin_user` where id = :admin_id AND is_enable=1", paramMap);
            return  resMap2;
        } catch (EmptyResultDataAccessException e) {
            Map<String, Object> resultMap = new HashMap<>();
            resultMap.put("sysErrorCode", "500");

            return resultMap;
        }
    }

    // 通过用户id,获取禁用和启用用户详情
    public Map<String, Object> enableAndNoEnableUserInfoByIdQueryForMap(String userID) {
        try {
            Map<String, Object> paramMap = new HashMap<>();
            paramMap.put("admin_id", userID);
            Map<String, Object> resMap2 = namedParameterJdbcTemplate.queryForMap(
                    "SELECT * FROM `admin_user` where id = :admin_id ", paramMap);
            return  resMap2;
        } catch (EmptyResultDataAccessException e) {
            Map<String, Object> resultMap = new HashMap<>();
            resultMap.put("sysErrorCode", "500");

            return resultMap;
        }
    }

    public void addPermissions(Map bindValue) {
//        insert into tableName(colunm1,colunm2,...) value(value1,value2,...);
        String sql = "INSERT INTO `roma_permissions` " +
                "(`parent_id`,`page_id`,`sort`,`value`,`display_name`,`description`,`type`,`created_at`,`icon`,`is_enable`) " +
                "value(:parent_id,:page_id,:sort,:value,:display_name,:description,:type,:created_at,:icon,:is_enable) ";
        Integer row = namedParameterJdbcTemplate.update(sql, bindValue);
    }

    // 通过id和数据表类型,查询不同的数据
    public Map getDetailsInfoByIdAndType(String tableName, String id) {
        try {
            Map<String, Object> paramMap = new HashMap<>();
            paramMap.put("id", id);
            Map<String, Object> resMap2 = namedParameterJdbcTemplate.queryForMap(
                    "SELECT * FROM `"+tableName+"` where id = :id", paramMap);
            return  resMap2;
        } catch (EmptyResultDataAccessException e) {
            Map<String, Object> resultMap = new HashMap<>();
            resultMap.put("sysErrorCode", "500");

            return resultMap;
        }
    }

    // 通过角色id,获取角色下全部的权限信息
    public List<Map<String, Object>> getAllPermissionByRoleId(String id){
        String sql = " SELECT * FROM `roma_permission_role` WHERE role_id=:id ";
        Map<String, Object> bindValue = new HashMap<>();
        bindValue.put("id", id);
        List<Map<String, Object>> resMap2 = namedParameterJdbcTemplate.queryForList(sql, bindValue);

        return resMap2;
    }

    // 通过角色id,获取角色下全部的权限信息
    public List<Map<String, Object>> getAllRoleByUserId(String id){
        String sql = " SELECT * FROM `roma_role_user` WHERE user_id=:id ";
        Map<String, Object> bindValue = new HashMap<>();
        bindValue.put("id", id);
        List<Map<String, Object>> resMap2 = namedParameterJdbcTemplate.queryForList(sql, bindValue);

        return resMap2;
    }

    // 添加角色
    public Integer addRole(Map<String, Object> bindValue) {
        String insertSql = " INSERT INTO `roma_roles` (`role_name`,`role_description`,`is_enable`,`created_at`)  " +
                "VALUES (:roleName,:roleDesc,:isEnable,:createdAt)  ";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        Integer row = namedParameterJdbcTemplate.update(insertSql, new MapSqlParameterSource(bindValue), keyHolder);
        int k = keyHolder.getKey().intValue();

        return k;
    }

    // 编辑角色信息
    public Integer editRole(Map<String, Object> bindValue) {
        String editSql = " UPDATE `roma_roles` SET `role_name`=:roleName,`role_description`=:roleDesc,`is_enable`=:isEnable WHERE id=:id";
        Integer row = namedParameterJdbcTemplate.update(editSql, bindValue);

        return row;
    }

    // 编辑角色权限
    public Integer editRolePermission(String info, String id) {
        // 先删除此角色的所有权限
        Map<String, Object> delParams = new HashMap<>();
        delParams.put("id", id);
        String delRolePermissionSql = " DELETE FROM `roma_permission_role` WHERE role_id=:id ";
        Integer row = namedParameterJdbcTemplate.update(delRolePermissionSql, delParams);
        // 增加此角色的权限信息
        Map addMap = new HashMap<>();
        String insertSql = " INSERT INTO `roma_permission_role` (`role_id`,`permission_id`) VALUES  " + info;
        Integer rowAdd = namedParameterJdbcTemplate.update(insertSql, addMap);

        return rowAdd;
    }

    // 插入角色和权限关联表数据
    public Integer addRolePermission(String info){
        Map map = new HashMap<>();
        String insertSql = " INSERT INTO `roma_permission_role` (`role_id`,`permission_id`) VALUES  " + info;
        Integer row = namedParameterJdbcTemplate.update(insertSql, map);

        return row;
    }

    // 添加page_details表数据
    public Integer addPageDetails(Map<String, Object> bindValue, String editId) {
        String sql = "";
        if (editId.length()==0) {
            sql = " INSERT INTO `page_details` (`page_name`,`page_code`,`page_json`,`created_at`,`is_enable`," +
                    "`front_path`,`page_description`)  VALUES (:pageName,:pageCode,:pageJson,:createdAt,:isEnable,:frontPath,:pageDesc)  ";
            KeyHolder keyHolder = new GeneratedKeyHolder();
            Integer row = namedParameterJdbcTemplate.update(sql, new MapSqlParameterSource(bindValue), keyHolder);
            int k = keyHolder.getKey().intValue();

            return k;
        } else {
            bindValue.put("id", editId);
            sql = " UPDATE `page_details` SET `page_name`=:pageName,`page_code`=:pageCode,`page_json`=:pageJson,`is_enable`=:isEnable," +
                    "`front_path`=:frontPath,`page_description`=:pageDesc WHERE id=:id";
            Integer row = namedParameterJdbcTemplate.update(sql, bindValue);

            return row;
        }
    }

    // 添加/编辑数据源数据
    public Integer saveDatasource(Map<String, Object> bindValue, String editId) {
        String sql = "";
        if (editId.length()==0) {
            sql = " INSERT INTO `datasource_config` (`datasource_url`,`datasource_code`,`datasource_username`," +
                    "`datasource_password`,`created_at`)  " +
                    "VALUES (:datasourceUrl,:datasourceCode,:datasourceUsername,:datasourcePassword,:createdAt)  ";
            KeyHolder keyHolder = new GeneratedKeyHolder();
            Integer row = namedParameterJdbcTemplate.update(sql, new MapSqlParameterSource(bindValue), keyHolder);
            int k = keyHolder.getKey().intValue();

            return k;
        } else {
            bindValue.put("id", editId);
            sql = " UPDATE `datasource_config` SET `datasource_url`=:datasourceUrl,`datasource_code`=:datasourceCode," +
                    "`datasource_username`=:datasourceUsername,`datasource_password`=:datasourcePassword " +
                    " WHERE id=:id";
            Integer row = namedParameterJdbcTemplate.update(sql, bindValue);

            return row;
        }
    }

    // 添加/编辑sql_details表数据
    public Integer addSqlDetails(Map<String, Object> bindValue, String editId) {
        String sql = "";
        if (editId.length()==0) {
            sql = " INSERT INTO `sql_details` (`sql_name`,`sql_code`,`sql_string`,`sql_type`,`is_list`," +
                    "`is_enable`,`created_user`,`created_at`,`sql_description`, `datasource_code`)  " +
                    "VALUES (:sqlName,:sqlCode,:sqlString,:sqlType,:isList,:isEnable,:actionUser,:createdAt, :sqlDesc, :datasourceCode)  ";
            KeyHolder keyHolder = new GeneratedKeyHolder();
            Integer row = namedParameterJdbcTemplate.update(sql, new MapSqlParameterSource(bindValue), keyHolder);
            int k = keyHolder.getKey().intValue();

            return k;
        } else {
            bindValue.put("id", editId);
            sql = " UPDATE `sql_details` SET `sql_name`=:sqlName,`sql_code`=:sqlCode,`sql_string`=:sqlString,`sql_type`=:sqlType," +
                    "`is_list`=:isList,`is_enable`=:isEnable,`sql_description`=:sqlDesc,`update_user`=:actionUser,`datasource_code`=:datasourceCode WHERE id=:id";
            Integer row = namedParameterJdbcTemplate.update(sql, bindValue);

            return row;
        }
    }

    // 添加/编辑api_details表数据
    public Integer saveApiDetails(Map<String, Object> bindValue, String editId) {
        String sql = "";
        if (editId.length()==0) {
            sql = " INSERT INTO `api_details` (`api_name`,`api_code`,`api_description`,`is_enable`,`created_at`) " +
                    "VALUES (:apiName,:apiCode,:apiDesc,:isEnable,:createdAt)  ";
            KeyHolder keyHolder = new GeneratedKeyHolder();
            Integer row = namedParameterJdbcTemplate.update(sql, new MapSqlParameterSource(bindValue), keyHolder);
            int k = keyHolder.getKey().intValue();

            return k;
        } else {
            bindValue.put("id", editId);
            sql = " UPDATE `api_details` SET `api_name`=:apiName,`api_code`=:apiCode,`api_description`=:apiDesc," +
                    "`is_enable`=:isEnable WHERE id=:id";
            Integer row = namedParameterJdbcTemplate.update(sql, bindValue);

            return row;
        }
    }

    // 添加/编辑item_details表数据
    public Integer saveItemDetails(Map<String, Object> bindValue, String editId) {
        String sql = "";
        if (editId.length()==0) {
            sql = " INSERT INTO `item_details` (`item_name`,`item_code`,`item_description`,`is_enable`,`created_at`) " +
                    "VALUES (:itemName,:itemCode,:itemDesc,:isEnable,:createdAt)  ";
            KeyHolder keyHolder = new GeneratedKeyHolder();
            Integer row = namedParameterJdbcTemplate.update(sql, new MapSqlParameterSource(bindValue), keyHolder);
            int k = keyHolder.getKey().intValue();

            return k;
        } else {
            bindValue.put("id", editId);
            sql = " UPDATE `item_details` SET `item_name`=:itemName,`item_code`=:itemCode,`item_description`=:itemDesc," +
                    "`is_enable`=:isEnable WHERE id=:id";
            Integer row = namedParameterJdbcTemplate.update(sql, bindValue);

            return row;
        }
    }

    // Role角色管理-获取总数
    public Integer getAllRoleListHasPagingCount(Map bindValue) {
        // 获取总数
        String countInnerSql = " SELECT * FROM `roma_roles` WHERE 1=1 ";
        String countSql = "SELECT COUNT(*) AS `totalData` FROM ( " + countInnerSql + " ) AS ROMA";
        Integer count = namedParameterJdbcTemplate.queryForObject(countSql, bindValue, Integer.class);

        return count;
    }

    // Page页面管理-获取带分页的列表数据
    public List<Map<String, Object>> getAllRoleListHasPaging(Map bindValue, String page, String perPage, Integer count) {
        // 计算分页
        String limitAfter = commonUtil.calcPagingString(count, page, perPage);
        String listSql = "SELECT  *  FROM `roma_roles` WHERE 1=1 ORDER BY `id` DESC " + " LIMIT " + limitAfter;

        List<Map<String, Object>> resMap2 = namedParameterJdbcTemplate.queryForList(listSql, bindValue);

        return resMap2;
    }

    //获取全部角色
    public List<Map<String, Object>> getAllRoleForList(){
        Map<String, Object> paramMap = new HashMap<>();
        String sql = " SELECT  * FROM `roma_roles`";
        List<Map<String, Object>> resMap2 = namedParameterJdbcTemplate.queryForList(sql, paramMap);

        return resMap2;
    }

    // 添加用户
    public Integer saveUser(Map bindValue, String userRole, String userId) {
        if (userId.length() > 0) {
            bindValue.put("id", userId);
            // 如果是编辑用户
            String updateUserInfoSql = " UPDATE `admin_user` SET `admin_name`=:adminName,`admin_avatar`=:apiUploadPic," +
                    "`is_enable`=:isEnable,`admin_email`=:adminEmail,`admin_phone`=:adminPhone WHERE id=:id ";
            Integer row = namedParameterJdbcTemplate.update(updateUserInfoSql, bindValue);
            if (row > 0) {
                Map<String, Object> delParams = new HashMap<>();
                delParams.put("id", userId);
                String delRolePermissionSql = " DELETE FROM `roma_role_user` WHERE user_id=:id ";
                namedParameterJdbcTemplate.update(delRolePermissionSql, delParams);
                if (userRole.length() > 0) {
                    // 如果添加用户,选择了角色
                    LinkedHashSet<String> uniqueRole = new LinkedHashSet<String>();
                    for (String s : userRole.split(",")) {
                        uniqueRole.add(s);
                    }
                    // 循环去重后的数据
                    String values = "";
                    for (String val : uniqueRole) {
                        values += " (" + userId + "," + val + "),";
                    }
                    Map addRoleUserMap = new HashMap<>();
                    values = commonUtil.trimFirstAndLastChar(values, ",");
                    String insertSql = " INSERT INTO `roma_role_user` (`user_id`, `role_id`) VALUES  " + values;
                    Integer addRoleUserRow = namedParameterJdbcTemplate.update(insertSql, addRoleUserMap);

                    return addRoleUserRow;
                } else {
                    return row;
                }
            }

            return 0;
        } else {
            // 如果是添加用户
            String sql = " INSERT INTO `admin_user` (`admin_name`,`admin_password`,`created_at`,`admin_avatar`," +
                    "`is_enable`,`admin_email`,`admin_phone`)" +
                    " VALUES (:adminName,:adminPassword,:createdAt,:apiUploadPic,:isEnable,:adminEmail,:adminPhone)  ";
            KeyHolder keyHolder = new GeneratedKeyHolder();
            Integer row = namedParameterJdbcTemplate.update(sql, new MapSqlParameterSource(bindValue), keyHolder);
            int k = keyHolder.getKey().intValue();
            if (userRole.length() > 0) {
                // 如果添加用户,选择了角色
                LinkedHashSet<String> uniqueRole = new LinkedHashSet<String>();
                for (String s : userRole.split(",")) {
                    uniqueRole.add(s);
                }
                // 循环去重后的数据
                String values = "";
                for (String val : uniqueRole) {
                    values += " (" + k + "," + val + "),";
                }
                Map addRoleUserMap = new HashMap<>();
                values = commonUtil.trimFirstAndLastChar(values, ",");
                String insertSql = " INSERT INTO `roma_role_user` (`user_id`, `role_id`) VALUES  " + values;
                Integer addRoleUserRow = namedParameterJdbcTemplate.update(insertSql, addRoleUserMap);

                return addRoleUserRow;
            } else {
                return k;
            }
        }
    }

    // 添加数据到roma_permission表
    public Integer savePermission(Map bindValue, String id) {
        try {
            if (id.length() > 0) {
                bindValue.put("id", id);
                String updateSql = " UPDATE `roma_permissions` SET `parent_id`=:parentId,`object_type`=:objectType," +
                        "`object_id`=:objectId,`is_dir`=:isMenu,`permission_sort`=:permissionSort," +
                        "`permissions_icon`=:permissionsIcon,`is_enable`=:isEnable,`created_at`=:createdAt," +
                        "`menu_name`=:menuName,`is_visible`=:isVisible,`permission_describe`=:permissionDescribe " +
                        " WHERE id=:id ";
                Integer updateRet = namedParameterJdbcTemplate.update(updateSql, bindValue);
                return updateRet;
            } else {
                String insertAql = " INSERT INTO `roma_permissions` (`parent_id`,`object_type`,`object_id`,`is_dir`," +
                        "`permission_sort`,`permissions_icon`,`is_enable`,`created_at`,`menu_name`,`is_visible`,`permission_describe`)" +
                        " VALUES (:parentId,:objectType,:objectId,:isMenu,:permissionSort,:permissionsIcon,:isEnable,:createdAt," +
                        ":menuName,:isVisible,:permissionDescribe)  ";
                KeyHolder keyHolder = new GeneratedKeyHolder();
                Integer row = namedParameterJdbcTemplate.update(insertAql, new MapSqlParameterSource(bindValue), keyHolder);
                int k = keyHolder.getKey().intValue();

                return k;
            }
        }catch (Exception e){
            return 0;
        }
    }

    // 删除权限,判断权限是否有子权限
    public Boolean hasSonPermissionById(Map bindValue) {
        String hasSql = " SELECT * FROM `roma_permissions` WHERE `parent_id`=:id ";
        List<Map<String, Object>> resMap2 = namedParameterJdbcTemplate.queryForList(hasSql, bindValue);
        if (resMap2 == null || resMap2.size() < 1) {
            return true;
        }

        return false;
    }

    // 没有被权限使用,则删除基础details表数据
    public Integer deletePermissionById(Map bindValue) {
        String sql = " DELETE FROM `roma_permissions` WHERE id=:id ";
        Integer updateRet = namedParameterJdbcTemplate.update(sql, bindValue);

        return updateRet;
    }

    // 判断基础表数据,是否被权限表引用. 返回true表示没有数据被权限表引用
    public Boolean usedPermissionByObjectId(Map bindValue){
        String hasUserSql = " SELECT * FROM `roma_permissions` WHERE `object_type`=:objectType AND `object_id`=:objectId ";
        List<Map<String, Object>> resMap2 = namedParameterJdbcTemplate.queryForList(hasUserSql, bindValue);
        if (resMap2 == null || resMap2.size() < 1) {
            return true;
        }

        return false;
    }

    // 没有被权限使用,则删除基础details表数据
    public Integer deleteDetailsDataByTableNameAndId(Map bindValue, String tableName) {
        String sql = " DELETE FROM `"+tableName+"` WHERE id=:id ";
        Integer updateRet = namedParameterJdbcTemplate.update(sql, bindValue);

        return updateRet;
    }

    // 判断是否有sql在使用此数据源
    public Boolean usedDatasource(Map params){
        Map sqlParams = new HashMap<>();
        sqlParams.put("datasourceCode", params.get("datasource_code"));
        String hasUserSql = " SELECT * FROM `sql_details` WHERE `datasource_code`=:datasourceCode";
        List<Map<String, Object>> resMap2 = namedParameterJdbcTemplate.queryForList(hasUserSql, sqlParams);
        if (resMap2 == null || resMap2.size() < 1) {
            return true;
        }

        return false;
    }

    // 通过id删除此数据
    public Integer deleteDatasourceById(Map bindValue) {
        String sql = " DELETE FROM `datasource_config` WHERE id=:id ";
        Integer updateRet = namedParameterJdbcTemplate.update(sql, bindValue);

        return updateRet;
    }
}