Java - Eclipse to Excel from SQL Query

Question

Getting the results of a SQL query where I want row 5 from every record that is returned.

I would like to export the data to an Excel spreadsheet in rows of thirteen, starting at the second row (I have headers).

My SQL logic is obviously off right now because I'm getting the first result thirteen times in increasing rows and columns, although it is properly starting on the second row and only going out 13 columns. The second result repeats in this fashion, as does each successive result.

I suspect my troubles start at while (rs.next()) { for (int i = 0; i < 13; i++) {

package process;

import java.util.Scanner;
import java.io.*;
import java.sql.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFCell;



public class Sec_Completions {

    public static void main(String[] args) {
        Sec_Completions obj_Sec_Completions=new Sec_Completions();
        obj_Sec_Completions.Check_Data();
    }


public void Check_Data() {

    Scanner scanner = new Scanner(System.in);
    System.out.println("Enter the beginning date of completion  YYYY/MM/DD");
    String doc = scanner.nextLine();
    doc = doc + " 00:00";
    System.out.println("Date is" + " " + doc);




    final String sql = "SELECT DISTINCT wp_grassblade_completions.user_id, wp_grassblade_completions.status, wp_grassblade_completions.content_id, wp_usermeta.meta_key, CASE WHEN meta_value = 'male' THEN 'M' WHEN meta_value = 'female' THEN 'F' WHEN meta_value = 'Louisiana' THEN 'LA' ELSE meta_value END AS '1', wp_usermeta.meta_value, wp_usermeta.user_id, wp_grassblade_completions.timestamp\r\n" + 
            "FROM wp_grassblade_completions \r\n" + 
            "INNER JOIN wp_usermeta ON wp_grassblade_completions.user_id = wp_usermeta.user_id\r\n" + 
            "WHERE wp_grassblade_completions.timestamp >= ? AND meta_key IN ('mepr_full_name', 'mepr_address', 'mepr_city', 'mepr_state', 'mepr_zip_code', 'mepr_home_phone_with_area_code', \r\n" + 
            "  'mepr_drivers_license_or_id', 'mepr_id_state', 'mepr_LAst_four_of_social_security_number', 'mepr_date_of_birth_mmddyyyy', 'mepr_sex_mf', 'mepr_height', 'mepr_weight') AND content_id IN ('1575, 642, 1580') \r\n" + 
            "ORDER BY wp_grassblade_completions.timestamp, content_id, wp_usermeta.user_id";
Class.forName("com.mysql.jdbc.Driver");
        Connection connection=DriverManager.getConnection("jdbc:mysql://wpengine.com:3306/wp_database","user", "passsword");
        PreparedStatement ps =null;
        ps=connection.prepareStatement(sql);
        ps.setString(1, doc);

        Statement st = connection.createStatement();
        ResultSet rs = ps.executeQuery();

        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("Security");
        XSSFRow rowhead = sheet.createRow((short) 0);

int index = 1;
            while (rs.next()) {
                for (int i = 0; i < 13; i++) {

             XSSFRow row = sheet.createRow((short) index);
             row.createCell((short) i).setCellValue(rs.getString(5));
             index++;
 }
            }

  FileOutputStream fileOut = new FileOutputStream("D://OneDrive//ABSEC//ATC_Reporting//expdata1.xlsx");
  wb.write(fileOut);
  fileOut.close();
  System.out.println("Data is saved in excel file.");
  rs.close();
  connection.close();


    } catch (Exception e) {
        System.out.println(e);
    }

Answer 1

Edited, again

Try something like this:

// META_KEY values, in the order that the columns should be. This should be a
// private static final outside of in the class, along with the query's text...
String[] keys = { "mepr_full_name", "mepr_address", "mepr_city", "mepr_state",
                  "mepr_zip_code", "mepr_home_phone_with_area_code",
                  "mepr_drivers_license_or_id", "mepr_id_state",
                  "mepr_LAst_four_of_social_security_number", "mepr_date_of_birth_mmddyyyy",
                  "mepr_sex_mf", "mepr_height", "mepr_weight" };

// Running thru the ResultSet
short index = 1;
while ( rs.next() )
{
   XSSFRow row = sheet.createRow( index );
   String key = rs.getString(4); // meta_key
   String value = rs.getString( 5 ); // meta_value
   for ( short i = 0; i < keys.length; ++i )
   {
      if ( keys[i].equals( key)) 
      {
         // Retrieving cell, creating if not exists
         XSSFCell cell = row.getCell( i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK );
         // if the cell is used, increment index o get to the next row
         if ( cell.getCellTypeEnum() != CellType.BLANK )
            ++index;
         // Set the value
         cell.setCellValue( value );
         // No need to walk thru the rest of the array...
         break;
      }
   }
}

This only works, tho, if any "next row" does not start with a column that has no value in the previous row...
For this reason you should also add meta_key to the ORDER BY clause...
With some additional cleanup, something like this:

SELECT DISTINCT wpgc.user_id, wpgc.status, wpgc.content_id, wpu.meta_key,
                CASE wpu.meta_value WHEN 'male' THEN 'M' WHEN 'female' THEN 'F'
                                    WHEN 'Louisiana' THEN 'LA' ELSE wpu.meta_value END AS '1',
                wpu.user_id, wpgc.timestamp
FROM wp_grassblade_completions AS wpgc
JOIN wp_usermeta AS wpu ON wpgc.user_id = wpu.user_id
WHERE wpgc.timestamp >= ?
  AND wpu.meta_key IN ('mepr_full_name', 'mepr_address', 'mepr_city', 'mepr_state',
                       'mepr_zip_code', 'mepr_home_phone_with_area_code',
                       'mepr_drivers_license_or_id', 'mepr_id_state',
                       'mepr_LAst_four_of_social_security_number',
                       'mepr_date_of_birth_mmddyyyy', 'mepr_sex_mf', 'mepr_height',
                       'mepr_weight')
  AND content_id IN ('1575, 642, 1580')
ORDER BY wpgc.timestamp, wpgc.content_id, wpu.user_id, wpu.meta_key