title: ensure uniqueness (distinct record) with JPQL or SQL?  Or both?
body: <p>How can I prevent TopLink from duplicating existing data?  Is it even a good idea to do that with Java code, or should that be built into the database and let inserts fail?</p>
  
  <p>In this case, I want to ensure that <code>newsgroups.newsgroup</code> is unique and that <code>articles.header_id_string</code> is also unique.</p>
  
  <p>Probably the best approach would be to simply run a query for uniqueness in Java JPQL?</p>
  
  <p>Schema:</p>
  
  <pre><code>mysql&gt; 
  mysql&gt; show tables;
  +---------------------+
  | Tables_in_nntp      |
  +---------------------+
  | articles            |
  | newsgroups          |
  | newsgroups_articles |
  +---------------------+
  3 rows in set (0.01 sec)
  
  mysql&gt; describe newsgroups;
  +-----------+---------+------+-----+---------+----------------+
  | Field     | Type    | Null | Key | Default | Extra          |
  +-----------+---------+------+-----+---------+----------------+
  | id        | int(11) | NO   | PRI | NULL    | auto_increment |
  | newsgroup | text    | NO   |     | NULL    |                |
  +-----------+---------+------+-----+---------+----------------+
  2 rows in set (0.00 sec)
  
  mysql&gt; describe articles;
  +------------------+---------+------+-----+---------+----------------+
  | Field            | Type    | Null | Key | Default | Extra          |
  +------------------+---------+------+-----+---------+----------------+
  | id               | int(11) | NO   | PRI | NULL    | auto_increment |
  | subject          | text    | NO   |     | NULL    |                |
  | content          | text    | NO   |     | NULL    |                |
  | number           | int(11) | NO   |     | NULL    |                |
  | sent             | date    | NO   |     | NULL    |                |
  | header_id_string | text    | NO   |     | NULL    |                |
  +------------------+---------+------+-----+---------+----------------+
  6 rows in set (0.00 sec)
  
  mysql&gt; describe newsgroups_articles;
  +--------------+---------+------+-----+---------+----------------+
  | Field        | Type    | Null | Key | Default | Extra          |
  +--------------+---------+------+-----+---------+----------------+
  | id           | int(11) | NO   | PRI | NULL    | auto_increment |
  | article_id   | int(11) | NO   |     | NULL    |                |
  | newsgroup_id | int(11) | NO   | MUL | NULL    |                |
  +--------------+---------+------+-----+---------+----------------+
  3 rows in set (0.00 sec)
  
  mysql&gt; 
  </code></pre>
  
  <p>The Newsgroup entity:</p>
  
  <pre><code>package net.bounceme.dur.usenet.model;
  
  import java.io.Serializable;
  import javax.mail.Folder;
  import javax.persistence.*;
  import javax.xml.bind.annotation.XmlRootElement;
  
  @Entity
  @Table(name = "newsgroups", catalog = "nntp", schema = "")
  @XmlRootElement
  @NamedQueries({
      @NamedQuery(name = "Newsgroups.findAll", query = "SELECT n FROM Newsgroup n"),
      @NamedQuery(name = "Newsgroups.findById", query = "SELECT n FROM Newsgroup n WHERE n.id = :id")})
  public class Newsgroup implements Serializable {
      private static final long serialVersionUID = 1L;
      @Id
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      @Basic(optional = false)
      @Column(name = "id", nullable = false)
      private Integer id;
      @Basic(optional = false)
      @Lob
      @Column(name = "newsgroup", nullable = false, length = 65535)
      private String newsgroup;
  
      public Newsgroup() {
      }
  
      public Newsgroup(Folder f){
          newsgroup = f.getFullName();
      }
  
      public Newsgroup(Integer id) {
          this.id = id;
      }
  
      public Newsgroup(Integer id, String newsgroup) {
          this.id = id;
          this.newsgroup = newsgroup;
      }
  
      public Integer getId() {
          return id;
      }
  
      public void setId(Integer id) {
          this.id = id;
      }
  
      public String getNewsgroup() {
          return newsgroup;
      }
  
      public void setNewsgroup(String newsgroup) {
          this.newsgroup = newsgroup;
      }
  
      @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 &amp;&amp; other.id != null) || (this.id != null &amp;&amp; !this.id.equals(other.id))) {
              return false;
          }
          return true;
      }
  
      @Override
      public String toString() {
          return "net.bounceme.dur.usenet.model.Newsgroups[ id=" + id + " ]";
      }
  
  }
  </code></pre>
  
  <p>And the Article entity:</p>
  
  <pre><code>package net.bounceme.dur.usenet.model;
  
  import java.io.Serializable;
  import java.util.Collection;
  import java.util.Date;
  import javax.persistence.*;
  import javax.xml.bind.annotation.XmlRootElement;
  import javax.xml.bind.annotation.XmlTransient;
  import net.bounceme.dur.usenet.controller.MessageBean;
  
  @Entity
  @Table(name = "articles", catalog = "nntp", schema = "")
  @XmlRootElement
  @NamedQueries({
      @NamedQuery(name = "Articles.findAll", query = "SELECT a FROM Article a"),
      @NamedQuery(name = "Articles.findById", query = "SELECT a FROM Article a WHERE a.id = :id"),
      @NamedQuery(name = "Articles.findByNumber", query = "SELECT a FROM Article a WHERE a.number = :number"),
      @NamedQuery(name = "Articles.findBySent", query = "SELECT a FROM Article a WHERE a.sent = :sent")})
  public class Article implements Serializable {
  
      private static final long serialVersionUID = 1L;
      @Id
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      @Basic(optional = false)
      @Column(name = "id", nullable = false)
      private Integer id;
      @Basic(optional = false)
      @Lob
      @Column(name = "subject", nullable = false, length = 65535)
      private String subject;
      @Basic(optional = false)
      @Lob
      @Column(name = "content", nullable = false, length = 65535)
      private String content;
      @Basic(optional = false)
      @Column(name = "number", nullable = false)
      private int number;
      @Basic(optional = false)
      @Column(name = "sent", nullable = false)
      @Temporal(TemporalType.DATE)
      private Date sent;
      @Basic(optional = false)
      @Lob
      @Column(name = "header_id_string", nullable = false, length = 65535)
      private String headerIdString;
      @OneToMany(cascade = CascadeType.ALL, mappedBy = "newsgroupId")
      private Collection&lt;NewsgroupsArticles&gt; newsgroupsArticlesCollection;
  
      public Article() {
      }
  
      public Article(MessageBean messageBean) {
          subject = messageBean.getSubject();
          content = messageBean.getContent();
          sent = messageBean.getSent();
          number = messageBean.getNumber();
          headerIdString = "dummy";
      }
  
      public Article(Integer id) {
          this.id = id;
      }
  
      public Article(Integer id, String subject, String content, int number, Date sent, String headerIdString) {
          this.id = id;
          this.subject = subject;
          this.content = content;
          this.number = number;
          this.sent = sent;
          this.headerIdString = headerIdString;
      }
  
      public Integer getId() {
          return id;
      }
  
      public void setId(Integer id) {
          this.id = id;
      }
  
      public String getSubject() {
          return subject;
      }
  
      public void setSubject(String subject) {
          this.subject = subject;
      }
  
      public String getContent() {
          return content;
      }
  
      public void setContent(String content) {
          this.content = content;
      }
  
      public int getNumber() {
          return number;
      }
  
      public void setNumber(int number) {
          this.number = number;
      }
  
      public Date getSent() {
          return sent;
      }
  
      public void setSent(Date sent) {
          this.sent = sent;
      }
  
      public String getHeaderIdString() {
          return headerIdString;
      }
  
      public void setHeaderIdString(String headerIdString) {
          this.headerIdString = headerIdString;
      }
  
      @XmlTransient
      public Collection&lt;NewsgroupsArticles&gt; getNewsgroupsArticlesCollection() {
          return newsgroupsArticlesCollection;
      }
  
      public void setNewsgroupsArticlesCollection(Collection&lt;NewsgroupsArticles&gt; newsgroupsArticlesCollection) {
          this.newsgroupsArticlesCollection = newsgroupsArticlesCollection;
      }
  
      @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 &amp;&amp; other.id != null) || (this.id != null &amp;&amp; !this.id.equals(other.id))) {
              return false;
          }
          return true;
      }
  
      @Override
      public String toString() {
          return "net.bounceme.dur.usenet.model.Articles[ id=" + id + " ]";
      }
  }
  </code></pre>