public List<AreaGovernante> buscarTodasAreaGovEstado(String estado) {
List<AreaGovernante> array = new ArrayList<AreaGovernante>();
String sql =
"SELECT governante_area.id_area, area.nome, media FROM governante_area, area, governante where governante_area.id_area = area.id_area and governante_area.id_governante = governante.id_governante and governante.estado like ? order by area.nome asc";
try {
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setString(1, estado);
ResultSet rs = stmt.executeQuery();
Governante governante =
GerenciadorGovernante.getGerenciadorGovernanteInstance()
.buscarGovernantePorEstado(estado);
while (rs.next()) {
AreaGovernante areaGovernante = new AreaGovernante();
Area area = new Area();
area.setIdArea(rs.getInt(1));
area.setNome(rs.getString(2));
areaGovernante.setArea(area);
areaGovernante.setMedia(rs.getDouble(3));
areaGovernante.setGovernante(governante);
array.add(areaGovernante);
}
stmt.close();
} catch (Exception e) {
return null;
}
return array;
}
public void atualizarMedia(AreaGovernante areaGovernante) {
String sql = "UPDATE governante_area SET media=? WHERE id_governante=? and id_area=?";
try {
PreparedStatement stmt = connection.prepareStatement(sql);
stmt = connection.prepareStatement(sql);
stmt.setFloat(1, Float.parseFloat(String.valueOf(areaGovernante.getMedia())));
stmt.setInt(2, areaGovernante.getGovernante().getIdGovernante());
stmt.setInt(3, areaGovernante.getArea().getIdArea());
stmt.execute();
stmt.close();
} catch (Exception e) {
}
}
public boolean inserirNovaMedia(AreaGovernante areaGovernante) {
String sql = "INSERT INTO governante_area(id_governante, id_area, media) VALUES (?, ?, ?);";
try {
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setInt(1, areaGovernante.getGovernante().getIdGovernante());
stmt.setInt(2, areaGovernante.getArea().getIdArea());
stmt.setFloat(3, Float.parseFloat(String.valueOf(areaGovernante.getMedia())));
stmt.execute();
stmt.close();
} catch (Exception e) {
System.out.println("Erro" + e.getMessage());
return false;
}
System.out.println("Media cadastrado com sucesso!!");
return true;
}
public boolean isEmpty(AreaGovernante areaGovernante) {
String sql = "select media from governante_area WHERE id_governante=? and id_area=?";
boolean decisao = true;
PreparedStatement stmt;
try {
stmt = connection.prepareStatement(sql);
stmt.setInt(1, areaGovernante.getGovernante().getIdGovernante());
stmt.setInt(2, areaGovernante.getArea().getIdArea());
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
decisao = false;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return decisao;
}
public double returnMedias(AreaGovernante areaGovernante) {
String sql =
"SELECT SUM(media) soma FROM governante_area WHERE id_governante = ? and id_area = ?";
double soma = 0;
PreparedStatement stmt;
try {
stmt = connection.prepareStatement(sql);
stmt.setInt(1, areaGovernante.getGovernante().getIdGovernante());
stmt.setInt(2, areaGovernante.getArea().getIdArea());
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
soma = rs.getDouble("soma");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return soma;
}