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.
2 Answers
Answers 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.
Answers 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 " simple Spring controller with data returned from @Procedure, how to? " context.... And, as I am using PostgreSQL (where a SELECT f(x) is valid), the @Query is a workaround for @Procedure... But ideal answer must be generic... So...
PROBLEMS WITH THIS ANSWER: use nativeQuery = true mode at @Query, and not using @Procedure.
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.
At domain's
domain.repositorypackage file, add a method with@Queryand with other imports add all Query-context imports (QueryAnnotation, JpaRepository, query.Param, etc. if need);At domain's
servicepackage file, add the new custom "find" method definition.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
Step3: 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+"}"; } } 
0 comments:
Post a Comment