代码之家 › 专栏 › 技术社区 › Achaius

哈希标签的最佳JPA实体模型

jpa hibernate mysql java

Achaius · 技术社区 · 6 年前

我需要在我的应用程序中为散列标签设计一个JPA实体模型,类似于Instagram、Twitter和堆栈溢出。我的申请。以下是我们的应用程序使用的特定于模式的点

哈希标记应属于问题
hashtag是用户特定的。
每个用户可以用相同的hashtag标记他们的问题

目前,我有两个以上模式的JPA模型

适用于两种型号的通用表

表:问题

列:ID、问题、描述等,

表:用户

列:ID、名称、角色、组等,

型号1

表:问题标签

列:id,question_id,user_id,hashtag_text

型号2

表:hashtags

列:id,hastag_文本

表:用户标签

列:user_id、hashtag_id、question_id

即使用户之间的hashtag相同,模型1也将拥有每一行。

模型2将具有唯一的hashtag行,并且使用user_hashtags在用户之间引用它。

我期待一个更好的和标准的模型超过这两个。

注意:可以根据哈希标签和用户搜索问题。

2 回复 | 直到 6 年前

Gerry Mantha 6 年前

Cepr0 6 年前

questions

create table questions (
    id bigint not null constraint questions_pkey primary key,
    user_id bigint constraint fk_questions_users references users,
    question text not null;
)

questions_hashtags hashtag

create table questions_hashtags (
    question_id bigint not null fk_questions_hashtags_questions references questions,
    hashtag text not null,
    constraint uk_questions_hashtags unique (question_id, hashtag)
);

create index index_questions_hashtags_hashtag on questions_hashtags(hashtag);

User

@Entity
@Table(name = "questions")
public class Question {
    @Id
    @GeneratedValue
    private Long id;

    @Column(nullable = false)
    private String question;

    @ManyToOne(optional = false)
    private User user;

    @CollectionTable(name = "questions_hashtags", joinColumns = @JoinColumn(name = "question_id"))
    @Column(name = "hashtag")
    @ElementCollection(fetch = FetchType.EAGER)
    @BatchSize(size = 20)
    private Set<String> hashtags = new HashSet<>();

    public Question(User user, String question) {
        this.user = user;
        this.question = question;
    }

    private Set<String> extractHashtags() {
      // extract hashtags from question to Set...
    }

    @PrePersist
    @PreUpdate
    private void populateHashtags() {
        hashtags.clear();
        hashtags.addAll(extractHashtags());
    }

    // other stuff
}

questionRepo.save(new Question(user, question));

questionRepo

@Query("select distinct h as hashtag from Question q join q.hashtags h")
List<String> getAllHashtags();

@Query("select q from Question q join q.hashtags h where h = ?1")
List<Question> getQuestionsByHashtag(String hashtag);

@Query("select q from Question q join q.hashtags h where h in (?1)")
List<Question> getQuestionsByHashtag(Set<String> hashtags);

@Query("select distinct u from Question q join q.user u join q.hashtags h where h in (?1)")
List<User> getUsersByHashtag(Set<String> hashtags);

sb-hashtag-usage-example

POST /users

{
    "name": "user1"
}

POST /questions

{
    "question": "How implement best JPA #entity #model for Hashtags?",
    "user": "/user/1"
}

GET /hashtags

GET/questions/search/by_hashtag ?hashtag=%23model

GET /questions/search/by_hashtags ?hashtags=%23entity,%23model

GET /users/search/by_hashtags ?hashtags=%23entity