Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import java.sql.*;
- // You should use this class so that you can represent SQL points as
- // Java PGpoint objects.
- import org.postgresql.geometric.PGpoint;
- // If you are looking for Java data structures, these are highly useful.
- // However, you can write the solution without them. And remember
- // that part of your mark is for doing as much in SQL (not Java) as you can.
- import java.util.ArrayList;
- import java.util.Collections;
- import java.util.HashMap;
- import java.util.HashSet;
- import java.util.Set;
- import java.sql.Timestamp;
- public class Assignment2 {
- // A connection to the database
- Connection connection;
- Assignment2() throws SQLException {
- try {
- Class.forName("org.postgresql.Driver");
- } catch (ClassNotFoundException e) {
- e.printStackTrace();
- }
- }
- /**
- * Connects and sets the search path.
- *
- * Establishes a connection to be used for this session, assigning it to
- * the instance variable 'connection'. In addition, sets the search
- * path to uber.
- *
- * @param url the url for the database
- * @param username the username to connect to the database
- * @param password the password to connect to the database
- * @return true if connecting is successful, false otherwise
- */
- public boolean connectDB(String URL, String username, String password) {
- // Implement this method!
- try{
- connection = DriverManager.getconnection(URL, username, password);
- }
- catch (SQLException ex) {
- return false;
- }
- if (connection != null ){
- queryString = "SET search_path TO uber";
- pStatement = connection.prepareStatement(queryString);
- pStatement.execute();
- return true;
- }
- return false;
- }
- /**
- * Closes the database connection.
- *
- * @return true if the closing was successful, false otherwise
- */
- public boolean disconnectDB() {
- // Implement this method!
- return false;
- }
- /* ======================= Driver-related methods ======================= */
- /**
- * Records the fact that a driver has declared that he or she is available
- * to pick up a client.
- *
- * Does so by inserting a row into the Available table.
- *
- * @param driverID id of the driver
- * @param when the date and time when the driver became available
- * @param location the coordinates of the driver at the time when
- * the driver became available
- * @return true if the insertion was successful, false otherwise.
- */
- public boolean available(int driverID, Timestamp when, PGpoint location) {
- // Implement this method!
- String UpdateString = "INSERT INTO Available Values "
- +"(?, ?, ?)";
- PreparedStatement ps = connection.preparedStatement(UpdateString);
- ps.setInt(1, driverID);
- ps.setTimestamp(2, when);
- ps.setObject(3, location);
- if (ps.executeUpdate()) {
- return true;}
- else {
- return false;}
- /**
- * Records the fact that a driver has picked up a client.
- *
- * If the driver was dispatched to pick up the client and the corresponding
- * pick-up has not been recorded, records it by adding a row to the
- * Pickup table, and returns true. Otherwise, returns false.
- *
- * @param driverID id of the driver
- * @param clientID id of the client
- * @param when the date and time when the pick-up occurred
- * @return true if the operation was successful, false otherwise
- */
- // Request, dispatch, pickup
- public boolean picked_up(int driverID, int clientID, Timestamp when) {
- // Implement this method!
- // Step 1: Check if driverID clientID matches the joined table request and dispatch and finds request ID
- String QueryString = "SELECT request_id, p.timestamp " +
- "FROM Request r JOIN Dispatch d " +
- "ON r.request_id=d.request_id "+
- "LEFT JOIN Pickup p "+
- "ON d.request_id = p.request_id "+
- "WHERE r.client_id=? AND d.driver_id=? "+
- "ORDER BY d.timestamp DESC";// We're assuming they get picked up by the most recent request that has not been picked up for client, in case there is a null value for when
- PrepareStatement ps=connection.preparedStatement(QueryString);
- ps.setInt(1,clientID);
- ps.setInt(2,driverID);
- rs=ps.executeQuery();
- while(rs.next()) {
- //If there are elements in the results, it will return a non-0 value.
- // Step 2: If driver ID, clientID matches the joined table, check if it exists in pickup table, if not, insert it.
- // if the time is different, from it, it has been called a different time, defer it.
- r_id=rs.getInt("request_id");
- time=rs.getTimestamp("timestamp");
- if (time == null) {
- //Assumption: If dispatch is missed, it is the one we're looking for
- String UpdateString = "INSERT INTO Pickup Values " +
- " ( ?, ?)";
- PrepareStatement ps = connection.preparedStatement(UpdateString)
- ps.setInt(1,r_id);
- ps.setTimestamp(2, when);
- return true;
- }
- else if(time == when) {
- // If timestamps matches input, then it is already in the pickup table, so don't do anything
- return false;
- }
- }
- //If timestamp value is different from input, continue to next value.
- return false;
- }
- /* ===================== Dispatcher-related methods ===================== */
- /**
- * Dispatches drivers to the clients who've requested rides in the area
- * bounded by NW and SE.
- *
- * For all clients who have requested rides in this area (i.e., whose
- * request has a source location in this area), dispatches drivers to them
- * one at a time, from the client with the highest total billings down
- * to the client with the lowest total billings, or until there are no
- * more drivers available.
- *
- * Only drivers who (a) have declared that they are available and have
- * not since then been dispatched, and (b) whose location is in the area
- * bounded by NW and SE, are dispatched. If there are several to choose
- * from, the one closest to the client's source location is chosen.
- * In the case of ties, any one of the tied drivers may be dispatched.
- *
- * Area boundaries are inclusive. For example, the point (4.0, 10.0)
- * is considered within the area defined by
- * NW = (1.0, 10.0) and SE = (25.0, 2.0)
- * even though it is right at the upper boundary of the area.
- *
- * Dispatching a driver is accomplished by adding a row to the
- * Dispatch table. All dispatching that results from a call to this
- * method is recorded to have happened at the same time, which is
- * passed through parameter 'when'.
- * public static void main(String[] args) {
- // You can put testing code in here. It will not affect our autotester.
- Assignment2
- A2 = new Assignment2();
- url = "jdbc:postgresql://localhost:5432/csc343h-c6fanjia";
- A2.connectDB(url, "c6fanjia","");
- System.out.println("Boo!");
- }
- * @param NW x, y coordinates in the northwest corner of this area.
- * @param SE x, y coordinates in the southeast corner of this area.
- * @param when the date and time when the dispatching occurred
- */
- public void dispatch(PGpoint NW, PGpoint SE, Timestamp when) {
- // Implement this method!
- // Matching clients with drivers, and the result is to match all of them until there is no more drivers in the area.
- // Step 1: Find all the available (open request) client in the area and order by total billing, find all avaiable driver in the area.
- String Querystring = "((SELECT client_id, location"+ // Sort all clients by their patronage
- "FROM Request r LEFT JOIN Billed b ON r.request_id=b.request_id " +
- "GROUP BY client_id " +
- "ORDER BY sum(amount) DESC NULLS LAST) " +
- "JOIN " +
- "(SELECT client_id " +// Find all open request for clients within the area
- "FROM Request r JOIN Place p "+
- "ON r.source = p.location " +
- // Definition: open request - request that has not been fulfilled yet(not billed)
- "WHERE r.request_id NOT IN ( " +
- "SELECT request_id " +
- "FROM Billed b ) " +
- "AND location[0]>= ? AND location[0]<= ? " +//within the specified area
- "AND location[1]<= ? AND location[1]>= ? )) " +
- "AS Availble_client"
- ;
- PreparedStatement ps = connection.preparedStatement(QueryString);
- ps.setDouble(1,NW.x);
- ps.setDouble(2,SE.x);
- ps.setDouble(3,NW.y);
- ps.setDouble(4,SE.y);
- c_ID=ps.executeQuery();
- // Actually available - the available timestamp > dispatched timestamp
- String Querystring = "(SELECT x.driver_id, x.location, x.datetime " + //Finds most recent available time for all drivers
- "FROM (" +
- "SELECT driver_id , max(datetime) " +
- "FROM Available a" +
- "GROUP BY driver_id " +
- ") "+
- "AS x " +
- "WHERE x.datetime > ( " +// Finds the dispatch
- "SELECT max(d.datetime) " +
- "FROM Dispatch d " +
- "GROUP BY d.driver_id " +
- "WHERE x.driver_id = d.driver_id " +
- " )"
- "AND x.location[0]>= ? AND x.location[0]<= ? " +// with in the area
- "AND x.location[1]<= ? AND x.location[1]>= ? " +
- ")"; +
- "AS Available_driver"
- PreparedStatement ps = connection.preparedStatement(QueryString);
- ps.setDouble(1,NW.x);
- ps.setDouble(2,SE.x);
- ps.setDouble(3,NW.y);
- ps.setDouble(4,SE.y);
- d_ID=ps.executeQuery();
- }
- public static void main(String[] args) {
- // You can put testing code in here. It will not affect our autotester.
- Assignment2
- A2 = new Assignment2();
- url = "jdbc:postgresql://localhost:5432/csc343h-c6fanjia";
- A2.connectDB(url, "c6fanjia","");
- System.out.println("Boo!");
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement