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

Question

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();

Answer 1

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.

Answer 2

I already had this error message. In my case, I was doing something like the code below:

@Repository
public interface RepositoryA extends CrudRepository<EntityA, Long> {

  @Query(nativeQuery = true, value = " ...SQL_1... ")
  List<EntityA> getListOfEntityA(...PARAMS_1...);

  @Query(nativeQuery = true, value = " ...SQL_2... ")
  List<EntityB> getListOfEntityB(...PARAMS_2...);

}

I was parameterizing the repository interface with an "EntityA" and had an other method returning other type parametrized with an "EntityB".

In your case you are parameterizing the JpaRepository with "Order" and then using a method that returns a list of "Customer"s... Maybe this is causing this exception.

Answer 3

I have also had a similar scenario, but in my case, there wasn't any need for mapping(Orders into customers).

If you have all the fields in the same Domain POJO(here it was CustomerDAO), No need to create a new POJO or change the return type to Object. All you need is a parameterized Constructor(with the same fields which you are fetching in the JPQL query) in your Domain POJO.

An Example

package com.mypackage.domain

@Entity
@Table(name = "TABLENAME")
public class UserDO {

@Column(name = "FIRST_NAME")
private String firstName;

@Column(name = "LAST_NAME")
private String lastName;

@Column(name = "DOB")
private String dob;

public UserDO(){
}

public UserDO(String firstName, String lastName){
this.firstName = firstName;
this.lastName = lastName;
}


}

And your repo should contain a method like this

@Query("Select NEW com.mypackage.domain.UserDO(user.firstName, user.lastName) from UserDO as user where user.lastName =?1")
UserDO findByLastName(String lastName);

Answer 4

Your query select o.customer.surname, sum(o.amount) as s from Order as o group by o.customer doesn't return Customer. The Customer entity expects the result set to at least look like:

|   id   | name   | surname |
-----------------------------
|   1    | john   | smith   |
|   2    | james  | white   |
|   3    | sara   | black   | 

That's how the Result Set gets mapped into Customer entity. If you want to stick to your query, then change the return type to List<Object[]>, and run through the list and extract the data that you want.

Answer 5

I was also facing this issue since long.

Another way to fix this one by below

@Query("select entity from com.package.info.EntityName entity where entity.ClientID=:ClientID") 
List<EntityName> findByClientID(@Param("clientID") Long clientID);