Java Code Examples for com.jfinal.plugin.activerecord.Db#find()

@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);
}
 

/**
 * 通过 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);
}
 

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);
}
 

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

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

/**
 * @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());
}
 

@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
@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 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());

}
 

/**
 * 通过通知类型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;
}
 

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;
}
 

@Test
public void test() {
    List<Filter> filterList = new ArrayList<>();
    Filter filter = new Filter();
    filter.setProperty("aaa");
    filter.setOperator(Filter.Operator.eq);
    filter.setValue("123");
    filterList.add(filter);

    filter = new Filter();
    filter.setProperty(null);
    filter.setOperator(Filter.Operator.eq);
    filter.setValue("111");
    filterList.add(filter);

    filter = new Filter();
    filter.setProperty("bbb");
    filter.setOperator(Filter.Operator.eq);
    filter.setValue("321");
    filterList.add(filter);

    filter = new Filter();
    filter.setProperty("intest");
    filter.setOperator(Filter.Operator.in);
    filter.setValue(new ArrayList<String>() {
        {add("aaa");add("bbb");}
    });
    filterList.add(filter);
    SqlPara sqlPara = Db.getSqlPara("common.findList", Kv.by("tableName", "aaa").set("filters", filterList));
    List<Record> record = Db.find(sqlPara);
    log.info("{}", record);
}
 

@Override
public List<CartDTO> listDetail(String userId) {
    SqlPara sqlPara =  Db.getSqlPara("cart.listDetail", Kv.by("userId", userId));
    List<Record> recordList = Db.find(sqlPara);
    List<CartDTO> cartDTOList = RecordUtils.converModel(recordList, CartDTO.class);
    return cartDTOList;
}
 

@Override
public List<ProductDTO> listDetailByProductIds(String productIds) {
    String[] productIdArr = productIds.split(",");
    SqlPara sqlPara = Db.getSqlPara("product.listDetailByProductIds", Kv.by("productIds", productIdArr));
    List<Record> recordList = Db.find(sqlPara);
    List<ProductDTO> productDTOList = RecordUtils.converModel(recordList, ProductDTO.class);

    return productDTOList;
}
 

@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 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 void index() {
    List<Record> records = Db.find("select * from `user`");
    renderJson(records);
}
 

public void index() {
    List<Record> records = Db.find("select * from `user`");
    renderJson(JSON.toJSON(records));
}