-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 
Author Message
 Post subject: How to create an conditional database unique constraint?
PostPosted: Thu Nov 10, 2016 8:44 pm 
Newbie

Joined: Thu Nov 10, 2016 8:41 pm
Posts: 3
Hi,

I have table with the following columns:

- name
- status
- from
- to
- pid

If pid is null and status=1, I have to make "name", "from", "to" as unique key, otherwise new record should be inserted.

How do I handle this using @UniqueConstraint?


Top
 Profile  
 
 Post subject: Re: How to create a conditional combinational key
PostPosted: Fri Nov 11, 2016 3:52 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1638
Location: Romania
The JPA annotation does not support this feature, and you should not use HBM2DDL in production. You should use Flyway or Liquibase instead.

If you're using HBM2DDL during testing and you need this functionality, you can provide an additional script that does:

Code:
CREATE UNIQUE INDEX my_unique_constraint ON my_table (name, from, to) WHERE (pid is null and status = 1);


If this DDL statement is located in a script file called: my_unique_constraint.sql, you can provide it to Hibernate like this:

Code:
<property
    name="hibernate.hbm2ddl.import_files"
    value="my_unique_constraint.sql" />

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: How to create a conditional database unique constraint?
PostPosted: Fri Nov 11, 2016 1:48 pm 
Newbie

Joined: Thu Nov 10, 2016 8:41 pm
Posts: 3
Thats great Thanks, thats what i was thinking too..


Top
 Profile  
 
 Post subject: Re: How to create a conditional combinational key
PostPosted: Fri Nov 11, 2016 7:23 pm 
Newbie

Joined: Thu Nov 10, 2016 8:41 pm
Posts: 3
vlad wrote:
The JPA annotation does not support this feature, and you should not use HBM2DDL in production. You should use Flyway or Liquibase instead.

If you're using HBM2DDL during testing and you need this functionality, you can provide an additional script that does:

Code:
CREATE UNIQUE INDEX my_unique_constraint ON my_table (name, from, to) WHERE (pid is null and status = 1);


If this DDL statement is located in a script file called: my_unique_constraint.sql, you can provide it to Hibernate like this:

Code:
<property
    name="hibernate.hbm2ddl.import_files"
    value="my_unique_constraint.sql" />


I am trying to resolve an issue in my application, which supports autosave(every 10 secs) of a form(only one table no child tables). when there is heavy load on the application hibernate inserting duplicate rows with different ids. Could you give me your view on this. Any inputs on how to fix this issue.

I basically check if the row already exists i update the fields, if its not, then create a new object call saveorupdate(object).
My application is Spring/Hibernate based application,
session factory is org.springframework.orm.hibernate4.LocalSessionFactoryBean
transactionManager is org.springframework.orm.hibernate4.HibernateTransactionManager


Top
 Profile  
 
 Post subject: Re: How to create a conditional database unique constraint?
PostPosted: Sat Nov 12, 2016 2:37 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1638
Location: Romania
In my book, High-Performance Java Persistence, I described how MERGE and UPSERT can be used for this purpose. However, these two operations are not supported by Hibernate, so you need to go the native SQL way.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.