Boa tarde,
Bem galera, eu iniciei a pouco tempo em Java, e eu estou querendo fazer um relatório no iReport da seguinte forma:
- A partir de uma classe (TotalOcorrencia) que nela tem: Ano, Mes e Count.
- Com esses dados eu queria retornar para uma lista a contagem de todas as ocorrencias feitas no dia ao percorrer do mês, resumindo seria uma lista com tamanho 30, contendo em cada posição o total de ocorrencia do dia correspondente.
Se ficou dificil de entender por favor avisem.
Segue abaixo o que já foi feito:
public static List<totalocorrencia> listarCount(int ano, int mes) {
SQL.conectar();
List<totalocorrencia> lista = new ArrayList<totalocorrencia>();
Statement stmt;
ResultSet rs;
String query = "SELECT count(*) as dia1,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=2) as dia2,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=3) as dia3,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=4) as dia4,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=5) as dia5,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=6) as dia6,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=7) as dia7,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=8) as dia8,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=9) as dia9,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=10) as dia10,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=11) as dia11,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=12) as dia12,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=13) as dia13,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=14) as dia14,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=15) as dia15,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=16) as dia16,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=17) as dia17,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=18) as dia18,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=19) as dia19,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=20) as dia20,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=21) as dia21,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=22) as dia22,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=23) as dia23,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=24) as dia24,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=25) as dia25,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=26) as dia26,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=27) as dia27,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=28) as dia28,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=29) as dia29,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=30) as dia30,"
+ "(SELECT count(*)FROM ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=31) as dia31"
+ "FROM"
+ "ocorrencias WHERE year(datahr)=" + ano + " and month(datahr)=" + mes + " and day(datahr)=1 ;";
int i = 1;
try {
stmt = SQL.conn.createStatement();
rs = stmt.executeQuery(query);
lista.add(new totalocorrencia(rs.getInt("dia1")));
lista.add(new totalocorrencia(rs.getInt("dia2")));
lista.add(new totalocorrencia(rs.getInt("dia3")));
lista.add(new totalocorrencia(rs.getInt("dia4")));
lista.add(new totalocorrencia(rs.getInt("dia5")));
lista.add(new totalocorrencia(rs.getInt("dia6")));
lista.add(new totalocorrencia(rs.getInt("dia7")));
lista.add(new totalocorrencia(rs.getInt("dia8")));
lista.add(new totalocorrencia(rs.getInt("dia9")));
lista.add(new totalocorrencia(rs.getInt("dia10")));
lista.add(new totalocorrencia(rs.getInt("dia11")));
lista.add(new totalocorrencia(rs.getInt("dia12")));
lista.add(new totalocorrencia(rs.getInt("dia13")));
lista.add(new totalocorrencia(rs.getInt("dia14")));
lista.add(new totalocorrencia(rs.getInt("dia15")));
lista.add(new totalocorrencia(rs.getInt("dia16")));
lista.add(new totalocorrencia(rs.getInt("dia17")));
lista.add(new totalocorrencia(rs.getInt("dia18")));
lista.add(new totalocorrencia(rs.getInt("dia19")));
lista.add(new totalocorrencia(rs.getInt("dia20")));
lista.add(new totalocorrencia(rs.getInt("dia21")));
lista.add(new totalocorrencia(rs.getInt("dia22")));
lista.add(new totalocorrencia(rs.getInt("dia23")));
lista.add(new totalocorrencia(rs.getInt("dia24")));
lista.add(new totalocorrencia(rs.getInt("dia25")));
lista.add(new totalocorrencia(rs.getInt("dia26")));
lista.add(new totalocorrencia(rs.getInt("dia27")));
lista.add(new totalocorrencia(rs.getInt("dia28")));
lista.add(new totalocorrencia(rs.getInt("dia29")));
lista.add(new totalocorrencia(rs.getInt("dia30")));
lista.add(new totalocorrencia(rs.getInt("dia31")));
} catch (SQLException ex) {
ex.printStackTrace();
}
return lista;
}
public static void main(String[] args) {
List<totalocorrencia> lista = listarCount(2016, 9);
for (int i = 0; i < lista.size(); i++) {
System.out.println(lista.get(i));
}
}
Aqui está o Output:
> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE year(datahr)=2016 and month(datahr)=9 and day(datahr)=1' at line 1 > at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) > at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) > at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) > at java.lang.reflect.Constructor.newInstance(Constructor.java:423) > at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) > at com.mysql.jdbc.Util.getInstance(Util.java:408) > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943) > at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3970) > at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906) > at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524) > at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2677) > at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545) > at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2503) > at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1369) > at br.com.samu.main.listarCount(main.java:55) > at br.com.samu.main.main(main.java:95)
