package com.bigdata.org.tes;

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Set;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class App {
//	private static final String CONNECTION_URL_PROPERTY = "jdbc:impala://10.13.85.8:21050";
	private static final String CONNECTION_URL_PROPERTY = "jdbc:impala://10.13.53.29:21050/etimad";

	private static final String JDBC_DRIVER_NAME_PROPERTY = "com.cloudera.impala.jdbc4.Driver";

	public static void main(String[] args) throws IOException {
		String sqlStatement = "select * from etimad.city";
               // loadConfiguration();

		System.out.println("\n=============================================");
		System.out.println("Cloudera Impala JDBC Example");
		System.out.println("Using Connection URL: " + CONNECTION_URL_PROPERTY);
		System.out.println("Running Query: " + sqlStatement);

		Connection con = null;
		XSSFWorkbook workbook = new XSSFWorkbook();
	    Sheet mainSheet = workbook.createSheet("Report");
		try {

			Class.forName(JDBC_DRIVER_NAME_PROPERTY);
			System.out.println("Driver class found (y)");

			con = DriverManager.getConnection(CONNECTION_URL_PROPERTY);

			Statement stmt = con.createStatement();

			ResultSet rs = stmt.executeQuery(sqlStatement);
			ResultSetMetaData rsmd = rs.getMetaData();
			int numOfCols = rsmd.getColumnCount();
			System.out.println("\n== Begin Query Results ======================");
			Row headerRow = mainSheet.createRow(0);
	        // Create cells
	        for(int i = 0 ; i < numOfCols ; i ++) {
		        Cell cell = headerRow.createCell(i);
		        cell.setCellValue(rsmd.getCatalogName(i + 1));
	        }

			int i = 1;
			while(rs.next()) {
				Row row = mainSheet.createRow(i);
				for(int j = 0 ; j < numOfCols ; j ++) {
			        Cell cell = row.createCell(j);
			        cell.setCellValue(rs.getString(j + 1));
			        System.out.println(rs.getString(j + 1));
		        }
			}

			FileOutputStream fileOut = new FileOutputStream("Query Data.xlsx");
	        workbook.write(fileOut);
	        fileOut.close();

	        // Closing the workbook
	        workbook.close();


			System.out.println("== End Query Results =======================\n\n");

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (Exception e) {
				// swallow
			}
		}
	}
}