时间戳

首页 > 解决方案 > Postgresql query runs manuaIIy but returns an error inside Java code

java - Postgresql query runs manuaIIy but returns an error inside Java code

问题描述

The query is:

WITH part_list AS ( 
    SELECT pd.id AS part_data_id, b.billname, pd.refpn, pd.originalpn, pd.catalogpn, pd.originalmfg, mfg.market, pd.contentid, b.accountid 
    FROM price_data pd 
    JOIN bills_price_data bpd ON pd.id = bpd.price_data_id 
    JOIN bill b ON bpd.bill_id = b.id 
    LEFT JOIN markets mfg ON pd.marketid = mfg.id 
    WHERE b.id = 35699799 -- ?#{#billId}  
) 
SELECT COUNT(*) FROM (
SELECT d.part_data_id, d.billname, d.refpn, d.originalpn, d.catalogpn,
 d.originalmfg, d.market, cd.declaration_type, 
       'Yes' AS rate_stock_contained, 
       s.id AS stock_id, s.stock, s.cas 
FROM part_list d  
INNER JOIN content c ON d.contentid = c.id 
INNER JOIN content_declaration_type cd ON c.declarationtypeid = cd.id 
INNER JOIN content_cards cc ON c.id = cc.content_id 
INNER JOIN card_materials cm ON cc.id = cm.content_cards_id 
INNER JOIN material_stocks ms ON cm.id = ms.card_materials_id 
INNER JOIN stocks s ON ms.stocks_id = s.id 
WHERE c.declarationtypeid IN (2,3)  
AND s.cas ~ ',?(10099-76-0|11120-22-2|117-81-7|12036-10-1|129915-35-1|1317-36-8|1317-38-0|65997-18-4|7439-92-1|7631-86-9),?' 
        UNION 
SELECT       d.part_data_id, d.billname, d.refpn, d.originalpn, d.catalogpn, d.originalmfg, d.market 
    , cd.declaration_type 
    , 'No' AS rate_stock_contained 
    , 0 AS stock_id, '' AS stock, '' AS cas 
FROM part_list d  
INNER JOIN content c ON d.contentid = c.id 
INNER JOIN content_declaration_type cd ON c.declarationtypeid = cd.id 
WHERE c.declarationtypeid IN (2,3) -- Full/Partial 
AND NOT EXISTS ( 
        SELECT * 
        FROM part_list d2 
        JOIN content c2 ON d2.contentid = c2.id 
        JOIN content_declaration_type cd2 ON c2.declarationtypeid = cd2.id 
        JOIN content_cards cc2 ON c2.id = cc2.content_id 
        JOIN card_materials cm2 ON cc2.id = cm2.content_cards_id 
        JOIN material_stocks ms2 ON cm2.id = ms2.card_materials_id 
        JOIN stocks s2 ON ms2.stocks_id = s2.id 
        WHERE c2.declarationtypeid IN (2,3) 
        AND s2.cas ~ ',?(10099-76-0|11120-22-2|117-81-7|12036-10-1|129915-35-1|1317-36-8|1317-38-0|65997-18-4|7439-92-1|7631-86-9),?' 
        AND d2.part_data_id = d.part_data_id 
    ) 
        UNION 
SELECT       d.part_data_id, d.billname, d.refpn, d.originalpn, d.catalogpn, d.originalmfg, d.market 
    , cd.declaration_type 
    , 'Yes' AS rate_stock_contained , s.id AS stock_id, s.stock, s.cas 
FROM part_list d  
INNER JOIN content c ON d.contentid = c.id 
INNER JOIN content_declaration_type cd ON c.declarationtypeid = cd.id 
INNER JOIN content_restricted_stocks crs ON c.id = crs.content_id 
INNER JOIN stocks s ON crs.stock_id = s.id 
WHERE c.declarationtypeid NOT IN (2,3) -- Full/Partial 
AND s.cas ~ ',?(10099-76-0|11120-22-2|117-81-7|12036-10-1|129915-35-1|1317-36-8|1317-38-0|65997-18-4|7439-92-1|7631-86-9),?' 
        UNION 
SELECT       d.part_data_id, d.billname, d.refpn, d.originalpn, d.catalogpn, d.originalmfg, d.market 
    , cd.declaration_type 
    , CASE c.has_no_restricted_stocks WHEN true THEN 'No' ELSE 'Unknown' END AS rate_stock_contained 
    , 0 AS stock_id, '' AS stock, '' AS cas 
FROM part_list d  
JOIN content c ON d.contentid = c.id 
JOIN content_declaration_type cd ON c.declarationtypeid = cd.id 
WHERE c.declarationtypeid NOT IN (2,3) -- Full/Partial 
AND NOT EXISTS ( 
        SELECT * 
        FROM part_list d2 
        JOIN content c2 ON d2.contentid = c2.id 
        JOIN content_restricted_stocks crs2 ON c2.id = crs2.content_id 
        WHERE d2.part_data_id = d.part_data_id 
    )
) z

As a query, it runs correctly and it returns results. Inside Java, this error is returned:

Mar 14, 2021 9:22:13 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions WARN: SQL Error: 0, SQLState: 42601 Mar 14, 2021 9:22:13 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions ERROR: ERROR: syntax error at end of input Position: 3288

Is there a syntax issue I am not seeing? And why would it run manually but not inside my Java api ?

标签： javapostgresql

解决方案

你错过了一个右括号，我猜PostgreSQL只是忽略了它，但Java不是。括号似乎也在位置 3288 附近。所以我猜在z.

推荐阅读