com.jfinal.plugin.activerecord.Record Java Examples

@Override
public void forDbUpdate(String tableName, String[] pKeys, Object[] ids, Record record, final StringBuilder sql, List<Object> paras) {
    Dialect builtInDelegate = findCompatibleDialect();
    if (builtInDelegate != null) {
        builtInDelegate.forDbUpdate(tableName, pKeys, ids, record, sql, paras);
        return;
    }

    tableName = tableName.trim();
    trimPrimaryKeys(pKeys);

    sql.append("update ").append(tableName).append(" set ");
    for (Map.Entry<String, Object> e : record.getColumns().entrySet()) {
        String colName = e.getKey();
        if (!isPrimaryKey(colName, pKeys)) {
            if (!paras.isEmpty()) {
                sql.append(", ");
            }
            sql.append(colName).append(" = ? ");
            paras.add(e.getValue());
        }
    }
    sql.append(" where ");
    appendWhereParamters(sql, pKeys);
}
 

@Override
@CachesEvict({
        @CacheEvict(name = "products", key = "*"),
        @CacheEvict(name = "product-category", key = "#(productId)"),
})
public void doUpdateCategorys(long productId, Long[] categoryIds) {
    Db.tx(() -> {
        Db.update("delete from product_category_mapping where product_id = ?", productId);

        if (categoryIds != null && categoryIds.length > 0) {
            List<Record> records = new ArrayList<>();
            for (long categoryId : categoryIds) {
                Record record = new Record();
                record.set("product_id", productId);
                record.set("category_id", categoryId);
                records.add(record);
            }
            Db.batchSave("product_category_mapping", records, records.size());
        }

        return true;
    });
}
 

@Override

    @CachesEvict({
            @CacheEvict(name = "articles", key = "*"),
            @CacheEvict(name = "article-category", key = "#(articleId)"),
    })
    public void doUpdateCategorys(long articleId, Long[] categoryIds) {

        Db.tx(() -> {
            Db.update("delete from article_category_mapping where article_id = ?", articleId);

            if (categoryIds != null && categoryIds.length > 0) {
                List<Record> records = new ArrayList<>();
                for (long categoryId : categoryIds) {
                    Record record = new Record();
                    record.set("article_id", articleId);
                    record.set("category_id", categoryId);
                    records.add(record);
                }
                Db.batchSave("article_category_mapping", records, records.size());
            }

            return true;
        });
    }
 

@Override
@CachesEvict({
        @CacheEvict(name = "user_role", key = "*"),
        @CacheEvict(name = "user_permission", key = "*")
})
public boolean doResetUserRoles(long userId, Long... RoleIds) {
    if (RoleIds == null || RoleIds.length == 0) {
        return Db.delete("delete from user_role_mapping where user_id = ? ", userId) > 0;
    }

    return Db.tx(() -> {
        Db.delete("delete from user_role_mapping where user_id = ? ", userId);

        List<Record> records = new ArrayList<>();
        for (Long roleId : RoleIds) {
            Record record = new Record();
            record.set("user_id", userId);
            record.set("role_id", roleId);
            records.add(record);
        }

        Db.batchSave("user_role_mapping", records, records.size());

        return true;
    });
}
 

@Override
public boolean hasAnyRole(long userId) {
    List<Record> records = findAllUserRoleMapping();
    if (records == null || records.isEmpty()) {
        return false;
    }

    for (Record record : records){
        Long uid = record.getLong("user_id");
        if (uid != null && uid.equals(userId)){
            return true;
        }
    }

    return false;
}
 

@Override
@Cacheable(name = "user_permission", key = "role:#(roleId)", nullCacheEnable = true)
public List<Permission> findPermissionListByRoleId(long roleId) {
    String sql = "select * from role_permission_mapping where role_id = ? ";
    List<Record> rolePermissionRecords = Db.find(sql, roleId);
    if (rolePermissionRecords == null || rolePermissionRecords.isEmpty()) {
        return null;
    }

    List<Permission> permissionList = new ArrayList<>();
    for (Record rolePermissionRecord : rolePermissionRecords) {
        Permission permission = findById(rolePermissionRecord.getLong("permission_id"));
        if (permission != null) {
            permissionList.add(permission);
        }
    }

    return permissionList;
}
 

@Override
@Cacheable(name = "user_permission", key = "user_permissions:#(userId)", nullCacheEnable = true)
public List<Permission> findPermissionListByUserId(long userId) {

    Set<Permission> permissions = new HashSet<>();
    String sql = "select * from user_role_mapping where user_id = ? ";
    List<Record> userRoleRecords = Db.find(sql, userId);
    if (userRoleRecords != null) {
        for (Record userRoleRecord : userRoleRecords) {
            List<Permission> rolePermissions = findPermissionListByRoleId(userRoleRecord.getLong("role_id"));
            if (rolePermissions != null) {
                permissions.addAll(rolePermissions);
            }
        }
    }

    return new ArrayList<>(permissions);
}
 

@Override
public void doUpdateTags(long userId, Long[] tagIds) {
    Db.tx(() -> {
        Db.update("delete from user_tag_mapping where user_id = ?", userId);

        if (tagIds != null && tagIds.length > 0) {
            List<Record> records = new ArrayList<>();
            for (long tagId : tagIds) {
                Record record = new Record();
                record.set("user_id", userId);
                record.set("tag_id", tagId);
                records.add(record);
            }
            Db.batchSave("user_tag_mapping", records, records.size());
        }

        return true;
    });
}
 

/**
 * 通过 menuId 和  roleId 查询按钮列表
 */
public void buttonList(){
  String menuId = getPara("menuId");
  String roleId = getPara("roleId");
  if(StringUtils.isEmpty(menuId) || StringUtils.isEmpty(roleId) ){
      renderJson(new ArrayList<>());
      return;
  }
  String sql = "SELECT a.id,a.buttonTxt,a.buttonCode,b.sysRoleId as checkFlag " +
          " FROM  sys_button a " +
          "	left join sys_role_button b on a.id = b.sysButtonId and b.sysRoleId = ? " +
          "WHERE " +
          "	sysMenuId = ? ";
  List<Record> btnList = Db.find(sql,roleId,menuId);
  renderJson(btnList);
}
 

/**
 * Record转为Map，驼峰命名
 *
 * @param record
 * @return
 */
public static Map<String, Object> recordToCamelCaseMap(Record record) {
    if (null == record) {
        return null;
    }
    String[] keys = record.getColumnNames();
    Map<String, Object> map = new HashMap<>();
    for (String key : keys) {
        Object value = record.get(key);
        key = StrKit.toCamelCase(key.toLowerCase());
        if (null != value) {
            map.put(key, value);
        }
    }
    return map;
}
 

/**
 * 查询某节点子孙树节点
 * @param rootId 根id
 * @param tableName 数据表名
 * @param idFieldName id 字段
 * @param pidFieldName pid 字段名
 * @return
 */
public static String getSonTreeIds(String rootId, String tableName, String idFieldName, String pidFieldName) {
    String sTemp = "$";
    String sTempChd = rootId;
    Record recordTemp;
    while (sTempChd != null) {
        sTemp = sTemp.concat(",").concat(sTempChd);
        recordTemp = Db.findFirst("SELECT group_concat(" + idFieldName + ") as sTempChd  FROM " + tableName + " where FIND_IN_SET(" + pidFieldName + ",'" + sTempChd + "')>0;");
        if (recordTemp == null) {
            sTempChd = null;
        } else {
            sTempChd = recordTemp.getStr("sTempChd");
        }
    }
    return sTemp.replaceAll("\\$,", "");
}
 

@Override
public boolean updateUserAmount(Object userId, BigDecimal oldAmount, BigDecimal updateAmount) {
    BigDecimal value = JbootDb.queryBigDecimal("select amount from user_amount where user_id = ?", userId);
    if (value == null) {
        Record record = new Record();
        record.set("user_id", userId);
        record.set("amount", updateAmount);
        record.set("modified", new Date());
        record.set("created", new Date());
        return Db.save("user_amount", record);
    } else {
        return Db.update("update user_amount set amount = ? , modified = ? where user_id = ? and amount = ?",
                value.add(updateAmount), new Date(), userId, oldAmount) > 0;
    }
}
 

/**
 * Page<Record>转为Page<Map<String, Object>>，驼峰命名
 *
 * @param records
 * @return
 */
public static Page<Map<String, Object>> recordsToCamelCaseMaps(Page<Record> records) {
    List<Record> recordList = records.getList();
    List<Map<String, Object>> maps = new ArrayList<>();
    for (Record record : recordList) {
        maps.add(recordToCamelCaseMap(record));
    }
    return new Page<>(maps, records.getPageNumber(), records.getPageSize(),
            records.getTotalPage(), records.getTotalRow());
}
 

@Override
public List<FavoriteGoodsDTO> list(String userId) {
    SqlPara sqlPara =  Db.getSqlPara("favoriteGoods.list", Kv.by("userId", userId));
    List<Record> recordList = Db.find(sqlPara);
    List<FavoriteGoodsDTO> favoriteGoodsDTOList = RecordUtils.converModel(recordList, FavoriteGoodsDTO.class);
    return favoriteGoodsDTOList;
}
 

public void query() {
    Map<String, AtomicInteger> cacheAsMap = CacheContainer.getLoginRetryLimitCache().getCache().asMap();
    Set<String> userNameSet = new LinkedHashSet<>();
    cacheAsMap.forEach((K, V) -> {
        if (V.get() >= LoginRetryLimitCache.RETRY_LIMIT) {
            userNameSet.add(K);
        }
    });
    String ids = "'" + Joiner.on("','").join(userNameSet) + "'";
    List<Record> records = Db.find("select id,username,realName,job from sys_user where username in (" + ids + ")");
    renderDatagrid(records);
}
 

/**
 * 通过用户id 查询 角色id
 *
 * @param userId
 * @return
 */
public String findRoleIdsByUserId(String userId) {
    String sql = " select GROUP_CONCAT(c.id) as roleIds" +
            "  from sys_user a, sys_user_role b,sys_role c " +
            "  where a.id = b.sysUserId and b.sysRoleId = c.id  and a.id = ? ";
    Record record = Db.findFirst(sql, userId);
    return record.getStr("roleIds");
}
 

/**
 * 通过用户查询角色编码
 *
 * @param userId
 * @return
 */
public String findRoleCodesByUserId(String userId) {
    String sql = "select GROUP_CONCAT(c.roleCode) as roleCodes" +
            "  from sys_user a, sys_user_role b,sys_role c " +
            "  where a.id = b.sysUserId and b.sysRoleId = c.id  and a.id = ? ";
    Record record = Db.findFirst(sql, userId);
    return record.getStr("roleCodes");
}
 

/**
 * 获得 系统通知  关联的所有用户id
 *
 * @param noticeTypeId 系统通知类型id
 * @return
 */
private Set<String> getNotificationReceivers(String noticeTypeId) {
    Set<String> userIdSet = new HashSet<>();
    // 通知类型 关联的角色，角色关联的用户
    List<Record> userIds = SysNoticeTypeSysRole.dao.findUserIdsByNoticeType(noticeTypeId);
    userIds.forEach(record -> userIdSet.add(record.get("sysUserId")));
    return userIdSet;
}
 

@Override
public List<UserTag> findListByUserId(Object userId) {
    List<Record> mapping = Db.find("select * from user_tag_mapping where user_id = ?",userId);
    if (mapping == null || mapping.isEmpty()){
        return null;
    }

    return mapping.stream()
            .map(record -> findById(record.get("tag_id")))
            .collect(Collectors.toList());

}
 

public List<Long> queryAllMenuId(Long userId) {
    SqlPara sqlPara = Db.getSqlPara("sysUser.queryAllMenuId", Kv.by("userId", userId));
    List<Record> sysMenuList = Db.find(sqlPara);
    List<Long> menuIds = new ArrayList<>();
    for (Record r:sysMenuList
         ) {
        if(r == null || r.get("menu_id") == null) {
            continue;
        }
        menuIds.add(r.get("menu_id"));
    }

    return menuIds;
}
 

public List<String> queryAllPerms(Long userId) {
    SqlPara sqlPara = Db.getSqlPara("sysUser.queryAllPerms", Kv.by("userId", userId));
    List<Record> sysUserList = Db.find(sqlPara);
    List<String> perms = new ArrayList<>();
    for (Record r:sysUserList
         ) {
        if(r == null || r.get("perms") == null) {
            continue;
        }
        perms.add(r.get("perms"));
    }

    return perms;
}
 

public static Model<?> toModel(Class<? extends Model<?>> clazz, Record record) {
    Model<?> model = null;
    try {
        model = clazz.newInstance();
    } catch (Exception e) {
        LOG.error(e.getMessage(), e);
        return model;
    }
    model.put(record.getColumns());
    return model;
}
 

/**
 * 通过通知类型id 查询关联角色 再查询到相关联的用户
 *
 * @param noticeTypeId
 * @return
 */
public List<Record> findUserIdsByNoticeType(String noticeTypeId) {
    List<Record> userIds = Db.find(" SELECT d.sysUserId " +
            "FROM " +
            "( SELECT a.sysRoleId FROM sys_notice_type_sys_role a, sys_role b  WHERE a.sysRoleId = b.id  and a.sysNoticeTypeId = ? ) aa " +
            "LEFT JOIN sys_user_role d ON aa.sysRoleId = d.sysRoleId", noticeTypeId);
    return userIds;
}
 

/**
 * Data Count
 */
public Long dataCount() {
	SqlPara sql = SqlpKit.select(this, "count(*) AS cnt");
	Record record = Db.findFirst(sql);
	if (null != record) {
		return record.get("cnt");
	}
	return 0L;
}
 

@Override
public boolean deleteById(Object id) {

    //搜索搜索引擎的内容
    ArticleSearcherFactory.getSearcher().deleteArticle(id);

    return Db.tx(() -> {
        boolean delOk = ArticleServiceProvider.super.deleteById(id);
        if (delOk == false) {
            return false;
        }

        //删除文章的管理分类
        List<Record> records = Db.find("select * from article_category_mapping where article_id = ? ", id);
        if (records != null && !records.isEmpty()) {
            //更新文章数量
            Db.update("delete from article_category_mapping where article_id = ?", id);
            records.forEach(record -> categoryService.doUpdateArticleCount(record.get("category_id")));
        }


        //删除文章的所有评论
        commentService.deleteByArticleId(id);

        return true;
    });
}
 

@Override
@Cacheable(name = "article-category", key = "#(articleId)", liveSeconds = 2 * CacheTime.HOUR, nullCacheEnable = true)
public List<ArticleCategory> findListByArticleId(long articleId) {
    List<Record> mappings = Db.find("select * from article_category_mapping where article_id = ?", articleId);
    if (mappings == null || mappings.isEmpty()) {
        return null;
    }

    return mappings
            .stream()
            .map(record -> DAO.findById(record.get("category_id")))
            .filter(Objects::nonNull)
            .collect(Collectors.toList());
}
 

@Override
public Long[] findCategoryIdsByArticleId(long articleId) {
    List<Record> records = Db.find("select * from article_category_mapping where article_id = ?", articleId);
    if (records == null || records.isEmpty()) {
        return null;
    }

    return ArrayUtils.toObject(records.stream().mapToLong(record -> record.get("category_id")).toArray());
}
 

@Override
public void forDbSave(String tableName, String[] pKeys, Record record, StringBuilder sql, List<Object> paras) {

    Dialect builtInDelegate = findCompatibleDialect();
    if (builtInDelegate != null) {
        builtInDelegate.forDbSave(tableName, pKeys, record, sql, paras);
        return;
    }


    tableName = tableName.trim();
    trimPrimaryKeys(pKeys);

    sql.append("insert into ");
    sql.append(tableName).append('(');
    StringBuilder temp = new StringBuilder();
    temp.append(") values(");

    int count = 0;
    for (Map.Entry<String, Object> e : record.getColumns().entrySet()) {
        String colName = e.getKey();
        if (count++ > 0) {
            sql.append(", ");
            temp.append(", ");
        }
        sql.append(colName);

        Object value = e.getValue();
        if (value instanceof String && isPrimaryKey(colName, pKeys) && ((String) value).endsWith(".nextval")) {
            temp.append(value);
        } else {
            temp.append('?');
            paras.add(value);
        }
    }
    sql.append(temp.toString()).append(')');
}
 

@Override
@CachesEvict({
        @CacheEvict(name = "product-category", key = "#(id)"),
        @CacheEvict(name = "products", key = "*")
})
public boolean deleteById(Object id) {

    //搜索搜索引擎的内容
    ProductSearcherFactory.getSearcher().deleteProduct(id);

    return Db.tx(() -> {
        boolean delOk = ProductServiceProvider.super.deleteById(id);
        if (delOk == false) {
            return false;
        }

        //删除文章的管理分类
        List<Record> records = Db.find("select * from product_category_mapping where product_id = ? ", id);
        if (records != null && !records.isEmpty()) {
            //更新文章数量
            Db.update("delete from product_category_mapping where product_id = ?", id);
            records.forEach(record -> categoryService.doUpdateProductCount(record.get("category_id")));
        }


        //删除产品的所有评论
        commentService.deleteByProductId(id);
        return true;
    });
}
 

/**
 * @param productId
 * @return
 */
@Override
@Cacheable(name = "product-category", key = "#(productId)", liveSeconds = 2 * CacheTime.HOUR, nullCacheEnable = true)
public List<ProductCategory> findListByProductId(long productId) {
    List<Record> mappings = Db.find("select * from product_category_mapping where product_id = ?", productId);
    if (mappings == null || mappings.isEmpty()) {
        return null;
    }

    return mappings
            .stream()
            .map(record -> DAO.findById(record.get("category_id")))
            .filter(Objects::nonNull)
            .collect(Collectors.toList());
}