Java tutorial
/*-------------------------------------------------------------------*/ /* */ /* Copyright IBM Corp. 2013 All Rights Reserved */ /* */ /*-------------------------------------------------------------------*/ /* */ /* NOTICE TO USERS OF THE SOURCE CODE EXAMPLES */ /* */ /* The source code examples provided by IBM are only intended to */ /* assist in the development of a working software program. */ /* */ /* International Business Machines Corporation provides the source */ /* code examples, both individually and as one or more groups, */ /* "as is" without warranty of any kind, either expressed or */ /* implied, including, but not limited to the warranty of */ /* non-infringement and the implied warranties of merchantability */ /* and fitness for a particular purpose. The entire risk */ /* as to the quality and performance of the source code */ /* examples, both individually and as one or more groups, is with */ /* you. Should any part of the source code examples prove defective, */ /* you (and not IBM or an authorized dealer) assume the entire cost */ /* of all necessary servicing, repair or correction. */ /* */ /* IBM does not warrant that the contents of the source code */ /* examples, whether individually or as one or more groups, will */ /* meet your requirements or that the source code examples are */ /* error-free. */ /* */ /* IBM may make improvements and/or changes in the source code */ /* examples at any time. */ /* */ /* Changes may be made periodically to the information in the */ /* source code examples; these changes may be reported, for the */ /* sample code included herein, in new editions of the examples. */ /* */ /* References in the source code examples to IBM products, programs, */ /* or services do not imply that IBM intends to make these */ /* available in all countries in which IBM operates. Any reference */ /* to the IBM licensed program in the source code examples is not */ /* intended to state or imply that IBM's licensed program must be */ /* used. Any functionally equivalent program may be used. */ /*-------------------------------------------------------------------*/ package com.ibm.bluemix.samples; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Date; import java.util.ArrayList; import java.util.List; import java.util.Map; import org.json.simple.JSONArray; import org.json.simple.JSONObject; import org.json.simple.parser.JSONParser; import org.apache.commons.io.FilenameUtils; /** * Contains methods to access and alter the PostgreSQL database */ @SuppressWarnings("unchecked") public class PostgreSQLReportedErrors { public PostgreSQLReportedErrors() { try { createTable(); } catch (Exception e) { e.printStackTrace(System.err); } } /** * Retrieve the file information * * @return JSON object with file information * @throws Exception TODO describe exception */ public JSONObject getFileInfo(String entryId) throws Exception { String sql = "SELECT * FROM reportedErrors WHERE entry_id = " + entryId; Connection connection = null; PreparedStatement statement = null; ResultSet results = null; try { connection = getConnection(); statement = connection.prepareStatement(sql); results = statement.executeQuery(); results.next(); JSONObject infoObject = new JSONObject(); infoObject.put("entry_id", Integer.toString(results.getInt("entry_id"))); infoObject.put("file_name", results.getString("file_name")); infoObject.put("s_language", results.getString("s_language")); infoObject.put("nlp_parser", results.getString("nlp_parser")); infoObject.put("upload_date", results.getDate("upload_date").toString()); infoObject.put("output_name", FilenameUtils.removeExtension(results.getString("file_name")) + ".xml"); return infoObject; } finally { if (results != null) { results.close(); } if (statement != null) { statement.close(); } if (connection != null) { connection.close(); } } } /** * Retrieve the original file data associated with a specific entry ID * * @return Bytes of the original file * @throws Exception TODO describe exception */ public byte[] getOriginalFile(String entryId) throws Exception { String sql = "SELECT file_data FROM reportedErrors WHERE entry_id = " + entryId; Connection connection = null; PreparedStatement statement = null; ResultSet results = null; try { connection = getConnection(); statement = connection.prepareStatement(sql); results = statement.executeQuery(); results.next(); return results.getBytes("file_data"); } finally { if (results != null) { results.close(); } if (statement != null) { statement.close(); } if (connection != null) { connection.close(); } } } /** * Retrieve the output file data associated with a specific entry ID * * @return Output XML file * @throws Exception TODO describe exception */ public byte[] getOutputFile(String entryId) throws Exception { String sql = "SELECT output_xml FROM reportedErrors WHERE entry_id = " + entryId; Connection connection = null; PreparedStatement statement = null; ResultSet results = null; try { connection = getConnection(); statement = connection.prepareStatement(sql); results = statement.executeQuery(); results.next(); return results.getBytes("output_xml"); } finally { if (results != null) { results.close(); } if (statement != null) { statement.close(); } if (connection != null) { connection.close(); } } } /** * Grab text from PostgreSQL * * @return List of Strings of text from PostgreSQL * * @throws Exception TODO describe exception */ public List<JSONObject> getResults() throws Exception { String sql = "SELECT * FROM reportedErrors ORDER BY entry_id DESC"; Connection connection = null; PreparedStatement statement = null; ResultSet results = null; try { connection = getConnection(); statement = connection.prepareStatement(sql); results = statement.executeQuery(); List<JSONObject> files = new ArrayList<JSONObject>(); while (results.next()) { // Generate a JSON object containing file information JSONObject singleFile = getFileInfo(Integer.toString(results.getInt("entry_id"))); files.add(singleFile); } return files; } finally { if (results != null) { results.close(); } if (statement != null) { statement.close(); } if (connection != null) { connection.close(); } } } /** * Insert text into PostgreSQL * * @param files * List of Strings of text to insert * * @return number of rows affected * * @throws Exception TODO describe exception */ public int addFile(String action_number, String make, String model, String year, String compname, String mfr_name, String odate, String cdate, String campno, String subject, String summary) throws Exception { String sql = "INSERT INTO reportedErrors (action_number, make, model, year, compname, mfr_name, odate, cdate, campno, subject, summary) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,)"; Connection connection = null; PreparedStatement statement = null; try { connection = getConnection(); connection.setAutoCommit(false); statement = connection.prepareStatement(sql); statement.setString(1, action_number); statement.setString(2, make); statement.setString(3, model); statement.setString(4, year); statement.setString(5, compname); statement.setString(6, mfr_name); statement.setString(7, odate); statement.setString(8, cdate); statement.setString(9, campno); statement.setString(10, subject); statement.setString(11, summary); statement.addBatch(); int[] rows = statement.executeBatch(); connection.commit(); return rows.length; } catch (SQLException e) { SQLException next = e.getNextException(); if (next != null) { throw next; } throw e; } finally { if (statement != null) { statement.close(); } if (connection != null) { connection.close(); } } } /** * Delete specific file from database * * @param entryId * the ID of the file in the database table * * @return number of rows affected * @throws Exception */ public int deleteFile(String entryId) throws Exception { String sql = "DELETE FROM reportedErrors WHERE entry_id = " + entryId; Connection connection = null; PreparedStatement statement = null; try { connection = getConnection(); statement = connection.prepareStatement(sql); return statement.executeUpdate(); } finally { if (statement != null) { statement.close(); } if (connection != null) { connection.close(); } } } /** * Delete all rows from PostgreSQL * * @return number of rows affected * * @throws Exception */ public int deleteAll() throws Exception { String sql = "DELETE FROM reportedErrors WHERE TRUE"; Connection connection = null; PreparedStatement statement = null; try { connection = getConnection(); statement = connection.prepareStatement(sql); return statement.executeUpdate(); } finally { if (statement != null) { statement.close(); } if (connection != null) { connection.close(); } } } /** * Establishes a connection to the database * * @return the established connection * * @throws Exception * a custom exception thrown if no postgresql service URL was found */ public static Connection getConnection() throws Exception { Map<String, String> env = System.getenv(); if (env.containsKey("VCAP_SERVICES")) { // we are running on cloud foundry, let's grab the service details from vcap_services JSONParser parser = new JSONParser(); JSONObject vcap = (JSONObject) parser.parse(env.get("VCAP_SERVICES")); JSONObject service = null; // We don't know exactly what the service is called, but it will contain "postgresql" for (Object key : vcap.keySet()) { String keyStr = (String) key; if (keyStr.toLowerCase().contains("postgresql")) { service = (JSONObject) ((JSONArray) vcap.get(keyStr)).get(0); break; } } if (service != null) { JSONObject creds = (JSONObject) service.get("credentials"); String name = (String) creds.get("name"); String host = (String) creds.get("host"); Long port = (Long) creds.get("port"); String user = (String) creds.get("user"); String password = (String) creds.get("password"); String url = "jdbc:postgresql://" + host + ":" + port + "/" + name; return DriverManager.getConnection(url, user, password); } } throw new Exception( "No PostgreSQL service URL found. Make sure you have bound the correct services to your app."); } /** * Create the files table if it doesn't already exist * * @throws Exception TODO describe exception */ private void createTable() throws Exception { String sql = "CREATE TABLE IF NOT EXISTS reportedErrors (" + "entry_id SERIAL PRIMARY KEY, " + "action_number TEXT, " + "make TEXT, " + "model TEXT, " + "year TEXT, " + "compname TEXT, " + "mfr_name TEXT, " + "odate TEXT, " + "cdate TEXT, " + "campno TEXT, " + "subject TEXT, " + "summary TEXT);"; Connection connection = null; PreparedStatement statement = null; try { connection = getConnection(); statement = connection.prepareStatement(sql); statement.executeUpdate(); } finally { if (statement != null) { statement.close(); } if (connection != null) { connection.close(); } } } }