Androidでsqliteを操作するサンプルコード
1.テーブルの作成
db.execSQL(“CREATE TABLE IF NOT EXISTS directions (dirTag STRING PRIMARY KEY, dirNameKey STRING, dirTitleKey STRING, dirRouteKey STRING)");
db.execSQL(“CREATE TABLE IF NOT EXISTS routes (route STRING PRIMARY KEY, color INTEGER, oppositecolor INTEGER, pathblob BLOB)");
db.execSQL(“CREATE TABLE IF NOT EXISTS stops (tag STRING PRIMARY KEY, lat FLOAT, lon FLOAT, title STRING)");
db.execSQL(“CREATE TABLE IF NOT EXISTS stopmapping (route STRING, tag STRING, dirTag STRING, PRIMARY KEY (route, tag))"); db.execSQL(“CREATE TABLE IF NOT EXISTS directionmapping (direction STRING, stop STRING, PRIMARY KEY(direction, stop))"); db.execSQL(“CREATE TABLE IF NOT EXISTS IDX_directionmapping on directionmapping (direction)");
db.execSQL(“CREATE INDEX IF NOT EXISTS IDX_routemapping ON stopmapping (route)");
db.execSQL(“CREATE INDEX IF NOT EXISTS IDX_stopmapping ON stopmapping (tag)");
2.データを挿入する
values.put(“dirTag", direction.getDirTag());
values.put(“dirNameKey", direction.getDirNameKey());
values.put(“dirTitlekey", direction.getDirTitlekey());
values.put(“dirRouteKey", direction.getDirRouteKey());
long id = db.insert(“directions", null, values);
3.データをクエリする
private static final String DEFAULT_CURRENT_ROUTE_TAG = “select tag from stopmapping where route=?";
private static final String[] DEFAULT_CURRENT_SELECTION = new String[]{“751"};
Cursor c = db.rawQuery(querySQL, selection);
Cursor cursor = db.rawQuery(“select count(*) as count from routes",null);
4.Javaコード：
public List<String> getDirection(String dirRouteKey){
directionList=new ArrayList<String>();
String selection="dirRouteKey="+dirRouteKey;
Cursor direction=db.query(“directions",null, selection, null,null,null,null);
if(direction.moveToFirst()){
for(int i=0,num=direction.getCount();i<num;i++){
direction.moveToPosition(i);
directionList.add(direction.getString(direction.getColumnIndex(“dirTag")));
}
}
direction.close();
return directionList;
}