如何在jpa查询中使用自定义函数？ [英] How to use a custom function in a jpa query?

问题描述

我是Spring Jpa和Hibernate的新手。我正尝试从Oracle数据库中使用自定义函数来获取数据。我可以定义一个实体及其相关的服务，实现和存储库。另外，我将使用 registerFunction 创建一个新的自定义Oracle方言，如下所示。

所以我有两个问题：
$ b $ 1在我的Oracle数据库中，函数位于不同的模式下。我需要指定它的模式吗？如果是这样如何？或者将hibernate自动找到它？

在提供我的完整堆栈跟踪之后，我会在本帖末尾提问第二个问题...

以下是我的完整堆栈跟踪：

MyOracle10gDialect

  package blog; 
 
导入org.hibernate.dialect.Oracle10gDialect; 
导入org.hibernate.dialect.function.StandardSQLFunction; 
 
 
 public class MyOracle10gDialect扩展Oracle10gDialect {
 
 public MyOracle10gDialect（）{
 super（）; 
 registerFunction（my_function，new StandardSQLFunction（my_function））; 
} 
 
} 
  

application.properties

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

实体：

  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）
私有字符串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; 
 
 $ b $ public WosItem（）{} 
 
 @Override 
 public String toString（）{
 returnWosItem {+ 
UT ='+ UT +'\''+ 
，TI =''+ TI +'\''+ 
'}'; 
 
 
  

服务：

  package blog.services; 
 
 import blog.models.WosItem; 
 import org.springframework.stereotype.Service; 
 
 import java.util.List; 
 
 @Service 
 public interface WosItemService {
 
 List< WosItem>找到所有（）; 
 WosItem findById（String id）; 
字符串find_ut（Long ut_seq）; 
} 
  

执行：

  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 {
 $ b $ @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（）; 
} 
} 
  

存储库 p>

  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扩展JpaRepository< WosItem，String> {
 @Query（SELECT function（'my_function'，input）FROM WosItem wos）; 
 String find_ut（）; 
 
 
 
 
 
 $ b 
所以在我的Oracle数据库中，我可以使用如下所示的函数： p> 
 
 
 从my_schema.TABLE中选择other_schema.my_function（aa.input）aa; 
  
例如。说aa.input是 332708100009 然后它返回 000332708100009  
 
 
 
至于我的第二个问题： 
 
 
 2）如何在jpa中执行此过程？我知道我的存储库根本不正确。我收到了一个错误，如注释不允许在这里。我找不到补救办法。
 
 
 
预先感谢。
 EXCEPTION：  
 
 
 引起：java.lang.IllegalStateException：节点没有数据类型：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} 
  函数的JPA 2.1特性调用你的选择语句，然后你需要执行一些额外的操作，然后才能使用它。
 
 
 
当你在你的中使用它语句，那么它在没有任何额外动作的情况下工作，但是因为我想将它用于我的项目在选择内部，就像你做的那样，你需要：
 $ b 
  1）扩展hibernate方言并注册你的函数。 ：
  package com.mypkg.dialect; 
 
导入org.hibernate.dialect.Oracle10gDialect; 
导入org.hibernate.dialect.function.StandardSQLFunction; 
 import org.hibernate.type.StringType; 
 $ b $ public class CustomOracle10gDialect extends Oracle10gDialect {
 
 public CustomOracle10gDialect（）{
 super（）; 
 registerFunction（my_function
，new StandardSQLFunction（my_function，new StringType（）））; 
} 
} 
  
  2）  hibernate.dialect 会话工厂的属性指向该自定义实现： 
 
 
 < property name =hibernate.dialectvalue =com.mypkg.dialect.CustomOracle10gDialect/> 
  
 更新  
 
 
如果函数需要从某个模式调用，那么会提示： 
 
 
  registerFunction（my_function
，new StandardSQLFunction（schema.my_function，new StringType（）））; 
  
进一步阅读 - > 原生函数调用 
 
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}

解决方案
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

                        
这篇关于如何在jpa查询中使用自定义函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！