This tutorial will demonstrate how to use the database adapter created in “Creating a SQLite Database in Android” to add and get data to and from the database to populate a ListView.
DatabaseQuery.java :
We must first create a class to handle the formatting for the queries. The database adapter takes ArrayLists of String objetcs to add rows to the database, however you will probably want a method to add one cell at a time, then when the contents of the row are completed, add the entire row to the database. We begin by declaring the variables for the ArrayLists.
import java.util.ArrayList;
import android.content.Context;
import android.database.Cursor;
/**
* This class adds multiple entries to the database and pulls them back
* out.
*/
public class DatabaseQuery {
// Variables area
private ArrayList<String> arrayKeys = null;
private ArrayList<String> arrayValues = null;
private ArrayList<String> databaseKeys = null;
private ArrayList<String> databaseKeyOptions = null;
private DBAdapter database;
We then create the constructor which will call the DBAdapter class and create the database.
/**
* Initialize the ArrayList
* @param context Pass context from calling class.
*/
public DatabaseQuery(Context context) {
// Create an ArrayList of keys and one of the options/parameters
// for the keys.
databaseKeys = new ArrayList<String>();
databaseKeyOptions = new ArrayList<String>();
databaseKeys.add("Title");
databaseKeyOptions.add("text not null");
// Call the database adapter to create the database
database = new DBAdapter(context, "testTable", databaseKeys, databaseKeyOptions);
database.open();
arrayKeys = new ArrayList<String>();
arrayValues = new ArrayList<String>();
}
databaseKeys is an ArrayList of all of the column names the database will contain.
databaseKeyOptions is an ArrayList of the options for the column names. This can be a field type such as “text” or “integer”.
In this example I have only added one key here. The DBAdapter class also adds a timeStamp column when a new row is inserted. This makes it easy to sort by time later on. The context is passed from the applications main activity, which we will create at the end. After the DBAdapter object has been created, we then use it to open the pipeline to the database. Now we can query the database.
In the constructor we also initialized two arrays “arrayKeys” and “arrayValues” which will hold all of the data that constitutes one row. This data is appended to the arrays in the appendData method. Once all of the keys and their values have been appended, then the addRow method is called to insert the row to the database.
/**
* Append data to an ArrayList to then submit to the database
* @param key Key of the value being appended to the Array.
* @param value Value to be appended to Array.
*/
public void appendData(String key, String value){
arrayKeys.add(key);
arrayValues.add(value);
}
/**
* This method adds the row created by appending data to the database.
* The parameters constitute one row of data.
*/
public void addRow(){
database.insertEntry(arrayKeys, arrayValues);
}
To run a query and get data from the database, we can specify parameters to sort all of the data in the database.
keys is a String[] of the column headers to return in the results
selection is a String to search for in the columns. Only columns with matching string are returned.
selectionArgs is a String[] with arguments for the selection.
groupBy is the String to group results by.
having is a filter to declare which row groups to include in the cursor.
sortBy is a key to sort the results by.
sortOption specifies the way to sort the data.
/**
* Get data from the table.
*/
public ArrayList<String> getData(String[] keys, String selection, String[]
selectionArgs, String groupBy, String having, String sortBy, String sortOption){
ArrayList<String> list = new ArrayList<String>();
Cursor results = database.getAllEntries(keys, selection,
selectionArgs, groupBy, having, sortBy, sortOption);
while(results.moveToNext())
list.add(results.getString(results.getColumnIndex(sortBy)));
return list;
}
In this example I only wanted the results to include one field, “Title”. I set the parameters for this in my main class. This returns an ArrayList, with only the column title. This is obtained by selecting the string to add to the ArrayList.
list.add(results.getString(results.getColumnIndex(sortBy)));
You can use results.get[String, Integer, etc.] in order to obtain the data in a specific format. You must supply it with a column index number, which can be obtained by results.getColumnIndex(“Title”). In my case sortBy has “Title” since I will be sorting by that key value as well. As it is this code will only return the results of one column of data for many rows. Modifications can be made to return more. One way to achieve this is to create an object and return an array of those objects.
Once we are all done, the pipe to the database must be closed.
/**
* Destroy the reporter.
* @throws Throwable
*/
public void destroy() throws Throwable{
database.close();
}
}
This attempts to close the database and returns an exception if it encounters an error.
DBAdapterTest.java :
This is the main class and will extend ListActivity in order to populate a list of the elements obtained from the database.
import java.util.ArrayList;
import android.app.ListActivity;
import android.os.Bundle;
import android.widget.ArrayAdapter;
public class DBAdapterTest extends ListActivity {
private ArrayList<String> queryString;
private DatabaseQuery query;
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
query = new DatabaseQuery(this);
// Populate the database
query.appendData("Title", "Row One");
query.addRow();
query.appendData("Title", "Row Two");
query.addRow();
// Pull the data from the database
queryString = query.getData(new String[] {"Title"}, null, null, null, null, "Title", " ASC");
try {
query.destroy();
} catch (Throwable e) {
e.printStackTrace();
}
// Set the ListView
setListAdapter(new ArrayAdapter<String>(this,
android.R.layout.simple_list_item_1, queryString));
getListView().setTextFilterEnabled(true);
}
}
A new instance of DatabaseQuery is instantiated and data is added to the database. Once the data has been added, a query is run to pull the data out of the database and populate a ListView. This application adds the same two entries every time it is run. This is simply an quick example of how to use the DBAdapter and changes should be made depending on the needs of your application.
The sorting is done alphabetically on text. Therefore "Row One" comes before "Row Two". However, if "Row Four" were to be used, that would precede the other two.
You can download the full Eclipse project folder below.
[ad#Google Adsense Text Ad Post]
Very good demo..
Thanks
I’m trying to test out this demo but I get an error..
ERROR Error: String types not allowed (at ‘id’ with value ‘+@id/list’).
it works after changing it to ‘@+id/list’
Hey,
Can anyone help me? Im using this sample to help with the app im writing (new to java). what would i need to do to have a database of 12 columns, and to query all or some of those columns?
thanks in advance.
Sorry about that, you are absolutely right, it was a typo on my part. I’ve fixed it and uploaded the new code.
Hey Myreuki,
The column names are added in this portion of the code:
databaseKeys = new ArrayList<String>(); databaseKeyOptions = new ArrayList<String>(); databaseKeys.add("Title"); databaseKeyOptions.add("text not null");All you need to do is add new key values (column names) to the databaseKeys ArrayList. You can also add options to the columns by adding values to the databaseKeyOptions ArrayList.
As for querying data, you can look at DBAdapterTest.java
Essentially the following code is what you modify to get data back:
queryString = query.getData(new String[] {"Title"}, null, null, null, null, "Title", " ASC");Essentially you create an instance of DatabaseQuery, which your database adapter. then you use that to call the query method and pass it the options that you want the query to run with.
These are the parameters for the query method in order:
keys is a String[] of the column headers to return in the results
selection is a String to search for in the columns. Only columns with matching string are returned.
selectionArgs is a String[] with arguments for the selection.
groupBy is the String to group results by.
having is a filter to declare which row groups to include in the cursor.
sortBy is a key to sort the results by.
sortOption specifies the way to sort the data.
Hope this helps.
Hi. Thank you for the demo. I’m unfortunately getting a runtime error “Unable to instantiate activity ComponentInfo {projectPathStuff.MainActivity}:java.lang.ClassNotFoundException.
I checked the manifest and included the needed activities. I don’t know why I’m getting this error. Obviously, I’m new to this. Any advice would be greatly appreciated.
Hello Hanly, first of all, my respect for your coding skils.
As a total noob in programming in java, not even to mention for android, i managed to do the HelloWorld android tutorial (yeah right you might think).
I also found the droiddraw ( you can google it if you not now abouth it yet)desktop application , and managed to get a nice GUI for a “simple app i want to create.
I assume i require sqlite for it.
As good tutorials are realy hard to find for android , i do a request for it here.
My intention is to create a form with multiple textfields.
In my case the textview names are related to metals.
For example:
Copper , stainless-steel,bronze,aluminium,led etc etc
Next to each of them i have a Edittext field.
Inside i want to add numeric value’s like 200 , representing 200 KiloGrams .. after entering the value’s i want to store them , and send true email.
So my request is simple i assume?
But to find such tutorial seems not , or is the notepad tutorial easyer as my request , ifso, then maby learning java is harder as i thought
If I have a stored data, and i want it to show in my list view, how would it be done? Is it similar? Cause yours is a hard code of inserting data.
Great Tutorial but the data is inserted into the DB everytime the app is started thus if I start the app twice on my emulator i end up with a listview like this:
Row One
Row One
Row Two
Row Two
How do I insert unique information? I havent quite gotm y head around SQLite yet!
Chees
Ally, if you look at the DBAdapterTest class, you will see where the code is called every time to add those two values to the database. The class used to perform the database functions is DatabaseQuery, which is covered in more detail here. To add data you call the appendData(String key, String value) passing it a key and value. This is what will get entered into the database. You add all your keys and values, then call the addRow() method to add the key value pairs to the database.
Very good tutorial and thanks for taking time to share with everyone. You have given me some light on the subject. The only question I still have is, “What if you want to populate a lot more data, let say 50 names saved in excel or notepad, how would you do that? Obviously you don’t want to do one by one.
Thank you in advance
Thanks for this awesome DB example! I managed to get my XML file parsed into it nicely. It took me a while to figure out how to integrate this into my app but I finally got it. Now to figure out how to filter and only show newest data… oh yea and to show more than one row (I have 6 rows). Getting all 6 rows into the DB was the easy part lol.
I know it’s something in or around this line but it may take me a bit to figure it out.
queryString = query.getData(new String[] {“Title”}, null, null, null, null, “Title”, ” ASC”);
Thanks again, this DB rocks!
What I meant was I need to figure out how to show all 6 colums, not rows. It shows plenty of rows lol. I can add colums to the query and sort by the one I select but it only shows the colum that I am sorting by. I added the table string into the query since I will be adding another table in the future. Here is what I have (exactly like other examples I’ve seen out there)
queryString = query.getData(“data.xml”, new String[] {“Name”, “Value”, “timeStamp”}, null, null, null, null, “timeStamp”, “DESC”);
I tried creating a list and then adding the list instead of the individual tables but it did the same thing. Another few hours of brain frying reading and surfing and I should just about have it lol.
On another note, I got the clearTable to work. Once I realized how to go through the DatabaseQuery class to do anything with DBAdapter I got it going in no time.
Ok that didnt take too long lol. I had to change a few things to get the database to return multiple columns. In the DatabaseQuery.java file it says list.add(results.getString(results.getColumnIndex(sortBy))); specificaly telling it only to return the sort by column. This is where you would add what all you want added in the results.
String[] names = results.getColumnNames(); \\ gets all columns
int length = names.length;
for(int i = length-1; i >= 0; i–) {
list.add(results.getString(i));
}
Something like this would return all strings in your query sorted by whatever string you choose. In the query itself just add the columns that you want queried. Hope this helps others.
Thanks again Hanly for the awesome tutorial.
Hello,your tutorial is very helpful to me but I have problem when I try to query a single row from my database by a title.
My code is like this:
// —retrieves a particular title—
public Cursor getTitle(String title) throws SQLException {
Cursor mCursor = db.query(true, DATABASE_TABLE, new String[] {
KEY_ROWID, KEY_ISBN, KEY_TITLE, KEY_PUBLISHER }, KEY_TITLE+ “=” + title, null, null, null, null, null);
if (mCursor != null) {
mCursor.moveToFirst();
}
return mCursor;
}
I want to know how can I use one of the “title” to query a single row from the database?
Hey Daisy, can you actually query for anything? Here is what I use to query for _id. I do this so when they select a row the onitemclicklistener queries that row. “columns” is a String[] of rows. All my rows are shown with quotes instead of using key_row.
queryString = query.getData(“data.xml”, columns, “_id =” + id, null, null, null, null, null);
From there you can have am EditText and save it to a String title and query the DB with the string.
In theory lol.
Now, I was wondering how I can input data into my DB outside of an activity. When I remove the “extends activity” from the class it shows error for the line
query = new DatabaseQuery(this);
I need to have this run from a service that auto downloads and inputs the data every minute without calling an activity. The service works but when it calls the activity to input data everything else goes to onPause and the input data activity takes focus then closes. Then the screen flashes and the main activity goes to onResume and refreshes.
great tutorial man. i have a question however. I am trying to use your DBAdapter but i want to use it with an existing database populated offline and added to the assets folder.I then want to be able to retrieve infomation from this database including bitmaps(blobs) and show them onto a listview which grows depending on items retrieved.Please help.this is how my database connection looks like
regards nyasha
public class dataHelper extends SQLiteOpenHelper{
private final Context myContext;
private static String DB_PATH = “data/data/android.vineetyadav.com.dbTest/databases/”;
private static String DB_NAME = “Myfirst.db”;
private SQLiteDatabase myDatabase;
public dataHelper(Context context) {
super(context, DB_NAME, null, 1);
this.myContext = context;
}
public void createDataBase() throws IOException{
// TODO Auto-generated method stub
boolean dbExist = checkDatabase();
if (dbExist) {
}else {
this.getReadableDatabase();
try {
copyDatabase();
} catch (IOException e) {
// TODO Auto-generated catch block
throw new Error(“Error copying database”);
}
}
}
private void copyDatabase() throws IOException {
InputStream myInput = myContext.getAssets().open(DB_NAME);
String outFile = DB_PATH+DB_NAME;
OutputStream myOutput = new FileOutputStream(outFile);
byte[] buffer = new byte[1024];
int length;
while((length = myInput.read(buffer)) > 0) {
myOutput.write(buffer, 0, length);
}
myOutput.flush();
myInput.close();
myOutput.close();
}
private boolean checkDatabase() {
SQLiteDatabase checkDb = null;
try {
String myPath = DB_PATH+DB_NAME;
checkDb = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
} catch (SQLiteException e) {
}
if (checkDb != null) {
checkDb.close();
}
return checkDb!= null ? true:false;
}
public void openDatabase() throws SQLiteException {
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();
}
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
}
public Cursor doQuery(String query) {
Cursor c = myDatabase.rawQuery(query,null);
return c;
}
}
http://mfarhan133.wordpress.com/2010/10/24/database-crud-tutorial-for-android/
Its an excellent tutorial for android DB CRUD with
COMPLETE SOURCE CODE
link is broken
I love anything n everything that is composed nicely… thumbs up you got some good content going on there for sure.
this is great toturial
i’m happy to visit here
Thanks for the tutorial …one of the few that really helped , But I need an example for something I haven`t seen . I have a database in my app and I need to retrieved the data from another activity (another screen with 6 buttons in it ) … The buttons in the other activity are labeled 1-6 … I need to click on a button , say #3 and retrieve the data from row 3 in my database and display it in a textView . I would appreciate any help ….Thanks
hey man great effort i like it…. and learned a lot thank you very much…
thanks for the code. it works. But when i created test.db in SQLite explicitly (with column name ‘name’) and call above code. it works! but it did not update the test.db which i created. also i search through the hard drive but there is only one test.db – the one which i created. where this database stores by default?
Hi, I desired to ask you one thing. Is this site a wordpress web page? We’re contemplating switching our web page from Blogger to wordpress, do you think this is doable? In addition did you construct the following theme by yourself some how? Bless you for the assistance!