ResultSet GROUP_CONCAT is not recognizing the manipulated values

Question

ResultSet GROUP_CONCAT is not recognizing the manipulated values

Navigation

#1 by (0 votes)

0

I have a problem, I have no idea how to solve it.

I am running GROUP_CONCAT for a pivot table in MySQL and running by PREPARED STATEMENT . So far so good, now when I try to recover result set I can only recover fixed values for example, string or integers without operations.

I need to add a simple sum operation with integers, which is not possible because the returned values are null.

In this case, I can only retrieve the information of NameSchool, values manipulated (CAST((REPROVADO)/(APROVADO+REPROVADO) AS SIGNED)) return null .

    @SQL:

SELECT nomeEscola,
       MAX(IF(temp_graph2.anoBase = '2012', CAST((REPROVADO)/(APROVADO+REPROVADO) AS SIGNED), NULL)) AS '2012',
       MAX(IF(temp_graph2.anoBase = '2014', CAST((REPROVADO)/(APROVADO+REPROVADO) AS SIGNED), NULL)) AS '2014',
       MAX(IF(temp_graph2.anoBase = '2013', CAST((REPROVADO)/(APROVADO+REPROVADO) AS SIGNED), NULL)) AS '2013'
FROM temp_graph2
GROUP BY nomeEscola

ResultSet MYSQL:

nomeEscola  2012    2014    2013
xxxxx   0,027   {null}  {null}
xxxxx   {null}  0,109   {null}
xxxxx   {null}  {null}  0,333
xxxxx   {null}  {null}  0,222
xxxxx   {null}  {null}  {null}
xxxxx   {null}  {null}  0,083
xxxxx   {null}  {null}  {null}
xxxxx   {null}  {null}  0,3

Recuperando o ResultSet:

final StringBuffer query = new StringBuffer("PREPARE stmt FROM @sql;");
pStmt  = conn.prepareStatement(query11.toString());
pStmt.executeUpdate();        

final StringBuffer query2 = new StringBuffer("EXECUTE stmt;");
pStmt  = conn.prepareStatement(query2.toString());
rstSet = obterResultadoDaConsulta(new Object[]{}, pStmt);    

            rstSet.beforeFirst();

            int columns = metaData.getColumnCount();

            while (rstSet.next()) {

              record = new ArrayList<Object>();

                for (int i = 1; i <= columns; i++) {

                    if(i==1){
                    Object value = "'"+rstSet.getObject(i).toString().replace("\"","")+"'";
                    record.add(value);
                    }else{
                        Object value = rstSet.getObject(i);
                        record.add(value);
                    }
                }

                if (!record.contains(null)){
                al.add(record);
                }
            }

mysql java

asked by anonymous 21.08.2015 / 16:01

1 answer

Uploading Video to Youtube with Asp .net MVC CSS Animation Is Repeating

score 0 · Answer 1

By the comments, I understood that the goal is to make the sum of the Year columns in Java.

The error occurs because the record is populated with a null object. So the solution is to test if what is coming from the resultset is NULL, if it is then you should enter 0 in the record .

Tip 1

Faced with this, the reported problem is here:

Object value = rstSet.getObject(i);
record.add(value);

And one possible solution is this:

Object value = rstSet.getObject(i);
rsSet.wasNull()?record.add(0):record.add(value);

See in the JavaDoc the getObject documentation that says that if the SQL result is NULL, a null object will be returned by getObject ()

Gets the value of the designated column in the current row of this   ResultSet object as an Object in the Java programming language.

This method will return the value of the given column as a Java   object. The type of the Java object will be the default Java object   type corresponding to the column's SQL type, following the mapping for   built-in types specified in the JDBC specification. If the value is an   SQL NULL, the driver returns to Java null.

Source: link

The relevant part is in bold and free translation would be: " If the value is a SQL NULL, then the JDBC driver returns a Java null ".

Tip 2

Change your SQL and nothing else:

SELECT nomeEscola,
       MAX(IF(temp_graph2.anoBase = '2012', CAST((REPROVADO)/(APROVADO+REPROVADO) AS SIGNED), 0)) AS '2012',
       MAX(IF(temp_graph2.anoBase = '2014', CAST((REPROVADO)/(APROVADO+REPROVADO) AS SIGNED), 0)) AS '2014',
       MAX(IF(temp_graph2.anoBase = '2013', CAST((REPROVADO)/(APROVADO+REPROVADO) AS SIGNED), 0)) AS '2013'
FROM temp_graph2
GROUP BY nomeEscola

See that at the end of the IF I changed NULL by 0.