如何在Spring Boot中无需原生查询调用自定义Oracle函数

Got it, let's work through this issue step by step. The core problem here is that Hibernate's JPQL doesn't automatically recognize your custom Oracle function todate. We need to explicitly register this function with Hibernate using a custom dialect, then configure your app to use that dialect.

Step 1: Create a Custom Hibernate Dialect Class #

First, we'll build a custom dialect that extends Hibernate's built-in Oracle dialect and registers your todate function. This tells Hibernate how to translate the JPQL function call into valid Oracle SQL.

package com.yourproject.persistence; // Replace with your actual package path

import org.hibernate.dialect.Oracle12cDialect;
import org.hibernate.type.StandardBasicTypes;
import org.hibernate.dialect.function.StandardSQLFunction;

public class CustomOracleDialect extends Oracle12cDialect {
    public CustomOracleDialect() {
        super();
        // Register the custom function: name, SQL function instance, return type
        registerFunction(
            "todate",
            new StandardSQLFunction("todate", StandardBasicTypes.DATE)
        );
    }
}

(Note: If you're using an older Oracle version, extend Oracle10gDialect or the appropriate dialect class instead of Oracle12cDialect.)

Step 2: Configure the Custom Dialect in application.yml #

Next, update your Spring Boot configuration to use the custom dialect you just created. Add this to your application.yml:

spring:
  jpa:
    properties:
      hibernate:
        dialect: com.yourproject.persistence.CustomOracleDialect # Use the full path to your class
        # Optional: Enable SQL formatting to verify the generated query
        format_sql: true
    show-sql: true # Optional: Log generated SQL to check if the function is called correctly

Step 3: Adjust Your JPQL Query (Optional but Recommended) #

Your existing query is almost correct, but you can make it type-safe by specifying the return type as Date instead of Object:

import java.util.List;
import java.util.Date;
import javax.persistence.EntityManager;
import org.springframework.beans.factory.annotation.Autowired;
// ...

@Autowired
private EntityManager entityManager;

public List<Date> getConvertedDates() {
    Query query1 = entityManager.createQuery(
        "select todate(ActSubT.createdDt) from ActSubT ActSubT",
        Date.class
    );
    return query1.getResultList();
}

Why This Works #

By registering the function in the custom dialect, Hibernate knows that when it sees todate() in JPQL, it should translate it directly to your Oracle custom function todate() in the generated SQL. Without this registration, Hibernate assumes todate is a JPQL standard function (which it isn't), leading to the error you're seeing.

内容的提问来源于stack exchange，提问作者Rajesh Khore