sql

import java.util.*;
import java.sql.*;

public class RawData {
        private int numberOfLocs = -1;
        private int numberOfRows = -1;

        private String tablename = " Agnes1 ";
//      private String tablename = " Data ";
//      private String tablename = " Visitors ";


            public String[] getNodeNames(String restriction, int classNumber, String dateRestriction) {
            Connection C = getConnection();
            String[] names = null;


            try {
                Statement Stmt = C.createStatement(
                      ResultSet.TYPE_SCROLL_INSENSITIVE,
                      ResultSet.CONCUR_UPDATABLE
                  );
                String sql = "CREATE TABLE temp (loc VARCHAR(30));";
                Stmt.executeUpdate(sql);
                String s = "WHERE class='" + classNumber + "'";
                if (!restriction.equals("")) {
                    s += " AND student like '" + restriction + "'";
                }
                if (!dateRestriction.equals("")) {
                    s += " AND timedate='" + dateRestriction + "'";
                }

                Stmt.executeUpdate("INSERT INTO temp SELECT DISTINCT fromLoc FROM Data " + s);
                Stmt.executeUpdate("INSERT INTO temp SELECT DISTINCT toLoc FROM   Data " + s);

                ResultSet RS = Stmt.executeQuery("SELECT DISTINCT loc FROM temp ORDER BY loc");

                RS.last();

                int n = RS.getRow();
                RS.beforeFirst();

                if (n < 1) return null;

                names = new String[n];
                int i = 0;
                while (RS.next()) {
                    names[i++] = RS.getString("loc");
                }
            }
            catch (SQLException E) {
                System.out.println("SQLException: " + E.getMessage());
                System.out.println("SQLState:     " + E.getSQLState());
                System.out.println("VendorError:  " + E.getErrorCode());
            }

            return names;
        }

        public String[] getStudentNames(String restriction, int classNumber, String dateRestriction) {
            Connection C = getConnection();
            String[] names = null;

            try {
                Statement Stmt = C.createStatement(
                       ResultSet.TYPE_SCROLL_INSENSITIVE,
                       ResultSet.CONCUR_UPDATABLE
);

                Stmt.executeUpdate("CREATE TABLE temp (student VARCHAR(30))");

                String s = "WHERE class='" + classNumber + "'";
                if (!restriction.equals("")) {
                    s += " AND student like '" + restriction + "'";
                }
                if (!dateRestriction.equals("")) {
                    s += " AND timedate='" + dateRestriction + "'";
                }

                Stmt.executeUpdate("INSERT INTO temp SELECT DISTINCT student FROM Data " + s);

                ResultSet RS2 = Stmt.executeQuery("SELECT DISTINCT student FROM temp ORDER BY student");

                RS2.last();
                int n = RS2.getRow();
                System.out.print("n has " + n + " students");

                RS2.beforeFirst();

                if (n < 1) return null;

                names = new String[n];
                int i = 0;
                while (RS2.next()) {
                    names[i++] = RS2.getString("student");
                }

            }
            catch (SQLException E) {
                System.out.println("SQLException: " + E.getMessage());
                System.out.println("SQLState:     " + E.getSQLState());
                System.out.println("VendorError:  " + E.getErrorCode());
            }

            return names;
        }

        public double[][] getTransitionMatrix(int classNumber, String dateRestriction, String studentName, Hashtable invNodeNames) {
            int n = invNodeNames.size();
            double[][] matrix = new double[n][n];

            Connection C = getConnection();

            try {
                Statement Stmt = C.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE
                 );

                String s;
                s =  "SELECT fromLoc,toLoc ";
                s += "FROM data ";
                s += "WHERE class='" + classNumber + "' ";
                s += "  AND student like '" + studentName + "' ";
                s += "  AND fromLoc != 'none' ";
                s += "  AND toLoc != 'none' ";

                if (!dateRestriction.equals("")) {
                    s += " AND timedate='" + dateRestriction + "'";
                }

                int fromLoc, toLoc;
                ResultSet RS = Stmt.executeQuery(s);
                while (RS.next()) {
                    fromLoc = ((Integer)invNodeNames.get(RS.getString("fromLoc").toLowerCase())).intValue();
                    toLoc =   ((Integer)invNodeNames.get(RS.getString("toLoc").toLowerCase())).intValue();
                    matrix[fromLoc][toLoc]++;
                }

                for (int i = 0; i < n; i++) {
                    for (int j = 0; j < n; j++) {
                        if (matrix[i][j] < .5) matrix[i][j] = -1;
                    }
                }


            }
            catch (SQLException E) {
                System.out.println("SQLException: " + E.getMessage());
                System.out.println("SQLState:     " + E.getSQLState());
                System.out.println("VendorError:  " + E.getErrorCode());
            }

            return matrix;
        }



        public ResultSet query(String q) {
            ResultSet RS = null;

            Connection C = getConnection();

            try {
                Statement Stmt = C.createStatement(
                      ResultSet.TYPE_SCROLL_INSENSITIVE,
                      ResultSet.CONCUR_UPDATABLE
);

                String s;
                s =  "CREATE TABLE #temp (";
                s += "id       INT, ";
                s += "class    INT, ";
                s += "student  VARCHAR(30), ";
                s += "purpose  VARCHAR(50), ";
                s += "fromLoc  VARCHAR(30), ";
                s += "toLoc    VARCHAR(30)";
                s += ")";

                Stmt.executeUpdate(s);

                Stmt.executeUpdate("INSERT INTO temp " + q);

                Stmt.executeUpdate("CREATE TABLE temp1 (loc VARCHAR(30))");

                Stmt.executeUpdate("INSERT INTO temp1 SELECT DISTINCT fromLoc FROM #temp");
                Stmt.executeUpdate("INSERT INTO temp1 SELECT DISTINCT toLoc FROM #temp");

                RS = Stmt.executeQuery("SELECT COUNT(DISTINCT loc) AS n FROM #temp1");
                if (RS.next()) {
                    numberOfLocs = RS.getInt("n");
                } else {
                    numberOfLocs = -1;
                }

                RS = Stmt.executeQuery(q);

                RS.last();
                numberOfRows = RS.getRow();
                RS.first();
            }
            catch (SQLException E) {
                System.out.println("SQLException: " + E.getMessage());
                System.out.println("SQLState:     " + E.getSQLState());
                System.out.println("VendorError:  " + E.getErrorCode());
            }

            return RS;
        }
        private Connection getConnection() {
            Connection C = null;
            try {

            } catch (Exception E) {
                System.err.println("Unable to load driver.");
                E.printStackTrace();
            }
            String dbname =   "PathfinderData";
            String username = "root";
            String password = "newpass";

            try {

                String DB_URL = "jdbc:mysql://localhost:3306/pathfinderdata";
                C = DriverManager.getConnection(DB_URL, username, password);
            }
            catch (SQLException E) {
                System.out.println("SQLException: " + E.getMessage());
                System.out.println("SQLState:     " + E.getSQLState());
                System.out.println("VendorError:  " + E.getErrorCode());
            }
            return C;
        }


        public int getNumberOfLocs() { return numberOfLocs; }

        public int getNumberOfRows() { return numberOfRows; }
}