/* filename: StateZipInfo.java */
package historyPlaces;

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

public class hp{

 /* 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();
 
 /* An ArrayList that contains all of the IDs needed to query for the timeline */
 private ArrayList photoID = new ArrayList();
 
 /*A LinkedList of Photos in database*/
 private LinkedList photoList = new LinkedList();
 
 /*A LinkedList of Places in database*/
 private LinkedList placeList = new LinkedList();
  
 /* Initializes the connection */
 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();
  }
 }
 
 private Connection getConn() {
    if (conn == null) {
     init();
    }
    return conn; 
 }
 
 public String getErrorInfo() {
  return "

" + DataBaseParam.getConnInfo() + debugString + "

"; } /* This function retuns various pieces 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; } /* Searches for the longitude and latitude by zip code */ 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 */ 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 */ 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 placeid = " + 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 */ 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; } 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; } /* Returns a single photo */ public LinkedList photoTimeline() { LinkedList timeline = new LinkedList(); return timeline; } /* return all photos in photo table*/ public LinkedList getAllPhoto() { String t; int photoid; photo myPhoto = new photo(-1,"","","","","",-1); try { Connection conn = getConn(); Statement s = conn.createStatement(); ResultSet r; String p1 = "SELECT * FROM photo order by date;"; String sql = p1; r = s.executeQuery(sql); while (r.next()) { photoid = r.getInt("photoid"); String name = r.getString("name"); String url = r.getString("url"); String comment = r.getString("comment"); //java.util.Date date = r.getDate("date"); String date = r.getString("date"); String photographer = r.getString("photographer"); int placeid = r.getInt("placeid"); myPhoto = new photo(photoid, name, url, comment, date, photographer, placeid); photoList.add(myPhoto); } r.close(); } catch(Exception e) { t = e.toString(); e.printStackTrace(); } return photoList; } public LinkedList getPeriodSpec(String s1, String s2, String s3) { String t; int photoid; photo myPhoto = new photo(-1,"","","","","",-1); try { Connection conn = getConn(); Statement s = conn.createStatement(); ResultSet r; String p1 = "SELECT * FROM photo f, place p"; String p2 = " WHERE date >= '" +s1 +"' AND date <= '" +s2+ "' AND p.photoid=f.photoid AND p.pname = '"+s3.trim()+"' " ; String p3 = "ORDER BY date DESC;"; String sql = p1+p2+p3; r = s.executeQuery(sql); while (r.next()) { photoid = r.getInt("photoid"); String name = r.getString("name"); String url = r.getString("url"); String comment = r.getString("comment"); //java.util.Date date = r.getDate("date"); String date = r.getString("date"); String photographer = r.getString("photographer"); int placeid = r.getInt("placeid"); myPhoto = new photo(photoid, name, url, comment, date, photographer, placeid); photoList.add(myPhoto); } r.close(); } catch(Exception e) { t = e.toString(); e.printStackTrace(); } return photoList; } public LinkedList getAllInPeriod(String q1, String q2) { String t; int photoid; photo myPhoto = new photo(-1,"","","","","",-1); try { Connection conn = getConn(); Statement s = conn.createStatement(); ResultSet r; String p1 = "SELECT * FROM photo "; String p2 = " WHERE date >= '"+q1 +"' AND date <= '"+q2+"' " ; String p3 = "ORDER BY date DESC;"; String sql = p1+p2+p3; r = s.executeQuery(sql); while (r.next()) { photoid = r.getInt("photoid"); String name = r.getString("name"); String url = r.getString("url"); String comment = r.getString("comment"); //java.util.Date date = r.getDate("date"); String date = r.getString("date"); String photographer = r.getString("photographer"); int placeid = r.getInt("placeid"); myPhoto = new photo(photoid, name, url, comment, date, photographer, placeid); photoList.add(myPhoto); } r.close(); } catch(Exception e) { t = e.toString(); e.printStackTrace(); } return photoList; } public LinkedList getSpecPlaceAllTime(String q) { String t; int photoid; photo myPhoto = new photo(-1,"","","","","",-1); try { Connection conn = getConn(); Statement s = conn.createStatement(); ResultSet r; String p1 = "SELECT * FROM photo f, place p"; String p2 = " WHERE p.photoid=f.photoid AND p.pname ='"+q.trim()+"'"; String p3 = "ORDER BY date DESC;"; String sql = p1+p2+p3; r = s.executeQuery(sql); while (r.next()) { photoid = r.getInt("photoid"); String name = r.getString("name"); String url = r.getString("url"); String comment = r.getString("comment"); //java.util.Date date = r.getDate("date"); String date = r.getString("date"); String photographer = r.getString("photographer"); int placeid = r.getInt("placeid"); myPhoto = new photo(photoid, name, url, comment, date, photographer, placeid); photoList.add(myPhoto); } r.close(); } catch(Exception e) { t = e.toString(); e.printStackTrace(); } return photoList; } public int AddPhoto(String photo, String filePath, String comment, String date, String photographer, String placeName) { String t; int add = -1; int placeid = -1; //boolean add =false; photo myPhoto = new photo(-1,"","","","","",-1); try { Connection conn = getConn(); Statement s = conn.createStatement(); ResultSet r; String s1 = "SELECT pid FROM place WHERE pname LIKE '%"+placeName.trim()+"%';"; r = s.executeQuery(s1); if (r.next()) { placeid = r.getInt("pid"); System.out.println(placeid); String p1 = "INSERT INTO photo (name,url,comment,date,photographer, placeid)"; String p2 = " VALUES ('" + photo + "', '" + filePath + "','" + comment + "','"+date+"','"+photographer+"'," + placeid + ");"; String sql = p1+p2; add = s.executeUpdate(sql); //add = true; } r.close(); // r.close(); } catch(Exception e) { t = e.toString(); e.printStackTrace(); add = -1; } return add; } /* similar to printParents in Ryan's implementation */ public String printAllPlace() { /* 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 ORDER BY pname"; 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 */ output = ""; while(r.next()) { /* Query generated a result -- now extract data by Name & Index */ int pid = r.getInt("pid"); String pname = r.getString("pname"); output += "\n"; } /*Not necessary here but good habit */ r.close(); }catch(Exception e) { t = e.toString(); e.printStackTrace(); } return output; } }