Tukukkk

I'm currently writing my first Spring Boot application where I wan't to create a basic todo app.

The database works fine as long as I only use the CRUD functions and as soons as I call my own query I get this error:

2018-11-19 10:00:36.353 ERROR 25065 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path  threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause



org.postgresql.util.PSQLException: ERROR: relation "todo" does not exist

My ToDoController:

package ch.aintevenmad.todo;



import org.springframework.web.bind.annotation.CrossOrigin;

import org.springframework.web.bind.annotation.GetMapping;

import org.springframework.web.bind.annotation.RestController;



import java.util.ArrayList;

import java.util.Collection;

import java.util.Date;



@RestController

public class ToDoController {

    private ToDoRepository repository;



    public ToDoController(ToDoRepository repository) {

        this.repository = repository;

    }



    @GetMapping("/todo")

    @CrossOrigin(origins = "http://localhost:4200")

    public Collection<ToDo> allToDo() {

        return new ArrayList<>(repository.findAll());

    }



    @GetMapping("/first")

    @CrossOrigin(origins = "http://localhost:4200")

    public ToDo firstToDo() {

        return repository.findAll().get(0);

    }



    @GetMapping("/delete")

    @CrossOrigin(origins = "http://localhost:4200")

    public void deleteToDo(ToDo toDo) {

        repository.delete(toDo);

    }



    @GetMapping("/add")

    @CrossOrigin(origins = "http://localhost:4200")

    public ToDo addToDO() {

        Date date = new Date();

        ToDo toDo = new ToDo("Hello", date, false);

        repository.save(toDo);

        return toDo;

    }



    @GetMapping("/countcompletedtasks")

    @CrossOrigin(origins = "http://localhost:4200")

    public int countCompletedTasks() {

        return repository.countCompletedTasks().size();

    }



    @GetMapping("/deleteall")

    @CrossOrigin(origins = "http://localhost:4200")

    public void deleteAll() {

        repository.deleteAll();

    }



    @GetMapping("/loaddefaults")

    @CrossOrigin(origins = "http://localhots:4200")

    public void createDefaults() {

        repository.save(new ToDo("PMB", false));

        repository.save(new ToDo("GMDU", false));

        repository.save(new ToDo("INMA", true));

        repository.save(new ToDo("SLGP", false));

    }



}

My ToDo Class:

package ch.aintevenmad.todo;



import lombok.*;



import javax.persistence.*;

import java.util.Date;



@Entity

@Data

@NoArgsConstructor

public class ToDo {

    @Id

    @GeneratedValue

    private Long id;

    private @NonNull

    String taskName;

    private Date dueDate;

    private String extraNote;

    private boolean taskCompleted;



    public ToDo(String taskName, boolean taskCompleted) {

        this.taskName = taskName;

        this.taskCompleted = taskCompleted;

    }

    public ToDo(String taskName, Date dueDate, boolean taskCompleted) {

        this.taskName = taskName;

        this.dueDate = dueDate;

        this.taskCompleted = taskCompleted;

    }

}

My ToDoRepository:

package ch.aintevenmad.todo;



import org.springframework.data.jpa.repository.JpaRepository;

import org.springframework.data.jpa.repository.Query;

import org.springframework.data.rest.core.annotation.RepositoryRestResource;

import org.springframework.web.bind.annotation.CrossOrigin;



import java.util.Collection;



@RepositoryRestResource

@CrossOrigin(origins = "http://localhost:4200")

public interface ToDoRepository extends JpaRepository<ToDo, Long> {

    @Query(value = "SELECT * FROM ToDo WHERE taskCompleted = true", nativeQuery = true)

    Collection<ToDo> countCompletedTasks();

}

Did I set up my project wrong or did I misunderstand how custom queries work with Spring Boot?

Thanks for any help!

EDIT: Thanks Tu.ma you found the problem. Postgresql didnt call the table todo as expected but to_do so of course it didn't fine the table...

Just writing the solution as an answer instead of as a comment.

You should check using a third-party client (not your spring application) the name of the relation in the database. Hibernate is probably giving it a different name.

You have to change your query like below.

Your true statement should be like 'true' instead of true only.

@RepositoryRestResource

@CrossOrigin(origins = "http://localhost:4200")

public interface ToDoRepository extends JpaRepository<ToDo, Long> {

    @Query(value = "SELECT * FROM ToDo WHERE taskCompleted = 'true'", nativeQuery = true)

    Collection<ToDo> countCompletedTasks();

}

Note that you could just use one of spring's built in queries:

Collection<ToDo> findByTaskCompletedIsTrue();

If you really need the number only (as your method's name suggests), you can also use countBy

Long countByTaskCompletedIsTrue();

See the docs for more queries

Thanks Tu.ma you found the problem. Postgresql didnt call the table todo as expected but to_do so of course it didn't fine the table.

Create the default constructor for Todo entity.