0

I've been asked to convert the following native SQL query to JPQL.

SELECT
        * 
    FROM
        (SELECT
            CUSTOMER_NAME,
            CUSTOMER_ID,
            CUSTOMER_KEY,
            CUST_F_REF,
            UV_ID,
            IRC_SRC,
            -------
            -------          
            PRECEDING_CUSTOMER_KEY 
        FROM
            (  SELECT
                CD.*,
                CASE 
                    WHEN CD.CUSTOMER_KEY=CD.PREC_CUSTOMER_KEY       THEN NULL 
                    ELSE CD.PREC_CUSTOMER_KEY    
                END AS PRECEDING_CUSTOMER_KEY   
            FROM
                (   SELECT
                    CDATA_1.EXEC_DATE AS REFERENCE_DATE,
                    CDATA_2.*,
                    FIRST_VALUE(CDATA_2.CUSTOMER_KEY) OVER(    PARTITION 
                BY
                    CDATA_2.CUST_F_REF 
                ORDER BY
                    CDATA_2.EXEC_DATE ROWS 1 PRECEDING) AS PREC_CUSTOMER_KEY    
                FROM
                    (    SELECT
                        CUST_F_REF,
                        UV_ID,
                        IRC_SRC,
                        EXEC_DATE 
                    FROM
                        CUTOMER_DWH.QUERY_CUST_DATA_FULL_V SV 
                    WHERE
                        1=1 
                        AND CUSTOMER_KEY=? ) CDATA_1,
                    CUTOMER_DWH.QUERY_CUST_DATA_VCD_V CDATA_2 
                WHERE
                    CDATA_1.CUST_F_REF=CDATA_2.CUST_F_REF 
                    OR (
                        CDATA_1.UV_ID=CDATA_2.UV_ID 
                        AND CDATA_1.CUST_F_REF IS NULL
                    ) 
                    OR (
                        CDATA_1.IRC_SRC=CDATA_2.IRC_SRC 
                        AND CDATA_1.CUST_F_REF IS NULL 
                        AND CDATA_1.UV_ID IS NULL
                    )
                ) CD 
            WHERE
                CD.EXEC_DATE=CD.REFERENCE_DATE 
                AND CUSTOMER_KEY=? )) CDATA

It's one of the existing application done very long ago, so I've no clue about what's the actual expected output data. I studied & understood the query by myself .

1=1 needs to be removed. I have pasted the query as such as it exists.

The native query is executed as follows,

    em.createNativeQuery(query.toString(), QUERY_CUST_DATA_VCD_V.class);
    q.setParameter(1, customerKey);
    q.setParameter(2, customerKey);

After exploring multiple existing questions (like this) , I found that it's not possible to convert this nested select into JPQL. Also one of the key reason for requesting this change is to protect it against vulnerability attacks since it's publicly exposed application. After referring a similar thread here , I understood that the current native query is secure.

  1. Is it possible to convert this SQL query to JPQL / HQL / Criteria by any other means ?
  2. Does the existing implementation of NativeQuery is secure against vulnerability ?

Any suggestions would be of great help.

Thank you very much in advance.

explorer
  • 1,074
  • 2
  • 11
  • 31
  • 2
    Your query is large and is using analytic functions which are definitely not present in JPQL/Hibernate. You either are looking at a big rewrite, or you would need to execute this as a native query. Another approach would be to use a prepared stored procedure. – Tim Biegeleisen Feb 07 '20 at 10:29
  • @TimBiegeleisen Executing this as a native query is secure enough to prevent against attacks ? – explorer Feb 07 '20 at 10:39
  • It looks like you are already using a prepared statement, so you should be safe from attacks. – Tim Biegeleisen Feb 07 '20 at 10:43

0 Answers0