[FIXED] How to select entities by calling a stored procedure with Spring Data

Issue

Let's say I have a MySQL stored procedure that contains the following select:

select * from users;

How to use the @Procedure annotation in a JpaRepository so that I can get the results? Something like:

public UserRepository extenda JpaRepository<User, Long>{
    @Procedure('get_users')
    List<User> getUsers();
}

Solution

Let's say this is your procedure:

CREATE PROCEDURE get_users()
BEGIN
    SELECT * FROM USERS;
END

You define a @NamedStoredProcedureQuery:

@NamedStoredProcedureQuery(
    name="getUsers", 
    procedureName="get_users", 
    resultClass=User.class, parameters={
        @StoredProcedureParameter(queryParameter="data", name="data", direction=Direction.OUT_CURSOR)
    }
)
@Entity
public class User {
    ...
}

And then you can call your procedure like this:

public UserRepository extends JpaRepository<User, Long>{
    @Procedure('User.getUsers')
    List<User> getUsers();
}

You can also check this using your EntityManager like this:

Query query = entitymanager.createNamedQuery("getUsers");
List users = query.getResultList();

Answered By - Vlad Mihalcea
Answer Checked By - Marie Seifert (JavaFixing Admin)