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}
Call a custom database function JPA 2.1 introduced function() to call database functions which are not directly supported by the standard. As you can see in the following code snippet, the syntax is pretty easy. You provide the name of the function as the first parameter and then all parameters of the custom function.
Some time case arises, where we need a custom query to fulfil one test case. We can use @Query annotation to specify a query within a repository. Following is an example. In this example, we are using JPQL, Java Persistence Query Language. We've added name query custom methods in Repository in JPA Named Query chapter.
In order to define SQL to execute for a Spring Data repository method, we can annotate the method with the @Query annotation — its value attribute contains the JPQL or SQL to execute. The @Query annotation takes precedence over named queries, which are annotated with @NamedQuery or defined in an orm. xml file.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With