Get record with SQL JDBC specifications

0

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

The system is the following, the basics: I have a database with a table called 'new_table', which has a field 'id_cutomer' and 'vl_total'. I need to compute the average of 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 works, but I was wondering if I could not do more through SQL than the code itself, because I'm getting the id, values and ordering by SQL, but the average of these values I'm doing for the code.

Here is the code for the method that calculates media and orders:

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 is the class I create to test and do the media calculation:

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 the operations, and is running ok. Thank you in advance, thank you. Thanks.

    
asked by anonymous 10.05.2018 / 18:40

1 answer

1

If you want, you can achieve these values only with querys . To get the average directly from the database use the AVG function. Applying on 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 query above to the method to calculate the mean:

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 get the ordered values you can continue using the query sampled 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 .

Free translation:

  

Returns the average of the expression values.

    
10.05.2018 / 20:08