Using the database connection class

1

It seems like a silly question but come on, I have some tables in my database and I need to query them, but they are not @Entity, I did not create the sources, the only way to do the queries would be with the Connection class. ? Would it be a good practice to do this or would it be better for me to create them as @Entity?

    
asked by anonymous 02.03.2015 / 18:26

2 answers

0

I made a class for connection and select what I needed:

import br.com.ofertacidade.model.dominio.Endereco;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class conexaoDadosCEP {

private final String SELECT_CEP = "SELECT \n"
        + "  logs.log_tipo_logradouro as tipoRua, \n"
        + "  logs.log_no as logradouro,\n"
        + "  bais.bai_no as bairro,\n"
        + "  locs.loc_no as cidade,\n"
        + "  locs.ufe_sg as uf,\n"
        + "  logs.cep\n"
        + "FROM  cep.log_logradouro logs ,cep.log_localidade locs ,cep.log_bairro  bais\n"
        + "WHERE logs.loc_nu_sequencial = locs.loc_nu_sequencial\n"
        + "AND logs.bai_nu_sequencial_ini = bais.bai_nu_sequencial\n"
        + "AND  logs.cep = ?";

public Connection getConnection() throws SQLException {
    try {
        Connection con = null;
        con = DriverManager.getConnection("jdbc:postgresql://localhost/BancoTCC?user=postgres&password=postgres");

        return con;
    } catch (SQLException ex) {

    }
    return null;
}

public void closeConnnection(Connection con) {
    try {
        con.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public List<Endereco> selectCEP(String cep) throws SQLException {
    Connection con = null;
    List<Endereco> listDadosCliente = new ArrayList<>();
    try {
        con = getConnection();

        PreparedStatement prepared = con.prepareStatement(SELECT_CEP);
        prepared.setString(1, cep);

        ResultSet resultSet = prepared.executeQuery();

        while (resultSet.next()) {
            Endereco dadosEndereço = new Endereco();
            dadosEndereço.setBairro(resultSet.getString("bairro"));
            dadosEndereço.setCidade(resultSet.getString("cidade"));
            dadosEndereço.setCep(resultSet.getString("cep"));
            dadosEndereço.setTipoEndereco(resultSet.getString("tipoRua"));
            dadosEndereço.setLogradouro(resultSet.getString("logradouro"));
            dadosEndereço.setUF(resultSet.getString("uf"));
            listDadosCliente.add(dadosEndereço);

        }

    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        closeConnnection(con);
    }
    return listDadosCliente;

}

}

I then sent the data to the Address entity and in my controller it called the method to get the data I needed.

public void testeCEP() throws SQLException {
    cep = cliente.getCep().toString();
    cep = cep.replaceAll("\D+", "");
    enderecos = dadosCEP.selectCEP(cep);
    for (Endereco a : enderecos) {
        System.out.println(a.getBairro());
        System.out.println(a.getCep());
        System.out.println(a.getCidade());
        System.out.println(a.getLogradouro());
        System.out.println(a.getTipoEndereco());
        System.out.println(a.getUF());
    }

}
    
05.03.2015 / 15:52
2

If you do not want to map your tables, you can query them using native queries. You create a native query as follows:

EntityManager em = getEntityManager();

//query em tabelas não mapeadas, usar SQL puro
Query nativeQuery = em.createNativeQuery("select * from tabela");

List<Object[]> rows = nativeQuery.getResultList();

Because your tables are not mapped, the result is returned as a list of object arrays. Each array is a table row.

    
03.03.2015 / 19:44