I'm having troubles reading from a db for my app, I'm fairly new to SQLite.
So I have a listview which is supposed to show a list of "expenses" coming from the database, When I do it with "desc" only it works just fine, but I tried adding code for a amount column and it wouldn't work.
I think the error is coming from the ExpenseDataSource class but I'll summarise the Main activity as well. It contains only the listview and you can add new and edit/delete items when you click them, if you leave a textfield blank the Edit becomes invalid. Every kind of help would be much appreciated. Thanks in advance
**Logs: **
04-27 11:55:33.877 1264-1264/cm3019.coursework1209323 D/﹕ HostConnection::get() New Host Connection established 0xb75deb70, tid 1264
04-27 11:55:33.927 1264-1264/cm3019.coursework1209323 W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-27 11:55:33.937 1264-1264/cm3019.coursework1209323 D/OpenGLRenderer﹕ Enabling debug mode 0
04-27 11:55:33.947 1264-1277/cm3019.coursework1209323 E/AndroidRuntime﹕ FATAL EXCEPTION: AsyncTask #1
Process: cm3019.coursework1209323, PID: 1264
java.lang.RuntimeException: An error occured while executing doInBackground()
at android.os.AsyncTask$3.done(AsyncTask.java:300)
at java.util.concurrent.FutureTask.finishCompletion(FutureTask.java:355)
at java.util.concurrent.FutureTask.setException(FutureTask.java:222)
at java.util.concurrent.FutureTask.run(FutureTask.java:242)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1112)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:587)
at java.lang.Thread.run(Thread.java:841)
Caused by: android.database.sqlite.SQLiteException: no such column: amount (code 1): , while compiling: SELECT _id, desc, amount FROM expenses
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1161)
at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1032)
at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1200)
at cm3019.coursework1209323.ExpenseDataSource.read(ExpenseDataSource.java:84)
at cm3019.coursework1209323.SQLiteExpenseDataLoader.buildList(SQLiteExpenseDataLoader.java:32)
at cm3019.coursework1209323.SQLiteExpenseDataLoader.buildList(SQLiteExpenseDataLoader.java:10)
at cm3019.coursework1209323.AbstractDataLoader.loadInBackground(AbstractDataLoader.java:26)
at cm3019.coursework1209323.AbstractDataLoader.loadInBackground(AbstractDataLoader.java:11)
at android.content.AsyncTaskLoader.onLoadInBackground(AsyncTaskLoader.java:312)
at android.content.AsyncTaskLoader$LoadTask.doInBackground(AsyncTaskLoader.java:69)
at android.content.AsyncTaskLoader$LoadTask.doInBackground(AsyncTaskLoader.java:57)
at android.os.AsyncTask$2.call(AsyncTask.java:288)
at java.util.concurrent.FutureTask.run(FutureTask.java:237)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1112)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:587)
at java.lang.Thread.run(Thread.java:841)
04-27 11:55:34.067 389-499/system_process W/ActivityManager﹕ Force finishing activity cm3019.coursework1209323/.MainActivity
04-27 11:55:34.397 389-499/system_process I/WindowManager﹕ Screenshot max retries 4 of Token{b205d250 ActivityRecord{b205d0f0 u0 cm3019.coursework1209323/.MainActivity t5 f}} appWin=Window{b2007f18 u0 Starting cm3019.coursework1209323} drawState=3
04-27 11:55:34.397 389-499/system_process W/WindowManager﹕ Screenshot failure taking screenshot for (768x1280) to layer 21010
04-27 11:55:34.487 389-562/system_process I/ActivityManager﹕ Killing 983:com.android.mms/u0a9 (adj 15): empty for 2037s
04-27 11:55:34.527 389-400/system_process D/CountryDetector﹕ No listener is left
04-27 11:55:34.757 554-554/com.android.launcher W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-27 11:55:35.077 1264-1264/cm3019.coursework1209323 I/Choreographer﹕ Skipped 31 frames! The application may be doing too much work on its main thread.
04-27 12:00:34.537 1264-1277/cm3019.coursework1209323 I/Process﹕ Sending signal. PID: 1264 SIG: 9
04-27 12:00:34.627 389-400/system_process I/ActivityManager﹕ Process cm3019.coursework1209323 (pid 1264) has died.
04-27 12:02:01.707 389-428/system_process D/ConnectivityService﹕ Sampling interval elapsed, updating statistics ..
04-27 12:02:01.837 389-428/system_process D/dalvikvm﹕ GC_FOR_ALLOC freed 1015K, 22% free 7327K/9304K, paused 87ms, total 94ms
04-27 12:02:01.877 389-428/system_process D/ConnectivityService﹕ Done.
04-27 12:02:01.877 389-428/system_process D/ConnectivityService﹕ Setting timer for 720seconds
04-27 12:02:52.597 540-540/com.android.phone D/dalvikvm﹕ GC_FOR_ALLOC freed 593K, 17% free 3430K/4088K, paused 50ms, total 51ms
Here's the code for the data table:
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import java.util.ArrayList;
/**
* Created by 1209323 on 14/04/2015.
*/
public class ExpenseDataSource extends DataSource<Expense> {
public static final String TABLE_NAME = "expenses";
public static final String COLUMN_ID = "_id";
public static final String COLUMN_DESC = "desc";
public static final String COLUMN_AMOUNT = "amount";
public static final String CREATE_COMMAND = "create table " + TABLE_NAME + "("
+ COLUMN_ID + " integer primary key autoincrement, "
+ COLUMN_DESC + " text not null, "
+ COLUMN_AMOUNT + "real not null"
+");";
public ExpenseDataSource(SQLiteDatabase database) {
super(database);
}
@Override
public boolean insert(Expense entity) {
if (entity == null) {
return false;
}
long result = mDatabase.insert(TABLE_NAME, null,
generateContentValuesFromObject(entity));
return result != -1;
}
@Override
public boolean delete(Expense entity) {
if (entity == null) {
return false;
}
int result = mDatabase.delete(TABLE_NAME,
COLUMN_ID + " = " + entity.getId(), null);
return result != 0;
}
@Override
public boolean update(Expense entity) {
if (entity == null) {
return false;
}
int result = mDatabase.update(TABLE_NAME,
generateContentValuesFromObject(entity), COLUMN_ID + " = "
+ entity.getId(), null);
return result != 0;
}
@Override
public ArrayList<Expense> read() {
Cursor cursor = mDatabase.query(TABLE_NAME, getAllColumns(), null,
null, null, null, null);
ArrayList expenses = new ArrayList();
if (cursor != null && cursor.moveToFirst()) {
while (!cursor.isAfterLast()) {
expenses.add(generateObjectFromCursor(cursor));
cursor.moveToNext();
}
cursor.close();
}
return expenses;
}
public String[] getAllColumns() {
return new String[] { COLUMN_ID, COLUMN_DESC, COLUMN_AMOUNT};
}
@Override
public ArrayList<Expense> read(String selection, String[] selectionArgs,
String groupBy, String having, String orderBy) {
Cursor cursor = mDatabase.query(TABLE_NAME, getAllColumns(), selection,
selectionArgs, groupBy, having, orderBy);
ArrayList tasks = new ArrayList();
if (cursor != null && cursor.moveToFirst()) {
while (!cursor.isAfterLast()) {
tasks.add(generateObjectFromCursor(cursor));
cursor.moveToNext();
}
cursor.close();
}
return tasks;
}
public Expense generateObjectFromCursor(Cursor cursor) {
if (cursor == null) {
return null;
}
Expense expense = new Expense();
expense.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_ID)));
expense.setDesc(cursor.getString(cursor.getColumnIndex(COLUMN_DESC)));
expense.setAmount(cursor.getDouble(cursor.getColumnIndex(COLUMN_AMOUNT)));
return expense;
}
public ContentValues generateContentValuesFromObject(Expense entity) {
if (entity == null) {
return null;
}
ContentValues values = new ContentValues();
values.put(COLUMN_DESC, entity.getDesc());
values.put(COLUMN_AMOUNT, entity.getAmount());
return values;
}
DBHelper :
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
/**
* Created by 1209323 on 14/04/2015.
*/
public class DatabaseHelper extends SQLiteOpenHelper{
private static final String DATABASE_NAME = "expenses.db";
private static final int DATABASE_VERSION = 1;
public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase database) {
database.execSQL(ExpenseDataSource.CREATE_COMMAND);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + ExpenseDataSource.TABLE_NAME);
onCreate(db);
}
}
Main Activity where DB is used
import android.app.DialogFragment;
import android.app.LoaderManager;
import android.database.sqlite.SQLiteDatabase;
import android.content.Loader;
import android.support.v7.app.ActionBarActivity;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.RadioButton;
import android.widget.TextView;
import android.widget.Toast;
import java.util.ArrayList;
public class MainActivity extends ActionBarActivity implements
LoaderManager.LoaderCallbacks<ArrayList<Expense>>
{
private ArrayAdapter<Expense> mAdapter;
private SQLiteDatabase mDatabase;
private ExpenseDataSource mDataSource;
private DatabaseHelper mDbHelper;
private static final int LOADER_ID = 1;
// The callbacks through which we will interact with the LoaderManager.
private LoaderManager.LoaderCallbacks<ArrayList<Expense>> mCallbacks;
private LoaderManager mLoaderManager;
private ListView mListView ; // the listview embedded in the main activity's screen layout
// used to save the position in the list view of any task selected by the user
private int mCurrentExpense;
private DialogFragment mNewFragment, mDeleteEditFragment;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// TODO Auto-generated method stub
//setHasOptionsMenu(true);
mDbHelper = new DatabaseHelper(this);
mDatabase = mDbHelper.getWritableDatabase();
mDataSource = new ExpenseDataSource(mDatabase);
mAdapter = new ArrayAdapter(this,
android.R.layout.simple_list_item_1);
mListView = (ListView)findViewById(R.id.listView1);
mListView.setAdapter(mAdapter);
TextView emptyText = (TextView)findViewById(android.R.id.empty);
mListView.setEmptyView(emptyText);
mListView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
public void onItemClick(AdapterView<?> adapter, View view, int position,
long id) {
mCurrentExpense = position;
Expense expense = mAdapter.getItem(mCurrentExpense);
showDeleteEditDialog(expense);
}
});
// The Activity (which implements the LoaderCallbacks<Cursor>
// interface) is the callbacks object through which we will interact
// with the LoaderManager. The LoaderManager uses this object to
// instantiate the Loader and to notify the client when data is made
// available/unavailable.
mCallbacks = this;
// Initialize the Loader with id '1' and callbacks 'mCallbacks'.
// If the loader doesn't already exist, one is created. Otherwise,
// the already created Loader is reused. In either case, the
// LoaderManager will manage the Loader across the Activity/Fragment
// lifecycle, will receive any new loads once they have completed,
// and will report this new data back to the 'mCallbacks' object.
mLoaderManager = getLoaderManager();
mLoaderManager.initLoader(LOADER_ID, null, mCallbacks);
}
// Will be called via the onClick attribute of the buttons in main.xml
public void onClick(View view) {
switch (view.getId()) {
case R.id.add:
showNewExpenseDialog();
break;
}
}
void showDeleteEditDialog(Expense expense) {
mDeleteEditFragment = DeleteEditExpenseDialogFragment.newInstance(
R.string.delete_edit_dialog_title, expense);
mDeleteEditFragment.show(getFragmentManager(), "dialog");
}
public void doDeleteEditExpensePositiveClick() {
@SuppressWarnings("unchecked")
//ArrayAdapter<Task> adapter = (ArrayAdapter<Task>) mListView.getAdapter();
Expense expense = mAdapter.getItem(mCurrentExpense);
Loader loader = mLoaderManager.getLoader(LOADER_ID);
RadioButton deleteEditRadioButton = (RadioButton) mDeleteEditFragment.getDialog().findViewById(R.id.delete);
if (deleteEditRadioButton.isChecked()) {
((SQLiteExpenseDataLoader)loader).delete(expense);
Toast.makeText(getApplicationContext(), "Delete confirmed",
Toast.LENGTH_LONG).show();
}
else {
EditText editDesc = (EditText) mDeleteEditFragment.getDialog().findViewById(R.id.editDesc);
EditText editMaj = (EditText) mDeleteEditFragment.getDialog().findViewById(R.id.editMajVal);
EditText editMin = (EditText) mDeleteEditFragment.getDialog().findViewById(R.id.editMinVal);
String desc = editDesc.getText().toString();
String majValString = editMaj.getText().toString();
String minValString = editMin.getText().toString();
if (!desc.equals("")
&&!minValString.equals("")
&&!majValString.equals("")
){
Integer majVal = new Integer(majValString);
Integer minVal = new Integer(minValString);
expense.setDesc(desc);
if (majVal<=999 && minVal<=99){
System.out.println("TEST MAJ VAL " + majVal);
System.out.println("TEST MIN VAL " + minVal);
System.out.println("TEST amount " + (majVal + minVal/100));
expense.setAmount(majVal, minVal);
System.out.println("TEST amount2 " + expense.getAmount());
((SQLiteExpenseDataLoader)loader).update(expense);
Toast.makeText(getApplicationContext(), "Edit confirmed",
Toast.LENGTH_LONG).show();
System.out.println("TEST TEST TEST " + expense.toString());
System.out.println("TEST amount3 " + expense.getAmount());
}
}
else
doDeleteEditTaskNegativeClick();
System.out.println(expense.getAmount());
System.out.println(expense.toString());
}
// force loader to refresh the listview
//Loader loader = mLoaderManager.getLoader(LOADER_ID);
//loader.onContentChanged();
}
public void doDeleteEditTaskNegativeClick() {
Toast.makeText(getApplicationContext(), "Cancelled",
Toast.LENGTH_LONG).show();
}
void showNewExpenseDialog() {
mNewFragment = NewExpenseDialogFragment.newInstance(
R.string.new_expense_dialog_title);
mNewFragment.show(getFragmentManager(), "dialog");
}
public void doNewExpensePositiveClick() {
EditText editDesc = (EditText) mNewFragment.getDialog().findViewById(R.id.editDesc);
EditText editMajVal = (EditText) mNewFragment.getDialog().findViewById(R.id.editMajVal);
EditText editMinVal = (EditText) mNewFragment.getDialog().findViewById(R.id.editMinVal);
String desc = editDesc.getText().toString();
String majValString = editMajVal.getText().toString();
String minValString = editMinVal.getText().toString();
Integer majValue, minValue;
if (!desc.equals("")
&&!minValString.equals("")
&&!majValString.equals("")){
majValue = new Integer(majValString);
minValue = new Integer(minValString);
Toast.makeText(getApplicationContext(), "New task confirmed",Toast.LENGTH_LONG).show();
//@SuppressWarnings("unchecked")
//ArrayAdapter<Task> adapter = (ArrayAdapter<Task>) mListView.getAdapter();
Loader loader = mLoaderManager.getLoader(LOADER_ID);
((SQLiteExpenseDataLoader)loader).insert(new Expense(desc, majValue, minValue));
// force loader to refresh the listview
//Loader loader = mLoaderManager.getLoader(LOADER_ID);
//loader.onContentChanged();
}else {
Toast.makeText(getApplicationContext(), "New task cancelled",
Toast.LENGTH_LONG).show();
}
}
public void doNewExpenseNegativeClick() {
Toast.makeText(getApplicationContext(), "New task cancelled",
Toast.LENGTH_LONG).show();
}
@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_main, 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);
}
@Override
public Loader<ArrayList<Expense>> onCreateLoader(int id, Bundle args) {
SQLiteExpenseDataLoader loader = new SQLiteExpenseDataLoader(this, mDataSource, null, null, null, null, null);
return loader;
}
@Override
public void onLoadFinished(Loader<ArrayList<Expense>> loader, ArrayList<Expense> data) {
mAdapter.clear();
for (Expense expense : data) {
mAdapter.add(expense);
}
}
@Override
public void onLoaderReset(Loader<ArrayList<Expense>> loader) {
mAdapter.clear();
}
@Override
public void onDestroy() {
super.onDestroy();
mDbHelper.close();
mDatabase.close();
mDataSource = null;
mDbHelper = null;
mDatabase = null;
}
}
And the loader
import android.content.Context;
import java.util.ArrayList;
/**
* Created by 1209323 on 14/04/2015.
*/
public class SQLiteExpenseDataLoader extends AbstractDataLoader<ArrayList<Expense>>{
private DataSource<Expense> mDataSource;
private String mSelection;
private String[] mSelectionArgs;
private String mGroupBy;
private String mHaving;
private String mOrderBy;
public SQLiteExpenseDataLoader(Context context, DataSource dataSource, String selection, String[] selectionArgs,
String groupBy, String having, String orderBy) {
super(context);
mDataSource = dataSource;
mSelection = selection;
mSelectionArgs = selectionArgs;
mGroupBy = groupBy;
mHaving = having;
mOrderBy = orderBy;
}
@Override
protected ArrayList<Expense> buildList() {
ArrayList<Expense> expenseList = mDataSource.read(mSelection, mSelectionArgs, mGroupBy, mHaving,
mOrderBy);
return expenseList;
}
public void insert(Expense entity) {
new InsertExpense(this).execute(entity);
}
public void update(Expense entity) {
new UpdateExpense(this).execute(entity);
}
public void delete(Expense entity) {
new DeleteExpense(this).execute(entity);
}
private class InsertExpense extends ContentChangingExpense<Expense, Void, Void> {
InsertExpense(SQLiteExpenseDataLoader loader) {
super(loader);
}
@Override
protected Void doInBackground(Expense... params) {
mDataSource.insert(params[0]);
return (null);
}
}
private class UpdateExpense extends ContentChangingExpense<Expense, Void, Void> {
UpdateExpense(SQLiteExpenseDataLoader loader) {
super(loader);
}
@Override
protected Void doInBackground(Expense... params) {
mDataSource.update(params[0]);
return (null);
}
}
private class DeleteExpense extends ContentChangingExpense<Expense, Void, Void> {
DeleteExpense(SQLiteExpenseDataLoader loader) {
super(loader);
}
@Override
protected Void doInBackground(Expense... params) {
mDataSource.delete(params[0]);
return (null);
}
}
}
Aucun commentaire:
Enregistrer un commentaire