Return BD data using List/Arraylist - Java

Question

I have a problem, I set up a dynamic table in Postgresql and I need to print the lines of it, performing a method in Java, whose beginning is already pre-set. That would be with a List simple with start and end dates as a parameter.

Follow the pre-established code:

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

The output needs to be for example: Loja01;100;100;50;0;250. But I don’t know how to access the column and the store row by going through all the data.

I already have a method that prints as follows. Example: Loja01 with 3 sales (100,00 visa; 55,00 visa; 200,00 master) will print: Loja01; visa; 155// on the other line Loja01;master;200; Follows code in java:

    public void printReport (Date initialDate, Date endDate){

        List<Sale> sales = getSales (initialDate, endDate);

        for (Sale s:sales){

            Store st = s.getStore(); // Recupera a Loja

            CreditCard cc = s.getCreditCard(); // Recupera o Cartão

            System.out.println(st.getNome() + ";" + cc.getName() + ";" + s.getValor());
        }
}

Answer 1

First, we start with a class that represents a sale. This class is immutable, see the reasons in that 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();
    }
}

That done, I’ll borrow of that other answer a class representing connection parameter:

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 may be a static variable) in order 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 code above worries about close resources properly with the help of the compiler.

The above code assumes that the Date also contains the time information. Therefore if you put that the endDate for no dia 10/10/2018 00:00:00, he will not take the sale made at 09:00 of the day 10/10/2018. If you don’t want this behavior, leave a comment here on that answer. An alternative to that would be work with the class LocalDate or LocalDateTime.

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

Answer 2

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

Answer 3

To print data separated by ;, considering the data search of the database made by the colleague above After Voce searched the data from your database or another, E has 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());

can get even easier if you overwrite the toString method of the Sales class

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

So no for Voce would do:

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


  }

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

Store class.

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

Sale class

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 BD 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 retrieving data from BD

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 the data is already coming right from the database:

method that formats the print:

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
}