I'm having some difficulty with using an SQLite database. I can't work out whats going wrong when I try to run any methods that interact with my database and cause it to crash the program. Any help would be appreciated.
Here is my SQLiteHelper class:
public class DatabaseHelper extends SQLiteOpenHelper{
//Create the database
public static final String dbName = "DiabetesResults";
public static final int dbVersion = 1;
//Table to store test results
public static final String resultTable = "Results";
public static final String colID = "1";
public final String colValue = "150";
public final String colDate = "Date";
public final String colNotes = "Notes";
//Table to store current and previous safe limits
public final String limitTable = "Limits";
public final String colLimitID = "LimitID";
public final String colLimitUpper = "350";
public final String colLimitLower = "200";
public final String colLimitDate = "LimitDate";
public final static String viewResults="viewResults";
public DatabaseHelper(Context context){
super(context, dbName, null, dbVersion);
}
public void onCreate(SQLiteDatabase db) {
//SQL to create the result table
db.execSQL("CREATE TABLE " + resultTable +
"(" + colID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
colValue + " Integer, " + colDate + " Date, " + colNotes + "TEXT, ");
//SQL to create the limit table
db.execSQL("CREATE TABLE " + limitTable +
"(" + colLimitID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
colLimitUpper + " Integer, " + colLimitLower + " Integer, " + colLimitDate + " Date, ");
//Create view containing all results
db.execSQL("CREATE VIEW " + viewResults +
" AS SELECT " + resultTable + "." + colID + " AS _id," +
" " + resultTable + "." + colValue + "," +
" " + resultTable + "." + colDate + "," +
" " + resultTable + "." + colNotes + "," +
" FROM " + resultTable
);
}
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//Used to upgrade the database if the schema needs to be changed
db.execSQL("DROP TABLE IF EXISTS " + resultTable);
db.execSQL("DROP TABLE IF EXISTS " + limitTable);
db.execSQL("DROP VIEW IF EXISTS " + viewResults);
onCreate(db);
}
//Method to update results table
public int UpdateResults(Result result)
{
SQLiteDatabase db=this.getWritableDatabase();
ContentValues cv=new ContentValues();
cv.put(colValue, result.getValue());
cv.put(colNotes, result.getNotes());
return db.update(resultTable, cv, colID+"=?",
new String []{String.valueOf(result.get_id())});
}
//Method to delete a result with given id
public void Delete(Integer id)
{
SQLiteDatabase db=this.getWritableDatabase();
db.delete(resultTable,colID+"=?", new String [] {String.valueOf(id)});
db.close();
}
public void insertResults(Result result){
SQLiteDatabase db=this.getWritableDatabase();
ContentValues cv=new ContentValues();
//Get current date for entered result
Calendar c = Calendar.getInstance();
SimpleDateFormat df = new SimpleDateFormat("dd-mm-yyyy");
String formattedDate = df.format(c.getTime());
//Put values into content values
cv.put(colID, 1);
cv.put(colValue, result.getValue());
cv.put(colNotes, result.getNotes());
cv.put(colDate, formattedDate);
//Insert values into database
db.insert(resultTable, colID, cv);
}
//Method to return all results in a cursor
public List<Result> getAllResults()
{
SQLiteDatabase db=this.getReadableDatabase();
//Create list to store results
List<Result> resultList = new ArrayList<>();
//query to select all rows in resultTable
String query = "SELECT * from " +resultTable;
//Cursor points to location in results
Cursor c = db.rawQuery(query, null);
//Move to first row
if(c!=null){
c.moveToFirst();
//If cursor is after last row, there are no more rows
while(c.isAfterLast()){
//Create result
Result res = new Result("1", "100", "", "");
//Set result values equal to that of result in this row
res.set_id(c.getString(c.getColumnIndex(colID)));
res.setDate(c.getString(c.getColumnIndex(colDate)));
res.setValue(c.getString(c.getColumnIndex(colValue)));
res.setNotes(c.getString(c.getColumnIndex(colNotes)));
//Add person to list
resultList.add(res);
//Move cursor to next row
c.moveToNext();
}
//Close cursor
c.close();
}
//Return list of results
return resultList;
}
//Method to return all results from a given date
public Cursor getResultsByDate(String date){
SQLiteDatabase db=this.getReadableDatabase();
String [] columns=new String[]{"_id",colDate,colValue,colNotes};
Cursor c=db.query(viewResults, columns, colDate+"=?",
new String[]{date}, null, null, null);
return c;
}
//Method to empty the database
public void emptyDatabase(){
SQLiteDatabase db=this.getWritableDatabase();
db.execSQL("delete * from "+ resultTable);
db.close();
}
public boolean checkIfLimitExists(){
SQLiteDatabase db=this.getReadableDatabase();
//Query to check if limit is in table already
String query = "SELECT * from " +limitTable;
//Create cursor
Cursor c = db.rawQuery(query, null);
if(c.getCount() == 0){
c.close();
return false;
}
c.close();
return true;
}
public void updateLimit(Limit limit){
SQLiteDatabase db=this.getWritableDatabase();
ContentValues cv=new ContentValues();
//Put the values into content values
cv.put(colLimitID, 1);
cv.put(colLimitUpper, limit.getUpper());
cv.put(colLimitLower, limit.getLower());
//If limit does not already exist, insert limit
if (checkIfLimitExists() == false){
db.insert(limitTable, colLimitID, cv);
}
//Update the limit with ID 1 in the database
//As there is only one row, it will have id 1
db.update(limitTable, cv, colLimitID+"as _id "+"="+1, null);
}
}
And here is for example my enterResults class, which contains my createResult() method which is not working:
public class enterResults extends ActionBarActivity {
DatabaseHelper db = new DatabaseHelper(this);
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_enter_results);
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.menu_enter_results, menu);
return true;
}
@Override
public boolean onOptionsItemSelected(MenuItem item) {
// Handle action bar item clicks here. The action bar will
// automatically handle clicks on the Home/Up button, so long
// as you specify a parent activity in AndroidManifest.xml.
int id = item.getItemId();
//noinspection SimplifiableIfStatement
if (id == R.id.action_settings) {
return true;
}
return super.onOptionsItemSelected(item);
}
//Method to return to the main activity
public void back(View view) {
final Intent intent = new Intent(this, MainActivity.class);
startActivity(intent);
}
//Called when confirm button is clicked
//Create result using
public void createResult(View view) {
//Variable to hold the value
EditText mEditValue;
//Get the value from the edittext
mEditValue = (EditText) findViewById(R.id.editValue);
//Store the value as a String
String value = mEditValue.getText().toString();
//Variable to hold the notes
EditText mEditNotes;
//Get the date from the edittext
mEditNotes = (EditText) findViewById(R.id.editNotes);
//Store the date as a String
String notes = mEditNotes.getText().toString();
//Create result with entered values
final Result res = new Result("1", value, "dd-mm-yyyy", notes);
//Call insert method, passing result as parameter
//Insert method will get the current date
db.insertResults(res);
}
}
Aucun commentaire:
Enregistrer un commentaire