[RESOLVIDO] createNativeQuery do JPA não funciona

[adautodasilvalima]

Boa noite!
Alguém sabe me dizer se eu posso executar esta query nativa?
No MySQL eu testei e roda, mas na aplicação dá erro!!!
Eu não posso usar qualquer sintaxe que funciona no MySQL?

Meu código:

@SuppressWarnings("unchecked")
public List<Imovel> todosCompleta() {
	StringBuilder sb =  new StringBuilder();
	sb.append("select id, case when areaServico = 'S' then 'Sim' when areaServico = 'N' then 'Não' end as area, " +
 "case when sobrado = 'S' then 'Sim' when sobrado = 'N' then 'Não' end as sobr, data_alteracao, " + 
 "data_cadastro, garagem, inativo, mictorio, observacoes, quarto, suite, endereco_id, proprietario_id, " + 
 "usuarioAlteracao_id, usuarioCadastro_id FROM Imovel");
						
	Query query = manager.createNativeQuery(sb.toString(), Imovel.class);

    return query.getResultList();
}	

O Erro:

   out 19, 2017 11:19:18 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 0, SQLState: S0022
out 19, 2017 11:19:18 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: Column 'areaServico' not found.
out 19, 2017 11:19:18 PM com.sun.faces.lifecycle.InvokeApplicationPhase execute
ADVERTÊNCIA: #{consultaImoveisBean.consultaGeral}: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
javax.faces.FacesException: #{consultaImoveisBean.consultaGeral}: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
	at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:118)
	at javax.faces.component.UIViewAction.broadcast(UIViewAction.java:562)
	at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:790)
	at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1282)
	at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:81)
	at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
	at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:198)
	at javax.faces.webapp.FacesServlet.service(FacesServlet.java:658)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at br.com.administracao.filter.JPAFilter.doFilter(JPAFilter.java:48)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at br.com.administracao.filter.AutenticacaoFilter.doFilter(AutenticacaoFilter.java:39)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:475)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:625)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:498)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:796)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1372)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Unknown Source)
Caused by: javax.faces.el.EvaluationException: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
	at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:101)
	at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102)
	... 35 more
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
	at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1763)
	at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677)
	at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:458)
	at br.com.administracao.repository.Imoveis.todosCompleta(Imoveis.java:82)
	at br.com.administracao.controller.ConsultaImoveisBean.consultaGeral(ConsultaImoveisBean.java:44)
	at br.com.administracao.controller.ConsultaImoveisBean$Proxy$_$$_WeldSubclass.consultaGeral(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.apache.el.parser.AstValue.invoke(AstValue.java:247)
	at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:267)
	at org.jboss.weld.util.el.ForwardingMethodExpression.invoke(ForwardingMethodExpression.java:40)
	at org.jboss.weld.el.WeldMethodExpression.invoke(WeldMethodExpression.java:50)
	at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:105)
	at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:87)
	... 36 more
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
	at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123)
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
	at org.hibernate.loader.Loader.doList(Loader.java:2557)
	at org.hibernate.loader.Loader.doList(Loader.java:2540)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370)
	at org.hibernate.loader.Loader.list(Loader.java:2365)
	at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:353)
	at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1873)
	at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:311)
	at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:141)
	at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:573)
	at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:449)
	... 49 more
Caused by: java.sql.SQLException: Column 'areaServico' not found.
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
	at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1077)
	at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5190)
	at org.hibernate.type.descriptor.sql.VarcharTypeDescriptor$2.doExtract(VarcharTypeDescriptor.java:74)
	at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:64)
	at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:267)
	at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:263)
	at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:253)
	at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:338)
	at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2969)
	at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1696)
	at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1628)
	at org.hibernate.loader.Loader.getRow(Loader.java:1515)
	at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:726)
	at org.hibernate.loader.Loader.processResultSet(Loader.java:953)
	at org.hibernate.loader.Loader.doQuery(Loader.java:921)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355)
	at org.hibernate.loader.Loader.doList(Loader.java:2554)
	... 58 more

out 19, 2017 11:19:18 PM com.sun.faces.context.ExceptionHandlerImpl log
FATAL: JSF1073: javax.faces.FacesException obtido durante o processamento de INVOKE_APPLICATION 5: UIComponent-ClientId=, Message=#{consultaImoveisBean.consultaGeral}: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
out 19, 2017 11:19:18 PM com.sun.faces.context.ExceptionHandlerImpl log
FATAL: #{consultaImoveisBean.consultaGeral}: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
javax.faces.FacesException: #{consultaImoveisBean.consultaGeral}: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
	at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:89)
	at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
	at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:198)
	at javax.faces.webapp.FacesServlet.service(FacesServlet.java:658)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at br.com.administracao.filter.JPAFilter.doFilter(JPAFilter.java:48)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at br.com.administracao.filter.AutenticacaoFilter.doFilter(AutenticacaoFilter.java:39)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:475)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:625)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:498)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:796)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1372)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Unknown Source)
Caused by: javax.faces.FacesException: #{consultaImoveisBean.consultaGeral}: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
	at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:118)
	at javax.faces.component.UIViewAction.broadcast(UIViewAction.java:562)
	at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:790)
	at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1282)
	at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:81)
	... 31 more
Caused by: javax.faces.el.EvaluationException: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
	at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:101)
	at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102)
	... 35 more
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
	at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1763)
	at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677)
	at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:458)
	at br.com.administracao.repository.Imoveis.todosCompleta(Imoveis.java:82)
	at br.com.administracao.controller.ConsultaImoveisBean.consultaGeral(ConsultaImoveisBean.java:44)
	at br.com.administracao.controller.ConsultaImoveisBean$Proxy$_$$_WeldSubclass.consultaGeral(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.apache.el.parser.AstValue.invoke(AstValue.java:247)
	at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:267)
	at org.jboss.weld.util.el.ForwardingMethodExpression.invoke(ForwardingMethodExpression.java:40)
	at org.jboss.weld.el.WeldMethodExpression.invoke(WeldMethodExpression.java:50)
	at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:105)
	at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:87)
	... 36 more
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
	at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123)
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
	at org.hibernate.loader.Loader.doList(Loader.java:2557)
	at org.hibernate.loader.Loader.doList(Loader.java:2540)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370)
	at org.hibernate.loader.Loader.list(Loader.java:2365)
	at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:353)
	at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1873)
	at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:311)
	at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:141)
	at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:573)
	at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:449)
	... 49 more
Caused by: java.sql.SQLException: Column 'areaServico' not found.
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
	at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1077)
	at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5190)
	at org.hibernate.type.descriptor.sql.VarcharTypeDescriptor$2.doExtract(VarcharTypeDescriptor.java:74)
	at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:64)
	at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:267)
	at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:263)
	at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:253)
	at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:338)
	at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2969)
	at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1696)
	at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1628)
	at org.hibernate.loader.Loader.getRow(Loader.java:1515)
	at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:726)
	at org.hibernate.loader.Loader.processResultSet(Loader.java:953)
	at org.hibernate.loader.Loader.doQuery(Loader.java:921)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355)
	at org.hibernate.loader.Loader.doList(Loader.java:2554)
	... 58 more

out 19, 2017 11:19:18 PM org.apache.catalina.core.StandardWrapperValve invoke
GRAVE: Servlet.service() for servlet [Faces Servlet] in context with path [/Imoveis] threw exception
java.lang.IllegalStateException: Transaction not active
	at org.hibernate.jpa.internal.TransactionImpl.rollback(TransactionImpl.java:105)
	at br.com.administracao.filter.JPAFilter.doFilter(JPAFilter.java:58)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at br.com.administracao.filter.AutenticacaoFilter.doFilter(AutenticacaoFilter.java:39)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:475)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:625)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:498)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:796)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1372)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Unknown Source)

Desde já agradeço, e se for preciso postar mais código, por favor é só falar!!!

[staroski]

Existe a coluna “areaServico”?

Quando sua query estiver OK, terás um ClassCastException ao chamar o getResultList().
Como se trata de query nativa, o retorno será um List de Object[] e não de Imovel.
Sugiro processar os elementos da lista e para cada Object[] e criar um objeto Imovel.

[adautodasilvalima]

Bom dia!

O campo areaServico existe sim, eu consigo rodar a query no meu banco que é o MySql.
Sem querer abusar, você poderia me dar um exemplo de como faço isto:
“processar os elementos da lista e para cada Object[] e criar um objeto Imovel”

Obrigado!

[edumatias]

Olá @adautodasilvalima

Com createNativeQuery faça da seguinte forma:

Query query = manager.createNativeQuery(sb.toString());

– Obtenha a lista –
List<Object[]> list = nativeQuery.getResultList();

– valide se não está nula e percorra ela –
if(list != null){
for(Object[] objectArray : list ){

e ai você pode fazer um cast para seu objeto, ex:

Imovel imovel = (Imovel) objectArray[0];

e então adicionar cada iteração em uma lista de imóveis

listaImovel.add(imovel);

Quanto ao seu erro, a coluna areaServico citada pelo staroski é que está ocasionando o problema.

Poste a classe Imovel para que possamos verificar.

[adautodasilvalima]

Segue a entidade:

package br.com.administracao.model;

//import java.beans.Transient;
import javax.persistence.Transient;
import java.io.Serializable;
import java.util.Calendar;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

@NamedQueries({
@NamedQuery(name = “Imovel.enderecoSendoUsado”,
query = " SELECT count(i) FROM Imovel i WHERE i.endereco.id = :id ")
})

@Table(name = “imovel”)
@Entity
public class Imovel implements Serializable{

private static final long serialVersionUID = 1L;

private Integer id;
private Endereco endereco;
private Proprietario proprietario;
private byte quarto;
private byte suite;
private byte mictorio;
private byte garagem;
private char areaServico;
private char sobrado;
private String observacoes;
private Usuario usuarioCadastro;
private Calendar dataCadastro;
private Usuario usuarioAlteracao;
private Calendar dataAlteracao;
private Boolean inativo = false;

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
public Integer getId() {
return id;
}

public void setId(Integer id) {
this.id = id;
}

//Isso significa que ao realizarmos um “SELECT * FROM Imoveis” teremos todos os campos retornados,
//mas os campos com a propriedade FetchType.LAZY (Endereço) estarão nulos, mesmo que eles existam no banco.
@ManyToOne(fetch=FetchType.EAGER)
@JoinColumn(name = “endereco_id”, nullable = false)
public Endereco getEndereco() {
return endereco;
}

public void setEndereco(Endereco endereco) {
this.endereco = endereco;
}

@ManyToOne(fetch=FetchType.EAGER)
@JoinColumn(name = “proprietario_id”, nullable = false)
public Proprietario getProprietario() {
return proprietario;
}

public void setProprietario(Proprietario proprietario) {
this.proprietario = proprietario;
}

@Column(nullable = false)
public byte getQuarto() {
return quarto;
}

public void setQuarto(byte quarto) {
this.quarto = quarto;
}

@Column(nullable = false)
public byte getSuite() {
return suite;
}

public void setSuite(byte suite) {
this.suite = suite;
}

@Column(nullable = false)
public byte getMictorio() {
return mictorio;
}

public void setMictorio(byte mictorio) {
this.mictorio = mictorio;
}

@Column(nullable = false)
public byte getGaragem() {
return garagem;
}

public void setGaragem(byte garagem) {
this.garagem = garagem;
}

@Column(length = 1, nullable = false)
public char getAreaServico() {
return areaServico;
}

public void setAreaServico(char areaServico) {
this.areaServico = areaServico;
}

@Column(length = 1, nullable = false)
public char getSobrado() {
return sobrado;
}

public void setSobrado(char sobrado) {
this.sobrado = sobrado;
}

@Transient
public String getAreaServicoDescricao() {
String areaServicoDescricao = null;
switch (areaServico) {
case ‘S’: areaServicoDescricao = “Sim”;
break;
case ‘N’: areaServicoDescricao = “Não”;
break;
}
return areaServicoDescricao;
}

@Transient
public String getSobradoDescricao() {
String sobradoDescricao = null;
switch (sobrado) {
case ‘S’: sobradoDescricao = “Sim”;
break;
case ‘N’: sobradoDescricao = “Não”;
break;
}
return sobradoDescricao;
}

public String getObservacoes() {
return observacoes;
}

public void setObservacoes(String observacoes) {
this.observacoes = observacoes;
}

@Temporal(TemporalType.TIMESTAMP)
@Column(name = “data_cadastro”, nullable = false)
public Calendar getDataCadastro() {
return dataCadastro;
}

public void setDataCadastro(Calendar dataCadastro) {
this.dataCadastro = dataCadastro;
}

public Boolean isInativo() {
return inativo;
}

public void setInativo(Boolean inativo) {
this.inativo = inativo;
}

@Temporal(TemporalType.TIMESTAMP)
@Column(name = “data_alteracao”)
public Calendar getDataAlteracao() {
return dataAlteracao;
}

public void setDataAlteracao(Calendar dataAlteracao) {
this.dataAlteracao = dataAlteracao;
}

@ManyToOne
@JoinColumn(name = “usuarioCadastro_id”)
public Usuario getUsuarioCadastro() {
return usuarioCadastro;
}

public void setUsuarioCadastro(Usuario usuarioCadastro) {
this.usuarioCadastro = usuarioCadastro;
}

@ManyToOne
@JoinColumn(name = “usuarioAlteracao_id”)
public Usuario getUsuarioAlteracao() {
return usuarioAlteracao;
}

public void setUsuarioAlteracao(Usuario usuarioAlteracao) {
this.usuarioAlteracao = usuarioAlteracao;
}

@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((id == null) ? 0 : id.hashCode());
return result;
}

@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
Imovel other = (Imovel) obj;
if (id == null) {
if (other.id != null)
return false;
} else if (!id.equals(other.id))
return false;
return true;
}

}

[edumatias]

Utilizando a ferramente de testes de sqlfiddle.com cheguei a conclusão que possui erro de sintaxe no uso do CASE.

Tente desta forma

select id, CASE areaServico WHEN 'S' THEN 'Sim' WHEN 'N' THEN 'Não' end as area,
CASE sobrado WHEN 'S' THEN 'Sim' WHEN 'N' THEN 'Não' end as sobr, data_alteracao, 
 data_cadastro, garagem, inativo, mictorio, observacoes, quarto, suite, endereco_id, proprietario_id,
 usuarioAlteracao_id, usuarioCadastro_id FROM Imovel

[adautodasilvalima]

Estou transferindo minha aplicação de casa para o trampo.
Já dou a resposta se deu tudo certo.
Obrigado pela atenção por enquanto!

[adautodasilvalima]

Agora danou-se, com a alteração no CASE que você propôs Edu, a select não roda no MySql

[edumatias]

Faça o teste no Java e veja o que acontece.

Use

select id, CASE areaServico WHEN 'S' THEN 'Sim' WHEN 'N' THEN 'Não' end as area,
CASE sobrado WHEN 'S' THEN 'Sim' WHEN 'N' THEN 'Não' end as sobr, data_alteracao, 
 data_cadastro, garagem, inativo, mictorio, observacoes, quarto, suite, endereco_id, proprietario_id,
 usuarioAlteracao_id, usuarioCadastro_id FROM Imovel

[adautodasilvalima]

Retornou este erro:

Hibernate: select id, case areaServico when 'S' then 'Sim' when 'N' then 'Não' end as area, case sobrado when 'S' then 'Sim' when 'N' then 'Não' end as sobr, data_alteracao, data_cadastro, garagem, inativo, mictorio, observacoes, quarto, suite, endereco_id, proprietario_id, usuarioAlteracao_id, usuarioCadastro_id FROM Imovel
out 20, 2017 11:06:45 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 0, SQLState: S0022
out 20, 2017 11:06:46 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: Column 'areaServico' not found.

Meu código::

> @SuppressWarnings("unchecked")
> 	public List<ImovelImpl> todosCompleta() {
> 		StringBuilder sb =  new StringBuilder();
> 		sb.append("select id, " +
> 				  "case areaServico when 'S' then 'Sim' when 'N' then 'Não' end as area, " +
> 			  "case sobrado when 'S' then 'Sim' when 'N' then 'Não' end as sobr, data_alteracao, " + 
>  "data_cadastro, garagem, inativo, mictorio, observacoes, quarto, suite, endereco_id, proprietario_id, " + 
> 				  "usuarioAlteracao_id, usuarioCadastro_id FROM Imovel");

> 		Query query = manager.createNativeQuery(sb.toString(), Imovel.class);
            O erro aconteceu nesta linha:
> 		**_List<Object> listObj = query.getResultList();_**
> 		List<ImovelImpl> listImoveis = new ArrayList<ImovelImpl>();

[edumatias]

ERROR: Column ‘areaServico’ not found.

Essa coluna areaServico que não está sendo encontrada.

Você está apontando pro mesmo banco que efetuou a sua consulta no MYSQL e funcionou, certo?

Tenta excluir e recriar esta coluna. Ela não está sendo reconhecida.

[edumatias]

sb.append("select id, " +
> "case areaServico when ‘S’ then ‘Sim’ when ‘N’ then ‘Não’ end as area, " +
> "case sobrado when ‘S’ then ‘Sim’ when ‘N’ then ‘Não’ end as sobr, data_alteracao, " +
> "data_cadastro, garagem, inativo, mictorio, observacoes, quarto, suite, endereco_id, proprietario_id, " +
> “usuarioAlteracao_id, usuarioCadastro_id FROM Imovel”);

Se você remover areaServico ai funciona?

Ex:

sb.append("select id, " +
      "case sobrado when 'S' then 'Sim' when 'N' then 'Não' end as sobr, data_alteracao, " +
     "data_cadastro, garagem, inativo, mictorio, observacoes, quarto, suite, endereco_id, proprietario_id, " +  				  
     "usuarioAlteracao_id, usuarioCadastro_id FROM Imovel");

[adautodasilvalima]

Olha só, troquei o nome do campo e o erro continuou.
Então eu tirei o campo do select e deu o mesmo erro ??? aí é loucura né?

Quando você me respondeu a primeira vez passou uma linha de código assim:
Query query = manager.createNativeQuery(sb.toString());
Mas eu estava criando a Query Nativa assim:
Query query = manager.createNativeQuery(sb.toString(), Imovel.class);

Foi retirar o nome da classe na chamada que funcionou.

Agora está dando outros erros, mas a sql funcionou. vou analisar o que é,
muito obrigado Edu!!!

[edumatias]

Query query = manager.createNativeQuery(sb.toString(), Imovel.class);

Isso,

Por estar usando createNativeQuery você recebe um retorno da uma lista Object, que é genérico, pois a sua classe até então é desconhecida.

Ai você trabalha essa lista para preencher uma lista do seu model.

Ótimo que achou uma solução.

Quando puder marque o comentário como Solução e altere o título para [RESOLVIDO]

Caso necessite de ajuda com o restante dos erros, poste aqui.

[]'s