andbook!.pdf - Learning Android Get an anddev.org - Android-Shirt Back to index
anddev.org Header Logo
FAQ Search Top rated articles Browse Feeds anddev.org - Authors Contact Details Register Log in

Working with the SQLite-Database - Cursors

Goto page 1, 2, 3, 4, 5  Next
 
       anddev.org - Android Development Community | Android Tutorials | Index -> Novice Tutorials
Author Message
plusminus
Site Admin
Site Admin


Joined: 14 Nov 2007
Posts: 2655
Location: College Park, MD

PostPosted: Wed Dec 19, 2007 1:05 am    Post subject: Working with the SQLite-Database - Cursors Reply with quote

Working with the SQLite-Database - Cursors

Warning Compatible for SDK version m3-xxx or older Warning


What you learn: You will learn how to create databases and tables, insert and query datasets in the Android-built-in SQLite-DataBase-Server.

Difficulty: 1 of 5 Smile

Idea Questions/Problems: Simply post below...

What it will look like:


Description:
We'll need to to the following things:
  1. Create a DataBase (generally this is done just once)
  2. Open the DataBase
  3. Create a Table (generally this is done just once)
  4. Insert some Datasets
  5. Query for some Datasets
  6. Close the Database

0.) So lets work it out:
We first do some setup. Declaring the DataBases/Tables we are using as final should always be preferred before typing the name to every single statement. (Changes are a lot easier !).
Java:
public class DataBaseWork extends ListActivity {

     private final String MY_DATABASE_NAME = "myCoolUserDB";
     private final String MY_DATABASE_TABLE = "t_Users";

     /** Called when the activity is first created. */
     @Override
     public void onCreate(Bundle icicle) {
          super.onCreate(icicle);
          /* Will hold the 'Output' we want to display at the end. */
          ArrayList<String> results = new ArrayList<String>();


1.) So lets create the DataBase:
Java:
          SQLiteDatabase myDB = null;
          try {
               /* Create the Database (no Errors if it already exists) */
               this.createDatabase(MY_DATABASE_NAME, 1, MODE_PRIVATE, null);


2.) Having created the DataBase we want to open it:
Java:
               /* Open the DB and remember it */
               myDB = this.openDatabase(MY_DATABASE_NAME, null);


3.) Now we create a simple Table with just four columns:
Java:
               /* Create a Table in the Database. */
               myDB.execSQL("CREATE TABLE IF NOT EXISTS "
                                   + MY_DATABASE_TABLE
                                   + " (LastName VARCHAR, FirstName VARCHAR,"
                                   + " Country VARCHAR, Age INT(3));");

4.) Put two DataSets to the recently created Table:
Java:
               /* Add two DataSets to the Table. */
               myDB.execSQL("INSERT INTO "
                                   + MY_DATABASE_TABLE
                                   + " (LastName, FirstName, Country, Age)"
                                   + " VALUES ('Gramlich', 'Nicolas', 'Germany', 20);");
               myDB.execSQL("INSERT INTO "
                                   + MY_DATABASE_TABLE
                                   + " (LastName, FirstName, Country, Age)"
                                   + " VALUES ('Doe', 'John', 'US', 34);");

5.) Having written some DataSets to the Table, we would want to receive them back somewhen. Thr result of a query is a Cursor that can move over all the results returned by the query. We apply Projection (Just the Specified Columns) and Selection (WHERE ...) to it and a LIMIT. Just as we would do in any other SQL-"Dialect":
Java:
               /* Query for some results with Selection and Projection. */
               Cursor c = myDB.query("SELECT FirstName,Age" +
                                        " FROM " + MY_DATABASE_TABLE
                                        + " WHERE Age > 10 LIMIT 7;",
                                        null);

6.) Now having queried, we retrieve the ColumIndexes of two Columns calling the getColumnIndex(String);-method of the Cursor:
Java:
               /* Get the indices of the Columns we will need */
               int firstNameColumn = c.getColumnIndex("FirstName");
               int ageColumn = c.getColumnIndex("Age");
               
               /* Check if our result was valid. */
               if (c != null) {
                    /* Check if at least one Result was returned. */
                    if (c.first()) {
                         int i = 0;
                         /* Loop through all Results */
                         do {
                              i++;
                              /* Retrieve the values of the Entry
                               * the Cursor is pointing to. */

                              String firstName = c.getString(firstNameColumn);
                              int age = c.getInt(ageColumn);
                              /* We can also receive the Name
                               * of a Column by its Index.
                               * Makes no sense, as we already
                               * know the Name, but just to shwo we can Wink */

                              String ageColumName = c.getColumnName(ageColumn);
                              
                              /* Add current Entry to results. */
                              results.add("" + i + ": " + firstName
                                             + " (" + ageColumName + ": " + age + ")");
                         } while (c.next());
                    }
               }

7.) Finally close the DataBase (if it has been opened):
Java:
          } catch (FileNotFoundException e) {
          } finally {
               if (myDB != null)
                    myDB.close();
          }

8.) In the end, display our Entries:
Java:
          this.setListAdapter(new ArrayAdapter<String>(this,
                    android.R.layout.simple_list_item_1_small, results));
     }
}


You probably have recognized that SQLite in an Android-Device is just as simple as on any other Machine.

And probably a bit more comfortable Exclamation

Thats it Smile


Regards,
plusminus

_________________
Download my apps Idea
Please remember, that this board is give & take Smile


| Android Development Community / Tutorials


Last edited by plusminus on Fri Feb 15, 2008 12:15 pm; edited 1 time in total
Back to top
View user's profile Send private message Send e-mail Visit poster's website
cybersat
Freshman
Freshman


Joined: 18 Dec 2007
Posts: 5

PostPosted: Tue Dec 25, 2007 4:02 pm    Post subject: Plz help Reply with quote

hi,
Please attach full working source code, i am unable to view database details.
1. Kindly attach full source code.zip
2. How can we view databases and tables using command prompt.
Back to top
View user's profile Send private message
plusminus
Site Admin
Site Admin


Joined: 14 Nov 2007
Posts: 2655
Location: College Park, MD

PostPosted: Wed Dec 26, 2007 11:03 pm    Post subject: Reply with quote

Hello Cybersat,

1. Added the full (zipped) source of my project.
2. Just quoting a Google-Groups-Thread for now:
Quote:
Try the command .tables once you get into sqlite with your command
line:
sqlite3 /data/data/softtek.com.apps.test/databases/data.db

When I do that my table shows. Maybe the table has a different name
(typo?). Here is a detailed account of what I did to see the contents
of the table that was modified using the sample app Note pad:

1. launch the emulator
2. type adb shell
3. now go to the folder that contains the db, in my case I am working
with the notepad.db:
3.1 cd data and then again cd data
3.2 ls *.* You will see a number of app_x (where x is a number) with
database at their right hand side.
3.3 ls You will see the same app_x now with a folder path. For
instance my app_11 has a folder path: com.google.android.notepad
3.4 cd com.google.android.notepad
3.5 ls You will see again the app_11 and to the right a folder
databases.
3.6 cd databases
3.7 your table should be there. My table showed as note_pad.db
3.8 don't go inside sqlite3, rather invoke the table with it: sqlite3
note_pad.db
4. NOW you are inside sqlite
5. .tables Note that 'notes' appears (I expected note_pad but rather
'notes' showed up!!)
6. And to finish: select * from notes provided me with the contents
of the table:

sqlite> .tables
.tables
notes
sqlite> select * from notes
select * from notes;
1|first note in 033|first note in 033|1198179145522|1198179153108
2|dddd|dddd|1198259573843|1198259578290
sqlite>

Hope it helps.


Regards,
plusminus



DataBaseWork.zip
 Description:
DataBaseWork Project

Download
 Filename:  DataBaseWork.zip
 Filesize:  36.07 KB
 Downloaded:  1839 Time(s)


_________________
Download my apps Idea
Please remember, that this board is give & take Smile


| Android Development Community / Tutorials
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Katharnavas
Senior Developer
Senior Developer


Joined: 04 Dec 2007
Posts: 100
Location: India

PostPosted: Thu Dec 27, 2007 5:48 am    Post subject: Reply with quote

Hi,
Nice tutorial and thanks for the step by step instructions of accessing the database thro the command line.
Back to top
View user's profile Send private message Yahoo Messenger
derek_lan
Freshman
Freshman


Joined: 25 Dec 2007
Posts: 8

PostPosted: Sat Dec 29, 2007 11:22 am    Post subject: Reply with quote

Tks a lot
Could u tell me how to run in the sqlite mode?
Back to top
View user's profile Send private message
plusminus
Site Admin
Site Admin


Joined: 14 Nov 2007
Posts: 2655
Location: College Park, MD

PostPosted: Thu Jan 03, 2008 11:54 am    Post subject: Reply with quote

derek_lan wrote:
Tks a lot
Could u tell me how to run in the sqlite mode?


Didn't understand your question Sad
Please rephrase.

Regards,
plusminus

_________________
Download my apps Idea
Please remember, that this board is give & take Smile


| Android Development Community / Tutorials
Back to top
View user's profile Send private message Send e-mail Visit poster's website
steve gerard
Freshman
Freshman


Joined: 26 Dec 2007
Posts: 2
Location: India

PostPosted: Wed Jan 09, 2008 12:14 pm    Post subject: Reply with quote

Hi,
I would like to know is there any possibility of doing the database operations like
1. creating a database
2. opening a database
3. creating a table
4. inserting data inside the table
5. update the table
from a normal utility class whose methods are called on purpose without extending an activity.

Because without extending activity we cant use
this.createDatabase(MY_DATABASE_NAME, 1, MODE_PRIVATE, null);
myDB = this.openDatabase(MY_DATABASE_NAME, null);

Bcoz these lines are throwing errors if we dont extend the activity class.
I dont wanna display anything in the database utility class all i need is to call the appropriate methods
in that class which will do the process and returns the result to the class that calls it.

How to do it ? Any possible solutions or ideas ?

Thanks in Advance..

Steve Gerard
Back to top
View user's profile Send private message
plusminus
Site Admin
Site Admin


Joined: 14 Nov 2007
Posts: 2655
Location: College Park, MD

PostPosted: Wed Jan 09, 2008 5:57 pm    Post subject: Reply with quote

Hello Steve,

a new SQLiteDatabase is privately associated with the application package of the (Application-)Context you are calling it from.
So as you do not want to display something, perhaps an IntentReceiver is enough for you. Like this:
Java:
public class MyStartupIntentReceiver extends IntentReceiver {
     @Override
     public void onReceiveIntent(Context context, Intent intent) {
          context.createDatabase(arg0, arg1, arg2, arg3);
          // ...
     }
}


Whole IntentReceiver-Example Source here.

Regards,
plusminus

_________________
Download my apps Idea
Please remember, that this board is give & take Smile


| Android Development Community / Tutorials
Back to top
View user's profile Send private message Send e-mail Visit poster's website
ramgraph1
Experienced Developer
Experienced Developer


Joined: 09 Jan 2008
Posts: 68

PostPosted: Wed Jan 09, 2008 10:22 pm    Post subject: Reply with quote

Thanks for posting this! This is really useful and helps me understand the whole using a database issue a lot better. What I still don't get though is how to properly alter the database info from another class.
If I have a class with an EditText and a button, what code would I use to:
*upon typing a country in the EditText and clicking the button -
*replace the "Germany" in your DataBaseWork with the country in the EditText.

If I could see a simple example in code, I think I could work from there to be able to use databases in my apps!
Thanks in advance for any help you can give.
Back to top
View user's profile Send private message
plusminus
Site Admin
Site Admin


Joined: 14 Nov 2007
Posts: 2655
Location: College Park, MD

PostPosted: Fri Jan 11, 2008 7:21 pm    Post subject: Reply with quote

Hello ramgraph,

This could should do what you requested:
Java:
okButton.setOnClickListener(new OnClickListener(){
               EditText et = (EditText)findViewById(R.id.myedittext);
               String replacewith = keyWordEditText.getText().toString();

               myDB.execSQL("UPDATE "
                                   + MY_DATABASE_TABLE
                                   + " SET country='" + replacewith + "'"
                                   + " WHERE country='Germany'");
}


Sorry for the late answer, but I'm currently really busy!

Regards,
plusminus

_________________
Download my apps Idea
Please remember, that this board is give & take Smile


| Android Development Community / Tutorials
Back to top
View user's profile Send private message Send e-mail Visit poster's website
ramgraph1
Experienced Developer
Experienced Developer


Joined: 09 Jan 2008
Posts: 68

PostPosted: Fri Jan 11, 2008 11:50 pm    Post subject: Reply with quote

Thanks for taking the time to help others when you are so busy! I will try out this solution. It should be what I need to make a decent start with using databases.
Back to top
View user's profile Send private message
Ghalya
Freshman
Freshman


Joined: 09 Feb 2008
Posts: 9
Location: Dubai, UAE

PostPosted: Tue Feb 12, 2008 1:40 pm    Post subject: Reply with quote

clear as always .... Keep it up Smile
Back to top
View user's profile Send private message
szeldon
Freshman
Freshman


Joined: 14 Feb 2008
Posts: 5

PostPosted: Fri Feb 15, 2008 11:32 am    Post subject: Reply with quote

Hi, great site and tutorial. I have a question regarding SQLiteDatabase.query(). Are you sure that arguments should look like those in tutorial? API doc says:

Cursor query(boolean distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy);

Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)

_________________
szeldon.com
Back to top
View user's profile Send private message
plusminus
Site Admin
Site Admin


Joined: 14 Nov 2007
Posts: 2655
Location: College Park, MD

PostPosted: Fri Feb 15, 2008 11:54 am    Post subject: Reply with quote

Hello szeldon,

this has changed from SDK update of m3 to m5 Exclamation
I like the queries much more now Smile
szeldon wrote:
Hi, great site and tutorial. I have a question regarding SQLiteDatabase.query(). Are you sure that arguments should look like those in tutorial? API doc says:

Cursor query(boolean distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy);

Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)


Regards,
plusminus

_________________
Download my apps Idea
Please remember, that this board is give & take Smile


| Android Development Community / Tutorials
Back to top
View user's profile Send private message Send e-mail Visit poster's website
szeldon
Freshman
Freshman


Joined: 14 Feb 2008
Posts: 5

PostPosted: Fri Feb 15, 2008 12:04 pm    Post subject: Reply with quote

plusminus wrote:
Hello szeldon,

this has changed from SDK update of m3 to m5 Exclamation
I like the queries much more now Smile
szeldon wrote:
Hi, great site and tutorial. I have a question regarding SQLiteDatabase.query(). Are you sure that arguments should look like those in tutorial? API doc says:

Cursor query(boolean distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy);

Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)


Regards,
plusminus


I thought so. Great new way for doing that. I like this "programming way" rather the old SQL way which I hate by the way Smile Thanks for clarifying.

_________________
szeldon.com
Back to top
View user's profile Send private message
Display posts from previous:   
       anddev.org - Android Development Community | Android Tutorials | Index -> Novice Tutorials All times are GMT + 1 Hour
Goto page 1, 2, 3, 4, 5  Next
Page 1 of 5

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You can download files in this forum


© 2007, Android Development Community
All rights reserved.
Powered by phpBB.