SQLite database in android and java

Question

I am having a bit of difficulty understanding the concepts of how SQLite databases work in android. I have created a DatabaseHelper.java class which is as follows:

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DatabaseHelper extends SQLiteOpenHelper {
 private static final String DATABASE_NAME = "library.db";
 public static final String TITLE = "title";
 public static final String MUSICTITLE = "musictitle";
 public static final String AUTHOR = "author";
 public static final String ARTIST = "artist";
 public DatabaseHelper(Context context) {
 super(context, DATABASE_NAME, null, 1);
 }
 @Override
 public void onCreate(SQLiteDatabase db) {
 db.execSQL( "CREATE TABLE music (_id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, artist TEXT);");
 db.execSQL( "CREATE TABLE books (_id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, author TEXT);");
 db.execSQL( "CREATE TABLE movies (_id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT);");
 }
 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 android.util.Log.v("Constants",
 "Upgrading database which will destroy all old data");
 db.execSQL("DROP TABLE IF EXISTS books");
 db.execSQL("DROP TABLE IF EXISTS music");
 db.execSQL("DROP TABLE IF EXISTS movies");
 onCreate(db);
 }
}

As far as i'm aware this does work and has been checked using the adb console.

My question is how do I query this database within the app from a different activity, for example I want in my app to query the database: SELECT title FROM books; which will return me the titles from the book table, how do I go about connecting to the database, running this query then storing all the results into an array?

----------------------------------Edit----------------------------------------

Right so in the part where I want to query I have placed this code:

if (category.equals("Books")){
 img.setImageResource(R.drawable.book);
 ArrayList<String> list = new ArrayList<String>();
 dh = new DatabaseHelper(this);
 SQLiteDatabase db = dh.getReadableDatabase();
 Cursor cursor = db.query("books", new String[] { "title" }, null, null, null, null, "title desc");
 if (cursor.moveToFirst()) {
 do {
 list.add(cursor.getString(1));
 } while (cursor.moveToNext());
 }
 if (cursor != null && !cursor.isClosed()) {
 cursor.close();
 }
 list.add("Test");
 cont_array = (String[]) list.toArray();
 db.close();
 }

Which passes the Category down prom the previous activity. From Olivier's answer. This code crashes the application and I have no idea why. the variable cont_array is used after this if statement to populate a ListView. Anybody got any ideas where I am going wrong, there is nothing in LogCat to tell me where it is failing?

Answer 1

Inside your activity do the following:

public class DBAct extends Activity {
 private DataHelper dh;
 @Override
 public void onCreate(Bundle savedInstanceState) {
 super.onCreate(savedInstanceState);
 //init your activity here
 //e.g. setContentView(R.layout.main);
 dh = new DataHelper(this);
 SQLiteDatabase db = dh.getReadableDatabase();
 Cursor cursor = db.query("books", new String[] { "title" }, null, null, null, null, "title desc");
 if (cursor.moveToFirst()) {
 do {
 list.add(cursor.getString(0));
 } while (cursor.moveToNext());
 }
 if (cursor != null && !cursor.isClosed()) {
 cursor.close();
 }
 db.close();
 }
}

• SamRowley: Edited my question after using your code and still have errors.
• Olivier Heidemann: Without the concrete error message, it is difficult to say. But there is one common pitfall in your code. Try using cont_array = (String[]) list.toArray(new String[list.size()])
• SamRowley: I thin kI have sorted this problem, but the fetching of the data doesn't seem to be bringing anything back, so list.add(cursor.getString(0)); doesn't seem to be doing anything. I tested this using a TextView and putting the result into that text view but it returns false.

Answer 2

In order to read from and write to the database you need to first grab an instance of a SQLiteDatabase object in one of your Activitys. Using your DatabaseHelper class, you can do this by calling databaseHelper.getReadableDatabase() or databaseHelper.getWritableDatabase() depending on what action you need to take (read/write).

After you have an instance of SQLiteDatabase you can use its various helper methods to perform CRUD operations. For instance, in order to run the query SELECT title FROM books, you can use one of the various query() methods. There are also insert(), update(), and delete() methods available. These methods will sometimes return a Cursor, which can then be used in objects such as ListView to traverse a result set from the database. I would recommend that you use a Cursor versus stuffing results into an Array if possible, simply because there are a decent amount of methods and objects that play nice with Cursors.

Note: Make sure to close any instance of SQLiteDatabase after you've completed your reads/writes to avoid any issues with resource leaks.

If you need any more information I recommend reading the specifics of each class in the API Documentation.

Answer 3

please check my post on SQLite http://android-pro.blogspot.com/2010/10/using-sqlite-database-with-android.html

there is a sample application, you can downlaod it and check for things you want

• Valentin Rocher: Please do not post links instead of answers. Post the answer instead, and if it needs more explanation, the link. This way, if the link drops dead, the answer will still be available.
• Mina Wissa: I posted the link to avoid writing large amout of text
• saxos: Sorry, I don't agree with you Valentin. In my opinion the link seems very useful and it's also kinda difficult to post well formatted text here.
• user1228: @Valen @mina @saxos At least a couple times a week a question is rendered useless because the referenced link is dead. At least, those are the ones we get flags for.