Consulta SQL com INNER JOIN e GROUP BY?

Programação Java
, ,
#1

Estou com problemas para realizar uma consulta SQL simples no banco mas no JAVA estou com muitas dificuldades. Tenho 2 classes Atendimentos e Motivos.

Criei a seguinte classe:

public class ConsultasIntegradasRepository implements Serializable {
	
 
	private static final long serialVersionUID = -7453686932955572773L;
	
	@Produces
	@PersistenceContext(unitName="sacFumacenseDS")
	EntityManager em;
	
	
	public List<AvgAtendConcluidos> getList() {
		List query = em.createNativeQuery("SELECT b.descricao as 'motivoSAC', avg(DATEDIFF(a.dataEncerramento,a.dataAbertura)) as 'mediaAtendimento' "+
                                          "  FROM atendimentos a " +
                                     " INNER JOIN motivos b ON a.codigoMotivo = b.codigo "+
                                          " WHERE a.situacao = 'R' "+
                                       " GROUP BY b.descricao").getResultList();
		System.out.println(query);
		return  query;
	}
}

A classe AvgAtendConcluidos foi criada com dois campos apenas para identificar as colunas resultantes do SQL.

public class AvgAtendConcluidos {
	
	private String motivoSAC;
	private BigDecimal mediaAtendimento;
	
	public AvgAtendConcluidos() {
	}

	public AvgAtendConcluidos(String motivoSAC, BigDecimal mediaAtendimento) {
		this.motivoSAC = motivoSAC;
		this.mediaAtendimento = mediaAtendimento;
	}

	public String getMotivoSAC() {
		return motivoSAC;
	}

	public void setMotivoSAC(String motivoSAC) {
		this.motivoSAC = motivoSAC;
	}

	public BigDecimal getMediaAtendimento() {
		return mediaAtendimento;
	}

	public void setMediaAtendimento(BigDecimal mediaAtendimento) {
		this.mediaAtendimento = mediaAtendimento;
	}

Nunca precisei fazer nenhuma consulta desta… alguem poderia dar uma luz?

As classes Atendimentos e Motivos estão em :

#2

Olá, que problemas?

#3

Ele não consulta! Não traz nada… não da mensagem de erro no log do Wildfly… nada…

#4

E onde vc está chamando esta consulta?

#5

Como utilizo REST, chamo pelo postman para teste:

http://IP:8080/sac/rest/consultasIntegradas/list

Todas as outras consultas de uma unica classe/tabela funciona…

#6

Rodrigo! Desculpe-me se estou fazendo a pergunta da forma errada,:

Minha dúvida é: Como criar uma consulta SQL que traga resultado de tuas classes diferentes (que são abstrações de duas tabelas diferentes no banco de dados).

O SQL é esse(depois vou passar as datas e situacao por parametro, mas para não complicar agora…):

SELECT b.descricao as ‘Motivo SAC’, avg(DATEDIFF(a.dataEncerramento,a.dataAbertura)) as 'Media Atendimento’
FROM atendimentos a
INNER JOIN motivos b ON a.codigoMotivo = b.codigo
WHERE a.situacao = 'R’
AND a.dataEncerramento between CAST(‘2017-05-01’ AS DATE) and CAST(‘2017-05-31’ AS DATE)
GROUP BY b.descricao

Tenho a classe que tem a representação dos dois atributos que eu quero recuperar do banco AvgAtendConcluidos

public class AvgAtendConcluidos {

private String motivoSAC;
private BigDecimal mediaAtendimento;

public AvgAtendConcluidos() {
}

public AvgAtendConcluidos(String motivoSAC, BigDecimal mediaAtendimento) {
	this.motivoSAC = motivoSAC;
	this.mediaAtendimento = mediaAtendimento;
}

public String getMotivoSAC() {
	return motivoSAC;
}

public void setMotivoSAC(String motivoSAC) {
	this.motivoSAC = motivoSAC;
}

public BigDecimal getMediaAtendimento() {
	return mediaAtendimento;
}

public void setMediaAtendimento(BigDecimal mediaAtendimento) {
	this.mediaAtendimento = mediaAtendimento;
}

}

Depois, eu criei uma classe que recebe a requisição do meu front end:

@Path("/consultasIntegradas")
public class ConsultasIntegradasResources {
@Inject
ConsultasIntegradasServices service;

@GET
@Path("/list")
@Produces("application/json")
public List<AvgAtendConcluidos> listar(){
	if (service.getAll().isEmpty()) {
		return null;
	} else {
		return service.getAll();
	}
}

Esta classe, tem injeção de outra classe que contém isto:

public class ConsultasIntegradasServices {
@Inject
ConsultasIntegradasRepository repository;

public List<AvgAtendConcluidos> getAll(){
	return repository.getList();
}

}

Que por sua vez tem injeção da classe a seguir:

public class ConsultasIntegradasRepository implements Serializable {
private static final long serialVersionUID = -7453686932955572773L;

@Produces
@PersistenceContext(unitName="sacFumacenseDS")
EntityManager em;

public List<AvgAtendConcluidos> getList() {
	TypedQuery<AvgAtendConcluidos> query = em.createQuery("SELECT b.descricao as 'motivoSAC', avg(DATEDIFF(a.dataEncerramento,a.dataAbertura)) as 'mediaAtendimento' "+
                                                          "  FROM atendimentos a " +
                                                     " INNER JOIN motivos b ON a.codigoMotivo = b.codigo "+
                                                          " WHERE a.situacao = 'R' "+
                                                       " GROUP BY b.descricao", AvgAtendConcluidos.class);
		System.out.println(query);
		return  query.getResultList();
	}

}

Esta ultima classe deveria fazer uma consulta SQL no banco e retornar ao meu front um JSON com o resutado…

Eu estou utilizando Wildfly como servidor de aplicação… nos logs, quando eu faço a requisição de testes através do POSTMAN, ele não retorna nada… nem erro… nem resultado JSON…

#7

Minha dúvida é: Como criar uma consulta SQL que traga resultado de tuas classes diferentes (que são abstrações de duas tabelas diferentes no banco de dados).

Por que essa complicação? Não pode criar uma estrutura simples que represente exatamente o resultado do SQL dessa funcionalidade? O que você mesmo fez em AvgAtendConcluidos já não atende?

#8

Eu poderia gerar tudo na classe AvgAtendConcluidos sim… com algumas alterações consegui que o WildFly gerasse o erro:
08:43:30,156 ERROR [org.hibernate.hql.internal.ast.ErrorCounter] (default task-18) Invalid path: 'b.descricao’
08:43:30,156 ERROR [org.hibernate.hql.internal.ast.ErrorCounter] (default task-18) Invalid path: ‘b.descricao’: Invalid path: ‘b.descricao’

P.S: Fiz classes separadas para tentar facilitar possíveis manutenções…

#9 
//Teu POJO:
public class AvgAtendConcluidos {
    
	private String motivoSAC;
	private BigDecimal mediaAtendimento;

	//get/set....
}
	
//Na sua classe de acesso a dados:	
String sql = "SELECT b.descricao as \"motivoSAC\", avg(DATEDIFF(a.dataEncerramento, a.dataAbertura)) as \"mediaAtendimento\" " +
             "FROM atendimentos a " +
             "INNER JOIN motivos b ON a.codigoMotivo = b.codigo " + 
             "WHERE a.situacao = 'R' " +
             "GROUP BY b.descricao";

List<AvgAtendConcluidos> resultado = session.createSQLQuery(sql)
  .setResultTransformer(Transformers.aliasToBean(AvgAtendConcluidos.class))
  .list();

Obs.: Não tem haver com o problema, mas procure deixar o nome da classe no singular.

Para ver mais opções sobre hibernate com sql nativo, consulte a documentação: https://docs.jboss.org/hibernate/orm/5.0/userguide/html_single/chapters/query/native/Native.html