| Author |
Message |
plusminus Site Admin

Joined: 14 Nov 2007 Posts: 2067 Location: Germany
|
Posted: Wed Dec 19, 2007 1:05 am Post subject: Working with the SQLite-Database - Cursors |
|
|
Working with the SQLite-Database - Cursors
Compatible for SDK version m3-xxx or older 
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
Questions/Problems: Simply post below...
What it will look like:
Description:
We'll need to to the following things:
- Create a DataBase (generally this is done just once)
- Open the DataBase
- Create a Table (generally this is done just once)
- Insert some Datasets
- Query for some Datasets
- 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 */
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
Thats it 
Regards,
plusminus
_________________
| Android Development Community / Tutorials
Last edited by plusminus on Fri Feb 15, 2008 12:15 pm; edited 1 time in total |
|
| Back to top |
|
 |
cybersat Freshman
Joined: 18 Dec 2007 Posts: 5
|
Posted: Tue Dec 25, 2007 4:02 pm Post subject: Plz help |
|
|
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 |
|
 |
plusminus Site Admin

Joined: 14 Nov 2007 Posts: 2067 Location: Germany
|
|
| Back to top |
|
 |
Katharnavas Senior Developer
Joined: 04 Dec 2007 Posts: 100 Location: India
|
Posted: Thu Dec 27, 2007 5:48 am Post subject: |
|
|
Hi,
Nice tutorial and thanks for the step by step instructions of accessing the database thro the command line.
|
|
| Back to top |
|
 |
derek_lan Freshman
Joined: 25 Dec 2007 Posts: 8
|
Posted: Sat Dec 29, 2007 11:22 am Post subject: |
|
|
Tks a lot
Could u tell me how to run in the sqlite mode?
|
|
| Back to top |
|
 |
plusminus Site Admin

Joined: 14 Nov 2007 Posts: 2067 Location: Germany
|
Posted: Thu Jan 03, 2008 11:54 am Post subject: |
|
|
| 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
_________________
| Android Development Community / Tutorials |
|
| Back to top |
|
 |
steve gerard Freshman
Joined: 26 Dec 2007 Posts: 2 Location: India
|
Posted: Wed Jan 09, 2008 12:14 pm Post subject: |
|
|
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 |
|
 |
plusminus Site Admin

Joined: 14 Nov 2007 Posts: 2067 Location: Germany
|
|
| Back to top |
|
 |
ramgraph1 Experienced Developer
Joined: 09 Jan 2008 Posts: 68
|
Posted: Wed Jan 09, 2008 10:22 pm Post subject: |
|
|
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 |
|
 |
plusminus Site Admin

Joined: 14 Nov 2007 Posts: 2067 Location: Germany
|
|
| Back to top |
|
 |
ramgraph1 Experienced Developer
Joined: 09 Jan 2008 Posts: 68
|
Posted: Fri Jan 11, 2008 11:50 pm Post subject: |
|
|
| 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 |
|
 |
Ghalya Freshman
Joined: 09 Feb 2008 Posts: 9 Location: Dubai, UAE
|
Posted: Tue Feb 12, 2008 1:40 pm Post subject: |
|
|
clear as always .... Keep it up
|
|
| Back to top |
|
 |
szeldon Freshman
Joined: 14 Feb 2008 Posts: 3
|
Posted: Fri Feb 15, 2008 11:32 am Post subject: |
|
|
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 |
|
 |
plusminus Site Admin

Joined: 14 Nov 2007 Posts: 2067 Location: Germany
|
Posted: Fri Feb 15, 2008 11:54 am Post subject: |
|
|
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
_________________
| Android Development Community / Tutorials |
|
| Back to top |
|
 |
szeldon Freshman
Joined: 14 Feb 2008 Posts: 3
|
Posted: Fri Feb 15, 2008 12:04 pm Post subject: |
|
|
| 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 |
|
| Back to top |
|
 |
|
|
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.
|