In this tutorial we are going to cover some advanced database code as well as tie in to some more advanced GUI techniques. We left off on the last tutorial showing you how to insert and select data to/from the database as well as make a table. What we need now is to be able to delete data if it is not needed and update it if we entered it incorrectly. We will tie these abilities in with some more advanced functionality utilizing a long press on the screen for delete and for updating we will just press the data we want to edit.
The project we are about to dive into is from the Android Developers website called Notepad v2 with modifications to make it geared more towards our RandomQuotes Project. We are using an already made example then modifying it because it covers more advanced ground on the GUI and database sides which is excellent for beginners and great for more advanced users to push on with. Since the items will be displayed to us in a ListView we can no longer entitle this project RandomQuote but will instead use EnhancedQuotes as our project name. Just to be clear, we will be creating a whole new project instead of copying another one over. Here is the required information below to make the project
Project Name: EnhancedQuotes
Build Target: Android 1.5
Application Name: EnhancedQuotes
Package Name: com.gregjacobs.enhancedquotes
Create Activity: QuotesMain
Min SDK Version: 3
After your project is created we can start some more advanced GUI work and integrate that with some update and delete statements. At this point, I’d like to start dividing our code into different files based on the need of the application. This is important in modern programming because it allows us to stay organized and execute functions for different screens or layouts efficiently and effectively. For this project we are going to split our code into 3 .java files and we are going to have 3 different layout files as well. We will start off with the basics by creating a new class file in our package com.gregjacobs.enhancedquotes called QuotesDBAdapter. This will contain our database code but instead of using the previous database file we created, we will start a new one. Lets look at how Google does it and see whats available other than Raw Queries from the previous tutorial.
package com.gregjacobs.enhancedquotes;import java.util.Random;import android.content.ContentValues;import android.content.Context;import android.database.Cursor;import android.database.SQLException;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;import android.util.Log;public class QuotesDBAdapter { static Random random = new Random(); public static final String KEY_QUOTES = "quotes"; public static final String KEY_ROWID = "_id"; private static final String TAG = "QuotesDbAdapter"; private DatabaseHelper mDbHelper; private SQLiteDatabase mDb; /** * Database creation sql statement */ private static final String DATABASE_CREATE = "create table tblRandomQuotes (_id integer primary key autoincrement, " + "quotes text not null);"; private static final String DATABASE_NAME = "Random"; private static final String DATABASE_TABLE = "tblRandomQuotes"; private static final int DATABASE_VERSION = 2; private final Context mCtx; private static class DatabaseHelper extends SQLiteOpenHelper { DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(DATABASE_CREATE); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data"); db.execSQL("DROP TABLE IF EXISTS tblRandomQuotes"); onCreate(db); } } /** * Constructor - takes the context to allow the database to be * opened/created * * @param ctx the Context within which to work */ public QuotesDBAdapter(Context ctx) { this.mCtx = ctx; } public QuotesDBAdapter open() throws SQLException { mDbHelper = new DatabaseHelper(mCtx); mDb = mDbHelper.getWritableDatabase(); return this; } public void close() { mDbHelper.close(); }Looking at the code above, all of the imports should look familiar as well as everything leading up to this point. This is standard database code to implement in your Android applications. In the code below we start getting into separating our SQL statements into sections and using the functions that were stated in the previous post.
public long createQuote(String quotes) { ContentValues initialValues = new ContentValues(); initialValues.put(KEY_QUOTES, quotes); return mDb.insert(DATABASE_TABLE, null, initialValues); }Looking at the insert statement the first variable would be the database table we are inserting into, the next variable is if we have a null set of values we would enter that here and the last is the values being inserted into the table.
public boolean deleteQuote(long rowId) { return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0; }The delete statement holds three values in its method. The first variable to enter would be the database table, the second being the where statement if there was one. In this case we will need it but for some instances you may not. The last variable is the Where statement arguments but if you included them in the previous part, that will work too. It is good to note that putting “?” in your where statement and defining them in the third variable can be done as well.
public Cursor fetchAllQuotes() { return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_QUOTES}, null, null, null, null, null); } public Cursor fetchQuote(long rowId) throws SQLException { Cursor mCursor = mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID, KEY_QUOTES}, KEY_ROWID + "=" + rowId, null, null, null, null, null); if (mCursor != null) { mCursor.moveToFirst(); } return mCursor; } FetchAllQuotes runs a query against the database and grabs the id and the quotes field and return all the results to a cursor. The first variable is the database table, the second is the columns the statement should return, third being the rows from the columns to return if there are any, fourth being the selection arguments, fifth is the group by SQL function, sixth is a having SQL statement, and seventh is the order by SQL function. For this we are only filling the first two and the rest can be null. The fetchQuote uses the same function but specifies what row its looking for.
public boolean updateQuote(long rowId, String title) { ContentValues args = new ContentValues(); args.put(KEY_QUOTES, title); return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0; } For the update statement we still need the database name, the new variables for any given row and finally the row number in which to update.
public int getAllEntries() { Cursor cursor = mDb.rawQuery( "SELECT COUNT(quotes) FROM tblRandomQuotes", null); if(cursor.moveToFirst()) { return cursor.getInt(0); } return cursor.getInt(0); } public String getRandomEntry() { int id = 1; id = getAllEntries(); int rand = random.nextInt(id) + 1; Cursor cursor = mDb.rawQuery( "SELECT quotes FROM tblRandomQuotes WHERE _id = " + rand, null); if(cursor.moveToFirst()) { return cursor.getString(0); } return cursor.getString(0); }}These two functions above were mentioned last post and will be used to generate a random quote on the screen using a Toast.
Next we are going to cover all of the .xml files starting with the strings.xml file. this will contain the strings for all three of our layout XML files. The code should be pretty straight forward with already having done two or three examples. The strings.xml is as follows:
Quotes Tracker No Quotes Yet Add Quote Delete Quote Quote: Confirm Edit Quote Generate Random Quote!After the strings.xml file we are going to move onto row.xml in the layout folder. It is not created yet so we are going to create a new XML file. We do this by right clicking on the layout folder and navigating to New and then to Other…. After this we will scroll down until we find the XML folder. Open it and double click on the file called XML. Change the name of the XML file from NewFile.xml to row.xml. The file will be created and the console may come up and present you with an error but we will fix that in a second. Now we get to the code we are going to insert into the XML file:
The source code for this layout is a label or TextView that will insert multiple times into the main.xml for every entry we have. We will move onto the main.xml to show you how this is done.
We are using a LinearLayout above and a ListView and a single Label that displays “No Quotes!” if the database is empty. Even though the items in the database are shown we will want to generate one randomly and that is what the button is doing at the bottom of the ListView. We can now move onto the edit.xml here which a new XML file (same spot as last time) will need to be created:
Above we have one linear layout after another and that is for a very specific reason. To be able to present a neat and clean layout we must use the first linear layout to align everything vertically and fill the parent window. After that, the second linear layout will align the textbox and label horizontally. If the two linear layouts were not present the textbox would be the size of the current screen instead of the neat one line layout we have now. Other than that, the layout is pretty is basic and there should be no trouble here.
Next we are going to create a new .java file in our package com.gregjacobs.enhancedquotes called QuoteEdit and it will contain code to accept any edits we may do on our items. Below is the code and comments on the important stuff you may not know, although it should look pretty familiar because we have used almost all of these functions and methods in previous posts. Here is the code for QuoteEdit.java:
package com.gregjacobs.enhancedquotes;import android.app.Activity;import android.content.Intent;import android.os.Bundle;import android.view.View;import android.widget.Button;import android.widget.EditText;public class QuoteEdit extends Activity { private EditText mQuoteText; private Long mRowId; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.edit); mQuoteText = (EditText) findViewById(R.id.title); Button confirmButton = (Button) findViewById(R.id.confirm); mRowId = null; Bundle extras = getIntent().getExtras(); if (extras != null) { String title = extras.getString(QuotesDBAdapter.KEY_QUOTES); mRowId = extras.getLong(QuotesDBAdapter.KEY_ROWID); if (title != null) { mQuoteText.setText(title); } } All above is pretty standard until you get to the Bundle extras = getIntent().getExtras(); part of the code. This code is pulling from the QuotesMain.java using an Intent. Now some beginners may be wondering what an Intent is. An Intent is a passive object to hold data that can pass between applications. In human-speak, its the glue that allows us to get information from the QuotesMain.java file to the QuotesEdit.java file efficiently and easily. Another new term would be a Bundle. A bundle allows use to map strings to objects such as the Intent we just talked about. So with the Bundle entitled extras, we are able to pull the data from the main .java file over to QuotesEdit.java file and vice versa.
confirmButton.setOnClickListener(new View.OnClickListener() { public void onClick(View view) { Bundle bundle = new Bundle(); bundle.putString(QuotesDBAdapter.KEY_QUOTES, mQuoteText.getText().toString()); if (mRowId != null) { bundle.putLong(QuotesDBAdapter.KEY_ROWID, mRowId); } Intent mIntent = new Intent(); mIntent.putExtras(bundle); setResult(RESULT_OK, mIntent); finish(); } }); }}
The Bundle above will package the current text in the textbox with the original ID of the object and send it back over the QuotesEdit.java to the*QuotesMain.java. We are now ready to move onto QuotesMain.java where we are going to pull everything we have done so far together. This code will implement the long press on items as well as utilizing the menu button on any phone to bring up an add function. Here is the code to utilize in QuotesMain.java:
http://hackadaycom.files.wordpress.com/2010/08/quotesmain3.png
package com.gregjacobs.enhancedquotes;import android.app.ListActivity;import android.view.View.OnClickListener;import android.content.Context;import android.content.Intent;import android.database.Cursor;import android.os.Bundle;import android.view.ContextMenu;import android.view.Menu;import android.view.MenuItem;import android.view.View;import android.view.ContextMenu.ContextMenuInfo;import android.widget.ListView;import android.widget.Button;import android.widget.SimpleCursorAdapter;import android.widget.Toast;import android.widget.AdapterView.AdapterContextMenuInfo;Above we have a few new imports to be able to use the more advanced items in this project such as intents, menu’s and menuitem, listview and simplecursoradapters. These will all be explained as they come up.public class QuotesMain extends ListActivity { private static final int ACTIVITY_CREATE=0; private static final int ACTIVITY_EDIT=1; private static final int INSERT_ID = Menu.FIRST; private static final int DELETE_ID = Menu.FIRST + 1; private QuotesDBAdapter mDbHelper; private Cursor mNotesCursor; public Button button; /** Called when the activity is first created. */ @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.main); mDbHelper = new QuotesDBAdapter(this); mDbHelper.open(); fillData(); registerForContextMenu(getListView()); button = (Button)findViewById(R.id.genRan); button.setOnClickListener(mAddListener); } We are making variables for creating, editing, inserting and deleting and making them static because they are not going to change. In the onCreate function we utilize fillData() which will be defined below. As well you will notice that we register the listview items in the context menu and set a listener for the button. A context menu is best described as kind of a pop-up menu and this will be utilized when we want to delete a item within the listview.
private OnClickListener mAddListener = new OnClickListener() { public void onClick(View v) { //long id1 = 0; // do something when the button is clicked try { String quote = ""; quote = mDbHelper.getRandomEntry(); Context context = getApplicationContext(); CharSequence text = quote; int duration = Toast.LENGTH_LONG; Toast toast = Toast.makeText(context, text, duration); toast.show(); } catch (Exception ex) { Context context = getApplicationContext(); CharSequence text = ex.toString(); int duration = Toast.LENGTH_LONG; Toast toast = Toast.makeText(context, text, duration); toast.show(); } } }; private void fillData() { // Get all of the rows from the database and create the item list mNotesCursor = mDbHelper.fetchAllQuotes(); startManagingCursor(mNotesCursor); // Create an array to specify the fields we want to display in the list (only TITLE) String[] from = new String[]{QuotesDBAdapter.KEY_QUOTES}; // and an array of the fields we want to bind those fields to (in this case just text1) int[] to = new int[]{R.id.text1}; // Now create a simple cursor adapter and set it to display SimpleCursorAdapter notes = new SimpleCursorAdapter(this, R.layout.row, mNotesCursor, from, to); setListAdapter(notes); } The button function above is exactly like the previous one that is used to generate a random quote from our list. The new method fillData() as mentioned above is going to be used to get all of the quotes and bind the ID and the actual quote together and add them to the listview using the SimpleCursorAdapter. The SimpleCursorAdapter is used to bind bind columns in a returned cursor to any text we place on the screen.
@Override public boolean onCreateOptionsMenu(Menu menu) { super.onCreateOptionsMenu(menu); menu.add(0, INSERT_ID,0, R.string.menu_insert); return true; } @Override public boolean onMenuItemSelected(int featureId, MenuItem item) { switch(item.getItemId()) { case INSERT_ID: createNote(); return true; } return super.onMenuItemSelected(featureId, item); }In the first function above called onCreateOptionsMenu() we are adding the ability to add an item to the database using the menu press option that will bring up dialog asking if we would like to do this. If this completes successfully then the statement will return true. The one below it checks to see if an item has been pressed in the menu. If it has it uses a switch statement to check the value that we defined above. If it matches then we create a note which is defined below.
@Override public void onCreateContextMenu(ContextMenu menu, View v, ContextMenuInfo menuInfo) { super.onCreateContextMenu(menu, v, menuInfo); menu.add(0, DELETE_ID, 0, R.string.menu_delete); } @Override public boolean onContextItemSelected(MenuItem item) { switch(item.getItemId()) { case DELETE_ID: AdapterContextMenuInfo info = (AdapterContextMenuInfo) item.getMenuInfo(); mDbHelper.deleteQuote(info.id); fillData(); return true; } return super.onContextItemSelected(item); } private void createNote() { Intent i = new Intent(this, QuoteEdit.class); startActivityForResult(i, ACTIVITY_CREATE); }The function above is used to register the context menu and give the option to delete items using the menu.add function as seen above as well as here. If the context menu item Delete is pressed then the database helper will delete the quote based on the ID. The createNote() function uses an intent to pass the application over to the QuoteEdit file and load a new screen and when done a new intent will send the completed data back over here so we can add it to the listview.
@Override protected void onListItemClick(ListView l, View v, int position, long id) { super.onListItemClick(l, v, position, id); Cursor c = mNotesCursor; c.moveToPosition(position); Intent i = new Intent(this, QuoteEdit.class); i.putExtra(QuotesDBAdapter.KEY_ROWID, id); i.putExtra(QuotesDBAdapter.KEY_QUOTES, c.getString( c.getColumnIndexOrThrow(QuotesDBAdapter.KEY_QUOTES))); startActivityForResult(i, ACTIVITY_EDIT); }If an item from the listview is pressed the function above is loaded to initialize an intent and put the information into the intent and pull it over to the QuoteEdit class to be edited. When completed the QuoteEdit class will send the completed data back over and we can continue to add, edit or delete more items.
@Override protected void onActivityResult(int requestCode, int resultCode, Intent intent) { super.onActivityResult(requestCode, resultCode, intent); Bundle extras = intent.getExtras(); switch(requestCode) { case ACTIVITY_CREATE: String title = extras.getString(QuotesDBAdapter.KEY_QUOTES); mDbHelper.createQuote(title); fillData(); break; case ACTIVITY_EDIT: Long rowId = extras.getLong(QuotesDBAdapter.KEY_ROWID); if (rowId != null) { String editTitle = extras.getString(QuotesDBAdapter.KEY_QUOTES); mDbHelper.updateQuote(rowId, editTitle); } fillData(); break; } }}The method above takes the result of an activity and uses the result to utilize a specific method. The result in this case would either be creating a new quote or editing an existing one. The basis of this switch statement is to utilize the database helper and either insert data or update data within the database.
We now have one more file to go over before we could run our application on the emulator. This would be the AndroidManifest.XML file and that will control what is registered and what runs, it is essentially the heart of the program and we need it to recognize that we have 2 parts to our application. Here is the code for the AndroidManifest:
If your code doesnt look like this then you will have to do some modifications to your to talor it to this. You will notice that we have added another to the manifest an are giving it the same name as the one above, app_name. This will denote that we have another activity that we would like to use and we register it here. Also, you will notice that the has been taken out. this is because an application does not really need it but it is always good to have. This just uses it as a reference and will use the specified build target when building your application.
The application should build and you will be able to try out the more advanced features of Android programming. The possibilities are endless with the knowledge you learn but what if your database/database code is not working?
Thats what the Dalvick Debug Monitor Server (DDMS) is for. When the emulator is running we are able to switch over to the DDMS by going to the top right of your screen and pressing the >> and then clicking on DDMS. If you are new to Android Development this new screen will be very confusing for you. What we are going to take out of going to the DDMS for right now is the ability to add and take from our emulator items which may be of interest. For this particular tutorial we are going to grab a database from the running emulator.
Before we get started we will need to download some software I find very useful for SQLite developers. This being SQLite Database Browser (SDB). This software will allow you to open SQLite databases and explore the contents, even modifying them through SQL statements. Once the file is downloaded find the folder and click on the .exe to start it up. Leave this program up and we will get back to it later.
To be able to put them into the SDB we need to pull them off the emulator. To do this we have to delve into the running emulator and find the database we want. It is key to remember that databases are application specific so we will need to find the package name and the database will be under a Database Folder. When in DDMS goto the devices tab and click on our emulator. then in the middle of the program should be a tab called File Explorer. Once File Explorer has been clicked we will now see three folders (maybe more depending on what you do with the device) called data,sdcard and system. We will leave system and sdcard alone for right now as we are going to use the data folder so open it. Once open, navigate to another folder called data and open it too. We are now presented with the package names with everything installed on our emulator. Navigate to com.gregjacobs.enhancedquotes and open it. Once open the two folders that appear should be databases and lib. Open databases folder and take the file called Random. Now to be able to take this file we are going to click on it once then navigate to the top of the tab and press the button that looks like a floppy disc with an arrow pointing to the left. Once this icon is clicked a dialog box will appear asking where you want to save the selected file. Choose an easy to locate place and click save.
One the file has been taken from the emulator we are going to go back to SDB and click the big open button, find our file we saved and click open. Once the file is open we are able to see the structure of the database and navigate to browse the data. to do this we are going to click on the tab called Browse Data and in the dropdown that says table beside it, we are going to choose tblRandomQuotes. The data in the table will now appear and now you know where to find your data if you ever need to modify something an put it back onto the emulator. The SDB is also good for testing out SQL queries if you are unsure of what the data returned would be. This will be an invaluable tool if you do database applications in Android.
Here are the files from my project for comparison:
AndroidManifest.xml | edit.xml | main.xml | QuoteEdit.java | QuoteMain.java | QuotesDBAdapter.java | row.xml | strings.xml
Now that you have an advanced understanding of some of the GUI options available to you and Database code covered in more detail in this tutorial, you are ready to start making some applications that have a little more depth than just button clicks. With the understanding you have of Intents and Bundles, you can make your programs well rounded and divide your code and layouts to match what your looking to make. If anyone has an idea that they have implemented since following this tutorial feel free to send them to me us so we can check out what you have learned. The next tutorial will cover designing the statistics tracker and using DroidDraw to develop some of the UI. Until the next tutorial, Happy Hacking!
Articles Used For Reference:
Google Notepad Tutorial – NotepadV2
