Hasan Can Saral's questions

注意：我正在使用 Spring Boot 2.2.0.RELEASE，所以与此无关。

我有一个MessagingGateway如下：

@Gateway(
            requestChannel = "myChannel",
            headers = @GatewayHeader(
                    name = "tenantId",
                    expression = "T(com.company.package.ContextHolder).getContext()?.tenant?.identifier"))
    void publishEvent(@Header(value = "eventType") String eventType,
                      @Payload Object payload);

我的ContextHolder课程类似于SecurityContextHolder或RequestContextHolder，并且我将一些与请求相关的基本信息放入其中，就像一样SecurityContext。

问题是，这在我的计算机上运行良好，并且对于大多数对该publishEvent方法的调用，它在服务器上也能正常工作。但是，对于某些方法，我得到：

org.springframework.expression.spel.SpelEvaluationException: EL1005E: Type cannot be found 'com.company.package.ContextHolder'
        at org.springframework.expression.spel.support.StandardTypeLocator.findType(StandardTypeLocator.java:117) ~[spring-expression-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
        at org.springframework.expression.spel.ExpressionState.findType(ExpressionState.java:155) ~[spring-expression-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
        at org.springframework.expression.spel.ast.TypeReference.getValueInternal(TypeReference.java:69) ~[spring-expression-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
        at org.springframework.expression.spel.ast.CompoundExpression.getValueRef(CompoundExpression.java:55) ~[spring-expression-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
        at org.springframework.expression.spel.ast.CompoundExpression.getValueInternal(CompoundExpression.java:91) ~[spring-expression-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
        at org.springframework.expression.spel.ast.SpelNodeImpl.getValue(SpelNodeImpl.java:112) ~[spring-expression-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
        at org.springframework.expression.spel.standard.SpelExpression.getValue(SpelExpression.java:330) ~[spring-expression-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
        at org.springframework.integration.gateway.GatewayMethodInboundMessageMapper.evaluateHeaders(GatewayMethodInboundMessageMapper.java:213) ~[spring-integration-core-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
        at org.springframework.integration.gateway.GatewayMethodInboundMessageMapper.access$1000(GatewayMethodInboundMessageMapper.java:86) ~[spring-integration-core-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
        at org.springframework.integration.gateway.GatewayMethodInboundMessageMapper$DefaultMethodArgsMessageMapper.buildMessage(GatewayMethodInboundMessageMapper.java:434) ~[spring-integration-core-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
        at org.springframework.integration.gateway.GatewayMethodInboundMessageMapper$DefaultMethodArgsMessageMapper.toMessage(GatewayMethodInboundMessageMapper.java:340) ~[spring-integration-core-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
        at org.springframework.integration.gateway.GatewayMethodInboundMessageMapper$DefaultMethodArgsMessageMapper.toMessage(GatewayMethodInboundMessageMapper.java:288) ~[spring-integration-core-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
        at org.springframework.integration.gateway.GatewayMethodInboundMessageMapper.mapArgumentsToMessage(GatewayMethodInboundMessageMapper.java:198) ~[spring-integration-core-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
        at org.springframework.integration.gateway.GatewayMethodInboundMessageMapper.toMessage(GatewayMethodInboundMessageMapper.java:192) ~[spring-integration-core-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
        at org.springframework.integration.gateway.GatewayMethodInboundMessageMapper.toMessage(GatewayMethodInboundMessageMapper.java:86) ~[spring-integration-core-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
        at org.springframework.integration.support.converter.SimpleMessageConverter.toMessage(SimpleMessageConverter.java:111) ~[spring-integration-core-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
        at org.springframework.messaging.core.AbstractMessageSendingTemplate.doConvert(AbstractMessageSendingTemplate.java:182) ~[spring-messaging-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
        at org.springframework.messaging.core.AbstractMessageSendingTemplate.convertAndSend(AbstractMessageSendingTemplate.java:150) ~[spring-messaging-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
        at org.springframework.messaging.core.AbstractMessageSendingTemplate.convertAndSend(AbstractMessageSendingTemplate.java:143) ~[spring-messaging-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
        at org.springframework.integration.gateway.MessagingGatewaySupport.send(MessagingGatewaySupport.java:417) ~[spring-integration-core-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
        at org.springframework.integration.gateway.GatewayProxyFactoryBean.sendOrSendAndReceive(GatewayProxyFactoryBean.java:576) ~[spring-integration-core-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
        at org.springframework.integration.gateway.GatewayProxyFactoryBean.invokeGatewayMethod(GatewayProxyFactoryBean.java:508) ~[spring-integration-core-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
        at org.springframework.integration.gateway.GatewayProxyFactoryBean.doInvoke(GatewayProxyFactoryBean.java:478) ~[spring-integration-core-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
        at org.springframework.integration.gateway.GatewayProxyFactoryBean.invoke(GatewayProxyFactoryBean.java:468) ~[spring-integration-core-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.2.0.RELEASE.jar!/:5.2.0.RELEASE]
        at com.sun.proxy.$Proxy168.publishEvent(Unknown Source) ~[na:na]
        at com.company.project.services.SomeService.lambda$bulk$2(SomeService.java:434) ~[project-api-0.5.0.RELEASE.jar!/:na]
        at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:183) ~[na:na]
        at java.base/java.util.stream.SpinedBuffer.forEach(SpinedBuffer.java:246) ~[na:na]
        at java.base/java.util.stream.Nodes$SpinedNodeBuilder.forEach(Nodes.java:1270) ~[na:na]
        at java.base/java.util.stream.Nodes$InternalNodeSpliterator$OfRef.forEachRemaining(Nodes.java:1105) ~[na:na]
        at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484) ~[na:na]
        at java.base/java.util.stream.ForEachOps$ForEachTask.compute(ForEachOps.java:290) ~[na:na]
        at java.base/java.util.concurrent.CountedCompleter.exec(CountedCompleter.java:746) ~[na:na]
        at java.base/java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:290) ~[na:na]
        at java.base/java.util.concurrent.ForkJoinPool$WorkQueue.topLevelExec(ForkJoinPool.java:1020) ~[na:na]
        at java.base/java.util.concurrent.ForkJoinPool.scan(ForkJoinPool.java:1656) ~[na:na]
        at java.base/java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1594) ~[na:na]
        at java.base/java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:183) ~[na:na]

并且bulk的方法SomeService有点像：

StreamSupport
    .stream(worksheet.spliterator(), true)
    .skip(1)
    .forEach(row -> {

        // insert row into db and publish message

        this.applicationEventGateway.publishEvent("rowCreated", row);

    });

Worksheet是 Apache POI 工作表。我知道该方法是在并行流中调用的，但这会导致找不到类的错误吗？我遗漏了什么？

我正在尝试使用来自https://ws-public.interpol.int/notices/v1/red的 json 。使用邮递员的请求完美运行：

但是当我尝试使用 SpringRestTemplate如下方法时（尝试准确复制请求标头）：

final HttpHeaders headers = new HttpHeaders();

headers.set(HttpHeaders.CACHE_CONTROL, "no-cache");
headers.set(HttpHeaders.USER_AGENT, "PostmanRuntime/7.42.0");
headers.set(HttpHeaders.HOST, "ws-public.interpol.int");
headers.set(HttpHeaders.ACCEPT, "*/*");
headers.set(HttpHeaders.ACCEPT_ENCODING, "gzip, deflate, br");
headers.set(HttpHeaders.CONNECTION, "keep-alive");

final HttpEntity<Void> entity = new HttpEntity<>((Void) null, headers);

final RestTemplate restTemplate = new RestTemplateBuilder()
    .setConnectTimeout(Duration.ofMillis(5000L))
    .setReadTimeout(Duration.ofMillis(5000L))
    .build();

final String response = restTemplate
    .exchange(URL, HttpMethod.GET, entity, String.class)
    .getBody();

我的请求失败，错误代码为 403 Forbidden：

Exception in thread "main" org.springframework.web.client.HttpClientErrorException$Forbidden: 403 Forbidden: "<HTML><HEAD><LF><TITLE>Access Denied</TITLE><LF></HEAD><BODY><LF><H1>Access Denied</H1><LF> <LF>You don't have permission to access "http&#58;&#47;&#47;ws&#45;public&#46;interpol&#46;int&#47;" on this server.<P><LF>Reference&#32;&#35;18&#46;1cf95568&#46;1730991641&#46;2abc645d<LF><P>https&#58;&#47;&#47;errors&#46;edgesuite&#46;net&#47;18&#46;1cf95568&#46;1730991641&#46;2abc645d</P><LF></BODY><LF></HTML><LF>"
    at org.springframework.web.client.HttpClientErrorException.create(HttpClientErrorException.java:109)
    at org.springframework.web.client.DefaultResponseErrorHandler.handleError(DefaultResponseErrorHandler.java:168)
    at org.springframework.web.client.DefaultResponseErrorHandler.handleError(DefaultResponseErrorHandler.java:122)
    at org.springframework.web.client.ResponseErrorHandler.handleError(ResponseErrorHandler.java:63)
    at org.springframework.web.client.RestTemplate.handleResponse(RestTemplate.java:819)
    at org.springframework.web.client.RestTemplate.doExecute(RestTemplate.java:777)
    at org.springframework.web.client.RestTemplate.execute(RestTemplate.java:711)
    at org.springframework.web.client.RestTemplate.exchange(RestTemplate.java:602)
    at com.company.sample.MySampleApp.main(MySampleApp.java:55)

我如何才能成功模仿 Postman 的请求？我错过了什么？

我有下表：

CREATE TABLE items
(
    id NUMERIC(20, 0) NOT NULL DEFAULT NEXTVAL('items_sequence') PRIMARY KEY,
    item_price NUMERIC(19, 2) DEFAULT NULL NULL,
    status NUMERIC(2, 0) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

具有以下索引：

CREATE INDEX items_dash_idx ON items (status, DATE(created_at));

我想按状态和每天对我的商品进行分组，持续约 30 天。这意味着我想获取过去 30 天内每个状态每天的商品数量和总价格，包括数量/金额为 0 的情况。我有 5 个状态，其中一个 (50) 不相关且行数太多（例如，状态 50 有约 400k 行，而状态 10、20、30 和 40 有约 1k 行，持续过去 30 天）。

我有以下疑问：

SELECT COUNT(i.id)                    AS count,
       COALESCE(SUM(i.item_price), 0) AS amount,
       dates_table.status,
       dates_table.created_at
FROM (SELECT created_at::DATE AS created_at, 10 AS status
      FROM GENERATE_SERIES('2024-09-18'::DATE, '2024-10-18'::DATE, INTERVAL '1 DAY') AS created_at
      UNION
      SELECT created_at::DATE AS created_at, 20 AS status
      FROM GENERATE_SERIES('2024-09-18'::DATE, '2024-10-18'::DATE, INTERVAL '1 DAY') AS created_at
      UNION
      SELECT created_at::DATE AS created_at, 30 AS status
      FROM GENERATE_SERIES('2024-09-18'::DATE, '2024-10-18'::DATE, INTERVAL '1 DAY') AS created_at
      UNION
      SELECT created_at::DATE AS created_at, 40 AS status
      FROM GENERATE_SERIES('2024-09-18'::DATE, '2024-10-18'::DATE, INTERVAL '1 DAY') AS created_at
     ) AS dates_table
LEFT JOIN items i 
       ON i.status = dates_table.status
      AND DATE(i.created_at) = dates_table.created_at
GROUP BY dates_table.created_at, dates_table.status
ORDER BY dates_table.created_at, dates_table.status;

此查询似乎需要 10 多秒，输出如下EXPLAIN (ANALYZE, BUFFERS)：

QUERY PLAN
Sort  (cost=2242005.05..2242006.05 rows=400 width=48) (actual time=21950.589..21950.601 rows=72 loops=1)
  Sort Key: dates_table.created_at, dates_table.status
  Sort Method: quicksort  Memory: 29kB
  Buffers: shared hit=676950 read=747852 dirtied=755, temp read=28515 written=28531
  ->  HashAggregate  (cost=2241982.76..2241987.76 rows=400 width=48) (actual time=21950.436..21950.492 rows=72 loops=1)
        Group Key: dates_table.created_at, dates_table.status
        Batches: 1  Memory Usage: 61kB
        Buffers: shared hit=676947 read=747852 dirtied=755, temp read=28515 written=28531
        ->  Merge Left Join  (cost=2161026.21..2239512.33 rows=247043 width=20) (actual time=21834.112..21948.382 rows=11066 loops=1)
              Merge Cond: ((dates_table.created_at = (date(i.created_at))) AND (((dates_table.status)::numeric) = i.status))
              Buffers: shared hit=676947 read=747852 dirtied=755, temp read=28515 written=28531
              ->  Sort  (cost=449.35..459.35 rows=4000 width=8) (actual time=895.905..895.933 rows=72 loops=1)
                    Sort Key: dates_table.created_at, ((dates_table.status)::numeric)
                    Sort Method: quicksort  Memory: 28kB
                    Buffers: shared hit=4
                    ->  Subquery Scan on dates_table  (cost=130.03..210.03 rows=4000 width=8) (actual time=895.792..895.846 rows=72 loops=1)
                          ->  HashAggregate  (cost=130.03..170.03 rows=4000 width=8) (actual time=895.788..895.831 rows=72 loops=1)
                                Group Key: ((created_ai.created_at)::date), (10)
                                Batches: 1  Memory Usage: 217kB
                                ->  Append  (cost=0.01..110.03 rows=4000 width=8) (actual time=895.697..895.749 rows=72 loops=1)
                                      ->  Function Scan on generate_series created_at  (cost=0.01..12.51 rows=1000 width=8) (actual time=895.694..895.697 rows=18 loops=1)
                                      ->  Function Scan on generate_series created_at_1  (cost=0.01..12.51 rows=1000 width=8) (actual time=0.012..0.014 rows=18 loops=1)
                                      ->  Function Scan on generate_series created_at_2  (cost=0.01..12.51 rows=1000 width=8) (actual time=0.010..0.012 rows=18 loops=1)
                                      ->  Function Scan on generate_series created_at_3  (cost=0.01..12.51 rows=1000 width=8) (actual time=0.010..0.012 rows=18 loops=1)
              ->  Materialize  (cost=2160576.87..2185898.76 rows=5064379 width=25) (actual time=19123.895..20601.926 rows=5066445 loops=1)
                    Buffers: shared hit=676943 read=747852 dirtied=755, temp read=28515 written=28531
                    ->  Sort  (cost=2160576.87..2173237.82 rows=5064379 width=25) (actual time=19123.888..20125.620 rows=5066445 loops=1)
                          Sort Key: (date(i.created_at)), i.status
                          Sort Method: external merge  Disk: 228120kB
                          Buffers: shared hit=676943 read=747852 dirtied=755, temp read=28515 written=28531
                          ->  Seq Scan on items i  (cost=0.00..1475438.79 rows=5064379 width=25) (actual time=0.064..16526.846 rows=5066445 loops=1)
                                Buffers: shared hit=676943 read=747852 dirtied=755
Planning Time: 0.399 ms
JIT:
  Functions: 44
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 2.096 ms, Inlining 293.474 ms, Optimization 383.558 ms, Emission 218.758 ms, Total 897.885 ms
Execution Time: 21989.150 ms

当我运行此查询时，我的缓存命中率从 99.9% 降至 50%。Oracle 中的相同索引（显然TRUNC(created_at)不是DATE(created_at)）和相同查询大约需要 500ms。

更新：

SELECT COUNT(i.id) AS count,
       SUM(i.item_price) AS amount,
       DATE(i.created_at) AS created_at,
       i.status AS status
FROM items i
WHERE i.status = 10 
AND DATE(i.created_at) >= '2024-10-01'
GROUP BY i.status, DATE(i.created_at)
ORDER BY i.status, DATE(i.created_at)

速度很快，输出如下EXPLAIN (ANALYZE, VERBOSE, BUFFERS, SETTINGS)：

QUERY PLAN
GroupAggregate  (cost=0.43..27.72 rows=9 width=49) (actual time=0.044..0.117 rows=9 loops=1)
  Output: count(id), sum(item_price), (date(created_at)), status
  Group Key: i.status, date(i.created_at)
  Buffers: shared hit=26
  ->  Index Scan using items_dash_idx  on tenant_xxx.items i  (cost=0.43..27.49 rows=9 width=21) (actual time=0.025..0.097 rows=24 loops=1)
        Output: date(created_at), status, id, item_price
        Index Cond: ((i.status = '10'::numeric) AND (date(i.created_at) >= '2024-10-01'::date))
        Buffers: shared hit=26
Settings: effective_cache_size = '16GB', search_path = 'tenant_xxx', work_mem = '32MB'
Planning:
  Buffers: shared hit=3
Planning Time: 0.255 ms
Execution Time: 0.140 ms

更新 #2：

如果我预先过滤项目，然后执行连接：

WITH items_table AS (SELECT COUNT(i.id)        AS count,
                            SUM(i.item_price)  AS amount,
                            DATE(i.created_at) AS created_at,
                            i.status           AS status
                     FROM items t
                     WHERE i.status IN (10, 20, 30, 40)
                       AND DATE(i.created_at) >= '2024-10-01'
                       AND DATE(i.created_at) < '2024-10-18'::DATE + INTERVAL '1 DAY'
                     GROUP BY i.status, DATE(i.created_at)
                     ORDER BY i.status, DATE(i.created_at))
SELECT dates_table.created_at          AS created_at,
       dates_table.status              AS status,
       COALESCE(items_table.count, 0)  AS count,
       COALESCE(items_table.amount, 0) AS amount
FROM (SELECT created_at::DATE               AS created_at
           , UNNEST(ARRAY [10, 20, 30, 40]) AS status
      FROM GENERATE_SERIES('2024-10-01'::DATE, '2024-10-18'::DATE, '1 DAY') AS created_at) AS dates_table
         LEFT JOIN items_table ON items_table.status = dates_table.status AND
                                  items_table.created_at = dates_table.created_at
ORDER BY dates_table.created_at, dates_table.status

它会在大约~100ms 后返回。

我有以下路线：

const DATA_CHILDREN: any[] = [{
  name: 'Child page',
  url: 'child-url'
}, {
  name: 'Child page two',
  url: 'child-url-two'
}];

const routes: Routes = [{
  path: 'data/some-url',
  loadChildren: ...
  component: SomeComponent,
  data: {
    pages: MENU_PAGES,
    children: DATA_CHILDREN,
  }
}];

我想DATA_CHILDREN从后端获取。我尝试将DATA_CHILDREN其转换为Promise，例如

let getDataChildren = (): Promise<Page[]> => {
  return inject(FormService).findRoutes();
};

这让我得到：

Uncaught RuntimeError: NG0203: inject() must be called from an injection context

我该如何处理这个问题？