-1

I would like to know what good practice is in the following situation:

@Entity
@Table(name = "word")
class WordEntity(uuid: UUID? = null,
                 @Column(nullable = false, unique = true) val name: String
) : BaseEntity(uuid)

If i have an Iterable of this entity and want to call the method saveAll to persist it to the database a ConstraintViolationException can be thrown.

So my goal is to add only unique records to the database. I can loop and do something like this:

fun saveAll(words: List<WordRequest>): List<WordDTO> {

        ...

        for (wordEntity in wordEntities) {
            try {
                result.add(wordRepository.save(wordEntity))
            } catch (e: RuntimeException) { }
        }

        ...
    }

OR i can do a findByName on every loop to check if it exists or not.

So my question is which option should i go for and is there a better way to handle this?

J. Adam
  • 1,457
  • 3
  • 20
  • 38

1 Answers1

1

You've got 2 options:

  • Some databases support INSERT IF NOT EXIST syntax. It's doing exactly what you need. But it means you need write a native SQL.
  • If you want to stick with ORM - you'll have to open a new session (and start new transaction) for each of the records. Because if an exception is thrown you can't keep relying on the same Session (EntityManager), the behavior is undocumented.

Checking if record already exists may lower the number of failed INSERTs (you can do this in 1 SELECT using in() statement), but it doesn't guarantee anything - there's a time between your SELECT and INSERT. Another transaction could INSERT in between. Well, unless you use Serializable isolation level.

Stanislav Bashkyrtsev
  • 14,470
  • 7
  • 42
  • 45
  • Thanks for answering. I tried the first option with a native query (@Query( value = "INSERT INTO word (uuid, name) VALUES (?1, ?2) ON CONFLICT (name) DO NOTHING;", nativeQuery = true)) but it throws a GenericJDBCException: could not extract ResultSet. Since it can't insert it – J. Adam May 15 '20 at 10:26
  • 1
    If you use Spring Data, you need to also add `@Modifying` for INSERTs/UPDATEs – Stanislav Bashkyrtsev May 15 '20 at 11:05
  • That solved the problem, thanks. Do you know is it's possible to return the inserted row as entity when adding the Modifying annotation? – J. Adam May 15 '20 at 11:57
  • 1
    Since you're using UUID you don't have option of getGeneratedKeys(). So it may not be possible, see if your DB supports `returning` syntax: https://stackoverflow.com/a/3552290/886697. You'll have to experiment with SpringData (e.g. remove `@Modifying`), but my guess is that you'd have to do this in pure JDBC. – Stanislav Bashkyrtsev May 15 '20 at 12:06