Use of the database connection class

Question

It seems to be 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 not? Would it be good practice to do this or would it be better if I created them as @Entity?

Answer 1

If you don’t want to map your tables, you can query them through 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();

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

Answer 2

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

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;

}

}

Ai sent the data to the entity Address and in my controller 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());
    }

}