Resultset GROUP_CONCAT is not recognizing the manipulated values

Asked

Viewed 163 times

0

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

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

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

In this case I can only retrieve the information of the schoolname, the manipulated values (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);
                }
            }
java mysql

  • Are you sure that for all records returned the APPROVED and DISAPPROVED always has a value other than NULL? Remember that if one of them has NULL value, the DISAPPROVED/(APPROVED+DISAPPROVED) account will result in NULL.

  • The APPROVED and DISAPPROVED value comes from a pivot table as well ,so it always has a value for each year, and that of running in the database: Resultset MYSQL: namescola 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} xxxxx {null} {null} 0,3 .

  • Ok, if you run this SQL in Mysql directly, all records return numeric value for all schools and years?

  • No, some years have no values.

  • Ok, but no value means to display NULL in Mysql directly, correct?

  • Correct. What is not a problem for the getObject method();

  • I understand. Your question is a little confused then. Your problem is not in Java. Your problem is not being able to do the FAIL/(OK+FAIL) account, because for some columns the value produced is NULL. Returning 0 instead of NULL in Mysql would be an option?

  • Sorry if I wasn’t clear, but the problem is really in the java Prepared statement. That is, if I run @sql in mysql the result I have is what I sent, then understanding that when running this statement in java I should have the same result. Correct?

  • Thanks for the answers, but the problem does not happen due to some values retonarem null and yes pq all values of the years return null. The problem must be within GROUP_CONCAT that mounts this query: SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(temp_graph2.anoBase = ''',anoBase,'''', (DISAPPROVED)/(OK+DISAPPROVED)'', 0)) AS ','''''',anoBase,'''')) INTO @sql TEMPFROM_graph2;" It must be something that the java resultset does not interpret in this operation (DISAPPROVED)/(APPROVED+DISAPPROVED), because if I change the column that will be selected to APPROVED for example, I have the values in get

  • But you see, you switched from NULL to 0 at the end of IF. I had given this suggestion in my reply.

  • To test I did this, but as I said this is not the problem. The problem is that all values of the years are returning empty.

  • When you say "The problem must be within the GROUP_CONCAT that mounts this query:" ... You don’t claim that the problem was this, you say that must.

Show 7 more comments

1 answer

0

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

The error occurs because the record is being populated with a null Object. Thus, the solution is to test if what is coming from the resultset is NULL, if it is then you must insert 0 in the record.

Suggestion 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 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: http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#getObject%28int%29

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

Suggestion 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 IF I traded NULL for 0.

Browser other questions tagged java mysql

You are not signed in. Login or sign up in order to post.