Failed to convert from type [java.lang.Object[]] to type [@org.springframework.data.jpa.repository.Query com.data.models.Users]

Question

I am trying to limit my query to only select specific columns from a table which am going to use, but when I write a simple select query I end up with an error

Resolved [org.springframework.core.convert.ConversionFailedException: Failed to convert from type [java.lang.Object[]] to type [@org.springframework.data.jpa.repository.Query com.aims.covidsurvey.models.Users] for value '{44, test@gmail.com, John , Smith}'; nested exception is org.springframework.core.convert

When I select all, no errors but it picks some big columns that take the query take long to execute.

This is what I have done so far

my repository

public interface ProductRepository extends JpaRepository<Users, Long> {
    
    @Query("SELECT p FROM Users p WHERE CONCAT(p.id, ' ', p.email, ' ', p.firstname, ' ', p.surname) LIKE %?1%")
     List<Users> search(String keyword);

    @Query("SELECT e.id, e.email, e.firstname, e.surname FROM Users e")
     List<Users> findByQuery();
}

My service

@Service
public class ProductService {
    @Autowired
    private ProductRepository repo;



    public List<Users> listAll(String keyword) {
        if (keyword != null) {
            return repo.search(keyword);
        }
        return repo.findByQuery();
    }
}

My model class

@Entity
@Table(name = "users")
public class Users {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @NotBlank
    @Size(max = 100)
    private String username;

    @NotBlank
    @Size(max = 100)
    @Email
    private String email;

    @NotBlank
    @Size(max = 120)
    private String password;

    private String surname;
    private String firstname;
    @Lob
    private byte[] data;
    private Date datecreated;
    private String date_of_birth;
    @Size(max = 10)
    private String gender;
    @Size(max = 30)
    private String national_id;
    @Size(max = 120)
    private String study_role;
    @Size(max = 20)
    private String assigned_team;
    private Integer active;
    private Date  activation_date;
    private String trainingvenue;
    private String staffid;
    private String trainingclass;
    private String country;
    private String resetPasswordToken;
    private Integer deleted;

    @GeneratedValue(generator = "uuid")
    @GenericGenerator(name = "uuid", strategy = "uuid2")
    private String userid;

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "user_roles",
            joinColumns = @JoinColumn(name = "user_id"),
            inverseJoinColumns = @JoinColumn(name = "role_id"))
    private Set<Role> roles = new HashSet<>();

    public Users() {
    }

    public Users(Long id, String email, String firstname, String surname) {
        this.id = id;
        this.email = email;
        this.surname = surname;
        this.firstname = firstname;

    }

My controller

@RequestMapping("/")
public String viewHomePage(Model model, @Param("keyword") String keyword) {
   // List<Users> listProducts = service.listAll(keyword);
    List<Users> listProducts = service.listAll(keyword);

    model.addAttribute("listProducts", listProducts);
    model.addAttribute("keyword", keyword);

    return "index";
}

What am I doing wrong?

Thank you in advance.

Answer 1

The problem is in your repository. When you only select some columns, Spring Boot cannot convert them into your Users object.

There are 2 ways to fix this:

1. Change the return type to List<Object[]>.

@Query("SELECT e.id, e.email, e.firstname, e.surname FROM Users e")
List<Object[]> findByQuery();

Then, you have to do the mapping yourself.

2. Change the query

@Query("SELECT new Users(e.id, e.email, e.firstname, e.surname) FROM Users e")
List<Users> findByQuery();

Just make sure that you have the appropriate constructor in your Users model. As I see, you already have it.

In my opinion, this is a more elegant approach.