Catch record with JDBC SQL specification

Question

Hello, good afternoon, sir. I’m having to develop a Java system with Mysql. It’s simple, just the part of the backend, but I’m inciante and had not run querys with java before hehe.

The system is the following, the basic: I have a database with a table called 'new_table', which has an 'id_cutomer' and 'vl_total' field. I need to calculate the average of the records with 'vl_total' greater than 560 and 'id_cutomer' between 1500 and 2700 , and list the values in descending order.

I made the system and it’s working, but I wanted to know if there would be no way to do more by SQL than by the code itself, because I’m taking the id, values and ordering by SQL but the average of these values I’m doing by code.

Follows the code of the method that calculates media and sorts:

public List<Cliente> calculaMedia() throws Exception{
    String sql = "SELECT id_cutomer,vl_total FROM new_table WHERE (vl_total > 560) AND (id_cutomer > 1500  AND id_cutomer < 2700 ) ORDER BY vl_total DESC";


    List<Cliente> clientes = new ArrayList<Cliente>();

    Connection conn = null;
    PreparedStatement pstm = null;
    ResultSet rset = null;

    try {
        conn = ConnectionFactory.createConnectionToMySQL();

        pstm = conn.prepareStatement(sql);

        rset = pstm.executeQuery();

        //Enquanto existir dados no banco de dados, faça
        while(rset.next()){

            Cliente cliente = new Cliente();

            cliente.setId(rset.getInt("id_cutomer"));

            cliente.setValorTotal(rset.getDouble("vl_total"));

            clientes.add(cliente);
        }
    }catch(Exception e) {
        System.out.println("Nao pegou ultimo id!");
        e.printStackTrace();
    }finally{
        try{
            if(rset != null){
                rset.close();
            }

            if(pstm != null){
                pstm.close();
            }

            if(conn != null){
                conn.close();
            }

    }catch(Exception e){

        e.printStackTrace();
    }   
    }
    return clientes;
}

And here’s the class I create to test and calculate the media:

public List<Cliente> calculaMedia() throws Exception{
    String sql = "SELECT id_cutomer,vl_total FROM new_table WHERE (vl_total > 560) AND (id_cutomer > 1500  AND id_cutomer < 2700 ) ORDER BY vl_total DESC";


    List<Cliente> clientes = new ArrayList<Cliente>();

    Connection conn = null;
    PreparedStatement pstm = null;
    ResultSet rset = null;

    try {
        conn = ConnectionFactory.createConnectionToMySQL();

        pstm = conn.prepareStatement(sql);

        rset = pstm.executeQuery();

        //Enquanto existir dados no banco de dados, faça
        while(rset.next()){

            Cliente cliente = new Cliente();

            cliente.setId(rset.getInt("id_cutomer"));

            cliente.setValorTotal(rset.getDouble("vl_total"));

            clientes.add(cliente);
        }
    }catch(Exception e) {
        System.out.println("Nao pegou ultimo id!");
        e.printStackTrace();
    }finally{
        try{
            if(rset != null){
                rset.close();
            }

            if(pstm != null){
                pstm.close();
            }

            if(conn != null){
                conn.close();
            }

    }catch(Exception e){

        e.printStackTrace();
    }   
    }
    return clientes;
}

NOTE: The table has enough records for operations, and is running ok. Thank you in advance. Hug.

Answer 1

If you want, you can achieve these values only with querys. To get the average directly from the database use the function AVG. Applying in your query:

SELECT AVG(nt.vl_total) AS media
  FROM new_table nt
 WHERE nt.vl_total > 560
   AND nt.id_cutomer > 1500
   AND nt.id_cutomer < 2700

Applying the query above in the method for calculating the average:

public Double avg() throws Exception {
  String query = "SELECT AVG(nt.vl_total) AS media" +
               "\n  FROM new_table nt" +
               "\n WHERE nt.vl_total > 560" +
               "\n   AND nt.id_cutomer > 1500" +
               "\n   AND nt.id_cutomer < 2700";
  Double resultado;

  // AQUI VAI A CONEXÃO COM O BANCO E EXECUÇÃO DA QUERY
  // ...

  rset.next();
  resultado = rset.getDouble("media");

  // FECHAR A CONEXÃO E DEMAIS OPERAÇÕES
  // ...

  return resultado;
}

To achieve the ordered values you can continue using the query exemplified in the question:

SELECT nt.id_cutomer,
       nt.vl_total
  FROM new_table nt
 WHERE nt.vl_total > 560
   AND nt.id_cutomer > 1500
   AND nt.id_cutomer < 2700
 ORDER BY nt.vl_total DESC

---

AVG

Returns the Average value of expr.

In free translation:

Returns the mean of the values of expression.