Android SQLite data storage, androidsqlite

Android SQLite data storage, androidsqlite
1. Introduction to saving data with SQLite

It is ideal for storing duplicate or structured data (such as contract information) in a database. One of the main principles of SQL database is architecture: how to organize a formal declaration of the database. The architecture is embodied in the SQL statements used to create databases. It helps to create companion classes, I .e. contract classes. It explicitly specifies the architecture layout in a systematic and self-recorded manner.

A contract class is a container used to define constants of URLs, tables, and column names. The contract class allows all other classes in the same software package to use the same constant. You can change the column name at a location and spread it throughout the Code. A good way to organize the contract class is to put the global definition of the entire database into the root level of the class. Create an internal class for each table listing its columns.

The SQLiteOpenHelper class has a set of useful APIs. When you use this class to obtain database references, the system will only execute operations that may run for a long time when necessary, instead of during application startup: create and update databases. Because they may run for a long time, make sure to call getWritableDatabase () or getReadableDatabase () in the background thread, such as using AsyncTask or IntentService.

SQLite is a lightweight embedded database engine that supports the SQL language and has good performance with only a small amount of memory. In addition, it is open-source and can be used by anyone. Many open-source projects (Mozilla, PHP, Python) Use SQLite. SQLite consists of the following components: SQL Compiler, kernel, backend, and attachment. SQLite uses the virtual database engine (VDBE ). It makes debugging, modification, and expansion of the SQLite kernel more convenient.

Features: it is designed for devices with limited resources and has no server processes. All data is stored across platforms in the same file and can be freely copied. The advantage is high efficiency. The Android runtime environment contains the complete SQLite.

The biggest difference between SQLite and other databases is its support for data types. When creating a TABLE, you can specify the Data Type of a column in the create table statement, however, you can put any data type into any column. When a value is inserted into the database, SQLite checks its type. If the type does not match the associated column, SQLite attempts to convert the value to the type of the column. If the conversion fails, the value is stored as its own type. For example, you can put a String in the INTEGER column. SQLite calls this "weak type" (manifest typing .). In addition, SQLite does not support some standard SQL functions, especially the FOREIGN KEY constraints (FOREIGN KEY constrains), nested transcaction and RIGHT OUTER JOIN and FULL OUTER JOIN, and some ALTER TABLE functions. In addition to the above functions, SQLite is a complete SQL System with complete triggers and transactions. Android integrates SQLite at runtime. All Android applications can use the SQLite database.

JDBC (Java database connection) consumes too much system resources, so JDBC is not suitable for memory devices such as mobile phones. Therefore, Android provides some new APIs to use the SQLite database. The database is stored in data/<project folder>/database. SQLite database is used in Android development. Activity can access a database through Content Provider or Service. To use SQLite in Android applications, you must create your own database, create tables, index, and fill in data.

Android provides SQLiteOpenHelper to help you create a database. As long as you inherit the SQLiteOpenHelper class, you can easily create a database. The SQLiteOpenHelper class encapsulates the logic used to create and update databases based on the needs of application development. The subclass of SQLiteOpenHelper requires at least three methods.

The subclass of SQLiteOpenHelper requires at least three methods.

(1) constructor: Call the constructor of SQLiteOpenHelper of the parent class. This method requires four parameters: context (for example, an Activity), Database Name, an optional cursor Project (usually null), and an integer representing the database model version in use.

(2) onCreate () method, which requires a SQLiteDatabase object as a parameter to fill the table and initialize data for this object as needed.

(3) onUpgrage () method, which requires three parameters: One SQLiteDatabase, one old version number and one new version number, in this way, we can understand how to transform a database from an old model to a new model.

To perform write and read operations from the database, call getWriteableDatabase () and getReadableDatabase () respectively. Both return a SQLiteDatabase object indicating the database and provide a method for SQLite operations. After the database operation is completed (adding Activity has been closed), you need to call the close () method of SQLiteDatabase to release the database connection. To create tables and indexes, you must call the execSQL () method of SQLiteDatabase to execute DDL statements. If no exception exists, this method does not return a value. SQLite automatically creates an index for the primary key column. Generally, tables and indexes are created when a database is created for the first time. You can use the execSQL () method to execute INSERT, UPDATE, DELETE, and other statements to update table data. You can also use the insert (), UPDATE (), and delete () Methods of SQLiteDatabase objects.

The update () method has four parameters: The table name, The ContentValues object that represents the column name and value, the optional WHERE condition and the optional string that fills in the WHERE statement, these strings will replace "?" Mark.

There are two ways to use SELECT to retrieve data from the SQLite database.

(1) Use rawQuery () to directly call the SELECT statement and use the query () method to create a query. The returned value is a cursor object.

(2) Use a cursor

No matter how the query is executed, a Cursor (Database Cursor) is returned, and the Cursor is obtained by re-executing the query using the requery () method.

Ii. usage method 1. Implement SQLiteOpenHelper

Public class SQDBLiteHelper extends SQLiteOpenHelper {private static final String DATABASE_NAME = "test. db "; private static final int DATABASE_VERSION = 1; public SQDBLiteHelper (Context context) {super (context, DATABASE_NAME, null, DATABASE_VERSION );} // when the database is created for the first time, onCreate will be called @ Override public void onCreate (SQLiteDatabase db) {}// Database Upgrade @ Override public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {}// database downgrade @ Override public void onDowngrade (SQLiteDatabase db, int oldVersion, int newVersion ){}}

2. Insert data

db.insert("person",null,values);

3. Read data

 Cursor c = db.rawQuery("SELECT * FROM person",null); while (c.moveToNext()){ 　　...    } c.close();

4. modify data

ContentValues cv = new ContentValues();        cv.put("age",person.age);        db.update("person", cv, "name = ?", new String[]{person.name});

5. delete data

db.delete("person", "age >= ?", new String[]{String.valueOf(person.age)});

Iii. Case 1. Add the strings. xml file

<Resources> <string name = "app_name"> DataStorageDemo </string> <string name = "action_settings"> Settings </string> <string name = "SQLite"> SQLite </ string> <string name = "create_table"> Create a table </string> <string name = "insert_table"> insert a table </string> <string name = "read_table"> Read a table </string> <string name = "delete_table"> delete a table </string> <string name = "update_table"> Update a table </string> </resources>

2. Modify the activity_main.xml File

<?xml version="1.0" encoding="utf-8"?><android.support.design.widget.CoordinatorLayout 
    xmlns:android="http://schemas.android.com/apk/res/android"    xmlns:tools="http://schemas.android.com/tools"    android:layout_width="match_parent"    android:layout_height="match_parent"    android:fitsSystemWindows="true"    tools:context="com.zhangmiao.datastoragedemo.MainActivity"><LinearLayout    android:layout_width="match_parent"    android:layout_height="match_parent"    android:orientation="vertical"    >    <TextView        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:text="@string/SQLite"        android:layout_gravity="center_horizontal"        />    <LinearLayout        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:orientation="horizontal"        android:layout_marginTop="@dimen/fab_margin"        android:layout_marginBottom="@dimen/fab_margin"        >        <Button            android:id="@+id/sqlite_insert"            android:layout_height="wrap_content"            android:layout_width="0dp"            android:layout_weight="1"            android:text="@string/insert_table" />        <Button            android:id="@+id/sqlite_update"            android:layout_height="wrap_content"            android:layout_width="0dp"            android:layout_weight="1"            android:text="@string/update_table" />        <Button            android:id="@+id/sqlite_read"            android:layout_height="wrap_content"            android:layout_width="0dp"            android:layout_weight="1"            android:text="@string/read_table" />        <Button            android:id="@+id/sqlite_delete"            android:layout_height="wrap_content"            android:layout_width="0dp"            android:layout_weight="1"            android:text="@string/delete_table" />    </LinearLayout>    <TextView        android:id="@+id/table_info"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:text="@string/app_name"        /></LinearLayout></android.support.design.widget.CoordinatorLayout>

3. Add the Person class

package com.zhangmiao.datastoragedemo;/** * Created by zhangmiao on 2016/12/16. */public class Person {    public int _id;    public String name;    public int age;    public String info;    public Person() {    }    public Person(String name, int age, String info) {        this.name = name;        this.age = age;        this.info = info;    }}

4. Add the SQLiteDBHelper class

Public class SQLiteDBHelper extends SQLiteOpenHelper {private static final String DATABASE_NAME = "test. db "; private static final int DATABASE_VERSION = 1; public SQLiteDBHelper (Context context) {super (context, DATABASE_NAME, null, DATABASE_VERSION );} // when the database is created for the first time, onCreate will be called @ Override public void onCreate (SQLiteDatabase db) {db.exe cSQL ("create table if not exists person" + "(_ id integer primary key autoincrement, name VARCHAR," + "age INTEGER, info TEXT )");} // Database Upgrade @ Override public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {db.exe cSQL ("drop table if exists person"); onCreate (db );} // database downgrade @ Override public void onDowngrade (SQLiteDatabase db, int oldVersion, int newVersion) {onUpgrade (db, oldVersion, newVersion );}}

5. Add the SQLiteDBManager class

package com.zhangmiao.datastoragedemo;import android.content.ContentValues;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import java.util.ArrayList;import java.util.List;/** * Created by zhangmiao on 2016/12/16. */public class SQLiteDBManager {    private SQLiteDBHelper helper;    private SQLiteDatabase db;    public SQLiteDBManager(Context context){        helper = new SQLiteDBHelper(context);        db = helper.getWritableDatabase();    }    public void add(List<Person> persons){        for(int i = 0; i<persons.size();i++){            ContentValues values = new ContentValues();            Person person = persons.get(i);            values.put("name",person.name);            values.put("age",person.age);            values.put("info",person.info);            db.insert("person",null,values);        }    }    public void updateAge(Person person){        ContentValues cv = new ContentValues();        cv.put("age",person.age);        db.update("person", cv, "name = ?", new String[]{person.name});    }    public void deleteOldPerson(Person person){        db.delete("person", "age >= ?", new String[]{String.valueOf(person.age)});    }    public List<Person> query(){        ArrayList<Person> persons = new ArrayList<>();        Cursor c = queryTheCursor();        while (c.moveToNext()){            Person person = new Person();            person._id = c.getInt(c.getColumnIndex("_id"));            person.name = c.getString(c.getColumnIndex("name"));            person.age = c.getInt(c.getColumnIndex("age"));            person.info = c.getString(c.getColumnIndex("info"));            persons.add(person);        }        c.close();        return persons;    }    public Cursor queryTheCursor(){        Cursor c = db.rawQuery("SELECT * FROM person",null);        return c;    }    public void closeDB(){        db.close();    }}

6. Modify MainActivity

package com.zhangmiao.datastoragedemo;import android.os.Bundle;import android.support.v7.app.AppCompatActivity;import android.view.View;import android.widget.Button;import android.widget.TextView;import java.util.ArrayList;import java.util.List;public class MainActivity extends AppCompatActivity implements View.OnClickListener{    private SQLiteDBHelper mDbHelper;    private List<Person> mPersons;    private SQLiteDBManager mManager;    private TextView mTableInfo;    @Override    protected void onCreate(Bundle savedInstanceState) {        super.onCreate(savedInstanceState);        setContentView(R.layout.activity_main);        mDbHelper = new SQLiteDBHelper(this);        mManager = new SQLiteDBManager(this);        Button sqliteInsert = (Button)findViewById(R.id.sqlite_insert);        Button sqliteRead = (Button)findViewById(R.id.sqlite_read);        Button sqliteUpdate = (Button)findViewById(R.id.sqlite_update);        Button sqliteDelete = (Button)findViewById(R.id.sqlite_delete);        mTableInfo = (TextView)findViewById(R.id.table_info);        sqliteDelete.setOnClickListener(this);        sqliteInsert.setOnClickListener(this);        sqliteRead.setOnClickListener(this);        sqliteUpdate.setOnClickListener(this);
        mPersons = new ArrayList<>();        initData();    }    private void initData(){        String[] names = new String[]{"zhang","zhao","li","wu"};        int[] ages = new int[]{20,21,19,28};        String[] infos = new String[]{"women","men","men","women"};        for(int i = 0; i< names.length;i++){            Person person = new Person(names[i],ages[i],infos[i]);            mPersons.add(person);        }    }    @Override    public void onClick(View v) {        switch (v.getId()){            case R.id.sqlite_insert:                mManager.add(mPersons);                break;            case R.id.sqlite_read:                writeTableInfo(mManager.query());                break;            case R.id.sqlite_update:                Person person = new Person("zhang",18,"women");                mManager.updateAge(person);                break;            case R.id.sqlite_delete:                Person person1 = new Person();                person1.age = 25;                mManager.deleteOldPerson(person1);                break;        }    }    private void writeTableInfo(List<Person> persons){        String message = "";        for(int i = 0; i<persons.size();i++){            Person person = persons.get(i);            message += "id: "+person._id + " name: "+ person.name                    +" age: "+person.age + " info: "+person.info + "\n";        }        mTableInfo.setText(message);    }}