MySQL上的JavaSpringBootJPA查询只读取第一行

Question

从mysql读取视图时，我面临一种奇怪的行为。 我的select语句只读取第一行

但是当我阅读其他db表时，它工作得很好

有人知道它是什么吗

这是我的create语句：

CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW v_monthlyexpense AS select distinct _v.customerid AS customerid,_o.createddatetime AS createddatetime,_o.pricegross AS pricegross from ((c_visit _v join c_visit_order _vo on(_vo.visitId = _v.id)) join c_order _o on(_o.id = _vo.orderId));

这是我的实体：

import javax.persistence.*;
import java.sql.Timestamp;

@Entity
@Immutable
@Table(name="v_monthlyexpense")
public class View_MonthlyExpense {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name="customerid", nullable = false, unique = true)
    private Long customerId;

    @Column(name="createddatetime")
    private Timestamp createdDateTime;

    @Column(name="pricegross")
    private float priceGross;

    protected View_MonthlyExpense() {
    }

    public View_MonthlyExpense(Long customerId, Timestamp createdDateTime, float priceGross) {
        this.customerId = customerId;
        this.createdDateTime = createdDateTime;
        this.priceGross = priceGross;
    }

    public Long getCustomerId() {
        return customerId;
    }

    public Timestamp getCreatedDateTime() {
        return createdDateTime;
    }

    public float getPriceGross() {
        return priceGross;
    }

    @Override
    public String toString() {
        return customerId + ". " + createdDateTime + " - " + priceGross + " USD";
    }
}

这是我的存储库：

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

public interface ExpenseRepository2 extends CrudRepository<View_MonthlyExpense, Long> {
    @Query("SELECT DISTINCT e FROM View_MonthlyExpense e")
    public List<View_MonthlyExpense> findAll();
}

这是我的应用程序：

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class ExpenseApp implements CommandLineRunner {

    @Autowired
    ExpenseRepository2 repository2;

    public static void main(String[] args) {
        SpringApplication.run(ExpenseApp.class, args);
    }

    @Override
    public void run(String... args) throws Exception {

        List<View_MonthlyExpense> list =  repository2.findAll();

        List<View_MonthlyExpense> iter = repository2.findAll();
        iter.forEach(item -> System.out.print(item + "\n"));
    }
}

这是应用程序的输出：

1002. 2021-11-24 22:38:55.0 - 8.0 USD
1003. 2021-11-24 22:38:55.0 - 8.0 USD
1004. 2021-11-24 22:38:55.0 - 8.0 USD
1005. 2021-11-24 22:38:55.0 - 8.0 USD
1006. 2021-11-24 22:38:55.0 - 8.0 USD
1007. 2021-11-24 22:38:55.0 - 8.0 USD
1008. 2021-11-24 22:38:55.0 - 8.0 USD
1009. 2021-11-26 23:17:03.0 - 12.0 USD
1010. 2021-11-26 23:17:03.0 - 12.0 USD
1011. 2021-11-26 23:17:03.0 - 12.0 USD
1012. 2021-11-26 23:17:03.0 - 12.0 USD
1013. 2021-11-26 23:17:03.0 - 12.0 USD
1014. 2021-11-26 23:17:03.0 - 12.0 USD
1015. 2021-11-26 23:17:03.0 - 12.0 USD
1016. 2021-11-26 23:17:03.0 - 12.0 USD
1017. 2021-11-26 23:17:03.0 - 12.0 USD

最后，这是数据库中的值：

customerId  createdDateTime     priceGross
1002        24.11.2021 22:38    8
1002        24.11.2021 22:38    10
1002        24.11.2021 22:38    6
1002        25.11.2021 00:46    6
1002        25.11.2021 00:46    10
1002        25.11.2021 00:46    11
1002        25.11.2021 00:46    15
1004        26.11.2021 23:17    12
1004        26.11.2021 23:17    8
1004        26.11.2021 23:17    10
1004        27.11.2021 02:06    12
1004        27.11.2021 02:06    8
1004        27.11.2021 02:06    6
1004        27.11.2021 02:06    30
1004        27.11.2021 02:06    15
1004        27.11.2021 02:06    50

在这里，我将结果的值发布到我的调试程序中，这是在我使用系统之前。出来println:

iter = {ArrayList@7390}  size = 16
 0 = {View_MonthlyExpense@7395} "1002. 2021-11-24 22:38:55.0 - 8.0 USD"
 1 = {View_MonthlyExpense@7395} "1002. 2021-11-24 22:38:55.0 - 8.0 USD"
 2 = {View_MonthlyExpense@7395} "1002. 2021-11-24 22:38:55.0 - 8.0 USD"
 3 = {View_MonthlyExpense@7395} "1002. 2021-11-24 22:38:55.0 - 8.0 USD"
 4 = {View_MonthlyExpense@7395} "1002. 2021-11-24 22:38:55.0 - 8.0 USD"
 5 = {View_MonthlyExpense@7395} "1002. 2021-11-24 22:38:55.0 - 8.0 USD"
 6 = {View_MonthlyExpense@7395} "1002. 2021-11-24 22:38:55.0 - 8.0 USD"
 7 = {View_MonthlyExpense@7396} "1004. 2021-11-26 23:17:03.0 - 12.0 USD"
 8 = {View_MonthlyExpense@7396} "1004. 2021-11-26 23:17:03.0 - 12.0 USD"
 9 = {View_MonthlyExpense@7396} "1004. 2021-11-26 23:17:03.0 - 12.0 USD"
 10 = {View_MonthlyExpense@7396} "1004. 2021-11-26 23:17:03.0 - 12.0 USD"
 11 = {View_MonthlyExpense@7396} "1004. 2021-11-26 23:17:03.0 - 12.0 USD"
 12 = {View_MonthlyExpense@7396} "1004. 2021-11-26 23:17:03.0 - 12.0 USD"
 13 = {View_MonthlyExpense@7396} "1004. 2021-11-26 23:17:03.0 - 12.0 USD"
 14 = {View_MonthlyExpense@7396} "1004. 2021-11-26 23:17:03.0 - 12.0 USD"
 15 = {View_MonthlyExpense@7396} "1004. 2021-11-26 23:17:03.0 - 12.0 USD"

提前谢谢你的帮助

Answer 1

如果您要使用迭代器，您必须创建一个。hasNext（）或。为每行调用next（）

while(it.hasNext()) {
     System.out.println(it.next());
}