SQL Lite DB

The size of database does not reflect in the app memory consumed.

The database files are stored in the device under

/data/data/PACKAGENAME/databases/xyz.db

To acccess the DB from emulator in android3.x, click on device explorer in the bottom right corner of the screen

Create store and retrieve info

public class MainActivity extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        try {
            SQLiteDatabase sqLiteDatabase = this.openOrCreateDatabase("Users", Context.MODE_PRIVATE, null);

            //Storing data
            //comment after first execution
            //NOTE : Database names and table names are different
            // INT(3) is an integer field with max 3 digits
            sqLiteDatabase.execSQL("CREATE TABLE IF NOT EXISTS users (name VARCHAR,age INT(3))");
            sqLiteDatabase.execSQL("INSERT INTO users (name,age) VALUES ('ABC',34)");
            sqLiteDatabase.execSQL("INSERT INTO users (name,age) VALUES ('XYZ',4)");

            //Retrieving data

            Cursor c = sqLiteDatabase.rawQuery("SELECT * FROM users", null);

            int nameIndex = c.getColumnIndex("name");
            int ageIndex = c.getColumnIndex("age");

            //The cursor starts before the first result row,
            // so on the first iteration this moves to the first result if it exists
            while (c.moveToNext()) {
                Log.i("name ", c.getString(nameIndex));
                Log.i("age ", String.valueOf(c.getInt(ageIndex)));
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

Delete database

getApplicationContext).deleteDatabase("DB_NAME");

Basic SQL queries

Raw queries

1) Simple condition

sqLiteDatabase.rawQuery(SELECT * FROM users WHERE age < 18)

2) Combining conditions

sqLiteDatabase.rawQuery(SELECT * FROM users WHERE name = 'kk' AND age = 22)

3) Matching patterns and limiting results

/* Find rows with name starting with A and limit results to 1 */
sqLiteDatabase.rawQuery(SELECT * FROM users WHERE name LIKE 'A%' LIMIT 1)
/* find rows with name that has letter 'o' */
sqLiteDatabase.rawQuery(SELECT * FROM users WHERE name LIKE '%o%')

Execution Queries

1) Create table

/*  // INT(3) is an integer field with max 3 digits */
sqLiteDatabase.execSQL("CREATE TABLE IF NOT EXISTS users (name VARCHAR,age INT(3))")

2) Create table with PRIMARY KEY : Primary key is automatically updated everytime an entry is added

sqLiteDatabase.execSQL("CREATE TABLE IF NOT EXISTS newusers (name VARCHAR, age INT(3), id INT PRIMARY KEY)");

3) Delete entry : If more than one UVW are present, all are deleted. Cant use LIMIT with DELETE in sqllite

sqLiteDatabase.execSQL("DELETE FROM users WHERE name = 'UVW'");

4) Update entry

sqLiteDatabase.execSQL("UPDATE users SET age=2 WHERE name='XYZ'");

5) Delete all rows from a table

sqLiteDatabase.execSQL("DELETE FROM users");

6) Using SQLstatements when looping through arrays

articleIDS = some array;
        titles = some array;
        urls = some array;

        String sql = "INSERT INTO cache (id,title,url) VALUES (? , ? , ?)";
        SQLiteStatement statement = sqLiteDatabase.compileStatement(sql);

        for(int i=0;i<articleIDs.size();i++){
            statement.bindString(1,articleIDs.get(i));
            statement.bindString(2,titles.get(i));
            statement.bindString(3,articleUrls.get(i));
            statement.execute();
        }