com.tinyhydra.botd.sql.SQLInterface.java Source code

Java tutorial

Introduction

Here is the source code for com.tinyhydra.botd.sql.SQLInterface.java

Source

package com.tinyhydra.botd.sql;

import com.tinyhydra.botd.JSONvalues;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.*;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Brew of the day
 * Copyright (C) 2012  tinyhydra.com
 * *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
public class SQLInterface {

    public Connection con;

    public SQLInterface(Connection con) {
        this.con = con;
    }

    // Tables
    private final String dbName = new SQLConnector().getDbName();
    private final String shopsTable = dbName + ".shops";
    private final String votesTable = dbName + ".votes";

    // Shops fields
    private final String shopsIdField = "shop_id";
    private final String shopsNameField = "shop_name";
    private final String shopsUrlField = "shop_url";
    private final String shopsVicinityField = "shop_vicinity";
    private final String shopsReferenceField = "shop_reference";

    // Votes fields
    private final String votesUsernameField = "votes_username";
    private final String votesDateField = "votes_date";
    private final String votesShopVoteField = "votes_shop_vote";
    private final String votesPointsField = "votes_vote_points";

    // QUERY STRINGS
    private final String qSELECT = " SELECT ";
    private final String qINSERT = " INSERT ";
    private final String qUPDATE = " UPDATE ";
    private final String qALL = " * ";
    private final String qDISTINCT = " DISTINCT ";
    private final String qDUPLICATE = " DUPLICATE ";
    private final String qKEY = " KEY ";
    private final String qINTO = " INTO ";
    private final String qFROM = " FROM ";
    private final String qJOIN = " JOIN ";
    private final String qGROUP = " GROUP ";
    private final String qORDER = " ORDER ";
    private final String qBY = " BY ";
    private final String qDESC = " DESC ";
    private final String qLIMIT = " LIMIT ";
    private final String qSET = " SET ";
    private final String qON = " ON ";
    private final String qWHERE = " WHERE ";
    private final String qAND = " AND ";
    private final String qEQUALS = " = ";
    private final String qEQUALS_VALUE = " = ?";

    // QUERY TEMPLATE

    private Object QueryMethodTemplate() {
        String table = "";
        String field = "";
        String value = "";

        Object returnObject = null;

        PreparedStatement getObjectStmt = null;
        String getObjectQuery = qSELECT + qALL + qFROM + table + qWHERE + field + qEQUALS_VALUE;

        ResultSet rs = null;
        try {
            if (con == null || con.isClosed())
                con = new SQLConnector().getConnection();

            getObjectStmt = con.prepareStatement(getObjectQuery);
            getObjectStmt.setString(1, value);

            rs = getObjectStmt.executeQuery();
            while (rs.next()) {
                returnObject = new Object();
            }

        } catch (SQLException sqe) {
            System.out.println(sqe);
        } finally {
            closeStatement(getObjectStmt);
        }
        if (rs == null)
            return null;

        return returnObject;
    }

    // this is 'get top 10' now. returns top 10 ranking shops for the day in json format
    //TODO: return count for display in the app
    public JSONArray GetBrewOfTheDay() {
        JSONArray brewRankings = new JSONArray();

        PreparedStatement getBrewOfTheDayStmt = null;
        String getBrewOfTheDayQuery = qSELECT + shopsTable + "." + shopsIdField + "," + shopsTable + "."
                + shopsNameField + "," + shopsTable + "." + shopsUrlField + "," + shopsTable + "."
                + shopsVicinityField + "," + shopsTable + "." + shopsReferenceField + "," + "COUNT(*) AS cnt"
                + qFROM + votesTable + qJOIN + shopsTable + qON + votesTable + "." + votesShopVoteField + qEQUALS
                + shopsTable + "." + shopsIdField + qWHERE + votesTable + "." + votesDateField + qEQUALS_VALUE

                + qGROUP + qBY + votesTable + "." + votesShopVoteField + qORDER + qBY + "cnt" + qDESC + qLIMIT
                + "10";

        ResultSet rs = null;
        try {
            if (con == null || con.isClosed())
                con = new SQLConnector().getConnection();

            getBrewOfTheDayStmt = con.prepareStatement(getBrewOfTheDayQuery);

            getBrewOfTheDayStmt.setLong(1, GetDate());

            rs = getBrewOfTheDayStmt.executeQuery();
            while (rs.next()) {
                try {
                    JSONObject jo = new JSONObject();
                    jo.put(JSONvalues.shopId.toString(), rs.getString(shopsIdField));
                    jo.put(JSONvalues.shopName.toString(), rs.getString(shopsNameField));
                    jo.put(JSONvalues.shopUrl.toString(), rs.getString(shopsUrlField));
                    jo.put(JSONvalues.shopVicinity.toString(), rs.getString(shopsVicinityField));
                    jo.put(JSONvalues.shopRef.toString(), rs.getString(shopsReferenceField));
                    jo.put(JSONvalues.shopVotes.toString(), rs.getString("cnt"));
                    brewRankings.put(jo);
                } catch (JSONException jex) {
                    System.out.println(jex);
                }
                System.out.println(rs.getString(shopsReferenceField));
            }

        } catch (SQLException sqe) {
            System.out.println(sqe);
        } finally {
            closeStatement(getBrewOfTheDayStmt);
        }
        if (rs == null)
            return null;

        return brewRankings;
    }

    // accepts a user's vote and stores it in the DB. Checks for an existing vote and returns '1' if this is the case
    //TODO: allow user to update their vote, and return some other int code for that.
    public boolean SubmitVote(String username, String shop_id, int points, String shop_reference) {
        boolean addSuccess = false;

        GetShopDataAndAdd(shop_reference);

        PreparedStatement insertVoteStmt = null;

        String insertVoteQuery = qINSERT + qINTO + votesTable + " (" + votesUsernameField + "," + votesDateField
                + "," + votesShopVoteField + "," + votesPointsField + ") VALUES(?,?,?,?)";
        //+ qON + qDUPLICATE + qKEY + qUPDATE + votesShopVoteField + qEQUALS_VALUE;

        try {
            if (con == null || con.isClosed())
                con = new SQLConnector().getConnection();
            insertVoteStmt = con.prepareStatement(insertVoteQuery);

            insertVoteStmt.setString(1, username);
            insertVoteStmt.setLong(2, GetDate());
            insertVoteStmt.setString(3, shop_id);
            insertVoteStmt.setInt(4, points);

            insertVoteStmt.execute();

            addSuccess = true;

        } catch (SQLException sqe) {
            addSuccess = false;
            System.out.println(sqe);
        } finally {
            closeStatement(insertVoteStmt);
        }

        return addSuccess;
    }

    private void GetShopDataAndAdd(final String reference) {
        new Thread() {
            @Override
            public void run() {
                try {
                    Properties creds = new Properties();
                    InputStream inputStream = this.getClass().getClassLoader()
                            .getResourceAsStream("conf/credentials.properties");
                    creds.load(inputStream);

                    URL url;
                    HttpURLConnection conn;
                    BufferedReader rd;
                    String line;
                    String result = "";
                    url = new URL("https://maps.googleapis.com/maps/api/place/details/json?reference=" + reference
                            + "&sensor=true&key=" + creds.getProperty("placesapikey"));
                    conn = (HttpURLConnection) url.openConnection();
                    conn.setRequestMethod("GET");
                    rd = new BufferedReader(new InputStreamReader(conn.getInputStream()));
                    while ((line = rd.readLine()) != null) {
                        result += line;
                    }
                    rd.close();

                    JSONObject results = new JSONObject(result);
                    String jstatus = results.getString("status");
                    if (jstatus.equals("OK")) {
                        JSONObject resultObj = results.getJSONObject("result");
                        AddShop(resultObj.getString("id"), resultObj.getString("name"), resultObj.getString("url"),
                                resultObj.getString("vicinity"), reference);
                    }
                } catch (MalformedURLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (JSONException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }.start();
    }

    // when a vote is cast, an id and ref code are included. If they don't exist in the
    // shops table, add them, otherwise do nothing.
    private boolean AddShop(String id, String name, String url, String vicinity, String reference) {
        boolean addSuccess = false;

        PreparedStatement insertShopStmt = null;

        String insertUserQuery = qINSERT + qINTO + shopsTable + " (" + shopsIdField + "," + shopsNameField + ","
                + shopsUrlField + "," + shopsVicinityField + "," + shopsReferenceField + ") VALUES(?,?,?,?,?)" + qON
                + qDUPLICATE + qKEY + qUPDATE + shopsNameField + qEQUALS_VALUE + "," + shopsUrlField + qEQUALS_VALUE
                + "," + shopsVicinityField + qEQUALS_VALUE + "," + shopsReferenceField + qEQUALS_VALUE;

        try {
            if (con == null || con.isClosed())
                con = new SQLConnector().getConnection();
            insertShopStmt = con.prepareStatement(insertUserQuery, Statement.RETURN_GENERATED_KEYS);

            insertShopStmt.setString(1, id);
            insertShopStmt.setString(2, name);
            insertShopStmt.setString(3, url);
            insertShopStmt.setString(4, vicinity);
            insertShopStmt.setString(5, reference);
            insertShopStmt.setString(6, name);
            insertShopStmt.setString(7, url);
            insertShopStmt.setString(8, vicinity);
            insertShopStmt.setString(9, reference);

            insertShopStmt.execute();

            addSuccess = true;

        } catch (SQLException sqe) {
            addSuccess = false;
            System.out.println(sqe);
        } finally {
            closeStatement(insertShopStmt);
        }

        return addSuccess;
    }

    // returns today's date with no time data. we'll use this to count votes for the day
    public long GetDate() {
        System.out.println("Returning today's date in GMT-8");
        SimpleDateFormat date_format_gmt = new SimpleDateFormat("yyyy-MM-dd");
        date_format_gmt.setTimeZone(TimeZone.getTimeZone("GMT-8"));
        long returnDate = 0;
        try {
            returnDate = date_format_gmt.parse(date_format_gmt.format(Calendar.getInstance().getTime())).getTime();
        } catch (ParseException pex) {
            pex.printStackTrace();
        }
        return returnDate;
    }

    private void closeStatement(Statement statement) {
        if (statement != null) {
            try {
                statement.close();

            } catch (SQLException sqe) {
                System.out.println("Unable to close connection: " + sqe);
            }
        }
    }

}