MySQL printing multiple times

Question

MySQL printing multiple times

Navigation

#1 by (2 votes)
#2 by (0 votes)
#3 by (0 votes)

0

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

I know where the problem is, but I can not 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 the two tables.

See:

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!"); 

   }

mysql sql java jdbc

asked by anonymous 24.07.2018 / 03:06

3 answers

Increase +1 in the database Selecting SQL tables

score 2 · Answer 1

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 example above is just a reference to the code you posted.

score 0 · Answer 2

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

SELECT DISTINCT * FROM locadora.cliente, locadora.endereco

But I recommend that you improve your query.

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 list the primary key of one table with the foreign key of another to prevent the query from returning with repeated information.

score 0 · Answer 3

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

select ENDE.tipo_endereco, pasadouro, neighborhood, city, uf, cep, CLI.name, code, seBomPagador, phone from endereco to ENDE inner join client CLI on (CLI.id = en.client_id);