-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 8 posts ] 
  Previous topic | Next topic 
Author Message
ohpoloha
 Post subject: any way hibernate can deal with array of foreign keys?
PostPosted: Fri Sep 01, 2017 1:43 pm 
Newbie

Joined: Fri Sep 01, 2017 2:40 am
Posts: 17
In postgres, we can use array to replace relation link table. Let's not talk about if this is a good design or not.

I have two tables, bookshelf and book.
Code:
Table "public.bookshelf”
       Column        |              Type              |                     Modifiers                        | Storage  | Stats target |               Description
---------------------+--------------------------------+------------------------------------------------------+----------+--------------+------------------------------------------
id                   | integer                        | not null default nextval(‘bookshelf_seq'::regclass)  | plain    |              |
section              | character varying(255)         |                                                      | extended |              |
book_ids             | integer[]                      |                                                      | extended |              |                     


Table "public.book”
       Column        |              Type              |                     Modifiers                     | Storage  | Stats target |               Description
---------------------+--------------------------------+---------------------------------------------------+----------+--------------+------------------------------------------
id                   | integer                        | not null default nextval(‘book_seq'::regclass)    | plain    |              |
name                 | character varying(255)         |                                                   | extended |              |                                         

book_ids in bookshelf table stores an array of book.id. All I want to perform is
Code:
select * from bookshelf join book on book.id = any(bookshelf.book_ids)
to get a bookshelf entity with a collection of books. Is this possible in hibernate? How can it be done? I've been trying to use @JoinColumn but it doesn't seem to work with array. Native query also can't. Thank you
Top
 Profile  
 
vlad
 Post subject: Re: any way hibernate can deal with array of foreign keys?
PostPosted: Fri Sep 01, 2017 2:35 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
While JPA only defines the @JoinColumn annotation which requires a FK to be mapped to the parent entity PK, Hibernate offers the @JoinFormula annotation for complex mapping, like this one.

In Book entity you could have a @ManyToOne to BookShelf:

Code:
@ManyToOne(fetch = FetchType.LAZY)
@JoinFormula(
   "select bs.id from bookshelf bs where id = any(bs.book_ids)"
)
private BookShelf bookShelf;


While in BookShelf you can have:

Code:
@OneToMany(mappedBy = "bookShelf")
private List<Book> books = new ArrayList<>();


Check out this article for more details about how to use @JoinFormula.
Top
 Profile  
 
ohpoloha
 Post subject: Re: any way hibernate can deal with array of foreign keys?
PostPosted: Fri Sep 01, 2017 4:13 pm 
Newbie

Joined: Fri Sep 01, 2017 2:40 am
Posts: 17
vlad wrote:
While JPA only defines the @JoinColumn annotation which requires a FK to be mapped to the parent entity PK, Hibernate offers the @JoinFormula annotation for complex mapping, like this one.

In Book entity you could have a @ManyToOne to BookShelf:

Code:
@ManyToOne(fetch = FetchType.LAZY)
@JoinFormula(
   "select bs.id from bookshelf bs where id = any(bs.book_ids)"
)
private BookShelf bookShelf;


While in BookShelf you can have:

Code:
@OneToMany(mappedBy = "bookShelf")
private List<Book> books = new ArrayList<>();


Check out this article for more details about how to use @JoinFormula.


Thank you for your help. After hours of study, I still don't quite understand your suggestion.
First of all, you had
Code:
select bs.id from bookshelf bs where id = any(bs.book_ids)

why would selecting id from bookshelf can be mapped to bookshelf entity?
I tried your suggestion and got
Code:
ClassCastException: org.hibernate.mapping.Formula cannot be cast to org.hibernate.mapping.Column

After googling, people suggested to remove @Id from subclass, but isn't every Entity requires an identifier?
Top
 Profile  
 
ohpoloha
 Post subject: Re: any way hibernate can deal with array of foreign keys?
PostPosted: Fri Sep 01, 2017 6:39 pm 
Newbie

Joined: Fri Sep 01, 2017 2:40 am
Posts: 17
ohpoloha wrote:
vlad wrote:
While JPA only defines the @JoinColumn annotation which requires a FK to be mapped to the parent entity PK, Hibernate offers the @JoinFormula annotation for complex mapping, like this one.

In Book entity you could have a @ManyToOne to BookShelf:

Code:
@ManyToOne(fetch = FetchType.LAZY)
@JoinFormula(
   "select bs.id from bookshelf bs where id = any(bs.book_ids)"
)
private BookShelf bookShelf;


While in BookShelf you can have:

Code:
@OneToMany(mappedBy = "bookShelf")
private List<Book> books = new ArrayList<>();


Check out this article for more details about how to use @JoinFormula.


Thank you for your help. After hours of study, I still don't quite understand your suggestion.
First of all, you had
Code:
select bs.id from bookshelf bs where id = any(bs.book_ids)

why would selecting id from bookshelf can be mapped to bookshelf entity?
I tried your suggestion and got
Code:
ClassCastException: org.hibernate.mapping.Formula cannot be cast to org.hibernate.mapping.Column

After googling, people suggested to remove @Id from subclass, but isn't every Entity requires an identifier?



ok, I think I understand the concept now. but still, I don't know why I'm getting
Code:
ClassCastException: org.hibernate.mapping.Formula cannot be cast to org.hibernate.mapping.Column
.
Even if I really take @Id out, I'm getting
Code:
org.hibernate.AnnotationException: No identifier specified
Top
 Profile  
 
vlad
 Post subject: Re: any way hibernate can deal with array of foreign keys?
PostPosted: Sat Sep 02, 2017 1:43 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
It works just fine for me. Check out this test on GitHub. I'm using Hibernate 5.2.10.

Are you sure you added the @JoinFormula to the @MantyToOne? From your error message it looks like you added it to some basic attribute or the id.

Quote:
After googling, people suggested to remove @Id from subclass, but isn't every Entity requires an identifier?


That's wrong. Always question what you read on the Internet.
Top
 Profile  
 
ohpoloha
 Post subject: Re: any way hibernate can deal with array of foreign keys?
PostPosted: Sat Sep 02, 2017 2:47 am 
Newbie

Joined: Fri Sep 01, 2017 2:40 am
Posts: 17
vlad wrote:
It works just fine for me. Check out this test on GitHub. I'm using Hibernate 5.2.10.

Are you sure you added the @JoinFormula to the @MantyToOne? From your error message it looks like you added it to some basic attribute or the id.

Quote:
After googling, people suggested to remove @Id from subclass, but isn't every Entity requires an identifier?


That's wrong. Always question what you read on the Internet.


Yes, I found that example in your blog. That's where helped me understand my question earlier about the query. Thank you so much.
I've posted my Entity classes.
I was using Hibernate 5.0.2 but just upgraded to 5.2.10.Final and still getting this exception.
Code:
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaAutoConfiguration.class]: Invocation of init method failed; nested exception is java.lang.ClassCastException: org.hibernate.mapping.Formula cannot be cast to org.hibernate.mapping.Column

Image
Code:
import lombok.Getter;
import lombok.Setter;

import javax.persistence.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Entity(name = "BookShelf")
@Table(name = "bookshelf")
public class BookShelf {
    @Id
    @Setter
    @Getter
    @Column(name = "id", insertable = false, updatable = false)
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    private Integer id;

    @Getter
    @Setter
    @OneToMany(mappedBy = "bookShelf")
    private List<Book> books = new ArrayList<>();
}


Code:
import lombok.Getter;
import lombok.Setter;
import org.hibernate.annotations.JoinFormula;

import javax.persistence.*;

@Entity(name = "Book")
@Table(name = "book")
public class Book {
    @Id
    @Setter
    @Getter
    @Column(name = "id", insertable = false, updatable = false)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Getter
    @Setter
    @Column(name = "name")
    private String name;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinFormula("select b.id from bookshelf b where id = any(b.book_ids)")
    private BookShelf bookShelf ;
}
Top
 Profile  
 
vlad
 Post subject: Re: any way hibernate can deal with array of foreign keys?
PostPosted: Sat Sep 02, 2017 3:19 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
That must be a Spring Boot issue, not a Hibernate one because the exception is thrown from HibernateJpaAutoConfiguration.

Try the example using our template to see it working with Hibernate, then you can open an issue for Spring Boot.
Top
 Profile  
 
ohpoloha
 Post subject: Re: any way hibernate can deal with array of foreign keys?
PostPosted: Sat Sep 02, 2017 4:04 am 
Newbie

Joined: Fri Sep 01, 2017 2:40 am
Posts: 17
vlad wrote:
That must be a Spring Boot issue, not a Hibernate one because the exception is thrown from HibernateJpaAutoConfiguration.

Try the example using our template to see it working with Hibernate, then you can open an issue for Spring Boot.


arrr my bad. I see it now. Thanks!!
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 8 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.