Mysql printing multiple times

Asked

Viewed 101 times

0

I’m having a problem printing Mysql data in Eclipse. When I put to print the list of registered data it is printing 3 times each information.

I know where the problem is, but I can’t see the solution. My database has a schema and two tables (cliente and endereco). What happens is that when I enter the information of 3 people, for example, Eclipse prints 3 times each information because the rs.next() is taking from both tables.

Look at:

public void listarClientes() {

   String sql = "SELECT * FROM locadora.cliente, locadora.endereco";

   try {

      PreparedStatement ps = jdbc.getConexao().prepareStatement(sql); ResultSet rs = ps.executeQuery();

      while (rs.next()) {

         System.out.println("Nome: " + rs.getString("nome") + " - Código: " + rs.getString("codigo") + " - Bom pagador: " + rs.getBoolean("seBomPagador") + " - Telefone: " + rs.getString("telefone"));

         System.out.println("Tipo de endereço: " + rs.getString("tipo_endereco") + " - Logradouro: " + rs.getString("logradouro") + " - Bairro: " + rs.getString("bairro") + " - Cidade: " + rs.getString("cidade") + " - UF: " + rs.getString("uf") + " - Cep: " + rs.getString("cep"));

         System.out.println(); 
      }

   System.out.println(); rs.close();

   } catch (Exception e) { // TODO: handle exception

   System.out.println("Erro na listagem dos clientes!"); 

   }
java mysql sql jdbc
  • 3

    Your problem should be in sql. Try to do more or less this way: SELECT * FROM cliente as c INNER JOIN endereco as e ON (c.idEndereco = e.id). How the two tables should have a relationship, the table cliente must contain an id of the table endereco, this way you can bring the data of the address of your respective client.

  • Thanks Thiago, I’ll see if I can!

  • Recommended reading: https://answall.com/q/172909/132

3 answers

2

Improve your query by linking tables and grouping values with group by

SELECT nome, endereco FROM locadora.cliente, locadora.endereco 
where locadora.cliente.cli_id = locadora.endereco.cli_id
group by nome, endereco

normalization does not look good, the above example is just a reference to the code you posted.

0

You can solve this problem with the Mysql DISTINCT command after SELECT. 

SELECT DISTINCT * FROM locadora.cliente, locadora.endereco

However I recommend that you improve your consultation.

SELECT cliente.nome, cliente.codigo, cliente.seBomPagador, cliente.telefone, 
       endereco.tipo_endereco, endereco.logradouro, endereco.bairro, endereco.cidade, endereco.uf, endereco.cep 
FROM cliente, endereco
 WHERE cliente.id = endereco.cliente_id;

After the WHERE you relate the primary key of one table to the foreign key of another to prevent the query from returning with repeated information.

0

Thank you all for your help! It was solved this way in the eclipse:

select ENDE.typo_address,street, neighborhood, city, zip code, CLI.name, code,seBomPaper,telephone from address to ENDE Inner Join customers to CLI on (CLI.id = ENDE.cliente_id);

Browser other questions tagged java mysql sql jdbc

You are not signed in. Login or sign up in order to post.