How to call oracle function from spring boot application?

Question

My requirement is i need to call oracle function from spring boot application without using NATIVE query.

below is my oracle function which is taking Date as input

create or replace FUNCTION todate(src_dt IN date) 
 RETURN date
 is
  BEGIN 
  RETURN(to_date(src_dt)); 
  END;

i was searching solution on internet but so far did't find. people are saying some custom dialect need to create but not found any perfect step by step link.

below is my java code :-

 Query query1 = entityManager.createQuery("select todate(ActSubT.createdDt) from ActSubT ActSubT");
    List<Object> result=query1.getResultList();

this code should run , as of now its giving error as todate is oracle function and i haven't configured anything in application.yml file.

below error i am getting

  java.lang.IllegalArgumentException: org.hibernate.QueryException: 
  No data type for node: 
 org.hibernate.hql.internal.ast.tree.MethodNode 
 \-[METHOD_CALL] MethodNode: '('
  +-[METHOD_NAME] IdentNode: 'todate' {originalText=todate}

Please help

Check https://stackoverflow.com/questions/41837546/how-to-implement-the-simplest-alien-in-a-rest-webservice/41895512#41895512 — parthivrshah, Aug 16 '19 at 11:52
@parthivrshah i went through on this answer earlier, but my requirement is without using NATIVE query i need to call db function — Harish Bagora, Aug 16 '19 at 12:05
@parthivrshah can you check this link https://thoughts-on-java.org/database-functions/ we have option but i am not able to configure custom dialect entry in our yml file — Harish Bagora, Aug 16 '19 at 12:31
The page to which linked says if we want to use the function in the projection of a query - as your example does - we need to **Register function in Hibernate dialect**. Have you done that? — APC, Aug 16 '19 at 13:43
@APC yes now i able to resolve my issue , i posted proper answer.. thanks:) — Harish Bagora, Aug 16 '19 at 14:01

score 3 · Accepted Answer · answered Aug 16 '19 at 14:00

I am able to solve my issue .

step 1 :- i created custom dialect, below is my code..

public class CustomDialect extends Oracle12cDialect {
    public CustomDialect() {
        super();
        // CustomFunction implements SqlFunction
      //  registerFunction("custom_function", new CustomFunction());
        // or use StandardSQLFunction; useful for coalesce
        registerFunction("todate", new StandardSQLFunction("todate", StandardBasicTypes.DATE));
    }
}

Step 2 :- now i am calling todate function , below is the Java code

Query query1 = entityManager.createQuery("select function('todate',ActSubT.createdDt) from ActSubT ActSubT where ActSubT.id=1105619");

        Object resulth=query1.getSingleResult();

Step 3 :- this entry we need to put in application.poperties / application.yml

# Allows Hibernate to generate SQL optimized for a particular DBMS
spring.jpa.properties.hibernate.dialect = com.sbill.app.config.CustomDialect

That's all now i can call db function with java code.

How to call oracle function from spring boot application?

1 Answers1