Java, Jms, JPA, Oracle create unique strings

Question:

It is necessary to take data from Jms and search in the Address table, if there is one, then take its ID, if not, create a new one and get its ID . Here is the code:

try{
   Query query = em.createQuery(
                "SELECT s.id FROM Address s WHERE s.counrtyId=?1 and s.districtId=?2 and s.regionId=?3  and s.city=upper(?4)")
                .setParameter(1, sa.getCounrtyId())
                .setParameter(2, sa.getDistrictId())
                .setParameter(3, sa.getRegionId())
                .setParameter(4, sa.getCity());

   Long result=(Long)query.getSingleResult();
   return result;
   } catch (NoResultException e){
        em.persist(entity);
        em.flush();
        return sa.getId();
  }

Since jms processes messages in parallel, it does not find such a row in the table, it creates a new one in transactions until all processing ends, only then it saves. If in a parallel stream the same row, it also does not find anything in the table and creates a new one when 2 such rows appear in the database. How can you prevent duplicate lines?

Answer:

Create a unique key in the database by countryId, districtId, regionId, city (if these are all fields except id), then an exception will be thrown instead of inserting a duplicate record into the database.
If the delivery of the jms message is carried out in a transaction, then it will be automatically canceled and later there will be a second attempt to deliver the same message. On the second try, id will be found.
If the jms delivery is not surrounded by a transaction, then you can catch an insert exception inside the method that receives the message.
You can also synchronize message processing, making it not parallel, but sequential, if this option is acceptable.

Scroll to Top