JQPL using DBMS_LOB.SUBSTR function

Question

Intellij IDE can not recognize it[enter image description here]

@Query("SELECT distinct new com.product.dto.ProductDescriptionsDto" + " (p.id, p.type, CAST(DBMS_LOB.SUBSTR(p.description,4000,1) AS string )) FROM Prodcut p " " WHERE p.code = :code and p.language = :language " ) List<ProductDescriptionsDto> find(@Param("code") String code , @Param("language") String language); (https://i.stack.imgur.com/CYaF5.png)

How do we use it?

I waited it will be converted correct sql but I didn't saw it

Answer 1

SELECT distinct
       new com.product.dto.ProductDescriptionsDto(
         p.id,
         p.type,
         CAST(DBMS_LOB.SUBSTR(p.description,4000,1) AS string )
       )
FROM   Prodcut p
WHERE  p.code = :code
and    p.language = :language

Looks like you are trying to mix Java and SQL into one statement and what you have is not valid Java (since you are using SQL statements) and is not valid SQL since a.b.c would be valid syntax as schema_name.package_name.function_name but a.b.c.d is not valid syntax (unless you are dereferencing object-derived tables and, in that case, using the NEW keyword would not be valid).

---

If you are trying to call a Java function from SQL then wrap the Java function in an SQL function (something like, untested):

CREATE AND COMPILE JAVA SOURCE NAMED ProductDescriptions AS
public class ProductDescriptions {
  public static String ProductDescriptionsDto(
    id          int,
    type        String,
    description String
  )
  {
    return com.product.dto.ProductDescriptionsDto(id, type, description);
  }
}

Then you need to create a PL/SQL function to call the Java class's function:

CREATE FUNCTION ProductDescriptions(
  in_id          IN NUMBER,
  in_type        IN VARCHAR2,
  in_description IN VARCHAR2
) RETURN VARCHAR2
AS LANGUAGE JAVA NAME 'ProductDescriptions.ProductDescriptionsDto(int, java.lang.String, java.lang.String) return java.lang.String';
/

And then the SQL code would look something like:

SELECT distinct 
       ProductDescriptions(
         p.id,
         p.type,
         CAST(DBMS_LOB.SUBSTR(p.description,4000,1) AS string )
       )
FROM   Prodcut p
WHERE  p.code = :code
and    p.language = :language

---

If you are trying to call a Java class constructor then you need to map from the Java class to an Oracle type using something like this answer.

If you are doing something else then you need to explain what you are trying to achieve because your code is not clear.

Answer 2

Please check: How to call custom database functions with JPA and Hibernate

for your case that would be:

 @Query(
 "SELECT distinct "
 + "new com.product.dto.ProductDescriptionsDto(p.id, p.type, function('DBMS_LOB.SUBSTR', p.description, 4000, 1)) "
 + "FROM Prodcut p WHERE p.code = :code and p.language = :language "
) 
List<ProductDescriptionsDto> find(@Param("code") String code , @Param("language") String language);