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?

Olivier Heidemann · Accepted Answer · 2010-12-09 13:32:13Z

3

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();
    }
}

answered Dec 9, 2010 at 13:32

Olivier Heidemann

1,33710 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SamRowley Over a year ago

Edited my question after using your code and still have errors.

Olivier Heidemann Over a year ago

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 Over a year ago

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.

McStretch · Accepted Answer · 2010-12-09 13:28:36Z

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.

Buhake Sindi · Accepted Answer · 2010-12-09 13:38:24Z

1

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

edited Dec 9, 2010 at 13:38

Buhake Sindi

89.5k30 gold badges176 silver badges234 bronze badges

answered Dec 9, 2010 at 13:26

Mina Wissa

11k13 gold badges97 silver badges162 bronze badges

4 Comments

Valentin Rocher Over a year ago

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 Over a year ago

I posted the link to avoid writing large amout of text

saxos Over a year ago

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 Over a year ago

@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.

Collectives™ on Stack Overflow

SQLite database in android and java

3 Answers 3

3 Comments

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related