Last active
November 24, 2024 23:53
-
-
Save JamesHovious/7231185 to your computer and use it in GitHub Desktop.
A simple example of creating a SQLIte DB in Android.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
package com.e3h.usmcknowledge.database; | |
import android.content.ContentValues; | |
import android.content.Context; | |
import android.database.Cursor; | |
import android.database.sqlite.SQLiteDatabase; | |
import android.database.sqlite.SQLiteOpenHelper; | |
public class DatabaseHelper { | |
// list of constants for referencing the db's internal values | |
public static final int DATABASE_VERSION = 1; | |
public static final String DATABASE_NAME = "corps_knowledge.db"; | |
public static final String TABLE_NAME = "pft_test_records"; | |
public static final String TIMETRACKER_COLUMN_ID = "_id"; | |
public static final String TIMETRACKER_COLUMN_RUNTIME = "runTime"; | |
public static final String TIMETRACKER_COLUMN_SITUPS = "situpCount"; | |
public static final String TIMETRACKER_COLUMN_PULLUPS = "pullupCount"; | |
public static final String TIMETRACKER_COLUMN_USERNAME = "userName"; | |
public static final String TIMETRACKER_COLUMN_SCORE = "testScore"; | |
public static final String TIMETRACKER_COLUMN_CLASS = "userClass"; | |
public static final String TIMETRACKER_COLUMN_TESTDATE = "testDate"; | |
public static final String USER_TABLE_NAME = "user_profile"; | |
public static final String PROFILE_COLUMN_ID = "_id"; | |
public static final String PROFILE_COLUMN_USERNAME = "userName"; | |
public static final String PROFILE_COLUMN_GENDER = "gender"; | |
public static final String PROFILE_COLUMN_AGE = "age"; | |
// stored local variables for the OpenHelper and the database it opens | |
public DatabaseOpenHelper openHelper; | |
public SQLiteDatabase database; | |
public DatabaseHelper(Context context) { | |
openHelper = new DatabaseOpenHelper(context); | |
database = openHelper.getWritableDatabase(); | |
} | |
// this is what actually declares the database so that other activites can | |
// read and write to it | |
private class DatabaseOpenHelper extends SQLiteOpenHelper { | |
DatabaseOpenHelper(Context context) { | |
// pass name (pft_test_records.db) and version number (1) as super | |
super(context, DATABASE_NAME, null, DATABASE_VERSION); | |
} | |
// when the db is created. this method is called by onUpgrade when the | |
// version number is changed | |
public void onCreate(SQLiteDatabase database) { | |
// execSQL actually creates the schema of the db we defined in the | |
// method above | |
database.execSQL("CREATE TABLE " + TABLE_NAME + "( " | |
+ TIMETRACKER_COLUMN_ID + " INTEGER PRIMARY KEY, " | |
+ TIMETRACKER_COLUMN_RUNTIME + " TEXT, " | |
+ TIMETRACKER_COLUMN_SITUPS + " INTEGER, " | |
+ TIMETRACKER_COLUMN_PULLUPS + " INTEGER, " | |
+ TIMETRACKER_COLUMN_USERNAME + " TEXT, " | |
+ TIMETRACKER_COLUMN_SCORE + " INTEGER, " | |
+ TIMETRACKER_COLUMN_CLASS + " TEXT, " | |
+ TIMETRACKER_COLUMN_TESTDATE + " TEXT )"); | |
database.execSQL("CREATE TABLE " + USER_TABLE_NAME + "( " | |
+ PROFILE_COLUMN_ID + " INTEGER PRIMARY KEY, " | |
+ PROFILE_COLUMN_USERNAME + " TEXT, " | |
+ PROFILE_COLUMN_GENDER + " TEXT, " + PROFILE_COLUMN_AGE | |
+ " TEXT )"); | |
} | |
// when the version number is changed, this method is called | |
public void onUpgrade(SQLiteDatabase database, int oldVersion, | |
int newVersion) { | |
// drops table if exists, and then calls onCreate which implements | |
// our new schema | |
database.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME); | |
database.execSQL("DROP TABLE IF EXISTS " + USER_TABLE_NAME); | |
onCreate(database); | |
} | |
} | |
// method called by onActivityResult which actually saves user entered data | |
// into the db | |
public void saveTimeRecord(String runTime, Integer sitUps, Integer pullUps, | |
String userName, Integer score, String userClass, String testDate) { | |
ContentValues contentValues = new ContentValues(); | |
contentValues.put(TIMETRACKER_COLUMN_RUNTIME, runTime); | |
contentValues.put(TIMETRACKER_COLUMN_SITUPS, sitUps); | |
contentValues.put(TIMETRACKER_COLUMN_PULLUPS, pullUps); | |
contentValues.put(TIMETRACKER_COLUMN_USERNAME, userName); | |
contentValues.put(TIMETRACKER_COLUMN_SCORE, score); | |
contentValues.put(TIMETRACKER_COLUMN_CLASS, userClass); | |
contentValues.put(TIMETRACKER_COLUMN_TESTDATE, testDate); | |
database.insert(TABLE_NAME, null, contentValues); | |
} | |
// method that saves the users profile information | |
public void saveUserProfile(String userName, String userGender, | |
String userAge) { | |
// Clears the legacy profile information first | |
database.execSQL("DELETE FROM user_profile;"); | |
// Writes the new profile information | |
ContentValues contentValues = new ContentValues(); | |
contentValues.put(PROFILE_COLUMN_USERNAME, userName); | |
contentValues.put(PROFILE_COLUMN_GENDER, userGender); | |
contentValues.put(PROFILE_COLUMN_AGE, userAge); | |
database.insert(USER_TABLE_NAME, null, contentValues); | |
} | |
// null because there are no selection args since we are just selecting | |
// everything | |
public Cursor getUserProfileInfo() { | |
return database.rawQuery("SELECT * FROM " + USER_TABLE_NAME, null); | |
} | |
// null because there are no selection args since we are just selecting | |
// everything | |
public Cursor getAllTimeRecords() { | |
return database.rawQuery("SELECT * FROM " + TABLE_NAME + " ORDER BY " | |
+ TIMETRACKER_COLUMN_ID + " DESC", null); | |
} | |
// null because there are no selection args since we are just selecting | |
// everything | |
public Cursor getSingleTimeRecord(String list_view_row_id) { | |
return database.rawQuery("SELECT * FROM pft_test_records WHERE _id = " | |
+ list_view_row_id + ";", null); | |
} | |
public void deleteSingleRecord(String list_view_row_id) { | |
database.execSQL("DELETE FROM pft_test_records WHERE _id = " | |
+ list_view_row_id + ";"); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment