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.