2

First of all, I know this should not be a solution, but I need to fix the problem this way.

I have a OneToOne relation that, very rarely, is not respected (DB returns two rows instead of one). I need to force this relation, something like a LIMIT 1 for this specific Hibernate query.

@Entity
@Table(name="contact", uniqueConstraints = @UniqueConstraint(columnNames="user_id"))
public class ContactDTO  implements Serializable {

(...)

// Force this relation
@OneToOne(cascade = CascadeType.REFRESH, fetch = FetchType.LAZY, optional = true)
@JoinColumn(name = "user_id", unique = true, insertable = false, updatable = false)
public UserDTO getBaseUser() {
    return baseUser;
}

public void setBaseUser(UserDTO baseUser) {
    this.baseUser = baseUser;
}

@Column(name = "user_id", unique = true)
public Integer getUserId() {
    return this.userId;
}

@Entity
@Table(name = "base_user")
public class UserDTO implements Serializable {

(...)

@OneToOne(fetch = FetchType.LAZY, mappedBy = "baseUser")
public ContactDTO getContact() {
    return contact;
}

public void setContact(ContactDTO contact) {
    this.contact = contact;
}

When I get two rows (maximum), Hibernate throws the following exception:

org.hibernate.HibernateException: More than one row with the given identifier was found

Is this possible or I really need to turn this relation into a @OneToMany?

Note: Hibernate 3.3, sadly I can not use JoinColumnsOrFormula.

Thank you for your help! Best regards.

BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
Daniel Pinto
  • 21
  • 1
  • 3
  • Could you please elaborate on the entities and tables involved? – Sanjeev Saha Jul 08 '16 at 17:50
  • Sure. Added some more information, I think it's enough to understand the logic. – Daniel Pinto Jul 08 '16 at 18:32
  • You could always change to OneToMany relationship, but use it on a private field. Then provide getters and setters that operate only on first element. It will basically encapsulate your undesired situation without having other code to know about it. – Rafał S Jul 08 '16 at 18:56

2 Answers2

0

If your receiving duplicate records than it most likely means the relationship may not be 1:1 in your database. You will need to either add database constraints to enforce this relationship or change your annotation to 1:many. It's worth noting that the @OneToOne annotation doesn't enforce anything in the database, It simply defines what Hibernate is expecting the relationship to be, which is why it throws an error when you receive multiple records.

Check the answer to this post to see how your relationship should look in your database:

Defining a one-to-one relationship in SQL Server

Community
  • 1
  • 1
Steve
  • 981
  • 1
  • 8
  • 22
  • Thank you for your answer Steve. The problem is that the Client doesn't want a database constraint and also doesn't want to fix the code problem. Makes sense? Of course not, I know. The only solution I have is to guarantee that this query returns only one row to respect the hibernate relation, or change it to OneToMany, despite the fact this only occurs like 5 times in a year. – Daniel Pinto Jul 08 '16 at 18:16
  • Of those two approaches, I would say the OnetoMany approach would be better practice. If you Limited the results of the query, you would be excluding valid data. – Steve Jul 08 '16 at 18:24
  • It's duplicated data, so I just need one of the two rows. The OneToMany approach is the one that I'm trying to avoid, but in last case I'll do it. For now, I'm trying to solve the problem with a view-trigger. Thank you for your help Steve. – Daniel Pinto Jul 08 '16 at 18:55
0

To achieve that you could use views. Assume that you have SOME_TABLE with two columns REAL_ID and BAD_ID. First one is unique, second one is your broken non-unique column. Just create view with for example:

select * from SOME_TABLE where REAL_ID in (
  select min(REAL_ID) from SOME_TABLE group by BAD_ID
)

Which basically is your original table minus records that duplicate BAD_ID values. Then in hibernate mapping you use this view instead of original table.

Obviously it's a hack, so I cannot guarantee its performance, but it should allow you to use @OneToOne binding. Biggest downside is that by default this will be read-only mapping (which probably could by changed with instead of triggers). EDIT Some DBs allow for simple views to be updateable, so it may work without any triggers.

You could also look into generating your entities straight from the query.

Rafał S
  • 813
  • 7
  • 13
  • Thank you for your answer Rafal. That is the solution I'm thinking about and would fit well in this case. As you said it's a hack, I totally agree. Also as you said, it would be a read-only mapping and I need it to be read-write. So, you have any idea how can I solve this with triggers? – Daniel Pinto Jul 08 '16 at 18:51
  • Here is an article which gives an example of using `instead of` triggers on view to do inserts and updates on real table: http://in.relation.to/2004/06/20/history-triggers-and-hibernate/ Hope it helps. – Rafał S Jul 08 '16 at 19:02
  • It may be easier in some DBs. As I read both [sqlserver](https://stackoverflow.com/questions/3866216/updatable-views-sql-server-2008) and [oracledb](https://docs.oracle.com/cd/B28359_01/server.111/b28310/views001.htm#i1007148) have updatable views – Rafał S 52 mins ago – Rafał S Jul 08 '16 at 20:16