Custom Scalar Valued function in Hibernate 5.x

Question

I have a custom Scalar Valued function in SQL with the below details:

• Name : fn_WorkDays
• Returns : Int
• Parameters : 1. StartDate (type - datetime) 2. EndDate (type - datetime)

I'm using Hibernate 5.4.6 to fetch some records from the database. I want to use the above function in the HQL (in the Where Clause), however currently facing issues.

Query<Device> query = session.createQuery("Select ud from Device ud where fn_WorkDays(ua.created, current_date())>2",   Device.class);

After going through some posts, I understand that the custom function needs to be registered before using it, hence have created the below class:

public class MySQLServerDialect extends SQLServerDialect {
    public MySQLServerDialect() {
        super();
        registerFunction("fn_WorkDays", new VarArgsSQLFunction(StandardBasicTypes.INTEGER, "fn_WorkDays(", ",", ")"));
    }
}

Have updated the hibernate.cfg.xml with :

<property name="dialect">com.test.service.utils.MySQLServerDialect</property>

I dont seem to understand how the registerFunction should be done as im getting the below error: ERROR SqlExceptionHelper:142 - 'fn_WorkDays' is not a recognized function name.

Log4j2.xml

<?xml version="1.0" encoding="UTF-8"?>
<Configuration>
    <Appenders>
        <!-- Console Appender -->
        <Console name="Console" target="SYSTEM_OUT">
            <PatternLayout
                pattern="%d{yyyy-MMM-dd HH:mm:ss a} [%t] %-5level %logger{36}:%L - %msg%n" />
        </Console>      
    </Appenders>

    <Loggers>
        <!-- Log everything in hibernate -->
        <Logger name="org.hibernate" level="debug" additivity="false">
            <AppenderRef ref="Console" />
        </Logger>

        <!-- Log SQL statements -->
        <Logger name="org.hibernate.SQL" level="debug" additivity="false">
            <AppenderRef ref="Console" />           
        </Logger>

        <!-- Log JDBC bind parameters -->
        <Logger name="org.hibernate.type.descriptor.sql" level="trace" additivity="false">
            <AppenderRef ref="Console" />           
        </Logger>

        <!-- Log custom packages -->
        <Logger name="com.test.service" level="debug"   additivity="false">
            <AppenderRef ref="Console" />           
        </Logger>

        <Root level="error">
            <AppenderRef ref="Console" />
        </Root>
    </Loggers>
</Configuration>

Any help to resolve the above will be greatly appreciated.

Answer 1

ERROR SqlExceptionHelper:142 - 'fn_WorkDays' is not a recognized function name.

The error already says what is wrong, the function does not exist in the database.

Try this:

registerFunction("fn_WorkDays", new VarArgsSQLFunction(StandardBasicTypes.INTEGER, "dbo.fn_WorkDays(", ",", ")"));