Spring boot - To execute statement sql update (JPA)

Question

I just have started with Spring Boot and I am practicing in base to this example: http://www.devglan.com/spring-boot/spring-boot-angular-example

The physical deleted is working ok, but I am trying to do a logical deleted and I do not know how to do it.

This is my classes:

UserController

@DeleteMapping(path ={"logical/{id}"})
public User deleteLogical(@PathVariable("id") int id, @RequestBody User user) {
    return userService.deleteLogical(id, user);
}

UserService

User deleteLogical(int id, User user);

UserServiceImpl

@Override
// @Query("update user set remove = false where id = ?1")
public User deleteLogical(int id, User user) {
    // user.setRemove(false);
    return repository.save(user);
}

UserRepository

User save(User user);

This is the SQL statement I want to execute:

UPDATE user SET remove = false WHERE id =?;

How I could do it? Thanks,

Alexander Petrov · Accepted Answer · 2018-05-18 09:30:28Z

1

I think you dont need to pass User object. Id will be enough.

So your code will be changed

@DeleteMapping(path ={"logical/{id}"})
public User deleteLogical(@PathVariable("id") int id) {
    return userService.deleteLogical(id, user);
}

UserService

User deleteLogical(int id);

UserServiceImpl

@Override
public User deleteLogical(int id) {
    User user = repository.findById(id);
    user.setRemove(true);
    return repository.save(user);
}

That's all.

answered May 18, 2018 at 9:30

Alexander Petrov

1,1218 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cepr0 · Accepted Answer · 2018-05-18 10:17:48Z

You try to implement "soft delete" so you can try this approach:

User entity:

@Entity
User {
    @Id
    @GeneratedValue
    private Integer id;

    //...

    private boolean removed; // user is removed if this property is true

    //...
}

User service:

public interface UserService {   
    Optional<Integer> softDelete(int id);
}

@Service    
public UserServiceImpl implements UserService (

   // Injecting UserRepo

   // First variant - read the user, set it removed, then updated it.
   @Transactional
   Optional<Integer> softDelete1(int id) {
       // Using method findById from Spring Boot 2+, for SB1.5+ - use method findOne 
       return userRepo.findById(id).map(user -> {
           user.setRemoved(true); 
           userRepo.save(user);
           return 1;
       });
   }

   // Second variant - update the user directly
   @Transactional
   Optional<Integer> softDelete2(int id) {
       return userRepo.softDelete(id);
   }
}

User controller:

@RestController
@RequestMapping("/users")
public class UserController {

   // Injecting UserService

   @DeleteMapping("/{id}")
   public ResponseEntity<?> softDelete(@PathVariable("id") int id) {
        return userService.softDelete1(id) // you can use 1 or 2 variants 
            .map(i -> ResponseEntity.noContent().build()) // on success
            .orElse(ResponseEntity.notFound().build()); // if user not found
   }
}

User repo:

public interface UserRepo extends JpaRepository<User, Integer>() {
    @Modifying(clearAutomatically = true)
    @Query("update User u set u.removed = true where u.id = ?1")
    int remove(int id);

    default Optional<Integer> softDelete(int id) {
        return (remove(id) > 0) ? Optional.of(1) : Optional.empty();
    }
}

UPDATED

Or, I think, you can just try to simply override the deleteById method of CrudRepository (not tested - please give a feedback):

@Override
@Modifying(clearAutomatically = true)
@Query("update User u set u.removed = true where u.id = ?1")
void deleteById(Integer id);

then use it in your service.

This information is more complete, thanks, when I will checked it, I'll leave a comment

Collectives™ on Stack Overflow

Spring boot - To execute statement sql update (JPA)

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related