stored procedures - How to implement the simplest alien in a REST webservice?

Question

I need a JSON endpoint that return data directally from a stored procedure. Example:

@Procedure("complex")
String complexStoredProcedure();

The simplest case is the GET endpoint with no parameter... The returned data is a (blabck-box) JSON, with no relationship with a Spring entity, and no special data-type (an "alien datatype" for my Spring application)...

If you need an example, suppose a simple controller as

@RequestMapping(value="/howto", method=RequestMethod.GET)
@ResponseStatus(HttpStatus.OK)
@Procedure(name = "MyRandom.random")  // ?? not work
public String howto() { 
    double x = random();  // from stored-procedure call, how to do it?
    return "{"result":"+x+"}";
}

How to implement it? that is, the simplest way to implement a controller method that do it (a call to a stored-procedure)?

---

EDIT: cleaned the redundant code fragments, transfered to answer-Wiki below.

Answer 1

Im not really sure if I understand your question well but this is my solution.

I created a simple procedure on a oracle DB:

PROCEDURE TESTPROCOUTPUT
   (param2 OUT VARCHAR2)
   IS
   GETPARAM VARCHAR2(100);
BEGIN
    param2 := 'procedure Called';
END TESTPROCOUTPUT;

To use @Procedure in the Spring environment you will need an Entity and a Repository. So I created a simple Entity and it's Repository:

@Entity
public class City {

    @Id
    private String cityCode;

    //...getter/setter
}

Repository:

public interface LandRepository extends CrudRepository<City, String> {

    @Procedure(name="TESTPROCOUTPUT", outputParameterName="param2")
    String TESTPROCOUTPUT();
}

It is important that the method name has the same name like the procedure has. (Not sure if its just for oracle the case. And could also be in camelCase I think)

So in your controller you can now easily autowire the repository (or if you have an implementation of the interface use this).

@Controller
public class CityController {

    private CityRepository cityRepository;

    @RequestMapping(value="/howto", method=RequestMethod.GET)
    @ResponseStatus(HttpStatus.OK)
    @ResponseBody
    public String howto() { 
        String s = cityRepository.TESTPROCOUTPUT();
        return "{"result":" + s + "}";
    }

    public CityRepository getCityRepository () {
        return cityRepository;
    }

    @Autowired
    public void setCityRepository (CityRepository cityRepository) {
        this.cityRepository= cityRepository;
    }
}

And the result is:

So you are not able to use @Procedure annotation on non repository methods.

Annotation to declare JPA 2.1 stored procedure mappings directly on repository methods.