insert插入数据
public void add(Map<String, String> data) { SQLiteDatabase database = recordHelper.getWritableDatabase(); ContentValues value = new ContentValues(); for (String key : data.keySet()) { //map.keySet()返回的是所有key的值 String values = data.get(key);//得到每个key多对用value的值 value.put(key, values); } database.insert(tab, null, value);
//插入完成后关闭,以免造成内存泄漏 database.close(); }
delete删除数据
//删除所有 public void deleteAllRecords() { SQLiteDatabase database = recordHelper.getWritableDatabase(); database.execSQL("delete from records"); database.close(); } //删除一条 public void deleteOneRecords(String id) { SQLiteDatabase database = recordHelper.getWritableDatabase(); // database.execSQL("delete from records where name=?"+name); database.delete("records", "id=?", new String[]{id}); database.close(); }
updata修改数据
//更新 public void updata(Map<String, String> data) { SQLiteDatabase database = recordHelper.getWritableDatabase(); ContentValues value = new ContentValues(); for (String key : data.keySet()) { //map.keySet()返回的是所有key的值 String values = data.get(key);//得到每个key多对用value的值 value.put(key, values); } database.update(tab, value, "id=?", new String[]{data.get("id")}); database.close(); }
query查找数据
//获取全部
public List<BuyerMsgBean> getRecordsList() {
List<BuyerMsgBean> recordsList = new ArrayList<>();
BuyerMsgBean bean = null;
SQLiteDatabase database = recordHelper.getReadableDatabase();
Cursor cursor = database.query("records", null, null, null, null, null, null);
while (cursor.moveToNext()) {
bean = new BuyerMsgBean();
bean.id = cursor.getString(cursor.getColumnIndexOrThrow("id"));
bean.updataid = cursor.getString(cursor.getColumnIndexOrThrow("updataid"));
bean.ywType = cursor.getString(cursor.getColumnIndexOrThrow("ywType"));
recordsList.add(bean);
}
cursor.close();
//关闭数据库
database.close();
return recordsList;
}
//模糊查询
public List<String> querySimlarRecord(String record) {
String queryStr = "select * from records where name like '%" + record + "%' order by name ";
List<String> similarRecords = new ArrayList<>();
Cursor cursor = recordHelper.getReadableDatabase().rawQuery(queryStr, null);
while (cursor.moveToNext()) {
String name = cursor.getString(cursor.getColumnIndexOrThrow("name"));
similarRecords.add(name);
}
cursor.close();
return similarRecords;
}
//模糊查询 public List<String> querySimlarRecord(String record) { String queryStr = "select * from records where name like '%" + record + "%' order by name "; List<String> similarRecords = new ArrayList<>(); Cursor cursor = recordHelper.getReadableDatabase().rawQuery(queryStr, null); while (cursor.moveToNext()) { String name = cursor.getString(cursor.getColumnIndexOrThrow("name")); similarRecords.add(name); } cursor.close(); return similarRecords; }
数据库的更新
参考地址:http://www.cnblogs.com/liqw/p/4264925.html
一个软件有三个版本:V1,V2,V3
如果第一次安装这个软件,不管哪个版本,都走onCreate方法,不走onUpgrade,之后如果升级只走onUpgrade,不走onCreate
onCreate方法只是在第一次安装app的时候会调用,之后的数据库要更改的话,必须使用数据库版本上升,或者卸载了重新安装
v1.0 --> v3.0 不走onCreate,走onUpgrade
v2.0 --> v3.0 不走onCreate,走onUpgrade
v3.0(直接安装v3.0) 走onCreate,不走onUpgrade
下面是V1升级V3D代码:
在oncreate里面执行onUpgrade
在onUpgrade里面判断当前版本
public
class
DBHelper
extends
SQLiteOpenHelper {
private
static
final
String DATABASE_NAME =
"mall.db"
; //数据库名字
// 请不要修改FIRST_DATABASE_VERSION的值,其为第一个数据库版本大小
private
final
int
FIRST_DATABASE_VERSION =
1000
;
private
static
final
int
DATABASE_VERSION =
1002
;//最新的数据库版本
private
static
DBHelper instance =
null
;
public
DBHelper(Context context) {
super
(context, DATABASE_NAME,
null
, DATABASE_VERSION);
}
public
synchronized
static
DBHelper getInstance(Context context) {
if
(instance ==
null
) {
instance =
new
DBHelper(context);
}
return
instance;
}
@Override
public
void
onCreate(SQLiteDatabase db) {
db.execSQL(SQL.CREATE_TABLE_FAVORITE);
// 若不是第一个版本安装,直接执行数据库升级
onUpgrade(db, FIRST_DATABASE_VERSION, DATABASE_VERSION);
}
@Override
public
void
onUpgrade(SQLiteDatabase db,
int
oldVersion,
int
newVersion) {
// 使用for实现跨版本升级数据库 先把V1升级到V2 再升级到V3
for
(
int
i = oldVersion; i < newVersion; i++) {
switch
(i) {
case
1000
:
upgradeToVersion1001(db);//V1升级到V2
break
;
case
1001
:
upgradeToVersion1002(db);//V2升级到V3
break
;
default
:
break
;
}
}
}
private
void
upgradeToVersion1001(SQLiteDatabase db){
// favorite表新增1个字段
String sql1 =
"ALTER TABLE "
+SQL.T_FAVORITE+
" ADD COLUMN name VARCHAR"
;
db.execSQL(sql1);
}
private
void
upgradeToVersion1002(SQLiteDatabase db){
// favorite表新增2个字段,添加新字段只能一个字段一个字段加,sqlite有限制不予许一条语句加多个字段
String sql1 =
"ALTER TABLE "
+SQL.T_FAVORITE+
" ADD COLUMN message VARCHAR"
;
String sql2 =
"ALTER TABLE "
+SQL.T_FAVORITE+
" ADD COLUMN type VARCHAR"
;
db.execSQL(sql1);
db.execSQL(sql2);
}
}