SQLite of Android data storage using _android

SQLite is an open source, embedded relational database, the first version of Alpha was released in 2000. SQLite has outstanding performance in portability, ease of use, compactness, efficiency and reliability.

The SQLite database created in Android is stored in the:/data/data/< package name >/databases/directory.

Main Features:

-Lightweight

-Independence, no dependencies, no installation

-Cross-platform support for many operating systems

-Supports databases up to 2TB in size

-Each database exists in the form of a single file

-stored in the hard disk as a B-TREE data structure

SQLite data type:

SQLite supports NULL, INTEGER, real, text, and BLOB data types

Represents: null value, integer value, floating-point value, string value, binary object.

Dynamic Data type (weak reference):

When a value is inserted into the database, SQLite detects its data type, and if the type does not match the associated column, SQLite attempts to convert the value to the type of the column and, if not, the value is stored as its own type.

The use of SQLite in Android mainly involves two classes:

Sqlitedatabase and Sqliteopenhelper, the following is a major analysis of these two classes.

Sqlitedatabase

This class provides methods for managing SQLite databases, such as creating, deleting, executing SQL commands, and performing other common database management tasks. The database name of each program is unique.

Common methods:

Db.execsql (String SQL)//execute any SQL statement

Db.insert (String table,string nullcolumnhack,contentvalues values)//INSERT Record

Db.delete (String table,string whereclause,string[] whereargs)/delete record

Db.update (String table,contentvalues values,string whereclause,string[] whereargs)//update record

Db.query (String table,string[] columns,string selection,string[] selectionargs,string groupby,string having,String By)//query record

Db.rawquery (String sql,string[] selectionargs)//Query records by SQL statement

The following is an example of a simple operation SQLite database:

public class MainActivity extends AppCompatActivity {
@Override
protected void onCreate (Bundle savedInstanceState) {
super.onCreate (savedInstanceState);
setContentView (R.layout.activity_main);
// Each program has its own database
// Open or create a database through openOrCreateDatabase, return the SQLiteDatabase object
/ **
* openOrCreateDatabase (String name, int mode, SQLiteDatabase.CursorFactory factory)
* name: database name
* mode: database permissions, MODE_PRIVATE is private to this application, MODE_WORLD_READABLE and MODE_WORLD_WRITEABLE are globally readable and writable, respectively.
* factory: A factory class that can be used to instantiate a cusor object
* /
SQLiteDatabase db = openOrCreateDatabase ("user.db", MODE_PRIVATE, null);
// Create a table
db.execSQL ("create table if not exists userTb (" +
"_id integer primary key," +
"name text not null, age integer not null," +
"sex text not null)");
// Insert records into the table
db.execSQL ("insert into userTb (name, age, sex) values ('zhangsan', 18, 'female')");
db.execSQL ("insert into userTb (name, age, sex) values ('Li Si', 19, 'Male')");
db.execSQL ("insert into userTb (name, age, sex) values ('wangwu', 20, 'female')");
// Cursor is a query result object, similar to ResultSet in JDBC
Cursor queryResult = db.rawQuery ("select * from userTb", null);
if (queryResult! = null) {
while (queryResult.moveToNext ()) {
Log.i ("info", "id:" + queryResult.getInt (queryResult.getColumnIndex ("_ id"))
+ "Name:" + queryResult.getString (queryResult.getColumnIndex ("name"))
+ "Age:" + queryResult.getInt (queryResult.getColumnIndex ("age"))
+ "Gender:" + queryResult.getString (queryResult.getColumnIndex ("sex")));
}
// Close the cursor object
queryResult.close ();
}
// Close the database
db.close ();
}
}

When Openorcreatedatabase ("User.db", Mode_private,null) is executed, a database file is created in the/data/data/< package name >/databases/directory. Open Ddms to view. You can also export it, using tools such as navigate to open view data inside.

In addition, the above example uses the Execsql () method for recording operations through native SQL statements, and, of course, it can be manipulated using the Sqlitedatabase common methods described above, such as insert (), delete (), update (), query ( ) and other methods. However, it is important to note that, in the case of inserting records, when the amount of data is small, inserting with SQL statements via Execsql () is almost as efficient as inserting records using the Insert () method, but if the data volume is large, then using the former is significantly more efficient than using the latter.

Sqliteopenhelper

This class is a Sqlitedatabase helper class that is primarily used to manage database creation and version updates. Sqlitehelper is an abstract class that is typically used by creating a subclass that inherits from it and overrides the Oncreat () and Onupgrade () methods.

-oncreat (Sqlitedatabase db)//is called when the database is first created, and is generally used for table-building operations.

-onupgrade (sqlitedatabase db,int oldversion,int newversion)//When upgrading the database version call

Here is a simple example of using Sqliteopenhelper:

Create a subclass that inherits from Sqliteopenhelper

public class SQLiteHelper extends SQLiteOpenHelper {
/ **
* context: context object
* name: database name
* /
public SQLiteHelper (Context context, String name) {
super (context, name, null, 1);
}
/ / Called when the database is first created, generally to create a table or some initialization operation
@Override
public void onCreate (SQLiteDatabase db) {
// Build the table
db.execSQL ("create table if not exists userTb (" +
"_id integer primary key," +
"name text not null, age integer not null," +
"sex text not null)");
}
// Automatically called when the database version is upgraded
@Override
public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {

}
}

Then we can get a Sqlitedatabase object by Sqlitehelper instance, and then do a series of operations on the database.

public class MainActivity2 extends appcompatactivity {@Override protected void onCreate (Bundle savedinstancestate) {sup
public class MainActivity2 extends AppCompatActivity {
@Override
protected void onCreate (Bundle savedInstanceState) {
super.onCreate (savedInstanceState);
setContentView (R.layout.activity_main2);
// Create a SQLiteHelper object
SQLiteHelper helper = new SQLiteHelper (MainActivity2.this, "stu.db");
// Use the getWritableDatabase () or getReadableDatabase () method to get the SQLiteDatabase object
SQLiteDatabase db = helper.getWritableDatabase ();
// Insert record
db.execSQL ("insert into userTb (name, age, sex) values ('zhangsan', 18, 'female')");
db.execSQL ("insert into userTb (name, age, sex) values ('Li Si', 19, 'Male')");
db.execSQL ("insert into userTb (name, age, sex) values ('wangwu', 20, 'female')");
// Get cursor object
Cursor queryResult = db.rawQuery ("select * from userTb", null);
if (queryResult! = null) {
// Print all records
while (queryResult.moveToNext ()) {
Log.i ("info", "id:" + queryResult.getInt (queryResult.getColumnIndex ("_ id"))
+ "Name:" + queryResult.getString (queryResult.getColumnIndex ("name"))
+ "Age:" + queryResult.getInt (queryResult.getColumnIndex ("age"))
+ "Gender:" + queryResult.getString (queryResult.getColumnIndex ("sex")));
}
// Close the cursor object
queryResult.close ();
}
// Close the database
db.close ();
}
}