You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Rupert Smith <ru...@googlemail.com> on 2014/06/10 16:44:59 UTC
How to prevent multiple inserts of same item (by some criteria)
Hi,
Is there a way to prevent JPA/OpenJPA from doing multiple inserts?
I have a somewhat inconvenient database design, whereby some types held in
a table only allow a single instance to be created (other types do allow
it). This makes preventing multiple instances not so easy to achieve with
simple unique constraints.
What I effectively want to do is something like this:
INSERT INTO Applications (..., type)
VALUES (..., someType)
WHERE NOT EXISTS (SELECT FROM Applications WHERE type = someType)
That is for some Applications where only a single instance is allowed for a
type, to prevent multiple instances from being inserted.
Only answer I have found so far is:
http://www.quora.com/Relational-Databases/JPA-conditional-insertion-RDBMS-transactions-isolation
But I cannot make sense of it.
Thanks for your help.
Rupert
Re: How to prevent multiple inserts of same item (by some criteria)
Posted by Rupert Smith <ru...@googlemail.com>.
On 10 June 2014 19:44, ljnelson <lj...@gmail.com> wrote:
> On Tue, Jun 10, 2014 at 11:37 AM, Rupert Smith [via OpenJPA] <
> ml-node+s208410n7586817h59@n2.nabble.com> wrote:
>
> > The quora link I posted suggests that this will work:
> >
> > 1. Start a transaction.
> > 2. Insert the new entity without checking any condition.
> > 3. Select all entities similar to the one you inserted, using
> > OPTIMISTIC_FORCE_INCREMENT. If it returns more than one entity abort,
> else
> > commit.
> >
> > I couldn't see how that would work, but I haven't tried it.
> >
>
> OK; this will only work if there's some sort of version column in your
> database that can be incremented by the JPA provider (OpenJPA). Do you
> have a @Version-annotated field or property in your entity? From your
> earlier posts I was assuming you were working with a legacy schema, most of
> which have no notion of a version-like column.
>
Yes, I have a version column thanks. Its a new schema, just large and
complex and so easy to change in this organization.
I still don't understand how this will work, but perhaps its best if I just
try it first. Thanks for your help.
Rupert
Re: How to prevent multiple inserts of same item (by some criteria)
Posted by ljnelson <lj...@gmail.com>.
On Tue, Jun 10, 2014 at 11:37 AM, Rupert Smith [via OpenJPA] <
ml-node+s208410n7586817h59@n2.nabble.com> wrote:
> The quora link I posted suggests that this will work:
>
> 1. Start a transaction.
> 2. Insert the new entity without checking any condition.
> 3. Select all entities similar to the one you inserted, using
> OPTIMISTIC_FORCE_INCREMENT. If it returns more than one entity abort, else
> commit.
>
> I couldn't see how that would work, but I haven't tried it.
>
OK; this will only work if there's some sort of version column in your
database that can be incremented by the JPA provider (OpenJPA). Do you
have a @Version-annotated field or property in your entity? From your
earlier posts I was assuming you were working with a legacy schema, most of
which have no notion of a version-like column.
Best,
Laird
--
http://about.me/lairdnelson
--
View this message in context: http://openjpa.208410.n2.nabble.com/How-to-prevent-multiple-inserts-of-same-item-by-some-criteria-tp7586812p7586818.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: How to prevent multiple inserts of same item (by some criteria)
Posted by Rupert Smith <ru...@googlemail.com>.
On 10 June 2014 18:50, ljnelson <lj...@gmail.com> wrote:
> (Just an observer here.)
>
> Sounds like--leaving JPA out of it for a moment--you must also be using
> pessimistic locking in the database, right? to ensure that this
> not-enforced-by-the-database constraint is holding?
I'll try that. The quora link I posted suggests that this will work:
1. Start a transaction.
2. Insert the new entity without checking any condition.
3. Select all entities similar to the one you inserted, using
OPTIMISTIC_FORCE_INCREMENT. If it returns more than one entity abort, else
commit.
I couldn't see how that would work, but I haven't tried it.
Rupert
Re: How to prevent multiple inserts of same item (by some criteria)
Posted by ljnelson <lj...@gmail.com>.
(Just an observer here.)
Sounds like--leaving JPA out of it for a moment--you must also be using
pessimistic locking in the database, right? to ensure that this
not-enforced-by-the-database constraint is holding?
If so, you may want to look into lock modes in JPA and issuing a find()
first with a pessimistic lock, and, if that fails, then proceeding with the
persist() call.
Best,
Laird
On Tue, Jun 10, 2014 at 10:44 AM, Rupert Smith [via OpenJPA] <
ml-node+s208410n7586814h66@n2.nabble.com> wrote:
> On 10 June 2014 16:31, Kevin Sutter <[hidden email]
> <http://user/SendEmail.jtp?type=node&node=7586814&i=0>> wrote:
>
> > I think you are just describing a unique constraint for a column (other
> > than the primary key).
>
>
> Sadly not, it isn't simply a question of putting a unique constraint on
> some columns. As I pointed out, in some cases a duplicate is allowed, in
> others it is not. I could just re-arrange the database design, but I don't
> own the design it is something that has been given to me.
>
> Consider the case where the criteria to allow an insert to happen is some
> arbitrary query, not just a unique constraint. This can be accomplished
> with a WHERE NOT EXISTS (SELECT ... the arbitrary insertion condition).
> How
> can this be done with JPA/OpenJPA?
>
> Rupert
>
>
> ------------------------------
> If you reply to this email, your message will be added to the discussion
> below:
>
> http://openjpa.208410.n2.nabble.com/How-to-prevent-multiple-inserts-of-same-item-by-some-criteria-tp7586812p7586814.html
> To start a new topic under OpenJPA Users, email
> ml-node+s208410n208411h73@n2.nabble.com
> To unsubscribe from OpenJPA Users, click here
> <http://openjpa.208410.n2.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=208411&code=bGpuZWxzb25AZ21haWwuY29tfDIwODQxMXw4OTk4MzY1OTI=>
> .
> NAML
> <http://openjpa.208410.n2.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>
--
http://about.me/lairdnelson
--
View this message in context: http://openjpa.208410.n2.nabble.com/How-to-prevent-multiple-inserts-of-same-item-by-some-criteria-tp7586812p7586815.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: How to prevent multiple inserts of same item (by some criteria)
Posted by Kevin Sutter <kw...@gmail.com>.
Okay, Rupert. I guess I read your initial note too quickly... :-) Sorry
about that.
I don't see how you could automate this type of checking into a single JPA
interaction with the database. You could accomplish something like this
with multiple SQL statements, but then the logic and locking is pretty much
left up to you.
(BTW, I didn't care for Quora's sign-in requirements, so I didn't actually
read the reference you posted.)
Good luck,
Kevin
On Tue, Jun 10, 2014 at 12:43 PM, Rupert Smith <rupertlssmith@googlemail.com
> wrote:
> On 10 June 2014 16:31, Kevin Sutter <kw...@gmail.com> wrote:
>
> > I think you are just describing a unique constraint for a column (other
> > than the primary key).
>
>
> Sadly not, it isn't simply a question of putting a unique constraint on
> some columns. As I pointed out, in some cases a duplicate is allowed, in
> others it is not. I could just re-arrange the database design, but I don't
> own the design it is something that has been given to me.
>
> Consider the case where the criteria to allow an insert to happen is some
> arbitrary query, not just a unique constraint. This can be accomplished
> with a WHERE NOT EXISTS (SELECT ... the arbitrary insertion condition). How
> can this be done with JPA/OpenJPA?
>
> Rupert
>
Re: How to prevent multiple inserts of same item (by some criteria)
Posted by Rupert Smith <ru...@googlemail.com>.
On 10 June 2014 16:31, Kevin Sutter <kw...@gmail.com> wrote:
> I think you are just describing a unique constraint for a column (other
> than the primary key).
Sadly not, it isn't simply a question of putting a unique constraint on
some columns. As I pointed out, in some cases a duplicate is allowed, in
others it is not. I could just re-arrange the database design, but I don't
own the design it is something that has been given to me.
Consider the case where the criteria to allow an insert to happen is some
arbitrary query, not just a unique constraint. This can be accomplished
with a WHERE NOT EXISTS (SELECT ... the arbitrary insertion condition). How
can this be done with JPA/OpenJPA?
Rupert
Re: How to prevent multiple inserts of same item (by some criteria)
Posted by Kevin Sutter <kw...@gmail.com>.
I think you are just describing a unique constraint for a column (other
than the primary key). Here's one reference in the OpenJPA documentation,
but there are several references through out the documentation or other
JPA-related documentation.
http://ci.apache.org/projects/openjpa/trunk/docbook/manual.html#jpa_overview_mapping_unq
Kevin
On Tue, Jun 10, 2014 at 9:44 AM, Rupert Smith <ru...@googlemail.com>
wrote:
> Hi,
>
> Is there a way to prevent JPA/OpenJPA from doing multiple inserts?
>
> I have a somewhat inconvenient database design, whereby some types held in
> a table only allow a single instance to be created (other types do allow
> it). This makes preventing multiple instances not so easy to achieve with
> simple unique constraints.
>
> What I effectively want to do is something like this:
>
> INSERT INTO Applications (..., type)
> VALUES (..., someType)
> WHERE NOT EXISTS (SELECT FROM Applications WHERE type = someType)
>
> That is for some Applications where only a single instance is allowed for a
> type, to prevent multiple instances from being inserted.
>
> Only answer I have found so far is:
>
>
> http://www.quora.com/Relational-Databases/JPA-conditional-insertion-RDBMS-transactions-isolation
>
> But I cannot make sense of it.
>
> Thanks for your help.
>
> Rupert
>