com.svi.uzabase.logic.ExtractData.java Source code

Introduction

Here is the source code for com.svi.uzabase.logic.ExtractData.java

Source

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.svi.uzabase.logic;

import com.svi.uzabase.frames.MainFrame;
import com.svi.uzabase.objects.SchemaFields;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.Callable;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.ss.usermodel.Workbook;

/**
 *
 * @author Dan
 */
public class ExtractData implements Callable<Map<String, List<?>>> {

    private final String MDB_PATH = "Databases\\dbASIAPAC_FSGIS_SCHEMA_V2.mdb";
    private final String COMPANY_EXCEL_PATH = "Databases\\Company Names.xls";
    MainFrame mf;

    public ExtractData(MainFrame mf) {
        this.mf = mf;
    }

    private Map<String, List<?>> getLists() {
        Map<String, List<?>> map = new HashMap();
        SchemaFields sf;
        try {
            mf.setJprogressText("Reading database");
            Connection conn;
            String connURL = "jdbc:ucanaccess://" + MDB_PATH;
            conn = DriverManager.getConnection(connURL, "", "");
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
            System.out.println("Reading database fields...");
            map.put("schema", getFieldValues(conn));
            System.out.println("Reading database provinces...");
            map.put("provinces", getProvinces(conn));
            System.out.println("Reading database cities...");
            map.put("cities", getCities(conn));
            System.out.println("Reading database nationalities...");
            map.put("nationality", getNationality(conn));

            //Closes db connection
            conn.close();
            map.put("company", extractCompanyNames());
            System.out.println("Extracting company names...");

        } catch (ClassNotFoundException | SQLException ex) {
            Logger.getLogger(ExtractData.class.getName()).log(Level.SEVERE, null, ex);
        }
        return map;
    }

    private List<String> extractCompanyNames() {
        List<String> companyList = new ArrayList<>();
        try {
            FileInputStream inputStream = new FileInputStream(new File(COMPANY_EXCEL_PATH));
            Workbook workbook = new HSSFWorkbook(inputStream);
            Row row;
            Cell cell;
            Sheet sheet;
            sheet = workbook.getSheetAt(0);
            for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                row = sheet.getRow(rowIndex);
                for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                    cell = row.getCell(colIndex);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    if (colIndex == 1) {
                        companyList.add(cell.getStringCellValue().toUpperCase());
                    }
                }
            }
            //Closes opened documents
            inputStream.close();
            workbook.close();
        } catch (IOException ex) {
            Logger.getLogger(ExtractData.class.getName()).log(Level.SEVERE, null, ex);
        }
        return companyList;
    }

    private List<SchemaFields> getFieldValues(Connection conn) throws SQLException {
        List<SchemaFields> schemaFields = new ArrayList<>();
        Statement stmt;
        ResultSet rs;
        SchemaFields sf;
        stmt = conn.createStatement();
        stmt.execute("SELECT FieldNo, FieldName FROM tblFields;");
        rs = stmt.getResultSet();
        while (rs.next()) {
            sf = new SchemaFields();
            sf.setFieldNo(rs.getInt("FieldNo"));
            sf.setFieldName(rs.getString("FieldName"));
            schemaFields.add(sf);
        }
        rs.close();
        stmt.close();
        return schemaFields;
    }

    private List<String> getProvinces(Connection conn) throws SQLException {
        List<String> provinceHolder = new ArrayList<>();
        Statement stmt;
        ResultSet rs;
        stmt = conn.createStatement();
        stmt.execute("SELECT DISTINCT Province FROM tblProvince;");
        rs = stmt.getResultSet();
        while (rs.next()) {
            provinceHolder.add(rs.getString("Province"));
        }
        rs.close();
        stmt.close();
        return provinceHolder;
    }

    private List<String> getCities(Connection conn) throws SQLException {
        List<String> cityHolder = new ArrayList<>();
        Statement stmt;
        ResultSet rs;
        stmt = conn.createStatement();
        stmt.execute("SELECT DISTINCT City_Town FROM tblProvince;");
        rs = stmt.getResultSet();
        while (rs.next()) {
            cityHolder.add(rs.getString("City_Town"));
        }
        rs.close();
        stmt.close();
        return cityHolder;
    }

    private List<String> getNationality(Connection conn) throws SQLException {
        List<String> cityHolder = new ArrayList<>();
        Statement stmt;
        ResultSet rs;
        stmt = conn.createStatement();
        stmt.execute("SELECT DISTINCT Nationality FROM tblNationality;");
        rs = stmt.getResultSet();
        while (rs.next()) {
            cityHolder.add(rs.getString("Nationality"));
        }
        rs.close();
        stmt.close();
        return cityHolder;
    }

    @Override
    public Map<String, List<?>> call() throws Exception {
        return getLists();
    }
}