Spring Boot + JPA + Stored Procedure

cuinto · Abril 10, 2019, 8:22pm

Pessoal,

Estou tentando listar alguns dados da tabela “usuario” utilizando Spring Boot, JPA e Stored Procedure, porém estou tendo alguma problemas.

Segue estrutura do projeto:

:: PROCEDURE

==============================================================================

@ RestController
@ RequestMapping(value="/usuarios")
public class UsuarioResource {

private UsuarioService usuarioService;

@RequestMapping(value="/{id}", method=RequestMethod.GET)
public ResponseEntity<?> findUsuario(@PathVariable Integer id) {
	
	Usuario obj = usuarioService.listarUsuarioPorId(id);
	
	return ResponseEntity.ok().body(obj);
}

}

==============================================================================

@ Service
public class UsuarioService {

private UsuarioRepository usuarioRepository;
private UsuarioRepositoryImpl usuarioRepositoryImpl;

public Usuario listarUsuarioPorId(Integer id) {
	List<Usuario> obj = usuarioRepositoryImpl.listar(id);
	
	Optional<Usuario> empty = Optional.of(obj.get(0));
	
	return empty.orElse(null);
}

}

==============================================================================

@ Repository
public class UsuarioRepositoryImpl {

@PersistenceContext	
private EntityManager entityManager;

@SuppressWarnings("unchecked")
public List<Usuario> listar(Integer id) {
	
	System.out.println("CLASSE USUARIO ====> UsuarioRepositoryImpl");
	
	
	try {
		// Dynamic stored procedure definition.
		StoredProcedureQuery query = entityManager.createStoredProcedureQuery("usp_select_Usuario");
		query.registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN);
		
		// Setting stored procedure parameters.
		query.setParameter(1, id);
	
		
		query.execute();
		
		//Optional<Usuario> users = (Optional<Usuario>) query.getSingleResult();
        
		//List<Usuario> users = query.getResultList();
		//return Optional.of( users.get(0) );
		
		//Optional<Usuario> users = (Optional<Usuario>) query.getSingleResult();
		//return users;
		
		List<Usuario> users = query.getResultList();
		return users;
	}
	catch (NonUniqueResultException e) {
        System.out.println(e.getCause());
        return null;
        
    } 
    catch (NoResultException e) {
        System.out.println(e.getCause());
        return null;
        
    }
}

}

:: DESCRIÇÃO DO ERRO

java.lang.ClassCastException: class [Ljava.lang.Object; cannot be cast to class br.com.exemploprocedurejpa.domain.Usuario ([Ljava.lang.Object; is in module java.base of loader ‘bootstrap’; br.com.exemploprocedurejpa.domain.Usuario is in unnamed module of loader ‘app’)
at br.com.exemploprocedurejpa.services.UsuarioService.buscar(UsuarioService.java:32) ~[classes/:na]
at br.com.exemploprocedurejpa.resources.UsuarioResource.findUsuario(UsuarioResource.java:36) ~[classes/:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:189) ~[spring-web-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138) ~[spring-web-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102) ~[spring-webmvc-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:892) ~[spring-webmvc-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:797) ~[spring-webmvc-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1038) ~[spring-webmvc-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:942) ~[spring-webmvc-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1005) ~[spring-webmvc-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:897) ~[spring-webmvc-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:634) ~[tomcat-embed-core-9.0.17.jar:9.0.17]
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:882) ~[spring-webmvc-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:741) ~[tomcat-embed-core-9.0.17.jar:9.0.17]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[tomcat-embed-core-9.0.17.jar:9.0.17]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.17.jar:9.0.17]
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-embed-websocket-9.0.17.jar:9.0.17]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.17.jar:9.0.17]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.17.jar:9.0.17]
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) ~[spring-web-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.17.jar:9.0.17]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.17.jar:9.0.17]
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:92) ~[spring-web-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.17.jar:9.0.17]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.17.jar:9.0.17]
at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:93) ~[spring-web-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.17.jar:9.0.17]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.17.jar:9.0.17]
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200) ~[spring-web-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.17.jar:9.0.17]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.17.jar:9.0.17]
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:200) ~[tomcat-embed-core-9.0.17.jar:9.0.17]
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) ~[tomcat-embed-core-9.0.17.jar:9.0.17]
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:490) ~[tomcat-embed-core-9.0.17.jar:9.0.17]
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) ~[tomcat-embed-core-9.0.17.jar:9.0.17]
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) ~[tomcat-embed-core-9.0.17.jar:9.0.17]
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) ~[tomcat-embed-core-9.0.17.jar:9.0.17]
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) ~[tomcat-embed-core-9.0.17.jar:9.0.17]
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408) ~[tomcat-embed-core-9.0.17.jar:9.0.17]
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) ~[tomcat-embed-core-9.0.17.jar:9.0.17]
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:834) ~[tomcat-embed-core-9.0.17.jar:9.0.17]
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1415) ~[tomcat-embed-core-9.0.17.jar:9.0.17]
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) ~[tomcat-embed-core-9.0.17.jar:9.0.17]
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[na:na]
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[na:na]
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-embed-core-9.0.17.jar:9.0.17]
at java.base/java.lang.Thread.run(Thread.java:834) ~[na:na]

Alguém poderia me ajudar?

Desde já agradeço.

darlan_machado · Abril 11, 2019, 12:30pm

ClassCastException signfiica que você está tentando transformar Laranja em uma Banana e isso não é possível.
Veja, o método retorna um Object e você diz para o java que ele deve esperar um Usuário. Logo, não vai funcionar.

Lucas_Camara · Abril 12, 2019, 4:07pm

Você deve especificar o tipo do retorno ao criar a query:

StoredProcedureQuery query = 
    this.em.createStoredProcedureQuery("usp_select_Usuario", Usuario.class);

adriano_si · Abril 12, 2019, 6:51pm

E só pra entender, você está fazendo isso apenas para um teste certo? A ideia é validar o retorno de uma procedure para usar quando tiver outros data sets quando precisares né?

Porque se não for isso, você está criando um nível de complexidade aí no meio criando uma proc apenas para retornar um select na tabela de usuários.

cuinto · Abril 15, 2019, 6:20pm

Fala Adriano, Tudo bem?

Correto, somente estou fazendo alguns testes estudando mais a fundo o JPA com Procedure.

Já posto o resultado assim que finalizar a classe.

adriano_si · Abril 16, 2019, 1:34pm

Massa, sendo assim, vai fundo…

Spring Boot + JPA + Stored Procedure

Cursos de Mobile

Cursos de Programação

Cursos de Front-end

Cursos de DevOps

Cursos de Design & UX

Cursos de Business

Cursos de Data & BI