Android SQLite CRUD ( Content value + Prepare statement )

SQLite { INSERT | SELECT | UPDATE | DELET }

CREATE [ insert ]

ContentValues
MySqlite sqlite = new MySqlite(MainActivity.this);
SQLiteDatabase db = sqlite.getWritableDatabase();

ContentValues values = new ContentValues();
values.put("username", username);
values.put("password", password);

db.insert("users", null, values);
db.close();


prepare statement
MySqlite sqlite = new MySqlite(MainActivity.this);
SQLiteDatabase db = sqlite.getWritableDatabase();

SQLiteStatement sql = db.compileStatement("INSERT INTO users (username, password) VALUES (?, ?)");
sql.bindString(1, username);
sql.bindString(2, password);

sql.execute();
db.close();

Insert & Get lastest id
long rowId = sql.executeInsert();

READ [Select]

img src : http://donkcowan.com/blog/2013/4/21/android-cursor-class-and-objects

/* Create instance and define for readable  database */

MySqlite sqlite = new MySqlite(MainActivity.this);
SQLiteDatabase db = sqlite.getReadableDatabase();


/* Using rawQuery method */

/* select all users */
Cursor cursor = db.rawQuery(" SELECT * FROM users", null);
/* select where */
Cursor cursor = db.rawQuery("SELECT * FROM users WHERE id = ?", new String[]{id});
/* user login case */
Cursor cursor = db.rawQuery("SELECT * FROM users WHERE username = ? and password = ?", new String[]{param1, param2});

or

/* Using "query" instead "rawQuery"  */
String[] columns = new String[]{"username", "password", "price"};
String whereClause = "id = ?";
String[] whereArgs = new String[]{id};

Cursor cursor = db.query("member", columns, whereClause, whereArgs, null, null, null);

/*  Get content from cursor */
if(cursor.moveToFirst()){
    while(!cursor.isAfterLast()){
        int username = cursor.getColumnIndex("username");
        String username = cursor.getString(username);
        Log.e("username", username);    // output : username
        cursor.moveToNext();
    }
}

cursor.close();

db.close();

UPDATE

ContentValues
MySqlite sqlite = new MySqlite(MainActivity.this);
SQLiteDatabase db = sqlite.getWritableDatabase();

ContentValues values = new ContentValues();
values.put("username", username);
values.put("password", passw0ord);


db = sqlite.getWritableDatabase();
db.update("users", values, "id = ?", new String[]{id});
db.close();
 


Prepare statement
MySqlite sqlite = new MySqlite(MainActivity.this);
SQLiteDatabase db = sqlite.getWritableDatabase();

SQLiteStatement sql = db.compileStatement("UPDATE users SET username = ?, password = ? WHERE id = ?");
sql.bindString(1, username);
sql.bindString(2, password);
sql.bindString(3, id);

sql.execute();
db.close();

DELETE

MySqlite sqlite = new MySqlite(MainActivity.this);
SQLiteDatabase db = sqlite.getWritableDatabase();
db.delete("users", "id = ?", new String[]{id});
db.close();

Prepare Statement
MySqlite sqlite = new MySqlite(MainActivity.this);
SQLiteDatabase db = sqlite.getWritableDatabase();

SQLiteStatement sql = db.compileStatement("DELETE FROM users WHERE id = ?");
sql.bindString(1, id);

sql.execute();
db.close();

PrepareStatement with transaction

ขั้นตอนโดยรวมของการใช้ PrepareStatement กับ transaction

• Create instance of Isqlite class                           
• Create instance of SQLiteOpenHelper class then define getReadableDatabase , getWritableDatabase

• begintransaction                <<< transaction   
• 
  
• CompileStatement > BindParams > Execute
• 
  
• 
  
• setTransactionSuccessful  <<< transaction
• endTransaction                  <<< transaction
• db.close();

Summary

> new instand from MyDatabaseClass [extends SQLiteOpenHelper]
> new instand SQLiteOpenHelper define getwritable || getreadable
> compile statement > bindparams > execute
> db.close();



ใช้ prepare statement ตอนไหน ?
- statement ที่ต้องการการดำเนินการแบบ multiple time (หลายครั้งในเวลาเดียวกัน)
- ต้องการความถูกต้องของข้อมูล "