redundant row data, or entity, in @OneToMany table relationship

Question

I'm getting redundant rows in the NEWSGROUP table. How can I change the entity or queries to prevent that?

Schema:

mysql> 
mysql> USE usenet;SHOW TABLES;DESCRIBE ARTICLE;DESCRIBE NEWSGROUP;
Database changed
+------------------+
| Tables_in_usenet |
+------------------+
| ARTICLE          |
| NEWSGROUP        |
+------------------+
2 rows in set (0.00 sec)

+---------------+------------+------+-----+---------+----------------+
| Field         | Type       | Null | Key | Default | Extra          |
+---------------+------------+------+-----+---------+----------------+
| ID            | bigint(20) | NO   | PRI | NULL    | auto_increment |
| MESSAGENUMBER | int(11)    | YES  |     | NULL    |                |
| NEWSGROUP_ID  | bigint(20) | YES  | MUL | NULL    |                |
+---------------+------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| ID        | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| NEWSGROUP | varchar(255) | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql>

Assuming a single connection to the database to keep the solution simple for the time being, while I do see that it's a complex topic, presumably there's a simple approach assuming the "right" conditions.

The Article entity:

    package net.bounceme.dur.usenet.model;

    import java.io.Serializable;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Set;
    import javax.mail.Folder;
    import javax.mail.Message;
    import javax.persistence.*;

    @Entity
    public class Article implements Serializable {

        private static final long serialVersionUID = 1L;
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Long id;
        @Column
        private int messageNumber;
        @ManyToOne(cascade = CascadeType.PERSIST)
        private Newsgroup newsgroup;

        public Article() {
        }

        public Article(Message message, Folder folder) {
            messageNumber = message.getMessageNumber();
            newsgroup = new Newsgroup(folder); //need to ensure uniqueness
        }

        public Long getId() {
            return id;
        }

        public void setId(Long id) {
            this.id = id;
        }

        @Override
        public int hashCode() {
            int hash = 0;
            hash += (id != null ? id.hashCode() : 0);
            return hash;
        }

        @Override
        public boolean equals(Object object) {
            // TODO: Warning - this method won't work in the case the id fields are not set
            if (!(object instanceof Article)) {
                return false;
            }
            Article other = (Article) object;
            if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
                return false;
            }
            return true;
        }

        @Override
        public String toString() {
            return "\nmessageNumber\t" + messageNumber;
        }

        public int getMessageNumber() {
            return messageNumber;
        }

        public void setMessageNumber(int messageNumber) {
            this.messageNumber = messageNumber;
        }
    }



And the Newsgroup entity:


package net.bounceme.dur.usenet.model;

import java.io.Serializable;
import java.util.HashSet;
import java.util.Set;
import javax.mail.Folder;
import javax.persistence.*;

@Entity
public class Newsgroup implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column //@Unique @UniqueConstraint interface..?
    private String newsgroup;
    @OneToMany(mappedBy = "newsgroup", cascade = CascadeType.PERSIST)
    private Set<Article> articles = new HashSet<>();

    public Newsgroup() {
    }

    public Newsgroup(Folder folder) {
        newsgroup = folder.getFullName();//if row already exists, then what?
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    @Override
    public int hashCode() {
        int hash = 0;
        hash += (id != null ? id.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {
        // TODO: Warning - this method won't work in the case the id fields are not set
        if (!(object instanceof Newsgroup)) {
            return false;
        }
        Newsgroup other = (Newsgroup) object;
        if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return newsgroup;
    }
}

Instead of creating a new Newsgroup entity each time, how can I query for an existing newsgroup first?

Redundant rows in NEWSGROUP:

mysql> 
mysql> USE usenet;SELECT * FROM ARTICLE;SELECT * FROM NEWSGROUP;
Database changed
+----+---------------+--------------+
| ID | MESSAGENUMBER | NEWSGROUP_ID |
+----+---------------+--------------+
|  1 |             4 |            1 |
|  2 |             5 |            2 |
|  3 |             6 |            3 |
|  4 |             7 |            4 |
|  5 |             8 |            5 |
|  6 |             9 |            6 |
|  7 |            10 |            7 |
|  8 |            11 |            8 |
|  9 |             4 |            9 |
| 10 |             4 |           10 |
| 11 |             5 |           11 |
| 12 |             6 |           12 |
| 13 |             7 |           13 |
| 14 |             8 |           14 |
| 15 |             9 |           15 |
| 16 |            10 |           16 |
| 17 |            11 |           17 |
| 18 |             4 |           18 |
| 19 |             5 |           19 |
| 20 |             6 |           20 |
| 21 |             7 |           21 |
| 22 |             8 |           22 |
| 23 |             9 |           23 |
| 24 |            10 |           24 |
| 25 |            11 |           25 |
+----+---------------+--------------+
25 rows in set (0.00 sec)

+----+-------------------------------+
| ID | NEWSGROUP                     |
+----+-------------------------------+
|  1 | gwene.com.androidcentral      |
|  2 | gwene.com.androidcentral      |
|  3 | gwene.com.androidcentral      |
|  4 | gwene.com.androidcentral      |
|  5 | gwene.com.androidcentral      |
|  6 | gwene.com.androidcentral      |
|  7 | gwene.com.androidcentral      |
|  8 | gwene.com.androidcentral      |
|  9 | gwene.com.blogspot.emacsworld |
| 10 | gwene.com.blogspot.googlecode |
| 11 | gwene.com.blogspot.googlecode |
| 12 | gwene.com.blogspot.googlecode |
| 13 | gwene.com.blogspot.googlecode |
| 14 | gwene.com.blogspot.googlecode |
| 15 | gwene.com.blogspot.googlecode |
| 16 | gwene.com.blogspot.googlecode |
| 17 | gwene.com.blogspot.googlecode |
| 18 | gwene.com.economist           |
| 19 | gwene.com.economist           |
| 20 | gwene.com.economist           |
| 21 | gwene.com.economist           |
| 22 | gwene.com.economist           |
| 23 | gwene.com.economist           |
| 24 | gwene.com.economist           |
| 25 | gwene.com.economist           |
+----+-------------------------------+
25 rows in set (0.00 sec)

mysql>

im not sure if u understood your question right.. when do you get the redundant rows... while saving an article? and why / where do you create new NewsGroup entities? — Jenson, Aug 03 '12 at 09:10
@Jenson yes, each Article creates a **new** Newsgroup entity when when that should only happen if it's a genuinely new, or unique, newsgroup. — Thufir, Aug 03 '12 at 09:13
than you have to search in the Article's constructor for that NewsGroup you want to add -> from database (if its null, create one) — Jenson, Aug 03 '12 at 09:16
@Jenson search with EntityManager type query? that doesn't create a database connection problem? Can't you end up with, potentially, dozens of recursive connections with that approach? Although, if it works for my use case, then it's sufficient :) thanks. — Thufir, Aug 03 '12 at 09:21

score 1 · Answer 1 · answered Aug 03 '12 at 09:35

Your Problem is that your ceating NewsGroups all the time... and of course if your saving the Article, a new 'reduntant' NewsGroup will be inserted into the database, cause i has no ID.

Hint (hopefully):

Search inside the Articles Countstructor (or outside the Article Class and add it) for the NewsGroup you want to add to the Article.

My Problem is... i dont know what that 'Folder' is so i just can give u a hint:

Query query = entityManager.createNativeQuery("SELECT * FROM Newsgroup WHERE "dunno what you want to select here" = ?1",
            Newsgroup.class);
query.setParameter(1, "your parameter);

Newsgroup group = (Newsgroup) query.getSingleResult();

If NewsGroup is null create a new One, otherwhise add the one you found.

I was also looking at [refresh](http://stackoverflow.com/questions/836569/what-do-refresh-and-merge-mean-in-terms-of-databases) but I'll try that. The folder is a javax.mail.folder, but your answer makes sense. — Thufir, Aug 03 '12 at 09:43

redundant row data, or entity, in @OneToMany table relationship

1 Answers1