/* * To change this template, choose Tools | Templates * and open the template in the editor. */ package databasePackage; //Basic dataholders are prepped here import dataHolderPackage.ZoneStorage; import dataHolderPackage.ZonePhoto; import dataHolderPackage.Thumbnail; import dataHolderPackage.MainPhoto; import dataHolderPackage.DownloadableImage; //SQL Imports import dataHolderPackage.PhotoExpirationLogHolder; import java.net.URLEncoder; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.sql.Date; import java.sql.ResultSet; import java.sql.Time; /** * * */ public class ZoneToDatabaseConnector { //Change as appropriate private final static String user = "root"; private final static String password = "qwertyuiop"; private Connection con; private Statement st; private Integer photo_expiration_id = 0; private final static String db = "iat_352_project_3_alexander_ryan"; //Non-flat query statement /* CREATE TABLE `lines_table` ( `ID` int(11) NOT NULL, `LINE` varchar(45) DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB */ private final static String LINES_TABLE = "CREATE TABLE iat_352_project_3_alexander_ryan.lines_table (\n`ID` int(11) NOT NULL,\n`LINE` varchar(45) DEFAULT NULL,\n PRIMARY KEY (`ID`));"; //EXTREMELY LONG QUERY FOR CREATING ZONE STORAGE TABLES WITH LOCATION DATA - DO NOT TOUCH //Non-flat query statement: /* CREATE TABLE `locations` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `TITLE` varchar(255) NOT NULL, `DESCRIPTION` varchar(1024) NOT NULL, `ZONE` int(11) NOT NULL, `LINES` int(11) NOT NULL, `LATITUDE` double NOT NULL, `LONGITUDE` double NOT NULL, PRIMARY KEY (`ID`), KEY `LINES_ID` (`LINES`), CONSTRAINT `LINES_ID` FOREIGN KEY (`LINES`) REFERENCES `lines_table` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION ) ENGINE=InnoDB */ private final static String LOCATIONS = "CREATE TABLE iat_352_project_3_alexander_ryan.locations (`ID` int(11) NOT NULL AUTO_INCREMENT, `TITLE` varchar(255) NOT NULL, `DESCRIPTION` varchar(1024) NOT NULL, `ZONE` int(11) NOT NULL, `LINES` int(11) NOT NULL, `LATITUDE` double NOT NULL, `LONGITUDE` double NOT NULL, PRIMARY KEY (`ID`), KEY `LINES_ID` (`LINES`), CONSTRAINT `LINES_ID` FOREIGN KEY (`LINES`) REFERENCES `iat_352_project_3_alexander_ryan`.`lines_table` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION); "; //Non-flat query statement: /* CREATE TABLE `photo_expiration_log` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `TIME_OF_QUERY` time NOT NULL, `DATE_OF_QUERY` date DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB */ private final static String PHOTO_EXPIRATION_LOG = "CREATE TABLE iat_352_project_3_alexander_ryan.photo_expiration_log ( `ID` int(11) NOT NULL AUTO_INCREMENT, `TIME_OF_QUERY` time NOT NULL, `DATE_OF_QUERY` date DEFAULT NULL, PRIMARY KEY (`ID`));"; //EXTEREMLY LONG QUERY FOR CREATING PHOTOS TABLE - DO NOT TOUCH //"LATITIDUE" == "LATITUDE", just mis-spelt here. //Therefore, consistent in code //DO NOT TOUCH QUERY //Non-flat query statement: /* CREATE TABLE `photos` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `LOCATION_ID` int(11) NOT NULL, `PHOTO_EXPIRATION_LOG_ID` int(11) NOT NULL, `LATITIDUE` double NOT NULL, `LONGITUDE` double NOT NULL, `T_URL` varchar(1024) DEFAULT NULL, `T_WIDTH` int(11) NOT NULL DEFAULT '100', `T_HEIGHT` int(11) NOT NULL DEFAULT '75', `M_URL` varchar(1024) DEFAULT NULL, `M_WIDTH` int(11) NOT NULL DEFAULT '400', `M_HEIGHT` int(11) NOT NULL DEFAULT '320', `D_URL` varchar(1024) DEFAULT NULL, `D_WIDTH` int(11) NOT NULL DEFAULT '1024', `D_HEIGHT` int(11) NOT NULL DEFAULT '800', `TITLE` varchar(1024) DEFAULT NULL, `ALTERNATE` varchar(1024) DEFAULT NULL, PRIMARY KEY (`ID`), KEY `LOCATION_ID` (`LOCATION_ID`), KEY `EXPIRATION_ID` (`PHOTO_EXPIRATION_LOG_ID`), CONSTRAINT `EXPIRATION_ID` FOREIGN KEY (`PHOTO_EXPIRATION_LOG_ID`) REFERENCES `photo_expiration_log` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `LOCATION_ID` FOREIGN KEY (`LOCATION_ID`) REFERENCES `locations` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB */ private final static String PHOTOS = "CREATE TABLE iat_352_project_3_alexander_ryan.photos ( `ID` int(11) NOT NULL AUTO_INCREMENT, `LOCATION_ID` int(11) NOT NULL, `PHOTO_EXPIRATION_LOG_ID` int(11) NOT NULL, `LATITIDUE` double NOT NULL, `LONGITUDE` double NOT NULL, `T_URL` varchar(1024) DEFAULT NULL, `T_WIDTH` int(11) NOT NULL DEFAULT '100', `T_HEIGHT` int(11) NOT NULL DEFAULT '75', `M_URL` varchar(1024) DEFAULT NULL, `M_WIDTH` int(11) NOT NULL DEFAULT '400', `M_HEIGHT` int(11) NOT NULL DEFAULT '320', `D_URL` varchar(1024) DEFAULT NULL, `D_WIDTH` int(11) NOT NULL DEFAULT '1024', `D_HEIGHT` int(11) NOT NULL DEFAULT '800', `TITLE` varchar(1024) DEFAULT NULL, `ALTERNATE` varchar(1024) DEFAULT NULL, PRIMARY KEY (`ID`), KEY `LOCATION_ID` (`LOCATION_ID`), KEY `EXPIRATION_ID` (`PHOTO_EXPIRATION_LOG_ID`), CONSTRAINT `EXPIRATION_ID` FOREIGN KEY (`PHOTO_EXPIRATION_LOG_ID`) REFERENCES `photo_expiration_log` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `LOCATION_ID` FOREIGN KEY (`LOCATION_ID`) REFERENCES `locations` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION);"; /** * Constructs the connection to the database - if the database doesn't exist, re-creates it. **/ public ZoneToDatabaseConnector() { //Base constructor sets up connection System.out.println("Getting connection Setup"); con = null; String url = "jdbc:mysql://localhost:3306/"; String driver = "com.mysql.jdbc.Driver"; try { Class.forName(driver); con = DriverManager.getConnection(url + db, user, password); st = con.createStatement(); } catch (Exception e) { System.out.println("Connection not established (1/2)"); e.printStackTrace(); try{ // con.close(); // st.close(); System.out.println("Creating database by scratch."); con = DriverManager.getConnection(url, user, password); st = con.createStatement(); st.executeUpdate("CREATE DATABASE "+ db); System.out.println("Created database by scratch."); st.close(); con.close(); System.out.println("Closed connection to database."); con = DriverManager.getConnection(url + db, user, password); st = con.createStatement(); System.out.println("Refresing connection"); boolean successful = createTables(); if(successful){ System.out.println("Connection not established (2/2)"); }else{ System.out.println("Connection created and established (1/1)"); } }catch(Exception i){ System.out.println("Connection not established (2/3)"); i.printStackTrace(); System.out.println("Connection not established (3/3)"); } } System.out.println("Getting connection Setup: finished."); } public boolean createTables(){ boolean hasFailed = false; //TODO: Database creation here try{ //TODO: Each table as a separate statement System.out.println("EXECUTING QUERY: \n" + URLEncoder.encode(LINES_TABLE, "UTF8")); int val = st.executeUpdate(LINES_TABLE); System.out.println("lines_table added: " + val); System.out.println("EXECUTING QUERY: \n" + URLEncoder.encode(LOCATIONS, "UTF8")); val = st.executeUpdate(LOCATIONS); System.out.println("locations added: " + val); System.out.println("EXECUTING QUERY: \n" + URLEncoder.encode(PHOTO_EXPIRATION_LOG, "UTF8")); val = st.executeUpdate(PHOTO_EXPIRATION_LOG); System.out.println("photo_expiration_log added: " + val); System.out.println("EXECUTING QUERY: \n" + URLEncoder.encode(PHOTOS, "UTF8")); val = st.executeUpdate(PHOTOS); System.out.println("photos added: " + val); System.out.println("INSERT STATEMENTS HERE - adding in data"); //TODO: Insert statements here System.out.println("Inserting in lines"); st.executeUpdate("INSERT INTO LINES_TABLE (`ID`, `LINE`) VALUES (0, 'Expo');"); st.executeUpdate("INSERT INTO LINES_TABLE (`ID`, `LINE`) VALUES (1, 'Millenium');"); st.executeUpdate("INSERT INTO LINES_TABLE (`ID`, `LINE`) VALUES (2, 'Expo & Millenium');"); System.out.println("Lines inserted"); System.out.println("Inserting in locations"); st.executeUpdate("INSERT INTO LOCATIONS (`ID`, `TITLE`, `DESCRIPTION`, `ZONE`, `LINES`, `LATITUDE`, `LONGITUDE`) VALUES (1, 'Columbia Station', 'This station runs at the intersection between Surrey and the rest of the Metro Vancouver area. Going east, it splits off towards Surrey on the Expo Line, and around Coquitlam and Burnaby on the Millennium Line. Well known as being the point of last return when going there, as going further east on either line requires you to backtrack if you want to revert said decision. West, it continues until Waterfront.', 2, 2, 49.204883, -122.905984);"); st.executeUpdate("INSERT INTO LOCATIONS (`ID`, `TITLE`, `DESCRIPTION`, `ZONE`, `LINES`, `LATITUDE`, `LONGITUDE`) VALUES (2, 'Surrey Central Station', 'This station runs just before the end of the Expo line, and is the new focus of newest developments in Surrey, from new apartment housing, to the new Central City Library and upcoming City Council building. Next to the Simon Fraser University Surrey Campus, and to the bustling transit hub for all of Surrey, this is the main focus for all of Surrey today.', 1, 0, 49.188948, -122.847879);"); st.executeUpdate("INSERT INTO LOCATIONS (`ID`, `TITLE`, `DESCRIPTION`, `ZONE`, `LINES`, `LATITUDE`, `LONGITUDE`) VALUES (3, 'Metrotown Station', 'Located in the heart of Vancouver, this is home to the Metrotown Skytrain station, this station is host to the Metrotown mall, which drives daily visitors to the station for recreational activities and related events. Alongside Surrey Central and Waterfront, provides the Skytrain with the majority of its traffic.', 3, 2, 49.225677, -123.003803);"); System.out.println("Locations inserted"); //Not inserting photo_expiration_log or photo entries as these would likely just be deleted anyways. }catch(Exception w){ System.out.println("Table query problem: (1/2)"); w.printStackTrace(); System.out.println("Table query problem: (2/2)"); hasFailed = true; } return hasFailed; } //Now we do actual expected code, such as pulling data or comitting it, as appropriate. // public ArrayList pull() { //Pulling logic here, and return all ZoneStorages (NOTE: Only ones that //will work throughout the XML output - leave out corrupted Data ZoneStorages [No photos, no id, etc.]) ArrayList zones = new ArrayList(); ArrayList lineMappings = new ArrayList(); //What we'll return ArrayList finalList = new ArrayList(); try { //Pulls lines as foreign keys System.out.println("Pulling foreign lines data"); System.out.println("Executing query: SELECT * FROM " + db + ".lines_table;"); ResultSet lineRes = st.executeQuery("SELECT * FROM " + db + ".lines_table;"); System.out.println("Adding Lines to array"); while (lineRes.next()) { System.out.println("Adding line"); lineMappings.add(lineRes.getString("LINE")); } System.out.println("Pulling location data for each zone"); System.out.println("Executing query: SELECT * FROM " + db + ".locations;"); ResultSet zoneRes = st.executeQuery("SELECT * FROM " + db + ".locations;"); System.out.println("Parsing Zone data now"); while (zoneRes.next()) { System.out.println("Adding zone"); ZoneStorage zone = new ZoneStorage(); //Requires uppper conversion to string - rather complicated workaround of problemin System.out.println("Getting ID"); int x = zoneRes.getInt("ID"); Integer y = x; zone.setIdentifier(y.toString()); System.out.println(zone.getIdentifier()); System.out.println("Getting LINES"); int lineID = zoneRes.getInt("LINES"); zone.setLines(lineMappings.get(lineID)); System.out.println(zone.getLines()); //Requires uppper conversion to string - rather complicated workaround of problemin System.out.println("Getting ZONE"); int zoneSpace = zoneRes.getInt("ZONE"); Integer zoneSpaceAsInteger = zoneSpace; zone.setZone(zoneSpaceAsInteger.toString()); System.out.println(zone.getZone()); System.out.println("Getting TITLE"); zone.setTitle(zoneRes.getString("TITLE")); System.out.println(zone.getDescription()); System.out.println("Getting DESCRIPTION"); zone.setDescription(zoneRes.getString("DESCRIPTION")); System.out.println(zone.getDescription()); //Requires uppper conversion to string - rather complicated workaround of problemin System.out.println("Getting LATITDUE"); double t = zoneRes.getDouble("LATITUDE"); Double w = t; zone.setLatitude(w.toString()); System.out.println(zone.getLatitude()); //Requires uppper conversion to string - rather complicated workaround of problemin System.out.println("Getting LONGITUDE"); double q = zoneRes.getDouble("LONGITUDE"); Double r = q; zone.setLongitude(r.toString()); System.out.println(zone.getLongitude()); System.out.println("Finished uploading base location data for location"); zones.add(zone); } //Now that we've stored the zones themselves, try and find photos via database or Flickr API, if database doesn't provide them. //We have to do this separately, otherwise the previous ResultSet object will close, and then we only have one Zone pulled out. System.out.println("Finding photos in database - if no images found, not using Zone"); for (ZoneStorage zone : zones) { if (pullImages(zone)) { System.out.println("Adding zone: " + zone.getIdentifier()); System.out.println("Zone Photos: " + zone.getPhotoSize()); finalList.add(zone); } else { System.out.println("Not adding zone - not enough photos: " + zone.getIdentifier()); System.out.println("Zone Photos: " + zone.getPhotoSize()); } } } catch (SQLException p) { System.out.println("SQL code does not execute: (1/2)"); p.printStackTrace(); System.out.println("SQL code does not execute: (2/2)"); } return finalList; } public boolean pullImages(ZoneStorage location) { //TODO: Based off of locationId, pull images if, and only if, they are within the expiration period java.util.Date compareDate = new java.util.Date(); Integer month = compareDate.getMonth(); Integer year = compareDate.getYear(); Integer date = compareDate.getDate(); Integer hour = compareDate.getHours(); Integer minute = compareDate.getMinutes(); //Converted to SQL date Date compareableDate = new Date(year, month, date); //We don't care about seconds - it'll always be 0 for us Time compareableTime = new Time(hour, minute, 0); System.out.println("Pulling images from database if possible"); try { //Start off by making a spare photo_expiration period - if we //don't find photos for a place, we'll pull them from flickr, //then store them associated to this time for pulling out next //time. System.out.println("If not, storing FLICKR API for time: " + compareableTime + "," + compareableDate); photo_expiration_id = st.executeUpdate("INSERT photo_expiration_log(TIME_OF_QUERY, DATE_OF_QUERY) VALUES('" + compareableTime + "', '" + compareableDate + "');"); System.out.println("Not Identifier of logged time - need to pull elsewhere, just return value of execute statement: " + photo_expiration_id); //Pull out all previous photo_expiration periods, indicating when photos were possibly pulled System.out.println("Executing Query: SELECT * FROM " + db + ".photo_expiration_log;"); ResultSet res = st.executeQuery("SELECT * FROM " + db + ".photo_expiration_log;"); System.out.println("Images previously stored from the following dates still stored in database."); System.out.println("Date: " + "\t\t" + "Time: "); // ArrayList photoExpirationLogHolder = new ArrayList(); ArrayList removablePhotoExpirationLogHolders = new ArrayList(); while (res.next()) { //Through each one of the times we may have pulled images int pEIds = res.getInt("ID"); Integer pEId = pEIds; if(photo_expiration_id < pEId){ photo_expiration_id = pEId; } Date i = res.getDate("DATE_OF_QUERY"); Time s = res.getTime("TIME_OF_QUERY"); System.out.println(i.toString() + "\t\t" + s.toString()); //Check for the time if (i.compareTo(compareableDate) == 0 || (i.getDate() >= compareableDate.getDate() - 1 && (s.getHours() > compareableTime.getHours()))) { //So, if the day is the same, *or* if // The day value is one day in the past *and* if //It hasn't been more than 24 hours System.out.println("Taking photos from this time period"); photoExpirationLogHolder.add(new PhotoExpirationLogHolder(pEId, i, s)); } else { //If they're out of date, we ignore them. System.out.println("Ignoring photos from date"); removablePhotoExpirationLogHolders.add(new PhotoExpirationLogHolder(pEId, i, s)); } } System.out.println("Removing expired photo references"); for(PhotoExpirationLogHolder pELHR: removablePhotoExpirationLogHolders){ System.out.println("Removing expired photos from: " + pELHR.getDate() + " @Time: " + pELHR.getTime() + "With identifier: " + pELHR.getIdentifier()); System.out.println("Executing Query: " + "DELETE FROM " + db + ".photo_expiration_log WHERE ID=" + pELHR.getIdentifier() + ";"); int val = st.executeUpdate("DELETE FROM " + db + ".photo_expiration_log WHERE ID=" + pELHR.getIdentifier()+ ";"); } System.out.println("Begining actual photo expiration confirmation"); for (PhotoExpirationLogHolder pELH : photoExpirationLogHolder) { System.out.println("Pulling photos from date: " + pELH.getDate() + "@Time: " + pELH.getTime()); } //Having problems filtering for the right photos, so pulling all data first, then skipping un-related photos System.out.println("Begining actual photo pulling from database for all times above at location " + location.getIdentifier()); System.out.println("Executing Query: " + "SELECT * FROM " + db + ".photos;"); ResultSet phoRes = st.executeQuery("SELECT * FROM " + db + ".photos;"); while (phoRes.next()) { //Checking against database stored id int locId = phoRes.getInt("LOCATION_ID"); Integer locIdUse = locId; System.out.println("Comparing location id: " + location.getIdentifier() + " vs. photo location id: " + locIdUse); if(!location.getIdentifier().equals(locIdUse.toString())){ //If they are not the same, then we skip this image. System.out.println("Photo not related to location id - ignoring photo here"); continue; } System.out.println("Adding new photo"); ZonePhoto fPhoto = new ZonePhoto(); int pID = phoRes.getInt("ID"); Integer pIDUse = pID; fPhoto.setIdentifier(pIDUse); System.out.println("Photo ID: " + fPhoto.getIdentifier()); double lat = phoRes.getDouble("LATITIDUE"); Double latUse = lat; fPhoto.setLatitude(latUse); System.out.println("Latitude: " + fPhoto.getLatitude()); double lon = phoRes.getDouble("LONGITUDE"); Double lonUse = lon; fPhoto.setLongitude(lonUse); System.out.println("Longitude: " + fPhoto.getLongitude()); String titleUse = phoRes.getString("TITLE"); fPhoto.setTitle(titleUse); System.out.println("Title: " + fPhoto.getTitle()); //Technically, Alt doesn't really matter, so we don't pull it out - it's always the same as Title. //Now we get photo data String tURLUse = phoRes.getString("T_URL"); System.out.println("Thumbnail URL: " + tURLUse); int tWidth = phoRes.getInt("T_WIDTH"); Integer tWidthUse = tWidth; System.out.println("Thumbnail Width: " + tWidthUse); int tHeight = phoRes.getInt("T_Height"); Integer tHeightUse = tHeight; System.out.println("Thumbnail Height: " + tHeightUse); System.out.println("Adding Thumbnail..."); fPhoto.setThumbnail(tURLUse, tWidthUse.toString(), tHeightUse.toString()); String mURLUse = phoRes.getString("M_URL"); System.out.println("Main URL: " + mURLUse); int mWidth = phoRes.getInt("M_WIDTH"); Integer mWidthUse = mWidth; System.out.println("Main Width: " + mWidthUse); int mHeight = phoRes.getInt("M_Height"); Integer mHeightUse = mHeight; System.out.println("Main Height: " + mHeightUse); System.out.println("Adding Main Image..."); fPhoto.setMainImage(mURLUse, mWidthUse.toString(), mHeightUse.toString()); String dURLUse = phoRes.getString("D_URL"); System.out.println("Download URL: " + dURLUse); int dWidth = phoRes.getInt("D_WIDTH"); Integer dWidthUse = dWidth; System.out.println("Download Width: " + dWidthUse); int dHeight = phoRes.getInt("D_Height"); Integer dHeightUse = dHeight; System.out.println("Download Height: " + dHeightUse); System.out.println("Adding Downloadable Image..."); fPhoto.setDownloadImage(dURLUse, dWidthUse.toString(), dHeightUse.toString()); System.out.println("Adding photo"); location.getPhotos().add(fPhoto); System.out.println("Added Photo successfully"); } // con.close(); } catch (SQLException s) { System.out.println("SQL code does not execute: (1/2)"); s.printStackTrace(); System.out.println("SQL code does not execute: (2/2)"); return false; } // ArrayList photoArray = new ArrayList(); if (location.getPhotoSize() < 1) {//If the photos weren't previously added, //Then we force a Flickr API call System.out.println("No photos found in database for location: " + location.getIdentifier()); System.out.println("Initiating Flickr API call"); location.initiatePhotos(Double.parseDouble(location.getLatitude()), Double.parseDouble(location.getLongitude()), "Skytrain", 5); System.out.println("Finished API call: Location has " + location.getPhotoSize() + " photos."); System.out.println("Committing to database(1/2)..."); //Now that we've got the Flickr API call images for ourselves, we store them in the database commitPhotoValues(location.getPhotos(), location); System.out.println("Committed to database(2/2)"); } System.out.println("Photos found in database/API for location: " + location.getIdentifier() + " : " + location.getPhotoSize()); //Tells us if the zone has at least one photo to it. return location.getPhotoSize() > 0; } private boolean commitPhotoValues(ArrayList insertablePhotos, ZoneStorage location) { System.out.println("Inserting values in Mysql database table 'PHOTOS'!"); boolean didNotError = true; //TODO: Create Photo_expiration_entry //See pullImages() Integer locationId = Integer.parseInt(location.getIdentifier()); for (ZonePhoto zp : insertablePhotos) { try { String title = zp.getTitle(); Double lat = zp.getLatitude(); Double lon = zp.getLongitude(); String t_url = zp.getThumbnail().getUrl(); Integer t_width = Integer.parseInt(zp.getThumbnail().getWidth()); Integer t_height = Integer.parseInt(zp.getThumbnail().getHeight()); String m_url = zp.getMainImage().getUrl(); Integer m_width = Integer.parseInt(zp.getMainImage().getWidth()); Integer m_height = Integer.parseInt(zp.getMainImage().getHeight()); //For not getting the download image, displays "404" sign. Just for debug purposes, it creates a checkable record String d_url = "http://us.123rf.com/400wm/400/400/arcady31/arcady311011/arcady31101100012/8157731-404-error-sign.jpg"; Integer d_width = 0; Integer d_height = 0; //Null check on Download Image - doesn't always take, so if I decide not to use it, these are basically blank cells if (zp.getDownloadImage() != null && zp.getDownloadImage().getUrl() != null && zp.getDownloadImage().getWidth() != null && zp.getDownloadImage().getHeight() != null) { d_url = zp.getDownloadImage().getUrl(); d_width = Integer.parseInt(zp.getDownloadImage().getWidth()); d_height = Integer.parseInt(zp.getDownloadImage().getHeight()); } //For debug purposes, make clear what the query is System.out.println("Executing query: " + "INSERT INTO photos(LOCATION_ID, PHOTO_EXPIRATION_LOG_ID, LATITIDUE, LONGITUDE, " + "T_URL, T_WIDTH, T_HEIGHT, M_URL, M_WIDTH', M_HEIGHT, D_URL, D_WIDTH, D_HEIGHT, TITLE, ALTERNATE)" + " VALUES(" + locationId + "," + photo_expiration_id + "," + lat + "," + lon + ",'" + t_url + "'," + t_width + "," + t_height + ",'" + m_url + "'," + m_width + "," + m_height + ",'" + d_url + "'," + d_width + "," + d_height + ",'" + title + "','" + title + "');"); //Then execute int val = st.executeUpdate("INSERT INTO photos(LOCATION_ID, PHOTO_EXPIRATION_LOG_ID, LATITIDUE, LONGITUDE, " + "T_URL, T_WIDTH, T_HEIGHT, M_URL, M_WIDTH, M_HEIGHT, D_URL, D_WIDTH, D_HEIGHT, TITLE, ALTERNATE)" + "VALUES(" + locationId + ", " + photo_expiration_id + ", " + lat + ", " + lon + ", '" + t_url + "', " + t_width + ", " + t_height + ", '" + m_url + "', " + m_width + ", " + m_height + ", '" + d_url + "', " + d_width + ", " + d_height + ", '" + title.replace("'", "_").replace("`", "_") + "', '" + title.replace("'", "_").replace("`", "_") + "');"); System.out.println(val + " row(s) affected"); } catch (SQLException s) { System.out.println("SQL statement is not executed - table may not exist! (1/2)"); s.printStackTrace(); System.out.println("SQL statement is not executed - table may not exist! (2/2)"); didNotError = false; } } return didNotError; } }