CODEWITHSUNDEEP
hibernatespring-boot
Radheya
Radheya

Reputation: 821

Duplicate entry for unique key in Database

I am new to Hibernate and gave up trying to update/merge an entry in the mysql database table because of the problem i've ran into. I have a unique constraint on a column in the table and I want to update it after changing some values in the database using hibernate but it never works:

This is my java class:

package com.weatherapi.test.weather_api.model;

import lombok.Data;

import javax.persistence.*;

@Entity
@Table(name="weather", uniqueConstraints = @UniqueConstraint(columnNames = {"city"}))
@Data
public class Weather {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private Long id;

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

    private String headline;

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

    @Transient
    private String wind;

    @Transient
    private String icon;

    private String currentTemp;
    private String minTemp;
    private String maxTemp;
    private String sunrise;
    private String sunset;

    @Transient
    private String unit;
}

and I am using session.merge(weatherToUpdate); to merge it back to the database after changing some values. but merge fails and even saveOrUpdate fails.

My database already has a city London entry into it which i want to extract from the table, merge some changes and upload it back to database.

Here is the error that i get:

java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'London' for key 'UK_835v8un5oinaj7vdsvc9ym7ub'
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117) ~[mysql-connector-java-8.0.15.jar:8.0.15]
    at

I tried using EntityManager to solve it using persist method, but i simply cannot install it in my setup. I tried different ways to update session but nothing works. i feel completely lost.

EDIT:

This is how i am trying to save: 

public void updateCityFromDatabase(Weather weatherToUpdate,String city){
    // Create Session
    Session session = factory.getCurrentSession();
    // Begin Transaction
    session.getTransaction().begin();
    try{
    LOGGER.info("reading the weather data...");
    Query query = session.createQuery("from Weather wt where wt.city = :city");
    query.setParameter("city",weatherToUpdate.getCity());
    List<Weather> retrievedWeatherData = query.list();
    if(retrievedWeatherData.get(0).getCity().equals(weatherToUpdate.getCity())){
        LOGGER.info("City already exists and parameters are same");
        session.save(weatherToUpdate);
    }else{
        session.clear();
    }
    session.getTransaction().commit();
    LOGGER.info("City parameters Updated!");
    factory.close();
}

Upvotes: 0

Views: 1891

Answers (1)

lzagkaretos
lzagkaretos

Reputation: 2910

First of all, you have to understand the way managing entity instances work in Hibernate. You can start by reading a guide here.

In your example, you need to pass retrievedWeatherData.get(0) object as an argument to merge() function after you copy the properties from weatherToUpdate entity.

Copy can be done either manually or using a helper utility function as BeanUtils.copyProperties() from Apache Commons library (see an example).

So your code becomes:

List<Weather> retrievedWeatherData = query.list();
Weather firstRetrievedWeatherData = retrievedWeatherData.get(0);

if (firstRetrievedWeatherData.getCity().equals(weatherToUpdate.getCity())) {

    // Manually Copy properties values from weatherToUpdate to retrieved object
    // Property value for id is excluded
    firstRetrievedWeatherData.setCity(weatherToUpdate.getCity());
    firstRetrievedWeatherData.setHeadline(weatherToUpdate.getHeadline());
    // Copy more other properties

    // Generate an update sql statement instead of insert
    session.merge(firstRetrievedWeatherData);
}

Finally, I would recommend the usage of Spring Data for managing hibernate entities, if using Spring, or Spring boot is an option for you. It is not difficult at all, you can stop worrying about managing DB transactions (start, commit, rollback) and also you are provided with a handful set of features.

Upvotes: 1

Related Questions