I am facing a strange behaviour when reading a view from mysql. My select statement is reading only the first row.

But it works fine when I read other db-tables.

Has someone an idea what it can be?

This is my create statement:

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

This is my Entity:

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

This is my repository:

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

This is my App:

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

This is the output of the App:

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

And finally, this are the values in the database:

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

Thank you for your help in advance.

CodePudding user response：

If you are going to use a Iterator you have to make a .hasNext() or .next() call for each row.

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

CodePudding user response：

Here I post the values of the result in my debuger, this is before I use System.out.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"