Open HVollmilch opened 3 years ago
We recently rolled out some updates to our support for stored procedures. Can you see if this problem still exists with the latest snapshots?
Hi! No, problem still exists in Version 2.6.0-M1 and 2.6.0-SNAPSHOT. Only error message changed to "OUT/INOUT parameter not available: 1".
Hi! No, problem still exists in Version 2.6.0-M1 and 2.6.0-SNAPSHOT. Only error message changed to "OUT/INOUT parameter not available: 1".
For me it works if i make one parameter as ParameterMode.INOUT and then when calling Stored Procedure pass that value as null. @StoredProcedureParameter(mode = ParameterMode.INOUT, name = "example1", type = Integer.class), e.g, Map<String, Integer> example(null) Getting same issue. Any update on same.
OpenFindingDTO.java
@NamedStoredProcedureQuery(name = "OpenFindingDTO.NamedQuery_GetOpenFindingsProcedure",
procedureName = "PRC_OPEN_FINDINGS",
resultClasses = {OpenFindingDTO.class},
parameters = {
@StoredProcedureParameter(name = "C_OUTPUT", mode = ParameterMode.REF_CURSOR, type = ResultSet.class)
})
@Entity
@AllArgsConstructor
public class OpenFindingDTO implements Serializable{
private static final long serialVersionUID = 1L;
public static final String NamedQuery_GetOpenFindingsProcedure = "fetchOpenFindings";
@Id
@Column(name="findingID")
private Integer findingID;
@Column(name="reviewID")
private Integer reviewID;
@Column(name="reviewIDStr")
private String reviewIDStr;
public Integer getFindingID() {
return findingID;
}
public void setFindingID(Integer findingID) {
this.findingID = findingID;
}
public Integer getReviewID() {
return reviewID;
}
public void setReviewID(Integer reviewID) {
this.reviewID = reviewID;
}
public String getReviewIDStr() {
return reviewIDStr;
}
public void setReviewIDStr(String reviewIDStr) {
this.reviewIDStr = reviewIDStr;
}
}
OpenFindingRepository.java
@Repository
public interface OpenFindingRepository extends CrudRepository<OpenFindingDTO, Integer> {
@Procedure(name="OpenFindingDTO.NamedQuery_GetOpenFindingsProcedure")
List<OpenFindingDTO> getOpenFindings();
}
ReportServiceImpl.java
@Service
@Slf4j
public class ReportServiceImpl implements ReportService {
@Autowired
ToolUtility toolUtil;
@Autowired
OpenFindingRepository openFindingRepository;
public Object createFile() {
log.info("Entered into ReportServiceImpl .createFile()");
Object obj=null;
try {
List<OpenFindingDTO> listOpenFindingDTO = openFindingRepository.getOpenFindings();
} catch (SecurityException | IOException e) {
e.printStackTrace();
}
return obj;
}
@Override
public String generateReport() {
createFile();
return "done";
}
}
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.5.6</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.xxx.rt.report</groupId>
<artifactId>rt-report-service</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>rt-report-service</name>
<description>Data check report</description>
<properties>
<java.version>11</java.version>
<spring-cloud.version>2020.0.4</spring-cloud.version>
<jacoco.version>0.8.6</jacoco.version>
<sonar.java.coveragePlugin>jacoco</sonar.java.coveragePlugin>
<sonar.dynamicAnalysis>reuseReports</sonar.dynamicAnalysis>
<sonar.jacoco.reportPath>${project.basedir}/../target/jacoco.exec</sonar.jacoco.reportPath>
<sonar.language>java</sonar.language>
</properties>
<dependencies>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-bootstrap</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-config</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-actuator</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-zipkin</artifactId>
<version>2.2.8.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-sleuth</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-netflix-eureka-client</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-feign</artifactId>
<version>1.4.7.RELEASE</version>
</dependency>
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc8</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-stream</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-stream-binder-rabbit</artifactId>
</dependency>
<dependency>
<groupId>javax.activation</groupId>
<artifactId>activation</artifactId>
<version>1.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-collections4</artifactId>
<version>4.3</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-compress</artifactId>
<version>1.18</version>
</dependency>
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.2</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-math3</artifactId>
<version>3.6.1</version>
</dependency>
<dependency>
<groupId>com.github.virtuald</groupId>
<artifactId>curvesapi</artifactId>
<version>1.06</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>4.1.0</version>
</dependency>
</dependencies>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-dependencies</artifactId>
<version>${spring-cloud.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
</project>
PRC_OPEN_FINDINGS.sql
CREATE PROCEDURE PRC_OPEN_FINDINGS ( C_OUTPUT OUT SYS_REFCURSOR)
AS
BEGIN
OPEN C_OUTPUT FOR
select A.FINDING_ID_PK as findingID,
B.REVIEW_ID_PK as reviewID,
B.REVIEW_ID_STR as reviewIDStr
FROM hellotable;
END;
As it is cursor, I should take mode = ParameterMode.REF_CURSOR
Hi there!
I'm not quite sure, but i think i experience an issue using NamedStoredProcedureQuery, calling stored procedures that only have output but no input parameters.
I follwed the example of [DATAJPA-707], defining a NamedStoredProcedureQuery on my Entity
Referencing it in my Repository
When i call this method at runtime, I get an exception:
Climbing up the stack, it seems that useNamedParameters of StoredProcedureJpaQuery is set to false. This is calclulated on startup and is false, because my Repository method has no (named input) parameters. If i set useNamedParameters to true using the debugger, my procedure call works fine. So i guess there should be another option or logic behind this, but may i miss something or this is not an issue in current versions anymore .
Im using spring-data-jpa 2.3.1
Thanks! Denis