• [free] exclusive: this is a very valuable collection of Android knowledge system!!

Why SQLite?

SQLite, as a lightweight relational database, takes up very few resources, so its application scenarios are particularly numerous. In mobile development, we often have a need to store data locally, and SQLite is our best choice. The good news is that SQLite is now perfectly integrated into Android, making it easier for developers to get started.

2. Start playing SQLite

First, let’s talk about the overall idea of operating SQLite database in Android:

1. User-defined database operation helper class, inherited from SQLiteOpenHelper class; 2. Initialize the SQLiteOpenHelper object in the Application and expose a method for other classes to call to get the object; 3. Instantiate SQLiteDatabase from SQLiteOpenHelper; 4. Finally, we can take the SQLiteDatabase object and perform SQLite database operations in general.Copy the code

The process isn’t that complicated. Take a little patience, put your finger on it, and look back at SQLite and say, “Wow, you’re so easy!”

1. Customize the database operation helper class, which inherits from SQLiteOpenHelper

import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; /** * Author: Silencezwm on 16/6/20 06:43 * Email: [email protected] * description: Database operation helper */ public class DBHelper extends SQLiteOpenHelper {// Database name private static final String DBName = "study.db"; Private static final int DBVersion = 1; Constructor public DBHelper(Context Context) {super(Context, DBName, null, DBVersion); ExecSQL ("CREATE TABLE study (_id INTEGER PRIMARY) execSQL("CREATE TABLE study (_id INTEGER PRIMARY)  KEY AUTOINCREMENT NOT NULL, bookName varchar, bookDesc varchar)"); } /** * database version update * @param db database instance * @param oldVersion oldVersion * @param newVersion newVersion */ @override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("DROP TABLE IF EXISTS study"); onCreate(db); }}Copy the code

This class inherits SQLiteOpenHelper and implements three methods, wherein onCreate() is called when the database is initialized, and onUpgrade() is called only when the new version of the database is greater than the old version, that is, the database is updated. Which involves the common SQL statement writing, do not understand children shoes have to take time to imagine this knowledge oh.

What does SQLiteOpenHelper do?

SQLiteOpenHelper is a database helper class provided by the Android platform for creating or opening databases.Copy the code

2. Initialize the SQLiteOpenHelper object in the Application and expose a method for other classes to call to get the object

Considering that database operations may be involved in different places throughout the Application, it is necessary to place the initialization of this class in our custom Application class. Like the following:

Public class MyApp extends Application {private static DBHelper mDBHelper; @Override public void onCreate() { super.onCreate(); mDBHelper = new DBHelper(getApplicationContext()); } public static DBHelper getmDBHelper(){return mDBHelper; }}Copy the code

Let’s look at the implementation of initializing the source code of the SQLiteOpenHelper class

public SQLiteOpenHelper(Context context, String name, CursorFactory factory, int version) {
    this(context, name, factory, version, null);
}
Copy the code

Parameter Meaning

Context: the context object, where we pass in ApplicationContext; Name: database name, here we define "study.db"; Factory: The default Cursor object returned after database operation. Here, we default to NULL. When we need to save the returned Cursor object later, we can construct it by ourselves. Version: Indicates the version of the database. The initial version must be greater than 1. Otherwise, the system throws IllegalArgumentException("Version must be >= 1, was "+ version).Copy the code

3. Instantiate SQLiteDatabase from SQLiteOpenHelper

This operation will give us the SQLiteDatabase object, and once we have this object, we can add, delete, change and query the database. This action can be wrapped in a separate class or implemented directly within an Activity. The core code is as follows:

GetWritableDatabase () is the same as getReadableDatabase(). SQLiteDatabase db = MyApp.getmDBHelper().getWritableDatabase(); //SQLiteDatabase db = MyApp.getmDBHelper(). getReadableDatabase();Copy the code

There are two ways to get an instance of SQLiteDatabase. The main differences between the two methods are:

When the database is read-only or the disk is full, getReadableDatabase returns only one read-only database object.Copy the code

In addition, when we initialize the SQLiteDatabase object, the system will conduct the database version number judgment, the core code of this judgment is as follows (interested children can study the getWritableDatabase(), getReadableDatabase() source code implementation) :

Final int version = db.getVersion(); final int version = db.getVersion(); // If the old version is different from the new version if (version! // If the database is read-only, If (db.isReadonly ()) {throw new SQLiteException("Can't upgrade Database from ") version " + db.getVersion() + " to " + mNewVersion + ": " + mName); } // start transaction db.beginTransaction(); If (version == 0) {onCreate(db); if (version == 0); If (version > mNewVersion) {onDowngrade(db, version, mNewVersion); // if (version > mNewVersion) {onDowngrade(db, version, mNewVersion); } else { onUpgrade(db, version, mNewVersion); } // Set the latest version of the database db.setVersion(mNewVersion); / / transaction successful completion of the db. SetTransactionSuccessful (); } finally {// finally endTransaction db.endtransaction (); }}Copy the code

4, Take SQLiteDatabase object for SQLite database operations

The following two operation methods are briefly introduced:

1. Use SQL statements. 2Copy the code

When we initialize the SQLiteDatabase object, we create a database: study.db, and create a table in the database: study, which contains the primary key increment _id and two fields: bookName, bookDesc. The following code to achieve the database insert, update, query, delete four common operations, at this time you should try yourself…

ExecSQL ("insert into study values(null,? ,?) ", new Object[]{et_insert_book_name.getText().toString(), et_insert_book_desc.getText().toString()}); Cv. put("bookName", et_insert_book_name. GetText ().tostring ())); cv.put("bookDesc", et_insert_book_desc.getText().toString()); long isOK = db.insert("study", null, cv); If (isOK == -1) {toast.maketext (dbDemoactivity. this, "insert failed ", toast.length_short).show(); } else {toast.maketext (dbDemoactivity.this, "insert successfully ", toast.length_short).show(); } } break; Db. execSQL("update study set bookName =? where bookName = ?" , new String[]{et_update_new_book_name.getText().toString(), et_update_book_name.getText().toString()}); } else { cv.put("bookName", et_update_new_book_name.getText().toString()); int updateCount = db.update("study", cv, "bookName = ?" , new String[]{et_update_book_name.getText().toString()}); If (updateCount! = 0) {toast.maketext (dbdemoactivity. this, "+ updateCount, toast.length_short).show(); } else {toast.maketext (dbDemoactivity.this, "update failed ", toast.length_short).show(); } } break; Case r.i.Btn_query: if (useSQL) {Cursor Cursor = db.rawQuery("select * from study", null); StringBuilder sb = new StringBuilder(); for (cursor.moveToFirst(); ! cursor.isAfterLast(); Cursor.movetonext ()) {sb.append(" total: "+ cursor.getCount() + "\\\\n" +" "+ cursor.getString(cursor.getColumnIndex("bookName")) + "---- " + cursor.getString(cursor.getColumnIndex("bookDesc"))); text_query_result.setText(sb.toString()); } // Remember to close cursor.close(); C = db.query("study", NULL, NULL, null, null, null, null, null); StringBuffer sb = new StringBuffer(); for (c.moveToFirst(); ! c.isAfterLast(); C. oveToNext ()) {sb. Be sad etCount chtistina georgina rossetti.british poetess append (" total: "+ () +" \ \ \ \ n "+" title: "+ c.columnIndex ("bookName") + "----" + C.columnIndex ("bookDesc")); text_query_result.setText(sb.toString()); } // Remember to close. } break; Case r.i.btn_del: if (useSQL) {db.execSQL("delete from study where bookName =?" , new String[]{et_del_book_name.getText().toString()}); } else { db.delete("study", "bookName = ?", new String[]{et_del_book_name.getText().toString()}); } break;Copy the code

Third, SQLite said

“555… You saw right through me again.”

  • [free] exclusive: this is a very valuable collection of Android knowledge system!!