title: How to run SQL(MYSQL) stored procedure in JAVA jdbc?
body: <p>I'm a MySQL user and I have been using following statements in MySQL Workbench :
  (these statements are based on <a href="https://stackoverflow.com/questions/13400071/select-column-names-whose-entries-are-not-null">Select column names whose entries are not null</a>)</p>
  
  <pre><code>SET group_concat_max_len = 4294967295;
  
  SELECT GROUP_CONCAT(
   ' SELECT ',QUOTE(COLUMN_NAME),
   ' FROM   ( select * from table_name where s3_01 = ', coloumn1,' ) abc',
   ' WHERE `',REPLACE(COLUMN_NAME, '`', '``'),'` IS NOT NULL',
   ' HAVING COUNT(*)'
  SEPARATOR ' UNION ALL ')
  INTO   @sql
  FROM   INFORMATION_SCHEMA.COLUMNS
  WHERE  TABLE_SCHEMA = DATABASE()
     AND TABLE_NAME = 'table_name';
  
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  </code></pre>
  
  <p>Although it work in my workbench, I do not know how to make it work in java.
  for example, I made following code:</p>
  
  <pre><code>String sql1 = "SET group_concat_max_len = 4294967295;";
  String sql2 = " SELECT GROUP_CONCAT(' SELECT ',QUOTE(COLUMN_NAME), ' FROM   ( select * from ptc_weight where s3_01 = ',column1,' ) abc', ' WHERE `',REPLACE(COLUMN_NAME, '`', '``'),'` IS NOT NULL', ' HAVING COUNT(*)' SEPARATOR ' UNION ALL ') INTO   @sql FROM   INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ptc_weight'; ";
  String sql3 = " PREPARE stmt FROM @sql; ";
  String sql4 = " EXECUTE stmt;"; 
  String sql5 = " DEALLOCATE PREPARE stmt;";
  
  String[] result = getResult(sql1+sql2+sql3+sql4+sql5);
  
  public static String[][] getResult(String sql) {
          System.out.println(sql);
          Connection con = null;
          PreparedStatement ps = null;
          ResultSet rs = null;
          String[][] resultTable = null;
          try {
              con = getCon();
              ps = con.prepareStatement(sql);
              rs = ps.executeQuery();
              ResultSetMetaData result = rs.getMetaData();
              int rowNum=0;
              // Go to the last row 
              rs.last(); 
              rowNum = rs.getRow(); 
          // Reset row before iterating to get data 
              rs.beforeFirst();
              int colNum = result.getColumnCount();
  
              resultTable = new String[rowNum][colNum];
              for(int itr1=0; itr1&lt;rowNum; itr1++){
                  rs.next();
                  for(int itr2=0; itr2&lt;colNum; itr2++){
                      resultTable[itr1][itr2] =     rs.getObject(itr2+1).toString();
                  }
              }
          } catch (SQLException e) {
              // TODO Auto-generated catch block
              e.printStackTrace();
          } finally {
              dbclose(con, ps, rs);
          }// finally
          return resultTable;
      }
  </code></pre>
  
  <p>However, it does not work. I guess I made a wrong code for utilizing stored procedure, but I don't have any idea to deal with this problem.</p>