I have this database structure. There are users who read books every year and compile lists of books that they've read:
users years books----booklists
| | |
------------------------------------
|
userlists
I've tried to do some mappings myself, but I don't think I'm correct.
users:
+-----------+---------+
| id | name |
+-----------+---------+
@Entity
public class User {
@Id @GeneratedValue
private int id;
//?
private List<UserList> booklists = new ArrayList<>();
}
Each user has a list of the books they've read in a certain year:
books:
+-----------+---------+
| id | title |
+-----------+---------+
@Entity
public class Book {
@Id @GeneratedValue
private int id;
}
the lists for every user are inside the booklists table:
booklists:
+-----+---------+---------+
| id | list_id | book_id |
+-----+---------+---------+
@Entity
public class BookList {
@Column(name="list_id")
private int id;
@JoinTable(
name = "books",
joinColumns = @JoinColumn(
name = "list_id",
referencedColumnName = "list_id"
),
inverseJoinColumns = @JoinColumn(
name = "book_id",
referencedColumnName = "id"
)
)
@OneToMany
private Collection<Book> books;
}
finally the booklists and users are united inside userlists:
years:
+----+------+
| id | year |
+----+------+
userlists:
+-----+---------+---------+---------+
| id | user_id | list_id | year_id |
+-----+---------+---------+---------+
@Entity
public class UserList {
@ManyToOne
@JoinColumn(name = "user_id")
private UserDao user;
@JoinTable(
name = "userlists",
joinColumns = @JoinColumn(
name = "id",
referencedColumnName = "id"
),
inverseJoinColumns = @JoinColumn(
name = "year_id",
referencedColumnName = "id"
)
)
@OneToOne
private String year;
//?
private BookList bookList;
}
I'm not sure how to get the private List<UserList> booklists = new ArrayList<>(); inside User. I know that one-to-many could be mapped like this:
@OneToMany
@JoinTable(joinColumns=@JoinColumn(name="user_id"),
inverseJoinColumns=@JoinColumn(name="list_id")
List<UserList> booklists = new ArrayList<>();
But my situation has more tables than any tutorials I've seen.
EDIT: It was pointed out to me, that it's ok to get rid of the userlists table and place information about user_id and year_id inside booklists. I'm still not entirely sure how to map it properly, so any help is appreciated.
Well, this particular problem is somewhat unique in that, for the tables you have defined, you have a list_id but no entity for it. The list_id is just a sort of sub key or something that identifies a list. I would question the normalization of the tables as you mentioned in the EDIT above, but first things first.
The essential entities are the User, the Book, and the Year entities:
@Entity
@Table(name = "users")
@Data
public class User {
@Id
private int id;
private String name;
}
@Entity
@Table(name = "books")
@Data
public class Book {
@Id
private int id;
private String title;
}
@Entity
@Table(name = "years")
@Data
public class Year {
@Id
private int id;
private int year;
}
As you have described it you can create a ListId, Booklist, and Userlist set of relationship tables list so:
@MappedSuperclass
@Data
@EqualsAndHashCode(of = "id")
public class ListId {
@Column(name="list_id")
private int listId;
@Id
private int id;
}
@Entity
@Table(name = "booklists")
@Data
@EqualsAndHashCode(callSuper = true)
@ToString(callSuper = true)
public class Booklist extends ListId {
@ManyToOne
private Book book;
}
@Entity
@Table(name = "userlists")
@Data
@EqualsAndHashCode(callSuper = true)
@ToString(callSuper = true)
public class Userlist extends ListId {
@ManyToOne
private User user;
@ManyToOne
private Year year;
}
There are definitely other ways to do this, but none will solve the problem of not having a real ListId entity to do JPA relationships on. However, you can use this setup like so:
List<Booklist> bls = em.createQuery("select bl from Booklist bl left outer join Userlist ul on ul.listId = bl.listId where ul.user = :user", Booklist.class)
.setParameter("user", u1)
.getResultList();
bls.forEach(System.out::println);
This requires a somewhat newer version of JPA, perhaps 2.2 level. At least it requires Hibernate 5.1 or greater as the implementation. If you want to add a bidirectional mapping to User for this schema, then:
@OneToMany(mappedBy = "user")
Set<Userlist> booklists;
And use it like so.
List<Booklist> bls3 = em.createQuery("select bl from User u left outer join u.booklists ul left outer join Booklist bl on ul.listId = bl.listId where u = :user and ul.year = :year", Booklist.class)
.setParameter("user", u1)
.setParameter("year", y1)
.getResultList();
bls3.forEach(System.out::println);
Assuming that you have some leeway in designing the schema the way you want to for the job, then yes, you should change it.
@Entity
@Table(name = "booklists")
@Data
@ToString(exclude = "user")
public class Booklist {
@Id
private int id;
@ManyToOne
private Book book;
@ManyToOne
private User user;
@ManyToOne
private Year year;
}
Using it is much easier and wouldn't require the non-related entities join from JPA 2.2 or an unmapped ListId class.
List<Booklist> bls2 = em.createQuery("select bl from Booklist bl where bl.user = :user", Booklist.class)
.setParameter("user", u2)
.getResultList();
bls2.forEach(System.out::println);
You can add a bidirectional mapping to User.
@OneToMany(mappedBy = "user")
Set<Booklist> booklists;
And use it like so:
List<Booklist> bls3 = em.createQuery("select bl from User u left outer join u.booklists bl where u = :user and bl.year = :year", Booklist.class)
.setParameter("user", u1)
.setParameter("year", y1)
.getResultList();
bls3.forEach(System.out::println);
This later schema gives you the same results as the former. If it wasn't so late I'd try to figure out which of the first 3 levels of normalization your first schema was violating but perhaps that's an exercise best left to the reader.