• (2 Pages)
  • +
  • 1
  • 2

Android, Part IV: Databases and Menus

#1 gabehabe  Icon User is offline

  • GabehabeSwamp
  • member icon




Reputation: 1377
  • View blog
  • Posts: 10,951
  • Joined: 06-February 08

Posted 08 October 2009 - 12:01 PM

*
POPULAR

Databases and Menus
Two for the price of one in this tutorial, actually. We're going to learn about SQLite databases and menus. I'm hoping you'll at least be slightly familiar with databases already, though SQLite is really quite simple. You can pick up the basics, of which we'll be using in this tutorial, at the following links: It may look a little intimidating, but once you get to grips with it, it's actually quite close to English. :)

We won't be creating the most sexy application, but databases are an important aspect of a lot of software design, including android.
Attached Image

Let's get to it. As usual, we're creating a package:
package dreamincode.tutorials.dic_tut4;

Attached Image
...and we'll import the usual suspects.
import android.app.Activity;
import android.os.Bundle;

Now there's quite a lot that we're going to import that hasn't already been covered, but do not panic. I'll explain it all as we go along, and it's honestly nothing to worry about. First up, a ListView. This is just another widget, and it's... well, it's a list. :)
import android.widget.ListView;

Next, we'll import the stuff related to the menu. We'll be using a Menu, and a couple of MenuItems.
import android.view.Menu;
import android.view.MenuItem;

The next bits are related to the database. The first is self-explanatory, it's a database. The second is a Cursor. A cursor is a way we can store temporary results from a table in memory. (We select stuff into it, explained a little later... keep reading!)
import android.database.sqlite.SQLiteDatabase;
import android.database.Cursor;

And finally, we're going to import an ArrayList to add our results into, and an ArrayAdapter to basically plug our ArrayList into the list. Again, this will be explained later, at this point all we really have is an overview.
import android.widget.ArrayAdapter;
import java.util.ArrayList;


On to the main part of the code, now. :) First off, we'll open up our class and add a few variables. We'll have a ListView, an SQLiteDatabase, and a few ints for the IDs of our menu items.
public class dic_tut4 extends Activity {
	ListView list;
	SQLiteDatabase db;
	
	private static final int MENU_ADD = Menu.FIRST;
	private static final int MENU_QUIT = Menu.FIRST + 1;
You'll see why we use these a little later. :)

This is where it gets a little different. I'll give you one method at a time, and talk through each line.
	/** Called when the activity is first created. */
	@Override
	public void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		
		this.list = new ListView(this);
		setContentView(this.list);
		
		this.db = this.openOrCreateDatabase("dic_tut4", MODE_PRIVATE, null);
		this.db.execSQL("CREATE TABLE IF NOT EXISTS table_of_dics(value REAL)");
		this.update_list();
	}

So, the start is nothing new. Nothing to worry about here. Then we initialise the list. Nothing new here, either. It's exactly the same as when we created a dynamic layout in part 3. Then we do some new things with our database. First we use openOrCreateDatabase("dic_tut4", MODE_PRIVATE, null);
-- this creates a database if it doesn't exist, or opens it if it does.
-- parameter 1 is the name of the database we're looking to create/open.
-- parameter 2 is the mode, doesn't require much explanation at this point. (Don't want to give you an information overload) :)
-- parameter 3, you don't even need to worry about. If you're interested, do some digging on CursorFactory.

Then another new line: this.db.execSQL("CREATE TABLE IF NOT EXISTS table_of_dics(value REAL)");
-- this will create a table if it doesn't already exist
-- it will add a single field into the table, called value. It is of type REAL, which is just like a double in Java.

And finally, this.update_list(); ... this is actually a custom method we'll be writing to query the database and populate the list. More info later, for now let's focus on the menu.

Creating a menu
Simple stuff actually. We use onCreateOptionsMenu(), which is called when the user hits the menu button.
	@Override 
	public boolean onCreateOptionsMenu(Menu menu) {
		menu.add(0, MENU_ADD, 0, "Add").setIcon(android.R.drawable.ic_menu_add);
		menu.add(0, MENU_QUIT, 0, "Quit").setIcon(android.R.drawable.ic_menu_close_clear_cancel);
		return super.onCreateOptionsMenu(menu);
	}

We're simply adding 2 items: "Add", and "Quit". We also set the icons for them using some default android resources. If you want a full list, this site is pretty cool.

The next method we'll be using is onOptionsItemSelected(), which is called when the user selects an item in the menu. We use the IDs we assigned in onCreateOptionsMenu() MENU_ADD and MENU_QUIT to decide what to do.
	@Override
	public boolean onOptionsItemSelected(MenuItem item) {
		switch(item.getItemId()) {
			case MENU_ADD:
				db.execSQL("INSERT INTO table_of_dics(value) VALUES(" + String.valueOf(Math.random()) + ")");
				this.update_list();
				break;
			case MENU_QUIT:
				this.db.close();
				this.finish();
		}
		
		return super.onOptionsItemSelected(item);
	}

So, we're using a switch() to find the ID of the menu item which called this method. If it's add, we're going to perform some SQL and insert a random value into the table. Then we'll update the list, just like we did in onCreate(). (Remember, we're defining this method ourselves later)

If the user selected the "Quit" option, we'll simple close the database and finish the activity. :)

NOTE: The SQL used here, INSERT INTO will insert a random value into the table. Remember to refer to the links at the beginning if you get stuck on the SQL.

Lastly, we're going to define our method for updating the list. It's quite simple, but the code is quite different to what we've done so far. If you've ever done any database work through software/web before, you may notice a few similarities here. We basically execute the query. Then loop through each row in the result, adding it into an ArrayList. Lastly, we set the adapter for the list to display the arraylist we created. The code looks a tad complex, but it's really quite simple:
	private void update_list() {
		ArrayList<String> db_results = new ArrayList<String>();
		Cursor cursor = db.rawQuery("SELECT value FROM table_of_dics ORDER BY value", null);
		while(cursor.moveToNext()) {
			db_results.add(String.valueOf(cursor.getDouble(cursor.getColumnIndex("value"))));
		}
		cursor.close();
			
		this.list.setAdapter(new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, db_results));
	}

I'll break this one up:
ArrayList<String> db_results = new ArrayList<String>(); -- create an ArrayList, where we'll store the results to display in the list.

Cursor cursor = db.rawQuery("SELECT value FROM table_of_dics ORDER BY value", null); -- create a cursor based on the query (this select statement will return all the values stored in the table, and order them)

while(cursor.moveToNext()) { -- while there is still something in the cursor to read (while we still have another row to read)

db_results.add(String.valueOf(cursor.getDouble(cursor.getColumnIndex("value")))); -- add the value from the row into the arraylist we created.

} -- this is self explanatory, really. :)

cursor.close(); -- close the cursor: we're done with it now. We have all our results in the ArrayList, which we can now set as the adapter:

this.list.setAdapter(new ArrayAdapter<String>(this, android.R.layout.simple_expandable_list_item_1, db_results)); -- set the adapter.
- parameter 1: the activity we want it for
- parameter 2: the layout type (we can add multiple widgets to a list item, but simple_list_item_1 is a default in android: simply a single TextView.
- parameter 3: the arraylist which we created and populated earlier.

And that's it! Don't forget to close off your class
}


Complete code:
package dreamincode.tutorials.dic_tut4;

import android.app.Activity;
import android.os.Bundle;
import android.widget.ListView;
import android.view.Menu;
import android.view.MenuItem;
import android.database.sqlite.SQLiteDatabase;
import android.database.Cursor;
import android.widget.ArrayAdapter;
import java.util.ArrayList;

public class dic_tut4 extends Activity {
	ListView list;
	SQLiteDatabase db;
	
	private static final int MENU_ADD = Menu.FIRST;
	private static final int MENU_QUIT = Menu.FIRST + 1;
	
	/** Called when the activity is first created. */
	@Override
	public void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		
		this.list = new ListView(this);
		this.registerForContextMenu(this.list);
		
		this.db = this.openOrCreateDatabase("dic_tut5", MODE_PRIVATE, null);
		this.db.execSQL("CREATE TABLE IF NOT EXISTS table_of_dics(value REAL)");
		this.update_list();
		
		setContentView(this.list);
	}
	
	@Override 
	public boolean onCreateOptionsMenu(Menu menu) {
		menu.add(0, MENU_ADD, 0, "Add").setIcon(android.R.drawable.ic_menu_add);
		menu.add(0, MENU_QUIT, 0, "Quit").setIcon(android.R.drawable.ic_menu_close_clear_cancel);
		return super.onCreateOptionsMenu(menu);
	}
	
	@Override
	public boolean onOptionsItemSelected(MenuItem item) {
		switch(item.getItemId()) {
			case MENU_ADD:
				db.execSQL("INSERT INTO table_of_dics(value) VALUES(" + String.valueOf(Math.random()) + ")");
				this.update_list();
				break;
			case MENU_QUIT:
				this.db.close();
				this.finish();
		}
		
		return super.onOptionsItemSelected(item);
	}
	
	private void update_list() {
		ArrayList<String> db_results = new ArrayList<String>();
		Cursor cursor = db.rawQuery("SELECT value FROM table_of_dics ORDER BY value", null);
		while(cursor.moveToNext()) {
			db_results.add(String.valueOf(cursor.getDouble(cursor.getColumnIndex("value"))));
		}
		cursor.close();
			
		this.list.setAdapter(new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, db_results));
	}
}


Complete project:
Attached File  dic_tut4.zip (28.67K)
Number of downloads: 1874

Enjoy! :)

This post has been edited by gabehabe: 08 October 2009 - 12:10 PM


Is This A Good Question/Topic? 7
  • +

Replies To: Android, Part IV: Databases and Menus

#2 Guest_DimasSup*


Reputation:

Posted 18 March 2010 - 02:38 PM

Big thanks for this tutorials!
Was This Post Helpful? 0

#3 Guest_Marc Ellis*


Reputation:

Posted 02 August 2010 - 12:39 PM

Hi, great tutorials - especially for someone like me whose just dipping their little toe into the world of Android development for the first time.

I tried this one out and am probably missing something here but I can't seem to get this to work :( I'm just left with a blank screen when I run the code.

Also, the code in the step-by-step explain it all section is a little different in the Complete Code section at the end ...which is the one we should use ?

Quote

01 /** Called when the activity is first created. */
02 @Override
03 public void onCreate(Bundle savedInstanceState) {
04 super.onCreate(savedInstanceState);
05
06 this.list = new ListView(this);
07 setContentView(this.list);
08
09 this.db = this.openOrCreateDatabase("dic_tut4", MODE_PRIVATE, null);
10 this.db.execSQL("CREATE TABLE IF NOT EXISTS table_of_dics(value REAL)");
11 this.update_list();
12 }


Quote

20 /** Called when the activity is first created. */
21 @Override
22 public void onCreate(Bundle savedInstanceState) {
23 super.onCreate(savedInstanceState);
24
25 this.list = new ListView(this);
26 this.registerForContextMenu(this.list);
27
28 this.db = this.openOrCreateDatabase("dic_tut5", MODE_PRIVATE, null);
29 this.db.execSQL("CREATE TABLE IF NOT EXISTS table_of_dics(value REAL)");
30 this.update_list();
31
32 setContentView(this.list);
33 }

Was This Post Helpful? 0

#4 Denis1  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 32
  • View blog
  • Posts: 102
  • Joined: 29-July 09

Posted 29 August 2010 - 01:46 PM

View PostMarc Ellis, on 02 August 2010 - 11:39 AM, said:

Hi, great tutorials - especially for someone like me whose just dipping their little toe into the world of Android development for the first time.

I tried this one out and am probably missing something here but I can't seem to get this to work :( I'm just left with a blank screen when I run the code.

Also, the code in the step-by-step explain it all section is a little different in the Complete Code section at the end ...which is the one we should use ?

Quote

01 /** Called when the activity is first created. */
02 @Override
03 public void onCreate(Bundle savedInstanceState) {
04 super.onCreate(savedInstanceState);
05
06 this.list = new ListView(this);
07 setContentView(this.list);
08
09 this.db = this.openOrCreateDatabase("dic_tut4", MODE_PRIVATE, null);
10 this.db.execSQL("CREATE TABLE IF NOT EXISTS table_of_dics(value REAL)");
11 this.update_list();
12 }


Quote

20 /** Called when the activity is first created. */
21 @Override
22 public void onCreate(Bundle savedInstanceState) {
23 super.onCreate(savedInstanceState);
24
25 this.list = new ListView(this);
26 this.registerForContextMenu(this.list);
27
28 this.db = this.openOrCreateDatabase("dic_tut5", MODE_PRIVATE, null);
29 this.db.execSQL("CREATE TABLE IF NOT EXISTS table_of_dics(value REAL)");
30 this.update_list();
31
32 setContentView(this.list);
33 }


its a blank screen because there is no data to display. click menu and add data and you will see it on your screen
Was This Post Helpful? 0
  • +
  • -

#5 Guest_xsylus*


Reputation:

Posted 06 September 2010 - 08:18 PM

Great tutorials! Are there any other resources for learning Android/java coding? Most of the books/tutorials and examples I've tried elsewhere fail due to version issues with either java, android, or eclipse. Every one of the dreamincode tutorials work flawlessly but I'm still a little fuzzy about the java syntax for Android. I created an app with AppInventor and I wanted to develop it more with Eclipse but the AppInventor doesn't let you see the java code so I would have to rewrite the app from scratch and I'm running into a mountain of problems primarily caused by syntax errors. Part of my app require some simple math but I have no idea how to multiply a preset value by a dynamic (user entered) value. Unfortunately my programming experience is limited to VB and C++. I'm a visual and hands-on learner so just reading a bunch of terms and jargon doesn't quite get me there. Thanks for your time and suggestions on where to go to get a better grip on java/android coding and syntax.
Was This Post Helpful? 0

#6 Guest_droidster*


Reputation:

Posted 09 November 2010 - 02:43 PM

Very nice tutorial

How can I make it so that it add text?
I tried, to replace REAL with, string, VAR, and text, but no luck
and off course i made the values into a 'text goes here' string
Was This Post Helpful? 0

#7 jeremejazz  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 22
  • View blog
  • Posts: 48
  • Joined: 23-April 10

Posted 17 November 2010 - 08:52 PM

thank you very much!
Was This Post Helpful? 0
  • +
  • -

#8 jeremejazz  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 22
  • View blog
  • Posts: 48
  • Joined: 23-April 10

Posted 21 November 2010 - 08:57 PM

Thanks a lot. I used your some of your code to connect to mysql database. Very easy to understand :D
Was This Post Helpful? 0
  • +
  • -

#9 Feuer  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 57
  • Joined: 15-June 11

Posted 08 November 2011 - 12:52 PM

Awesome tutorials, gabehabe!

I was wondering how I could get my hands on a table made like in your example with something like this. I am not able to find neither the damn table nor the database that holds it with the file explorer the app gives me :(
Was This Post Helpful? 0
  • +
  • -

#10 snappy46  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 06-February 12

Posted 06 February 2012 - 07:47 AM

Very nice tutorial just the way I like it "simple and clear"

I do have a question though .....

What kind of method can I use to capture the data store in the list when the items is clicked.

Thanks
Was This Post Helpful? 0
  • +
  • -

#11 pingoo  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 11-March 08

Posted 11 April 2012 - 06:38 AM

Thanks for the tutorials. Really great!

I am having a problem with this one though. When I run the program the following message appears in the console:

"The application dic_tut4 (process dreamincode.tutorials.dic_tut4) has stopped unexpectedly. Please try again"

what am I doing wrong?

Thanks!
Was This Post Helpful? 0
  • +
  • -

#12 EndLessMind  Icon User is offline

  • These are the droids you're looking for
  • member icon

Reputation: 193
  • View blog
  • Posts: 1,099
  • Joined: 13-March 09

Posted 11 April 2012 - 07:35 PM

You need to provide us with some more info.
I recommend you to start a new topic about you problem in the Android forum.
This way, more of us Android people will see your post and can help you.
And to spear you some time, post information from the logcat :)
Was This Post Helpful? 0
  • +
  • -

#13 pingoo  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 11-March 08

Posted 12 April 2012 - 01:55 AM

Unfortunately there isn't much else I can say on the issue. All the code is exactly the same as it is in the tutorial but when I run the program in avd it goes directly to giving me the following error message:

"The application dic_tut4 (process dreamincode.tutorials.dic_tut4) has stopped unexpectedly. Please try again"

I don't get any other error messages or hints in Eclipse, just the error in avd when the program is executed.

That's why I am a bit baffled! The only thing that is different to the tutorial is that my AVD is set to 2.2 rather than 1.5, but I can't see that making much of a difference. If I had any more info I'd be glad to share it!
Was This Post Helpful? 0
  • +
  • -

#14 EndLessMind  Icon User is offline

  • These are the droids you're looking for
  • member icon

Reputation: 193
  • View blog
  • Posts: 1,099
  • Joined: 13-March 09

Posted 12 April 2012 - 02:09 AM

The logcat will tell you where the exception is and what fired it.
But yes, i can be because you are using a other android version.
every new version uses a new API level.
the same functions are still there, but maybe accessible vid other methods.
Was This Post Helpful? 0
  • +
  • -

#15 pingoo  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 11-March 08

Posted 12 April 2012 - 04:14 AM

Thanks for the tip EndLessMind. I'll check the logcat and hopefully will find the solution there!

Found the error. I typed 'EXIST' instead of 'EXISTS' on line 29. I should have checked logcat straight away but for some reason I didn't think of it! Doh! Sorry, noob error.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2