Android Database Tutorial
Creating the Database Manager Class
In the previous section we extended the class that is responsible for creating and updating the database. We also created a SQLiteDatabase object and some database constants. All of this was wrapped in the beginnings of a database manager class. Now we are going to create this manager class. One of it’s main purposes is to communicate with the SQLiteDatabase object. Our class will call methods of the database object, the database object will send queries to the database and return data to our manager class for output to our application.
During the last section, we created the outline for our new class (AABDatabaseManager). We now need to include functionality in our class for retrieving, adding, and updating data to and from the database. I am going to make five different methods: addRow(), deleteRow(), updateRow(), getRow(), and getAllRows(). Let’s go over these methods one at a time.
The addRow() Method
Our addRow() method of the database manager class is going to take two String arguments and ask the database object to add them as a new row in the given database table. The database object has an insert() method that we need to call. The insert() method takes three arguments.>
The first argument is the table name. We will pass the constant TABLE_NAME here.
The next argument requires a nullColumnHack type. This has to do with whether the row can be empty or not. We are going to pass in null as the variable here. In this instance, our database will not allow empty rows.
The final argument for the database’s insert() method requires the type ContentValues. A ContentValues object is a modified java collection that contains key/value pairs. We need to create a ContentValues object and pass it two key/value pairs to satisfy the database requirement (the id column will fill in automatically, that leaves the text_one and text_two columns). Once we have our ContentValues object, we can call the insert() method to add the new data to the database. I will always place database method calls inside of try/catch blocks to pass error messages to the log for debugging. Let’s take a look at this code:
public void addRow(String rowStringOne, String rowStringTwo) { // this is a key value pair holder used by android's SQLite functions ContentValues values = new ContentValues(); // this is how you add a value to a ContentValues object // we are passing in a key string and a value string each time values.put(TABLE_ROW_ONE, rowStringOne); values.put(TABLE_ROW_TWO, rowStringTwo); // ask the database object to insert the new data try { db.insert(TABLE_NAME, null, values); } catch(Exception e) { Log.e("DB ERROR", e.toString()); // prints the error message to the log e.printStackTrace(); // prints the stack trace to the log } }
The deleteRow() Method
The deleteRow() method is a little simpler. It is going to take one argument, long rowID. We use a Long instead of an Integer because integers in SQLite are larger than integers in Java. Collecting data from a SQLite database and storing it in a Java Integer type may result in data loss. Using Long when dealing with SQLite in Java is recommended. Once we pass in our rowID argument, we are going to call the SQLiteDatabase object’s delete() method, passing it three arguments. The first argument, again, is the table name we are deleting a row from – our TABLE_NAME constant. The second argument we pass is the SQLite WHERE clause without the word “where”. We need the “id” to equal the id we want to be deleted. Since this is a String argument, we can build the String inside the method call like this: TABLE_ROW_ID + “=” + rowID. Here is the deleteRow() method:
public void deleteRow(long rowID) { // ask the database manager to delete the row of given id try { db.delete(TABLE_NAME, TABLE_ROW_ID + "=" + rowID, null); } catch (Exception e) { Log.e("DB ERROR", e.toString()); e.printStackTrace(); } }
The updateRow() Method
The updateRow() method requires three arguments: the rowID, rowStringOne, and rowStringTwo. Like in the previous addRow() method, we create our ContentValues object and give it two key/value pairs. Once everything is ready, we send it to the database object inside of a try/catch block.
Here, the SQLite database object has an update() method that takes four arguments. The first argument is the table name. The next argument is the ContentValues object. The third argument is the SQLite WHERE clause. Again we put this string together inside of the method call. The final argument is the WHERE clause arugments. Typically, you could specify a String array of arguments to pass in for the WHERE conditions. For this case there is only one argument (that we’ve already specified) so we will pass in null. Here is the entire method:
public void updateRow(long rowID, String rowStringOne, String rowStringTwo) { // this is a key value pair holder used by android's SQLite functions ContentValues values = new ContentValues(); values.put(TABLE_ROW_ONE, rowStringOne); values.put(TABLE_ROW_TWO, rowStringTwo); // ask the database object to update the database row of given rowID try {db.update(TABLE_NAME, values, TABLE_ROW_ID + "=" + rowID, null);} catch (Exception e) { Log.e("DB Error", e.toString()); e.printStackTrace(); } }
The getRowAsArray() and getAllRowsAsArrays() Methods
These next two methods are going to exemplify the use of Cursor objects. When we retrieve a single row or multiple rows from the SQLiteDatabase object, we recieve back a Cursor. These Cursor objects act as containers and iterators for the rows of data that are returned. I chose to have our methods convert the data inside the Cursor to ArrayLists. It’s probably cleaner to just pass the Cursor out but I want to illistrate how to collect data from these objects. I will explain the getAllRowsAsArrays() method and not the getRowAsArray() method. Once you figure out the getAllRowsAsArrays() method, figuring out the getRowAsArray() should hopefully not be a problem. This is the most complex method so far.
Before we get started, I’d like to briefly cover the casting required for the ArrayList. Casting an ArrayList looks like this: ArrayList<ObjectType>. Inside of the brackets is where you specify what type of objects will be stored inside of the ArrayList. In this example, we have ArrayLists of unspecified objects inside of an ArrayList. If we were to declare an ArrayList of Objects. We would write it: ArrayList<Object>(). Since the type in the ArrayList we are going to use is an ArrayList of “Objects” we place that inside of an ArrayList declaration. Our ArrrayList declaration looks like this:
ArrayList<ArrayList<Object>> dataArrays = new ArrayList<ArrayList<Object>>();
Let’s move on to the getRowsAsArrays() method. The first thing we do is create an ArrayList of ArrayLists of Objects to store the data in the method. I just covered how to do that. The next thing we need to do is create our Cursor object. I declare the Cursor object. Then, when I create it, I do so by calling the database object’s query() method. Remember that the query() method returns a Cursor object. Our query() method takes seven arguments. Fortunately, we only need to worry about the first two to collect data from all rows in the datase. The first argument is the table name to gather data from. The second argument is an array of the names (Strings) of each column we want the query to return. If we want data from two columns, we need two column names. If we want data from five columns, we would need five column names. In our case, we are going to collect data from all three columns in our example table: the id, table_row_one, and table_row_two. We can pass the value null five times for the next five arguments. That gives us all seven arguments for the SQLiteDatabase object’s query() method.
Our getRowAsArray() method passes a third argument to the query() method. This argument, a String, satisifies the WHERE clause by specifying “TABLE_ROW_ID=rowID”. This is similar to the deleteRow() method’s WHERE clause that I covered earlier in this section. So when we write the getRowAsArray() method. We pass in three arguments and and four null arugments to the SQLiteDatabase’s query() method.
Let’s go ahead and take a look at what we have so far in this method. In this code I have expanded the database query() method call into seven lines. I prefer to do this for readability – you may or may not want to do the same.
public ArrayList<Object> getRowAsArray(long rowID) { ArrayList<Object> rowArray = new ArrayList<Object>(); Cursor cursor; try { // this method call is spread out over seven lines as a personal preference cursor = db.query ( TABLE_NAME, new String[] { TABLE_ROW_ID, TABLE_ROW_ONE, TABLE_ROW_TWO }, TABLE_ROW_ID + "=" + rowID, null, null, null, null, null ); // TODO Move the data from the cursor to the arraylist. } catch (SQLException e) { Log.e("DB ERROR", e.toString()); e.printStackTrace(); } return rowArray; }
At this point we have a cursor object that has the data from all of the rows of the database table. The next thing we are going to do is collect this data from the cursor and store it in an ArrayList. Since the cursor object filled itself one item at a time, the cursor’s pointer is at the end of the cursor. You can think of this like a type writer typing out a row of text. After each letter is typed, the pointer (the little arrow showing you where the next letter is going to go) points at the next available space to type a new letter. Our cursor object inserted data into each space and now it’s pointer is at the end of the data that it entered. We want to read the data from the beginning so we need to move the pointer there. We do this with the Cursor’s moveToFirst() function.
Once the pointer is at the front of the data, we can iterate through the cursor and collect the data as we go. Let’s iterate through the cursor with a do/while block. We are going to “do” the data collecting “while” the Cursor’s moveToNext() method returns false. The moveToNext() method simply tries to move the pointer to the next row of data. If there are any available rows ahead, it goes to the “next” one and returns true. If there are no more rows, the method does nothing and returns false.
do { // TODO: put data from current cursor row into an array // TODO: put the new array into the array of arrays } // try to move the cursor's pointer forward one position. while (cursor.moveToNext());
With our pointer now on the first row of data, we need to collect the data from that row from within the do block. The first thing I do is create a new ArrayList of Objects. In order to add the first piece of data I use one of the cursor’s “get” methods. There is a “get” method for each of the cursor’s supported data types. We know the data from the first column of data is the “id” of type Long. This means we need to use the cursor’s getLong() method.
Each of these “get” methods takes a single argument that represents the column of data we want the data from. When we created the cursor object, the second arugment we passed to the query() method was an array of three Strings. The position of each of these column name Strings in the array of Strings corresponds to the needed argument in the Cursor’s get functions. If we want the TABLE_ROW_ID (“id”) column’s data, we look at the array of Strings, see that it is in position zero in that array, and decide that we need to pass “0″ to getLong() to get back the “id” of the first row.
All we have to do to get the “id” into the ArrayList is call the ArrayList’s add() method, passing in cursor.getLong(0) as the argument. cursor.getLong(0) will pass the data into add() which will add the data to the ArrayList. After that we do the same for the next two positions of data using getString() twice – passing in “1″ and “2″ consecutively.
Here is the do while list that will collect the data from the ArrayList:
do { ArrayList<Object> dataList = new ArrayList<Object>(); dataList.add(cursor.getLong(0)); dataList.add(cursor.getString(1)); dataList.add(cursor.getString(2)); dataArrays.add(dataList); } // move the cursor's pointer up one position. while (cursor.moveToNext());
The only other thing that we need to worry about here is the possibilty that there are no rows of data for the cursor to collect. This would leave us with an empty cursor. I went ahead and placed this do/while block inside of an if block so that it only runs “if” !cursor.isAfterLast() returns true. That is, once the cursor’s pointer was moved to the beginning of the cursor, “if” the pointer is not after the last row (ie., the beginning is not the end), then run the do/while code block. Once our do/while code completes, we can return our newly created ArrayList and we are done with writing this method.
Whew! I apologize if this has been long winded. I am trying to make this as short and concise as I can…so time to move on. Let’s take a look at the two methods we just discussed. I went ahead and left some documentation in the code.
/********************************************************************** * RETRIEVING ALL ROWS FROM THE DATABASE TABLE * * This is an example of how to retrieve all data from a database * table using this class. You should edit this method to suit your * needs. * * the key is automatically assigned by the database */ public ArrayList<ArrayList<Object>> getAllRowsAsArrays() { // create an ArrayList that will hold all of the data collected from // the database. ArrayList<ArrayList<Object>> dataArrays = new ArrayList<ArrayList<Object>>(); // this is a database call that creates a "cursor" object. // the cursor object store the information collected from the // database and is used to iterate through the data. Cursor cursor; try { // ask the database object to create the cursor. cursor = db.query( TABLE_NAME, new String[]{TABLE_ROW_ID, TABLE_ROW_ONE, TABLE_ROW_TWO}, null, null, null, null, null ); // move the cursor's pointer to position zero. cursor.moveToFirst(); // if there is data after the current cursor position, add it // to the ArrayList. if (!cursor.isAfterLast()) { do { ArrayList<Object> dataList = new ArrayList<Object>(); dataList.add(cursor.getLong(0)); dataList.add(cursor.getString(1)); dataList.add(cursor.getString(2)); dataArrays.add(dataList); } // move the cursor's pointer up one position. while (cursor.moveToNext()); } } catch (SQLException e) { Log.e("DB Error", e.toString()); e.printStackTrace(); } // return the ArrayList that holds the data collected from // the database. return dataArrays; } /********************************************************************** * RETRIEVING A ROW FROM THE DATABASE TABLE * * This is an example of how to retrieve a row from a database table * using this class. You should edit this method to suit your needs. * * @param rowID the id of the row to retrieve * @return an array containing the data from the row */ public ArrayList<Object> getRowAsArray(long rowID) { // create an array list to store data from the database row. // I would recommend creating a JavaBean compliant object // to store this data instead. That way you can ensure // data types are correct. ArrayList<Object> rowArray = new ArrayList<Object>(); Cursor cursor; try { // this is a database call that creates a "cursor" object. // the cursor object store the information collected from the // database and is used to iterate through the data. cursor = db.query ( TABLE_NAME, new String[] { TABLE_ROW_ID, TABLE_ROW_ONE, TABLE_ROW_TWO }, TABLE_ROW_ID + "=" + rowID, null, null, null, null, null ); // move the pointer to position zero in the cursor. cursor.moveToFirst(); // if there is data available after the cursor's pointer, add // it to the ArrayList that will be returned by the method. if (!cursor.isAfterLast()) { do { rowArray.add(cursor.getLong(0)); rowArray.add(cursor.getString(1)); rowArray.add(cursor.getString(2)); } while (cursor.moveToNext()); } // let java know that you are through with the cursor. cursor.close(); } catch (SQLException e) { Log.e("DB ERROR", e.toString()); e.printStackTrace(); } // return the ArrayList containing the given row from the database. return rowArray; }
Believe it or not, we are now finished with our entire database manager object. It can be easily modified and patched into many small projects. The next page is the entire code. After that, we are going to write a small Activity that uses our new class. If you can handle that part then you are done! Thank you for reading. I hope it was helpful and please leave feedback if you have any. If you would like to see this database manager class in action, read on.
Table of Contents
Comments
Comment from Randall Mitchell
Time August 23, 2010 at 6:53 pm
No Problem. I’ve added it to the last page: here.
Pingback from links for 2010-08-23 | Chris F. Waigl
Time August 24, 2010 at 1:02 am
[...] Another Android Blog » Android Database Tutorial Maybe useful. (tags: android database sql sqlite programming) [...]
Comment from muhammad babar naveed
Time August 26, 2010 at 5:21 pm
Dear Sir,
I start android development week ago . i have a lot of problem in database
your above example solve my all problem and broad my vision in database. I am very thankful to you. I hope GOD give you long life.
Comment from SATYA.N
Time August 31, 2010 at 7:46 am
hi,
Its a great tutorial.
I got a problem in retrieving data from the table.
Actually I have created a table and the table will be updated dynamically for every 13.6 seconds, and I need to get the last row of the table when it was updated.
when I am using the Max(ROWID) its saying misuse of Max() function.
Can you help me in this??
Thank You,
SATYA.N
Comment from Randall Mitchell
Time September 4, 2010 at 7:29 am
Sorry I am just getting back to this. I had notification going to the wrong email address. I’m guessing you’ve already moved on. If not… From what I can tell, you are using a good enough method for getting the last row. There are actually many ways you can accomplish the same thing. Row ID works fine. Using a timestamp would be another effective method.
I can’t say for sure what the problem is without first seeing the code. Your SQLite query should probably look something like this:
SELECT * FROM tableName WHERE rowID = (SELECT MAX(rowID) FROM tableName);
hope that helps!
Comment from Seven
Time September 7, 2010 at 2:37 pm
Great!
What about database with more than one table with foreign keys, etc ?
Comment from Randall Mitchell
Time September 8, 2010 at 4:08 pm
Hey Seven.
Creating multiple tables isn’t much harder, just more work. You should have a set of constants for each new table in your database manager class. In the onCreate() method of the SQLiteOpenHelper, you would create a query string and run a db.execSQL() command for each table. Of course, there will be a need to modify the existing methods or create new ones. On more complex apps, you could design your class like I’ve done my database manager class; but, then you may need to consider refactoring to keep things organized (i.e., make yourself a database manager interface and have db manager classes that implement it – but that’s an entirely different tutorial).
As far as foreign keys. I think that’s a bit much to discuss in a comment to a blog post. You might look at the following link for more information…
http://www.sqlite.org/foreignkeys.html
If you have questions from there. let me know. I don’t have plans at the moment to do a more advanced DB tutorial (or a part two)…maybe in the future sometime.
Comment from Siddharth
Time September 19, 2010 at 12:25 pm
Hello. I have made some changes to your example to accommodate 8 columns in the table. Also, instead of displaying them in the layout you have used for the example, I want to show just one column entries in a ListActivity. Could you help me with the modifications to the code, or at least give me some hints which will get me in the right direction?
Thanks in advance. Love the tutorial. 
Comment from Randall Mitchell
Time September 21, 2010 at 2:05 pm
Hey Siddharth, are you having any luck with your project? I’ve got a tutorial on ListView. The code is here. You might take a look at it. Sorry for the slowness (again). I’m taking four classes this semester and working full time so my times been pretty short lately. Have a look at that code and let me know if you need some additional assistance. Thanks for reading!
Comment from Siddharth
Time September 22, 2010 at 10:53 am
Thanks for the link Randall. Do take your own time with the classes, etc. The more time flies by, the more information I search and find newer things while I am at it. 
I gave a look to the listview tutorial link you provided and it the last part which I think is more relevant to my situation. And unfortunately, that’s the one which is not coded. 
My test application is based on this Database tutorial and if you could provide the code for the listview with this tut in context, it would be mighty helpful. But please don’t go out of way doing it. As and when time permits you to look into this works for me.
Many thanks for your help and this brilliant tutorial. I was fishing in muddy waters before chancing on your website.
Regards.
Comment from Randall Mitchell
Time September 22, 2010 at 5:50 pm
Hey Siddharth,
I’ve got a couple tips for you to hopefully get you pointed in the right direction. ListActivity can be used with a Cursor object. You can create a method inside the database class that returns all of the data from a column inside of a Cursor object. Here is the code I came up with:
public Cursor getColumn(String columnName) { Cursor cursor = null; String[] columns = new String[1]; columns[0] = columnName; try { cursor = db.query(tableName, columns, null, null, null, null, null); } catch (SQLiteException e) { Log.e("Database.addRow", "Database Error: " + e.toString()); e.printStackTrace(); } return cursor; }
Here’s some code you can use inside of the ListActivity:
... public class MyList extends ListActivity { // bring in the database object AABDatabaseMananger db; ... @Override onCreate(Bundle savedInstanceState) { ... db = new AABDatabaseManager(); String columnName = "my_db_column"; // better to use constants here // these arrays are used by the list adapter while connecting the data to the ListView String[] fields = new String[] { columnName }; String[] views = new int[] { android.R.id.text1 } // create our cursor by calling the database method. Cursor cursor = db.getColumn(columnName); // call setListAdapter(), which is a method from the parent class. setListAdapter(new SimpleListAdapter( this, android.R.layout.simple_list_item_1, cursor, fields, views)); ... } ... }
Hopefully this will get you pointed in the right direction. If you need further explanation, let me know. Also, I did not test this out; so, if there are any errors, get back to me.
Good luck.
Comment from Siddharth
Time September 22, 2010 at 7:32 pm
Hey Randall.
Thanks a bunch for the quick reply. I will try it out first thing tomorrow morning. But judging by the tutorial, I think it will work like a charm anyway. Will post results as soon as its done.
Thanks a lot yet again for your patience.
Cheers….
Comment from Siddharth
Time September 23, 2010 at 7:27 pm
Whoops…. That’s, sadly, still a no go. Would it help if I was to give you a code of what I have done so far? Only if you have the time to look into it of course.
Thanks.
Comment from Siddharth
Time September 24, 2010 at 12:09 pm
Hey there. Just in case you get the time, please tell me where I am going wrong with this.
Code for the Adapter:
package com.dzinesunlimited.quotetogo; import java.util.ArrayList; 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.SQLiteException; import android.database.sqlite.SQLiteOpenHelper; import android.util.Log; public class DBAdapter { // the Activity or Application that is creating an object from this class. Context context; // a reference to the database used by this application/object private SQLiteDatabase db; // These constants are specific to the database. They should be // changed to suit your needs. private final String DB_NAME = "quoteonthego"; private final int DB_VERSION = 1; // These constants are specific to the database table. They should be // changed to suit your needs. private final String TABLE_NAME = "projects"; private final String TABLE_ROW_ID = "id"; final static String TABLE_ROW_NAME = "name"; private final String TABLE_ROW_TYPE = "type"; private final String TABLE_ROW_CONTACTS = "contacts"; private final String TABLE_ROW_HOURS = "hours"; private final String TABLE_ROW_CHARGES = "charges"; private final String TABLE_ROW_STARTDATE = "startdate"; private final String TABLE_ROW_ENDDATE = "enddate"; private final String TABLE_ROW_ESTIMATE = "estimate"; public DBAdapter(Context context) { this.context = context; // create or open the database CustomSQLiteOpenHelper helper = new CustomSQLiteOpenHelper(context); this.db = helper.getWritableDatabase(); } /********************************************************************** * ADDING A ROW TO THE DATABASE TABLE * * * the key is automatically assigned by the database * @param rowStringOne the value for the row's first column * @param rowStringTwo the value for the row's second column */ public void addRow( String rowName, String rowType, String rowContacts, String rowHours, String rowCharges, String rowStartDate, String rowEndDate, String rowEstimate) { // this is a key value pair holder used by android's SQLite functions ContentValues values = new ContentValues(); values.put(TABLE_ROW_NAME, rowName); values.put(TABLE_ROW_TYPE, rowType); values.put(TABLE_ROW_CONTACTS, rowContacts); values.put(TABLE_ROW_HOURS, rowHours); values.put(TABLE_ROW_CHARGES, rowCharges); values.put(TABLE_ROW_STARTDATE, rowStartDate); values.put(TABLE_ROW_ENDDATE, rowEndDate); values.put(TABLE_ROW_ESTIMATE, rowEstimate); // ask the database object to insert the new data try{db.insert(TABLE_NAME, null, values);} catch(Exception e) { Log.e("DB ERROR", e.toString()); e.printStackTrace(); } } /********************************************************************** * DELETING A ROW FROM THE DATABASE TABLE * * This is an example of how to delete a row from a database table * using this class. In most cases, this method probably does * not need to be rewritten. * * @param rowID the SQLite database identifier for the row to delete. */ public void deleteRow(long rowID) { // ask the database manager to delete the row of given id try {db.delete(TABLE_NAME, TABLE_ROW_ID + "=" + rowID, null);} catch (Exception e) { Log.e("DB ERROR", e.toString()); e.printStackTrace(); } } /********************************************************************** * UPDATING A ROW IN THE DATABASE TABLE * * @param rowID the SQLite database identifier for the row to update. * @param rowStringOne the new value for the row's first column * @param rowStringTwo the new value for the row's second column */ public void updateRow(long rowID, String rowName, String rowType, String rowContacts, String rowHours, String rowCharges, String rowStartDate, String rowEndDate, String rowEstimate) { // this is a key value pair holder used by android's SQLite functions ContentValues values = new ContentValues(); values.put(TABLE_ROW_NAME, rowName); values.put(TABLE_ROW_TYPE, rowType); values.put(TABLE_ROW_CONTACTS, rowContacts); values.put(TABLE_ROW_HOURS, rowHours); values.put(TABLE_ROW_CHARGES, rowCharges); values.put(TABLE_ROW_STARTDATE, rowStartDate); values.put(TABLE_ROW_ENDDATE, rowEndDate); values.put(TABLE_ROW_ESTIMATE, rowEstimate); // ask the database object to update the database row of given rowID try {db.update(TABLE_NAME, values, TABLE_ROW_ID + "=" + rowID, null);} catch (Exception e) { Log.e("DB Error", e.toString()); e.printStackTrace(); } } /********************************************************************** * RETRIEVING A ROW FROM THE DATABASE TABLE * * This is an example of how to retrieve a row from a database table * using this class. You should edit this method to suit your needs. * * @param rowID the id of the row to retrieve * @return an array containing the data from the row */ public ArrayList getRowAsArray(long rowID) { // create an array list to store data from the database row. // I would recommend creating a JavaBean compliant object // to store this data instead. That way you can ensure // data types are correct. ArrayList rowArray = new ArrayList(); Cursor cursor; try { // this is a database call that creates a "cursor" object. // the cursor object store the information collected from the // database and is used to iterate through the data. cursor = db.query ( TABLE_NAME, new String[] { TABLE_ROW_ID, TABLE_ROW_NAME, TABLE_ROW_TYPE, TABLE_ROW_CONTACTS, TABLE_ROW_HOURS, TABLE_ROW_CHARGES, TABLE_ROW_STARTDATE, TABLE_ROW_ENDDATE, TABLE_ROW_ESTIMATE}, TABLE_ROW_ID + "=" + rowID, null, null, null, null, null ); // move the pointer to position zero in the cursor. cursor.moveToFirst(); // if there is data available after the cursor's pointer, add // it to the ArrayList that will be returned by the method. if (!cursor.isAfterLast()) { do { rowArray.add(cursor.getLong(0)); rowArray.add(cursor.getString(1)); rowArray.add(cursor.getString(2)); rowArray.add(cursor.getString(3)); rowArray.add(cursor.getString(4)); rowArray.add(cursor.getString(5)); rowArray.add(cursor.getString(6)); rowArray.add(cursor.getString(7)); rowArray.add(cursor.getString(8)); } while (cursor.moveToNext()); } // let java know that you are through with the cursor. cursor.close(); } catch (SQLException e) { Log.e("DB ERROR", e.toString()); e.printStackTrace(); } // return the ArrayList containing the given row from the database. return rowArray; } /********************************************************************** * RETRIEVING ALL ROWS FROM THE DATABASE TABLE * * This is an example of how to retrieve all data from a database * table using this class. You should edit this method to suit your * needs. * * the key is automatically assigned by the database */ public ArrayList<ArrayList> getAllRowsAsArrays() { // create an ArrayList that will hold all of the data collected from // the database. ArrayList<ArrayList> dataArrays = new ArrayList<ArrayList>(); // this is a database call that creates a "cursor" object. // the cursor object store the information collected from the // database and is used to iterate through the data. Cursor cursor; try { // ask the database object to create the cursor. cursor = db.query( TABLE_NAME, new String[]{TABLE_ROW_ID, TABLE_ROW_NAME, TABLE_ROW_TYPE, TABLE_ROW_CONTACTS, TABLE_ROW_HOURS, TABLE_ROW_CHARGES, TABLE_ROW_STARTDATE, TABLE_ROW_ENDDATE, TABLE_ROW_ESTIMATE}, null, null, null, null, null ); // move the cursor's pointer to position zero. cursor.moveToFirst(); // if there is data after the current cursor position, add it // to the ArrayList. if (!cursor.isAfterLast()) { do { ArrayList dataList = new ArrayList(); dataList.add(cursor.getLong(0)); dataList.add(cursor.getString(1)); dataList.add(cursor.getString(2)); dataList.add(cursor.getString(3)); dataList.add(cursor.getString(4)); dataList.add(cursor.getString(5)); dataList.add(cursor.getString(6)); dataList.add(cursor.getString(7)); dataList.add(cursor.getString(8)); dataArrays.add(dataList); } // move the cursor's pointer up one position. while (cursor.moveToNext()); } } catch (SQLException e) { Log.e("DB Error", e.toString()); e.printStackTrace(); } // return the ArrayList that holds the data collected from // the database. return dataArrays; } public Cursor getColumn(String columnName) { Cursor cursor = null; String[] columns = new String[1]; columns[0] = columnName; try { cursor = db.query(TABLE_NAME, columns, TABLE_ROW_NAME, null, null, null, null, null); } catch (SQLiteException e) { Log.e("Database.addRow", "Database Error: " + e.toString()); e.printStackTrace(); } return cursor; } /********************************************************************** * THIS IS THE BEGINNING OF THE INTERNAL SQLiteOpenHelper SUBCLASS. * * I MADE THIS CLASS INTERNAL SO I CAN COPY A SINGLE FILE TO NEW APPS * AND MODIFYING IT - ACHIEVING DATABASE FUNCTIONALITY. ALSO, THIS WAY * I DO NOT HAVE TO SHARE CONSTANTS BETWEEN TWO FILES AND CAN * INSTEAD MAKE THEM PRIVATE AND/OR NON-STATIC. HOWEVER, I THINK THE * INDUSTRY STANDARD IS TO KEEP THIS CLASS IN A SEPARATE FILE. *********************************************************************/ /** * This class is designed to check if there is a database that currently * exists for the given program. If the database does not exist, it creates * one. After the class ensures that the database exists, this class * will open the database for use. Most of this functionality will be * handled by the SQLiteOpenHelper parent class. The purpose of extending * this class is to tell the class how to create (or update) the database. * * @author Randall Mitchell * */ private class CustomSQLiteOpenHelper extends SQLiteOpenHelper { public CustomSQLiteOpenHelper(Context context) { super(context, DB_NAME, null, DB_VERSION); } @Override public void onCreate(SQLiteDatabase db) { // This string is used to create the database. It should // be changed to suit your needs. String newTableQueryString = "create table " + TABLE_NAME + " (" + TABLE_ROW_ID + " integer primary key autoincrement not null," + TABLE_ROW_NAME + " text," + TABLE_ROW_TYPE + " text," + TABLE_ROW_CONTACTS + " text," + TABLE_ROW_HOURS + " text," + TABLE_ROW_CHARGES + " text," + TABLE_ROW_STARTDATE + " text," + TABLE_ROW_ENDDATE + " text," + TABLE_ROW_ESTIMATE + " text" + ");"; // execute the query string to the database. db.execSQL(newTableQueryString); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // NOTHING TO DO HERE. THIS IS THE ORIGINAL DATABASE VERSION. // OTHERWISE, YOU WOULD SPECIFIY HOW TO UPGRADE THE DATABASE. } } }
————————————————————————————————
Code for the JAVA file:
package com.dzinesunlimited.quotetogo; import android.app.ListActivity; import android.database.Cursor; import android.os.Bundle; import android.widget.SimpleCursorAdapter; public class EditProject extends ListActivity { @Override protected void onCreate(Bundle savedInstanceState) { DBAdapter db; // TODO Auto-generated method stub super.onCreate(savedInstanceState); db = new DBAdapter(this); String columnName = "TABLE_ROW_NAME"; // better to use constants here // these arrays are used by the list adapter while connecting the data to the ListView String[] fields = new String[] { columnName }; int[] views = new int[] { android.R.id.text1 }; // create our cursor by calling the database method. Cursor cursor = db.getColumn(columnName); // call setListAdapter(), which is a method from the parent class. setListAdapter(new SimpleCursorAdapter( this, android.R.layout.simple_list_item_1, cursor, fields, views)); } }
Hope I don’t end up taking too much of your time. Thanks in advance.
Comment from vicky
Time September 28, 2010 at 7:15 am
Can any one help me for copy or use the assets folder database to data/data/package/databases folder…
Please help me..
Comment from Randall Mitchell
Time September 28, 2010 at 5:01 pm
@Sid. A couple things. Could you patch in the xml file as well. Also, the line in your activity class that looks like this:
String columnName = "TABLE_ROW_NAME"; // better to use constants here
should look like this based on your adapter class:
String columnName = db.TABLE_ROW_NAME;
You’ll need to change all of the static final variables in the adapter from private to public. That’s what I meant by “better to use constants”. Right now, you are pulling values from a row that doesn’t exist.
@vicky I didn’t really cover using an existing database (I think that’s what your after). There is a tutorial here that may have what you are looking for.
Comment from Siddharth
Time October 1, 2010 at 5:38 am
Hey there.
Sorry for the late reply. Was traveling on business and could not keep up with the blog. I did a lot of trial and error and finally got the solution. And your suggestion was absolutely on target. That was what I was missing. Along with that, I had absolutely no luck with the DB Adapter. Was messing up the code doing various things. Finally I realized that I could use debugging to see what I am doing wrong. And bingo. Got the solution.
Let me know if you would like to see the code. Slightly different from your suggestion, but pretty much takes after it.
Many thanks yet again.
Comment from Azeem
Time October 7, 2010 at 1:21 pm
I am very thankful to the author who wrote this wonderful article which helped me to understand the android database concept.
Thank You so much!!!
Comment from Siddharth
Time October 11, 2010 at 12:22 pm
Hey. I hope I wont bother you with another problem of mine. Instead of fetching records based on their ID, I need to fetch records based on their name. What do I need to change to get that functionality?
Thanks in advance.
Comment from Randall Mitchell
Time October 13, 2010 at 5:36 am
Hey Siddharth,
You could try something like this instead of the getAllIDs() method in the database object. It probably would have been better for me to do it this way in the first place. In this method, I’m just returning the cursor (actually copied this from a project I’m working on). You could of course pull the data from the cursor first if you’d prefer – just it similar to how I did in getAllIDs(). Let me know how that goes.
/** * Get one or more columns from a database table. * @param tableName the name of the table to get a column from. * @param columnNames an Array of column names to be retrieved. * @return a Cursor with all the given columns from a given table. */ public Cursor getColumns(String tableName, String[] columnNames) { Cursor cursor = null; try { cursor = db.query(tableName, columnNames, null, null, null, null, null); } catch (SQLiteException e) { Log.e("Database.getColumns()", e.toString()); e.printStackTrace(); } return cursor; }
Comment from Fiaz
Time October 19, 2010 at 12:01 pm
hey guys, all your help is excellent. I have tried to follow this tutorial (newbie) and cant get it to updatethe database how i needed. if i keep it at two row like in the example it works. the moment i add more of my own id doesnt.. any explantion on or has any had this same problem
Comment from fiaz
Time October 19, 2010 at 12:09 pm
hey Randall , all your work is really appreciated. I am new to android and have tried to follow your database tutorial and have managed to get it working fine. However when i add more rows to the database it doesnt seem to update the database. You currently have 2 rows in your example and i require 7 rows but no luck. I have follows just as in the tutorial no luck. Could i email you my eclipse project and see where it is i am goin wrong . Thanks randall
Comment from fiaz
Time October 20, 2010 at 9:22 am
Hey Randall, basically i created a student planner as a project of mine. I have read up on the android basics and im ok with that now. I need to create a profile section though.
the profile section will allow the student to add the following details on ONE screen:
1.student name
2. student Id
3.Lecturer Name
4. Lecturer Number
5.Lecurer Email
6. Unversity/college name
7. university/college number
the above seven need to be inputted by the student then put into database.
A SEPERATE screen is needed to show all of the above information.
a third screen needed to update the profile.
i have tried for several days to alter and try my methods along with this tutorial but have failed. soon as i start using more than two edittext boxeses it doenst seem to work. Sorry for this , really need your help .
Thanks Randall
Comment from Randall Mitchell
Time October 21, 2010 at 1:03 am
Hey Fiaz. Sorry to keep you hanging. I’ve been buried in a project and haven’t been coming out much. Have you been checking out the error log and console? Go to the debugging perspective and check the “LogCat”, “Error Log”, and Console windows for error messages (in red). Let me know if you find any leads there. Otherwise we can go over the code and try to find the problem.
Comment from kihore
Time October 21, 2010 at 11:24 am
Hai Randall Mitchell , Can u help me for copying or using the database in assets folder… i tried with the code below by placing database, which is created in sql manager in assets folder,
public class DataBaseHelper extends SQLiteOpenHelper {
//The Android’s default system path of your application database.
private static String DB_PATH = “/data/data/com.dharani.LazyApple/databases/”;
private static String DB_NAME = “ParkingHeroDB.sqlite”;
private SQLiteDatabase myDataBase;
private final Context myContext;
/**
* Constructor
* Takes and keeps a reference of the passed context in order to access to the application assets and resources.
* @param context
*/
public DataBaseHelper(Context context) {
super(context, DB_NAME, null, 1);
this.myContext = context;
}
/**
* Creates a empty database on the system and rewrites it with your own database.
* */
public void createDataBase() throws IOException{
boolean dbExist = checkDataBase();
if(dbExist){
//do nothing – database already exist
}else{
//By calling this method and empty database will be created into the default system path
//of your application so we are gonna be able to overwrite that database with our database.
this.getReadableDatabase();
try {
copyDataBase();
} catch (IOException e) {
throw new Error(“Error copying database”);
}
}
}
/**
* Check if the database already exist to avoid re-copying the file each time you open the application.
* @return true if it exists, false if it doesn’t
*/
private boolean checkDataBase(){
SQLiteDatabase checkDB = null;
try{
String myPath = DB_PATH + DB_NAME;
checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
}catch(SQLiteException e){
//database does’t exist yet.
}
if(checkDB != null){
checkDB.close();
}
return checkDB != null ? true : false;
}
/**
* Copies your database from your local assets-folder to the just created empty database in the
* system folder, from where it can be accessed and handled.
* This is done by transfering bytestream.
* */
private void copyDataBase() throws IOException{
//Open your local db as the input stream
InputStream myInput = myContext.getAssets().open(DB_NAME);
// Path to the just created empty db
String outFileName = DB_PATH + DB_NAME;
//Open the empty db as the output stream
OutputStream myOutput = new FileOutputStream(outFileName);
//transfer bytes from the inputfile to the outputfile
int length;
byte[] buffer = new byte[12024];
while ((length = myInput.read(buffer))>0){
myOutput.write(buffer, 0, length);
}
//Close the streams
myOutput.flush();
myOutput.close();
myInput.close();
}
public void openDataBase() throws SQLException{
//Open the database
String myPath = DB_PATH + DB_NAME;
myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
}
@Override
public synchronized void close() {
if(myDataBase != null)
myDataBase.close();
super.close();
}
public void onCreate(SQLiteDatabase db) {
}
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
and code in main class
public void onCreate(Bundle icicle)
{
super.onCreate(icicle);
setContentView(R.layout.main);
DataBaseHelper myDbHelper = new DataBaseHelper(this);
myDbHelper = new DataBaseHelper(this);
try {
myDbHelper.createDataBase();
} catch (IOException ioe) {
throw new Error(“Unable to create database”);
}
try {
myDbHelper.openDataBase();
}catch(SQLException sqle){
throw sqle;
}
}
}
really need your help .
Comment from Fizz
Time October 21, 2010 at 11:59 am
Thanks for getting back Randall. ok i have tried it again and have doubled checked all the code and everything seems ok. BUT when im entering the data for the 7 text fields and click ADD. it emptys the texts like it should but not displaying the data at the bottom. I checked the error log and have posted it here below. it says it cant find coloumn 3 and 7 but i have set them up.
could i send you my code in a project file so you can run it and see whats what. i have tried to follow just like you said. Really appreciate all your work.
10-21 11:54:15.791: WARN/System.err(315): at android.app.ActivityThread.main(ActivityThread.java:4627)
10-21 11:54:15.791: WARN/System.err(315): at java.lang.reflect.Method.invokeNative(Native Method)
10-21 11:54:15.802: WARN/System.err(315): at java.lang.reflect.Method.invoke(Method.java:521)
10-21 11:54:15.802: WARN/System.err(315): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868)
10-21 11:54:15.802: WARN/System.err(315): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626)
10-21 11:54:15.811: WARN/System.err(315): at dalvik.system.NativeStart.main(Native Method)
10-21 11:54:30.782: DEBUG/dalvikvm(109): GC_EXTERNAL_ALLOC freed 442 objects / 23288 bytes in 139ms
10-21 11:54:32.732: DEBUG/dalvikvm(109): GC_EXTERNAL_ALLOC freed 404 objects / 22128 bytes in 70ms
10-21 11:54:34.452: DEBUG/dalvikvm(109): GC_EXTERNAL_ALLOC freed 402 objects / 21952 bytes in 70ms
10-21 11:54:37.512: INFO/Database(315): sqlite returned: error code = 1, msg = table profileTable has no column named table_row_seven
10-21 11:54:37.612: DEBUG/dalvikvm(315): GC_FOR_MALLOC freed 6370 objects / 378984 bytes in 87ms
10-21 11:54:37.622: INFO/Database(315): sqlite returned: error code = 1, msg = no such column: table_row_three
10-21 11:54:37.632: WARN/System.err(315): android.database.sqlite.SQLiteException: no such column: table_row_three: , while compiling: SELECT id, table_row_one, table_row_two, table_row_three, table_row_four, table_row_five, table_row_six, table_row_seven FROM profileTable
10-21 11:54:37.632: WARN/System.err(315): at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
10-21 11:54:37.632: WARN/System.err(315): at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:91)
10-21 11:54:37.641: WARN/System.err(315): at android.database.sqlite.SQLiteCompiledSql.(SQLiteCompiledSql.java:64)
10-21 11:54:37.641: WARN/System.err(315): at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:80)
10-21 11:54:37.641: WARN/System.err(315): at android.database.sqlite.SQLiteQuery.(SQLiteQuery.java:46)
10-21 11:54:37.641: WARN/System.err(315): at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:42)
10-21 11:54:37.641: WARN/System.err(315): at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1345)
10-21 11:54:37.641: WARN/System.err(315): at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1229)
10-21 11:54:37.641: WARN/System.err(315): at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1184)
10-21 11:54:37.641: WARN/System.err(315): at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1264)
10-21 11:54:37.641: WARN/System.err(315): at com.ProfileManager.ProfileDbHelper.getAllRowsAsArrays(ProfileDbHelper.java:224)
10-21 11:54:37.641: WARN/System.err(315): at com.ProfileManager.ProfileManager.updateTable(ProfileManager.java:258)
10-21 11:54:37.651: WARN/System.err(315): at com.ProfileManager.ProfileManager.addRow(ProfileManager.java:149)
10-21 11:54:37.651: WARN/System.err(315): at com.ProfileManager.ProfileManager.access$0(ProfileManager.java:132)
10-21 11:54:37.651: WARN/System.err(315): at com.ProfileManager.ProfileManager$1.onClick(ProfileManager.java:100)
10-21 11:54:37.651: WARN/System.err(315): at android.view.View.performClick(View.java:2408)
10-21 11:54:37.651: WARN/System.err(315): at android.view.View$PerformClick.run(View.java:8816)
10-21 11:54:37.651: WARN/System.err(315): at android.os.Handler.handleCallback(Handler.java:587)
10-21 11:54:37.651: WARN/System.err(315): at android.os.Handler.dispatchMessage(Handler.java:92)
10-21 11:54:37.651: WARN/System.err(315): at android.os.Looper.loop(Looper.java:123)
10-21 11:54:37.661: WARN/System.err(315): at android.app.ActivityThread.main(ActivityThread.java:4627)
10-21 11:54:37.661: WARN/System.err(315): at java.lang.reflect.Method.invokeNative(Native Method)
10-21 11:54:37.661: WARN/System.err(315): at java.lang.reflect.Method.invoke(Method.java:521)
10-21 11:54:37.661: WARN/System.err(315): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868)
10-21 11:54:37.661: WARN/System.err(315): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626)
10-21 11:54:37.661: WARN/System.err(315): at dalvik.system.NativeStart.main(Native Method)
10-21 11:54:37.831: DEBUG/dalvikvm(109): GC_EXTERNAL_ALLOC freed 392 objects / 21568 bytes in 60ms
theres the log above,
whats your email Randall plzzz
Comment from Fizz
Time October 21, 2010 at 12:03 pm
I wanted to get it so i could have like a tabular display. the first tab will have all the edit texts fileds where the student input the data. the second tab is where they can view all there details in a list and the third tab in which they can update the data. Sorry to pester but im stuck. i have looked up on tabular display and im learning that now. but dont know how it will integrate with your coz i cant even get the basic version working…i got your version with the 2 inputs working fine.. just not 7 like i need.
Comment from Fizz
Time October 24, 2010 at 8:11 pm
Hey randall i got your email and replied to it with the attachment ..have u got it…
Comment from Randall Mitchell
Time October 25, 2010 at 3:15 pm
typed on my samsung epic…i have. sorrry i have not gotten back on it yet. i had friends in from out of town and i am serving jury duty today…currently waiting for jury selection to begin. i will do my best to get to it tonight. i did briefly look at the code but did not notice anything right away.
Comment from kavya
Time October 26, 2010 at 5:51 am
hi im new to database i tried ur program given in the tutorial plz give me a solution to this error
Cannot instantiate the type AABDatabaseManager.CustomSQLiteOpenHelper
Comment from mackern
Time October 26, 2010 at 11:38 am
Hi,
I get this error message when trying to add the clickhandler for the button. The method onClick(View) of type new View.OnClickListener(){} must override a superclass method
Im a both Android and Java newbie, could it be some settings of my Eclipse that is wrong?
Comment from Randall Mitchell
Time October 26, 2010 at 5:34 pm
@mackern
Briefly,
An object of a class that is a subclass of an abstract class must Override the abstract methods of that class. Meaning, if you make a non-abstract child of an abstract class, you must write its abstract methods. You do this by using @Override directive followed immediately by the non-abstract method. In Eclipse, you should be able to point at the code underlined in red and Eclipse will prompt you to let it add the missing method.
You want something that looks similar to:
addButton.setOnClickListener ( new View.OnClickListener() { @Override public void onClick(View v) {addRow();} } );
Comment from Randall Mitchell
Time October 26, 2010 at 5:36 pm
@kavya I would not be able to figure that one out without more code/information. Feel free to zip and email me your code: mitch@anotherandroidblog.com.
Comment from Randall Mitchell
Time October 26, 2010 at 5:41 pm
@ FizzI haven’t really messed with tabs Fizz. I would imagine that they are not much different than any other container embedded in an Activity. You would create the tabs widget just like any other object and then do the work with it in java via reference findViewByID(). I’d also guess that creating custom widgets by extending regular widgets would be the best method for each tab page.
Comment from mackern
Time October 28, 2010 at 11:26 am
Randall, that was exactly what I had done when I got the error.
I read on some post on another forum, that I should change the Java Compiler Compliance level from 1.5 to 1.6 and that seemed to do the trick.
Another question though. How can I access the logfile which Log class writes to, I assume it is a file somewhere on the emulator?
Comment from Randall Mitchell
Time October 28, 2010 at 5:53 pm
Hey mackern,
On the top right of Eclipse (below the minimize, close, etc. buttons) is where you change perspectives. Switch to (or add) the Debug perspective there. It should be set to “Java” right now. Once in the Debug perspective, you can go to Window->Show View->LogCat. Honestly, I’ve never retrieved the log from file before so I couldn’t tell you where it is off hand.
Comment from Fon
Time November 8, 2010 at 1:49 pm
Hi Randall,
I’m trying to read data from a database and store the data into an array “int[] db_date”, but I have trouble accessing this “db_date” outside the try-catch section.
(I got an error “db_date cannot be solved to a variable” in the last line of the code below.)
Since I’m new to both Java and Android, it might be that the way I’m trying to store the data isn’t that good. Do you have any suggestions about how to store the data that can be read outside? Your answer will be very much appreciated.
public class CalendarAct extends Activity { AABDatabaseManager db; // the class that opens the database /** Called when the activity is first created. */ @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(new DrawView(this)); } private class DrawView extends View { private Paint paint; public DrawView(Context context) { super(context); } protected void onDraw(Canvas canvas) { super.onDraw(canvas); try { // create the database manager object db = new DatabaseManager(CalendarAct.this); // get row information ArrayList<ArrayList> data = db.getAllRowsAsArrays(); // get size of database and create integer array of that size int sizeOfDb = data.size(); int[] db_date = new int[sizeOfDb]; // iterate the ArrayList for (int position=0; position < sizeOfDb; position++) { ArrayList row = data.get(position); //Get data from database and write to db_date array db_date[position] = Integer.parseInt(row.get(1).toString()); } } catch (Exception e) { Log.e("GetError", e.toString()); } int quotient = (int)(db_date[idx]/7);
Comment from Randall Mitchell
Time November 9, 2010 at 1:57 pm
Hey Fon. Where you declare a variable, as in ArrayList<ArrayList> data;, determines where you can use it. Because you declared the ArrayList inside of the try/catch block, it is a local variable and is destroyed as soon as the block has finished executing. The same is true if you declare a variable inside of a method. As soon as the method has finished executing, the variable would not longer be available after.
You need to declare the array outside of the try/catch block. If you want the array available anywhere inside of the class, at the top of the class you should have.
public class... { AABDatabaseManager db; // the class that opens the database ArrayList<ArrayList> data; // I would change data to something more meaningful that describes what the data is. ...
Then, in the onCreate() method, you instantiate you variables.
@Override public void onCreate(Bundle savedInstanceState) { ... db = new DatabaseManager(CalendarAct.this); data = new ArrayList<ArrayList>(); }
And finally, you give the array it’s data in the try/catch block.
data = db.getAllRowsAsArrays();
I hope that clears it up for you.
Comment from Fon
Time November 10, 2010 at 1:37 pm
Hi Randall,
Thank you very much for your answer. It was very clear with example which helped me solve the problem.
Comment from Harshal Kulkarni
Time December 3, 2010 at 12:50 pm
Really Nice Work….
It helped me a lot… !!!!!
Comment from Ford
Time December 18, 2010 at 2:08 pm
Hi , must say great tutorial – google should use this!
Comment from satish
Time December 31, 2010 at 7:12 am
your tutorial is very useful for me,because before that i have seen many tutorials about database ,but i did not get proper idea ,but when i see your tutorial i got a clear idea ,
one request from me ,
That is i want to display my data table data into another screen not bottom of the main screen ,what should i do i tried a lot but i did not get the data into another screen,plz help me out.
thanks.
Comment from Randall Mitchell
Time January 5, 2011 at 9:38 pm
hi satish. sorry for the delay. I am working on a tutorial that will show you how to use a single database object throughout an application. It involves creating a subclass of Android’s Application class, using your manifest file to tell you application to use the new class, creating an instance of the database object inside the application subclass, then creating a pointer to your application subclass within each of your activities. If you need to store data permanently, this may be the best way to go. Any application settings should be handled different. Also, data you only need temporarily should be passed through an intent.
Some of these tutorials take me longer to write than you’d expect. This one may not be done until late in the month; however, there are many like it online already.
Comment from adrian
Time January 7, 2011 at 6:39 am
Hi Randall,
Thanks for the great, succinct, tutorial. Could you tell me how I would add a spinner to your project for a user then select an table1 name.
I can then get the row id and parse the output to my program which currently only uses a static XML based array.
Many thanks,
Adrian
Comment from Randall Mitchell
Time January 10, 2011 at 6:01 pm
Hi Adrian. I haven’t really played around with the spinner before. This looks like a pretty good spinner tutorial here
Comment from adrian
Time January 11, 2011 at 2:34 am
Thanks Randall for the reply.
I had a look at the link and personally require a SQLite and Spinner example.
There are a few out there but ive looked extensively and found none with complete source examples.
I’m a learn by example kinda guy unfortunately.
Thanks,
Adrian
Comment from Mehmet Gonullu
Time January 16, 2011 at 5:32 am
Awesome Tutorial, the only one that explained it clear and easily, I would make a donation for you soon
Pingback from Using Databases in Android Development | MGNom Blogs
Time January 16, 2011 at 6:16 am
Comment from E
Time January 19, 2011 at 10:14 pm
Let me begin by saying this is a great writeup that is easy to follow. Nice work, Randall.
Fizz,
In the openDataBase method, do you have it set to OPEN_READONLY? I had to change mine to OPEN_READWRITE in order to have my database update to any row changes.
Adrian,
Check out stackoverflow.com as they have numerous questions and answers regarding spinners. I spent a lot of time getting dynamic spinners setup for my app.
Comment from Terry Bieritz
Time January 26, 2011 at 2:15 am
Hi Randall
I just found your site while roaming around looking for Android examples. I love what you’ve put together. And it sounds like your a busy fellow! I hope college is going well for you. I’m new to Android development and have a database challenge. I’d like to see if you’ve “been there” but wanted to make sure you are in a position to respond. Thanks for getting back when you have a moment.
Terry
Comment from Micheal
Time February 2, 2011 at 2:50 am
Great Tutorial. I have been looking for a long time for something like this, And you have done a great job putting it all together, in order for newbie’s like myself can understand………… I have been able to split up the main.xml into four others “add.xml” , “edit.xml” , “view.xml” , ”delete.xml” along with their corresponding java classes…The Main.xml now contains only the data table (TableLayout),Is it possible to add a OnClickListener to this tablelayout that brings up a menu showing “Edit” , ”View” and “Delete” and then once clicked pass the information over and take you to the corresponding xml ………….after many hours searching, I have come to the conclusion that it may be best to use a Listview for this if this is correct or not could you please advise and even better show me the way…Thanks
Comment from shakuri
Time February 7, 2011 at 4:27 am
Can someone please send me a tutorial/source or something about how to make dynamic spinners….i am a newbie ….THANKS IN ADVANCE FOR ANYONE THAT CAN HELP
Pingback from Bloggin' with Charlotte Backus
Time February 10, 2011 at 4:06 pm
[...] tutorial here. Categories: Project Tags: Android, project, [...]
Comment from KISHORE
Time February 17, 2011 at 7:23 am
Hi Randall Mitchell, my name is K. Kishore and i’m from india. I am trying to create a fuel calculator app. This tutorial is very much helpful to me. I followed the tutorial, every thing worked out well but i am unable to view the table in the UI and I feel that OnClickListener’s are not working good.
Please find time to go through the code and help me out.
thanks….
this is my cod for Main Activity FuelInfor.java
package com.kk.FuelInfo;
import java.util.ArrayList;
import android.app.Activity;
import android.app.ListActivity;
import android.content.Context;
import android.database.Cursor;
import android.os.Bundle;
import android.speech.tts.TextToSpeech;
import android.speech.tts.TextToSpeech.OnInitListener;
import android.util.Log;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;
import android.widget.TableLayout;
import android.widget.TableRow;
import android.widget.TextView;
import android.widget.Toast;
public class FuelInfo extends ListActivity implements OnInitListener {
EditText textFieldOne, textFieldTwo,textFieldThree,textFieldFour,textFieldFive,textFieldSix, idField, updateIDField, updateTextFieldOne,updateTextFieldTwo,
updateTextFieldThree,updateTextFieldFour,updateTextFieldFive,updateTextFieldSix;
TextToSpeech tx;
Button addButton, deleteButton, retrieveButton, updateButton;
private ArrayAdapter listAdapter;
TableLayout dataTable;
DataHandler db;
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState)
{
// this try catch block returns better error reporting to the log
try
{
// Android OS specific calls
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
db = new DataHandler(this);
setupViews();
// make the buttons clicks perform actions
addButtonListeners();
// load the data table
updateTable();
}
catch (Exception e)
{
Log.e(“ERROR”, e.toString());
e.printStackTrace();
}
}
private void setupViews()
{
// THE DATA TABLE
dataTable= (TableLayout)findViewById(R.id.data_table);
// THE DATA FORM FIELDS
textFieldOne= (EditText)findViewById(R.id.text_field_one);
textFieldTwo= (EditText)findViewById(R.id.text_field_two);
idField= (EditText)findViewById(R.id.id_field);
updateIDField= (EditText)findViewById(R.id.update_id_field);
updateTextFieldOne= (EditText)findViewById(R.id.update_text_field_one);
updateTextFieldTwo= (EditText)findViewById(R.id.update_text_field_two);
updateTextFieldThree=(EditText)findViewById(R.id.update_text_field_three);
updateTextFieldFour=(EditText)findViewById(R.id.update_text_field_four);
updateTextFieldFive=(EditText)findViewById(R.id.update_text_field_five);
updateTextFieldSix=(EditText)findViewById(R.id.update_text_field_six);
// THE BUTTONS
addButton = (Button)findViewById(R.id.add_button);
deleteButton = (Button)findViewById(R.id.delete_button);
retrieveButton = (Button)findViewById(R.id.retrieve_button);
updateButton = (Button)findViewById(R.id.update_button);
}
private void addButtonListeners()
{
addButton.setOnClickListener
(
new View.OnClickListener()
{
@Override public void onClick(View v) {addRow(); tx.speak(“Updated”,TextToSpeech.QUEUE_FLUSH, null);}
}
);
deleteButton.setOnClickListener
(
new View.OnClickListener()
{
@Override public void onClick(View v) {deleteRow(); tx.speak(“Updated”,TextToSpeech.QUEUE_FLUSH, null);}
}
);
updateButton.setOnClickListener
(
new View.OnClickListener()
{
@Override public void onClick(View v) {updateRow(); tx.speak(“Updated”,TextToSpeech.QUEUE_FLUSH, null);}
}
);
retrieveButton.setOnClickListener
(
new View.OnClickListener()
{
@Override public void onClick(View v) {retrieveRow(); tx.speak(“Updated”,TextToSpeech.QUEUE_FLUSH, null);}
}
);
}
private void addRow()
{
try
{
// ask the database manager to add a row given the two strings
// this is addRow() in the activity calling addRow() in the database object
db.addRow
(
textFieldOne.getText().toString(),
textFieldTwo.getText().toString(),textFieldThree.getText().toString(),textFieldFour.getText().toString(),textFieldFive.getText().toString(),textFieldSix.getText().toString()
);
// request the table be updated
updateTable();
// remove all user input from the Activity
emptyFormFields();
}
catch (Exception e)
{
Log.e(“Add Error”, e.toString());
e.printStackTrace();
}
}
private void deleteRow()
{
try
{
// ask the database manager to delete the row with the give rowID.
db.deleteRow(Long.parseLong(idField.getText().toString()));
// request the table be updated
updateTable();
// remove all user input from the Activity
emptyFormFields();
}
catch (Exception e)
{
Log.e(“Delete Error”, e.toString());
e.printStackTrace();
}
}
private void retrieveRow()
{
try
{
// The ArrayList that holds the row data
ArrayList row;
// ask the database manager to retrieve the row with the given rowID
row = db.getRowAsArray(Long.parseLong(updateIDField.getText().toString()));
// update the form fields to hold the retrieved data
updateTextFieldOne.setText((String)row.get(1));
updateTextFieldTwo.setText((String)row.get(2));
updateTextFieldThree.setText((String)row.get(3));
updateTextFieldFour.setText((String)row.get(4));
updateTextFieldFive.setText((String)row.get(5));
updateTextFieldSix.setText((String)row.get(6));
}
catch (Exception e)
{
Log.e(“Retrieve Error”, e.toString());
e.printStackTrace();
}
}
private void updateRow()
{
try
{
// ask the database manager to update the row based on the information
// found in the corresponding user entry fields
db.updateRow
(
Long.parseLong(updateIDField.getText().toString()),
updateTextFieldOne.getText().toString(),
updateTextFieldTwo.getText().toString(),updateTextFieldThree.getText().toString(),updateTextFieldFour.getText().toString(),updateTextFieldFive.getText().toString(),updateTextFieldSix.getText().toString()
);
// request the table be updated
updateTable();
// remove all user input from the Activity
emptyFormFields();
}
catch (Exception e)
{
Log.e(“Update Error”, e.toString());
e.printStackTrace();
}
}
private void updateTable()
{
// delete all but the first row. remember that the count
// starts at one and the index starts at zero
while (dataTable.getChildCount() > 1)
{
// while there are at least two rows in the table widget, delete
// the second row.
dataTable.removeViewAt(1);
}
// collect all row data from the database and
// store it in a two dimensional ArrayList
ArrayList<ArrayList> data = db.getAllRowsAsArrays();
// iterate the ArrayList, create new rows each time and add them
// to the table widget.
for (int position=0; position < data.size(); position++)
{
TableRow tableRow= new TableRow(this);
ArrayList row = data.get(position);
TextView idText = new TextView(this);
idText.setText(row.get(0).toString());
tableRow.addView(idText);
TextView textOne = new TextView(this);
textOne.setText(row.get(1).toString());
tableRow.addView(textOne);
TextView textTwo = new TextView(this);
textTwo.setText(row.get(2).toString());
tableRow.addView(textTwo);
TextView textThree = new TextView(this);
textTwo.setText(row.get(3).toString());
tableRow.addView(textTwo);
TextView textFour= new TextView(this);
textTwo.setText(row.get(4).toString());
tableRow.addView(textTwo);
TextView textFive = new TextView(this);
textTwo.setText(row.get(5).toString());
tableRow.addView(textTwo);
TextView textSix = new TextView(this);
textTwo.setText(row.get(6).toString());
tableRow.addView(textTwo);
dataTable.addView(tableRow);
}
}
private void emptyFormFields()
{
textFieldOne.setText(“”);
textFieldTwo.setText(“”);
idField.setText(“”);
updateIDField.setText(“”);
updateTextFieldOne.setText(“”);
updateTextFieldTwo.setText(“”);
updateTextFieldThree.setText(“”);
updateTextFieldFour.setText(“”);
updateTextFieldFive.setText(“”);
updateTextFieldSix.setText(“”);
}
@Override
public void onInit(int status) {
// TODO Auto-generated method stub
}
}
This is my data handler code DataHandler.java
package com.kk.FuelInfo;
import java.io.Flushable;
import java.util.ArrayList;
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.speech.tts.TextToSpeech;
import android.speech.tts.TextToSpeech.OnInitListener;
import android.util.Log;
public class DataHandler implements OnInitListener {
private SQLiteDatabase db; // a reference to the database manager class.
private final String DB_NAME = “Master”; // the name of our database
private final int DB_VERSION = 1; // the version of the database
Context context;
TextToSpeech tx;
// the names for our database columns
private final String TABLE_NAME = “Fuel_Info”;
private final String TABLE_ROW_No = “SNo”;
private final String TABLE_ROW_ONE = “FillDate”;
private final String TABLE_ROW_TWO = “FuelQuantity”;
private final String TABLE_ROW_THREE = “FCPLtr”;
private final String TABLE_ROW_FOUR = “FuelAvl”;
private final String TABLE_ROW_FIVE = “TotFAvl”;
private final String TABLE_ROW_SIX = “TotalFuelCost”;
public DataHandler(Context context) {
this.context=context;
MySQLiteOpenHelper helper = new MySQLiteOpenHelper(context);
this.db = helper.getWritableDatabase();
}
public void addRow(String FillDate, String FuelQuant,String FCPL,String FuelAvl,String TotFuel,String TFC)
{
// this is a key value pair holder used by android’s SQLite functions
ContentValues values = new ContentValues();
// this is how you add a value to a ContentValues object
// we are passing in a key string and a value string each time
values.put(TABLE_ROW_ONE, FillDate);
values.put(TABLE_ROW_TWO, FuelQuant);
values.put(TABLE_ROW_THREE, FCPL);
values.put(TABLE_ROW_FOUR, FuelAvl);
values.put(TABLE_ROW_FIVE, TotFuel);
values.put(TABLE_ROW_SIX, TFC);
// ask the database object to insert the new data
try
{
db.insert(TABLE_NAME, null, values);
}
catch(Exception e)
{
Log.e(“DB ERROR”, e.toString()); // prints the error message to the log
e.printStackTrace(); // prints the stack trace to the log
}
}
public void deleteRow(long rowID)
{
// ask the database manager to delete the row of given id
try
{
db.delete(TABLE_NAME, TABLE_ROW_No + “=” + rowID, null);
}
catch (Exception e)
{
Log.e(“DB ERROR”, e.toString());
e.printStackTrace();
}
}
private class MySQLiteOpenHelper extends SQLiteOpenHelper
{
public MySQLiteOpenHelper(Context context) {
super(context, DB_NAME, null, DB_VERSION);
// TODO Auto-generated constructor stub
}
// TODO: override the constructor and other methods for the parent class
@Override
public void onCreate(SQLiteDatabase db) {
String Query =
“create table ” +
TABLE_NAME +
” (” +
TABLE_ROW_No + ” integer primary key autoincrement not null,” +
TABLE_ROW_ONE + ” text,” +
TABLE_ROW_TWO + ” text” +
TABLE_ROW_THREE + ” text” +
TABLE_ROW_FOUR + ” text” +
TABLE_ROW_FIVE + ” text” +
TABLE_ROW_SIX + ” text” +
“);”;
tx.speak(“Database Created”,TextToSpeech.QUEUE_FLUSH, null);
// execute the query string to the database.
db.execSQL(Query);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
}
}
public void updateRow(long rowID,String FillDate, String FuelQuant,String FCPL,String FuelAvl,String TotFuel,String TFC)
{
// this is a key value pair holder used by android’s SQLite functions
ContentValues values = new ContentValues();
values.put(TABLE_ROW_ONE, FillDate);
values.put(TABLE_ROW_TWO, FuelQuant);
values.put(TABLE_ROW_THREE, FCPL);
values.put(TABLE_ROW_FOUR, FuelAvl);
values.put(TABLE_ROW_FIVE, TotFuel);
values.put(TABLE_ROW_SIX, TFC);
// ask the database object to update the database row of given rowID
try {db.update(TABLE_NAME, values, TABLE_ROW_No + “=” + rowID, null);
tx.speak(“Updated”,TextToSpeech.QUEUE_FLUSH, null);
}
catch (Exception e)
{
Log.e(“DB Error”, e.toString());
e.printStackTrace();
}
}
public ArrayList getRowAsArray(long rowID)
{
// create an array list to store data from the database row.
ArrayList rowArray = new ArrayList();
Cursor cursor;
try
{
// this is a database call that creates a “cursor” object.
// the cursor object store the information collected from the
// database and is used to iterate through the data.
cursor = db.query
(
TABLE_NAME,
new String[] { TABLE_ROW_No, TABLE_ROW_ONE, TABLE_ROW_TWO ,TABLE_ROW_THREE,TABLE_ROW_FOUR,TABLE_ROW_FIVE,TABLE_ROW_SIX},
TABLE_ROW_No + “=” + rowID,
null, null, null, null, null
);
// move’s the pointer to position zero in the cursor.
cursor.moveToFirst();
// if there is data available after the cursor’s pointer, we are adding
// it to the ArrayList that will be returned by the method.
if (!cursor.isAfterLast())
{
do
{
rowArray.add(cursor.getLong(0));
rowArray.add(cursor.getString(1));
rowArray.add(cursor.getString(2));
rowArray.add(cursor.getString(3));
rowArray.add(cursor.getString(4));
rowArray.add(cursor.getString(5));
rowArray.add(cursor.getString(6));
rowArray.add(cursor.getString(7));
}
while (cursor.moveToNext());
}
tx.speak(“Updated”,TextToSpeech.QUEUE_FLUSH, null);
cursor.close();
}
catch (SQLException e)
{
Log.e(“DB ERROR”, e.toString());
e.printStackTrace();
}
// return the ArrayList containing the given row from the database.
return rowArray;
}
public ArrayList<ArrayList> getAllRowsAsArrays()
{
// create an ArrayList that will hold all of the data collected from
// the database.
ArrayList<ArrayList> dataArrays = new ArrayList<ArrayList>();
// this is a database call that creates a “cursor” object.
// the cursor object store the information collected from the
// database and is used to iterate through the data.
Cursor cursor;
try
{
// database object to create the cursor.
cursor = db.query(
TABLE_NAME,
new String[]{TABLE_ROW_No, TABLE_ROW_ONE, TABLE_ROW_TWO,TABLE_ROW_THREE,TABLE_ROW_FOUR,TABLE_ROW_FIVE,TABLE_ROW_SIX},
null, null, null, null, null
);
// move the cursor’s pointer to position zero.
cursor.moveToFirst();
// if there is data after the current cursor position,we are adding it
// to the ArrayList.
if (!cursor.isAfterLast())
{
do
{
ArrayList dataList = new ArrayList();
dataList.add(cursor.getLong(0));
dataList.add(cursor.getString(1));
dataList.add(cursor.getString(2));
dataList.add(cursor.getString(3));
dataList.add(cursor.getString(4));
dataList.add(cursor.getString(5));
dataList.add(cursor.getString(6));
dataList.add(cursor.getString(7));
dataArrays.add(dataList);
}
// move the cursor’s pointer up one position.
while (cursor.moveToNext());
tx.speak(“Retrived”,TextToSpeech.QUEUE_FLUSH, null);
}
}
catch (SQLException e)
{
Log.e(“DB Error”, e.toString());
e.printStackTrace();
}
// return the ArrayList that holds the data collected from
// the database.
return dataArrays;
}
@Override
public void onInit(int status) {
// TODO Auto-generated method stub
db.execSQL(“create table ” +
TABLE_NAME +
” (” +
TABLE_ROW_No + ” integer primary key autoincrement not null,” +
TABLE_ROW_ONE + ” text,” +
TABLE_ROW_TWO + ” text” +
TABLE_ROW_THREE + ” text” +
TABLE_ROW_FOUR + ” text” +
TABLE_ROW_FIVE + ” text” +
TABLE_ROW_SIX + ” text” +
“);”);
tx.speak(“DB CREATED”,TextToSpeech.QUEUE_FLUSH, null);
}
}
Layout main.xml
Comment from x1um1n
Time February 17, 2011 at 10:05 pm
Hi,
I’m new to android dev (and haven’t done any serious java in a few years) so I suspect I’m making a fairly stupid mistake. My app FC’s with a null pointer exception when I try and create a dbmanager object. I think I’m not passing the context correctly, but can’t figure out what I’m doing wrong:
[code]public class newListDialog extends Dialog implements OnClickListener {
Button btnOK;
Button btnCancel;
EditText txtListName;
Spinner cmbNewListStore;
DBManager db;
public newListDialog(Context context) {
super(context);
setContentView(R.layout.new_list_dlg);
//fixme:seriously broken WTF!!!!!!
db = new DBManager(this.getContext());
btnOK = (Button) findViewById(R.id.btnOK);
btnOK.setOnClickListener(this);
btnCancel = (Button) findViewById(R.id.btnCancel);
btnCancel.setOnClickListener(this);
txtListName = (EditText) findViewById(R.id.txtListName);
//Store Selector Combobox
String[] storez = context.getResources().getStringArray(R.array.storez);
//fixme: connect to the DB and fill storez with store names
//db.getAllStores().toArray(storez);
ArrayAdapter storezAdapter = new ArrayAdapter (context, android.R.layout.simple_spinner_item, storez);
cmbNewListStore = (Spinner) findViewById(R.id.cmbNewListStore);
cmbNewListStore.setAdapter(storezAdapter);
}
@Override
public void onClick(View v) {
if (v == btnOK) {
//db.addList(txtListName.getText().toString(), db.getStoreID(cmbNewListStore.getSelectedItem().toString()));
//clear the form
txtListName.setText("");
cmbNewListStore.setSelection(0);
dismiss();
} else {
if (v == btnCancel) {
//clear the form
txtListName.setText("");
cmbNewListStore.setSelection(0);
cancel();
}
}
}
}[/code]
any help would be much appreciated. If you need to see more code, it’s all available here: https://code.google.com/p/yasl/source/browse/#svn%2Ftrunk%2Fsrc%2Fuk%2Fco%2Fpenguinpower%2Fyasl
Thanks
x1um1n
Comment from JohnyXD
Time February 25, 2011 at 8:42 am
Great tutorial, easy enough to understand.
BTW I don’t know if anyone noticed but you sometimes use 8 arguments for db.query()
Comment from sazid
Time March 3, 2011 at 10:03 am
Hey…Randall…great tutorial…..the best tutorial on android i have seen so far…
Comment from Shaista Naaz
Time March 4, 2011 at 5:52 am
Awesome tutorial. thanks a tons.
Comment from Irena
Time March 4, 2011 at 1:19 pm
Great Tutorial. Thanks a lot! Please keep posting tutorial for Android
Comment from Imran Zahid
Time April 3, 2011 at 4:16 pm
i have completely copied your tutorial but my application can’t retrieve data by click get button.database file has created in data folder but i am not able to fetch data in table layout.please help me………….
Comment from Sayantini
Time April 25, 2011 at 8:50 am
Hi Randy, I have tried your code but it didnt work. I am surprised why. The build was a success. Even the emulator came up but it was blank even after launching the application. Do you have any idea why ?
Comment from Santosh
Time May 5, 2011 at 10:11 am
I am just reading your blog and by the way I just looked at your appeal section on donation and it is a genuine appeal. I am also sorry for not being able to help you on that part.
But I want to thank you from heart to your effort and the way you reply all queries and give your 100% to make readers understand fully.I found your blog very helpful and guys like you are always an inspiration for me to carry on and work hard.
Thanks again
Comment from Randall Mitchell
Time May 5, 2011 at 2:36 pm
Hi Imran and Sayantini. Sorry I missed these. I’m not sure if I missed the notification emails, they didn’t get sent, or I just failed to follow up on it. You’ve probably got some sort of type in your code. If you give me a little more information, I may be able to help. Probably, you should investigate debugging techniques regarding the log but I will be happy to help.
Randall
Comment from laurence
Time May 7, 2011 at 12:24 am
Nice tutorial, just a few comments:
I was using the code from your tutorial in my application, however, since I was using the database in multiple activities, I noticed that logcat was giving me error messages likes: “close() was never explicitly called on database”
You should close the database and helper when you’re done using them:
http://stackoverflow.com/questions/4464892/android-error-close-was-never-explicitly-called-on-database
I would change the code sample above to something like this:
private CustomSQLiteOpenHelper helper;
private SQLiteDatabase db;
public DBProfileManager(Context context)
{
this.context = context;
// create or open the database
helper = new CustomSQLiteOpenHelper(context);
this.db = helper.getWritableDatabase();
}
public void close()
{
if (helper != null)
helper.close();
if (db != null)
db.close();
}
and then use it like:
DBProfileManager db = new DBProfileManager(ProfileActivity.this);
db.addRow(entryOne, entryTwo);
db.close();
Comment from m0rb
Time May 7, 2011 at 10:53 am
Hey Guys,
thx first to Randall who wrote this very helpful tutorial.
@laurence
I got the same problem as u have and i found the same close method used in other tutorials. It seems pretty simple to write the close Method for the helper Object but i still have an “basic understanding” problem of the usage of that particular helper class.
e.g.: I have several activities (or views), where i need to call DB operations. Do i have to instantiate the DB class just once in each new activity or do i have to do this in every single method !?
Would be very thankful for an answer !
greetings from Germany
m0rb
Comment from Randall Mitchell
Time May 7, 2011 at 5:55 pm
@laurence. Thanks for that. I have since found this to be a problem in this software. Basically, I refactored this class in subsequent software and need to update this tutorial. Passing out arrays would mean you could open and close the database inside of each method in the db object. Better would be to pass out custom objects that handle the data you need to collect and pass those out.
@m0rb there are different ways to do what you are looking for. One method is to create the db object inside of the activity class. Do that if the database will need to be accessed several times inside of the activity. You need to think about the trade off between size and speed. Storing in the activity uses a little more space the entire time the activity is open. Creating a new db object each time you call a method takes time and can slow your application down. If you call that method from inside of a loop, your application could very well slow to a crawl. Alternatively, create a custom android.app.Application object and store it there. It takes a little more setup but if you need the database in most of your activities this could be very useful. This would take a little more memory but help with performance because you only load the db class once each time your user uses your application. Generally speaking, these are nominal – maybe unnoticed – quantities unless you do something like call a database method inside of a loop and end up creating the database object over and over.
Check out the code in this link for an alternative structure to using a database class: http://www.anotherandroidblog.com/2011/03/07/a-very-basic-application/.
Comment from Sayantini
Time May 25, 2011 at 6:12 pm
Hi Randall, I always keep on coming back to your site. Randall, I would like some help from you for an app. If you could give me a some lead, it would be really great. I need to store a .csv file onto my app and fetch the data with date.
Comment from Randall Mitchell
Time May 25, 2011 at 6:29 pm
Hi Sayantini, it sounds like you will need a script to iterate through the cvs file. You will also need to be able to load the file into your program in some manner. Are you retrieving the file from a web server or locally? Storing and retrieving the data would be hard to explain in this format. Have you already started trying to get the code written?
Comment from n.a.
Time June 11, 2011 at 5:23 pm
This is the best article I’ve ever read about SQLite. Seriously. And I’ve read a lot, but the others are not comparable. It could be bigger, and I wouldn’t care, because it didn’t bored me.
Now I’m feelin like I had discovered the world. haha
Comment from John Scott
Time June 16, 2011 at 4:34 pm
What a fantastic blog post. This has just saved my life up against a very tight deadline. Just wanted to say thank you
Comment from Avi Kumar Manku
Time June 17, 2011 at 1:38 pm
Thanx, Randall Mitchell it is really a helpful tutorial
and nice
Pingback from android | niujins blog
Time June 22, 2011 at 2:29 am
Pingback from Remove deletion anomaly ? | Android JB
Time June 23, 2011 at 12:01 pm
[...] am developing an application where i build a database , i build my database with help of tutorial http://www.anotherandroidblog.com/2010/08/04/android-database-tutorial/ ,the code is working fine, but the problem is that when i delete an i item from a particular id, it [...]
Comment from Noman khan
Time July 14, 2011 at 8:42 am
Simply da best tutorial for DB
Comment from Shardul
Time July 14, 2011 at 6:06 pm
Nice Work there! Made the learning curve for Android DB super smooth for me!
Comment from Theo Kramer
Time July 30, 2011 at 7:35 pm
Great tutorial, including all details about typecasting and declarations to get data in and out of sqlite, and a working project, thanks a lot
Comment from Panneerselvam.G
Time August 4, 2011 at 8:20 am
Hey its really good ..
I was trouble in Database concept but now i cleared my all doubts.
Its superb:)
Pingback from SQLite issues. Please help!! – Android Forums
Time August 9, 2011 at 4:23 pm
[...] these database tutorials: Using your own SQLite database in Android applications | ReignDesign Blog Another Android Blog Android Database Tutorial I learned a lot from them. The first link deals a lot with creating and opening a database. The [...]
Comment from Niyi
Time August 21, 2011 at 2:53 pm
Hello Randall, I discovered your blog last week and I think it is absolutely fantastic. Thank you for posting up such helpful and carefully explained details.
I have carefully worked through your tutorials and have found them excellent. I do apologise but I have been stuck for 2 days now on the cursor to ListView example that you and Siddarth worked through a few months back. I just can’t see where I am going wrong and wondered if you can see anything obviously wrong please. Initially the application would crash when I opened the SimpleCursorAdapter file, now it gives a screen but nothing from the database. My AADBDatabaseManager and SimpleCursorAdapter code are below. Any advice would be very much appreciated. Regards Niyi
/*==================================================================================== */
public Cursor getColumn(String columnName)
{
Cursor cursor = null;
String[] columns = new String[1];
columns[0] = columnName;
try
{
cursor = db.query(TABLE_NAME, columns, null, null, null, null,null);
}
catch (SQLiteException e)
{
Log.e(“Database.addRow”, “Database Error: ” + e.toString());
e.printStackTrace();
}
return cursor;
}}
//=========================================================
package com.android.revision;
import android.app.ListActivity;
import android.database.Cursor;
import android.os.Bundle;
import android.widget.SimpleCursorAdapter;
public class ListPop extends ListActivity {
@Override
protected void onCreate(Bundle savedInstanceState) {
AABDatabaseManager db;
// TODO Auto-generated method stub
super.onCreate(savedInstanceState);
db = new AABDatabaseManager(this);
//String columnName = db.TABLE_ROW_ONE; // better to use constants here
String columnName = “db.TABLE_ROW_ONE”; // better to use constants here
// these arrays are used by the list adapter while connecting the data to the ListView
String[] fields = new String[]{columnName};
//String[] fields = new String[]{“id”,”table_row_one”,”table_row_two”};
int[] views = new int[]{android.R.id.text1};
// create our cursor by calling the database method.
Cursor cursor = db.getColumn(columnName);
// call setListAdapter(), which is a method from the parent class.
setListAdapter(new SimpleCursorAdapter(
this,
android.R.layout.simple_list_item_1,
cursor,
fields,
views));
}
}
Comment from Niyi
Time August 22, 2011 at 6:12 am
Hello Randall,
Please ignore my last post. I have picked through my code tidied it up and got it working. It took a few days but very useful learning. Many thanks again. Niyi
Comment from Randall Mitchell
Time August 22, 2011 at 1:06 pm
Hi Niyi, Glad to hear you found the solution. Sorry I was of no help. I’ve got family in town and am gearing up for school to start Thursday. I didn’t get your post until just now. Best regards, Randall.
Comment from Niyi
Time August 24, 2011 at 8:53 am
Hello Randall,
Absolutely nothing to apologise for. You are helping to educate people for free. Rather than just learning Android by rote I need to spend a lot more time reading what parameters the functions accept and that kind of thing. Best wishes with your school work, if this blog is anything to go by I am sure you will excel.
Best regards
Niyi
Comment from Fredrick
Time September 29, 2011 at 11:18 am
This is the greatest post about SQLite I have seen, Thanks Alot.
In your XML( )
android:id=”@+id/data_table”
Where and what is this (data_table) ?
I cant see it as a field, button or text. It is suppose to be a vurtiral palce where you temporiry store data?
Can you please advise?
Comment from Randall Mitchell
Time September 29, 2011 at 1:06 pm
HI and thanks. “data_table” is actually the table that the data is fed to in the user interface. When a user enters data and submits, the data is entered into the database and then a new row is added to “data_table” for user feedback. The table also feeds back updates and deletions. On page 7 you can see data_table being created in setupViews(), and it is recreated at each db modification (waste of processing but increase in simplicity for the tutorial) in updateTable() on the same page.
Cheers,
Randall
Comment from Fredrick
Time September 29, 2011 at 1:37 pm
Thanks for the quick reply. Can I please get in touch via email: adamblogger@yahoo.co.uk
I am strugling with my project and can need a hand. Moneys will be sorted.
Thanks.
Comment from Nina
Time November 10, 2011 at 7:10 am
Hye Randall,
Thanks for the tutorial. I added another edit text as input named TABLE_ROW_THREE into the system, but I got error saying
11-10 02:39:51.405: ERROR/DB Error(308): android.database.sqlite.SQLiteException: no such column: table_row_three: , while compiling: SELECT id, table_row_one, table_row_two, table_row_three FROM database_table
Where does the possible line located? The error clearly said at SELECT statement, but there is no SELECT statement in the coding. Or is it located somewhere else?
Comment from Randall Mitchell
Time November 10, 2011 at 12:42 pm
Based on the code you sent me and this error message I was able quickly to find at least one error in this section of code in AABDatabaseManager.java:
@Override
public void onCreate(SQLiteDatabase db)
{
// This string is used to create the database. It should
// be changed to suit your needs.
String newTableQueryString = “create table ” +
TABLE_NAME +
” (” +
TABLE_ROW_ID + ” integer primary key autoincrement not null,” +
TABLE_ROW_ONE + ” text,” +
TABLE_ROW_TWO + ” text” +
TABLE_ROW_THREE + ” text” +
“);”;
// execute the query string to the database.
db.execSQL(newTableQueryString);
}
You are missing a comma after the word text in the line:
TABLE_ROW_TWO + ” text” +
That line should look like:
TABLE_ROW_TWO + ” text,” +
Note that the line after that does not need a comma after text. I’ve made that mistake before. It can be hard to notice.
Good luck,
Randall
Comment from Nina
Time November 15, 2011 at 1:21 am
Hi Randall,
How to display the data_table in a new screen? Is it possible to do this without adding another activity?
Comment from Rajesh
Time December 23, 2011 at 7:09 am
Hi, Excellent in-depth tutorial.
One question:
In AndroidManifest.xml, for the TableLayout, you have declared a TableRow with 2 TextViews, but not used it anywhere. What is the purpose of declaring it? Can it be removed?
Comment from Randall Mitchell
Time December 24, 2011 at 2:52 am
Hi Rajesh. Honestly, it’s been a great while since I reviewed this code and don’t remember exactly. From a brief overview of the code in the activity class, I think you are right about these not being used. Possibly I added these for clarity for the readers, or just didn’t notice they were not used.
Comment from Randall Mitchell
Time December 24, 2011 at 2:58 am
@Nina. Sorry I missed answering your post – just noticed it. Here is my opinion on the matter for anyone else who reads this. If you follow the standards of android, a new “screen” is, in a round about way, a new activity. I would suggest creating a new activity if you want a new screen. I generally add the database object to a child of “Application” and access it within each activity through the application. There are other ways to do this (for example, adding a database object to each activity). My post of a very basic application has example code doing this:
http://www.anotherandroidblog.com/2011/03/07/a-very-basic-application
- Randall
Comment from mayur
Time January 23, 2012 at 5:35 am
Fantastic work!!!!!!
thanks a lot!!
Comment from Arpit Trivedi
Time February 5, 2012 at 8:50 am
thanks for your blog. can you give me simple example of android-database for more than one table….
Thanks in advance…
Comment from giumazzi
Time August 22, 2010 at 3:45 pm
I’m new to android world. Can You attach the entire eclipse project to understanda better?
Thank You