Is there a way to create a Conditional insert with JPA

[Tony Evans]

Hi

I have a collection of People that contains a collection of Accounts, that contains a collection of fundsm that i insert into a table .

I have mapped this as People manyToMany Accounts manyTomany Funds in my JPA layer

I have free tables PEOPLE, ACCOUNT, FUND

I am extending  org.springframework.data.jpa.repository.JpaRepository and use  JpaRepository   List save(Iterable entities);

This code works, and I can add and read from the DB.

But now I want to extend the functionality, in FUND i have added a datetime, and if the fund comes in and has a datetime that is less than the datetime in the Table. I dont want that data inserted.

Now I know of two ways to add this logic

1) I can add this check in the Java layer, which I dont want to do, as it means hitting the FUND Table to get dateTimes, then again to insert from the java code.
2) Use Hibernate Query Language to write the JPA layer

But I want to keep with usng the JpaRepository methods.  Is there an annotation that i can use on the Entity, that says check a condition is satisfied before saving the data.

Thanks for any help

Tony

[Tony Evans]

Sorry for recreating this post. I cant find an edit or a delete post button for my first post

[Tim Moores]

No worries, I've deleted the other post.

[Tim Holloway]

Phrased the way you stated it, almost certainly there wouldn't be an annotation. Although there are annotations that can throw Exceptions when columns don't meet stated validation criteria. But that's low-level stuff, not business logic.

In fact, I can't visualize any way to do a conditional insert in standard SQL, with or without Hibernate and/or JPA, so as far as I can tell, you'd have to query before insert, regardless. Or, at best, have a framework whose low-level functions made the database hit silently. Which would simply make you feel more virtuous than you actually were.

In fact, about the only way to make a conditional INSERT would be to code conditions into a trigger (or use a stored procedure) and have the database refuse attempts to violate those conditions. Which is pretty icky. I don't recommend putting logic on the database side unless it's absolutely essential and besides, trying to figure out in JPA whether the failure was on the conditions or due to some other fault might be difficult.

A better approach would be to make sure your application-side database caches are tuned to minimize the actual SQL requests that will be needed.

[Tony Evans]

Hi Tim Holloway

Thank you for your reply, it was very informative. I felt there was no straight forward soultion. and no JPA annotaion based approach. I just wanted to throw the problem out there, just in case I was wrong. Being told what we cant do is just an informative of being told what we can do. And I am now restructuring the logic.

Thanks Tony

[Tim Holloway]

Well, I have made pompous pronouncements more than once before and been clued into features that made a liar out of me. But we're talking some pretty basic aspects about how SQL works here, so I don't think anyone is going to ridicule you if you design that way.

And if they do, and can prove there's a better way, let us know so we can spread the world. I do hate looking like an idiot more than once.

[Tony Evans]

If there is, I will update this blog.