Database in android studio

Question

I'm a windows phone developer and newly I started developing android apps using android studio.

I need to create a database and store in it values and retrieve the updated values on screen, so I need help in:

1. Creating the database.
2. How to show values from the database on screen?

Solution 1

to create database , you need to extend SQLiteOpenHelper and need a constructor that takes Context. lets say you name this class DBOperator. The table creation process will look something like this ,

public class DbOperator extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "DB_NAME";
    protected static final String FIRST_TABLE_NAME = "FIRST_TABLE";
    protected static final String SECOND_TABLE_NAME = "SECOND_TABLE";

    public static final String CREATE_FIRST_TABLE = "create table if not exists "
            + FIRST_TABLE_NAME
            + " ( _id integer primary key autoincrement, COL1  TEXT NOT NULL, COL2 TEXT NOT NULL,COL3 TEXT, COL4 int, COL5 TEXT,"
            + "COL6 TEXT,COL7 REAL, COL8 INTEGER,COL9 TEXT not null);";

    public static final String CREATE_SECOND_TABLE = "create table if not exists "
            + SECOND_TABLE_NAME+.........

    public DbOperator(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_SFIRST_TABLE);
        db.execSQL(CREATE_SECOND_TABLE);
        //db.close();
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //THIS WILL BE EXECUTED WHEN YOU UPDATED VERSION OF DATABASE_VERSION
        //YOUR DROP AND CREATE QUERIES
    }
}

---

Now your data manipulation class ( add, delete , update ) will look something like this ,

public class FirstTableDML extends DbOperator {

    public FirstTableDML(Context context) {
        super(context);
    }

    private static final String COL_ID = "_id";
    private static final String COL1 = "COL1";
    private static final String COL2 = "COL2";
    ........
    .......

    public void deleteFirstTableDataList(List<FirstTableData> firstTableDataList) {
        for (FirstTableData data : firstTableDataList)
            deleteFirstTableDetailData(data);
    }
    public void deleteFirstTableDetailData(FirstTableData item) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(FIRST_TABLE_NAME, item.getId() + "=" + COL_ID, null);
        db.close();
    }
    /**this method retrieves all the records from table and returns them as list of  
     FirstTableData types. Now you use this list to display detail on your screen as per your 
    requirements.
    */
    public List< FirstTableData > getFirstTableDataList() {
        List< FirstTableData > firstTableDataList = new ArrayList< FirstTableData >();
        String refQuery = "Select * From " + FIRST_TABLE_NAME;
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(refQuery, null);
        try {
            if (cursor.moveToFirst()) {
                do {
                    FirstTableData itemData = new FirstTableData();

                    itemData.setId(cursor.getInt(0));

                    itemData.setCol1(cursor.getString(1));
                    itemData.setCol2(cursor.getInt(2));
                    .....
                    .....

                    firstTableDataList.add(itemData);
                } while (cursor.moveToNext());
            }
        } finally {

            db.close();
        }

        Collections.sort(itemDataList);
        return itemDataList;
    }

    public int  addFirstTableData(FirstTableData data) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();

        values.put(COL1, data.getCol1());
        values.put(COL2, data.getCol2());
        .....
        .....
        long x=db.insert(FIRST_TABLE_NAME, null, values);
        db.close();
        return (int)x;
    }

    public void updateItemDetailData(FirstTableData data) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();

        values.put(COL1, data.getCol1());
        values.put(COL2, data.getCol2());
        values.put(COL3, data.getCol3());
        .....
        .....


        db.update(FIRST_TABLE_NAME, values, COL_ID + "=" + data.getId(),    null);
        db.close();
    }

}

P.S : *Data class are POJO data class representing the corresponding table. Since you said you are not totally new to these, I have not provided any helper comments as most of the method names are self explanatory. Hope it helps you to get started.

Solution 2

To creating a database for Android application, there are 2 ways:

1. Create database and tables using Code
2. Use existing database

1) Create database and tables using Code

In this scenario, you have to write a class and code to create database and tables for it. You have to use different classes and interfaces like SQLiteOpenHelper, SQLiteDatabase, etc. Check answer posted by Jimmy above.

2) Use existing database

In this scenario, you can use your existing sqlite database inside your android application. You have to place database file inside assets folder and write a code to copy that existing database on to either internal or external storage.

Regarding best scenario, I would say it's depend on the application functionality and nature, if your database is small then you should go with 1st scenario and if your database is large with many tables then you should go with 2nd scenario because you would be creating database using any GUI based SQLite browser and which would help you to make less mistakes. (When I say less mistakes using GUI, believe me there are chances of creating tables by code).

How to show values from the database on screen?

For that you have to write a SQL query which gives you Cursor in return which is a set of resultant data, so you have to iterate through the cursor data and prepare a set of data in terms of ArrayList or Array or HashMap.

You can display this set of data in ListView or GridView.

P.S. I am not posting links to any tutorials or examples as there are plenty of information/examples available on web, so suggesting you to search around the given points.

Solution 3

A good way to start is to read about Storage Options on the official Android documentation website: http://developer.android.com/guide/topics/data/data-storage.html