Data storage and access - first encounter with SQLite database
Introduction to this section:
In this section we continue to learn the third way of Android data storage and access: SQLite database, which is different from other SQL databases. We do not need to install another database software on the mobile phone. The Android system has integrated this database. We do not need to install another database software like When using other database software (Oracle, MSSQL, MySql, etc.), you need to install it, complete the relevant configuration, and change the port! That’s all for the introduction, let’s learn this next thing~
1. Basic concepts
1) What is SQLite? Why use SQLite? What are the characteristics of SQLite?
Answer: Please listen to Xiaozhu’s explanation below:
①SQLite is a lightweight relational database, which has fast computing speed and takes up less resources. It is very suitable for mobile applications. used on the device, It not only supports standard SQL syntax, but also follows ACID (database transaction) principles. No account is required, and it is very convenient to use!
② Earlier we learned to use files and SharedPreference to save data, but in many cases, The file is not necessarily valid, for example, concurrent access by multiple threads is relevant; the app has to deal with complex data structures that may change, etc.! Such as depositing and withdrawing money from the bank! Using the first two will seem very powerless or cumbersome. The emergence of the database can solve this problem. And Android provides us with such a lightweight SQLite, why not use it?
③SQLite supports five data types: NULL, INTEGER, REAL (floating point number), TEXT (string text) and BLOB (binary object) Although there are only five types, other data types such as varchar and char can be saved; because SQLite has the biggest feature: You can save data of various data types into any field without caring about the field declaration. What is the data type , such as yours Strings can be stored in Integer type fields, of courseIn addition to declaring it as the primary key INTEGER PRIMARY The KEY field can only store 64-bit integers ! In addition, when SQLite parses the CREATE TABLE statement, The data type information following the field name in the CREATE TABLE statement will be ignored. For example, the following statement will ignore the type information of the name field: CREATE TABLE person (personid integer primary key autoincrement, name varchar(20))
Summary of the features:
SQlite saves the database through files. A file is a database, and the database contains multiple There are tables, and there are Multiple records, each record is composed of multiple fields, each field has a corresponding value, and we can specify the type# for each value ##, you can also not specify it Type (except primary key)
PS: By the way, Android’s built-in SQLite is SQLite 3 version~
2) Several related classes:Hey, when you are learning something new, what you don’t like the most is encountering some new terms, right? Let’s talk about a few first Three classes we use when using the database:
- SQLiteOpenHelper: Abstract class, we inherit this class and then override the database creation and update methods, We can also obtain a database instance through an object of this class, or close the database!
- SQLiteDatabase: Database access class: We can use the objects of this class to do some operations of adding, deleting, modifying and checking the database
- Cursor: Cursor , somewhat similar to resultset in JDBC, result set! It can be simply understood as pointing to a certain point in the database A record pointer!
2. Use the SQLiteOpenHelper class to create databases and version management
For apps involving databases, we cannot manually create a database for them file, so you need to enable the app for the first time The database table is created when the application is upgraded; when our application needs to be upgraded and the structure of the database table needs to be modified, this time The database table has been updated; for these two operations, Android provides us with two methods of SQLiteOpenHelper, onCreate( ) and onUpgrade( ). Implementation
Method analysis:
- onCreate(database): Generate database table when using the software for the first time
- onUpgrade(database,oldVersion,newVersion): Will be called when the database version changes. Generally, the version number only needs to be changed when the software is upgraded, and the version of the database is controlled by the programmer. Assume that the current version of the database The version is 1. Due to business changes, the database table structure has been modified. At this time, the software needs to be upgraded. When upgrading the software, you hope Update the database table structure in the user's mobile phone. In order to achieve this purpose, the original database version can be set to 2 Or any other number that is different from the old version number!
Code example：
public class MyDBOpenHelper extends SQLiteOpenHelper {
public MyDBOpenHelper(Context context, String name, CursorFactory factory,
int version) {super(context, "my.db", null, 1); }
@Override
//数据库第一次创建时被调用
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE person(personid INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(20))");
}
//软件版本号发生改变时调用
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("ALTER TABLE person ADD phone VARCHAR(12) NULL");
}
}Code analysis：
The above code is the first When we start the application the first time, we will create the my.db file and execute the method in onCreate(). Create a Person table with two fields, the primary key personId and the name field; then we modify the db version number, then the method in onUpgrade() will be called next time it is started, and another field will be inserted into the table! In addition, here is the insertion A field, so the data will not be lost. If the table is rebuilt, all the data in the table will be lost. In the next section we will Let’s teach you how to solve this problem!
Process Summary:
- Step 1: Customize a class to inherit the SQLiteOpenHelper class
- Step 2: Set it in the super of the constructor of the class The database name and version number to be created
- Step 3: Rewrite the onCreate() method to create the table structure
- Step 4: Rewrite The onUpgrade() method defines the operations to be performed after the version number changes
3. How to view the db file we generated
When we call the above MyDBOpenhelper The object's getWritableDatabase() will create our db in the following directory Database file:
#We found that there are two databases, the former is the database we created, and the latter is created to enable the database to support transactions. Temporary log files! The general size is 0 bytes! But in File Explorer we cannot open files, not even txt, let alone .db! So here are two options for you:
- 1. Export it first, and then use SQLite’s graphical tool to view it
- 2. After configuring the adb environment variable, use adb Shell to view (command line, install the tool)!
Well, let me demonstrate the above two methods to you, just choose the one you like~~
Method 1: Use SQLite graphical Tools for viewing db files
There are many such software. The author uses SQLite Expert Professional. Of course, you can also use other tools. If you need it, you can download it: SQLiteExpert.zip
Export our db file to the computer desktop, open SQLiteExpert, the interface is as follows:
Don’t ask me how to play, import the db and play slowly by yourself, the usage is very simple, I don’t understand Baidu~
As for the second method, I originally wanted to try it, but later I found that the sqlite command could not be found Come on, just try it a few times and forget it. It will be used later in the fine button. If you are interested, you can find Guo Lin's "The First Line of Code - Android" and try it according to the flow chart! Only the first part is posted here, read the command part yourself!
Method 2: The adb shell command line will take you to pretend and fly
1. Configure SDK environment variables:
Right-click me Computer ——> Advanced system settings -> Environment variables -> New system variables -> Put the platform-tools of SDK Copy the path: For example, the author's: C:\Software\Coding\android-sdks-as\platform-tools
Confirm, and then Find the Path environment variable, edit it, and add at the end: %SDK_HOME%;
Then open the command line, enter adb, swish swish a bunch of things, it means the configuration is successful!
——————Emphasis——————: Before executing subsequent command line instructions, there may be several machines for your tests: 1.Native simulator: That line, you skip here and continue. 2.Genymotion Simulator: No problem, Genymotion Shell cannot execute the following command 3.Real machine (rooted): Then open File Explorer and see if there is anything in the data/data/ directory? No? Here is a method, which is to first install a RE file manager, then grant RE Root permission, and then go to the root directory: Then long press the data directory, a dialog box like this will pop up:
Then wait for him to slowly modify the permissions, and the modification is completed Finally, we open the File Explorer of DDMS again, and we can see:
Okay, you can see the things in data/data! ————————————————————————
2. Enter adb shell, and then type the following command to Go to the databases directory of our app:
Then enter the following commands in sequence:
- sqlite3 my.db: Open the database file
- .table to view which tables are in the database Then you can directly enter the database statement, such as query: Select * from person
- .schema: View the table creation statement
- .quit :Exit the editing of the database
- .exit:Exit the device console
...Because system/bin/sh sqlite3: not found, For this problem, the subsequent Sqlite commands cannot be used. If you want to see the renderings, just check Guo Daxia’s book by yourself~ But below we still export the db file first, and then use graphical Database tools to view!
4. Use the API provided by Android to operate SQLite
If you have not learned database-related syntax, or you are lazy and don’t want to write database syntax, you can use Android Give us Provides some API methods for operating the database. Let's write a simple example to cover the usage of these APIs!
Code Example:
Running renderings:
Implementation code:
The layout is too simple, just four Button, I won’t post it anymore, just post the code of MainActivity.java:
public class MainActivity extends AppCompatActivity implements View.OnClickListener {
private Context mContext;
private Button btn_insert;
private Button btn_query;
private Button btn_update;
private Button btn_delete;
private SQLiteDatabase db;
private MyDBOpenHelper myDBHelper;
private StringBuilder sb;
private int i = 1;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
mContext = MainActivity.this;
myDBHelper = new MyDBOpenHelper(mContext, "my.db", null, 1);
bindViews();
}
private void bindViews() {
btn_insert = (Button) findViewById(R.id.btn_insert);
btn_query = (Button) findViewById(R.id.btn_query);
btn_update = (Button) findViewById(R.id.btn_update);
btn_delete = (Button) findViewById(R.id.btn_delete);
btn_query.setOnClickListener(this);
btn_insert.setOnClickListener(this);
btn_update.setOnClickListener(this);
btn_delete.setOnClickListener(this);
}
@Override
public void onClick(View v) {
db = myDBHelper.getWritableDatabase();
switch (v.getId()) {
case R.id.btn_insert:
ContentValues values1 = new ContentValues();
values1.put("name", "呵呵~" + i);
i++;
//参数依次是：表名，强行插入null值得数据列的列名，一行记录的数据
db.insert("person", null, values1);
Toast.makeText(mContext, "插入完毕~", Toast.LENGTH_SHORT).show();
break;
case R.id.btn_query:
sb = new StringBuilder();
//参数依次是:表名，列名，where约束条件，where中占位符提供具体的值，指定group by的列，进一步约束
//指定查询结果的排序方式
Cursor cursor = db.query("person", null, null, null, null, null, null);
if (cursor.moveToFirst()) {
do {
int pid = cursor.getInt(cursor.getColumnIndex("personid"));
String name = cursor.getString(cursor.getColumnIndex("name"));
sb.append("id：" + pid + "：" + name + "\n");
} while (cursor.moveToNext());
}
cursor.close();
Toast.makeText(mContext, sb.toString(), Toast.LENGTH_SHORT).show();
break;
case R.id.btn_update:
ContentValues values2 = new ContentValues();
values2.put("name", "嘻嘻~");
//参数依次是表名，修改后的值，where条件，以及约束，如果不指定三四两个参数，会更改所有行
db.update("person", values2, "name = ?", new String[]{"呵呵~2"});
break;
case R.id.btn_delete:
//参数依次是表名，以及where条件与约束
db.delete("person", "personid = ?", new String[]{"3"});
break;
}
}
}5. Use SQL statements to operate the database
Of course, you You may have learned SQL and can write related SQL statements, but you don’t want to use the APIs provided by Android. You can directly use the relevant methods provided by SQLiteDatabase:
- execSQL(SQL,Object[]): Use SQL statements with placeholders, this is to execute and modify the database The content sql statement uses
- rawQuery(SQL,Object[]): Use SQL query operation with placeholder In addition, I forgot to introduce Curosr and related attributes earlier. I will add it here: ——Cursor The object is somewhat similar to the ResultSet in JDBC, the result set! The usage is similar. Here is a method to move the record pointer of the query result:
- move(offset) :Specify the number of rows to move up or down, an integer means moving down; a negative number means moving up!
- moveToFirst(): The pointer moves to the first row, and returns true successfully, which also indicates that there is data
- moveToLast(): The pointer moves to The last thing is the same, it returns true successfully;
- moveToNext(): The pointer moves to the next line, it returns true successfully, indicating that there are still elements!
- moveToPrevious(): Move to the previous record
- getCount() Get the total number of data
- isFirst(): Whether it is the first record
- isLast(): Whether it is the last item
- moveToPosition(int) :Move to the specified row
Usage code example:
1.Insert data:
public void save(Person p)
{
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.execSQL("INSERT INTO person(name,phone) values(?,?)",
new String[]{p.getName(),p.getPhone()});
}2.Delete data:
public void delete(Integer id)
{
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.execSQL("DELETE FROM person WHERE personid = ?",
new String[]{id});
}3. Modify data:
public void update(Person p)
{
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.execSQL("UPDATE person SET name = ?,phone = ? WHERE personid = ?",
new String[]{p.getName(),p.getPhone(),p.getId()});
}4. Query data:
public Person find(Integer id)
{
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("SELECT * FROM person WHERE personid = ?",
new String[]{id.toString()});
//存在数据才返回true
if(cursor.moveToFirst())
{
int personid = cursor.getInt(cursor.getColumnIndex("personid"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
return new Person(personid,name,phone);
}
cursor.close();
return null;
}5. Data paging:
public List<Person> getScrollData(int offset,int maxResult)
{
List<Person> person = new ArrayList<Person>();
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("SELECT * FROM person ORDER BY personid ASC LIMIT= ?,?",
new String[]{String.valueOf(offset),String.valueOf(maxResult)});
while(cursor.moveToNext())
{
int personid = cursor.getInt(cursor.getColumnIndex("personid"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
person.add(new Person(personid,name,phone)) ;
}
cursor.close();
return person;
}6. Query the number of records:
public long getCount()
{
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("SELECT COUNT (*) FROM person",null);
cursor.moveToFirst();
long result = cursor.getLong(0);
cursor.close();
return result;
}PS: In addition to the above method of obtaining the number of items, you can also use the cursor.getCount() method to obtain the number of data items. But the SQL statement needs to be changed! For example SELECT * FROM person;
Summary of this section:
This section introduces you to the basic usage of Android’s built-in SQLite, which is relatively simple. Yes, we will study a little more in the next section. More advanced things, SQLite transactions, how to process data in the application update database, and database storage of large binary files Methods! Okay, that’s it for this section~
