thank you so much for the posted solution. it is of great help for me.
I want to ask you though some more questions.
1. I would like to populate my tables statically(before even the app is
launched) may be reading from a file onto the db. How can I do it?
2. I would like to add texts written in languages different from english. How
can I do this too?
Please I am really in need of these two things and quick response would be
appreciated.
thank you in advance
--- On Mon, 4/19/10, Liviu Ungureanu <smartli...@gmail.com> wrote:
From: Liviu Ungureanu <smartli...@gmail.com>
Subject: Re: [android-developers] Need Help--- Regarding Creating Database and
Tables
To: android-developers@googlegroups.com
Date: Monday, April 19, 2010, 12:42 PM
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 android-developers@googlegroups.com
To unsubscribe from this group, send email to
android-developers+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/android-developers?hl=en
--
You received this message because you are subscribed to the Google
Groups "Android Developers" group.
To post to this group, send email to android-developers@googlegroups.com
To unsubscribe from this group, send email to
android-developers+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/android-developers?hl=en
