I've a custom object
@Component
public class SaleReport {
private Date date;
private String customerName;
private String phoneNo;
private String productName;
private Integer quantity;
private Double totalAmount;
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
public String getCustomerName() {
return customerName;
}
public void setCustomerName(String customerName) {
this.customerName = customerName;
}
public String getPhoneNo() {
return phoneNo;
}
public void setPhoneNo(String phoneNo) {
this.phoneNo = phoneNo;
}
public String getProductName() {
return productName;
}
public void setProductName(String productName) {
this.productName = productName;
}
public Integer getQuantity() {
return quantity;
}
public void setQuantity(Integer quantity) {
this.quantity = quantity;
}
public Double getTotalAmount() {
return totalAmount;
}
public void setTotalAmount(Double totalAmount) {
this.totalAmount = totalAmount;
}
}
I need to return List of this object using a JpaRepository<Sale, Integer>
The SaleRepository
is as follow:
@Repository
public interface SaleRepository extends JpaRepository<Sale, Integer> {
@Query(value = "SELECT B.order_date AS date, E.customer_name AS customerName, " +
"E.phone_no AS phoneNo, D.product_name AS productName, C.quantity AS quantity, " +
"C.total_price AS totalAmount " +
"FROM SALE A " +
"INNER JOIN A.quotation_id B " +
"INNER JOIN B.quotation_id C " +
"INNER JOIN C.product_id D " +
"INNER JOIN B.customer_id E " +
"WHERE (B.order_date BETWEEN :from_date AND :to_date)", nativeQuery = true)
public List<SaleReport> getSaleReportByDate(@Param("from_date")String fromDate, @Param("to_date")String toDate);
}
Here is my Controller method:
@GetMapping("api/report/sale/{from_date}/{to_date}")
public List<SaleReport> getSaleReportByDate(@PathVariable("from_date") String fromDate, @PathVariable("to_date") String toDate){
return saleRepository.getSaleReportByDate(fromDate, toDate);
}
While I run the code it shows this error:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown database 'a'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_161]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_161]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_161]
at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_161]
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) ~[mysql-connector-java-5.1.47.jar:5.1.47]
at com.mysql.jdbc.Util.getInstance(Util.java:408) ~[mysql-connector-java-5.1.47.jar:5.1.47]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944) ~[mysql-connector-java-5.1.47.jar:5.1.47]
My DATABASE NAME is alright. It works perfectly while I request in other methods. Please suggest me an ideal solution.
Spring has an annotation called @SqlResultSetMapping
This annotation can be used for above problem. Details about this annotation can be found from following link:
Annotation Type ConstructorResult
This is actually mapping a hibernate query result into a plain POJO
Class. Thats what actually needed.
Example:
Query q = em.createNativeQuery(
"SELECT c.id, c.name, COUNT(o) as orderCount, AVG(o.price) AS avgOrder " +
"FROM Customer c, Orders o " +
"WHERE o.cid = c.id " +
"GROUP BY c.id, c.name",
"CustomerDetailsResult");
@SqlResultSetMapping(
name="CustomerDetailsResult",
classes={
@ConstructorResult(
targetClass=com.acme.CustomerDetails.class,
columns={
@ColumnResult(name="id"),
@ColumnResult(name="name"),
@ColumnResult(name="orderCount"),
@ColumnResult(name="avgOrder", type=Double.class)
}
)
}
)
Put @SqlResultSetMapping
into top of any @Entity
and make sure the datatype
returning from database is same as your POJO
Class datatype
.
Hope this will help you. Enjoy :)