さて今回はSQLiteDatabaseを使用してみたいと思います。下記の雑誌の記事を参考に、より簡単にしたものを作っています
やりたいこと
2. DBにdata挿入
3. DBからdataを読み込み
4. SpinnerやListViewを使用してdataを表示
Spinnerで地域名を選ぶとそのAddressを持ったshop名をListViewに表示
実装の要点を下記にまとめます
1. SQLiteOpenHelper拡張classを作成
public class MySQLiteOpenHelper extends SQLiteOpenHelper {
@Override
public void onCreate(SQLiteDatabase db) {
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
2. SQLiteDatabase objectを使用し、SQLiteOpenHelper#onCreate()でTableを作成
private static final String CREATE_SHOP_TABLE = "create table " + MyDao.SHOP_TABLE_NAME + " (" +
MyDao.SHOP_ID + " integer primary key autoincrement, " +
MyDao.SHOP_NAME + " text not null, " +
MyDao.ADDRESS1 + " text not null, " +
MyDao.ADDRESS2 + " text not null, " +
MyDao.ADDRESS3 + " text not null)";
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_SHOP_TABLE);
}
3. DAO(Data Access Object) classにData挿入のmethodを定義
public class MyDao {
public static void insert(SQLiteDatabase db, String shopName, String address1, String address2, String address3){
ContentValues values = new ContentValues();
values.put(SHOP_NAME, shopName);
values.put(ADDRESS1, address1);
values.put(ADDRESS2, address2);
values.put(ADDRESS3, address3);
db.insert(SHOP_TABLE_NAME, null, values);
}
public static Cursor getAll(SQLiteDatabase db){
return db.rawQuery(GET_ALL, null);
}
public static Cursor getAllDistinctAddress2(SQLiteDatabase db){
return db.rawQuery(GET_ALL_DISTINCT, null);
}
public static Cursor getAllByAddress2(SQLiteDatabase db, String address2){
return db.rawQuery(GET_ALL_BY_ADDRESS2, new String[]{address2});
}
}
4. DAO classにData読み込みのmethodを定義
public class MyDao {
public static final String SHOP_TABLE_NAME = "shop";
public static final String SHOP_ID = "_id";
public static final String SHOP_NAME = "shop_name";
public static final String ADDRESS1 = "address1";
public static final String ADDRESS2 = "address2";
public static final String ADDRESS3 = "address3";
private static final String GET_ALL = "select * from " + SHOP_TABLE_NAME;
private static final String GET_ALL_DISTINCT = "select distinct " + ADDRESS2 + " from " + SHOP_TABLE_NAME;
private static final String GET_ALL_BY_ADDRESS2 = "select * from " + SHOP_TABLE_NAME + " where " + ADDRESS2 + " = ?";
public static Cursor getAll(SQLiteDatabase db){
return db.rawQuery(GET_ALL, null);
}
public static Cursor getAllDistinctAddress2(SQLiteDatabase db){
return db.rawQuery(GET_ALL_DISTINCT, null);
}
public static Cursor getAllByAddress2(SQLiteDatabase db, String address2){
return db.rawQuery(GET_ALL_BY_ADDRESS2, new String[]{address2});
}
}
5. Adapterに取得したDataをbindしSpinnerやListViewにset
他、覚えておきたい点まとめます
· SQLiteOpenHelper#onCreate()ははじめてDBを作成する時のみ呼ばれる
· Activity#startManagingCursor()でCursorのlife cycleをActivityに任せられる
· Transaction処理が多い場合SQLiteDatabase#beginTransaction()をcall、SQLiteDatabase#setTransactionSuccessful()でcommit、SQLiteDatabase
0 件のコメント:
コメントを投稿