Hi!
I use this method to work with database:
// this is my DatabaseManager class
package com.liviu.app.nearbyplace.data;
import java.util.ArrayList;
import com.liviu.app.nearbyplace.util.Constants;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
public class DatabaseManager {
//data
private String TAG = "DatabaseManager";
private SQLiteDatabase db;
private Context context;
public DatabaseManager(Context ctx) {
Log.e(TAG, "DatabaseManager Constructor");
context = ctx;
openAndCreateDatabase();
closeDatabaseManager();
}
public boolean openAndCreateDatabase(){
try{
db = context.openOrCreateDatabase(Constants.DATABASE_NAME,
Context.MODE_PRIVATE, null);
Log.e(TAG,"Database is ready!");
// here I create my tables:
db.execSQL(Constants.CREATE_SAVED_ITEMS_TABLE);
db.execSQL(Constants.CREATE_HISTORY_TABLE);
return true;
}
catch (SQLException e){
Log.e(TAG,"ERROR at accesing database!");
Log.e(TAG,e.toString());
return false;
}
catch (IllegalStateException e) {
e.printStackTrace();
Log.e(TAG, "database is not closed in openAndCreateDatabase()");
closeDatabaseManager();
return false;
}
}
public boolean openDatabase(){
if(db != null && db.isOpen())
db.close();
try{
db =
context.openOrCreateDatabase(Constants.DATABASE_NAME,Context.MODE_PRIVATE,
null);
return true;
}
catch (SQLException e){
Log.e(TAG,"ERROR at accesing database!");
Log.e(TAG,e.toString());
return false;
}
catch (IllegalStateException e) {
e.printStackTrace();
Log.e(TAG, "database is not closed in openDatabase()");
closeDatabaseManager();
return openDatabase();
}
}
public void closeDatabaseManager(){
if(db.isOpen())
db.close();
else
Log.e(TAG,"Database is not open!");
}
public boolean insertToHistory(String when, String what, int count) {
ContentValues values = new ContentValues(3);
values.put(Constants.DATE_FIELD, when);
values.put(Constants.ITEM_TITLE_FIELD, what);
values.put(Constants.ITEM_RESULTS_COUNT_FIELD, count);
long affectedRows = 0;
try{
affectedRows = db.insertOrThrow(Constants.TABLE_HISTORY, null, values);
Log.e(TAG, "affectedRows: " + affectedRows);
if(affectedRows != -1)
return true;
else
return false;
}
catch (SQLException e) {
Log.e(TAG, "nu am inserat in baza de date " + what + " count: " + count);
e.printStackTrace();
return false;
}
}
public ArrayList<HistoryItem> getSuggestions() {
Log.e(TAG, "getSuggestions()");
HistoryItem hItem;
ArrayList<HistoryItem> suggestions;
String[] sProjection = new String[]{"distinct " +
Constants.ITEM_TITLE_FIELD, Constants.ITEM_RESULTS_COUNT_FIELD};
Cursor cSuggestions = db.query(Constants.TABLE_HISTORY,
sProjection,
null,
null,
null,
null,
null);
if(cSuggestions == null){
Log.e(TAG, "cSuggestions is null");
return null;
}
int numRows = cSuggestions.getCount();
suggestions = new ArrayList<HistoryItem>(numRows);
cSuggestions.moveToFirst();
for(int i = 0; i < numRows; i++){
hItem = new HistoryItem(cSuggestions.getString(0), cSuggestions.getInt(1));
suggestions.add(hItem);
Log.e(TAG, "Suggestion: " + cSuggestions.getString(0) + " results count: " +
cSuggestions.getInt(1));
cSuggestions.moveToNext();
}
Log.e(TAG, "suggestions count: " + suggestions.size());
return suggestions;
}
public String[] getSuggestionsAsArray() {
Log.e(TAG, "getSuggestions()");
String[] suggestions;
String[] sProjection = new String[]{ "distinct " +
Constants.ITEM_TITLE_FIELD, Constants.ITEM_RESULTS_COUNT_FIELD };
Cursor cSuggestions = db.query(Constants.TABLE_HISTORY,
sProjection,
null,
null,
null,
null,
null);
if(cSuggestions == null){
Log.e(TAG, "cSuggestions is null");
return null;
}
int numRows = cSuggestions.getCount();
suggestions = new String[numRows];
cSuggestions.moveToFirst();
for(int i = 0; i < numRows; i++){
suggestions[i] = cSuggestions.getString(0);
Log.e(TAG, "Suggestion: " + cSuggestions.getString(0) + " results count: " +
cSuggestions.getInt(1));
cSuggestions.moveToNext();
}
Log.e(TAG, "suggestions count: " + suggestions.length);
return suggestions;
}
public ArrayList<String> getHistoryDates() {
ArrayList<String> datesList;
int numRows;
String[] datesProjection = new String[]{ "distinct " +
Constants.DATE_FIELD };
Cursor dateCursor = db.query(Constants.TABLE_HISTORY,
datesProjection,
null,
null,
null,
null,
null);
if(dateCursor == null){
Log.e(TAG, "dateCursor is null");
return new ArrayList<String>();
}
numRows = dateCursor.getCount();
datesList = new ArrayList<String>(numRows);
dateCursor.moveToFirst();
for(int i = 0; i < numRows; i++){
Log.e(TAG, "date: " + dateCursor.getString(0));
datesList.add(dateCursor.getString(0));
dateCursor.moveToNext();
}
Log.e(TAG, "getHistoryDates() return " + datesList.size() + " dates");
return datesList;
}
public ArrayList<String> getHistoryForDate(String date) {
ArrayList<String> hItemsList;
int numRows;
String[] hItemsProjection = new String[] { Constants.ITEM_TITLE_FIELD,
Constants.ITEM_RESULTS_COUNT_FIELD };
Cursor hCursor = db.query( Constants.TABLE_HISTORY,
hItemsProjection,
Constants.DATE_FIELD + "='" + date + "'",
null,
null,
null,
null);
if(hCursor == null){
Log.e(TAG, "hCursor is null");
return new ArrayList<String>();
}
numRows = hCursor.getCount();
hItemsList = new ArrayList<String>();
hCursor.moveToFirst();
for(int i = 0; i < numRows; i++){
Log.e(TAG, "item_title: " + hCursor.getString(0) + " results count: " +
hCursor.getInt(1));
hItemsList.add(hCursor.getString(0));
hCursor.moveToNext();
}
Log.e(TAG, "getHistoryForDate(" + date + ") return " + hItemsList.size() +
" results");
return hItemsList;
}
}
// Constants class
package com.liviu.app.nearbyplace.util;
public class Constants {
// database
public static final String DATABASE_NAME = "items_found_database";
public static final String TABLE_HISTORY = "history_table";
public static final String TABLE_SAVED_ITEMS = "saved_items_table";
public static final String CREATE_HISTORY_TABLE = "create table if not
exists history_table( date_field text not null," +
"item_title_field text not null," +
"item_results_count integer not null);";
public static final String CREATE_SAVED_ITEMS_TABLE = "create table if not
exists saved_items_table( item_id_field integer not null primary key
autoincrement," +
"item_title_field text not null," +
"item_type_field text not null," +
"item_street_field text," +
"item_city_field text," +
"item_country_field text," +
"item_latitude_field double not null," +
"item_longitude_field double not null," +
"item_phone_field text not null);";
public static final String ITEM_ID_FIELD = "item_id_field";
public static final String ITEM_TITLE_FIELD = "item_title_field";
public static final String ITEM_TYPE_FIELD = "item_type_field";
public static final String ITEM_STREET_FIELD = "item_street_field";
public static final String ITEM_CITY_FIELD = "item_city_field";
public static final String ITEM_COUNTRY_FIELD = "item_country_field";
public static final String ITEM_LATITUDE_FIELD = "item_latitude_field";
public static final String ITEM_LONGITUDE_FIELD = "item_longitude_field";
public static final String ITEM_PHONE_FIELD = "item_phone_field";
public static final String ITEM_RESULTS_COUNT_FIELD = "item_results_count";
public static final String DATE_FIELD = "date_field";
}
If you want to use this way to work with database, you have to:
1. In your activity you must create an object DatabaseManager and work with
it:
Example:
DatabaseManager dbManager = new DatabaseManager(getApplicationContext());
// everytime when you want to execute an method from this object you have
to do that in this way:
dbManager.openDatabase();
// call an method from this object
dbManager.yourMethod();
// we have to close database otherwise we will have an memory leak
dbManager.closeDatabase();
Hope this help!
p.s: If you have questions, I am happy to help you!
Liviu
--
You received this message because you are subscribed to the Google
Groups "Android Developers" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/android-developers?hl=en
