mysql - 使用 JPQL 或 SQL 确保唯一性(不同记录)？或两者？

Question

如何防止 TopLink 复制现有数据？使用 Java 代码来做到这一点是个好主意，还是应该将其内置到数据库中并让插入失败？

在本例中，我想确保 newsgroups.newsgroup 是唯一的，并且 articles.header_id_string 也是唯一的。

也许最好的方法是简单地运行 Java JPQL 中的唯一性查询？

架构:

mysql> 
mysql> show tables;
+---------------------+
| Tables_in_nntp      |
+---------------------+
| articles            |
| newsgroups          |
| newsgroups_articles |
+---------------------+
3 rows in set (0.01 sec)

mysql> 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> 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> 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> 

新闻组实体:

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 && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "net.bounceme.dur.usenet.model.Newsgroups[ id=" + id + " ]";
    }

}

以及文章实体:

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<NewsgroupsArticles> 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<NewsgroupsArticles> getNewsgroupsArticlesCollection() {
        return newsgroupsArticlesCollection;
    }

    public void setNewsgroupsArticlesCollection(Collection<NewsgroupsArticles> 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 && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "net.bounceme.dur.usenet.model.Articles[ id=" + id + " ]";
    }
}

Answer 1

should that be built into the database and let inserts fail?

当然，将其放入数据库中。

这是你的“最后一道防线”，几乎没有办法绕过它。如果您只有 Java 代码，则无法防止错误的 SQL 脚本或编程错误。

您仍然可以检查您的代码，例如向用户显示更好的错误消息。