(Android) Sqlite & Related API

1. Extend SQLiteOpenHelper

 

public class DatabaseHelper extends SQLiteOpenHelper{
 
 private final static int VERSION = 1;

 public DatabaseHelper(Context context, String name, CursorFactory factory,
   int version) {
  super(context, name, factory, version);
 }
 
 public DatabaseHelper(Context context) {
  super(context, SQLs.SQL_DB_NAME,  null , VERSION);
 }

 @Override
 public void onCreate(SQLiteDatabase db) {
  db.execSQL(SQLs.SQL_CREATETABLE);
 }

 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  db.execSQL("Drop table if exists " + SQLs.SQL_TABLE_USER);
 }

}

 

2. Create and open database

private DatabaseHelper dbHelper;
private SQLiteDatabase db;

dbHelper = new DatabaseHelper(this);
db = dbHelper.getWritableDatabase();

 

3. Run SQL

db.beginTransaction();
  try {
   Cursor cursor = db.query(SQLs.SQL_TABLE_USER, new String[] { "name",
     "job", "sex" }, null, null, null, null, null, null);
   while(cursor.moveToNext()){
    String name = cursor.getString(0);
    String job = cursor.getString(1);
    long sex = cursor.getLong(2);
    if(sex==1){
    }
    if(sex==2){
    }
   }
   db.setTransactionSuccessful();
  } finally {
   db.endTransaction();
  }

 

4. Close Database

if (db != null && db.isOpen()) {
   db.close();
}

if (dbHelper != null) {
   dbHelper.close();
}

 

public class SQLs {

 public final static String SQL_TABLE_USER = "user";

 public final static String SQL_DB_NAME = "db_user";

 public static final String SQL_CREATETABLE = "CREATE TABLE \"user\" "
   + "(\"id\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , "
   + "\"name\" VARCHAR NOT NULL , \"job\" VARCHAR NOT NULL , "
   + "\"sex\" INTEGER NOT NULL )";

 public static final String SQL_INSERT = "insert into user ('name', 'job', 'sex') values "
   + "('Palmer', 'Software Engineer', 1);";

 public static final String SQL_QUERY = "select * from user;";

 public static final String SQL_UPDATE = "update user set job = 'Millionaire' where job = 'Software Engineer';";

 public static final String SQL_DELETE = "delete from user;";
}