Requirement: How to get all Book objects who contains Publisher > Name contains "Pub2","Pub3" in publisher object.
Expected Output:
{
"resources": [
{
"book_id": "1",
"publishers": [
{
"pub_id": "1",
"name": "pub1",
"publishedDate": "2021-06-06T14:42:30.754+00:00"
},
{
"pub_id": "2",
"name": "pub2",
"publishedDate": "2021-06-06T14:42:30.754+00:00"
}
]
},
{
"book_id": "2",
"publishers": [
{
"pub_id": "1",
"name": "Pub1",
"publishedDate": "2021-06-06T14:42:30.754+00:00"
},
{
"pub_id": "2",
"name": "pub2",
"publishedDate": "2021-06-06T14:42:30.754+00:00"
}
]
},
{
"book_id": "3",
"publishers": [
{
"pub_id": "3",
"name": "Pub3",
"publishedDate": "2021-06-06T14:42:30.754+00:00"
}
]
}
]
}
Relationship Used: ManytoMany with Extra Column added in Third Table which contains @EmbeddedId
Entity Objects:
@Entity
@NoArgsConstructor
@JsonIdentityInfo(generator = ObjectIdGenerators.PropertyGenerator.class, property = "id")
public class Book {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String name;
@OneToMany(mappedBy = "book")
private Set<BookPublisher> bookPublishers = new HashSet<>();
public Book(String name) {
this.name = name;
}
}
@Entity
@NoArgsConstructor
@JsonIdentityInfo(generator = ObjectIdGenerators.PropertyGenerator.class, property = "id")
public class Publisher {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String name;
@OneToMany(mappedBy = "publisher")
private Set<BookPublisher> bookPublishers = new HashSet<>();
public Publisher(String name) {
this.name = name;
}
}
@Getter
@Setter
@NoArgsConstructor
@Entity
@Table(name = "book_publisher")
public class BookPublisher {
@EmbeddedId
private BookPublisherId id;
@ManyToOne
@MapsId("bookId")
@JoinColumn(name = "book_id")
private Book book;
@ManyToOne
@MapsId("publisherId")
@JoinColumn(name = "publisher_id")
private Publisher publisher;
@Column(name = "published_date")
private Date publishedDate;
public BookPublisher(Book book, Publisher publisher, Date publishedDate) {
this.id = new BookPublisherId(book.getId(), publisher.getId());
this.book = book;
this.publisher = publisher;
this.publishedDate = publishedDate;
}
}
@Data
@AllArgsConstructor
@NoArgsConstructor
@Embeddable
public class BookPublisherId implements Serializable {
@Column(name = "book_id")
private Integer bookId;
@Column(name = "publisher_id")
private Integer publisherId;
}
Solutions tried so far: Solution: 1
public interface BookPublisherRepository extends JpaRepository<BookPublisher, BookPublisherId> {
List<BookPublisher> findAllBookByPublisher_NameIn(List<String> s);
}
Actual Output:
[
{
"id": {
"bookId": 1,
"publisherId": 2
},
"book": {
"id": 1,
"name": "Book1",
"bookPublishers": [
{
"id": {
"bookId": 1,
"publisherId": 2
},
"book": 1,
"publisher": {
"id": 2,
"name": "Pub2",
"bookPublishers": [
{
"id": {
"bookId": 1,
"publisherId": 2
},
"book": 1,
"publisher": 2,
"publishedDate": "2021-06-06T14:42:30.754+00:00"
},
{
"id": {
"bookId": 2,
"publisherId": 2
},
"book": {
"id": 2,
"name": "Book2",
"bookPublishers": [
{
"id": {
"bookId": 2,
"publisherId": 1
},
"book": 2,
"publisher": {
"id": 1,
"name": "Pub1",
"bookPublishers": [
{
"id": {
"bookId": 2,
"publisherId": 1
},
"book": 2,
"publisher": 1,
"publishedDate": "2021-06-06T14:42:30.754+00:00"
},
{
"id": {
"bookId": 1,
"publisherId": 1
},
"book": 1,
"publisher": 1,
"publishedDate": "2021-06-06T14:42:30.754+00:00"
}
]
},
"publishedDate": "2021-06-06T14:42:30.754+00:00"
},
{
"id": {
"bookId": 2,
"publisherId": 2
},
"book": 2,
"publisher": 2,
"publishedDate": "2021-06-06T14:42:30.754+00:00"
}
]
},
"publisher": 2,
"publishedDate": "2021-06-06T14:42:30.754+00:00"
}
]
},
"publishedDate": "2021-06-06T14:42:30.754+00:00"
},
{
"id": {
"bookId": 1,
"publisherId": 1
},
"book": 1,
"publisher": 1,
"publishedDate": "2021-06-06T14:42:30.754+00:00"
}
]
},
"publisher": 2,
"publishedDate": "2021-06-06T14:42:30.754+00:00"
},
{
"id": {
"bookId": 2,
"publisherId": 2
},
"book": 2,
"publisher": 2,
"publishedDate": "2021-06-06T14:42:30.754+00:00"
},
{
"id": {
"bookId": 3,
"publisherId": 3
},
"book": {
"id": 3,
"name": "Book3",
"bookPublishers": [
{
"id": {
"bookId": 3,
"publisherId": 3
},
"book": 3,
"publisher": {
"id": 3,
"name": "Pub3",
"bookPublishers": [
{
"id": {
"bookId": 3,
"publisherId": 3
},
"book": 3,
"publisher": 3,
"publishedDate": "2021-06-06T14:42:30.754+00:00"
}
]
},
"publishedDate": "2021-06-06T14:42:30.754+00:00"
}
]
},
"publisher": 3,
"publishedDate": "2021-06-06T14:42:30.754+00:00"
}
]
Actual Data:
select * from book;
select * from publisher;
select * from book_publisher;
id name
1 Book1
2 Book2
3 Book3
id name
1 Pub1
2 Pub2
3 Pub3
book_id publisher_id published_date
1 1 2021-06-06 20:12:30.7540000
1 2 2021-06-06 20:12:30.7540000
2 1 2021-06-06 20:12:30.7540000
2 2 2021-06-06 20:12:30.7540000
3 3 2021-06-06 20:12:30.7540000
Queries:
- I see 3 objects which is BookPublisher which somehow matches outer layer of expected results, but 2nd array book2 object not available. i see only bookid, Why?
- How to hide all the id's and nested mappings, i only need Book->Set of Publishers like expected results?
- Am i querying right repository class which is BookPublisherRepository?
Highly appreciated your help on this issue investigation and the proposed solution in advance.