[FIXED] Failed to convert from type [java.lang.Object[]] to type

Issue

I have spring web app (JPA/Hibernate + MySQL). I have two DAO classes.

CustomerDAO

@Entity
@Table(name = "customers")
public class Customer {

  @Id
  @Column(name = "customer_id")
  @GeneratedValue(strategy = GenerationType.AUTO)
  private Long id;

  @Column(name = "name", length = 50)
  private String name;

  @Column(name = "surname", length = 50)
  private String surname;

  @OneToMany(mappedBy = "customer")
  private Set<Order> orders = new HashSet<>();
}

OrderDAO

@Entity
@Table(name = "orders")
public class Order {

  @Id
  @Column(name = "order_id")
  @GeneratedValue(strategy = GenerationType.AUTO)
  private Long id;

  @Column(name = "date")
  private Date date;

  @Digits(integer = 5, fraction = 2)
  @Column(name = "amount")
  private BigDecimal amount;

  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "customer_id")
  private Customer customer;

  @OneToMany(mappedBy = "order")
  private Set<OrderDetail> ordersDetails = new HashSet<>();

And i have a class for retrieving data from DB:

@Repository
public interface OrderDAO extends JpaRepository<Order, Long> {

    @Query("select o.customer.surname, sum(o.amount) as s from Order as o group by o.customer")
    List<Customer> findCustomersBySumOfAmount();

}

It is giving me result like this:

+---------+---------------+
| surname | sum of amount | 
+---------+---------------+
|Bielecki | 141.49        | 
|Bielen   | 130.34        |
......

Now i want 'unbox' data from DB with this method List<Customer> findCustomersBySumOfAmount()

I have method for this in my spring controller class:

 List<Customer> findCustomersBySumOfAmount = orderService.findCustomersBySumOfAmount();
 model.addAttribute("findCustomersBySumOfAmount", findCustomersBySumOfAmount);

 for(Customer c : findCustomersBySumOfAmount) {
     String s = c.getSurname();
     System.out.println(c);
 }

And i have error:

Failed to convert from type [java.lang.Object[]] to type [com.twistezo.models.Customer] for value '{Bielecki, 141.49}'; nested exception is org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [java.lang.String] to type [com.twistezo.models.Customer]

I suppose it's because I'm getting List<Object[]>. I know that I can iterate between this List<Object[]> of my data but maybe there is some simpler way to retrieve data directly to <Customer> ? I'm new in this stuff. Since now I used methods like List<Customer> findAll() without @Query annotation and i'm looking for similar "unboxing".

I was trying do something like this (add Customer.class in query) without effect:

@Query("select o.customer.surname, sum(o.amount) as s from Order as o group by o.customer", Customer.class) List<Customer> findCustomersBySumOfAmount();

Solution

I would suggest creating a POJO class just to store the results of that query:

package com.mypackage;

public class CustomerAmountResult{

    private String surname;
    private BigDecimal amountSum;

    public CustomerAmountResult(String surname, BigDecimal amountSum){
       this.surname = surname;
       this.amountSum = amountSum;
    }

    // getters / setters
}

Then change your query to the following:

@Query("select NEW com.mypackage.CustomerAmountResult(
            o.customer.surname, sum(o.amount)) 
        from Order as o
        group by o.customer.surname") 
List<CustomerAmountResult> findCustomersBySumOfAmount();

Thanks to that you will not need to parse the result set manually.

Answered By - Maciej Kowalski
Answer Checked By - Clifford M. (JavaFixing Volunteer)