sqlException : column not found JPA @query

Question

I'm using @query annotation but when I try to fetch count of records it throws

java.sql.SQLException: Column 'allowPartialPay' not found. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1094) ~[mysql-connector-java-5.1.31.jar:na] at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:997) ~[mysql-connector-java-5.1.31.jar:na] at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:983) ~[mysql-connector-java-5.1.31.jar:na] at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:928) ~[mysql-connector-java-5.1.31.jar:na] at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1162) ~[mysql-connector-java-5.1.31.jar:na] at com.mysql.jdbc.ResultSetImpl.getBoolean(ResultSetImpl.java:1781) ~[mysql-connector-java-5.1.31.jar:na]

I'm writing my custom queries in repository.

InvoiceRepository.java

public interface InvoiceRepository extends JpaRepository<Invoice, Integer>{ Invoice findByInvoiceNumber(String invoiceNumber); List<Invoice> findByUserId(int id); @Query(value = "select c.id,c.business_name,count(i.id) from client c join invoice i on c.id = i.client_id where i.date <= :agingDate group by c.id",nativeQuery=true) List<Invoice> findInvoiceCount(@Param("agingDate")Date agingDate); }

ReportService.java

if(report.getReportBy().equals("invoiceCount")){ result = invoiceRepository.findInvoiceCount(report.getAgingDate()); }

Invoice.java

@Entity @Table public class Invoice { @Id @GeneratedValue(strategy=GenerationType.AUTO) @Column(name="id") private int id; @ManyToOne private Client client; @Column private boolean allowPartialPay; }

Database

it comes from invoice table and the query invoked is 'select c.id,c.business_name,count(i.id) from client c join invoice i on c.id = i.client_id where i.date <= ? group by c.id' — white_cat
– white_cat, Commented Jul 1, 2017 at 8:38
@NeilStockton also i've added service from which method is called — white_cat
– white_cat, Commented Jul 1, 2017 at 8:41
Since you are running a native query return List<Object[]> from your query method. — Abdullah Mohammad Motiullah
– Abdullah Mohammad Motiullah, Commented Jul 2, 2017 at 7:19

xyz · Accepted Answer · 2017-07-02 18:11:24Z

It comes during mapping result set into java Invoice class (as you declared it as return type List for method findInvoiceCount() ). native query return in your case Object[] instead of List.

You can see it in log exception

ResultSetImpl.findColumn(ResultSetImpl.java:1162

So it happens in result mapping stage ,after query has executed.

@Query(value = "select c.id,c.business_name,count(i.id) from client c join invoice i on c.id = i.client_id where i.date <= :agingDate group by c.id",nativeQuery=true) List<Invoice> findInvoiceCount(@Param("agingDate")Date agingDate);

spring data gets result set from query result and tries to map it into Invoice field by field (try to contruct Invoice class ). But actual type it's Object[].

If you need get some DTO as result your query , with fields like is result set : 'c.id,c.business_name,count(i.id)' use @SqlResultSetMapping (you can map result columns from select query into your dto). Or change return type from List to Object[] and iterate it as you need.

Here is example for Result Set Mapping: The Basics.

Athiththan · Accepted Answer · 2023-05-13 08:40:56Z

Use, nativeQuery=false, because you have used the JQL query and not the SQL query. If you are using the SQL query, you can simply use the nativeQuery=true.

Collectives™ on Stack Overflow

sqlException : column not found JPA @query

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related