title: How to use a custom function in a jpa query?
body: <p>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 <code>registerFunction</code> as you will see below. </p>
  
  <p>So I have two questions:</p>
  
  <p>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?</p>
  
  <p>I will be asking my second question at the end of this post after providing my full stacktrace...</p>
  
  <p>Here is my full stack trace:</p>
  
  <p><strong>MyOracle10gDialect</strong></p>
  
  <pre><code>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"));
      }
  
  }
  </code></pre>
  
  <p><strong>application.properties</strong></p>
  
  <pre><code>...
  spring.jpa.database-platform=blog.MyOracle10gDialect
  ...
  </code></pre>
  
  <p><strong>Entity:</strong></p>
  
  <pre><code>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 + '\'' +
                  '}';
      }
  }
  </code></pre>
  
  <p><strong>Service:</strong></p>
  
  <pre><code>package blog.services;
  
  import blog.models.WosItem;
  import org.springframework.stereotype.Service;
  
  import java.util.List;
  
  @Service
  public interface WosItemService {
  
      List&lt;WosItem&gt; findAll();
      WosItem findById(String id);
      String find_ut(Long ut_seq);
  }
  </code></pre>
  
  <p><strong>Implementation:</strong></p>
  
  <pre><code>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&lt;WosItem&gt; 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();
      }
  }
  </code></pre>
  
  <p><strong>Repository:</strong></p>
  
  <pre><code>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&lt;WosItem, String&gt; {
      @Query("SELECT function('my_function', input) FROM WosItem wos");
      String find_ut();
  }
  </code></pre>
  
  <p>So in my Oracle db I can use this function as shown below:</p>
  
  <pre><code>select other_schema.my_function(aa.input) from my_schema.TABLE aa;
  </code></pre>
  
  <p>For ex. say aa.input is <code>332708100009</code> then it returns <code>000332708100009</code></p>
  
  <p>As for my second question:</p>
  
  <p>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.</p>
  
  <p>Thanks in advance.</p>
  
  <p><strong>EDIT ON THROWN EXCEPTION:</strong></p>
  
  <pre><code>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}
  </code></pre>