How to use a custom function in a jpa query?

Question

I am new to Spring Jpa and Hibernate. I am trying to fetch data using a custom function from an Oracle db. I could define an entity along with its related service, implementation and repository. In addition, I created a new custom Oracle dialect by using registerFunction as you will see below.

So I have two questions:

1) In my Oracle db, the function sits under a different schema. Do I need to specify its schema? If so how? Or will hibernate find it automatically?

I will be asking my second question at the end of this post after providing my full stacktrace...

Here is my full stack trace:

MyOracle10gDialect

package blog;

import org.hibernate.dialect.Oracle10gDialect;
import org.hibernate.dialect.function.StandardSQLFunction;


public class MyOracle10gDialect extends Oracle10gDialect {

    public MyOracle10gDialect() {
        super();
        registerFunction("my_function", new StandardSQLFunction("my_function"));
    }

}

application.properties

...
spring.jpa.database-platform=blog.MyOracle10gDialect
...

Entity:

package blog.models;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "item", schema = "WOS_SOURCE")
public class WosItem {

    @Id
    @Column(nullable = false)
    private String UT;

    @Column(nullable = false)
    private String TI;

    public String getUT() {
        return UT;
    }

    public void setUT(String UT) {
        this.UT = UT;
    }

    public String getTI() {
        return TI;
    }

    public void setTI(String TI) {
        this.TI = TI;
    }

    public WosItem(String UT, String TI) {
        this.UT = UT;
        this.TI = TI;
    }

    public WosItem() { }

    @Override
    public String toString() {
        return "WosItem{" +
                "UT='" + UT + '\'' +
                ", TI='" + TI + '\'' +
                '}';
    }
}

Service:

package blog.services;

import blog.models.WosItem;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public interface WosItemService {

    List<WosItem> findAll();
    WosItem findById(String id);
    String find_ut(Long ut_seq);
}

Implementation:

package blog.services;

import blog.models.WosItem;
import blog.repositories.WosItemRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class WosItemServiceJpaImpl implements WosItemService {

    @Autowired
    private WosItemRepository wosItemRepository;

    @Override
    public List<WosItem> findAll() {
        return this.wosItemRepository.findAll();
    }

    @Override
    public WosItem findById(String id) {
        return this.wosItemRepository.findOne(id);
    }

    @Override
    public String find_ut(Long ut_seq) {
        return this.wosItemRepository.find_ut();
    }
}

Repository:

package blog.repositories;

import blog.models.WosItem;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;


@Repository
public interface WosItemRepository extends JpaRepository<WosItem, String> {
    @Query("SELECT function('my_function', input) FROM WosItem wos");
    String find_ut();
}

So in my Oracle db I can use this function as shown below:

select other_schema.my_function(aa.input) from my_schema.TABLE aa;

For ex. say aa.input is 332708100009 then it returns 000332708100009

As for my second question:

2) How can I carry out this process in jpa? I am aware that my repository is not correct at all. I get an error like "Annotations are not allowed here". I could not find a way to remedy this.

Thanks in advance.

EDIT ON THROWN EXCEPTION:

Caused by: java.lang.IllegalStateException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode 
 \-[METHOD_CALL] MethodNode: 'function (my_function)'
    +-[METHOD_NAME] IdentNode: 'my_function' {originalText=my_function}
    \-[EXPR_LIST] SqlNode: 'exprList'
       \-[NAMED_PARAM] ParameterNode: '?' {name=ut_seq, expectedType=null}

Solution

Unfortunately if you want to use the JPA 2.1 feature of the custom function call in your Select statement then you will need to perform some additional actions before you can use it.

When you use it in your where statement then it works without any additional actions, but as i wanted to use it for one of my projects inside the select just as you did then you would need to:

1) Extend the hibernate dialect and register your function(s):

package com.mypkg.dialect;

import org.hibernate.dialect.Oracle10gDialect;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.type.StringType;

public class CustomOracle10gDialect extends Oracle10gDialect {

    public CustomOracle10gDialect() {
        super();
        registerFunction("my_function"
           , new StandardSQLFunction("my_function", new StringType()));
    }
}

2) Edit your hibernate.dialect property of your session factory to point to that custom implementation:

<property name="hibernate.dialect" value="com.mypkg.dialect.CustomOracle10gDialect"/>

Update

If the function needs to be called from a certain schema then this would be suggested:

registerFunction("my_function"
           , new StandardSQLFunction("schema.my_function", new StringType()));

Further reading -> native function calls

Comments

[mlee_jordan]

Thanks for the answer but I have already done this. Please see my application properties code above. Now it seems that I cannot call my input value to be used in my repository.

[Maciej Kowalski]

have u tried adding explicit return type new StringType()? Sorry i completely missed the beginning of your question..

[Maciej Kowalski]

btw.. your WosItem does not seem to have an 'input' field or am i missing something?

[mlee_jordan]

Ah yes I was careless not adding StringType(). When adding that, code worked. As for your other warning, yes I was not sure to add it since it is not a column in my table. So I am a bit confused.

[Maciej Kowalski]

I see.. so basically your method should have a param find_ut(String input) and in your query you use :input in the function. Anyway.. glad it is now working for you.

[mlee_jordan]

In addition, how can I explicitly state the schema including the function? Because, now I get the following error: java.sql.SQLSyntaxErrorException: ORA-00904: "my_function": invalid identifier...And here is the query Hibernate: select my_function(?) as col_0_0_ from TABLE.item wositem0_

[Maciej Kowalski]

I would try: registerFunction("my_function" , new StandardSQLFunction("other_schema.my_function", new StringType()));

[Maciej Kowalski]

or you can set a default schema which should be picked up by hibernate: spring.jpa.properties.hibernate.default_schema=schema

[mlee_jordan]

Many thanks @Maciej Kowalski ! your first suggestion (other_schema.my_function) worked very well. In addition, I added String find_ut(Long ut_seq); to my service and now it works!

[Maciej Kowalski]

Great! i have updated the answer for other users to know