Summary of some essentials of Android App using SQLite database
- 2021-06-28 14:01:40
- OfStack
/DATA/data/Package Name/databases is the directory where the program stores data, and DATA is the path returned by the Environment.getDataDirectory() method.Once the database is found, you can select user.db to perform the export.
If the data directory cannot be opened during debugging on the real machine, it means that your mobile phone does not have root, so you can use the emulator instead.
1. Get the SQLiteDatabase object:
SQLiteDatabase db = openOrCreateDatabase(File file, SQLiteDatabase.Cursor, Factory factor);
2.SQLiteDatabase provides the following methods:
db.execSQL(sql) // Perform any SQL Sentence
db.insert(table, nullColumnHack, value) // (Add)
db.delete(table, whereClause, whereArgs) // (Delete)
db.updata(table, values, whereClause, whereArgs) // (change)
db.query ( table,columns,whereClause,whereArgs,groupBy,having,orderBy) // (Check)
db.rawQuery(sql, selectionArgs) // have access to SQL Statement direct query
3. Perform the query and rawQuery operations and return an Cursor cursor object that traverses the entire query. Cursor provides the following methods to move the cursor:
c.move( int offset) // Moves the cursor up or down by the specified number of rows, positive down, negative up
c.moveToFirst() // Move to 1 Row, returns Boolean value
c.moveToLast()
c.moveToNext()
c.moveToPostion(int postion) // Moves to the specified row, returning a Boolean value
c.moveToPrevious() // Move Up 1 That's ok
c.isFirst(); // Does it point to the 1 strip
c.isLast(); // Does it point to the last 1 strip
c.isBeforeFirst(); // Does it point to the 1 Before bar
c.isAfterLast(); // Does it point to the last 1 After bar
c.isNull(int columnIndex); // Specifies whether the column is empty ( Column cardinality is 0)
c.isClosed(); // Is the cursor closed
c.getCount(); // Total number of data items
c.getPosition(); // Returns the number of rows pointed to by the current cursor
c.getColumnIndex(String columnName); // Returns the column index value for a column name
c.getString(int columnIndex); // Returns the value of the specified column of the current row
Below is an example of creating an SQLiteDatabase object and querying only with the SQL statement
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
//每个程序都有自己的数据库，而且互不干扰
//创建1个数据库，并且打开，这个方法返回的是1个SQLiteDadabase对象(如果没有就创建，有就直接打开）
//这个数据库的名字叫user.db，这样取名是为了以后导出到电脑上后方便第3方软件打开，第2个参数是1个常量，此例表示私有别的数据库无法访问
SQLiteDatabase db = openOrCreateDatabase("user.db", MODE_PRIVATE, null);
//创建1张表 usertb ，主键名字建议写成_id, 1个主键，3列, 加上_id总共4列
db.execSQL("create table if not exists usertb(_id integer primary key autoincrement, name text not null, age integer not null, sex text not null)");
//往这张表usertb中加3条数据，分别3列，3个对应的值
db.execSQL("insert into usertb(name, age, sex) values('Jack','18','男')");
db.execSQL("insert into usertb(name, age, sex) values('Hellen','19','女')");
db.execSQL("insert into usertb(name, age, sex) values('Mike','20','男')");
//查询数据，第1个参数还是1条语句，查询方法，指定如何查找查询条件， 第2个参数是查询条件，默认把数据全部查询出来
//这里的返回值是Cursor，是查询数据后得到的管理集合的1个类，可以理解为list（游标接口）
Cursor c = db.rawQuery("select * from usertb", null);
if (c!= null){ //如果能查询到数据
c.moveToFirst(); //如果没有处理过数据，这条可以省略，默认光标第1行
while(c.moveToNext()){ //每次只能查询到1条数据，判断是否能查询到下1行（重点：每次光标到达1行后，下面的语句依次打印那1行中的数据，再循环，打印下面1行数据）
Log.i ("info", " "+ c.getInt(c.getColumnIndex("_id"))); //第1个字段int型， 需要转成String型才能用Log打印（找到这1条数据中字段角标为0的integer型数据）
Log.i("info", c.getString(c.getColumnIndex("name"))); //第2个字段是text型
Log.i("info", " "+c.getInt(c.getColumnIndex("age")));
Log.i("info", c.getString(c.getColumnIndex("sex")));
Log.i("info", "~~~~~~~~"); //测试1次循环有多少数据被打印
}
c.close(); //查询完，游标1定要释放
}
db.close();
}
4. Addition, deletion and alteration of related parameters:
table: Table name for querying data
columns: Column name to query
whereClause: Query condition clause, placeholder'?'
whereArgs: Used to pass in parameter values for placeholders
groupBy: Used to control grouping
having: Used for grouping filtering
orderBy: Used to sort records
ContentValues is a wrapper for key/value that allows you to encapsulate data to be inserted or modified as key/value and use it directly when using the appropriate modification methods.
It has two methods of saving and removing:
put(String key , Xxx);
getAsXxx ( String Key ) ;
The following is an example of using built-in functions to manipulate database add-delete checks:
SQLiteDatabase db = openOrCreateDatabase("user.db", MODE_PRIVATE, null);
db.execSQL("create table if not exists usertb(_id integer primary key autoincrement, name text not null, age integer not null, sex integer not null) ");
//在执行增、改方法之前，先创建insert方法中的1个ContentValues对象，再对这个对象存入数据，存完后把values插入
ContentValues values = new ContentValues();
//增
values.put("name", "张3");
values.put("age",18);
values.put("sex","男");
db.insert("usertb", null, values); //插入方法的返回值是1个long，表示新添记录的行号
values.clear(); //在插入下1条数据前需要把values清空，再对values存入新数据
values.put("name", "李4");
values.put("age",19);
values.put("sex","男");
db.insert("usertb", null, values);
values.clear();
values.put("name", "王5");
values.put("age",20);
values.put("sex","男");
db.insert("usertb", null, values);
values.clear();
//改 (将id大于的性别改成女
values.put("sex", "女");
db.update("usertb", values, "_id >?", new String[]{"2"});
//删 (将名字里带3的人删除）
db.delete("uesrtb", "name like ?", new String [] {"%3%"});
//查 (查询usertb这张表，所有行都差，_id >0的数据都查，查询出的数据按照name排序）
Cursor c = db.query("usertb", null, "_id > ?", new String[]{"0"}, null, null, "name");
c.close();
//关闭当前数据库
db.close();
//删除user.db数据库（注意不是表名table）
deleteDatabase("user.db");
5.SQLiteOpenHelper:
SQLiteOpenHelper is a help class that manages our database by inheriting it and implementing the onCreate and Upgrade methods.
SQLiteDatabase db = helper.getWritableDatabase();
SQLiteDatabase db = helper.getReadableDatabase();
The following example creates a new class that inherits SQLiteOpenHelper
public class DBOpenHelper extends SQLiteOpenHelper{
public DBOpenHelper(Context context, String name) {
super(context, name, null, 1);
}
public DBOpenHelper(Context context, String name, CursorFactory factory,int version) {
super(context, name, factory, version);
}
@Override// Called the first time a database is created 1 You can build libraries in general Table building operations
public void onCreate(SQLiteDatabase db) {
db.execSQL("create table if not exists stutb(_id integer primary key autoincrement,name text not null,sex text not null,age integer not null)");
db.execSQL("insert into stutb(name,sex,age)values(' Zhang 3',' female ',18)");
}
@Override// When the version of the database changes Will execute automatically
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
An object of this subclass can then be created in the main activity, and an SQLiteDatabase object can be obtained from the get method of this class.
DBOpenHelper helper =new DBOpenHelper(MainActivity.this, "stu.db");
SQLiteDatabase db = helper.getWritableDatabase();