[FIXED] TransactionRequiredException when updating with Jpa Repository

Issue

On succesfull authentication I'd like to make a call to db to retrieve some additional info but I get:

org.springframework.dao.InvalidDataAccessApiUsageException: Executing an update/delete query; nested exception is javax.persistence.TransactionRequiredException: Executing an update/delete query

My successfulAuthentication method:

    @Override
    public void successfulAuthentication(HttpServletRequest request, HttpServletResponse response, FilterChain chain, Authentication authentication) throws IOException, ServletException {
        User user = (User) authentication.getPrincipal();
        AppUser appUser = userRepo.findByUsername(user.getUsername());

        Port port;
        portRepo.enableExpiredPorts(LocalDateTime.now());
        port = portRepo.checkIfUserHasPort(appUser.getUserId());
        if (port == null) {
            port = portRepo.getOpenPort();
        } else {
            portRepo.extendDuration(port.getPort());
        }
        portRepo.setInUse(appUser.getUserId(), port.getPort());
        //Some unrelated logic
    }

Repo that I am calling:

@Repository
public interface PortRepo extends JpaRepository<Port, Integer> {

    @Modifying
    @Query(value = "UPDATE Port SET active=1 WHERE expiration<datetime()", nativeQuery = true)
    void enableExpiredPorts(@Param("localDateTime")LocalDateTime localDateTime);

    @Query(value = "SELECT * FROM Port WHERE user_id=:userId", nativeQuery = true)
    Port checkIfUserHasPort(@Param("userId") Long userId);

    @Query(value = "SELECT * FROM Port WHERE active=1 LIMIT 1", nativeQuery = true)
    Port getOpenPort();

    @Modifying
    @Query(value = "UPDATE Port SET active=0, user_id=:userId, expiration=datetime('now')  WHERE port=:port", nativeQuery = true)
    void setInUse(@Param("userId") Long userId, @Param("port") int port);

    @Modifying
    @Query(value = "UPDATE Port SET expiration=datetime('now')  WHERE port=:port", nativeQuery = true)
    void extendDuration(@Param("port") int port);
}

Adding @Transactional annotation didn't help (It was correct one from org.springframework.transaction.annotation.Transactional)

Solution

The successulAuthentication is an internal method call so adding @Transactional won't help (as not passing through the proxy).

The easiest is to create another class and move the logic to there and annotate that class (or the method) with @Transactional.

@Component
@Transactional
public class AfterAuthenticationService {

  private final UserRepo userRepo;
  private final PortRepo portRepo;

  public AfterAuthenticationService(UserRepo userRepo, PortRepo portRepo) {
    this.userRepo=userRepo;
    this.portRepo=portRepo;
}

  public void onSuccessfulAuthentication(Authentication authentication) {
    User user = (User) authentication.getPrincipal();
    AppUser appUser = userRepo.findByUsername(user.getUsername());

    portRepo.enableExpiredPorts(LocalDateTime.now());
    Port port = portRepo.checkIfUserHasPort(appUser.getUserId());
    if (port == null) {
      port = portRepo.getOpenPort();
    } else {
      portRepo.extendDuration(port.getPort());
    }
    portRepo.setInUse(appUser.getUserId(), port.getPort());
   //Some unrelated logic
  }
}

Then in your filter call this service and next the super method.

@Override
public void successfulAuthentication(HttpServletRequest request, HttpServletResponse response, FilterChain chain, Authentication authentication) throws IOException, ServletException {
  this.afterAuthenticationService.onSuccessfulAuthentication(authentication);
  super.successfulAuthentication(request, response, chain, authentication);    
}

Or ditch this all together and write an event listener for InteractiveAuthenticationSuccessEvent and then run your logic there. That way you don't have to extend filters etc. and still get the desired behavior (and have it loosy coupled).

@Component
@Transactional
public class AfterAuthenticationListener {

  private final UserRepo userRepo;
  private final PortRepo portRepo;

  public AfterAuthenticationService(UserRepo userRepo, PortRepo portRepo) {
    this.userRepo=userRepo;
    this.portRepo=portRepo;
}

  @EventListener(InteractiveAuthenticationSuccessEvent.class)
  public void onSuccesfulAuthentication(InteractiveAuthenticationSuccessEvent event) {
    Authentication authentication = event.getAuthentication();
    User user = (User) authentication.getPrincipal();
    AppUser appUser = userRepo.findByUsername(user.getUsername());

    portRepo.enableExpiredPorts(LocalDateTime.now());
    Port port = portRepo.checkIfUserHasPort(appUser.getUserId());
    if (port == null) {
      port = portRepo.getOpenPort();
    } else {
      portRepo.extendDuration(port.getPort());
    }
    portRepo.setInUse(appUser.getUserId(), port.getPort());
   //Some unrelated logic
  }
}

With the listener you can now omit your modified filter.

Answered By - M. Deinum
Answer Checked By - Robin (JavaFixing Admin)