"Reprint" of SQLite for Android data storage

SQLite is an open source embedded database engine written by D.richard Hipp in C language. It supports most of the SQL92 standards and can be run on all major operating systems.

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

Main Features:

-Lightweight

-Independence, no dependencies, no installation

-Cross-platform, support for many operating systems

-Support for databases up to 2TB in size

-Each database exists as a single file

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

Data type of SQLite:

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

Represents: null, Integer, floating-point, String, binary, respectively.

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 it cannot, the value is stored as its own type.

Using SQLite in Android mainly involves two classes:

sqlitedatabase and sqliteopenhelper, the following are the main analysis of these two classes.

Sqlitedatabase

This class provides some ways to manage SQLite databases, such as creating, deleting, executing SQL commands, and performing other common database administration tasks. The database name is unique for each program.

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 )//Query record

Db.rawquery (String sql,string[] selectionargs)//Querying records through SQL statements

Here is an example of a simple operation of the 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//through openorcreatedatabase to open or create a database, return Sqlitedatabase object/** * Openorcreatedatabase (St Ring Name,int mode,sqlitedatabase.cursorfactory Factory) * Name: Database name * Mode: Database permissions, mode_private for this application private         , 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 a record into the table Db.execsql ("INSERT into USERTB (name,age,sex) VALUES (' Zhang San ', 18, ' female ')"); Db.execsql ("INSERT INTO USERTB (Name,age,sex) VALUES (' John Doe ', 19, ' Male ') ");        Db.execsql ("INSERT into USERTB (name,age,sex) VALUES (' Harry ', 20, ' female ')");        The cursor is a query result object, similar to resultset Cursor QueryResult = db.rawquery ("SELECT * from USERTB", null) in JDBC; if (queryresult! = null) {while (Queryresult.movetonext ()) {log.i ("info", "ID:" + queryresult . GetInt (Queryresult.getcolumnindex ("_id") + "Name:" + queryresult.getstring (queryresult.getcolum                          NIndex ("name")) + "Age:" + queryresult.getint ("Queryresult.getcolumnindex")            + "Gender:" + queryresult.getstring (queryresult.getcolumnindex ("Sex"));        }//Close Cursor object Queryresult.close ();    }//Close database Db.close (); }}

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

In addition, the above example records the operation using the Execsql () method through the native SQL statement, of course, you can also use the sqlitedatabase commonly used methods described above, such as insert (), delete (), update (), query ( ) and other methods. However, it is important to note that in order to insert records for example, when the amount of data is not large, the use of Execsql () using SQL statements to insert the record with the Insert () method is similar to the efficiency, but if the volume of data is larger, then the use of the former is significantly higher than the efficiency of the latter.

Sqliteopenhelper

This class is a helper class for Sqlitedatabase, which is used primarily 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 overriding the Oncreat () and Onupgrade () methods.

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

-onupgrade (sqlitedatabase db,int oldversion,int newversion)//called when upgrading the database version

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, typically for a table or some initialized operation    @Override public    void OnCreate (Sqlitedatabase db) {        //Build Table        Db.execsql ("CREATE table if not exists USERTB (" + "                _id integer primary key," +                "Name text not null,age integer Not NULL, "+                " sex text is not null) ")    ;    Automatically call    @Override public    void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {    }} when the database version is upgraded

You can then get a Sqlitedatabase object from an instance of Sqlitehelper and then perform a series of operations on the database.

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"); Get Sqlitedatabase object Sqlitedatabase db = Helper.getwritabledatabase using the Getwritabledatabase () or Getreadabledatabase () method        ();        Insert Record Db.execsql ("INSERT into USERTB (name,age,sex) VALUES (' Zhang San ', 18, ' female ')");        Db.execsql ("INSERT into USERTB (name,age,sex) VALUES (' John Doe ', 19, ' Male ')");        Db.execsql ("INSERT into USERTB (name,age,sex) VALUES (' Harry ', 20, ' female ')");        Gets the cursor object cursor QueryResult = Db.rawquery ("SELECT * from USERTB", null); if (queryresult! = null) {//prints 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 ("se            X "));        }//Close Cursor object Queryresult.close ();    }//Close database Db.close (); }}

"Reprint" of SQLite for Android data storage