This is my first time implementing SQLite databases and I believe I have it working for the most part. My only issue/question is that every time I insert data, and then later try to retrieve all data that has been inserted into the database, it seems that the last entry is entered in several times. In otherwords each time I add any message, after checking the database it looks like the last message I added was added several times and my previous messages were overwritten. I will post my entire code so that you can review it.
But this is sort of what happens. First time running the app, lets say I try to add "hello" to database. The result is:
test entry>> hello
Then on second entry of putting "world" it prints
test entry>> world
test entry>> world
Then on third entry of putting "where is hello" it prings
test entry>> where is hello
test entry>> where is hello
test entry>> where is hello
This pattern will continue forever. What am I doing wrong? I retrieve data from editText widget and save that to database with
DatabaseManager.saveEntry(MainActivity.this, et.getText().toString().trim(), "hardcoded");
Here is the rest of my code.
DatabaseManager.java
public class DatabaseManager {
public static void saveEntry(Context context, String entry, String test) {
try {
ContentValues values = getContentValuesEntryTable(entry, test);
ContentResolver resolver = context.getContentResolver();
Cursor cursor = resolver.query(EntryTable.CONTENT_URI, null, null, null, null);
if (cursor != null && cursor.getCount() > 0) {
resolver.update(EntryTable.CONTENT_URI, values, null, null);
} else {
resolver.insert(EntryTable.CONTENT_URI, values);
}
cursor.close();
resolver.insert(EntryTable.CONTENT_URI, values);
} catch (Exception e) {
Log.e("TEST", "error: " + e.toString());
e.printStackTrace();
}
}
public static Cursor getEntry(Context context, String entry) {
Cursor cursor;
String sorting = null;
if (TextUtils.isEmpty(entry)) {
cursor = context.getContentResolver().query(EntryTable.CONTENT_URI, null, null, null, sorting);
} else {
cursor = context.getContentResolver().query(EntryTable.CONTENT_URI, null, EntryTable.Cols.COLUMN_ENTRY + " ='" + entry + "'", null, sorting);
}
if (cursor != null) {
cursor.moveToFirst();
}
return cursor;
}
public static Cursor getTest(Context context, String test) {
Cursor cursor;
String sorting = null;
if (TextUtils.isEmpty(test)) {
cursor = context.getContentResolver().query(EntryTable.CONTENT_URI, null, null, null, sorting);
} else {
cursor = context.getContentResolver().query(EntryTable.CONTENT_URI, null, EntryTable.Cols.COLUMN_TEST + " = '" + test + "'", null, sorting);
}
if (cursor != null) {
cursor.moveToFirst();
}
return cursor;
}
private static ContentValues getContentValuesEntryTable(String entry, String test) {
ContentValues values = new ContentValues();
values.put(EntryTable.Cols.COLUMN_ENTRY, entry);
values.put(EntryTable.Cols.COLUMN_TEST, test);
return values;
}
}
DatabaseHelper.java
public class DatabaseHelper extends SQLiteOpenHelper {
public static final String KEY_CREATE_TABLE = "CREATE TABLE IF NOT EXISTS {0} ({1})";
public static final String KEY_DROP_TABLE = "DROP TABLE IF EXISTS {0}";
private static final int CURRENT_DB_VERSION = 1;
private static final String DB_NAME = "qmun.db";
public DatabaseHelper(Context context) {
super(context, DB_NAME, null, CURRENT_DB_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
Log.d("TEST", "DB Creation :: Going to create db ");
createEntryTable(db);
}
private void createEntryTable(SQLiteDatabase db) {
StringBuilder entryTableFields = new StringBuilder();
entryTableFields.append(EntryTable.Cols.COLUMN_ID)
.append(" INTEGER PRIMARY KEY AUTOINCREMENT, ")
.append(EntryTable.Cols.COLUMN_ENTRY).append(" TEXT, ")
.append(EntryTable.Cols.COLUMN_TEST).append(" TEXT");
createTable(db, EntryTable.TABLE_NAME, entryTableFields.toString());
}
public void dropTable(SQLiteDatabase db, String name) {
String query = MessageFormat.format(DatabaseHelper.KEY_DROP_TABLE, name);
db.execSQL(query);
}
public void createTable(SQLiteDatabase db, String name, String fields) {
String query = MessageFormat.format(DatabaseHelper.KEY_CREATE_TABLE, name, fields);
db.execSQL(query);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
dropTable(db, EntryTable.TABLE_NAME);
onCreate(db);
}
}
DatabaseProvider.java
public class DatabaseProvider extends ContentProvider {
private static final String UNKNOWN_URI = "unknown_uri";
private DatabaseHelper dBHelper;
@Override
public boolean onCreate() {
// TODO Auto-generated method stub
Log.i("TEST", "creating db");
dBHelper = new DatabaseHelper(getContext());
dBHelper.getWritableDatabase();
return false;
}
@Override
public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) {
// TODO Auto-generated method stub
SQLiteDatabase db = dBHelper.getReadableDatabase();
final int token = ContentDescriptor.URI_MATCHER.match(uri);
Cursor result = null;
switch (token) {
case EntryTable.PATH_TOKEN: {
result = doQuery(db, uri, EntryTable.TABLE_NAME, projection, selection, selectionArgs,
sortOrder);
break;
}
default:
break;
}
return result;
}
private Cursor doQuery(SQLiteDatabase db, Uri uri, String tableName, String[] projection,
String selection, String[] selectionArgs, String sortOrder) {
SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
builder.setTables(tableName);
Cursor result = builder.query(db, projection, selection, selectionArgs, sortOrder, null, null);
result.setNotificationUri(getContext().getContentResolver(), uri);
return result;
}
@Override
public String getType(Uri uri) {
// TODO Auto-generated method stub
return null;
}
@Override
public Uri insert(Uri uri, ContentValues values) {
// TODO Auto-generated method stub
SQLiteDatabase db = dBHelper.getWritableDatabase();
int token = ContentDescriptor.URI_MATCHER.match(uri);
Uri result = null;
switch (token) {
case EntryTable.PATH_TOKEN: {
result = doInsert(db, EntryTable.TABLE_NAME, EntryTable.CONTENT_URI, uri, values);
break;
}
default:
break;
}
if (result == null) {
throw new IllegalArgumentException(UNKNOWN_URI + uri);
}
return result;
}
@Override
public int bulkInsert(Uri uri, ContentValues[] values) {
String table = null;
int token = ContentDescriptor.URI_MATCHER.match(uri);
switch (token) {
case EntryTable.PATH_TOKEN: {
table = EntryTable.TABLE_NAME;
break;
}
default:
break;
}
SQLiteDatabase db = dBHelper.getWritableDatabase();
db.beginTransaction();
for (ContentValues cv : values) {
db.insert(table, null, cv);
}
db.setTransactionSuccessful();
db.endTransaction();
return values.length;
}
@Override
public int delete(Uri uri, String selection, String[] selectionArgs) {
// TODO Auto-generated method stub
SQLiteDatabase db = dBHelper.getWritableDatabase();
int token = ContentDescriptor.URI_MATCHER.match(uri);
int result = 0;
switch (token) {
case EntryTable.PATH_TOKEN: {
result = doDelete(db, uri, EntryTable.TABLE_NAME, selection, selectionArgs);
break;
}
default:
break;
}
return result;
}
@Override
public int update(Uri uri, ContentValues values, String selection, String[] selectionArgs) {
// TODO Auto-generated method stub
SQLiteDatabase db = dBHelper.getWritableDatabase();
int token = ContentDescriptor.URI_MATCHER.match(uri);
int result = 0;
switch (token) {
case EntryTable.PATH_TOKEN: {
result = doUpdate(db, uri, EntryTable.TABLE_NAME, selection, selectionArgs, values);
break;
}
default:
break;
}
return result;
}
private int doUpdate(SQLiteDatabase db, Uri uri, String tableName, String selection,
String[] selectionArgs, ContentValues values) {
int result = db.update(tableName, values, selection, selectionArgs);
getContext().getContentResolver().notifyChange(uri, null);
return result;
}
private int doDelete(SQLiteDatabase db, Uri uri, String tableName, String selection,
String[] selectionArgs) {
int result = db.delete(tableName, selection, selectionArgs);
getContext().getContentResolver().notifyChange(uri, null);
return result;
}
private Uri doInsert(SQLiteDatabase db, String tableName, Uri contentUri, Uri uri, ContentValues values) {
long id = db.insert(tableName, null, values);
Uri result = contentUri.buildUpon().appendPath(String.valueOf(id)).build();
getContext().getContentResolver().notifyChange(uri, null);
return result;
}
}
DBHelper.java
public class DBHelper extends SQLiteOpenHelper {
public static String TAG = DBHelper.class.getSimpleName();
// Constants
private static Context mContext;
private SQLiteDatabase mDatabase;
public static final String KEY_ID = "id";
private static final String DATABASE_NAME = "database.db";
private static final String DATABASE_PATH = "data/sqlite/abcmouse/";
private static final String DATABASE_TABLE = "tbl_masteraccount";
private static final int DATABASE_VERSION = 1;
public DBHelper(Context context) {
// TODO Auto-generated constructor stub
super(context, DATABASE_NAME, null, DATABASE_VERSION);
mContext = context;
}
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
System.out.println("Upgrading db version (v" + oldVersion +
") to (v" + newVersion + ")");
db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
onCreate(db);
}
/**
* Method is used to create an empty database that will be created into the default system path
* of the application. The former database will be overwritten
*
* @throws IOException
*/
public void createDatabase() throws IOException {
boolean dBExist = checkDatabase();
if (!dBExist) {
getReadableDatabase();
try {
copyDatabase();
} catch (Exception e) {
Log.e(TAG, "error creating db: " + e.toString());
e.printStackTrace();
}
}
}
/**
* Method is used to copy database from local assets folder to the created database in the
* system folder. The copying is done via transferring bytestream
*
* @throws IOException
*/
private void copyDatabase() throws IOException {
// open local db as input stream
InputStream is = mContext.getAssets().open(DATABASE_NAME);
// path to the new created empty db
String outFileName = DATABASE_PATH + DATABASE_NAME;
// open the empty db as the output stream
OutputStream os = new FileOutputStream(outFileName);
// transfer bytes from the inputfile to the outputfile
byte buffer[] = new byte[1024];
int length;
while ((length = is.read(buffer)) > 0) {
os.write(buffer, 0, length);
}
// close streams
os.flush();
os.close();
is.close();
}
/**
* Method is used to check if the database already exists to avoid re-copying the file each time
* you open the application
*
* @return true if it exists, otherwise false
*/
private boolean checkDatabase() {
SQLiteDatabase checkDb = null;
try {
String mPath = DATABASE_PATH + DATABASE_NAME;
checkDb = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.OPEN_READONLY);
} catch (Exception e) {
Log.e(TAG, "error checking existing db: " + e.toString());
e.printStackTrace();
}
if (checkDb != null) {
checkDb.close();
}
return checkDb != null ? true : false;
}
/**
* Method is used to add data to database
*
* @param table
* @param key
* @param value
* @return
*/
public long addData(String table, String[] key, String[] value) {
mDatabase = getWritableDatabase();
if (mDatabase.isOpen()) {
ContentValues cv = new ContentValues();
for (int i = 0; i < key.length; i++) {
cv.put(key[i], value[i]);
}
return mDatabase.insert(table, null, cv);
}
return 0;
}
/**
* Method is used to update data on database
*
* @param table
* @param key
* @param value
* @param whereClause
* @return
*/
public long upgradeData(String table, String[] key, String[] value, String whereClause) {
mDatabase = getWritableDatabase();
if (mDatabase.isOpen()) {
ContentValues cv = new ContentValues();
for (int i = 0; i < key.length; i++) {
cv.put(key[i], value[i]);
}
return mDatabase.update(table, cv, whereClause, null);
}
return 0;
}
/**
* Method is used to retrieve stored data
*
* @param table
* @param columns
* @param selection
* @param selectionArgs
* @param groupBy
* @param having
* @param orderBy
* @return
*/
public Cursor getAllData(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy) {
Cursor cursor = null;
if (mDatabase != null) {
if (!mDatabase.isOpen()) {
mDatabase = getWritableDatabase();
}
if (mDatabase.isOpen()) {
cursor = mDatabase.query(table, columns, selection, selectionArgs, groupBy, having, orderBy);
}
}
return cursor;
}
/**
* Method is used to open the database
*
* @throws SQLException
*/
public void openDatabase() throws SQLException {
String mPath = DATABASE_PATH + DATABASE_NAME;
mDatabase = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.OPEN_READONLY);
}
public long deleteRecord(String table, String condition) {
mDatabase = getWritableDatabase();
if (mDatabase.isOpen()) {
return mDatabase.delete(table, condition, null);
}
return 0;
}
@Override
public void close() {
if (mDatabase != null) {
mDatabase.close();
}
super.close();
}
}
In my MainActivity after I saveEntry method, I print out what I have in my database with
Cursor cursor = DatabaseManager.getEntry(MainActivity.this, "");
if (cursor != null) {
if (cursor.getCount() > 0) {
if (cursor.moveToFirst()) {
do {
Log.d("TEST", "test`enter code here` entry>> " + cursor.getString(cursor.getColumnIndex(EntryTable.Cols.COLUMN_ENTRY)));
} while (cursor.moveToNext());
}
}
cursor.close();
}
Aucun commentaire:
Enregistrer un commentaire