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.dbTo acccess the DB from emulator in android3.x, click on device explorer in the bottom right corner of the screen
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();
}
}
}
getApplicationContext).deleteDatabase("DB_NAME");
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();
}