hibernate avoid duplicates in database

Question

How to avoid duplicates in the database.

'1', 'Male'
'2', 'Male'
'3', 'Female'

to only save

'1', 'Male'
'2', 'Female'

or:

to only save

Countries

    '1', 'UK'
    '2', 'Brazil'
    '3', 'China'

My database schema:

CREATE TABLE IF NOT EXISTS `bi_person` (
        `id_person` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `name` VARCHAR(50) NOT NULL,
        `last_name` VARCHAR(100) NOT NULL,
         `additional_info` VARCHAR(50) NOT NULL,
        `gender_id` VARCHAR (50) UNIQUE REFERENCES bi_gender
    ) COLLATE='utf8_bin';

-- Table 'bi.gender`

CREATE TABLE IF NOT EXISTS `bi_gender` (
        `id_gender` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `name`  VARCHAR(10) NOT NULL  
    ) COLLATE='utf8_bin';

-- Table 'bi.country`

CREATE TABLE IF NOT EXISTS `bi_country` (
        `id_country` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `name`  VARCHAR(10) NOT NULL 
    ) COLLATE='utf8_bin';

I have models with relation many to many - person countries and one to many person-->gender

Model:

public class Country {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="id_book")
    private Integer id;

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

    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(name="bi_person_country", joinColumns=@JoinColumn(name="id_country"), inverseJoinColumns=@JoinColumn(name="id_person"))
    private Set<Person> persons;

Model gender:

@Table(name = "bi_gender")
@Entity
public class Gender {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

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

Person entity:

public class Person {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="id_person")
    private Integer id;

    @Column(name = "name")
    private String name;
    @Column(name = "last_name")
    private String lastName;
    @Column(name = "additional_info")
    private String additionalInfo;

    @ManyToMany(cascade = CascadeType.ALL, mappedBy = "persons")
    private Set<Country> countries;

Adding person object:

Person person = Person();

            Gender gender = new Gender();

            gender.setGenderName(pepPersons.get(a).getGender());

            gender = genderRepository.save(gender);

            Country country = new Country();

            country.setCountryName(pepPersons.get(a).getCountry());

            country = countryRepository.save(country);

            person.setName(pepPersons.get(a).getFirstName());
            person.setLastName(pepPersons.get(a).getLastName());
            person.setAdditionalInfo(pepPersons.get(0).getFunction());
            person.setGender(gender);

            Set<Country> countries = new HashSet();
            countries.add(country);
            person.setCountries(countries);

            personRepository.save(person);

the country and gender tables are master data, containing unique countries and genders respectively, that is why you do not want to add entries to it? — Rohit
– Rohit, Commented Aug 13, 2018 at 9:16

Vlad Bochenin · Accepted Answer · 2018-08-13 09:10:23Z

2

I would recommend to use unique index to prevent a duplicates in DB table.

In MySQL it could be done with statement:

CREATE UNIQUE INDEX ux_bi_gender_name
ON bi_gender(name);

It would guarantee uniqueness on DB Level.

Regarding Hibernate annotations:

As far as I know Hibernate doesn't make any check for uniqueness.

Based on Hibernate JavaDoc for @Column

This is a shortcut for the UniqueConstraint annotation at the table level and is useful for when the unique key constraint corresponds to only a single column. This constraint applies in addition to any constraint entailed by primary key mapping and to constraints specified at the table level.

and @UniqueConstraint

Specifies that a unique constraint is to be included in the generated DDL for a primary or secondary table.

So it is helpful if you are generating DB Schema with Hibernate as well as for documentation purposes.

answered Aug 13, 2018 at 9:10

Vlad Bochenin

3,0821 gold badge23 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vlad Bochenin Over a year ago

@user9381327 on table where you want to have unique column. in my example, you will have unique values in column name in table bi_gender

Mohamed Nabli · Accepted Answer · 2018-08-13 08:45:40Z

1

You will have to declare that column as unique : @Column(unique = true)

answered Aug 13, 2018 at 8:45

Mohamed Nabli

1,6493 gold badges17 silver badges24 bronze badges

Comments

Rohit · Accepted Answer · 2018-08-13 11:31:26Z

1

If I understand you correctly, the country and gender tables are master data, containing unique countries and genders respectively.

In that case you do not need cascade, in application fetch the required gender set it on person and persist person. remove the cascade = CascadeType.ALL

update

Gender male = genderRepository.findByName("Male");
person.setGender(male);
...
personRepository.save(person)

edited Aug 13, 2018 at 11:31

answered Aug 13, 2018 at 9:19

Rohit

2,1621 gold badge15 silver badges24 bronze badges

5 Comments

user9381327 Over a year ago

i deleted cascade = CascadeType.ALL, but nothing changed

Rohit Over a year ago

Need to see your code where you create and persist Person.

user9381327 Over a year ago

I added persist Person

Rohit Over a year ago

You do not need to explicitly persist gender and country every time you persist Person. Find the required gender from db, set it on Person you are about to persist and then persist it.

user9381327 Over a year ago

how it should looks like?

Ashok Kumar N · Accepted Answer · 2018-08-13 09:03:26Z

0

@Column(name = "name",unique = true, nullable=false) 
private String name;

use this in entity to avoid the duplicate name

edited Aug 13, 2018 at 9:03

answered Aug 13, 2018 at 8:51

Ashok Kumar N

5736 silver badges26 bronze badges

10 Comments

user9381327 Over a year ago

I added @Column(unique = true) for column name in gender and countries entity., but nothing changed.

Ashok Kumar N Over a year ago

please allow you ORM to create the table, drop and check once. because this constrain will work during ddl time not in rum time

user9381327 Over a year ago

I droped tables, added @Column(name = "name"), but still '1', 'Male' '2', 'Male' '3', 'Male' '4', 'Male' '5', 'Male'

Ashok Kumar N Over a year ago

@Column(name = "name",unique = true, nullable=false) use this and check again

user9381327 Over a year ago

nothing changed... @Column(name = "name", unique = true, nullable=false) private String name; but still duplicated

|

Collectives™ on Stack Overflow

hibernate avoid duplicates in database

4 Answers 4

1 Comment

Comments

5 Comments

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

5 Comments

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related