spring数据spa和oracle包 [英] spring data spa and oracle package

问题描述

我正在尝试构建一个 spring boot rest 端点，该端点调用具有 2 个 in 参数和 45 个限制参数的 oracle 存储过程.我创建了一个实体:

I'm trying to build a spring boot rest endpoint that calls an oracle stored procedure that has 2 in parameters and 45 restrain parameters. I have created an entity:

package com.me.data.userservice.models;

import javax.persistence.*;
import java.util.Date;

/**
 * Created by me on 5/27/17.
 */
@Entity
@NamedStoredProcedureQueries({
        @NamedStoredProcedureQuery(name = "getUserDetailsSp",
                procedureName = "user_pkg.get_user_details",
                parameters = {
                        @StoredProcedureParameter(mode = ParameterMode.IN, name = "p_in_user_id", type = Long.class),
                        @StoredProcedureParameter(mode = ParameterMode.IN, name = "p_in_region", type = String.class)
                },
                resultClasses = UserDetails.class)
})
public class UserDetails {
    //region Private members
    @Id
    private Long user_id;
    private String first_name;
    private String last_name;
    private String email;
    @Column(name = "p_in_user_id", nullable = false)
    private String userId;
    private String user_id;
    @Column(name = "p_in_region", nullable = false)
    private String region;
    private Long scnd_user_id;
    ...
}

还有一个仓库:

package com.me.data.userservice.repositories;

import com.me.data.userservice.models.UserDetails;
import org.springframework.data.jpa.repository.query.Procedure;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;

/**
 * Created by me on 5/27/17.
 */
public interface UserDetailsRepository extends CrudRepository<UserDetails, Long> {
    @Procedure(name="getUserDetailsSp")
    public UserDetails findByUserIdAndRegion(@Param("p_in_user_id") String userId, @Param("p_in_region") String region);
}

所有这些代码都编译得很好，但是当我调用存储库时，我收到以下错误:

All of this code compiles fine but when I call the repository I get the following error:

2017-05-27 18:41:27 错误 o.a.c.c.C.[.[.[.[dispatcherServlet] -Servlet.service() 用于 servlet [dispatcherServlet] 上下文中的路径[] 抛出异常 [请求处理失败；嵌套异常是org.springframework.dao.InvalidDataAccessApiUsageException:对象类 [org.springframework.data.jpa.repository.query.PartTreeJpaQuery]必须是类的实例org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery;嵌套异常是 java.lang.IllegalArgumentException: Object of类 [org.springframework.data.jpa.repository.query.PartTreeJpaQuery]必须是类的实例org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery]根本原因 java.lang.IllegalArgumentException: Object of class[org.springframework.data.jpa.repository.query.PartTreeJpaQuery] 必须成为类的一个实例org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery在org.springframework.util.Assert.instanceCheckFailed(Assert.java:389)在 org.springframework.util.Assert.isInstanceOf(Assert.java:327) 在org.springframework.util.Assert.isInstanceOf(Assert.java:339) 在org.springframework.data.jpa.repository.query.JpaQueryExecution$ProcedureExecution.doExecute(JpaQueryExecution.java:300)在org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:82)在...

2017-05-27 18:41:27 ERROR o.a.c.c.C.[.[.[.[dispatcherServlet] - Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: Object of class [org.springframework.data.jpa.repository.query.PartTreeJpaQuery] must be an instance of class org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery; nested exception is java.lang.IllegalArgumentException: Object of class [org.springframework.data.jpa.repository.query.PartTreeJpaQuery] must be an instance of class org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery] with root cause java.lang.IllegalArgumentException: Object of class [org.springframework.data.jpa.repository.query.PartTreeJpaQuery] must be an instance of class org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery at org.springframework.util.Assert.instanceCheckFailed(Assert.java:389) at org.springframework.util.Assert.isInstanceOf(Assert.java:327) at org.springframework.util.Assert.isInstanceOf(Assert.java:339) at org.springframework.data.jpa.repository.query.JpaQueryExecution$ProcedureExecution.doExecute(JpaQueryExecution.java:300) at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:82) at...

出于某种原因，我无法解码此错误，并且在 google 上找不到任何帮助.

For some reason I am failing at decoding this error and can't find any help on google.

这一定是可能的，但我没有找到任何具有多个返回值或仅输入参数的示例.

This must be possible but I am not finding any examples that have more than one return value nor input-only parameters.

添加细节

我实际上刚刚意识到我 user_pkg.get_user_details 实际上是一个查询函数.这会如何改变我的应用?

I actually just realized that I user_pkg.get_user_details is actually a query function. How does this change my app?

推荐答案

好的，我找到了解决方案.我不确定它是最佳解决方案，但它是一个解决方案.

Ok, so I found a solution. I'm not sure that it is the best solution but it is a solution.

首先，我将存储库更改为标准组件，并选择使用 NamedParameterJdbcTemplate 和自定义行映射器.

First I changed my repository to be a standard component and chose to use a NamedParameterJdbcTemplate and a custom row mapper.

@Component
public class UserDetailsRepository {

    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;

    public UserDetails findByUserIdAndDomain(@Param("p_in_user_id") String userId, @Param("p_in_region") String region) {

        String sql = "select * from TABLE(user_pkg.get_user_details(:p_in_user_id,:p_in_region))";
        MapSqlParameterSource namedParameters = new MapSqlParameterSource("p_in_user_id", userId)
                .addValue("p_in_region", region);
        UserDetails result = (UserDetails)jdbcTemplate.queryForObject(
                sql, namedParameters, new UserDetailsMapper());

        return result;

    }
}

行映射器:

import com.me.data.userservice.models.UserDetails;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Created by me on 5/27/17.
 */
public class UserDetailsMapper implements RowMapper {
    @Override
    public Object mapRow(ResultSet resultSet, int i) throws SQLException {
        UserDetails userDetails = new UserDetails();
               userDetails.setAccount_number(resultSet.getString("account_number"));
        userDetails.setCompany_name(resultSet.getString("company_name"));
        ...
        return userDetails;
    }
}

这篇关于spring数据spa和oracle包的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！