Spring JPA native query with Projection gives "ConverterNotFoundException"

Question

I'm using Spring JPA and I need to have a native query. With that query, I need to get only two fields from the table, so I'm trying to use Projections. It isn't working, this is the error I'm getting:

org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type [com.example.IdsOnly]

I tried to follow precisely the instructions of that page I linked, I tried to make my query non-native (do I actually need it to be native if I use projections, btw?), but I always get that error.
If I use an interface it works, but the results are proxies and I really need them to be "normal results" that I can turn into json.

So, here's my code. The Entity:

import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
@Entity
@Table(name = "TestTable")
public class TestTable {

    @Id
    @Basic(optional = false)
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    @Column(name = "Id")
    private Integer id;
    @Column(name = "OtherId")
    private String otherId;
    @Column(name = "CreationDate")
    @Temporal(TemporalType.TIMESTAMP)
    private Date creationDate;
    @Column(name = "Type")
    private Integer type;
}

The class for the projection:

import lombok.Value;

@Value // This annotation fills in the "hashCode" and "equals" methods, plus the all-arguments constructor
public class IdsOnly {

    private final Integer id;
    private final String otherId;
}

The Repository:

public interface TestTableRepository extends JpaRepository<TestTable, Integer> {

    @Query(value = "select Id, OtherId from TestTable where CreationDate > ?1 and Type in (?2)", nativeQuery = true)
    public Collection<IdsOnly> findEntriesAfterDate(Date creationDate, List<Integer> types);
}

And the code that tries to get the data:

@Autowired
TestTableRepository ttRepo;
...
    Date theDate = ...
    List<Integer> theListOfTypes = ...
    ...
    Collection<IdsOnly> results = ttRepo.findEntriesAfterDate(theDate, theListOfTypes);  

Thanks for the help. I really don't understand what I'm doing wrong.

Footy

Where is your MyProjectionClass class in the code – Nonintervention

Sorry, I changed its name while making the actual code "pseudo". I edited the question so that the error displays "com.example.IdsOnly". – Footy

Answer 1

with spring data you can cut the middle-man and simply define

public interface IdsOnly {
  Integer getId();
  String getOtherId();
}

and use a native query like;

@Query(value = "Id, OtherId from TestTable where CreationDate > ?1 and Type in (?2)", nativeQuery = true)
    public Collection<IdsOnly> findEntriesAfterDate(Date creationDate, List<Integer> types);

check out https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#projections

Watch

@InêsGomes, it basically run a native sql query and convert the output schema to a POJO, so what you can do with sql you can do here as well, I don't quite understand your question, but if you can send the error and issues you are encounter, I'll try to help, I know the community here will be happy to help as well so maybe you should post a new question specifically about it – Watch

I changed the accepted answer to be this one since it's actually for native queries and has more votes than the other one. – Footy

and you can exactly the same with nativeQuery=true – Stair

How about boolean fields in the result? Those seem to brake the code whenever you try to do interface.getBooleanField. – Potentate

Can I have joins (nested projections)? – Eade

Answer 2

The query should be using a constructor expression:

@Query("select new com.example.IdsOnly(t.id, t.otherId) from TestTable t where t.creationDate > ?1 and t.type in (?2)")

And i dont know Lombok, but make sure there is a constructor that takes the two IDs as parameters.

Licko

If you don't know Lombok, definitely check it out! >_> Thanks, I'll try this. So I guess my query doesn't need to be native, after all. – Footy

It worked. Thank you very much. Btw I had to also write the column names with the variable names of the Entity (so, "id", "otherId", "creationDate" and "type"). – Footy

Comment: solution not for nativeQuery – Saire

Ok, I selected @shahaf's answer as the accepted one since it's for native queries and has more votes. – Footy

the question was for native query – Icaria

Answer 3

You can return list of Object Array (List) as return type of the native query method in repository class.

@Query(
            value = "SELECT [type],sum([cost]),[currency] FROM [CostDetails] " +
                    "where product_id = ? group by [type],[currency] ",
            nativeQuery = true
    )
    public List<Object[]> getCostDetailsByProduct(Long productId);

for(Object[] obj : objectList){
     String type = (String) obj[0];
     Double cost = (Double) obj[1];
     String currency = (String) obj[2];
     }

Gupton

Answer 4

JPA 2.1 introduces an interesting ConstructorResult feature if you want to keep it native.

Tirzah

Thanks, I saw that but it seemed overly complicated for my simple need (and I thought that Projections also worked with native queries). For sure I'll use that if I'll actually need my queries to be native. The ones I have now didn't need to, in the end. – Footy

I also found no way to get my native SQL Query Result to map to a custom type, other than providing mentioned SqlResultSetMapping along with a ConstructorResult on the JPA entity and then executing the query directly on the EntityManager. So unfortunately no way to use the Spring Data's @Query annotations given these constraints (native query + custom projection). – Experienced

Answer 5

@Query(value = "select  isler.saat_dilimi as SAAT, isler.deger as DEGER from isler where isler.id=:id", nativeQuery = true) 
List<Period> getById(@Param("id") Long id);


public interface Period{
    Long getDEGER(); 

    Long getSAAT();

}

as seen in the example code for native query given above, cast return values to any value like as "SAAT", "DEGER" and then define interface "period" which have getDEGER() and getSAAT(). Even if I have not understand why parameter after get must be uppercase, in lowercase scenario it didn't work properly. ie. interface with getDeger(), getSaat() does not work properly in my case.

Confederation

So you had select isler.saat_dilimi as saat and getSaat() didn't work? That would be odd. – Footy

@Footy sorry for late reply. You are correct. Do you know whether any possibility exist to set native query result to dto instead of the interface projection or not? – Confederation

Nope, I don't know. I needed that too in another project and I gave up and made both the interface and the DTO with the same fields (well sort of, the interface only has methods but you know what I mean) and made a constructor method in the DTO that gets all the values from the interface given as the only parameter. Like: public class PeriodDTO { private Long deger; private Long saat; public PeriodDTO(Period periodInterface) { this.deger = periodInterface.getDEGER(); this.saat = periodInterface.getSAAT(); } } It's ugly but it's something... – Footy

@Footy thanks for reply, yes, I think too, this solution is ugly – Confederation

Parameters here are filled in order, so change fields order in your interface or in your query - so they match (1st is SAAT, 2nd: DEGER) – Stair