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

Solution 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.

Solution 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.

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

Comments

[twistezo]

Good idea @Maciej Kowalski but in your query is error. Console says Validation failed for query for method public abstract java.util.List com.twistezo.repositories.OrderDAO.findCustomersBySumOfAmoun‌​t()! and Intellij suggests Cannot resolve constructor 'com.twistezo.repositories.CustomerTotalAmountResult(java.la‌​ng.String, java.math.BigDecimal) in part ( o.customer.surname , sum(o.amount))` How to fix this ? i have never seen query construction like this, but looks like that its fit to constructor (String, BigDecimal).

[Maciej Kowalski]

try to use NEW in capitals instead of new

[Maciej Kowalski]

also try with Double instead of BigDecimal

[twistezo]

Ok @raminr when i'm printing values from this List<Object[]> findCustomersBySumOfAmount(); with for(Object[] o : findCustomersBySumOfAmount){ System.out.println(o[0]); System.out.println(o[1]); } i have Bielecki 141.49 Bielen 222.39 Nowak 40.80 Nowakowski 171.78 but how can i connect those parameters with for example CustomerAmountResult class from @Maciej Kowalski post ?

[Maciej Kowalski]

On the side you should group by o.customer.surname, not by o.customer. Apart from that, this example is working fine for me. It must work as it is the JPA standard for projection mapping. Can you add full stack trace?

[twistezo]

Ok, now it's working. I had missed something.. Thanks @Maciej Kowalski

[Krishna Barri]

Hi Maciej Kowalski, I have the same scenario like above. Here my exception is could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet, could you help on this?

[Krishna Barri]

For more information, refer this link stackoverflow.com/questions/47631154/…

[kavi]

facing the same issue. can any one help ?

[raminr]

My suggestion was to avoid creating a new class, like it was suggested in response Maciej Kowalski provided. If it's ok to create a new class, then follow his suggestion, it's cleaner.

[codezoner]

Very nice solution !!

[Martijn Hiemstra]

Absolutely brilliant. I have an application that is a hybrid of entity driven and native sql driven DAO's and this solved all my issues with cross entities in same DAO's.