Thread: For Loops in a Route Assignment Algorithm

Basically, The aim of this program is to check if route times collide and if they do then suggest a new time and perform a recheck within the algorithm.



As this Sample Image shows, there are collision points.

PSEUDO CODE:

For each booking
	Get start time from user
	Call SQL statement to find the max time of all routes
	Call SQL statement to select all bookings within the max time before and after
	Check for free routes in Booking table
 
	If all routes are free
		Insert all routes into a list
		Randomize list
		Return random route RX
		Print confirmation message
	Else
		Insert all free routes into a list
		Randomize list
		Return random route RX
		For X=1, X < Number Of Routes, x++  
			For RX
				Check if RX is in Collision Table
				Check RX and the current booked route
					For each current booking
						Check if it collides with RX with the given start time
							If RX collides with booked route
								Add 5 minutes to start time
								Try again
							Else
								Print confirmation message

My Code:

import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import javax.swing.JOptionPane;
 
public class Main {
 
    public static void main(String[] args) throws SQLException {
 
         int records = 0;
         int maxLength = 0;
         int routesFree = 0;
 
 
        //<editor-fold defaultstate="collapsed" desc="Creates new Student and booking">    
 
        Student s1 = new Student();
        Booking b1 = new Booking();
 
 
        s1.setStudentId(Integer.parseInt(JOptionPane.showInputDialog("Enter ID for Student: [0001]")));
        s1.setFname(JOptionPane.showInputDialog("Enter first name of Student: "));
        s1.setLname(JOptionPane.showInputDialog("Enter last name of Student: "));
        s1.setAddress(JOptionPane.showInputDialog("Enter address for Student: "));
        s1.setPhoneNo(JOptionPane.showInputDialog("Enter phone number for Student: "));
        s1.setOtherDetails(JOptionPane.showInputDialog("Enter other details for Student: [Glasses?]"));
 
        b1.setBookingId(0002);
        b1.setStartTime(Integer.parseInt(JOptionPane.showInputDialog("Enter Start time for Booking: [1200]")));
        b1.setBookingDate(JOptionPane.showInputDialog("Enter Date for Booking: [01-JAN-12]"));
        //</editor-fold>
 
 
       List <Booking> allBookings = new ArrayList<Booking>(); 
 
       allBookings.add(b1);
 
 
       for(Booking b:allBookings){ 
 
           JOptionPane.showMessageDialog(null, "Start Time: " + b1.getStartTime());//Get Start Time from user
 
           //<editor-fold defaultstate="collapsed" desc="To select max time of all routes">
           try {
               Class.forName("oracle.jdbc.driver.OracleDriver");
               //load the oracle driver...needs to be in classes folder in jre folder
           } catch (ClassNotFoundException e) {
               System.out.println(
                       " Can't find class oracle.jdbc.driver.OracleDriver");
               System.exit(1);
           }
 
           Connection conn = null;
           //new connection object
           Statement stmtMax = null;
           //new statemnt object
           ResultSet maxTime = null;
           //new record set object
           try {
               conn = DriverManager.getConnection("jdbc:oracle:thin:@studentoracle.students.ittralee.ie:1521:orcl",
                       "t00145500", "76smqyps");
               stmtMax = conn.createStatement();
               // create the statement for this connection
               //</editor-fold>
 
               /*maxTime = stmtMax.executeQuery(
               "SELECT MAX(LENGTH) FROM ROUTE");
               // get the results of select query and store in recordset object
               while (maxTime.next()) {
                   // move to first/next record of recordset
                   JOptionPane.showMessageDialog(null, "Check: Max time of all routes: " + maxTime.getString(1));
                   // output next record using string format
               }*/
 
            String sql2 = "SELECT MAX(LENGTH) FROM ROUTE";
            PreparedStatement prest2 = conn.prepareStatement(sql2);
            ResultSet rs2 = prest2.executeQuery();
            while (rs2.next()){
            maxLength = rs2.getInt(1);
 
          }
 
          JOptionPane.showMessageDialog(null, "Check: Max time of all routes: " + maxLength);
 
               //<editor-fold defaultstate="collapsed" desc="Error handling for Select Statement"> 
               maxTime.close();
               maxTime = null;
               stmtMax.close();
               stmtMax = null;
               conn.close();
               conn = null;
 
           } catch (SQLException e) {
               System.out.println(" A SQL error: " + e.getMessage());
 
           } finally {
               if (maxTime != null) {
                   try {
                       maxTime.close();
                   } catch (SQLException ignore) {
                   }
               }
 
               if (stmtMax != null) {
                   try {
                       stmtMax.close();
                   } catch (SQLException ignore) {
                   }
               }
 
               if (conn != null) {
                   try {
                       conn.close();
                   } catch (SQLException ignore) {
                   }
               }
           }
 
          // </editor-fold>
 
 
 
 
          //<editor-fold defaultstate="collapsed" desc="To select all bookings within a time">
           try {
               Class.forName("oracle.jdbc.driver.OracleDriver");
               //load the oracle driver...needs to be in classes folder in jre folder
           } catch (ClassNotFoundException e) {
               System.out.println(
                       " Can't find class oracle.jdbc.driver.OracleDriver");
               System.exit(1);
           }
 
           Connection conn2 = null;
           //new connection object
          // Statement stmtTime = null;
           //new statemnt object
          // ResultSet withinTime = null;
           //new record set object
           try {
               conn2 = DriverManager.getConnection("jdbc:oracle:thin:@studentoracle.students.ittralee.ie:1521:orcl",
                       "t00145500", "76smqyps");
               //stmtTime = conn2.createStatement();
               // create the statement for this connection
               //</editor-fold>
 
 
 
               PreparedStatement prest1;
 
                String sql1 = "SELECT * FROM BOOKING where BOOKINGDATE = ? AND STARTTIME BETWEEN ? AND ?";
                prest1 = conn2.prepareStatement(sql1);
                prest1.setString(1,b1.getBookingDate());
                prest1.setInt(2,b1.getStartTime()-5);
                prest1.setInt(3,b1.getStartTime()+5);
                ResultSet rs1 = prest1.executeQuery();
                while(rs1.next()) {
 
                String bookDate = rs1.getString(1);
                int startTimePlus = rs1.getInt(2);
                int startTimeMinus = rs1.getInt(3);
 
                JOptionPane.showMessageDialog(null, " Check: Bookings within a time: \n\nDate: \n" + bookDate + "\nStart Time plus:\n"
                        + startTimePlus+ "\nStart Time minus:\n" + startTimeMinus);
 
                }
 
 
               /*
               withinTime = stmtTime.executeQuery(
               "SELECT * FROM BOOKINGS WHERE" + b1.getStartTime() + "<=" + b1.getStartTime() + "-" + maxTime +
                       "AND" + b1.getStartTime() + ">=" + b1.getStartTime() + "+" + maxTime);
               // get the results of select query and store in recordset object
 
                 JOptionPane.showMessageDialog(null, " Check: Bookings within a time: \n" + withinTime.getString(1));
 
                 while (withinTime.next()) {
                   // move to first/next record of recordset
                   JOptionPane.showMessageDialog(null, " Check: Bookings within a time: \n" + withinTime.getString(1));
                   // output next record using string format
               }*/
               //<editor-fold defaultstate="collapsed" desc="Error handling for Select Statement">
              // withinTime.close();
              // withinTime = null;
              // stmtTime.close();
              // stmtTime = null;
               conn2.close();
               conn2 = null;
 
           } catch (SQLException e) {
               System.out.println(" A SQL error: " + e.getMessage());
 
           } /*finally {
               if (withinTime != null) {
                   try {
                       withinTime.close();
                   } catch (SQLException ignore) {
                   }
               }
 
               if (stmtTime != null) {
                   try {
                       stmtTime.close();
                   } catch (SQLException ignore) {
                   }
               }*/
 
               if (conn2 != null) {
                   try {
                       conn2.close();
                   } catch (SQLException ignore) {
                   }
               }
           }
 
          // </editor-fold>
 
 
 
            //<editor-fold defaultstate="collapsed" desc="To select all free routes">
           try {
               Class.forName("oracle.jdbc.driver.OracleDriver");
               //load the oracle driver...needs to be in classes folder in jre folder
           } catch (ClassNotFoundException e) {
               System.out.println(
                       " Can't find class oracle.jdbc.driver.OracleDriver");
               System.exit(1);
           }
 
           Connection conn3 = null;
           //new connection object
           Statement stmtFreeR = null;
           //new statemnt object
           ResultSet freeRoute = null;
 
           //new record set object
           try {
               conn3 = DriverManager.getConnection("jdbc:oracle:thin:@studentoracle.students.ittralee.ie:1521:orcl",
                       "t00145500", "76smqyps");
               stmtFreeR = conn3.createStatement();
               // create the statement for this connection
               //</editor-fold>
 
              /* freeRoute = stmtFreeR.executeQuery(
               " SELECT ROUTEID FROM Route MINUS SELECT ROUTEID FROM Booking ");
               // get the results of select query and store in recordset object
 
 
               while (freeRoute.next()) {
                   // move to first/next record of recordset
                   JOptionPane.showMessageDialog(null, "Check: Select all free RouteId's: " + freeRoute.getString(1));
                   //JOptionPane.showMessageDialog(null, " the answer is " + fRoutes);
                   // output next record using string format
               }*/
 
            String sql3 = "SELECT ROUTEID FROM Route MINUS SELECT ROUTEID FROM Booking";
            PreparedStatement prest3 = conn3.prepareStatement(sql3);
            ResultSet rs3 = prest3.executeQuery();
            while (rs3.next()){
            routesFree = rs3.getInt(1);
 
          }
 
          JOptionPane.showMessageDialog(null, "Check: Select all free RouteId's: " + routesFree);
 
 
                //<editor-fold defaultstate="collapsed" desc="To randomize free routes">
 
           if( freeRoute != null) {
 
            List RouteX = new ArrayList();
 
            while (freeRoute.next()) {
                RouteX.add(freeRoute.getString(1));
 
                Collections.shuffle(RouteX);
 
                JOptionPane.showMessageDialog(null, "Free routes list: " + RouteX);
 
            }
 
           }
 
        else {
 
            List RouteX = new ArrayList();
 
            while (freeRoute.next()) {
                RouteX.add(freeRoute.getString(1));
 
                Collections.shuffle(RouteX);
 
                JOptionPane.showMessageDialog(null,"Free routes list: " + RouteX);
 
            }
        }
 
       //</editor-fold>
 
 
               //<editor-fold defaultstate="collapsed" desc="Error handling for Select Statement">
               freeRoute.close();
               freeRoute = null;
               stmtFreeR.close();
               stmtFreeR = null;
               conn3.close();
               conn3 = null;
 
           } catch (SQLException e) {
               System.out.println(" A SQL error: " + e.getMessage());
 
           } finally {
               if (freeRoute != null) {
                   try {
                       freeRoute.close();
                   } catch (SQLException ignore) {
                   }
               }
 
               if (stmtFreeR != null) {
                   try {
                       stmtFreeR.close();
                   } catch (SQLException ignore) {
                   }
               }
 
               if (conn3 != null) {
                   try {
                       conn3.close();
                   } catch (SQLException ignore) {
                   }
               }
           }
 
          // </editor-fold>
 
 
 
 
 
            //<editor-fold defaultstate="collapsed" desc="To count number of routes">
           try {
               Class.forName("oracle.jdbc.driver.OracleDriver");
               //load the oracle driver...needs to be in classes folder in jre folder
           } catch (ClassNotFoundException e) {
               System.out.println(
                       " Can't find class oracle.jdbc.driver.OracleDriver");
               System.exit(1);
           }
 
           Connection conn4 = null;
           //new connection object
           Statement stmtCountR = null;
 
           //new statemnt object
          // ResultSet countRoutes = null;
           //new record set object
           try {
               conn4 = DriverManager.getConnection("jdbc:oracle:thin:@studentoracle.students.ittralee.ie:1521:orcl",
                       "t00145500", "76smqyps");
               stmtCountR = conn4.createStatement();
               // create the statement for this connection
               //</editor-fold>
 
            String sql = "SELECT COUNT(*) FROM ROUTE";
            PreparedStatement prest = conn4.prepareStatement(sql);
            ResultSet rs = prest.executeQuery();
            while (rs.next()){
            records = rs.getInt(1);
 
 
 
          }
 
          JOptionPane.showMessageDialog(null, " Number of total routes: " + records);
 
          //System.out.println("Number of records: " + records);
                   // move to first/next record of recordset
 
                   //JOptionPane.showMessageDialog(null, " the answer is " + fRoutes);
                   // output next record using string format
 
 
      //<editor-fold defaultstate="collapsed" desc="Error handling for Select Statement">
              // countRoutes.close();
               //countRoutes = null;
               rs.close();
               rs = null;
               stmtCountR.close();
               stmtCountR = null;
               conn4.close();
               conn4 = null;
 
           } catch (SQLException e) {
               System.out.println(" A SQL error: " + e.getMessage());
 
           }/* finally {
               if (countRoutes != null) {
                   try {
                       countRoutes.close();
                   } catch (SQLException ignore) {
                   }
               }*/
 
               if (stmtCountR != null) {
                   try {
                       stmtCountR.close();
                   } catch (SQLException ignore) {
                   }
               }
 
               if (conn4 != null) {
                   try {
                       conn4.close();
                   } catch (SQLException ignore) {
                   }
               }
           }
 
          //</editor-fold>
 
 
 
           for(int X = 1; X < records; X++) {
 
                for(r[X]) {
 
            //<editor-fold defaultstate="collapsed" desc="To check if RX is in Collision Table">
           try {
               Class.forName("oracle.jdbc.driver.OracleDriver");
               //load the oracle driver...needs to be in classes folder in jre folder
           } catch (ClassNotFoundException e) {
               System.out.println(
                       " Can't find class oracle.jdbc.driver.OracleDriver");
               System.exit(1);
           }
 
           Connection conn5 = null;
           //new connection object
           Statement stmtFindRx = null;
           //new statemnt object
           ResultSet checkRx = null;
           //new record set object
           try {
               conn5 = DriverManager.getConnection("jdbc:oracle:thin:@studentoracle.students.ittralee.ie:1521:orcl",
                       "t00145500", "76smqyps");
               stmtFindRx = conn5.createStatement();
               // create the statement for this connection
               //</editor-fold>
 
                   checkRx = stmtFindRx.executeQuery(
                   "*********");
                   // get the results of select query and store in recordset object
 
 
                   while (checkRx.next()) {
                   // move to first/next record of recordset
                   JOptionPane.showMessageDialog(null, " the answer is " + checkRx.getString(1));
 
                   // output next record using string format
               }
 
               //<editor-fold defaultstate="collapsed" desc="Error handling for Select Statement">
               checkRx.close();
               checkRx = null;
               stmtFindRx.close();
               stmtFindRx = null;
               conn5.close();
               conn5 = null;
 
           } catch (SQLException e) {
               System.out.println(" A SQL error: " + e.getMessage());
 
           } finally {
               if (checkRx != null) {
                   try {
                       checkRx.close();
                   } catch (SQLException ignore) {
                   }
               }
 
               if (stmtFindRx != null) {
                   try {
                       stmtFindRx.close();
                   } catch (SQLException ignore) {
                   }
               }
 
               if (conn5 != null) {
                   try {
                       conn5.close();
                   } catch (SQLException ignore) {
                   }
               }
           }
 
          // </editor-fold>
 
 
 
 
 
                }
 
           }
 
 
 
 
/*
       if(R[X].equals(b1.getRoute())) {
 
          b1.setStartTime(b1.getStartTime() + 0005);
 
       } else {
 
 
       String strConn = "jdbc:oracle:thin:@studentoracle.students.ittralee.ie:1521:orcl";
       String strUser = "T00145500";
       String strPassword = "76smqyps";
 
       try {
        Driver drv = new oracle.jdbc.driver.OracleDriver();
        DriverManager.registerDriver(drv);
        Connection conn6 = DriverManager.getConnection(strConn, strUser, strPassword);
        //code to execute commands...
 
        //Booking Insert
        String query1 = "INSERT INTO Booking(BOOKINGID, BOOKINGTYPE, LNAME, STARTTIME, " +
        "BOOKINGDATE, HISTORY) VALUES (?, ?, ?, ?, ?)";
 
        PreparedStatement pstmt1 = conn6.prepareStatement(query1);
        pstmt1.setInt(1, b1.getBookingId());
        pstmt1.setDouble(3, b1.getStartTime());
        pstmt1.setString(4, b1.getBookingDate());
        pstmt1.executeUpdate();
 
        JOptionPane.showMessageDialog(null, "Booking Confirmed");
 
        conn6.close();
 
        }
 
        catch(SQLException e) {
        System.out.println(" A SQL error: " + e.getMessage());
 
           }*/
        }

The problem i am having right now is the, FOR RX loop.

X is a value that is incremented, and i want it to be assigned with a string character R.

E.g R1, R2, R3.

This would then correspond to values in a database table.

How do i create this for loop?

Because as it stands i have:

 for(int X = 1; X < records; X++) {                  
 
for(r[X]) {

Also, i know the connections shouldnt be in loops, but i will resolve this later, i just need this algorithm to be working 1st.

Thank You.

X is a value that is incremented, and i want it to be assigned with a string character R.

E.g R1, R2, R3.

I don't understand what you are trying to do here.
If X is an int value used in a for loop and you want to create String values by concatenating the value of X to "R" to create new Strings: "R1", "R2", etc
Can you explain what you are trying to do?

Can you explain the problem you are having concatenating the value of x to "R" to create the Strings: "R1", "R2" etc

String newStr = "R" + X; // Create String by concatenating "R" and loop control variable