Duplicate entry for unique key in Database

Question

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();
}

same error `java.sql.SQLIntegrityConstraintViolationException: Duplicate entry..` what i dont get is, all i want to only update the fields and set it back.. but it doesnt allows me. it is hell frustrating to get stuck in such problems. i lost almost 2-3 hours now — Radheya, Mar 29 '19 at 22:44

score 1 · Accepted Answer · answered Mar 30 '19 at 02:21

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.

your method works like a charm. i tried it and now no more errors. I get it now, so you need to get the data from database first and then do save,update operations. — Radheya, Mar 30 '19 at 10:48
You retrieve a hibernate managed entity, which you can think of as the record in the database, in which you perform operations on.. however, I recommend you spend some time reading. — lzagkaretos, Mar 30 '19 at 12:14
okay. yes i am doing some reading and also watching video to understand same. thanks a ton! — Radheya, Mar 30 '19 at 12:27

Duplicate entry for unique key in Database

1 Answers1