Arquivo

Posts Tagged ‘sql’

Como Encontrar Linhas Duplicadas no Banco de Dado

Ultimamente estou trabalhando bastante com consultas SQL. Essa dica pode ser bastante útil para você que deseja descobrir que linhas estão duplicadas no banco de dados de acordo com algum critério. Você precisa, basicamente, fazer um join da tabela analisada com ela mesma. Aqui há várias dicas de como fazer essa consulta, mas o select abaixo funcionou bem para mim:

SELECT *
FROM TABLE A
WHERE EXISTS (
  SELECT 1 FROM TABLE
  WHERE COLUMN_NAME = A.COLUMN_NAME
  AND ROWID < A.ROWID
)
Categorias:Programação Tags:,

Como Exibir o Valor Máximo de uma Coluna com JPA

Consigo pensar em várias maneiras diferentes de apresentar o maior valor de uma coluna, mas há duas que considero as mais simples se você usa JPA: JPQL e TypedQuery.

1. Com JPQL fica assim:

Integer maiorIdade = (Integer)entityManager.createQuery(
   "SELECT MAX(f.idade) FROM Funcionario f").getSingleResult();

2. Com TypedQuery fica assim:

Integer maiorIdade = entityManager.createQuery(
   "SELECT MAX(f.idade) FROM Funcionario f", Integer.class)
     .getSingleResult();

Qual das duas é a melhor?

Acho que a resposta para esse tipo de pergunta ajuda a determinar a maturidade profissional. Faz anos que respondo quase automaticamente: depende. Se o seu objetivo é extrair o valor máximo de uma coluna mesmo que precise passar alguns parâmetros para construir uma cláusula WHERE simples, utilize JPQL ou NamedQuery. Se a sua consulta demanda parâmetros dinâmico e alguns JOIN mais elaborados, é melhor passar para o universo do Criteria onde TypedQuery é um dos atores.

Como Fazer o Count de Várias Tabelas com Union All

Para fazer o count de várias tabelas de uma vez só, você pode utilizar o operador UNION ALL. De acordo com a documentação:

The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows

Vamos supor que você queira fazer o count de três tabelas: TBL_A, TBL_B e TBL_C. A consulta ficaria assim:

SELECT * FROM (
    SELECT 'TBL_A',COUNT(TA.PK)
    FROM TBL_A TA GROUP BY 'TBL_A'
  UNION ALL
    SELECT 'TBL_B', COUNT(TB.PK)
    FROM TBL_B TB
  UNION ALL
    SELECT 'TBL_C', COUNT(TC.PK)
    FROM TBL_C TC
)

Utilizando a Interface ReturningWork para Diminuir a Complexidade de um ORM

ORM (Object Relational Mapping) em geral e o Hibernate em particular são fontes de discordância entre DBAs e desenvolvedores de software. Minhas melhores experiências ao definir arquiteturas foram quando os diferentes papéis defenderam suas opiniões com argumentos sólidos e estavam dispostos a ouvir, entender e aceitar a opinião dos outros membros do time.

Se a discussão gira em torno de lógica de negócio em banco de dados ou na aplicação, a resposta correta é “depende”. Em geral, mas isso também “depende”, no início de um projeto os desenvolvedores devem implementar a lógica de negócio do lado deles, pois eles têm mais ferramentas para utilizar inclusive para realizar testes. Em algum momento tardio no desenvolvimento ou durante algum tempo do software rodando em ambiente de testes integrados pode-se chegar a conclusão de que é melhor passar parte da lógica de negócio para o banco de dados em virtude de um grande ganho de performance. Só é possível chegar a essa conclusão após medições. Falando em regras de negócio em banco de dados e otimização precoce, passo a palavra para dois nomes conhecidos: Martin Fowler e Joshua Bloch.

Para Martin Fowler em Patterns of Enterprise Application Architecture:

Por todas estas questões, muitas pessoas evitam implementar regras de negócio dentro de um banco de dados. Eu tento me alinhar com esta visão a menos que haja um grande ganho de desempenho a ser obtido, o que, para ser sincero frequentemente ocorre. Nesse caso, pego um método de negócio da camada de domínio e, alegremente o transformo em um procedure dentro de um banco de dados. Faço isso apenas em áreas com claro problemas de desempenho, tratando-o como um abordagem de otimização e não como um principio arquitetural.

Para Joshua Bloch em Java Effective:

A historia das décadas passadas nos mostram que otimização prematura na verdade é mais propenso a causar danos do que benefícios. O caminho da otimização precoce pode leva-lo a uma solução que ainda não seja rápida, arquiteturalmente ruim e pior de tudo inflexível de difícil evolução. Portanto, não tente criar programas rápidos! Na verdade, foque em criar bons programas, usando todos os conceitos, princípios e abordagem necessários. Se um programa bem arquitetado não for rápido suficiente, a boa arquitetura já estabelecida permitira que ele seja facilmente otimizado. Não pense em problemas de desempenho enquanto estiver projetando uma solução. Quando terminar a codificação, avalie seu desempenho. Se ele for suficientemente rápido, tudo estará resolvido. Caso contrário, localize a fonte de gargalo usando uma ferramenta de profile e trabalhe nas partes relevantes. Repita esse processo conforme necessário, avaliando o desempenho após cada alteração até apresentar um tempo satisfatório.

Tudo é muito bonito quando um software é construído desde o início, mas e quando um software legado deve ser evoluído? É bem provável que alguém lhe diga algo do tipo: “Já está tudo pronto! Você só precisa chamar essa proc aqui, aquele web service ali, consumir aquele outro serviço acolá e gravar naquela tabela lá”. Detalhe: a tabela tem chave primária sêxtupla, o web service é SOA e o serviço é REST. O que nos importa aqui é a complexidade da integração com o banco de dados.

A característica marcante do Hibernate é a possibilidade de criar um modelo orientado a objetos que representa um modelo de dados. Se você tem um modelo de dados legado, utilizar o mapeamento do Hibernate pode ser muito complexo.

É possível contornar esse problema sem criar um modelo complexo e sem precisar escrever duas formas diferentes de acesso a dados. Vou mostrar como utilizar a Interface ReturningWork[T] para obter uma conexão com o banco dentro da mesma Session. A classe Session oferece o método [T] T doReturningWork(ReturningWork[T] work) para possibilitar o uso da conexão gerenciada pela sessão e retornar um resultado.

Primeiro, você precisa fazer uma implementação de ReturningWork[T] para executar seu SQL no método execute:

public class MyWork implements ReturningWork<List<Produto>>
{
    @Override
    public List<Produto> execute( Connection connection ) throws SQLException
    {
        List<Produto> produtos = new ArrayList<Produto>;
        PreparedStatement ps = connection.prepareStatement( "SELECT * FROM VW_PRODUTO_DIFICIL_DE_MAPEAR" );
        ResultSet rs = ps.executeQuery();
        while( rs.next() )  
        {
            produto = new Produto(rs.getInt( 1 ), rs.getString( 2 ));
            produtos.add(produto);
        }
        return produtos;
    }
}

Notou que o SQL incide sobre uma view? A complexidade do mapeamento está na implementação da view, mas poderia estar em uma stored procedure ou ainda poderíamos escrever todos os relacionamentos e executar diretamente contra o banco de dados, mas aí já não recomendo, pois uma vez que a concessão foi feita, a responsabilidade foi dividida com um DBA e é ele quem deve fornecer uma solução que diminua a complexidade do mapeamento das tabelas para o desenvolvedor. Em seguida, você utiliza o método doReturningWork passando sua implementação de ReturningWork para obter o valor retornado:

        MyWork myWork = new MyWork( );

        Session session = getSessionFactory().openSession();

        List<Produto> produtos = session.doReturningWork( myWork );

Simples e até elegante, não? Você poderia utilizar a interface Work para obter o mesmo resultado, mas o caminho seria mais longo e o valor resultante teria que ser armazenado temporariamente na sua implementação de Work – o design não ficaria tão elegante. Essa interface é indicada para execução de SQL que não retorna valor, como um update, por exemplo.

Referências

1. http://www.informit.com/guides/content.aspx?g=java&seqNum=575
2. http://keyurj.blogspot.com.br/2012/12/dowork-in-hibernate.html
3. http://www.concretepage.com/hibernate-4/hibernate-4-returningwork-and-session-doreturningwork-example-for-jdbc