需要使用jpa@query返回自定义对象的列表

o2g1uqev  于 2021-06-18  发布在  Mysql
关注(0)|答案(3)|浏览(545)

我有一个自定义对象

@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;
    }
}

我需要使用 JpaRepository<Sale, Integer> 这个 SaleRepository 具体如下:

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

}

以下是我的控制器方法:

@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);
}

当我运行代码时,它显示以下错误:

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]

我的数据库名是正确的。当我用其他方法请求时,它工作得很好。请给我一个理想的解决方案。

yhxst69z

yhxst69z1#

1) 您需要为 SaleReport 类,该类接受在 select 陈述和顺序。
2) 更改查询,以便用 new 声明:

@Query(value = "SELECT new org.mypackage.SaleReport(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 B " +
            "INNER JOIN B.quotation C " +
            "INNER JOIN C.product D " +
            "INNER JOIN B.customer E " +
            "WHERE (B.order_date BETWEEN :from_date AND :to_date)")

3) 在我看来,这里不需要本机查询,可以直接使用hql(更改的连接)。

mzsu5hc0

mzsu5hc02#

我希望这个答案能对你有所帮助!!
sale是实体类,salereport是pojo类。

public List<SaleReport> getSaleReportByDate(@Param("from_date")String fromDate, @Param("to_date")String toDate);

不能从数据库返回pojo类列表。它将返回实体类列表。使用公共列表而不是公共列表

public List<Sale> getSaleReportByDate(@Param("from_date")String fromDate, @Param("to_date")String toDate);

@Repository
public interface SaleRepository extends JpaRepository<**Sale**, Integer>

必须将实体类列表Map到pojo类列表。
实体类列表到pojo类列表的Map:因为有很多方法可以实现这个仍然最简单的解决方案是循环实体类并将实体属性设置为pojo属性:
我不建议使用构造函数,因为有时您可能只想设置几个字段,而不是pojo类的所有字段。

Arraylist<SaleReport> sr = new Arraylist<SaleReport>();
ArrayList<Sale> saleEntityList= saleRepository.getSaleReportByDate(fromDate,toDate);
for(Sale  s : saleEntityList){
    saleReport = new SaleReport();
    saleReport.setDate(s.getDate);
    //same for all attributes
    sr.add(saleReport);
} 
return sr;

这是你的工作清单。

mo49yndu

mo49yndu3#

spring有一个名为 @SqlResultSetMapping 此注解可用于上述问题。有关此注解的详细信息,请参见以下链接:
注解类型构造函数结果
这实际上是将hibernate查询结果Map到 POJO 班级。这才是真正需要的。

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)
                    }
          )
       }
      )

@SqlResultSetMapping 在任何 @Entity 确保 datatype 从数据库返回与 POJO 班级 datatype .
希望这对你有帮助。享受:)

相关问题