This article use the project source code on my own making: https://github.com/lateautumn4lin/TechPaoding/tree/main/practice_demo/Cattle
1 know SQLite
1.1 the SQLite definition
SQLite is a lightweight relational database. Why lightweight? This is mainly because it takes up very few resources, usually only a few hundred K of memory is enough, but also because its structure is simple enough, the computing speed is very fast, especially suitable for mobile devices to use.
1.2 the SQLite features
- Lightweight use of SQLite requires only one dynamic library (the NDK developed SO library) to enjoy its full functionality, and the size of the dynamic library is relatively small, SO it is “good for mobile” use.
- The core engine of a standalone SQLite database does not rely on third-party software, nor does it require an “installation.”
- Isolation All the information in an SQLite database (such as tables, views, triggers, etc.) is contained in a folder for easy management and maintenance. In the form of each database is a DB file, the libraries are separated from each other.
- Cross-platform SQLite currently supports most operating systems, not just computer operating systems, but also many mobile operating systems, such as Android and IOS.
- Multilanguage Interface The SQLite database supports multilanguage programming interfaces (typically developed on the Android side using JDK or NDK).
- Security SQLite databases implement independent transaction processing through exclusive and shared locks at the database level. This means that multiple processes can read from the same database at the same time, but only one can write (concurrency issues are handled in a similar way to another relational database, MySQL).
- Weakly typed fields can have different types of data in the same column (good news for coding freaks, bad news for coding obsessives)
1.3 SQLite data types
SQLite has the following five common data types, all of which are fairly common basic types:
| type | meaning |
|---|---|
| NULL | Value is a NULL value |
| INTEGER | The value is a signed integer stored in 1, 2, 3, 4, 6, or 8 bytes depending on the size of the value |
| REAL | The value is a floating point value stored as an 8-byte IEEE floating point number |
| TEXT | The value is a text string stored using database encoding (UTF-8, UTF-16BE, or UTF-16LE) |
| BLOB | The value is a BLOB of data, stored entirely according to its input |
That is about the basic knowledge of the SQLite, you idea is good, detailed information can see the WIKI:https://zh.wikipedia.org/wiki/SQLite
1.4 install SQLite
The easiest way to install SQLite is, of course, “install for lazy people.
sudo yum install sqlite-devel
Copy the codeAfter the installation can and normal database, you can try “add delete change check”, such as the following
- First of all, we specify a DB file to enter the interactive interface. The db file is also specified so that we can store it for future table creation operations
- Then is normal for the database “add delete change check”
Of course, doing things on the command line is not our main concern this time, we will use Java libraries to do that later.
SQLite debugging tool
As mentioned above, SQLite is actually a relational database, so reminiscent of MySQL, SQLite also has its own specific visualization tool, or debugging tool, because we’ll be using this tool to look at the App’s data store in real time.
2.1 Command Line Debugging
The command line debugging is similar to the way we used to operate the database in CentOS7, because Android is a Linux system and the commands are the same. If you don’t bother, you can use ADB Shell directly into the mobile system.
2.2 SQLiteStudio tools
I think more people choose visual tools than command lines. Here I recommend SQLiteStudio, which I am currently using. In fact, I wrestled with many tools when choosing a tool. However, other tools can not connect to the Android machine in real time debugging or the security and encryption function is poor, so I chose SQLiteStudio directly.
The latest version of SQLiteStudio is SQLiteStudio3.2 at https://sqlitestudio.pl/. You can download it from the website. It’s also open source, and if you have any questions, you can bother the guy on Github.
The overall interface looks like this
Database->Add a Database-> open your selected DB file.
3 Introduction to basic SQLite classes
We have covered the definition of SQLite and the debugging tools. Next, we will formally talk about the use of SQLite. This article will not go into the source code for the time being, and I will post a separate source code analysis later. Before we talk about how to use the SQLite database, it’s worth introducing two important SQLite classes: SQLiteDatabase and SQLiteOpenHelper, which are the two most basic classes in the SQLite database API.
3.1 SQLiteDatabase
In Android’s built-in SQLite library, all SQLite operations are derived from SQLiteDatabase. Another class, SQLiteOpenHelper, is derived from this class for database creation and version management. Let’s take a quick look at the analysis of this class
Insert, query, and other familiar database operations have been encapsulated, we only need to pass in the appropriate parameters to complete such as insert, update, query, and so on. Of course SQLiteDatabase also provides a way to execute SQL statements directly, such as
-
execSQL
db.execSQL("create table if not exists " + TABLE_NAME +"(id text primary key,name text)"); -
rawQuery
db.rawQuery("SELECT * FROM test", null);
3.2 SQLiteOpenHelper
SQLiteOpenHelper is an auxiliary class of SQLiteDatabase, which simplifies the operation of database creation and version management by encapsulating the internal methods of SQLiteDatabase. It is an abstract class. In general, we need to inherit and overwrite these two superclass methods:
- OnCreate is called when the database is first generated. We usually override onCreate to generate the database table structure and add some initialization data that the application uses
- OnUpgrade This method is called when the database version is updated. This is where we normally perform database update operations, such as field updates, table additions and deletations
In addition, there will be onConfigure, ondowngraded, onOpen methods, which are rarely used in general projects. If you need to learn more about them, you can see the official documentation.
3.3 Relationship between SQLiteOpenHelper and SQLiteDatabase
Now that WE’ve introduced SQLiteOpenHelper and SQLiteDatabase, how do they relate? Let’s look at it from the source code
SQLiteOpenHelper provides two methods for creating a database
- getWritableDatabase
- getReadableDatabase
From the source code, we can see that the two methods have something in common, that is, they both call the getDatabaseLocked method, let’s look at the getDatabaseLocked method
- The first step
- The second step
- The third step
According to the above analysis, we can see how SQLiteOpenHelper generates a DB instance step by step by calling the SQLiteDatabase method, which indicates that SQLiteOpenHelper is a higher-dimensional package of SQLiteDatabase.
3.4 SQLiteDatabase object generation process
After summarizing the relationship between SQLiteOpenHelper and SQLiteDatabase, we can take a look at the generation process of SQLiteDatabase object, which can also be used as a template for SQLite we will use later in the development process. There are three ways to generate SQLiteDatabase.
- Use SQLiteOpenHelper to open or create a database with getWritableDatabase/getReadableDatabase (recommended for beginners)
- Call SQLiteDatabase. OpenOrCreateDatabase open or create the database
- Call Context. OpenOrCreateDatabase open or create the database
All three methods will eventually be calledSQLiteDatabase.openDatabasemethods
Explain the parameters
- String Path Database file path
- CursorFactory Factory is used to construct a custom Cursor subclass object. It is returned when a query is performed. If null is passed, the default Factory is used to construct Cursor
- int flagsTo control the database access mode, the parameters that can be passed in are
- CREATE_IF_NECESSARY: Create the database file when the database does not exist
- ENABLE_WRITE_AHEAD_LOGGING: Bypasses the locking mechanism of the database and uses multiple threads to read and write the database
- NO_LOCALIZED_COLLATORS: When the database is opened, the database is not sorted according to the localized language
- OPEN_READONLY: opens the database in read-only mode
- OPEN_READWRITE: Opens the database in read and write mode
- DatabaseErrorHandler errorHandler Is an interface to call back when database corruption is detected. There is usually no special need to pass null
As you can see, we generate an instance of SQLiteDatabase through openDatabase, set the state of the DB instance to on, and finally return the DB instance.
3.5 Path for Creating a Database
The SQLiteDatabase source contains a line of code related to the DB file path
final File filePath = mContext.getDatabasePath(mName);
Copy the codeSo the path that we get from this code is
/data/data/<package_name>/databases/
Copy the codeDb “, the system will automatically create a database file named “xxx.db” under the default path. The biggest benefit of doing this is security, because from Android7, Android policy has restricted the access permission between apps. This also ensures the security of the App.
4 SQLite Demo development
With the basic knowledge of SQLiteDatabase class above, let’s start with a Demo
4.1 create MySQLiteOpenHelper
The first step is to use the fastest way to create SQLiteDatabase, which is to inherit SQLiteOpenHelper to create our own MySQLiteOpenHelper, as follows
4.2 create SQLiteCattleActivity
With the MySQLiteOpenHelper created, we need to use it in a new Activity
Here we use two methods to create the DB instance: SQLiteDatabase’s own openOrCreateDatabase and our MySQLiteOpenHelper’s getWritableDatabase
4.3 Connect SQLiteStudio for real-time debugging
In the above two steps, we have developed the basic App and realized the basic button to complete the “add, delete, change and check function”, but after all, the database is in the mobile phone, how do we carry out real-time database debugging? The following is a real-time debugging scheme based on SQLiteStudio. Since SQLite database is saved in the directory of mobile phone in the form of DB file, we cannot directly and conveniently obtain the content of SQLite in real time. Therefore, we need to use the end-to-end communication function of SQLiteStudio to obtain SQLite data.
4.3.1 SQLiteStudio Environment configuration
The first step is to export the Remote Jar package of SQLiteStudio. The step is Tools->Get Android Connect Jar File to Get the Jar package and put it in the root directory of the project /libs.
We need to turn onSQLiteStudio theSQLiteThe permission to debug the database is yesTools->Open configuration dialogThe configuration page is displayed, and select in the plug-in columnSQLiteOptions.
4.3.2 Configuring the Android project environment
Now that SQLiteStudio is configured, let’s configure our App project. We have introduced the Remote Jar package in our root directory /libs. We also need to write implementation fileTree(include: [‘*.jar’], dir: ‘libs’) in the build.gradle file to ensure that all jar packages under the libs can be imported properly.
Import the Jar package after we only need to add one line of code in our Activity SQLiteStudioService. The instance (). The start (this); We can start an instance of SQLiteStudioService as soon as our Activity starts, listen on the Android port XXX, and wait for the remote SQLiteStudio to connect.
4.3.3 SQLiteStudio Connected mobile phone real-time debugging
configuredAppAfter that, you just need to startAppCan be created in the directoryThe db filesAnd then we’ll be able toSQLiteStudioThe interface is connected remotely.
The steps are similar to what we did before, except for the data type we choose Android SQLite, and then for the DB file we select Port Forwarding, Connect to the phone’s port 12121, which the instance of SQLiteStudioService is listening on, and you’ll be happy to debug.
5 food references
The above is an exclusive guide to the development of SQlite on Android. Due to the space problem, it is only a few simple aspects, but I believe you can also understand the basic process of SQlite development. Of course, this is only the first article in this section of SQlite, and there will be two more articles that will focus on two other aspects, including the security version of SQlite and the source analysis of the implementation of SQlite.
This article was typeset using MDNICE