Android Open Source - WorkTime db Helper

• Back to App/time  ↑

From Project

Back to project page WorkTime.

License

The source code is released under:

GNU General Public License

Java Source Code

package com.overapps.worktime;
//from  w w  w  .ja  va 2s  . c  om
import android.content.*;
import android.database.*;
import android.database.sqlite.*;
import android.util.*;
import java.util.*;

public class dbHelper extends SQLiteOpenHelper
{

    // Logcat tag
    private static final String LOG = "dbHelper";

    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "WorkTimeTable";

    // Table Names
    private static final String TABLE_TIME = "times";

    // Common column names
    private static final String KEY_ID = "id";
    private static final String KEY_CREATED_AT = "created_at";

    // TIMES Table - column nmaes
    private static final String KEY_TIME = "time";
    private static final String KEY_DAY = "day";
    private static final String KEY_STATUS = "status";

    // Table Create Statements
    // Time table create statement
    private static final String CREATE_TABLE_TIME = "CREATE TABLE "
    + TABLE_TIME + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_TIME
    + " TEXT," + KEY_DAY
    + " TEXT," + KEY_STATUS +  " INTEGER," + KEY_CREATED_AT
    + " DATETIME" + ")";

    public dbHelper(Context context)
    {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db)
    {

        // creating required tables
        db.execSQL(CREATE_TABLE_TIME);
//        db.execSQL(CREATE_TABLE_TAG);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        // on upgrade drop older tables
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_TIME);
//        db.execSQL("DROP TABLE IF EXISTS " + TABLE_TAG);

        // create new tables
        onCreate(db);
    }

    /*
     * Creating a time row
     */
    public long createTimeRow(dbModel Time)
    {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_TIME, Time.getTime());
        values.put(KEY_DAY, Time.getDay());
        values.put(KEY_STATUS, Time.getStatus());

        // insert row
        long row_id = db.insert(TABLE_TIME, null, values);

        return row_id;
    }

    /*
     * get single time row in day
     */
    public dbModel getTimeOnDay(String day)
    {
        SQLiteDatabase db = this.getReadableDatabase();

        String selectQuery = "SELECT  * FROM " + TABLE_TIME + " WHERE "
            + KEY_DAY + " = " + day + " ORDER BY " + KEY_ID + " ASC ";

        Log.e(LOG, selectQuery);

        Cursor c = db.rawQuery(selectQuery, null);

        if (c != null)
            c.moveToFirst();

        dbModel t = new dbModel();
        t.setId(c.getInt(c.getColumnIndex(KEY_ID)));
        t.setTime(c.getString(c.getColumnIndex(KEY_TIME)), c.getString(c.getColumnIndex(KEY_DAY)));
        t.setCreatedAt(c.getString(c.getColumnIndex(KEY_CREATED_AT)));
        t.setStatus(c.getInt(c.getColumnIndex(KEY_STATUS)));
        return t;
    }

    public List<dbModel> getAllRecords()
    {
        List<dbModel> records = new ArrayList<dbModel>();
        String selectQuery = "SELECT * FROM " + TABLE_TIME + "";

        Log.e(LOG, selectQuery);

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (c.moveToFirst())
        {
            do {
                dbModel td = new dbModel();
                td.setId(c.getInt((c.getColumnIndex(KEY_ID))));
                td.setTime(c.getString(c.getColumnIndex(KEY_TIME)), c.getString(c.getColumnIndex(KEY_DAY)));
                td.setStatus(c.getInt(c.getColumnIndex(KEY_STATUS)));
                td.setCreatedAt(c.getString(c.getColumnIndex(KEY_CREATED_AT)));

                // adding to timestamp records list
                records.add(td);
            } while (c.moveToNext());
        }
        return records;
    }
    
    //Get all records in a range of days
    public List<dbModel> getAllRecords(String begin, String end)
    {
        List<dbModel> records = new ArrayList<dbModel>();
        String selectQuery = "SELECT * FROM " + TABLE_TIME + 
                    " WHERE " + KEY_DAY + " >= " + begin +
                    " AND " + KEY_DAY + " <= " + end + 
                    " ORDER BY " + KEY_ID + " DESC ";

        Log.e(LOG, selectQuery);

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (c.moveToFirst())
        {
            do {
                dbModel td = new dbModel();
                td.setId(c.getInt((c.getColumnIndex(KEY_ID))));
                td.setTime(c.getString(c.getColumnIndex(KEY_TIME)), c.getString(c.getColumnIndex(KEY_DAY)));
                td.setStatus(c.getInt(c.getColumnIndex(KEY_STATUS)));
                td.setCreatedAt(c.getString(c.getColumnIndex(KEY_CREATED_AT)));

                // adding to timestamp records list
                records.add(td);
            } while (c.moveToNext());
        }
        return records;
    }
}

Java Source Code List

• Back to App/time  ↑