Android a very wordy summary of SQLite knowledge

Copyright notice: this article is the blogger’s original article, without the permission of the blogger shall not be reproduced source :github.com/AnliaLee if you see any mistakes or good suggestions, welcome to leave a comment

preface

Bloggers have been on a whim for the past two days to review SQLite, but the online search has been a pain in the ass because few blogs cover the basics of SQLite, rather than several in one, so my browser TAB looks like this

Is the so-called diy food and clothing, I don’t want to forget to do it again, so I decided to summarize their own, set the long to a hotchpotch. This blog has a lot to cover, but I have to be verbose in order to cover everything. Please look at the table of contents and skip through them as needed

---

What is the SQLite

There are a lot of articles about SQLite on the web, and one of them summarizes it very well

SQLite is introduced

SQLite is a lightweight relational database that is very fast and consumes very few resources, usually only a few hundred K of memory, making it especially suitable for use on mobile devices.

SQLite features

• To use lightweight SQLite, you only need to come with a dynamic library to enjoy its full capabilities, and that dynamic library should be small.
• The stand-alone SQLite database core engine does not rely on third-party software, nor does it require so-called “installation.”
• Isolation All the information in an SQLite database (such as tables, views, triggers, and so on) is contained in a single folder for easy management and maintenance.
• Cross-platform SQLite currently supports most operating systems, not just PCS, but also many mobile operating systems, such as Android and IOS.
• Multilingual Interfaces SQLite database supports multilingual programming interfaces.
• Security SQLite databases implement independent transaction processing through exclusivity and shared locks at the database level. This means that multiple processes can read data from the same database at the same time, but only one can write data.
• Weakly typed fields The data in the same column can be of different types

SQLite data type

SQLite has the following five common data types:

Storage class	Storage class
NULL	The value is a NULL value
INTEGER	A value is a signed integer stored in 1, 2, 3, 4, 6, or 8 bytes, depending on the size of the value
REAL	A value is a floating point value, stored as an 8-byte IEEE floating point number
TEXT	The value is a text string stored using a database encoding (UTF-8, UTF-16BE, or UTF-16LE)
BLOB	A value is a BLOB of data stored entirely based on its input

---

A debugging tool

SQLite Expert (Personal) is recommended for debugging SQLite databases

Of course, you can also use ADB shell directly to view the database, this is personal preference

---

SQLiteDatabase and SQLiteOpenHelper

Before I talk about how to use SQLite databases, it’s worth mentioning two important classes: SQLiteDatabase and SQLiteOpenHelper, which are the two most basic classes in the SQLite database API

SQLiteDatabase

In Android, the use of SQLite databases starts with the SQLiteDatabase class (SQLiteOpenHelper also uses SQLiteDatabase for database creation and versioning, more on that later). We can take a look at its internal methods.

Insert, query, and other familiar words can be found. These methods are already wrapped and all we need to do is pass in the appropriate parameters to perform operations such as insert, update, and query. Of course SQLiteDatabase also provides a way to execute SQL statements directly, for example

• ExecSQL can execute SQL statements with change behavior such as INSERT, DELETE, UPDATE, and CREATE TABLE
• RawQuery is used to execute select statements

In summary, we can think of SQLiteDatabase as a database object whose methods can be called to create, delete, execute SQL commands, and perform other common database management tasks. We will go into more detail in later chapters on how to set up our local database step by step using SQLiteDatabase

SQLiteOpenHelper

SQLiteOpenHelper is a secondary class to SQLiteDatabase, which simplifies database creation and versioning by encapsulating SQLiteDatabase internal methods. It is an abstract class, and in general we need to inherit and rewrite the two superclass methods:

• OnCreate is called when the database is first generated. We typically 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. We usually perform database update operations here, such as updating fields, adding and deleting tables, etc

In addition, the parent methods include onConfigure, onDowngrade, onOpen, which are rarely used in general projects. If you need to know more about them, you can check this blog, so we won’t repeat them here

So how do SQLiteOpenHelper and SQLiteDatabase relate? SQLiteOpenHelper provides the getWritableDatabase and getReadableDatabase methods, which both end up calling getDatabaseLocked, And perform the database operations we previously overwrote in onCreate, onUpgrade, etc., on the first call (or database version update). The difference between these two methods is that

• GetWritableDatabase opens a readable/writable database, and if the database does not exist, a database is automatically created, eventually returning the SQLiteDatabase object
• GetReadableDatabase opens a readable database, as above

---

Creating a database

A few things to know before you do the actual work

In general, there are three ways to create an SQLite database, listed in order of frequency of use

• Inherit SQLiteOpenHelper and call getWritableDatabase/getReadableDatabase to open or create a database (recommended for beginners)
• Call SQLiteDatabase. OpenOrCreateDatabase open or create the database
• Call Context. OpenOrCreateDatabase open or create the database

Eventually they are to be called SQLiteDatabase. The openDatabase method, chart can simply summarized their invocation relationship

So let’s take a look at what openDatabase does

public static SQLiteDatabase openDatabase(String path, CursorFactory factory, int flags,
		DatabaseErrorHandler errorHandler) {
	SQLiteDatabase db = new SQLiteDatabase(path, flags, factory, errorHandler);
	db.open();// Continue to open or create the database
	return db;
}
Copy the code

Explain the parameters

• String path Database file path
• The CursorFactory Factory is used to construct a custom Cursor subclass object that is returned when a query is executed. If null is passed, the default factory is used to construct the Cursor
• int flags
  
  To control the access mode of the database, the parameters that can be passed in are
  • CREATE_IF_NECESSARY: Create a database file when the database does not exist
  • ENABLE_WRITE_AHEAD_LOGGING: Bypasses the locking mechanism of the database and reads and writes the database using multiple threads
  • NO_LOCALIZED_COLLATORS: When a database is opened, it is not sorted according to the localized language
  • OPEN_READONLY: opens the database in read-only mode
  • OPEN_READWRITE: Opens the database in read/write mode
• DatabaseErrorHandler errorHandler Interface used to call back when a database corruption is detected. Null is not required

As a beginner, we don’t have to dig into the meaning and usage scenarios of each parameter too much. We know that there are so many contents, and we can expand our skills to play an index role in the future, so we won’t waste space to continue to dig deeply here

About the path to create the database

It’s worth a separate section on this, because many sources ignore this or don’t make it very clear. The default path of the database is

/data/data/<package_name>/databases/
Copy the code

Db “, the system will automatically create a database file named “xxx.db” in the default path. The biggest advantage of this is security. To get this file, we have to root the phone (it is said that we can directly get it in the emulator, I have not tried it personally). And the database file will be deleted as the App is deleted. However, in some scenarios, such as when we need to retrieve database files for debugging, creating the database in the default path is not as convenient. Therefore, we can create the database file in internal storage or on an SD card, for example the path we pass in can be written like this

Environment.getExternalStorageDirectory().getPath() + "/SQLiteTest/xxx.db"
Copy the code

The database file xxx.db will be placed in the SQLiteTest directory in memory. Note that if the SQLiteTest directory does not exist, the system will throw an exception

. This is because we mentioned above SQLiteDatabase openDatabase method in the db. The open () follow-up did not create the directory of the code, so we need to create the directory manually (remember to configure permissions)

File dir = new File(Environment.getExternalStorageDirectory().getPath() + "/SQLiteTest/");
if(! dir.exists()) { dir.mkdir(); }Copy the code

After the directory is created, we can continue to create the database

Mysql > create database file test.db; create table test.db

SQLiteOpenHelper (recommended for beginners)

As mentioned earlier, we need to inherit SQLiteOpenHelper and override the onCreate and onUpgrade methods

How to call

Create MySQLiteOpenHelper

public class MySQLiteOpenHelper extends SQLiteOpenHelper {
    public static final String FILE_DIR = Environment.getExternalStorageDirectory().getPath() + "/SQLiteTest/";
    public static final int DATABASE_VERSION = 1;
    public static final String TABLE_NAME = "test";

    public MySQLiteOpenHelper(Context context, String name){
        this(context, name, null, DATABASE_VERSION);
    }

    public MySQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        this(context, name, factory, version, null);
    }

    public MySQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version, DatabaseErrorHandler errorHandler) {
        super(context, name, factory, version, errorHandler);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        File dir = new File(FILE_DIR);
        if(! dir.exists()) { dir.mkdir(); }try{
            db.execSQL("create table if not exists " + TABLE_NAME +
                    "(id text primary key,name text)");
        }
        catch(SQLException se){ se.printStackTrace(); }}@Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if(newVersion > oldVersion){
            String sql = "DROP TABLE IF EXISTS "+ TABLE_NAME; db.execSQL(sql); onCreate(db); }}}Copy the code

Call getWritableDatabase/getReadableDatabase in your Activity.

public class MainActivity extends AppCompatActivity {
    public static final String DATABASE_NAME = FILE_DIR + "test.db";
    private static final int CODE_PERMISSION_REQUEST = 100;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        if (ContextCompat.checkSelfPermission(this, Manifest.permission.ACCESS_COARSE_LOCATION) ! = PackageManager.PERMISSION_GRANTED) {// Apply for write permission
            ActivityCompat.requestPermissions(this.new String[]{
                    Manifest.permission.WRITE_EXTERNAL_STORAGE
            }, CODE_PERMISSION_REQUEST);
        } else{ createDB(); }}@Override
    public void onRequestPermissionsResult(int requestCode, @NonNull String[] permissions, @NonNull int[] grantResults) {
        super.onRequestPermissionsResult(requestCode, permissions, grantResults);
        switch(requestCode) {
            case CODE_PERMISSION_REQUEST:
                if(grantResults[0] == PackageManager.PERMISSION_GRANTED) {
                    createDB();
                } else{}break;
            default:
                break; }}private void createDB(a){
        MySQLiteOpenHelper sqLiteOpenHelper = new MySQLiteOpenHelper(this,DATABASE_NAME); SQLiteDatabase database = sqLiteOpenHelper.getWritableDatabase(); }}Copy the code

Take a look at the results using SQLite Expert

Second, call SQLiteDatabase. OpenOrCreateDatabase open or create the database

Take a look at the source of the openOrCreateDatabase method

public static SQLiteDatabase openOrCreateDatabase(File file, CursorFactory factory) {
	return openOrCreateDatabase(file.getPath(), factory);
}

public static SQLiteDatabase openOrCreateDatabase(String path, CursorFactory factory) {
	return openDatabase(path, factory, CREATE_IF_NECESSARY, null);
}

public static SQLiteDatabase openOrCreateDatabase(String path, CursorFactory factory, DatabaseErrorHandler errorHandler) {
	return openDatabase(path, factory, CREATE_IF_NECESSARY, errorHandler);
}
Copy the code

You can see that openOrCreateDatabase is actually opened in CREATE_IF_NECESSARY mode to create the database

How to call

Execute the corresponding code in the Activity

private static final String FILE_DIR = Environment.getExternalStorageDirectory().getPath() + "/SQLiteTest/";
public static final String DATABASE_NAME = FILE_DIR + "test.db";

private void createDB(a){
	File dir = new File(FILE_DIR);
	if(! dir.exists()) { dir.mkdir(); } SQLiteDatabase database = SQLiteDatabase.openOrCreateDatabase(DATABASE_NAME,null);
	database.execSQL("create table if not exists " + "test" +
			"(id text primary key,name text)");
}
Copy the code

The same result I will not repeat the paste out

Third, callContext.openOrCreateDatabaseOpen or create the database

Context. The implement in ContextImpl openOrCreateDatabase class (knowledge about the Context you can consult to understand)

@Override
public SQLiteDatabase openOrCreateDatabase(String name, int mode, CursorFactory factory,
		DatabaseErrorHandler errorHandler) {
	checkMode(mode);
	File f = getDatabasePath(name);
	int flags = SQLiteDatabase.CREATE_IF_NECESSARY;
	if((mode & MODE_ENABLE_WRITE_AHEAD_LOGGING) ! =0) {
		flags |= SQLiteDatabase.ENABLE_WRITE_AHEAD_LOGGING;
	}
	if((mode & MODE_NO_LOCALIZED_COLLATORS) ! =0) {
		flags |= SQLiteDatabase.NO_LOCALIZED_COLLATORS;
	}
	SQLiteDatabase db = SQLiteDatabase.openDatabase(f.getPath(), factory, flags, errorHandler);
	setFilePermissionsFromMode(f.getPath(), mode, 0);
	return db;
}
Copy the code

As you can see the Context. OpenOrCreateDatabase and SQLiteDatabase. OpenOrCreateDatabase essentially do not have too big difference, just one more mode parameters are used to set the operation mode, can be incoming parameters

• MODE_PRIVATE Specifies the default mode for creating database files that can only be accessed by the calling application (or by all applications sharing the same user ID)
• The MODE_ENABLE_WRITE_AHEAD_LOGGING function is the same as ENABLE_WRITE_AHEAD_LOGGING
• The MODE_NO_LOCALIZED_COLLATORS function is the same as NO_LOCALIZED_COLLATORS
• After the MODE_WORLD_READABLE setting is set, the current file can be read by other applications. It is not recommended to set this parameter for API 17 or later.
• MODE_WORLD_WRITEABLE specifies that the current file can be written to other applications. It is not recommended to set this parameter in API 17 or later.

How to call

private static final String FILE_DIR = Environment.getExternalStorageDirectory().getPath() + "/SQLiteTest/";
public static final String DATABASE_NAME = FILE_DIR + "test.db";

private void createDB(a){
	File dir = new File(FILE_DIR);
	if(! dir.exists()) { dir.mkdir(); } SQLiteDatabase database =this.openOrCreateDatabase(DATABASE_NAME,MODE_PRIVATE,null);
	database.execSQL("create table if not exists " + "test" +
			"(id text primary key,name text)");
}
Copy the code

---

Operations related to the database

Here only introduces the simple add delete change check operation, the rest of us can consult the data to understand

increase

SQLiteDatabase provides the insert method

public long insert(String table, String nullColumnHack, ContentValues values) {
	try {
		return insertWithOnConflict(table, nullColumnHack, values, CONFLICT_NONE);
	} catch (SQLException e) {
		Log.e(TAG, "Error inserting " + values, e);
		return -1; }}Copy the code

The meanings of the parameters are described as follows

• String table Specifies the name of the table to which data is to be inserted
• String nullColumnHack Insert will fail if values are null or there is no content in it. To prevent this, specify a column name. The value of the specified column name is set to NULL and then inserted into the database. If values are not null and the number of elements is greater than 0, nullColumnHack is generally set to NULL
• ContentValues Values ContentValues is similar to a map. Store values as key-value pairs

How to call

As mentioned earlier, we can add data in two ways: sqliteDatabase. insert and SQliteDatabase. execSQL

ContentValues values = new ContentValues();
values.put("id"."1");
values.put("name"."name1");
database.insert("test".null,values);

database.execSQL("insert into test(id, name) values(2, 'name2')");
database.close();
Copy the code

The result is shown in figure

delete

The same SQLiteDatabase provides a delete method to delete data

public int delete(String table, String whereClause, String[] whereArgs) {
	acquireReference();
	try {
		SQLiteStatement statement =  new SQLiteStatement(this."DELETE FROM "+ table + (! TextUtils.isEmpty(whereClause) ?" WHERE " + whereClause : ""), whereArgs);
		try {
			return statement.executeUpdateDelete();
		} finally{ statement.close(); }}finally{ releaseReference(); }}Copy the code

• String Table Name of the table
• The String whereClause condition statement, equivalent to the WHERE keyword, can separate multiple conditions using placeholders
• String[] whereArgs is an array of the values of the conditional statement. Symbol) one to one correspondence

How to call

Let’s add a few pieces of data for testing

database.execSQL("insert into test(id, name) values(3, 'name3')");
database.execSQL("insert into test(id, name) values(4, 'name4')");
database.execSQL("insert into test(id, name) values(5, 'name5')");
Copy the code

Execute delete statement

String whereClause = "id=?";
String[] whereArgs = {"3"};
database.delete("test",whereClause,whereArgs);

database.execSQL("delete from test where name = 'name4'");
database.close();
Copy the code

change

Sqlitedatabase. update is used to update data

public int update(String table, ContentValues values, String whereClause, String[] whereArgs) {
	return updateWithOnConflict(table, values, whereClause, whereArgs, CONFLICT_NONE);
}
Copy the code

UpdateWithOnConflict you can look it up on your own, but I’m just going to briefly explain what each parameter means

• String Table Name of the table
• ContentValues Values ContentValues is similar to a map. Store values as key-value pairs
• The String whereClause condition statement, equivalent to the WHERE keyword, can separate multiple conditions using placeholders
• String[] whereArgs is an array of the values of the conditional statement. Symbol) one to one correspondence

How to call

ContentValues values = new ContentValues();
values.put("name"."update2");
String whereClause = "id=?";
String[] whereArgs={"2"};
database.update("test",values,whereClause,whereArgs);

database.execSQL("update test set name = 'update5' where id = 5");
database.close();
Copy the code

check

SQLiteDatabase provides query and rawQuery methods to perform query operations, and returns a Cursor object at the end of the query. Cursor is a Cursor interface that provides a method for traversing the results of a query. Since this blog is not focused on that, I won’t cover it. Let’s move on to the Query method

public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy) {

	return query(false, table, columns, selection, selectionArgs, groupBy,
			having, orderBy, null /* limit */);
}

public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) {

	return query(false, table, columns, selection, selectionArgs, groupBy,
			having, orderBy, limit);
}

public Cursor query(boolean distinct, String table, String[] columns,
		String selection, String[] selectionArgs, String groupBy,
		String having, String orderBy, String limit) {
	return queryWithFactory(null, distinct, table, columns, selection, selectionArgs,
			groupBy, having, orderBy, limit, null);
}

public Cursor query(boolean distinct, String table, String[] columns,
		String selection, String[] selectionArgs, String groupBy,
		String having, String orderBy, String limit, CancellationSignal cancellationSignal) {
	return queryWithFactory(null, distinct, table, columns, selection, selectionArgs,
			groupBy, having, orderBy, limit, cancellationSignal);
}
Copy the code

The query method takes a lot of parameters

• Boolean DISTINCT Specifies whether to remove duplicate records
• String Table Name of the table
• String[] columns An array of column names to be queried, such as SELECT ID, name FROM test
• A String Selection condition statement, equivalent to the WHERE keyword, can use placeholders to separate multiple conditions
• String[] selectionArgs Is an array of values for conditional statements. Symbol) one to one correspondence
• String groupBy Groups the query results into groups
• String having group conditions to restrict the results of a group
• String orderBy sort statement
• String limit Limit of paging query
• CancellationSignal CancellationSignal indicates the CancellationSignal. It is used to set subsequent operations when a query is cancelled. If no query is cancelled, it is set to null. If the operation is cancelled, the query statement runtime throws an exception (OperationCanceledException)

How to call

The rawQuery method is used to execute a query statement. The rawQuery method is used to execute a query statement. The rawQuery method is used to execute a query.

Call the query method, and I won’t try each parameter, just to give a few examples

if(database! =null){
	Cursor cursor = database.query ("test".null.null.null.null.null.null);
	while (cursor.moveToNext()){
		String id = cursor.getString(0);
		String name=cursor.getString(1);
		Log.e("SQLiteTest query"."id:"+id+" name:"+name);
	}
	database.close();
}
Copy the code

or

if(database! =null){
	Cursor cursor = database.rawQuery("SELECT * FROM test".null);
	while (cursor.moveToNext()){
		String id = cursor.getString(0);
		String name=cursor.getString(1);
		Log.e("SQLiteTest query"."id:"+id+" name:"+name);
	}
	database.close();
}
Copy the code

Add multiple conditions

if(database! =null){
	String selection = "id=? or name=?";
	String[] selectionArgs = {"1"."update2"};
	Cursor cursor = database.query ("test".null,selection,selectionArgs,null.null.null);
	while (cursor.moveToNext()){
		String id = cursor.getString(0);
		String name=cursor.getString(1);
		Log.e("SQLiteTest query"."id:"+id+" name:"+name);
	}
	database.close();
}
Copy the code

or

if(database! =null){
	Cursor cursor = database.rawQuery("SELECT * FROM test WHERE id=? or name=?".new String[]{"1"."update2"});
	while (cursor.moveToNext()){
		String id = cursor.getString(0);
		String name=cursor.getString(1);
		Log.e("SQLiteTest query"."id:"+id+" name:"+name);
	}
	database.close();
}
Copy the code

---

Alternatives to SQLite

As technology changes, native SQLite is no longer the only option. Once you’ve laid the foundation, try out popular database frameworks such as OrmLite, greenDAO, ObjectBox, and Realm. Each framework has its own advantages and disadvantages, you can choose their own framework to use. Due to the blogger did not have every framework has been analyzed and tried, in this forced popular science that is playing hooligan, so directly pull a few well-written contrast blog to do foreign aid ~

Related blog link database in the end which strong? Android Database framework summary, there is always a suitable for you! Android database framework GreenDao&Realm analysis