How do I insert comma separated value using Hibernate with MySQL?

Question

How do I store integer type list in a comma-separated format in Hibernate?

For example - I have a city table where I have cityId and CityName. In another table I have employee details.An employee can be tagged with multiple city.

I have created another table for employee-city mapping where I want to store the cities in comma separated format.

Table- Employee
----------------------------
Employee_ID   Employee_Name
1             Employee 1
2             Employee 2

Table -city
------------------------
City_ID   City_Name
1         City_1
2         City_2

Table- Employee-City Mapping
-----------------------
Emp_ID  City_id
1        1,2
2        1,2

How to do the this kind actions in Hibernate ? Please help. I am using MySQL as the database backend.

bad design. never store csv data in a relational database, especially when you need to access individual bits of that data. normalize your tables, and your problem goes away. — Marc B
– Marc B, Commented Aug 5, 2015 at 19:07
What do you mean by storing data in comma separated format in a relational database ? — Daniel Jipa
– Daniel Jipa, Commented Aug 5, 2015 at 19:07

Alain O'Dea · Accepted Answer · 2015-08-05 19:21:58Z

It looks like you are trying to represent a one-to-many relationship.

The conventional SQL way to represent a one-to-many relationship is via multiple rows in a join table. In your case the join table is the employee-city table with a modified design with multiple rows per city rather than multi-valued columns:

Table- Employee-City Mapping
-----------------------
Emp_ID  City_id
1        1
1        2
2        1
2        2

JPA (which Hibernate supports) has direct support for mapping these relationships to Collection fields. Your Employee class would have a cities field with type List<City> annotated with @OneToMany.

@Entity
public class Employee {
  @Id
  @Column(name="Emp_ID")
  private long id;

  @OneToMany
  @JoinColumn(name="Emp_ID", referencedColumnName="City_ID")
  private List<City> cities;
  // ... rest of the class: constructor, getters, setters
}

The following guide may also help: https://en.wikibooks.org/wiki/Java_Persistence/OneToMany

Collectives™ on Stack Overflow

How do I insert comma separated value using Hibernate with MySQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related