error:No results were returned by the query in Spring data-jpa how to fix this error?-CodePudding

I'm facing an error with the Spring data jpa in springboot. I have tried many times and looked on google, but I didn't find any solution to my problem. Can anyone help me to fix this error?

Source Code

**TodoController.java

@CrossOrigin(origins="http://localhost:4200")
@RestController
public class TodoController {

    @Autowired
    private TodoService todoservice ;
    
    @GetMapping(path="/AllTodos/{username}")
    public List<Todo> GetAllTodos(@PathVariable String username){
        return todoservice.getAllCount(username) ;
        
    }
    
    @DeleteMapping("AllTodos/{username}/todos/{id}")
    public Todo deleteTodos(@PathVariable String username,@PathVariable Long id){
        
      
       
      return  todoservice.deleteTodos(username,id);
      
    }

TodoService.java

@Service
public class TodoService {
    
    
    @Autowired
   private TodoRepo todorepo ;
    
    
    public List<Todo> getAllCount(String username) {
        // TODO Auto-generated method stub
        return todorepo.findAllByUsername(username);
    }


    public Todo deleteTodos(String username, Long id) {
        
         return todorepo.deleteByUsernameId(username,id);
    }
    
    
}

TodoRepo.java

@Repository
public interface TodoRepo extends JpaRepository<Todo,String> {

    

    List<Todo> findAllByUsername(String username);

    
    @Query(value="DELETE  FROM public.Todo WHERE username=? And id=? ",nativeQuery = true)
    Todo deleteByUsernameId(String username, Long id);

    
}

Error:

org.postgresql.util.PSQLException: No results were returned by the query.
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:120) ~[postgresql-42.3.4.jar:42.3.4]
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-4.0.3.jar:na]
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57) ~[hibernate-core-5.6.8.Final.jar:5.6.8.Final]
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2322) ~[hibernate-core-5.6.8.Final.jar:5.6.8.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2075) ~[hibernate-core-5.6.8.Final.jar:5.6.8.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2037) ~[hibernate-core-5.6.8.Final.jar:5.6.8.Final]
    at org.hibernate.loader.Loader.doQuery(Loader.java:956) ~[hibernate-core-5.6.8.Final.jar:5.6.8.Final]

CodePudding user response：

Try adding numbers after the ? marks and remove the word public as i illustrate below:

@Query(value="DELETE  FROM todo WHERE username=?1 And id=?2 ",nativeQuery = true)
Todo deleteByUsernameId(String username, Long id);

CodePudding user response：

That is because you are attempting to return Todo and your query does not return any results. Change the return type to void. Also why do you need a username if you have the id? Is the id not the pk? Note that you can use derived JPA delete methods, for example:

Long deleteByUsername(String username);

Note that derived delete methods either return void or the delete count as per https://docs.spring.io/spring-data/jpa/docs/current/reference/html/.