Problem showing data Many to Many

Question

I’m having trouble displaying the result of the query. I’m not being able to display the results correctly.

I have the following tables and records:

CREATE DATABASE IF NOT EXISTS `livraria`;
USE livraria;

CREATE TABLE IF NOT EXISTS `categoria` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nome` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `livro` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `categoria_id` INT UNSIGNED NOT NULL,
  `nome` VARCHAR(60) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `categoria_fk` FOREIGN KEY (`categoria_id`) REFERENCES 
`categoria` (`id`)
)ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `autor` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nome` VARCHAR(60) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `livro_autor` (
  `livro_id` INT UNSIGNED NOT NULL,
  `autor_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`livro_id`, `autor_id`),
  CONSTRAINT `livro_fk` FOREIGN KEY (`livro_id`) REFERENCES `livro` (`id`),
  CONSTRAINT `autor_fk` FOREIGN KEY (`autor_id`) REFERENCES `autor` (`id`)
)ENGINE = InnoDB;



INSERT INTO categoria (`nome`) VALUES ('Ação'), ('Aventura'), ('Terror');
INSERT INTO autor (`nome`) VALUES ('Fulano'), ('Sicrano'), ('Beltrano');
INSERT INTO livro (`categoria_id`, `nome`) VALUES (1, 'Explosão Total'), (1, 'Piloto Mariano'), (3, 'Terror no hotel da rua 3');

INSERT INTO livro_autor (`livro_id`, `autor_id`) VALUES (1, 1), (1, 2), (2, 3);

# TESTE

SELECT DISTINCT * FROM livro l 
INNER JOIN categoria c ON l.categoria_id = c.id
INNER JOIN livro_autor la ON l.id = la.livro_id
INNER JOIN autor a WHERE a.id = la.autor_id;

+----+--------------+----------------+----+----------+----------+----------+----+----------+
| id | categoria_id | nome           | id | nome     | livro_id | autor_id | id | nome     | 
+----+--------------+----------------+----+----------+----------+----------+----+----------+
| 1  | 1            | Explosão Total | 1  | Ação     | 1        | 1        | 1  | Fulano   |
+----+--------------+----------------+----+----------+----------+----------+----+----------+
| 1  | 1            | Explosão Total | 1  | Ação     | 1        | 2        | 2  | Sicrano  |
+----+--------------+----------------+----+----------+----------+----------+----+----------+
| 2  | 1            | Piloto Mariano | 1  | Ação     | 2        | 3        | 3  | Beltrano  |
+----+--------------+----------------+----+----------+----------+----------+----+----------+

In java I have my classes: Author, Category, Book, with the basic implementation of a javabean, I also have the class that represents the relationship many to many Book Maker

public class LivroAutor {

    private Livro livro;
    private Autor autor;

    public Livro getLivro() {
        return livro;
    }
    public void setLivro(Livro livro) {
        this.livro = livro;
    }
    public Autor getAutor() {
        return autor;
    }
    public void setAutor(Autor autor) {
        this.autor = autor;
    }
}

I also have the Book class with the method listAll():

public List<Livro> listAll() {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    String sql = "SELECT DISTINCT * FROM livro l " + 
            "INNER JOIN categoria c ON l.categoria_id = c.id " + 
            "INNER JOIN livro_autor la ON l.id = la.livro_id " + 
            "INNER JOIN autor a WHERE a.id = la.autor_id";
    try {
        stmt = this.conn.prepareStatement(sql);
        rs = stmt.executeQuery();

        List<Livro> livros = new ArrayList<>();
        List<LivroAutor> listaAutores =  new ArrayList<>();

        while (rs.next()) {
            Livro livro = new Livro(rs.getInt("l.id"), rs.getString("l.nome"));
            Categoria categoria = new Categoria(rs.getInt("c.id"), rs.getString("c.nome"));
            Autor autor = new Autor(rs.getInt("a.id"), rs.getString("a.nome"));

            LivroAutor livroAautor = new LivroAutor();
            livroAautor.setAutor(autor);

            listaAutores.add(livroAautor);


            livro.setCategoria(categoria);
            livro.setAutores(listaAutores);

            livros.add(livro);

        }
        rs.close();
        stmt.close();
        this.conn.close();

        return livros;


    } catch (SQLException e) {
        e.printStackTrace();
    }
    return null;
}

JSP is like this:

    <table class="table table-hover table-striped table-sm">
        <thead>
            <tr>
                <th>id</th>
                <th>Livro</th>
                <th>Categoria</th>  
                <th>Autores</th>    
                <th>Ações</th>
            </tr>
        </thead>
        <tbody>
                <jsp:useBean id="livro" class="br.com.livraria.dao.LivroDao" />
                <c:forEach var="liv" items="${livro.listAll()}">
                <tr>
                    <td>${liv.id}</td>
                    <td>${liv.nome}</td>
                    <td>${liv.categoria.nome}</td>
                    <td>
                        <c:forEach var="aut" items="${liv.autores}">
                            ${aut.autor.nome}, 
                        </c:forEach>
                    </td>

                    <td>
                        <a href="#">Editar</a>
                        <a href="#">Excluir</a>    
                    </td>
                </tr>
                </c:forEach>
        </tbody>    
    </table>

and the result is this

https://postimg.cc/dDNKMbSw

As the result shown in the image above, it was not as expected...

If anyone has any hint of how to resolve the doubt presented in the image, where there is a repetition of records and how to display only the authors who wrote the book. Thank you very much.

Obs*: I could use JPA/Hibernate with the @Manytomany annotation and everything, but it is precisely because I do not know how to nail that I am studying jdbc and this doubt arose

Thank you guys!

Answer 1

Currently I have no way to test in mysql so I did on postgresql

In your class Livro change the field autores to receive a list of type Author only with updated gets and sets:

public class Livro { ... List<Autor> autores = new ArrayList<>(); ... // gets and sets }

Your query has a small error in the clause WHERE you do not need this clause, because you are doing a select without filter (I imagine using WHERE to search for a specific book or specific category), the correct would be:

SELECT DISTINCT * FROM livro l INNER JOIN categoria c ON l.categoria_id = c.id INNER JOIN livro_autor la ON l.id = la.livro_id INNER JOIN autor a on a.id = la.autor_id;

With this you will get the same results:

+----+--------------+----------------+----+----------+----------+----------+----+----------+
| id | categoria_id | nome           | id | nome     | livro_id | autor_id | id | nome     | 
+----+--------------+----------------+----+----------+----------+----------+----+----------+
| 1  | 1            | Explosão Total | 1  | Ação     | 1        | 1        | 1  | Fulano   |
+----+--------------+----------------+----+----------+----------+----------+----+----------+
| 1  | 1            | Explosão Total | 1  | Ação     | 1        | 2        | 2  | Sicrano  |
+----+--------------+----------------+----+----------+----------+----------+----+----------+
| 2  | 1            | Piloto Mariano | 1  | Ação     | 2        | 3        | 3  | Beltrano  |
+----+--------------+----------------+----+----------+----------+----------+----+----------+

Now we can group them by author and add them to their respective books. In his method listAll() :

    ...
    List<Livro> livros = new ArrayList<>();
    //1 -List<LivroAutor> listaAutores =  new ArrayList<>();

    while (rs.next()) {
        Livro livro = new Livro(rs.getInt("l.id"), rs.getString("l.nome"));
        Categoria categoria = new Categoria(rs.getInt("c.id"), rs.getString("c.nome"));
        Autor autor = new Autor(rs.getInt("a.id"), rs.getString("a.nome"));

        //1 -LivroAutor livroAautor = new LivroAutor();
        //1 -livroAautor.setAutor(autor);

        //1 -listaAutores.add(livroAautor);


        livro.setCategoria(categoria);
        //1 -livro.setAutores(listaAutores);

        int index = livros.indexOf(livro);
        if (index == -1) {
            livros.add(livro);
        }else {
            Livro livroExistente = livros.get(index);
            livroExistente.getAutores().add(autor);
        }

    }
   ...

In your JSP change the foreach:

<td>
   <c:forEach var="aut" items="${liv.autores}">
        ${aut.nome}, 
   </c:forEach>
</td>

Explanation:

• The first thing to do is to change the author field to receive an author list. When I have a relationship N - N, it’s actually a relationship 1 - N where ONE author may have several books and ONE book may have several authors. If my 3° table only references ids i would not create a class to represent it. The same can be done in the class Autor create a field with the list of Books that belongs to it.

• With this remove or comment the code that references the class LivroAutor. The method indexOf() finds the first occurrence of the element in the list and returns the position or returns -1 if this element is not present in the list. It is quite simple if the book already exists just add a new author to this book.

It is important that your book class is with the equals and methods hashcode implemented. Answer here on the stack about. Algaworks-Equals and hashcode