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.

Answer 2

I am trying to respond... It is only "the simplest" for my view point and my basic tests, you can show another solution to get the bounty.

Starting as " https://stackoverflow.com/q/41880120/287948 " context.... And, as I am using PostgreSQL (where a SELECT f(x) is valid), the @Query (with nativeQuery = true ) is a workaround for @Procedure...

PROBLEMS WITH THIS ANSWER: not used @Procedure... After Patrick's answer I see (and edited this line) that you can replace @Query to @Procedure (and other things as specified by Patrick) that the method is the same!

---

3 steps for add "alien @Query" in an existent domain

Any Spring domain can use any @Query, so the domain choice is only a kind of "house organization" and semantic, no constraint over your native SQL code and domain/repository choice.

1. At domain's domain.repository package file, add a method with @Query and with other imports add all Query-context imports (QueryAnnotation, JpaRepository, query.Param, etc. if need);

2. At domain's service package file, add the new custom "find" method definition.

3. At controll's method, call the method defined in the service.

---

Illustrating with real files

Step1: add the new @Query into a existing repository file, eg. myprj/address/domain/repository/ICityRepository.java

package com.myprj.address.domain.repository;  // old

import com.myprj.address.domain.entity.City;   //old
// ... other project's specific (old)
import org.springframework.data.jpa.repository.JpaRepository; 
import org.springframework.data.jpa.repository.Query; // new
import org.springframework.data.repository.query.Param; // new
import org.springframework.data.jpa.repository.query.Procedure; // new

@Repository
public interface ICityRepository extends BaseRepository<City, Long> { //old
    Page<City> findByState(State state, Pageable pageable); //old

    // here an alien example! (simplest is a call to a constant value)
    @Query(nativeQuery = true, value= "SELECT 1234.5678") // NEW! 
    Double findCustom();

}

The alien is there!

Step2: import repository and add defined findCustom() into a existing service file, eg. myprj/address/service/CityService.java

package com.myprj.address.service; // old

import com.myprj.address.domain.entity.City;
// ... other project's specific (old)

@Service   
public class CityService 
       extends BaseService<City, ICityRepository, Long> {   // old

    @Autowired
    public CityService(ICityRepository repository) {super(repository);} // old

    public Page<City> findByState(State state, Pageable pageable) {
        return repository.findByState(state, pageable);
    } // old

    public Double findCustom() { return repository.findCustom(); }  // NEW!!

}

Step3: add defined cityService.findCustom() into a existing controller file, eg. myprj/address/controller/CityController.java ... It is a dummy endpoint to test and show the query result,

package com.myprj.address.controller;  // old

import com.myprj.address.service.CityService;  // reuse old
// ... other project's specific (old)

@RestController   // old
@RequestMapping(value = "/zip", produces = "application/json")  // old
public class ZipController {   // old

    @Autowired  // old
    private CityService cityService;  // old, so reuse it

    // .. many many endpoints ... OLD    

    // NEW!!
    @RequestMapping(value="/dummy", method=RequestMethod.GET)
    @ResponseStatus(HttpStatus.OK)
    public String dummy() {
        double x = cityService.findCustom();
        return "{\"success\":"+x+"}";
    }
}

Reducing to 2 steps and 2 files

As showed by Patrick you can add @Autowired to the repository to set it, and use repository.findCustom() directly in the Controller.java file.