Board index
FAQ
Register
Login
Working with the SQLite-Database - Cursors
:warning: Compatible for SDK version m3-xxx or older
:warning: 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
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 !).
Using java Syntax Highlighting
- 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>();
Parsed in 0.031 seconds, using GeSHi 1.0.8.4
1.) So lets create the DataBase:
Using java Syntax Highlighting
- SQLiteDatabase myDB = null;
- try {
- /* Create the Database (no Errors if it already exists) */
- 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:
Using java Syntax Highlighting
- /* Open the DB and remember it */
- myDB = this.openDatabase(MY_DATABASE_NAME, null);
Parsed in 0.034 seconds, using GeSHi 1.0.8.4
3.) Now we create a simple Table with just four columns:
Using java Syntax Highlighting
- /* 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));");
Parsed in 0.035 seconds, using GeSHi 1.0.8.4
4.) Put two DataSets to the recently created Table:
Using java Syntax Highlighting
- /* 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);");
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":
Using java Syntax Highlighting
- /* 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);
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:
Using java Syntax Highlighting
- /* 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 <img src="http://www.anddev.org/images/smilies/wink.png" alt=";)" title="Wink" /> */
- String ageColumName = c.getColumnName(ageColumn);
- /* Add current Entry to results. */
- results.add("" + i + ": " + firstName
- + " (" + ageColumName + ": " + age + ")");
- } while (c.next());
- }
- }
Parsed in 0.039 seconds, using GeSHi 1.0.8.4
7.) Finally close the DataBase (if it has been opened):
Using java Syntax Highlighting
- } catch (FileNotFoundException e) {
- } finally {
- if (myDB != null)
- myDB.close();
- }
Parsed in 0.036 seconds, using GeSHi 1.0.8.4
8.) In the end, display our Entries:
Using java Syntax Highlighting
- this.setListAdapter(new ArrayAdapter<String>(this,
- android.R.layout.simple_list_item_1_small, results));
- }
- }
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
And probably a bit more comfortable
Thats it
Regards,
plusminus
