How to get started with SQLite on Android

SQLite is a database product frequently used in software development on the Android platform. As a lightweight database, SQLite is designed to be embedded, moreover, it is already used in many embedded products, and its resource usage is very low. In embedded devices, it may only need several hundred KB of memory. Next, let's take a look at the use of SQlite 3 on the Android platform.

The following concepts are all understood under the SQlite restrictions of the Android platform:

Basic Database concepts

◆ SQlite stores databases through files. A file is a database.

◆ The database contains several tables;

◆ Each table contains multiple records;
 
◆ Each record consists of multiple fields;

◆ Each field has its corresponding value;

◆ The type can be specified for each value.

The database name is the file name. The table has its own name. The record has no name. Each field has a name. In SQlite, records do not have the concept of order. The first and second concepts do not exist. You can only query records that meet the conditions. We operate the database by executing SQL commands.

Database classes on the Android platform

◆ SQLiteOpenHelper abstract class: Implements user classes by inheriting from this class to provide operation functions such as opening and closing databases.

◆ SQLiteDatabase category: Execute insert records and query records on the database.

◆ SQLiteCursor query structure operation class: used to access records in the query results.

Create a database

Assume that a database is used to store a table. Multiple records are saved in the Table. Each record contains three fields: ID, name, and age. Implement a class from SQLiteOpenHelper, and all database operations are encapsulated in this class.

Constructor:

 
 

  
  
public DbHelper(Context context, String name, CursorFactory factory, int version) {  
  
  
        super(context, name, factory, version);  
  
  
    }  
 
 

The parameter name in this function is the name of the database, that is, the name of the database file. version starts from 1. When the table and field of the Database Change in the future, you can improve the version, this triggers onUpgrade () to upgrade old data.

Create a table in the onCreate function:

 
 

  
  
      public void onCreate(SQLiteDatabase db) {  
  
  
    StringBuilder sql = new StringBuilder();  
  
  
    sql.append("CREATE TABLE ");  
  
  
    sql.append(TBL_NAME);  
  
  
    sql.append(" (");  
  
  
    sql.append(FIELD_ID + " TEXT PRIMARY KEY NOT NULL, ");  
  
  
    sql.append(FIELD_NAME + " TEXT, ");  
  
  
    sql.append(FIELD_AGE + " INTEGER");  
  
  
    sql.append(");");  
  
  
    db.execSQL(sql.toString());  
  
  
}  
 
 

The onCreate function is called only when the database is created for the first time. Therefore, this function is suitable for database initialization.

The above function actually executes the SQL statement:
 
Create table mytable (id text primary key not null, name TEXT, age INTEGER );

Add data to a table

You can add data to a table in two ways. One is to directly execute SQL commands using the execSQL function provided by SQLiteDatabase, and the other is to use the insert function provided by SQLiteDatabase, this function encapsulates SQL statements for your convenience.
 
◆ ExecSQL ("insert into mytable VALUES ('idstr', 'namestr', 20);"); you can INSERT a record,
When an error occurs in the insert record, an exception is thrown and needs to be handled.

◆ Using the insert function: the insert function requires a value of the ContentValues type, which is similar to hashmap, a key, and a value with a pair. The key is the field name.

ContentValues v = new ContentValues ();
V. put ("id", "101 ");
V. put ("name", "user101 ");
V. put ("age", 44 );
MDb. insert ("mytable", "null", v );

If the key in v does not specify all fields completely, such as less name or less id, whether the record is successfully inserted depends on the field restrictions during table creation, such as less name, this record will be inserted, but the value of the name field is NULL. If there is less id, the record will NOT be inserted, because not null is specified during creation.

You can run the following command to view the result:

After the adb shell is executed, enter the shell;

Cd/data/cn. demo. SQL

Sqlite3 database. db enters the sqlite operation interface.

. Help. You can see the Command help.
 
. Dump to view the data in the database.
 
For ease of use, you can customize an insert function to pass values through parameters, while ContentValues settings are implemented within the function.

 
 

  
  
            public void insert(String id, String name, int age) {  
  
  
if( mDb == null ) mDb = getWritableDatabase();  
  
  
ContentValues v = new ContentValues();  
  
  
v.put("id", id);  
  
  
v.put("name", name);  
  
  
if(age > 0) v.put("age", age);  
  
  
mDb.insert(TBL_NAME, "null", v);          
  
  
 
 
 

Query records from a table

SQL statement: SELECT column list FROM table WHERE expr ORDER BY column list; Use the query () function of the SQLiteDatabase class:

 
 

  
  
Cursor SQLiteDatabase.query(String table, 
  
  
String[] columns, String selection, String[] selectionArgs, 
  
  
String groupBy, String having, String orderBy); 
 
 

Parameters:
The name of the table where the record to be updated is located.
The value of the field to be updated.
Where clause. Which records are updated.
'? 'Replacement string.
When performing the update operation, if only some fields are assigned values, the original values remain unchanged after the update operation.

Delete records from the table:

 
 

  
  
 
  
  
int SQLiteDatabase.delete(String table, String whereClause, String[] whereArgs); 
  
  
 
 
 

From the entire process of SQLite creation and query, we can see that on the Android platform, due to the features of SQLite, all objects are dominated by files and there is no concept of order. We can query the records that meet the conditions and operate the database using SQL commands. In addition, we should note several classes for operating SQLite on the Android platform: SQLiteOpenHelper abstract class, SQLiteDatabase category class, And SQLiteCursor query structure operation class.

Phone Club -- BKJIA mobile development offline technology salon activity Date: December 19
Topic: advanced Android Application Development Technology
Location: Floor 18, Block B, Third Pole building, No. 66, beisihuan West Road, Haidian District, Beijing
Lecturer: Wang mingli innovation Workshop) Fan huaiyu Netease) Click to register