zadanie1

import java.sql.*;

/**
 * Created by Jakubek on 2017-07-12.
 */
public class zadanie {
    //todo pobieranie danych z tabeli na bieżco, optymalizacja działnia całego programu
    private String stopName[] = getDataString("C:\\Users\\Jakubek\\Desktop\\NewagIP\\Wojtek\\Zadanie 1\\Baza danych\\passcount.sqlite",
            "recorded_course_stops",
            "stop_name");

    private int stopGlobalId[] = getDataInt("C:\\Users\\Jakubek\\Desktop\\NewagIP\\Wojtek\\Zadanie 1\\Baza danych\\passcount.sqlite",
            "recorded_course_stops",
            "stop_global_id");

    private int courseRowId[] = getDataInt("C:\\Users\\Jakubek\\Desktop\\NewagIP\\Wojtek\\Zadanie 1\\Baza danych\\passcount.sqlite",
            "recorded_course_stops",
            "course_rowid");

    private int firstEventRowId[] = getDataInt("C:\\Users\\Jakubek\\Desktop\\NewagIP\\Wojtek\\Zadanie 1\\Baza danych\\passcount.sqlite",
            "recorded_course_stops",
            "first_event_rowid");

    private int lastEventRowId[] = getDataInt("C:\\Users\\Jakubek\\Desktop\\NewagIP\\Wojtek\\Zadanie 1\\Baza danych\\passcount.sqlite",
            "recorded_course_stops",
            "last_event_rowid");


    //tutaj jest import.
    private int eventCode[] = getDataInt("C:\\Users\\Jakubek\\Desktop\\NewagIP\\Wojtek\\Zadanie 1\\Baza danych\\passcount.sqlite",
            "recorded_events",
            "event_code");



    private int eventTimeUnix[] = getDataInt("C:\\Users\\Jakubek\\Desktop\\NewagIP\\Wojtek\\Zadanie 1\\Baza danych\\passcount.sqlite",
            "recorded_events",
            "event_time_unix");

    private int eventSubCode[] = getDataInt("C:\\Users\\Jakubek\\Desktop\\NewagIP\\Wojtek\\Zadanie 1\\Baza danych\\passcount.sqlite",
            "recorded_events",
            "event_subcode");

    private int stopRowIdFromStopsTable[] = getDataInt("C:\\Users\\Jakubek\\Desktop\\NewagIP\\Wojtek\\Zadanie 1\\Baza danych\\passcount.sqlite",
            "recorded_course_stops",
            "stop_rowid");

    private int stopRowId[] = getDataInt("C:\\Users\\Jakubek\\Desktop\\NewagIP\\Wojtek\\Zadanie 1\\Baza danych\\passcount.sqlite",
            "recorded_state",
            "stop_rowid");

    private int trainNumber[] = getDataInt("C:\\Users\\Jakubek\\Desktop\\NewagIP\\Wojtek\\Zadanie 1\\Baza danych\\passcount.sqlite",
            "recorded_courses",
            "train_number");

    private int distanceSincePowerUp[] = getDataInt("C:\\Users\\Jakubek\\Desktop\\NewagIP\\Wojtek\\Zadanie 1\\Baza danych\\passcount.sqlite",
            "recorded_state",
            "distance_since_power_up_meters");

    private int distanceSinceProduced[] = getDataInt("C:\\Users\\Jakubek\\Desktop\\NewagIP\\Wojtek\\Zadanie 1\\Baza danych\\passcount.sqlite",
            "recorded_state",
            "vehicle_lifelong_distance_meters");

    private double gps1Longitude[] = getDataDouble("C:\\Users\\Jakubek\\Desktop\\NewagIP\\Wojtek\\Zadanie 1\\Baza danych\\passcount.sqlite",
            "recorded_state",
            "gps1_lon");

    private double gps1Latitude[] = getDataDouble("C:\\Users\\Jakubek\\Desktop\\NewagIP\\Wojtek\\Zadanie 1\\Baza danych\\passcount.sqlite",
            "recorded_state",
            "gps1_lat");

    private double gps2Longitude[] = getDataDouble("C:\\Users\\Jakubek\\Desktop\\NewagIP\\Wojtek\\Zadanie 1\\Baza danych\\passcount.sqlite",
            "recorded_state",
            "gps2_lon");

    private double gps2Latitude[] = getDataDouble("C:\\Users\\Jakubek\\Desktop\\NewagIP\\Wojtek\\Zadanie 1\\Baza danych\\passcount.sqlite",
            "recorded_state",
            "gps2_lat");

    private String[] result;

    zadanie() {    }

    private String[] getDataString(String url, String from, String select){
        int i=0;
        //TODO Dynamiczna alojacja pamięci
        String[] result = new String[500000];
        try{
            Class.forName("org.sqlite.JDBC");
            Connection connection = DriverManager.getConnection("jdbc:sqlite:"+url);
            Statement stmt  = connection.createStatement();
            ResultSet rs    = stmt.executeQuery("SELECT " + select + " FROM "+ from);
            while (rs.next()){
                result[i]=rs.getString(select);
                i++;
            }
            System.out.println("Pobrano z tabeli: "+from+" Dane dot.: "+select);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println("Wielbłąd!" + e.getMessage());
            e.printStackTrace();
        }
        return result;
    }

    private int[] getDataInt(String url, String from, String select){
        int i=0;
        //TODO Dynamiczna alojacja pamięci
        int[] result = new int[5000000];
        try{
            Class.forName("org.sqlite.JDBC");
            Connection connection = DriverManager.getConnection("jdbc:sqlite:"+url);
            Statement stmt  = connection.createStatement();
            ResultSet rs    = stmt.executeQuery("SELECT " + select + " FROM "+ from);
            while (rs.next()){
                result[i]=rs.getInt(select);
                i++;
            }
            System.out.println("Pobrano z tabeli: "+from+" Dane dot.: "+select);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println("Wielbłąd!" + e.getMessage());
            e.printStackTrace();
        }
        return result;
    }

    private double[] getDataDouble(String url, String from, String select){
        int i=0;
        //TODO Dynamiczna alojacja pamięci
        double[] result = new double[5000000];
        try{
            Class.forName("org.sqlite.JDBC");
            Connection connection = DriverManager.getConnection("jdbc:sqlite:"+url);
            Statement stmt  = connection.createStatement();
            ResultSet rs    = stmt.executeQuery("SELECT " + select + " FROM "+ from);
            while (rs.next()){
                result[i]=rs.getDouble(select);
                i++;
            }
            System.out.println("Pobrano z tabeli: "+from+" Dane dot.: "+select);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println("Wielbłąd!" + e.getMessage());
            e.printStackTrace();
        }
        return result;
    }

    public String getStopName(int lp){
        return stopName[lp];
    }

    public int getStopGlobalId(int lp){
        return stopGlobalId[lp];
    }

    public int getStopRowId(int lp){
        return stopRowIdFromStopsTable[lp];
    }

    public int getCourseRowId(int lp){
        return courseRowId[lp];
    }

    public int getCourseRowMaxId(){
        int max=0;
        for (int i = 1; i < courseRowId.length; i++) {
            if (courseRowId[i] > max) {
                max = courseRowId[i];
            }
        }
        return max;
    }

    public int getTrainNumber(int lp){
        return trainNumber[lp];
    }

    public int getDistanceSincePowerUpForEachStation(int lp){
     int distance = 0;
        for (int i=0;i<stopRowId.length;i++) {
            if (stopRowId[i] == lp) {
                distance=distanceSincePowerUp[i];
            }
        }
     return distance;
    }

    public int getDistanceSinceProduced(int lp){
        int distance = 0;
        for (int i=0;i<stopRowId.length;i++) {
            if (stopRowId[i] == lp) {
                distance=(distanceSinceProduced[i])/1000;
            }
        }
        return distance;
    }

    public double getGps1Longitude(int lp){
        double gps = 0;
        for (int i=0;i<stopRowId.length;i++) {
            if (stopRowId[i] == lp) {
                gps=(gps1Longitude[i]);
            }
        }
        return gps;
    }

    public double getGps1Latitude(int lp){
        double gps = 0;
        for (int i=0;i<stopRowId.length;i++) {
            if (stopRowId[i] == lp) {
                gps=(gps1Latitude[i]);
            }
        }
        return gps;
    }

    public double getGps2Longitude(int lp){
        double gps = 0;
        for (int i=0;i<stopRowId.length;i++) {
            if (stopRowId[i] == lp) {
                gps=(gps2Longitude[i]);
            }
        }
        return gps;
    }

    public double getGps2Latitude(int lp){
        double gps = 0;
        for (int i=0;i<stopRowId.length;i++) {
            if (stopRowId[i] == lp) {
                gps=(gps2Latitude[i]);
            }
        }
        return gps;
    }

    public int getFirstEventId(int lp){
        return firstEventRowId[lp];
    }

    public int getLastEventId(int lp){
        return lastEventRowId[lp];
    }

    public int getEventUnixTime(int lp){
        return eventTimeUnix[lp];
    }

    public int getEventCode(int lp){
        return eventCode[lp];
    }

    public int getEventSubCode(int lp){
        return eventSubCode[lp];
    }

    public static void main(String[] args) {
        new zadanie();
    }

}