I am using Spring Boot JPA with oracle DB.
In my application, I am dealing with DB data only.
I am learning to code jpa with @procedure,
but facing error as below, kindly guide me what is wrong in below code:
Hibernate: {call ZERO(?,?,?,?,?)}
Root cause class: java.lang.IllegalArgumentException
Root cause IllegalArgumentException: Positional parameter [1] is not registered with this procedure call
org.springframework.dao.InvalidDataAccessApiUsageException: Positional parameter [1] is not registered with this procedure call
at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:371)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:246)
//Entity Class : using lambok and variable names are same as column name in Table.
@NamedStoredProcedureQueries({
@NamedStoredProcedureQuery(
name = "zero",
procedureName = "zero",
parameters = {
@StoredProcedureParameter(mode = ParameterMode.OUT, name="ASM", type = Integer.class),
@StoredProcedureParameter(mode = ParameterMode.OUT, name = "BD", type = Integer.class),
@StoredProcedureParameter(mode = ParameterMode.OUT, name = "SPD", type = Date.class),
@StoredProcedureParameter(mode = ParameterMode.OUT, name = "SPN", type = Integer.class),
@StoredProcedureParameter(mode = ParameterMode.OUT, name = "SD", type = Integer.class)
}
),
@NamedStoredProcedureQuery(
name = "ONE",
procedureName = "ONE",
parameters = {
@StoredProcedureParameter(mode = ParameterMode.IN, name="ACC_SPD", type = Integer.class),
@StoredProcedureParameter(mode = ParameterMode.IN, name = "ACC_SPN", type = Integer.class),
@StoredProcedureParameter(mode = ParameterMode.OUT, name="ASM", type = Integer.class),
@StoredProcedureParameter(mode = ParameterMode.OUT, name = "BD", type = Integer.class),
@StoredProcedureParameter(mode = ParameterMode.OUT, name = "SPD", type = Date.class),
@StoredProcedureParameter(mode = ParameterMode.OUT, name = "SPN", type = Integer.class),
@StoredProcedureParameter(mode = ParameterMode.OUT, name = "SD", type = Integer.class)
}
)
})
@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ABEntity {
// i am not fetching primary key frm procedure, so this is dummy variable, is it okay?
// tried without @id annotation, it gives error. so added dummy variable.
@Id
private int id;
public int ASM;
public int BD;
public Date SPD;
public int SPN;
public int SD;
// this CT variable is not related to procedure. it used to for external value setting.
public int CT;
}
// Repository class
@Repository
public interface ABRepo extends JpaRepository<ABEntity, Integer>{
@Procedure(name = "ZERO", procedureName = "ZERO")
public List<ABEntity> getZero();
@Procedure(name = "ONE", procedureName = "ONE")
public List<ABEntity> getOne(@Param("ACC_SPD") Date ACC_SPD, @Param("ACC_SPN") int ACC_SPN );
}
//Service Class
@Service
public class ABService {
@Autowired
ABRepo aBRepo;
@Autowired
ARecord aRecord;
public String method() {
List<ABEntity> ABEntityList = null;
try {
if(abc==0){
System.out.println("inside 0");
ABEntityList = aBRepo.getZero();
}else if(abc==1){
ABEntityList = aBRepo.getOne(aRecord.sDATE,aRecord.sNUMBER);
}
} catch (DataAccessException e) {
// Handle the exception
// Log the exception details or perform any other error handling actions
}
}
}
//procedure code is below
create or replace PROCEDURE ZERO(
ASM OUT NUMBER,
BD OUT NUMBER,
SPD OUT DATE,
SPN OUT NUMBER,
SD OUT NUMBER
)
IS
BEGIN
SELECT S.ASM,
S.BD,
S.SPD,
S.SPN,
S.SD
INTO ASM,
BD,
SPD,
SPN,
SD
FROM ...... S
WHERE
.....
END ZERO;
create or replace PROCEDURE ONE(
ACC_SPD IN DATE,
ACC_SPN IN NUMBER,
ASM OUT NUMBER,
BD OUT NUMBER,
SPD OUT DATE,
SPN OUT NUMBER,
SD OUT NUMBER
)
IS
BEGIN
SELECT S.ASM,
S.BD,
S.SPD,
S.SPN,
S.SD
INTO ASM,
BD,
SPD,
SPN,
SD
FROM ...... S
WHERE
.....
END ZERO;