CODEWITHSUNDEEP
javaspringhibernatejpastored-procedures
KRISTIJAN TOMASINI
KRISTIJAN TOMASINI

Reputation: 459

How to execute a stored procedure which does not return all columns in Spring Boot?

If there is a table T with columns A, B and C, and if there is a stored procedure P how can I define it in Spring's repository and call it?

So procedure is supposed to return the list of rows from the table (the so called resultset), but, the problem is that it does not select all columns. I have an entity T defined which includes all columns of the table, and an entity TS which includes only the subset of the columns from the table. R is the repository interface and A is the main application file.

How can I define this procedure in Spring's repository?

I never called a procedure before, so I do not know why my approach does not work.

This is what I tried so far:

P.SQL

ALTER PROCEDURE [dbo].[P]
AS
BEGIN
    SELECT A, B FROM [sph_test].[dbo].[T]
END

T.JAVA

@Entity
public class T
{
    @Id
    int A;
    int B;
    int C;
}

TS.JAVA

@Entity
public class TS
{
    @Id
    int A;
    int B;
}

R.JAVA

public interface R extends JpaRepository<T, Long> {@Procedure List<TS> P();}

A.JAVA

@SpringBootApplication
public class A
{
    public static void main(String[] n) {SpringApplication.run(A.class, null);}
    @Autowired
    private R r;
    @PostConstruct
    public void M() {this.r.P();}
}

Here application starts, repository gets injected, M method gets called and fails when calling the procedure.

Error is:

org.springframework.dao.InvalidDataAccessApiUsageException: Type cannot be null

I would gladly provide the type, but the error message does not specify to what type it is referring to, nor where to set that type.

Perhaps, I need to add some argument to the @Procedure annotation in the repository? Table, columns and procedure in the database are all defined using the same names as seen here in the entity class, columns in the entity class and the procedure method declaration in the repository, so Spring should be able to find all that stuff in the database without the problem.

Problem is not with the database connectivity or with Spring not being able to find the table or something like that since I can execute other autoimplemented methods in the R repository.

I can always write a @Query and make a method that executes this procedure through query, but I prefer to simply use the @Procedure to execute it.

I was not able to find any example of a procedure returning a subset of columns from the table. All examples which I found were returning a primitive value or result sets with all columns included.

Upvotes: 0

Views: 1490

Answers (1)

Julian Eckhardt
Julian Eckhardt

Reputation: 309

Have you already seen this thread? Seems like it could be relevant to you.

Upvotes: 1

Related Questions