com.ibm.bluemix.samples.PostgreSQLReportedErrors.java Source code

Java tutorial

Introduction

Here is the source code for com.ibm.bluemix.samples.PostgreSQLReportedErrors.java

Source

/*-------------------------------------------------------------------*/
/*                                                                   */
/* 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();
            }
        }
    }
}