数据库一无所知,最近用到,百度+问群,刚刚入门,自己总结一下做个笔记:
1,创建数据库:继承SQLiteOpenHelper
public class DatabaseHelper extends SQLiteOpenHelper {
private String Tag = "DatabaseHelper";
public DatabaseHelper(Context context, String name,
CursorFactory factory, int version) {
super(context, name, factory, version);
// TODO Auto-generated constructor stub
}
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
LogUtils.i(Tag, "onCreate");
try {
//本机处理操作同步到云端后的记录
db.execSQL("CREATE TABLE devicesOperation_tb ("
+ "directory VARCHAR (50) NOT NULL,"
+ "rootDir VARCHAR (20) NOT NULL,"
+ "parentDir VARCHAR (20) NOT NULL,"
+ "fileName VARCHAR (50) NOT NULL,"
+ "time VARCHAR (50) NOT NULL"
+ ");");
//本机所有文件记录,用于和操作后的对比,devicesFile_tb记录没有,devicesOperation_tb记录有的话,则是删除操作
db.execSQL("CREATE TABLE devicesFile_tb ("
+ "directory VARCHAR (50) NOT NULL,"
+ "rootDir VARCHAR (20) NOT NULL,"
+ "parentDir VARCHAR (20) NOT NULL,"
+ "fileName VARCHAR (50) NOT NULL,"
+ "time VARCHAR (50) NOT NULL"
+ ");");
//云端操作记录表
db.execSQL("CREATE TABLE serverOperation_tb ("
+ "fileName VARCHAR (50) NOT NULL,"
+ "rootDir VARCHAR (20) NOT NULL,"
+ "parentDir VARCHAR (20) NOT NULL,"
+ "operation VARCHAR (10) NOT NULL,"
+ "filePath VARCHAR (100)"
+ ");");
} catch (Exception e) {
// TODO: handle exception
LogUtils.i(Tag, "onCreate:"+e);
}
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
}
}
db.execSQL("CREATE TABLE devicesOperation_tb ("
+ "directory VARCHAR (50) NOT NULL,"
+ "rootDir VARCHAR (20) NOT NULL,"
+ "parentDir VARCHAR (20) NOT NULL,"
+ "fileName VARCHAR (50) NOT NULL,"
+ "time VARCHAR (50) NOT NULL"
+ ");");
这一句是创建数据库,devicesOperation_tb:表名;后面都是字段名,每一句以“,”逗号结束,最后一句不用
2,获取执行创建数据库,得到数据库:
mDatabaseHelper = new DatabaseHelper(mContext, DATABASE_NAME, null, 1);
mSqLiteDatabase = mDatabaseHelper.getReadableDatabase();
只new是得不到数据库的,必须要getReadableDatabase()或者getWriteableDatabase()一下才能看到数据库,这句执行后,就可以在data/data/你的项目包名/databases下面看到有一个devicesOperation_tb.db的数据库了
3,数据库添加数据,也就是insert语句:
ContentValues cv = new ContentValues();
cv.put(DevicesOperationTB.directory, f.toString());
cv.put(DevicesOperationTB.rootDir, FilePath.split("/")[0]);
cv.put(DevicesOperationTB.parentDir, FilePath.split("/")[1]);
cv.put(DevicesOperationTB.fileName, FilePath.split("/")[2]);
mSqLiteDatabase.insert(DevicesFileTB.devicesFile_tb, null, cv);
说明:ContentValues :作用和amp差不多,放键值对的,当然key值要和你数据库里面的字段对应,要不然就报错了mSqLiteDatabase.insert(DevicesFileTB.devicesFile_tb, null, cv);要插入的表名,后面是cv,插入成功返回行号,失败返回-1
4,数据库查询,query语句:
Cursor mCursor = mSqLiteDatabase.query(DevicesOperationTB.devicesOperation_tb, null, "directory = ?", new String[] {f.toString()}, null, null, null);
参数1:表名
参数2:查询到后返回这条数据那一列的值,如果null则返回整条数据的值
参数3:要查询的列的字段
参数4:要查询的值,参数3和参数4是对应的,如果查询的是二列,后面的参数4也就2个值,
例:参数3是"directory = ? and fileName = ?" 那么参数4就应该是new String[] {f.toString(),“文件名.txt”}
注意:参数3里面是二哥条件的话要用and而不是&
5,遍历:
你查询到结果,你要遍历
Cursor mCursor = mSqLiteDatabase.query(DevicesOperationTB.devicesOperation_tb, null, "directory = ?", new String[] {f.toString()}, null, null, null);
//先查询,在遍历
if (mCursor != null) {//判断数据库不为空
if (mCursor.getCount() != 0) {/*
注意,这里才是判断是否查询到数据,只要数据库存在,
mCursor 就不会为空,没查询到数据mCursor.getCount()==0的*/
for (mCursor.moveToFirst(); !mCursor.isAfterLast(); mCursor.moveToNext()){
LogUtils.i(Tag, "filename: "+mCursor.getString(mCursor.getColumnIndex(DevicesOperationTB.directory)));
LogUtils.i(Tag, "fileTime: "+mCursor.getString(mCursor.getColumnIndex(DevicesOperationTB.time)));}
}
}
6,删除:delete语句
String[] whereArgs = {"测试.txt","add"};
mSqLiteDatabase.delete(tb, "fileName=? and operation=?", whereArgs);
参数1:表名
参数2:条件,二个条件用and 我这句意思是:删除fileName = "测试.txt"并且operation="add"的那条数据
7,清空表,也是delete语句,参数不同而已:
mSqLiteDatabase.delete(tb, null, null);
无条件删除,就是清空了
总结先到这里吧,自己做笔记用,数据库小白!