How do I create a pie chart that displays the quantity of something registered by each someone?

Question

In my project, I want to display in the pie chart how many institutions each employee registered in the system. The institutions table contains a foreign key with the id of the employee who registered the institution, so every institution has an employee associated with it.

I built the following code, but couldn't go further:

//etc

    String query = "SELECT i.ins_id, f.fun_nome FROM instituicao i " +
                "JOIN funcionario f " +
                "ON(f.fun_id = i.ins_fun_id)";

        PreparedStatement stmt = ConexaoDAO.con.prepareStatement(query,
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);

    ResultSet rs = stmt.executeQuery();

    while(rs.next()) {
        total = rs.getInt(1);
    }

    DefaultPieDataset pieDataset = new DefaultPieDataset();

    for(VFuncionarioVO vo : lista) {
        //pieDataset.setValue(vo.getNome(), );
    }

//etc

I tried using GROUP BY in the query, but that didn't solve the problem.

EDIT: actually, using the GROUP BY statement is necessary for this job. Please read the accepted answer and its commentaries.

Answer 1

You don't need the PreparedStatement. Use the org.jfree.data.jdbc.JDBCPieDataset constructor that accepts a query "that returns data in two columns, the first containing VARCHAR data, and the second containing numerical data." You can use the dataset in your ChartFactory.

JDBCPieDataset ds = new JDBCPieDataset(conn, "SELECT ...");

A complete example is shown here.