2

my question is exactly the same as this in hibernate but minus the ordering.

I have two tables, Person and Fruit

@Entity
@Table(name = "person")
public class Person {
    @Id
    @Setter
    @Getter
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    private int id;

    @Getter
    @Setter
    private String name;

    @Getter
    @Setter
    @Column(name = "fruit_ids")
    private Integer[] fruitIds;
}                                                             

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

and fruit

@Entity
@Table(name = “fruit”)
public class Fruit {
    @Id
    @Setter
    @Getter
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    private int id;

    @Getter
    @Setter
    private String name;
}

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

What I want to approach is something like select p from person p join fruit f on f.id = any(p.fruit_ids) where p.id = 5;

How can I do it in Hibernate? I currently have 

@Getter
@Setter
@OneToMany
@JoinColumn(name = "fruit_ids", referencedColumnName = "id")
private List<Fruit> Fruits= new ArrayList<>();

but it gives me error. Please note that fruit_ids in person table is an array of integer. This is not the old school one to many relationship in mysql.

user1865027
  • 3,505
  • 6
  • 33
  • 71
  • Why are you using `integer[]`? It doesn't play well with Hibernate. – Kayaman Aug 31 '17 at 19:38
  • I don't want to. I would like to convert it to a collection of Fruit, but `@JoinColumn` doesn't work since fruit_ids column is integer[] not a single int – user1865027 Aug 31 '17 at 20:10
  • Yes, which is why I asked you why are you using `integer[]`. Arrays aren't very relational, so Hibernate won't understand how to handle those. Convert that to a normal link table instead. – Kayaman Sep 01 '17 at 06:13
  • but being able to store array is a big advantage in postgres. I want to keep it if possible. `select * from person join fruit on fruit.id = any(person.fruit_ids)` would do the trick but I don't know how to make it happen in hibernate – user1865027 Sep 01 '17 at 06:24
  • It's also a disadvantage. Hibernate doesn't understand arrays. You can't even select an array, even if using a native query. There are certain situations where arrays are useful, but they're fewer than you think. – Kayaman Sep 01 '17 at 06:37
  • some guy did a benchmark [here](https://github.com/bookshelf/bookshelf/issues/363#issuecomment-218217286) which is why I wish to keep this feature – user1865027 Sep 01 '17 at 06:37
  • arrr...ok. that means I can never get it to work. ok thank you so much – user1865027 Sep 01 '17 at 06:38
  • I wouldn't trust "some guy's" comment right away. If you don't understand the principles of RDBMS, you're going to be doing a lot of stupid things because some guy commented somewhere. Wishing to use arrays because an unreliable guy's unreliable benchmark said that "it's 5 times faster" is a really really really bad idea. – Kayaman Sep 01 '17 at 06:40
  • I didn't convert all my tables to use array. only on the ones that I think might be benefited even if I need to sacrifice foreign constraints. – user1865027 Sep 01 '17 at 06:52
  • but if its not possible in hibernate, I think I'll convert them back to the real relations – user1865027 Sep 01 '17 at 06:52
  • I wouldn't recommend that. Do you have a performance issue? Is the last thing you can think of using arrays? Are you well experienced with Postgres (and its internal workings and configurations)? If the answer is no to any one of those, you're basically thinking "Hey cool, apparently arrays are fast and cool. I'm going to use those without really understanding what I'm doing". You might want to do some reading around, see how Postgres' advanced datatypes (arrays, `json`, `jsonb`, `hstore`, etc.) work and **where** they are useful. Don't do things blindly. – Kayaman Sep 01 '17 at 06:54

0 Answers0