6

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}
Maciej Kowalski
  • 25,605
  • 12
  • 54
  • 63
mlee_jordan
  • 772
  • 4
  • 18
  • 50
  • Annotations don't require to be ended with `;`. Just remove that. – M. Deinum Feb 27 '17 at 11:27
  • Thanks! @M.Deinum Now I have an error as updated above. How can I invoke the input value in my repository? – mlee_jordan Feb 27 '17 at 12:15
  • You don't seem to have an `input` column on `WosItem`. Can you post your exact code without trivialising it? – Strelok Feb 27 '17 at 13:07
  • 1
    Please limit question to one question at a time. If you have multiple questions make them multiple posts. Also, once a problem is solved, accept the correct and upvote all helpful answer. Please do not edit the question to ask a different question. Again, just create a new question instead. – Jens Schauder Sep 04 '18 at 06:17

1 Answers1

5

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

Maciej Kowalski
  • 25,605
  • 12
  • 54
  • 63
  • 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. – mlee_jordan Feb 27 '17 at 12:56
  • have u tried adding explicit return type new StringType()? Sorry i completely missed the beginning of your question.. – Maciej Kowalski Feb 27 '17 at 12:58
  • btw.. your WosItem does not seem to have an 'input' field or am i missing something? – Maciej Kowalski Feb 27 '17 at 13:03
  • 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. – mlee_jordan Feb 27 '17 at 13:11
  • 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. – Maciej Kowalski Feb 27 '17 at 13:14
  • 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_ – mlee_jordan Feb 27 '17 at 13:14
  • I would try: registerFunction("my_function" , new StandardSQLFunction("other_schema.my_function", new StringType())); – Maciej Kowalski Feb 27 '17 at 13:18
  • or you can set a default schema which should be picked up by hibernate: spring.jpa.properties.hibernate.default_schema=schema – Maciej Kowalski Feb 27 '17 at 13:24
  • 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! – mlee_jordan Feb 27 '17 at 13:31
  • 1
    Great! i have updated the answer for other users to know – Maciej Kowalski Feb 27 '17 at 13:33