package historyPlaces; import java.sql.*; import java.util.*; /** * filename: engine.java * Created By: Ryan Prins * Last Modified: May 31, 2004 * * This file is the main java class that runs the project. It * contins the neeeded methods to generate the output for * the specific deliverables required for History Places. */ public class engine{ /** Holds the connection to database -- created by init() * By default, transactions are auto-committed */ private Connection conn = null; /** A variable for holding Exception error messages, etc. */ private String debugString = "nothing"; /** A linked list that contains all the children of a particular parent */ private LinkedList children = new LinkedList(); /** A linked list that contains the crrent hierarchy */ private LinkedList hierarchy = new LinkedList(); /** A linked list that contians all of the places */ private LinkedList allPlaces = new LinkedList(); /** An ArrayList that contains all of the IDs needed to query for the timeline */ private ArrayList photoID = new ArrayList(); /** A LinkedList that contains all of the photos */ private LinkedList allPhotos = new LinkedList(); /** * Initializes the connection to the database */ private void init() { try { /* This statement implicitly loads the driver */ Class.forName(DataBaseParam.getDriver()); /* Now, attempt to create a connection */ conn = DriverManager.getConnection( DataBaseParam.getDbURL(), DataBaseParam.getUserID(), DataBaseParam.getUserPassword()); if (conn == null) throw new Exception("Could not connect to " + DataBaseParam.getConnInfo()); } catch(Exception e) { debugString = e.toString(); e.printStackTrace(); } } /** * Gets the connection to the database and returns it to the user * @return the database connection */ private Connection getConn() { if (conn == null) { init(); } return conn; } /** * Will return to the user all of the Error information * @return database error information */ public String getErrorInfo() { return "<P>" + DataBaseParam.getConnInfo() + debugString + "</P>"; } /** * This function retuns various pieces meta data about the connection * @return a string with various meta data about the connection */ public String getConnectionInfo() { String t = ""; try{ /* Class connection presents a 'connection' to a database. Using connections, you control transactions, db resources, authentication, etc. */ Connection conn = getConn(); /* Class DatabaseMetaData can be used to retrieve a lot of interesting information about the database */ DatabaseMetaData m = conn.getMetaData(); String url = m.getURL(); String user = m.getUserName(); String p = m.getDatabaseProductName(); String v = m.getDatabaseProductVersion(); t += "<dbinfo><misc> \n"; t += "<dburl>" + url + "</dburl> \n"; t += "<dbuser>" + user + "</dbuser> \n"; t += "<dbproductname>" + p + "</dbproductname> \n"; t += "<dbproductversion>" + v + "</dbproductversion> \n"; t += "</misc></dbinfo>\n"; /* Determine the names and columns of the tables in the database */ ResultSet r = m.getTables(null,null,null,null); t += "<dbtables>\n"; while(r.next()) { t += "<dbtable>\n"; t += "<name>"; String tname = r.getString(3); t += tname; t += "</name>\n"; /* Determine the column info for the table -- many other attributes */ t += "<cols>"; ResultSet rc = m.getColumns(null,null,tname,null); while (rc.next()) { t += "<col>\n"; t += "<name>" + rc.getString(4) + "</name>\n"; t += "<type>" + rc.getString(6) + "</type>\n"; t += "</col>\n"; } t += "</cols>\n"; t += "</dbtable>\n"; rc.close(); } t += "</dbtables>\n"; } catch(Exception e) { t = e.toString(); e.printStackTrace(); } return t; } /** * Returns the hierarchy to the user in a LinkedList. * @return all places in their hierarchy * @param q the place id to get the hierarchy from */ public LinkedList getHierarchy(int q) { /* Integer to build */ String t = ""; int pid = -1; int parent = -1; try{ Connection conn = getConn(); /* Class statement is used to issue SQL statements to a database */ Statement s = conn.createStatement(); /* Class ResultSet contains the results from a query */ ResultSet r; /* Build up query */ String p1 = "SELECT * FROM place "; String p2 = "WHERE pid = " + q; String sql = p1 + p2; /* Execute query -- the results are in r. If no results are to be returned use int executeUpdate(String q) */ r = s.executeQuery(sql); /* A 'cursor' is used to move through results -- initially, the cursor is set BEFORE the first row in the result set. While next() returns TRUE then information is available */ if(r.next()) { /* Query generated a result -- now extract data by Name & Index */ pid = r.getInt("pid"); parent = r.getInt("parent"); String pname = r.getString("pname"); String comment = r.getString("comment"); int photoid = r.getInt("photoid"); place myPlace = new place(pid, parent, pname, comment, photoid); hierarchy.add(myPlace); } /*Not necessary here but good habit */ r.close(); }catch(Exception e) { t = e.toString(); e.printStackTrace(); } if(pid != parent){ return getHierarchy(parent); }else{ return hierarchy; } } /** * Searches for the children of a particular parent * @return all of the places with their placeid and name */ public String printParents() { /* Integer to build */ String t = ""; String output = ""; try{ Connection conn = getConn(); /* Class statement is used to issue SQL statements to a database */ Statement s = conn.createStatement(); /* Class ResultSet contains the results from a query */ ResultSet r; /* Build up query */ String p1 = "SELECT pid, pname FROM place"; String sql = p1; /* Execute query -- the results are in r. If no results are to be returned use int executeUpdate(String q) */ r = s.executeQuery(sql); /* A 'cursor' is used to move through results -- initially, the cursor is set BEFORE the first row in the result set. While next() returns TRUE then information is available */ while(r.next()) { /* Query generated a result -- now extract data by Name & Index */ int pid = r.getInt("pid"); String pname = r.getString("pname"); output += "<option value=\"" + pid + "\">" + pname + "(" + pid + ")</optoin>\n"; } /*Not necessary here but good habit */ r.close(); }catch(Exception e) { t = e.toString(); e.printStackTrace(); } return output; } /** * Searches for the children of a particular parent * @return all of the children of a particular parent */ public LinkedList getChildren(int q) { /* Integer to build */ String t = ""; int pid = -1; int parent = -1; try{ Connection conn = getConn(); /* Class statement is used to issue SQL statements to a database */ Statement s = conn.createStatement(); /* Class ResultSet contains the results from a query */ ResultSet r; /* Build up query */ String p1 = "SELECT * FROM place "; String p2 = "WHERE parent = " + q; String sql = p1 + p2; /* Execute query -- the results are in r. If no results are to be returned use int executeUpdate(String q) */ r = s.executeQuery(sql); /* A 'cursor' is used to move through results -- initially, the cursor is set BEFORE the first row in the result set. While next() returns TRUE then information is available */ while(r.next()) { /* Query generated a result -- now extract data by Name & Index */ pid = r.getInt("pid"); parent = r.getInt("parent"); String pname = r.getString("pname"); String comment = r.getString("comment"); int photoid = r.getInt("photoid"); place myPlace = new place(pid, parent, pname, comment, photoid); children.add(myPlace); } /*Not necessary here but good habit */ r.close(); }catch(Exception e) { t = e.toString(); e.printStackTrace(); } return children; } /** * Returns a single place * @param q the place id for the place which you want data */ public place getPlace(int q) { /* Integer to build */ String t = ""; int pid = -1; int parent = -1; place myPlace = new place(-1, -1, "", "", -1); try{ Connection conn = getConn(); /* Class statement is used to issue SQL statements to a database */ Statement s = conn.createStatement(); /* Class ResultSet contains the results from a query */ ResultSet r; /* Build up query */ String p1 = "SELECT * FROM place "; String p2 = "WHERE pid = " + q; String sql = p1 + p2; /* Execute query -- the results are in r. If no results are to be returned use int executeUpdate(String q) */ r = s.executeQuery(sql); /* A 'cursor' is used to move through results -- initially, the cursor is set BEFORE the first row in the result set. While next() returns TRUE then information is available */ if(r.next()) { /* Query generated a result -- now extract data by Name & Index */ pid = r.getInt("pid"); parent = r.getInt("parent"); String pname = r.getString("pname"); String comment = r.getString("comment"); int photoid = r.getInt("photoid"); myPlace = new place(pid, parent, pname, comment, photoid); } /*Not necessary here but good habit */ r.close(); }catch(Exception e) { t = e.toString(); e.printStackTrace(); } return myPlace; } /** * Returns a single photo * @param q the photo id of the photo that you want information on * @return the information about the specific photo */ public photo getPhoto(int q) { /* Integer to build */ String t = ""; photo badPhoto = new photo(); try{ Connection conn = getConn(); /* Class statement is used to issue SQL statements to a database */ Statement s = conn.createStatement(); /* Class ResultSet contains the results from a query */ ResultSet r; /* Build up query */ String p1 = "SELECT * FROM photo "; String p2 = "WHERE photoid = " + q; String sql = p1 + p2; /* Execute query -- the results are in r. If no results are to be returned use int executeUpdate(String q) */ r = s.executeQuery(sql); /* A 'cursor' is used to move through results -- initially, the cursor is set BEFORE the first row in the result set. While next() returns TRUE then information is available */ if(r.next()) { /* Query generated a result -- now extract data by Name & Index */ int photoid = r.getInt("photoid"); String name = r.getString("name"); String url = r.getString("url"); String comment = r.getString("comment"); java.sql.Date date = r.getDate("date"); String photographer = r.getString("photographer"); int placeid = r.getInt("placeid"); String dateString = date.toString(); System.out.println(photoid); System.out.println(name); System.out.println(url); System.out.println(comment); System.out.println(date.toString()); System.out.println(photographer); System.out.println(placeid); photo myPhoto = new photo(photoid, name, url, comment, dateString, photographer, placeid); return myPhoto; } /*Not necessary here but good habit */ r.close(); }catch(Exception e) { t = e.toString(); e.printStackTrace(); } return badPhoto; } /** * Returns a single photo * @param q the photo id of the photo that you want information on * @return the information about the specific photo */ public LinkedList getAllPhoto(int id) { /* Integer to build */ String t = ""; photo badPhoto = new photo(); try{ Connection conn = getConn(); /* Class statement is used to issue SQL statements to a database */ Statement s = conn.createStatement(); /* Class ResultSet contains the results from a query */ ResultSet r; /* Build up query */ String p1 = "SELECT * FROM photo WHERE placeid = " + id; String sql = p1; /* Execute query -- the results are in r. If no results are to be returned use int executeUpdate(String q) */ r = s.executeQuery(sql); /* A 'cursor' is used to move through results -- initially, the cursor is set BEFORE the first row in the result set. While next() returns TRUE then information is available */ while(r.next()) { /* Query generated a result -- now extract data by Name & Index */ int photoid = r.getInt("photoid"); String name = r.getString("name"); String url = r.getString("url"); String comment = r.getString("comment"); java.sql.Date date = r.getDate("date"); String photographer = r.getString("photographer"); int placeid = r.getInt("placeid"); String dateString = date.toString(); System.out.println(photoid); System.out.println(name); System.out.println(url); System.out.println(comment); System.out.println(date.toString()); System.out.println(photographer); System.out.println(placeid); photo myPhoto = new photo(photoid, name, url, comment, dateString, photographer, placeid); allPhotos.add(myPhoto); } /*Not necessary here but good habit */ r.close(); }catch(Exception e) { t = e.toString(); e.printStackTrace(); } return allPhotos; } /** * Gives the user a list of photoids * @return all of the photoids */ public ArrayList getPhotoListID() { /* Integer to build */ String t = ""; photo badPhoto = new photo(); try{ Connection conn = getConn(); /* Class statement is used to issue SQL statements to a database */ Statement s = conn.createStatement(); /* Class ResultSet contains the results from a query */ ResultSet r; /* Build up query */ String p1 = "SELECT photoid FROM photo"; String sql = p1; /* Execute query -- the results are in r. If no results are to be returned use int executeUpdate(String q) */ r = s.executeQuery(sql); /* A 'cursor' is used to move through results -- initially, the cursor is set BEFORE the first row in the result set. While next() returns TRUE then information is available */ while(r.next()) { /* Query generated a result -- now extract data by Name & Index */ int photoid = r.getInt("photoid"); Integer myInteger = new Integer(photoid); photoID.add(myInteger); } /*Not necessary here but good habit */ r.close(); }catch(Exception e) { t = e.toString(); e.printStackTrace(); } return photoID; } /** * Adds a place into the database. * @param parent the parent id of the place * @param name the name of the place * @param comment a comment about the place */ public void addPlace(int parent, String name, String comment) { /* Integer to build */ String t = ""; try{ Connection conn = getConn(); /* Class statement is used to issue SQL statements to a database */ Statement s = conn.createStatement(); /* Build up query */ String p1 = "INSERT INTO place (parent, pname, comment)"; String p2 = "VALUES (" + parent + ", '" + name + "', '" + comment + "')"; String sql = p1 + p2; /* Execute query -- the results are in r. If no results are to be returned use int executeUpdate(String q) */ s.executeUpdate(sql); }catch(Exception e) { t = e.toString(); e.printStackTrace(); } } /** * Returns all of the places to the user * @return all of the places to the user */ public LinkedList getPlaces() { /* Integer to build */ String t = ""; int pid = -1; int parent = -1; try{ Connection conn = getConn(); /* Class statement is used to issue SQL statements to a database */ Statement s = conn.createStatement(); /* Class ResultSet contains the results from a query */ ResultSet r; /* Build up query */ String p1 = "SELECT * FROM place"; String sql = p1; /* Execute query -- the results are in r. If no results are to be returned use int executeUpdate(String q) */ r = s.executeQuery(sql); /* A 'cursor' is used to move through results -- initially, the cursor is set BEFORE the first row in the result set. While next() returns TRUE then information is available */ while(r.next()) { /* Query generated a result -- now extract data by Name & Index */ pid = r.getInt("pid"); parent = r.getInt("parent"); String pname = r.getString("pname"); String comment = r.getString("comment"); int photoid = r.getInt("photoid"); place myPlace = new place(pid, parent, pname, comment, photoid); allPlaces.add(myPlace); } /*Not necessary here but good habit */ r.close(); }catch(Exception e) { t = e.toString(); e.printStackTrace(); } return allPlaces; } /** * Builds a navagation list * @param l the list of places to build it off of * @return the string of the order of places */ public String buildNav(LinkedList l){ String nav = ""; for(int i = l.size() - 1; i >= 0; i--){ place curPlace = (place)l.get(i); nav += "<a href=\"./hierarchy.jsp?pid=" + curPlace.getPlaceID() + "\">" + curPlace.getPlaceName() + "</a> > "; } return nav; } /** * Searches for the children of a particular parent * @return all of the places with their placeid and name */ public LinkedList letteredList(String letter) { /* Integer to build */ String t = ""; String output = ""; LinkedList list = new LinkedList(); try{ Connection conn = getConn(); /* Class statement is used to issue SQL statements to a database */ Statement s = conn.createStatement(); /* Class ResultSet contains the results from a query */ ResultSet r; /* Build up query */ String p1 = "SELECT * FROM place where pname LIKE '" + letter + "%'OR pname LIKE '" + letter + "%';"; String sql = p1; /* Execute query -- the results are in r. If no results are to be returned use int executeUpdate(String q) */ r = s.executeQuery(sql); /* A 'cursor' is used to move through results -- initially, the cursor is set BEFORE the first row in the result set. While next() returns TRUE then information is available */ while(r.next()) { /* Query generated a result -- now extract data by Name & Index */ int pid = r.getInt("pid"); int parent = r.getInt("parent"); String pname = r.getString("pname"); String comment = r.getString("comment"); int photoid = r.getInt("photoid"); place myPlace = new place(pid, parent, pname, comment, photoid); list.add(myPlace); } /*Not necessary here but good habit */ r.close(); }catch(Exception e) { t = e.toString(); e.printStackTrace(); } return list; } /** * Searches for the children of a particular parent * @return all of the places with their placeid and name */ public int numberOfChildren(int parent) { /* Integer to build */ String t = ""; String output = ""; int count = -1; try{ Connection conn = getConn(); /* Class statement is used to issue SQL statements to a database */ Statement s = conn.createStatement(); /* Class ResultSet contains the results from a query */ ResultSet r; /* Build up query */ String p1 = "SELECT COUNT(*) AS count FROM place WHERE parent = " + parent; String sql = p1; /* Execute query -- the results are in r. If no results are to be returned use int executeUpdate(String q) */ r = s.executeQuery(sql); /* A 'cursor' is used to move through results -- initially, the cursor is set BEFORE the first row in the result set. While next() returns TRUE then information is available */ if(r.next()) { /* Query generated a result -- now extract data by Name & Index */ count = r.getInt("count"); } /*Not necessary here but good habit */ r.close(); }catch(Exception e) { t = e.toString(); e.printStackTrace(); } return count; } }