We have the below entity mapping in our project. When we add a new phone to an existing employee then we see hibernate is generating un-necessary DB update - "update PHONE set PHONE_TYPE_ID=? where PHONE_TYPE_ID=? and EMPLOYEE_ID=?". It updates PHONE_TYPE_ID to itself. This update seems like harmless but it adds additional updates to DB. We are using hibernate 4.3.11 and Oracle DB.
Would like to see if there is any way we can avoid this un-necessary DB update. I think changing
Changing the association from Map to some other collection may resolve the issue but not be an option for us since we would like to use Map.
Any config, mapping, annotation change would be helpful.
{code:java}
@Entity
@Table(name = "EMPLOYEE")
public class Employee implements Serializable
{
@Id
@Column(name = "EMPLOYEE_ID")
private Integer id;
@OneToMany(mappedBy="owner", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
@MapKeyJoinColumn(name = "PHONE_TYPE_ID")
private Map<PhoneType, Phone> phones = new HashMap<PhoneType, Phone>();
@NotNull
@Column(name = "FIRST_NAME")
private String firstName;
@NotNull
@Column(name = "LAST_NAME")
private String lastName;
protected Employee()
{
//for ORM
}
public Employee(Integer id, String firstName, String lastName)
{
this.id = id;
this.firstName = firstName;
this.lastName = lastName;
}
public long getId()
{
return id;
}
public Map<PhoneType, Phone> getPhones()
{
return phones;
}
public String getFirstName()
{
return firstName;
}
public String getLastName()
{
return lastName;
}
public void addPhones(Phone phone)
{
phones.put(phone.getPhoneType(), phone);
}
@Override
public boolean equals(Object o)
{
if (this == o)
{
return true;
}
if (o == null || getClass() != o.getClass())
{
return false;
}
Employee other = (Employee) o;
return new EqualsBuilder()
.append(id, other.id)
.append(firstName, other.firstName)
.append(lastName, other.lastName)
.isEquals();
}
@Override
public int hashCode()
{
return new HashCodeBuilder()
.append(id)
.append(firstName)
.append(lastName)
.toHashCode();
}
@Override
public String toString()
{
return String.format("id[%s], firstName[%s], lastName[%s]", id, firstName, lastName);
}
}
@Entity
@Table(name = "PHONE")
public class Phone implements Serializable
{
@Id
@ManyToOne
@JoinColumn(name = "EMPLOYEE_ID", referencedColumnName = "EMPLOYEE_ID")
@NotNull
private Employee owner;
@Id
@ManyToOne
@JoinColumn(name = "PHONE_TYPE_ID", nullable = false, updatable=false)
@NotNull
@Fetch(FetchMode.SELECT)
private PhoneType phoneType;
@NotNull
@Column(name = "PHONE_NUMBER")
private String phoneNumber;
protected Phone()
{
//for ORM
}
public Phone(Employee owner, PhoneType phoneType, String phoneNumber)
{
this.owner = owner;
this.phoneType = phoneType;
this.phoneNumber = phoneNumber;
}
public Employee getOwner()
{
return owner;
}
public PhoneType getPhoneType()
{
return phoneType;
}
public String getPhoneNumber()
{
return phoneNumber;
}
@Override
public boolean equals(Object o)
{
if (this == o)
{
return true;
}
if (o == null || getClass() != o.getClass())
{
return false;
}
Phone other = (Phone) o;
return new EqualsBuilder()
.append(owner, other.owner)
.append(phoneType, other.phoneType)
.append(phoneNumber, other.phoneNumber)
.isEquals();
}
@Override
public int hashCode()
{
return new HashCodeBuilder()
.append(owner)
.append(phoneType)
.append(phoneNumber)
.toHashCode();
}
@Override
public String toString()
{
return String.format("owner[%s], phoneType[%s], phoneNumber[%s]", owner, phoneType, phoneNumber);
}
}
@Entity
@Table(name = "PHONE_TYPE")
public class PhoneType implements Serializable
{
@Id
@Column(name = "PHONE_TYPE_ID")
private Integer id;
@NotNull
@Column(name = "PHONE_TYPE")
private String phoneType;
protected PhoneType()
{
//for ORM
}
public PhoneType(Integer id, String phoneType)
{
this.id = id;
this.phoneType = phoneType;
}
public long getId() {
return id;
}
public String getPhoneType() {
return phoneType;
}
@Override
public boolean equals(Object o)
{
if (this == o)
{
return true;
}
if (o == null || getClass() != o.getClass())
{
return false;
}
PhoneType other = (PhoneType) o;
return new EqualsBuilder()
.append(id, other.id)
.append(phoneType, other.phoneType)
.isEquals();
}
@Override
public int hashCode()
{
return new HashCodeBuilder()
.append(id)
.append(phoneType)
.toHashCode();
}
@Override
public String toString()
{
return String.format("id[%s], phoneType[%s]", id, phoneType);
}
}
{code}
DB table scripts:
{code:java}
create table EMPLOYEE
(
EMPLOYEE_ID number(10),
FIRST_NAME varchar(50),
LAST_NAME varchar2(50)
);
alter table EMPLOYEE
add constraint EMPLOYEE_PK primary key (EMPLOYEE_ID);
create table PHONE
(
EMPLOYEE_ID number(10),
PHONE_TYPE_ID number(10),
PHONE_NUMBER varchar2(50)
);
alter table PHONE
add constraint PHONE_PK primary key (EMPLOYEE_ID, PHONE_TYPE_ID);
create table PHONE_TYPE
(
PHONE_TYPE_ID number(10),
PHONE_TYPE varchar2(50)
);
alter table PHONE_TYPE
add constraint PHONE_TYPE_PK primary key (PHONE_TYPE_ID);
insert into EMPLOYEE (EMPLOYEE_ID, FIRST_NAME, LAST_NAME)
values(1, 'first-1', 'last-1');
insert into PHONE (EMPLOYEE_ID, PHONE_TYPE_ID, PHONE_NUMBER)
values(1, 10, '123-123-1234');
insert into PHONE_TYPE (PHONE_TYPE_ID, PHONE_TYPE)
values(10, 'Mobile');
insert into PHONE_TYPE (PHONE_TYPE_ID, PHONE_TYPE)
values(11, 'Home');
{code}
Test Class:
{code:java}
@Transactional(timeout = 60)
public class EmployeeTest extends AbstractTransactionalTestNGSpringContextTests
{
@Autowired
EmployeeRepository employeeRepository;
@Autowired
PhoneTypeRepository phoneTypeRepository;
@PersistenceContext(unitName = "base")
private EntityManager entityManager;
@Test
public void addPhoneType()
{
Employee employee = employeeRepository.findById(1);
PhoneType phoneType = phoneTypeRepository.findById(11);
Assert.assertEquals(employee.getPhones().size(), 1);
Phone phone = new Phone(employee, phoneType, "123-456-7890");
employee.addPhones(phone);
employeeRepository.persist(employee);
entityManager.flush();
Assert.assertEquals(employee.getPhones().size(), 2);
}
}
{code}
For the above test case, the employee with id=1 already exists in the DB. This employee has 'Mobile' phone - 123-123-1234. The test is adding a new phone 'Home' phone - 123-456-7890.
The test case succeeds and adds the 'Home' phone to the employee but generates additional SQL update - "update PHONE set PHONE_TYPE_ID=? where PHONE_TYPE_ID=? and EMPLOYEE_ID=?", which updates PHONE_TYPE_ID to itself. Listed blow are the SQLs executed while running the test ( We use P6Spy which shows the SQLs with parameter values) :
{code:java}
Hibernate: select employee0_.EMPLOYEE_ID as EMPLOYEE_ID1_43_0_, employee0_.FIRST_NAME as FIRST_NAME2_43_0_, employee0_.LAST_NAME as LAST_NAME3_43_0_, phones1_.EMPLOYEE_ID as EMPLOYEE_ID3_43_1_, phones1_.PHONE_TYPE_ID as PHONE_TYPE_ID2_65_1_, phones1_.EMPLOYEE_ID as EMPLOYEE_ID3_65_1_, phones1_.PHONE_TYPE_ID as PHONE_TYPE_ID2_1_, phones1_.PHONE_TYPE_ID as PHONE_TYPE_ID2_65_2_, phones1_.EMPLOYEE_ID as EMPLOYEE_ID3_65_2_, phones1_.PHONE_NUMBER as PHONE_NUMBER1_65_2_, phonetype3_.PHONE_TYPE_ID as PHONE_TYPE_ID1_66_4_, phonetype3_.PHONE_TYPE as PHONE_TYPE2_66_4_ from EMPLOYEE employee0_ left outer join PHONE phones1_ on employee0_.EMPLOYEE_ID=phones1_.EMPLOYEE_ID left outer join PHONE_TYPE phonetype2_ on phones1_.PHONE_TYPE_ID=phonetype2_.PHONE_TYPE_ID left outer join PHONE_TYPE phonetype3_ on phones1_.PHONE_TYPE_ID=phonetype3_.PHONE_TYPE_ID where employee0_.EMPLOYEE_ID=?
1457118857445|6|1|statement|select employee0_.EMPLOYEE_ID as EMPLOYEE_ID1_43_0_, employee0_.FIRST_NAME as FIRST_NAME2_43_0_, employee0_.LAST_NAME as LAST_NAME3_43_0_, phones1_.EMPLOYEE_ID as EMPLOYEE_ID3_43_1_, phones1_.PHONE_TYPE_ID as PHONE_TYPE_ID2_65_1_, phones1_.EMPLOYEE_ID as EMPLOYEE_ID3_65_1_, phones1_.PHONE_TYPE_ID as PHONE_TYPE_ID2_1_, phones1_.PHONE_TYPE_ID as PHONE_TYPE_ID2_65_2_, phones1_.EMPLOYEE_ID as EMPLOYEE_ID3_65_2_, phones1_.PHONE_NUMBER as PHONE_NUMBER1_65_2_, phonetype3_.PHONE_TYPE_ID as PHONE_TYPE_ID1_66_4_, phonetype3_.PHONE_TYPE as PHONE_TYPE2_66_4_ from EMPLOYEE employee0_ left outer join PHONE phones1_ on employee0_.EMPLOYEE_ID=phones1_.EMPLOYEE_ID left outer join PHONE_TYPE phonetype2_ on phones1_.PHONE_TYPE_ID=phonetype2_.PHONE_TYPE_ID left outer join PHONE_TYPE phonetype3_ on phones1_.PHONE_TYPE_ID=phonetype3_.PHONE_TYPE_ID where employee0_.EMPLOYEE_ID=?|select employee0_.EMPLOYEE_ID as EMPLOYEE_ID1_43_0_, employee0_.FIRST_NAME as FIRST_NAME2_43_0_, employee0_.LAST_NAME as LAST_NAME3_43_0_, phones1_.EMPLOYEE_ID as EMPLOYEE_ID3_43_1_, phones1_.PHONE_TYPE_ID as PHONE_TYPE_ID2_65_1_, phones1_.EMPLOYEE_ID as EMPLOYEE_ID3_65_1_, phones1_.PHONE_TYPE_ID as PHONE_TYPE_ID2_1_, phones1_.PHONE_TYPE_ID as PHONE_TYPE_ID2_65_2_, phones1_.EMPLOYEE_ID as EMPLOYEE_ID3_65_2_, phones1_.PHONE_NUMBER as PHONE_NUMBER1_65_2_, phonetype3_.PHONE_TYPE_ID as PHONE_TYPE_ID1_66_4_, phonetype3_.PHONE_TYPE as PHONE_TYPE2_66_4_ from EMPLOYEE employee0_ left outer join PHONE phones1_ on employee0_.EMPLOYEE_ID=phones1_.EMPLOYEE_ID left outer join PHONE_TYPE phonetype2_ on phones1_.PHONE_TYPE_ID=phonetype2_.PHONE_TYPE_ID left outer join PHONE_TYPE phonetype3_ on phones1_.PHONE_TYPE_ID=phonetype3_.PHONE_TYPE_ID where employee0_.EMPLOYEE_ID=1
1457118857462|-1||resultset|select employee0_.EMPLOYEE_ID as EMPLOYEE_ID1_43_0_, employee0_.FIRST_NAME as FIRST_NAME2_43_0_, employee0_.LAST_NAME as LAST_NAME3_43_0_, phones1_.EMPLOYEE_ID as EMPLOYEE_ID3_43_1_, phones1_.PHONE_TYPE_ID as PHONE_TYPE_ID2_65_1_, phones1_.EMPLOYEE_ID as EMPLOYEE_ID3_65_1_, phones1_.PHONE_TYPE_ID as PHONE_TYPE_ID2_1_, phones1_.PHONE_TYPE_ID as PHONE_TYPE_ID2_65_2_, phones1_.EMPLOYEE_ID as EMPLOYEE_ID3_65_2_, phones1_.PHONE_NUMBER as PHONE_NUMBER1_65_2_, phonetype3_.PHONE_TYPE_ID as PHONE_TYPE_ID1_66_4_, phonetype3_.PHONE_TYPE as PHONE_TYPE2_66_4_ from EMPLOYEE employee0_ left outer join PHONE phones1_ on employee0_.EMPLOYEE_ID=phones1_.EMPLOYEE_ID left outer join PHONE_TYPE phonetype2_ on phones1_.PHONE_TYPE_ID=phonetype2_.PHONE_TYPE_ID left outer join PHONE_TYPE phonetype3_ on phones1_.PHONE_TYPE_ID=phonetype3_.PHONE_TYPE_ID where employee0_.EMPLOYEE_ID=1|EMPLOYEE_ID3_43_1_ = 1, EMPLOYEE_ID3_65_1_ = 1, EMPLOYEE_ID3_65_2_ = 1, FIRST_NAME2_43_0_ = first-1, LAST_NAME3_43_0_ = last-1, PHONE_NUMBER1_65_2_ = 123-123-1234, PHONE_TYPE2_66_4_ = Mobile, PHONE_TYPE_ID1_66_4_ = 10, PHONE_TYPE_ID2_1_ = 10, PHONE_TYPE_ID2_65_1_ = 10, PHONE_TYPE_ID2_65_2_ = 10
Hibernate: select phonetype0_.PHONE_TYPE_ID as PHONE_TYPE_ID1_66_0_, phonetype0_.PHONE_TYPE as PHONE_TYPE2_66_0_ from PHONE_TYPE phonetype0_ where phonetype0_.PHONE_TYPE_ID=?
1457118857476|2|1|statement|select phonetype0_.PHONE_TYPE_ID as PHONE_TYPE_ID1_66_0_, phonetype0_.PHONE_TYPE as PHONE_TYPE2_66_0_ from PHONE_TYPE phonetype0_ where phonetype0_.PHONE_TYPE_ID=?|select phonetype0_.PHONE_TYPE_ID as PHONE_TYPE_ID1_66_0_, phonetype0_.PHONE_TYPE as PHONE_TYPE2_66_0_ from PHONE_TYPE phonetype0_ where phonetype0_.PHONE_TYPE_ID=11
1457118857477|-1||resultset|select phonetype0_.PHONE_TYPE_ID as PHONE_TYPE_ID1_66_0_, phonetype0_.PHONE_TYPE as PHONE_TYPE2_66_0_ from PHONE_TYPE phonetype0_ where phonetype0_.PHONE_TYPE_ID=11|PHONE_TYPE2_66_0_ = Home
Hibernate: insert into PHONE (PHONE_NUMBER, PHONE_TYPE_ID, EMPLOYEE_ID) values (?, ?, ?)
1457118857605|3|1|statement|insert into PHONE (PHONE_NUMBER, PHONE_TYPE_ID, EMPLOYEE_ID) values (?, ?, ?)|insert into PHONE (PHONE_NUMBER, PHONE_TYPE_ID, EMPLOYEE_ID) values ('123-456-7890', 11, 1)
Hibernate: update PHONE set PHONE_TYPE_ID=? where PHONE_TYPE_ID=? and EMPLOYEE_ID=?
1457118857611|2|1|statement|update PHONE set PHONE_TYPE_ID=? where PHONE_TYPE_ID=? and EMPLOYEE_ID=?|update PHONE set PHONE_TYPE_ID=10 where PHONE_TYPE_ID=10 and EMPLOYEE_ID=1
Hibernate: update PHONE set PHONE_TYPE_ID=? where PHONE_TYPE_ID=? and EMPLOYEE_ID=?
1457118857613|2|1|statement|update PHONE set PHONE_TYPE_ID=? where PHONE_TYPE_ID=? and EMPLOYEE_ID=?|update PHONE set PHONE_TYPE_ID=11 where PHONE_TYPE_ID=11 and EMPLOYEE_ID=1
1457118857635|21|1|rollback||
PASSED: addPhoneType
{code}
|
|