Android data storage Engine---sqlite database

Target: Can I use SQLite database to make a financial file on the PC-side desktop?

Directory:

Source:

Practice:

Summary and comparison:

About SQLite data

What is, what is the internal structure, what is the relationship between the database and the table?

What's the use?

What is the usual operation?

SQLite database Use

Increase

By deleting

Change

Check

The optimization measures in the practice of SQLite database

For the Android platform, the system has a rich API built in for developers to manipulate SQLite, making it easy to access data.

Step 1, familiar with the creation of database tables, familiar with the relevant operational instructions, to achieve the perceptual knowledge of SQLite database

Create a database with simple content (database name) and corresponding table (table name), and after creating the table, you can perform a simple "Delete and change" command.

    /**     * < function description > Initialize database, CREATE DATABASE     *      * @return void [return type description] *    /private void Initdatabase () {        Mdatabase = Openorcreatedatabase ("Sqldemo.db", context.mode_private,                null);        create_table = "CREATE TABLE person (_id INTEGER PRIMARY KEY autoincrement, name VARCHAR, age SMALLINT)";        Mdatabase.execsql (create_table);    }

The operation that created the database called Execsql (...), which represents the creation of a database directive, and the result is that the corresponding database file is generated in the/data/data/packagename/databases/directory. A data table that creates a person in the database is also executed.

Step 2, create the data table in the database and perform the related basic operations

    /** * < function description > perform some database and table operations * * @return void [return type description] */private void dosomeaction () {        person person = new Person ("John", 30); Mdatabase.execsql ("INSERT into person VALUES (NULL,?,?)", new object[] {person.ge        Tname (), Person.getage ()});        Person.setname ("David");        Person.setage (33);        Contentvalues CV = new Contentvalues ();        Cv.put ("Name", Person.getname ());        Cv.put ("Age", Person.getage ());        Mdatabase.insert ("person", NULL, CV);        CV = new Contentvalues ();        Cv.put ("Age", 35);        Mdatabase.update ("Person", CV, "Name=", new string[] {"John"});                    cursor cursor = mdatabase.rawquery ("SELECT * from" where is the age >=? ", new string[] {        "33"}); while (cursor! = NULL && Cursor.movetofirst ()) {int _id = Cursor.getint (Cursor.getcolumnindex ("_id"));            String name = cursor.getstring (Cursor.getcolumnindex ("name"));            int age = Cursor.getint (Cursor.getcolumnindex ("Age"));        LOG.D (TAG, "_id>=" + _id + ", name=>" + name + ", age=>" + age);        } cursor.close ();        Mdatabase.delete ("Person", "age<?", New string[] {"35"});    Mdatabase.close (); }

The following two kinds of operations are roughly differentiated: database operations, data table operations in the database.

There are two ways that you can add, remove, and update data tables in a database by using the following methods:

public void Execsql (String sql) throws Sqlexception;public void Execsql (String sql, object[] bindargs) throws SQLException ;

The above can be considered as a unified form, the implementation of the traditional database operation instructions.

In addition, there are corresponding API methods that can be differentiated:

Public long Insert (string table, String nullcolumnhack, contentvalues values);p ublic int Update (string table, Contentvalu Es values, String whereclause, string[] whereargs);p ublic int Delete (string table, String Whereclause, string[] whereargs) ;

The table in the parameter indicates that the corresponding table;contentvalues instance in the corresponding database has the following values:

/** holds the actual values */private hashmap<string, object> mvalues;

Where key represents the column name, values represents the content to be written. The argument string whereclause represents the where expression, and string[] is the actual parameter value of the corresponding placeholder.

Query statements are relatively complex, because engineering problems often face a variety of query problems, the system also takes into account this complexity, provides us with a richer query form:

Public Cursor query (string table, string[] columns, string selection, string[] Selectionargs, String groupBy, String havin g, string by) public Cursor query (string table, string[] columns, string selection, string[] Selectionargs, String gro Upby, string having, string-by-clause, string-Limit public Cursor query (boolean distinct, string table, string[] columns, St Ring selection, string[] Selectionargs, String groupBy, String having, string-by-clause, string limit) public Cursor query (bo Olean Distinct, string table, string[] columns, string selection, string[] Selectionargs, String groupBy, string having, S Tring, String limit, cancellationsignal cancellationsignal) public Cursor querywithfactory (cursorfactory  Cursorfactory, Boolean distinct, string table, string[] columns, string selection, string[] Selectionargs, String groupBy, String has, string-by-clause, string limit public Cursor querywithfactory (Cursorfactory cursorfactory, Boolean distinct , String table, string[] columns, STring selection, string[] Selectionargs, String groupBy, String having, string by-clause, string limit, cancellationsignal C ancellationsignal)

In addition, there are four kinds of rough query methods:

Public cursor Rawquery (String sql, string[] selectionargs) public cursor rawquery (String sql, string[] Selectionargs, Canc Ellationsignal cancellationsignal) Public Cursor rawquerywithfactory (cursorfactory cursorfactory, String sql, String[] Selectionargs, String edittable) public Cursor rawquerywithfactory (cursorfactory cursorfactory, String sql, string[] Selectionargs, String edittable, cancellationsignal cancellationsignal)

where the SQL string, which represents an SQL statement, can use placeholders in place of the actual values, while Selectionargs is the actual set of parameters for the placeholder, columns represents all the names of the columns to query, and selection represents a conditional statement after where. You can use placeholders.

The API above returns cursor instances, which represent the cursors of the dataset.

The flexible use of SQL database, more in the query statement, including more tips and methods, because this is the Android UI display content source.

The cursor is available in the following ways:

C.move (int offset); Moves to the specified line C.movetofirst () as a reference to the current position  ;    Move to the first line  c.movetolast ();     Move to the last line  c.movetoposition (int position);//move to the specified line  c.movetoprevious ();//move to the previous line  C.movetonext ();     Move to the next line  C.isfirst ();        Whether to point to the first  c.islast ();     Whether to point to the last  C.isbeforefirst ();  Whether to point to the first article before  c.isafterlast ();    Whether to point to the last bar after  c.isnull (int columnindex);  Specifies whether the column is empty (column cardinality is 0)  c.isclosed ();       Whether the cursor is closed  c.getcount ();       Returns the total number of data items (rows)  c.getposition ();    Returns the number of rows pointed to by the current cursor  c.getcolumnindex (String columnName);//Returns the column index value  c.getstring (int columnindex) for a column name;   Returns the value of the specified column for the current row  

Getting content is using the following API:

Byte[] GetBlob (int columnindex); String getString (int columnindex); short getshort (int columnindex); int getInt (int columnindex); long Getlong (int columnindex), float getfloat (int columnindex);d ouble getdouble (int columnindex);

Step 3: Practical development to improve the above operations to facilitate the development process.

In real development, in order to better manage and maintain the database, it encapsulates a database operations management class that inherits from the Sqliteopenhelper class and then encapsulates its own business logic based on this class.

A Helper class to manage database creation and version management.

Android data storage Engine---sqlite database