Working with the SQLite-Database - Cursors

Basic Tutorials concerning: GUI, Views, Activites, XML, Layouts, Intents, ...

Working with the SQLite-Database - Cursors

Postby plusminus » Wed Dec 19, 2007 1:05 am

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 :)

:idea: Questions/Problems: Simply post below...

What it will look like:
Image


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 !).
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. public class DataBaseWork extends ListActivity {
  2.  
  3.         private final String MY_DATABASE_NAME = "myCoolUserDB";
  4.         private final String MY_DATABASE_TABLE = "t_Users";
  5.  
  6.         /** Called when the activity is first created. */
  7.         @Override
  8.         public void onCreate(Bundle icicle) {
  9.                 super.onCreate(icicle);
  10.                 /* Will hold the 'Output' we want to display at the end. */
  11.                 ArrayList<String> results = new ArrayList<String>();
Parsed in 0.032 seconds, using GeSHi 1.0.8.4


1.) So lets create the DataBase:
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.                 SQLiteDatabase myDB = null;
  2.                 try {
  3.                         /* Create the Database (no Errors if it already exists) */
  4.                         this.createDatabase(MY_DATABASE_NAME, 1, MODE_PRIVATE, null);
Parsed in 0.031 seconds, using GeSHi 1.0.8.4


2.) Having created the DataBase we want to open it:
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.                         /* Open the DB and remember it */
  2.                         myDB = this.openDatabase(MY_DATABASE_NAME, null);
Parsed in 0.035 seconds, using GeSHi 1.0.8.4


3.) Now we create a simple Table with just four columns:
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.                         /* Create a Table in the Database. */
  2.                         myDB.execSQL("CREATE TABLE IF NOT EXISTS "
  3.                                                         + MY_DATABASE_TABLE
  4.                                                         + " (LastName VARCHAR, FirstName VARCHAR,"
  5.                                                         + " Country VARCHAR, Age INT(3));");
Parsed in 0.036 seconds, using GeSHi 1.0.8.4

4.) Put two DataSets to the recently created Table:
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.                         /* Add two DataSets to the Table. */
  2.                         myDB.execSQL("INSERT INTO "
  3.                                                         + MY_DATABASE_TABLE
  4.                                                         + " (LastName, FirstName, Country, Age)"
  5.                                                         + " VALUES ('Gramlich', 'Nicolas', 'Germany', 20);");
  6.                         myDB.execSQL("INSERT INTO "
  7.                                                         + MY_DATABASE_TABLE
  8.                                                         + " (LastName, FirstName, Country, Age)"
  9.                                                         + " VALUES ('Doe', 'John', 'US', 34);");
Parsed in 0.037 seconds, using GeSHi 1.0.8.4

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":
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.                         /* Query for some results with Selection and Projection. */
  2.                         Cursor c = myDB.query("SELECT FirstName,Age" +
  3.                                                                 " FROM " + MY_DATABASE_TABLE
  4.                                                                 + " WHERE Age > 10 LIMIT 7;",
  5.                                                                 null);
Parsed in 0.036 seconds, using GeSHi 1.0.8.4

6.) Now having queried, we retrieve the ColumIndexes of two Columns calling the getColumnIndex(String);-method of the Cursor:
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.                         /* Get the indices of the Columns we will need */
  2.                         int firstNameColumn = c.getColumnIndex("FirstName");
  3.                         int ageColumn = c.getColumnIndex("Age");
  4.                        
  5.                         /* Check if our result was valid. */
  6.                         if (c != null) {
  7.                                 /* Check if at least one Result was returned. */
  8.                                 if (c.first()) {
  9.                                         int i = 0;
  10.                                         /* Loop through all Results */
  11.                                         do {
  12.                                                 i++;
  13.                                                 /* Retrieve the values of the Entry
  14.                                                  * the Cursor is pointing to. */
  15.                                                 String firstName = c.getString(firstNameColumn);
  16.                                                 int age = c.getInt(ageColumn);
  17.                                                 /* We can also receive the Name
  18.                                                  * of a Column by its Index.
  19.                                                  * Makes no sense, as we already
  20.                                                  * know the Name, but just to shwo we can <img src="http://www.anddev.org/images/smilies/wink.png" alt=";)" title="Wink" /> */
  21.                                                 String ageColumName = c.getColumnName(ageColumn);
  22.                                                
  23.                                                 /* Add current Entry to results. */
  24.                                                 results.add("" + i + ": " + firstName
  25.                                                                         + " (" + ageColumName + ": " + age + ")");
  26.                                         } while (c.next());
  27.                                 }
  28.                         }
Parsed in 0.039 seconds, using GeSHi 1.0.8.4

7.) Finally close the DataBase (if it has been opened):
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.                 } catch (FileNotFoundException e) {
  2.                 } finally {
  3.                         if (myDB != null)
  4.                                 myDB.close();
  5.                 }
Parsed in 0.036 seconds, using GeSHi 1.0.8.4

8.) In the end, display our Entries:
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.                 this.setListAdapter(new ArrayAdapter<String>(this,
  2.                                 android.R.layout.simple_list_item_1_small, results));
  3.         }
  4. }
Parsed in 0.036 seconds, using GeSHi 1.0.8.4


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 :!:

Thats it :)


Regards,
plusminus
Last edited by plusminus on Fri Feb 15, 2008 12:15 pm, edited 1 time in total.
Image
Image | Android Development Community / Tutorials
User avatar
plusminus
Site Admin
Site Admin
 
Posts: 2688
Joined: Wed Nov 14, 2007 8:37 pm
Location: Schriesheim, Germany

Top

Plz help

Postby cybersat » Tue Dec 25, 2007 4:02 pm

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.
cybersat
Freshman
Freshman
 
Posts: 5
Joined: Tue Dec 18, 2007 8:09 am

Postby plusminus » Wed Dec 26, 2007 11:03 pm

Hello Cybersat,

1. Added the full (zipped) source of my project.
2. Just quoting a Google-Groups-Thread for now:
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
Attachments
DataBaseWork.zip
DataBaseWork Project
(36.07 KiB) Downloaded 10382 times
Image
Image | Android Development Community / Tutorials
User avatar
plusminus
Site Admin
Site Admin
 
Posts: 2688
Joined: Wed Nov 14, 2007 8:37 pm
Location: Schriesheim, Germany

Postby Katharnavas » Thu Dec 27, 2007 5:48 am

Hi,
Nice tutorial and thanks for the step by step instructions of accessing the database thro the command line.
Katharnavas
Senior Developer
Senior Developer
 
Posts: 100
Joined: Tue Dec 04, 2007 5:57 am
Location: India

Postby derek_lan » Sat Dec 29, 2007 11:22 am

Tks a lot
Could u tell me how to run in the sqlite mode?
derek_lan
Freshman
Freshman
 
Posts: 8
Joined: Tue Dec 25, 2007 10:31 am

Postby plusminus » Thu Jan 03, 2008 11:54 am

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


Didn't understand your question :(
Please rephrase.

Regards,
plusminus
Image
Image | Android Development Community / Tutorials
User avatar
plusminus
Site Admin
Site Admin
 
Posts: 2688
Joined: Wed Nov 14, 2007 8:37 pm
Location: Schriesheim, Germany

Top

Postby steve gerard » Wed Jan 09, 2008 12:14 pm

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
steve gerard
Freshman
Freshman
 
Posts: 2
Joined: Wed Dec 26, 2007 5:59 am
Location: India

Postby plusminus » Wed Jan 09, 2008 5:57 pm

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:
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. public class MyStartupIntentReceiver extends IntentReceiver {
  2.  
  3.         @Override
  4.  
  5.         public void onReceiveIntent(Context context, Intent intent) {
  6.  
  7.                 context.createDatabase(arg0, arg1, arg2, arg3);
  8.  
  9.                 // ...
  10.  
  11.         }
  12.  
  13. }
Parsed in 0.037 seconds, using GeSHi 1.0.8.4


Whole IntentReceiver-Example :src: here.

Regards,
plusminus
Image
Image | Android Development Community / Tutorials
User avatar
plusminus
Site Admin
Site Admin
 
Posts: 2688
Joined: Wed Nov 14, 2007 8:37 pm
Location: Schriesheim, Germany

Postby ramgraph1 » Wed Jan 09, 2008 10:22 pm

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.
ramgraph1
Experienced Developer
Experienced Developer
 
Posts: 68
Joined: Wed Jan 09, 2008 10:03 pm

Postby plusminus » Fri Jan 11, 2008 7:21 pm

Hello ramgraph,

This could should do what you requested:
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. okButton.setOnClickListener(new OnClickListener(){
  2.  
  3.                EditText et = (EditText)findViewById(R.id.myedittext);
  4.  
  5.                String replacewith = keyWordEditText.getText().toString();
  6.  
  7.  
  8.  
  9.                myDB.execSQL("UPDATE "
  10.  
  11.                                    + MY_DATABASE_TABLE
  12.  
  13.                                    + " SET country='" + replacewith + "'"
  14.  
  15.                                    + " WHERE country='Germany'");
  16.  
  17. }
Parsed in 0.038 seconds, using GeSHi 1.0.8.4


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

Regards,
plusminus
Image
Image | Android Development Community / Tutorials
User avatar
plusminus
Site Admin
Site Admin
 
Posts: 2688
Joined: Wed Nov 14, 2007 8:37 pm
Location: Schriesheim, Germany

Postby ramgraph1 » Fri Jan 11, 2008 11:50 pm

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.
ramgraph1
Experienced Developer
Experienced Developer
 
Posts: 68
Joined: Wed Jan 09, 2008 10:03 pm

Postby Ghalya » Tue Feb 12, 2008 1:40 pm

clear as always .... Keep it up :)
Ghalya
Freshman
Freshman
 
Posts: 9
Joined: Sat Feb 09, 2008 4:07 pm
Location: Dubai, UAE

Postby szeldon » Fri Feb 15, 2008 11:32 am

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
szeldon
Freshman
Freshman
 
Posts: 5
Joined: Thu Feb 14, 2008 7:53 pm

Postby plusminus » Fri Feb 15, 2008 11:54 am

Hello szeldon,

this has changed from SDK update of m3 to m5 :!:
I like the queries much more now :)
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
Image
Image | Android Development Community / Tutorials
User avatar
plusminus
Site Admin
Site Admin
 
Posts: 2688
Joined: Wed Nov 14, 2007 8:37 pm
Location: Schriesheim, Germany

Postby szeldon » Fri Feb 15, 2008 12:04 pm

plusminus wrote:Hello szeldon,

this has changed from SDK update of m3 to m5 :!:
I like the queries much more now :)
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 :) Thanks for clarifying.
szeldon.com
szeldon
Freshman
Freshman
 
Posts: 5
Joined: Thu Feb 14, 2008 7:53 pm

Top
Next

Return to Novice Tutorials

Who is online

Users browsing this forum: No registered users and 8 guests