Jpa Repository in Spring boot app findBy issue

Question

I'm trying to create findBy JpaRepo it's about returning only the data where isDeleted attribute is false.

this is my Service :

public List<Customer> getAllCustomers() {
    List<Customer> customers =  cutomerRepository.findByIsDeletedFalse();
    return customers;
}

and this is my Controller :

@GetMapping("/viewList")
@CrossOrigin("http://localhost:4200/")
public ResponseEntity<List<Customer>> getAllCustomers() {
        List<Customer> customers = new ArrayList<>();
        customers = customerService.getAllCustomers();
        if (customers.isEmpty()) {
            LOGGER.error("no content ");
            return new ResponseEntity<>(HttpStatus.NO_CONTENT);
        }
    LOGGER.info("calling list of customers");
    return new ResponseEntity<>(customers, HttpStatus.OK);
}

and this is customer model :

public class Customer {
    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    private int id;

    @Column(name = "serial_number")
    private long serialNumber;
    @Column(name = "first_name")
    private String firstName;
    @Column(name = "last_name")
    private String lastName;
    @Column(name = "email")
    private String email;
    @Column(name = "mobile_number")
    private String mobileNumber;

    @Column(name = "is_deleted")
    private boolean isDeleted;
}

but when I run it in postman it's not working and return an error :

Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: boolean = integer Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
Position: 315

How could I solve this issue?

Answer 1

Looks like the name for your query isn't created right.

However, in this case, the usage of @Query will be much clearer.

Code snippet:

public interface CustomerRepo extends JpaRepository<Customer, Integer> {

    List<Customer> findAllByIsDeletedIsFalse();
    
    @Query("from Customer c where c.isDeleted=false")
    List<Customer> getAllCustomers();
}

Iinstead of:

cutomerRepository.findByIsDeletedFalse()

You missed one more Is at the name of the method.

---

Update your Domain:

public class Customer implements Serializable {
    private final static long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    private Integer id;
    @Column(name = "serial_number")
    private Long serialNumber;
    // ...
    @Column(name = "is_deleted")
    private Boolean isDeleted;
}

JPA fields should be Objects instead of primitives. And entity class should implement Serializable as well.

If the exception will be the same you could try to update @Query:

@Query("from Customer c where c.isDeleted=0")

---

If pure SQL works for your DB you could use native query:

@Query(
  value = "select * from Customer where is_deleted = false",
  nativeQuery = true)
List<Customer> getAllCustomers();

Answer 2

It's not working because it doesn't follow the naming conventions for a boolean field. Usually in Java the primitive booleans are named without is prefix and the getter would be using this is prefix.

So in your case your entity class should look like that:

public class Customer {
    // ...
  
    @Column(name = "is_deleted")
    private boolean deleted;

    public boolean isDeleted() {
        return deleted;
    }

    public void setDeleted(boolean deleted) {
        this.deleted = deleted;
    }

}

Also the naming of the spring repository method should be:

List<Customer> findAllByDeletedIsFalse();

In case you want to use a Boolean reference type you can name your field isDeleted, but then the class would look like that:

public class Customer {
    // ...

    @Column(name = "is_deleted")
    private Boolean isDeleted;

    public Boolean getIsDeleted() {
        return isDeleted;
    }

    public void setIsDeleted(Boolean isDeleted) {
        this.isDeleted = isDeleted;
    }

}

and the repository method:

 List<Customer> findAllByIsDeletedIsFalse();

Answer 3

Boolean Java maps a bit datatype column. You are probably using int as datatype in your database.