itachi1706/SingBuses

View on GitHub
app/src/main/java/com/itachi1706/busarrivalsg/Database/BusStopsDB.java

Summary

Maintainability
A
1 hr
Test Coverage
package com.itachi1706.busarrivalsg.Database;

import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;

import com.itachi1706.busarrivalsg.gsonObjects.sgLTA.BusStopJSON;
import com.itachi1706.helperlib.helpers.LogHelper;

import java.io.File;
import java.util.ArrayList;

/**
 * Created by Kenneth on 20/6/2015
 * for SingBuses in package com.itachi1706.busarrivalsg.Database
 */
public class BusStopsDB extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 3;

    //DB Name
    private static final String DATABASE_NAME = "appdb.db";

    //DB table Name
    private static final String TABLE_ITEMS = "BusStops";

    //Bus Stops Table Column Names
    private static final String CODE_ID = "id";
    private static final String BUS_STOP_CODE = "busStopCode";
    private static final String BUS_STOP_ROAD = "roadName";
    private static final String BUS_STOP_DESC = "description";
    private static final String BUS_STOP_LATITUDE = "latitude";
    private static final String BUS_STOP_LONGITUDE = "longitude";
    private static final String BUS_STOP_SERVICES = "services";
    private static final String BUS_STOP_TIMESTAMP = "timestamp";

    public BusStopsDB(Context context){
        super(context, context.getExternalFilesDir(null) + File.separator + DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_BUS_TABLE = "CREATE TABLE " + TABLE_ITEMS + "(" + CODE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + BUS_STOP_CODE + " TEXT," + BUS_STOP_ROAD + " TEXT," + BUS_STOP_DESC + " TEXT," + BUS_STOP_LATITUDE + " DOUBLE,"
                + BUS_STOP_LONGITUDE + " DOUBLE," + BUS_STOP_SERVICES + " TEXT," + BUS_STOP_TIMESTAMP + " INTEGER);";
        db.execSQL(CREATE_BUS_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_ITEMS);
        onCreate(db);
    }

    public void dropAndRebuildDB(){
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_ITEMS);
        onCreate(db);
    }

    private void bulkAddFromJSON(BusStopJSON[] busStops) {
        SQLiteDatabase db = this.getWritableDatabase();
        String sql = "INSERT INTO " + TABLE_ITEMS + " (" + BUS_STOP_CODE + ", " + BUS_STOP_ROAD + ", "
                + BUS_STOP_DESC + ", " + BUS_STOP_LATITUDE + ", " + BUS_STOP_LONGITUDE + ", " + BUS_STOP_SERVICES + ", "
                + BUS_STOP_TIMESTAMP + ") VALUES (?,?,?,?,?,?,?);";
        db.beginTransaction();
        SQLiteStatement stmt = db.compileStatement(sql);
        for (BusStopJSON busStop : busStops) {
            LogHelper.d("DEBUG-BusStops", "Processing Bus Stop: " + busStop.getBusStopCode());
            stmt.bindString(1, busStop.getBusStopCode());
            stmt.bindString(2, busStop.getRoadName());
            stmt.bindString(3, busStop.getDescription());
            stmt.bindDouble(4, busStop.getLatitude());
            stmt.bindDouble(5, busStop.getLongitude());
            stmt.bindString(6, busStop.getServices());
            stmt.bindLong(7, busStop.getTimestamp());
            stmt.executeInsert();
            stmt.clearBindings();
        }
        db.setTransactionSuccessful();
        db.endTransaction();
        db.close();
    }

    /**
     * Add an array of records to the database
     * @param busStops The array of objects to add
     */
    public void addMultipleToDB(BusStopJSON[] busStops) {
        bulkAddFromJSON(busStops);
    }

    private BusStopJSON getBusStopJsonObject(Cursor cursor) {
        BusStopJSON bs = new BusStopJSON();
        bs.setBusStopCode(cursor.getString(1));
        bs.setRoadName(cursor.getString(2));
        bs.setDescription(cursor.getString(3));
        bs.setLatitude(cursor.getDouble(4));
        bs.setLongitude(cursor.getDouble(5));
        bs.setServices(cursor.getString(6));
        bs.setTimestamp(cursor.getInt(7));
        return bs;
    }

    /**
     * Gets all Bus Stops in the database
     * @return An ArrayList of all bus stops in the DB
     */
    public ArrayList<BusStopJSON> getAllBusStops(){
        String query = "SELECT * FROM " + TABLE_ITEMS;
        SQLiteDatabase db = this.getReadableDatabase();
        ArrayList<BusStopJSON> results = new ArrayList<>();
        Cursor cursor = db.rawQuery(query, null);

        if (cursor.moveToFirst()){
            do {
                results.add(getBusStopJsonObject(cursor));
            } while (cursor.moveToNext());
        }
        cursor.close();

        return results;
    }

    /**
     * Returns a single Bus Stop Object based on Unique Bus Stop Code
     * @param busStopCode Bus Stop Code
     * @return Bus Stop Object
     */
    public BusStopJSON getBusStopByBusStopCode(String busStopCode){
        busStopCode = DatabaseUtils.sqlEscapeString(busStopCode);
        String query = "SELECT * FROM " + TABLE_ITEMS + " WHERE " + BUS_STOP_CODE + "=" + busStopCode + ";";
        SQLiteDatabase db = this.getReadableDatabase();
        BusStopJSON bs = new BusStopJSON();

        Cursor cursor = db.rawQuery(query, null);
        if (cursor.moveToFirst()){
            do {
                bs = getBusStopJsonObject(cursor);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return bs;
    }

    /**
     * Returns a list of Bus Stop Object based on Bus Stop Name
     * @param stopName Bus Stop Name
     * @return Bus Stop Object ArrayList
     */
    public ArrayList<BusStopJSON> getBusStopsByStopName(String stopName){
        stopName = DatabaseUtils.sqlEscapeString(stopName);
        String query = "SELECT * FROM " + TABLE_ITEMS + " WHERE " + BUS_STOP_DESC + " LIKE " + stopName + ";";
        SQLiteDatabase db = this.getReadableDatabase();
        ArrayList<BusStopJSON> result = new ArrayList<>();

        Cursor cursor = db.rawQuery(query, null);
        if (cursor.getCount() == 0)
            return null;
        if (cursor.moveToFirst()){
            do {
                result.add(getBusStopJsonObject(cursor));
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return result;
    }

    /**
     * Returns a list of Bus Stop Object based on Service No
     * @param svcNo Service No
     * @param company Bus Service Company
     * @return Bus Stop Object ArrayList
     */
    public ArrayList<BusStopJSON> getBusStopsBySvcNo(String svcNo, String company){
        String concat = "%" + svcNo + ":" + company + "%";
        concat = DatabaseUtils.sqlEscapeString(concat);
        String query = "SELECT * FROM " + TABLE_ITEMS + " WHERE " + BUS_STOP_SERVICES + " LIKE " + concat + ";";
        SQLiteDatabase db = this.getReadableDatabase();
        ArrayList<BusStopJSON> result = new ArrayList<>();

        Cursor cursor = db.rawQuery(query, null);
        if (cursor.getCount() == 0)
            return null;
        if (cursor.moveToFirst()){
            do {
                result.add(getBusStopJsonObject(cursor));
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return result;
    }

    /**
     * Returns a bus stop object that is found
     * @param lng Longitude
     * @param lat Latitude
     * @return Bus Stop Object if found, null otherwise
     */
    public BusStopJSON getBusStopByLocation(double lng, double lat) {
        String longitude = lng + "";
        String latitude = lat + "";
        longitude = longitude.substring(0, longitude.length() - 2);
        latitude = latitude.substring(0, latitude.length() - 2);
        String query = "SELECT * FROM " + TABLE_ITEMS + " WHERE " + BUS_STOP_LONGITUDE + " LIKE '" + longitude + "%' AND " + BUS_STOP_LATITUDE + " LIKE '" + latitude + "%';";
        SQLiteDatabase db = this.getReadableDatabase();
        BusStopJSON result = new BusStopJSON();

        Cursor cursor = db.rawQuery(query, null);
        if (cursor.getCount() == 0) return null;
        if (cursor.moveToFirst()) {
            do {
                result = getBusStopJsonObject(cursor);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return result;
    }

    /**
     * Returns a list of Bus Stops based on query string
     * @param query Query String
     * @return Bus Stop Object ArrayList
     */
    public ArrayList<BusStopJSON> getBusStopsByQuery(String query){
        query = DatabaseUtils.sqlEscapeString("%" + query + "%");
        String queryString = "SELECT * FROM " + TABLE_ITEMS + " WHERE " + BUS_STOP_CODE + " LIKE " + query + " COLLATE NOCASE OR "
                + BUS_STOP_ROAD + " LIKE " + query + " COLLATE NOCASE OR " + BUS_STOP_DESC + " LIKE " + query + " COLLATE NOCASE;";
        System.out.println("DB QUERY-STRING: "+ queryString);
        SQLiteDatabase db = this.getReadableDatabase();
        ArrayList<BusStopJSON> result = new ArrayList<>();

        Cursor cursor = db.rawQuery(queryString, null);
        if (cursor.getCount() == 0)
            return null;
        if (cursor.moveToFirst()){
            do {
                result.add(getBusStopJsonObject(cursor));
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return result;
    }

    public int getSize(){
        String query = "SELECT * FROM " + TABLE_ITEMS + ";";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(query, null);
        int count = cursor.getCount();
        cursor.close();
        return count;
    }
}