Android数据库操作：表清理与CRUD功能详解笔记

表结构

private final String storeColumns &＃61; "store_id,store_name,store_area_id,store_area_name,user_id,user_name";

清空表数据&＃xff0c;并根据表中的字段入库

/**

* &＃64;param storeList

*/

public void insertStore(List storeList) {

if (storeList.size() &＃61;&＃61; 0) {

Log.i(TAG, "BasicDataManager---insertStore--数据为空&＃xff0c;不执行插入");

return;

}

SQLiteDatabase db &＃61; dbOpenHelper.getWritableDatabase();

try {

db.beginTransaction();

//先删除

db.execSQL("delete from wd_store", new Object[]{});

for (int i &＃61; 0; i

StoreEntity entity &＃61; storeList.get(i);

db.execSQL(

"insert into wd_store(" &＃43; storeColumns &＃43; ") values(?,?,?,?,?,?)",

new Object[]{entity.getStoreId(), entity.getStoreName(), entity.getAreaId(), entity.getAreaName(),

UserManager.getUser().getUserId(),

UserManager.getUser().getUserName()});

}

db.setTransactionSuccessful();

Log.i(TAG, "BasicDataManager---insertStore成功!");

} catch (Exception e) {

e.printStackTrace();

Log.i(TAG, "BasicDataManager---insertStore错误&＃xff1a;" &＃43; e.getMessage());

} finally {

db.endTransaction();

}

}

查询并

/**

* &＃64;return

*/

public List getArea() {

Cursor cursor &＃61; null;

List list &＃61; new ArrayList();

SQLiteDatabase db &＃61; dbOpenHelper.getWritableDatabase();

try {

cursor &＃61; db.rawQuery(

"select store_area_id,store_area_name from wd_store where user_id &＃61; ? group by store_area_name",

new String[]{UserManager.getUser().getUserId()});

while (cursor.moveToNext()) {

String areaId &＃61; cursor.getString(cursor

.getColumnIndex("store_area_id"));

String areaName &＃61; cursor.getString(cursor

.getColumnIndex("store_area_name"));

StoreEntity entity &＃61; new StoreEntity();

entity.setAreaId(areaId);

entity.setAreaName(areaName);

list.add(entity);

}

Log.i(TAG, "BasicDataManager---getArea成功!");

} catch (Exception e) {

e.printStackTrace();

Log.i(TAG, "BasicDataManager---getArea错误&＃xff1a;" &＃43; e.getMessage());

} finally {

if (cursor !&＃61; null) {

cursor.close();

cursor &＃61; null;

}

}

return list;

}

/**

* 查询该用户的所有区域

*

* &＃64;return

*/

public List getSXTTArea() {

Cursor cursor &＃61; null;

List list &＃61; new ArrayList();

SQLiteDatabase db &＃61; dbOpenHelper.getWritableDatabase();

try {

// cursor &＃61; db.rawQuery(

// "select distinct store_id,store_name from sxtt_store where user_id &＃61; ? and store_flag &＃61;?",

// new String[]{UserManager.getUser().getUserId(),"市"});

// cursor &＃61; db.rawQuery(

// "select store_id,store_name from sxtt_store where user_id &＃61; ? and store_flag &＃61;&＃39;省&＃39; or store_flag &＃61; &＃39;市&＃39; order by store_name",

// new String[]{UserManager.getUser().getUserId()});

cursor &＃61; db.rawQuery("select distinct store_id,store_name from sxtt_store where user_id &＃61; ? and store_flag in(&＃39;省&＃39;,&＃39;市&＃39;) ", new String[]{UserManager.getUser().getUserId()}

);

while (cursor.moveToNext()) {

String areaId &＃61; cursor.getString(cursor

.getColumnIndex("store_id"));

String areaName &＃61; cursor.getString(cursor

.getColumnIndex("store_name"));

StoreEntity entity &＃61; new StoreEntity();

entity.setAreaId(areaId);

entity.setAreaName(areaName);

list.add(entity);

}

Log.i(TAG, "BasicDataManager---getArea成功!");

} catch (Exception e) {

e.printStackTrace();

Log.i(TAG, "BasicDataManager---getArea错误&＃xff1a;" &＃43; e.getMessage());

} finally {

if (cursor !&＃61; null) {

cursor.close();

cursor &＃61; null;

}

}

return list;

}

多个条件查询&＃xff0c;采用and并使用distinct去重

/**

* &＃64;return

*/

public List getSXTTStore(String storeId&＃xff0c;String storeFlag) {

Cursor cursor &＃61; null;

List list &＃61; new ArrayList();

SQLiteDatabase db &＃61; dbOpenHelper.getWritableDatabase();

try {

cursor &＃61; db.rawQuery("select distinct store_id,store_name from sxtt_store where user_id &＃61; ? and store_area_id&＃61;? and store_flag&＃61;?", new String[]{UserManager.getUser().getUserId(), storeId,storeFlag});

while (cursor.moveToNext()) {

String areaId &＃61; cursor.getString(cursor

.getColumnIndex("store_id"));

String areaName &＃61; cursor.getString(cursor

.getColumnIndex("store_name"));

StoreEntity entity &＃61; new StoreEntity();

entity.setAreaId(areaId);

entity.setAreaName(areaName);

list.add(entity);

}

Log.i(TAG, "BasicDataManager---getArea成功!");

} catch (Exception e) {

e.printStackTrace();

Log.i(TAG, "BasicDataManager---getArea错误&＃xff1a;" &＃43; e.getMessage());

} finally {

if (cursor !&＃61; null) {

cursor.close();

cursor &＃61; null;

}

}

return list;

}