Return DB data using List / ArrayList - Java

First, we start with a class that represents a sale. This class is immutable, check out in this other answer of mine :

public final class Sale {
    private final String storeNome;
    private final int visa;
    private final int master;
    private final int diners;
    private final int amex;

    public Sale(String storeName, int visa, int mster, int diners, int amex) {
        this.storeName = storeName;
        this.visa = visa;
        this.master = master;
        this.diners = diners;
        this.amex = amex;
    }

    public String getStoreNome() {
        return storeNome;
    }

    public int getVisa() {
        return visa;
    }

    public int getMaster() {
        return master;
    }

    public int getDiners() {
        return diners;
    }

    public int getAmex() {
        return amex;
    }

    public int getTotal() {
        return visa + master + diners + amex;
    }

    @Override
    public String toString() {
        return storeNome + ";"
                + visa + ";" + master + ";" + diners + ";" + amex + ";" + getTotal();
    }
}

Once this is done, I'll borrow from that other answer a class that represents a parameter connection:

import java.sql.DriverManager;
import java.sql.SQLException;

public class ParametrosDeConexao {

    private final String url;
    private final String usuario;
    private final String senha;

    public BaseDeDados(String url, String usuario, String senha) {
        this.url = url;
        this.usuario = usuario;
        this.senha = senha;
    }

    public Connection conectar() throws SQLException {
        return DriverManager.getConnection(url, usuario, senha);
    }
}

You will have to create an instance of this class and keep it somewhere (it can be a static variable) to be able to connect to the database. The URL, user, and password setting will depend on what your database will be.

Finally, we can make the class that accesses the database:

public class SaleDAO {

    private static final String SALES_BY_DATES_SQL = ""
            + "SELECT store_nome, visa, master, diners, amex "
            + "FROM Sale "
            + "WHERE date >= ? AND date <= ?";

    private final ParametrosDeConexao params;

    public SaleDAO(ParametrosDeConexao params) {
        this.params = params;
    }

    public List<Sale> getSales(Date startDate, Date endDate) {
        try (
            Connection c = params.conectar();
            PreparedStatement ps = c.prepareStatement(SALES_BY_DATES_SQL);
        )
        {
            ps.setDate(1, startDate);
            ps.setDate(2, endDate);
            try (ResultSet rs = ps.executeQuery()) {
                List<Sale> sales = new ArrayList<>();
                while (rs.hasNext()) {
                    Sale s = new Sale(
                            rs.getString("store_nome"),
                            rs.getInt("visa"),
                            rs.getInt("master"),
                            rs.getInt("diners"),
                            rs.getInt("amex"));
                    sales.add(s);
                }
                return sales;
            }
        }
    }
}

The above code is concerned with closing the resources properly with the help of the compiler .

The above code assumes that Date also contains the time information. So if you put endDate on 10/10/2018 00:00:00, it will not pick up the sale made at 09:00 on 10/10/2018. If you do not want this behavior, leave a comment here for this answer. An alternative to this would be working with the class LocalDate or LocalDateTime .

Notice that I omitted the total field in SQL and in the Sale class. The reason is that it is always the sum of visa , master , diners and amex , so it would not have to be read from the database or stored in memory. If this is indeed the case, you could even remove it from the table.

    public List<Sale> getSales (Date startDate, Date endDate  ){

        Conection con = //de alguma forma vc pega conexao com o banco

        Statement stmt = con.createStatement();

         List<Sale> sales = new ArrayList<>();

        //aqui você recebe um objeto ResultSet com todos os elementos
        //da tabela ales:

        ResultSet rs = stmt.executeQuery("SELECT * FROM sales s WHERE  s.date BETWEEN ? AND ? ");

           stmt.setDate(1, new java.sql.Date( startDate.getTime() )) ;
           stmt.setDate(2, new java.sql.Date(endDate.getTime() ));

        //para percorrer o resultset, faca:

        while(rs.next()) {

           Sale sale = new Sale();

          //pega o valor da coluna nome, de cada linha:
          sale.setStoreName ( rs.getString("store_nome") ) ;
          sale.setVisa( rs.getString("visa") );
          sale.setMaster( rs.getString("master") ) ;
          sale.setDiners ( rs.getString("diners") );
          sale.setAmex ( rs.getString("amex") );
          sale.setTotal ( rs.getDouble("total") );

          sales.add(sale);

    }

   return  sales;

 }// end getSales

CLASS

public class  Sale {

         //por questões didáticas coloquei como String a maioria.

          private String storeName;
          private String visa ;
          private String master ;
          private String diners;
          private String amex ;
          private Double total;


     //getts e setts

}

METHOD:

public void printReport(Date initialDate, Date endDate) {
          List<Sale> sales = getSales(initialDate, endDate);//chamando getSales
          ...
}

To print the data separated by ; , considering the search of the data of the database done by the colleague up there After you have searched the data in your database or another, and have the data in a collection in java (ArrayList), just iterate the array like this:

      public void printReport(Date initialDate, Date endDate) {
        List<Sale> sales = getSales(initialDate, endDate);//chamando getSales
        StringBuilder sb = new StringBuilder();
        for(Sales sale: sales){
        sb.append(sale.getStoreName() + ";" + sale.getVisa() + ";"+sale.getMaster() + ";" + sale.getDiners() + ";" + sale.getAmex() + ";" + sale.getTotal());


    }
}
System.out.println(sb.toString());

It may be even easier if you override the toString method of the Sales class

 @override
 public String toString(){
  return storeName+";"+visa+";"+master+";"+dinners+";"+amex+";"+total;
 }

Then you would do it:

for(Sales sale: sales){
   sb.append(sale.toString());


  }

Edit: Consider the Store object as an attribute of the class Sale (Code snippets of other answers in this question)

Store class.

public class Store{
    private int id;
    private String name;
    //gets and sets
}

Class Sale

public class  Sale {

     //por questões didáticas coloquei como String a maioria.

      private Store store;
      private String visa ;
      private String master ;
      private String diners;
      private String amex ;
      private Double total;


 //getts e setts
 } 

An example of Select that would bring the DB data

private static final String SALES_BY_DATES_SQL = ""
        + "SELECT store.name store_name, store.id store_id, visa, master, diners, amex "
        + "FROM Sale, Store "
        + "WHERE date >= ? AND date <= ?"
        + " and sale.store_id = store.id";

Method that retrieves DB data

public List<Sale> getSales(Date startDate, Date endDate) {
    try (
        Connection c = params.conectar();
        PreparedStatement ps = c.prepareStatement(SALES_BY_DATES_SQL);
    )
    {
        ps.setDate(1, startDate);
        ps.setDate(2, endDate);
        try (ResultSet rs = ps.executeQuery()) {
            List<Sale> sales = new ArrayList<>();
            while (rs.hasNext()) {
                Sale s = new Sale(
                        //carregando o objeto store.
                        Store store = new Store();
                        store.setId(rs.getInt("store_id"));
                        store.setName(rs.getString("store_name"));
                        sale.setStore(store);

                        rs.getInt("visa"),
                        rs.getInt("master"),
                        rs.getInt("diners"),
                        rs.getInt("amex"));
                sales.add(s);
            }
            return sales;
        }
    }
}

Then to create the layout:

for(Sales sale: sales){
        sb.append(sale.getStore().getName() + ";" + sale.getVisa() + ";"+sale.getMaster() + ";" + sale.getDiners() + ";" + sale.getAmex() + ";" + sale.getTotal());


}

or:

 @override
 public String toString(){
  return getStore().getName()+";"+visa+";"+master+";"+dinners+";"+amex+";"+total;
 }

Edit: Whereas data is already coming right from the database:

method that formats the printout:

public StringBuilder formatarLayout(ArrayList<Sales> sales){
StirngBuilder sb = new StringBuilder();
    for(Sales sale: sales){
        sb.append(sale.getStore().getName() + ";" + sale.getVisa() + ";"+sale.getMaster() + ";" + sale.getDiners() + ";" + sale.getAmex() + ";" + sale.getTotal());


    }

    return sb;

}

printReport method

public void printReport(Date initialDate, Date endDate) {
          List<Sale> sales = getSales(initialDate, endDate);
          //isso ja retorna seus dados formatados
          String  layout = formatarLayout(sales).toString();
          // agora pode fazer o que quiser com eles
}