SQLite database (top)

(i) overview
This section we continue to learn the third way of Android data storage and Access: SQLite database, and other SQL database, we do not need to install another database software on the phone, the Android system has integrated this database, we do not need to use the same as other database software ( Oracle,mssql,mysql, etc.) install again, then complete the relevant configuration, but also change the port and so on!
(ii) SQLite database
1. Basic Concepts
1) What is SQLite? Why use SQLite? What are the characteristics of SQLite?

Answer: Now let ’s listen to the little pig saying:

① SQLite is a lightweight relational database with fast calculation speed and low resource consumption. It is suitable for use on mobile devices. It not only supports standard SQL syntax, but also follows the ACID (database transaction) principle. No account is required. It is very convenient to use!

② Earlier we learned to use files and SharedPreference to save data, but in many cases, files are not necessarily valid, such as multi-threaded concurrent access is relevant; apps have to deal with complex data structures that may change, etc.! Such as bank deposits and withdrawals! Using the former two will appear very weak or tedious. The emergence of a database can solve this problem, and Android provides us with such a lightweight SQLite, why not use it?

③ SQLite supports five data types: NULL, INTEGER, REAL (floating point number), TEXT (string text) and BLOB (binary object). Although there are only five types, varchar, char and other data types can be saved; because SQLite has one of the biggest features: you can save data of various data types into any field without caring about the data type declared by the field.For example, you can store strings in fields of type Integer, except of course the primary key INTEGER is declared. The PRIMARY KEY field can only store 64-bit integers! In addition, when SQLite parses a CREATE TABLE statement, it ignores the data type information that follows the field name in the CREATE TABLE statement. For example, the following statement ignores the type information of the name field: CREATE TABLE person (personid integer primary key autoincrement, name varchar (20 ))

Summary features:

SQlite saves the database through a file. A file is a database. The database contains multiple tables. There are multiple records in the table. Each record consists of multiple fields. Each field has a corresponding value. You can specify the type or not (except for the primary key)
PS: By the way, SQLite built in Android is SQLite 3 version ~
2) Several related classes:

Hey, when learning something new, the least I like is encountering some new nouns, right, let's first talk about the three classes we use when using the database:

    SQLiteOpenHelper: Abstract class, we can inherit the class, and then override the database creation and update methods, we can also get a database instance through the objects of this class, or close the database!
    SQLiteDatabase: database access class: we can use the objects of this class to add, delete, modify and check the database
    Cursor: cursor, a bit similar to the resultset in JDBC, the result set! Can be simply understood as a pointer to a record in the database!
2. Use SQLiteOpenHelper class to create database and version management

For an app that involves a database, we can't manually create a database file for him, so we need to create a database table when the app is first enabled; and when our application is upgraded to modify the structure of the database table, At this time, the database table needs to be updated; for these two operations, Android provides us two methods of SQLiteOpenHelper, onCreate () and onUpgrade () to achieve
Method analysis:

    onCreate (database): Generate database tables when using the software for the first time
    onUpgrade (database, oldVersion, newVersion): It will be called when the version of the database changes. Generally, the version number needs to be changed when the software is upgraded. The version of the database is controlled by the programmer. Assuming that the database version is 1, Due to business changes, the database table structure was modified. At this time, the software needs to be upgraded. When upgrading the software, you want to update the database table structure in the user's mobile phone. To achieve this, you can set the original database version to 2 or other Numbers with different version numbers are just fine!
Code example:

public class MyDBOpenHelper extends SQLiteOpenHelper {
    public MyDBOpenHelper (Context context, String name, CursorFactory factory,
            int version) {super (context, "my.db", null, 1);}
    @Override
    // Called when the database is first created
    public void onCreate (SQLiteDatabase db) {
        db.execSQL ("CREATE TABLE person (personid INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR (20))");

    }
    // Called when the software version number changes
    @Override
    public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL ("ALTER TABLE person ADD phone VARCHAR (12) NULL");
    }
}
Code analysis:

The above code starts the application for the first time. We will create this my.db file and execute the method in onCreate () to create a Person table. It has two fields, the primary key personId and name fields. Modify the version number of db, then the next on startup will call the method in onUpgrade (), insert a field into the table! In addition, a field is inserted here, so the data will not be lost. If the table is rebuilt, all the data in the table will be lost. In the next section, we will teach you how to solve this problem!
Process summary:

    Step 1: Customize a class to inherit the SQLiteOpenHelper class
    Step 2: Set the database name and version number in the super of the constructor of this class.
    Step 3: Override the onCreate () method to create the table structure
    Step 4: Override the onUpgrade () method to define the operation to be performed after the version number is changed.
3. How to view the db file we generated

When we call getWritableDatabase () of the above MyDBOpenhelper object, our db database file will be created in the following directory:


We found that there are two databases, the former is the database we created, and the latter is a temporary log file generated to enable the database to support transactions! The general size is 0 bytes! In File Explorer, we really ca n’t open files, not even txt, let alone .db! So here are two options for everyone:

    1. Export it first, and then use SQLite graphical tools to view
    2. After configuring the adb environment variable, view it through the adb shell!
Well, I will show you the above two methods, choose the one you like ~~

Method 1: Use the SQLite graphical tool to view the db file

There are many such softwares. I use SQLite Expert Professional. Of course, you can also use other tools. You can download what you need: SQLiteExpert.zip

Export our db file to the computer desktop, open SQLiteExpert, the interface is as follows:

After importing db, play slowly by yourself, the usage is very simple, do not understand Baidu ~

Method 2: The adb shell command line will take you to force you to fly

1. Configure SDK environment variables:

Right-click My Computer-> Advanced System Settings-> Environment Variables-> New System Variable-> Copy the platform-tools path of the SDK: For example: C: \ Software \ Coding \ android-sdks-as \ platform- tools

OK, then find the Path environment variable, edit it, and add:% SDK_HOME% at the end;

Then open the command line, enter adb, pinch a bunch of things, the configuration is successful!

—————— Key Points ——————: Before executing subsequent command line instructions, there may be several types of machines for your test: 1. Native emulator: That line, you skip here and continue down 2.Genymotion simulator: No play, Genymotion Shell cannot execute the following commands 3. Real machine (rooted): Then you open File Explorer and see if there is something in the data / data / directory? Is it? The following provides a method, that is, first install a RE file manager, then grant RE Root permissions, and then go to the root directory: Then long-press the data directory, a dialog box like this will pop up:

Then wait for him to modify the permissions slowly. After the modification, we open the DDMS File Explorer again, we can see:

OK, you can see the data / data stuff! ——————————————————————

2. Enter the adb shell, then type the following instructions to go to the databases directory of our app:

The name of your package is erased, then enter the following commands in order:

    sqlite3 my.db: Open database file
    .table to see which tables in the database Then you can directly enter the database statement, such as the query: Select * from person
    .schema: View table creation statements
    .quit: Quit database editing
    .exit: Exit the device console
… Because system / bin / sh sqlite3: not found, this problem cannot be used with the following Sqlite commands. If you want, you can check Guo Daxia's book by yourself ~ and we will export the db file first and then use the graphical database tool Check it out!

4. Operate SQLite using API provided by Android
If you haven't learned database-related syntax, or you are lazy and don't want to write database syntax, you can use some API methods provided by Android to operate the database. Below we write a simple example to cover up the usage of these APIs!

Code example:

run:
Implementation code:

The layout is too simple. Just four Buttons will not be posted, and directly paste the code of MainActivity.java:

public class MainActivity extends AppCompatActivity implements View.OnClickListener {

    private Context mContext;
    private Button btn_insert;
    private Button btn_query;
    private Button btn_update;
    private Button btn_delete;
    private SQLiteDatabase db;
    private MyDBOpenHelper myDBHelper;
    private StringBuilder sb;
    private int i = 1;

    @Override
    protected void onCreate (Bundle savedInstanceState) {
        super.onCreate (savedInstanceState);
        setContentView (R.layout.activity_main);
        mContext = MainActivity.this;
        myDBHelper = new MyDBOpenHelper (mContext, "my.db", null, 1);
        bindViews ();
    }

    private void bindViews () {
        btn_insert = (Button) findViewById (R.id.btn_insert);
        btn_query = (Button) findViewById (R.id.btn_query);
        btn_update = (Button) findViewById (R.id.btn_update);
        btn_delete = (Button) findViewById (R.id.btn_delete);

        btn_query.setOnClickListener (this);
        btn_insert.setOnClickListener (this);
        btn_update.setOnClickListener (this);
        btn_delete.setOnClickListener (this);
    }

    @Override
    public void onClick (View v) {
        db = myDBHelper.getWritableDatabase ();
        switch (v.getId ()) {
            case R.id.btn_insert:
                ContentValues values1 = new ContentValues ();
                values1.put ("name", "Wahaha ~" + i);
                i ++;
                // The parameters are in order: the table name, the column name forcibly inserting null worthy data columns, and the data for one row of records
                db.insert ("person", null, values1);
                Toast.makeText (mContext, "Insertion completed ~", Toast.LENGTH_SHORT) .show ();
                break;
            case R.id.btn_query:
                sb = new StringBuilder ();
                // The parameters are in order: table name, column name, where constraints, placeholders in where provide specific values, specify columns by group by, and further constrain
                // Specify the sorting method of query results
                Cursor cursor = db.query ("person", null, null, null, null, null, null);
                if (cursor.moveToFirst ()) {
                    do {
                        int pid = cursor.getInt (cursor.getColumnIndex ("personid"));
                        String name = cursor.getString (cursor.getColumnIndex ("name"));
                        sb.append ("id:" + pid + ":" + name + "\ n");
                    } while (cursor.moveToNext ());
                }
                cursor.close ();
                Toast.makeText (mContext, sb.toString (), Toast.LENGTH_SHORT) .show ();
                break;
            case R.id.btn_update:
                ContentValues values2 = new ContentValues ();
                values2.put ("name", "Wowaka ~");
                // The parameters are the table name, the modified value, the where condition, and the constraint. If you do not specify three or two parameters, all rows will be changed.
                db.update ("person", values2, "name =?", new String [] {"Wahaha ~ 2"});
                break;
            case R.id.btn_delete:
                // The parameters are the table name, and where conditions and constraints
                db.delete ("person", "personid =?", new String [] {"3"});
                break;
        }
    }
}
5. Use SQL statements to operate the database

Of course, you may have learned SQL and will write related SQL statements, and do not want to use these APIs provided by Android, you can directly use the related methods provided by SQLiteDatabase:

    execSQL (SQL, Object []): Uses SQL statements with placeholders. This is used to execute SQL statements that modify the contents of the database.
    rawQuery (SQL, Object []): Use SQL query operations with placeholders. In addition, I forgot to introduce the Curosr thing and related attributes. Here is a supplement:-Cursor object is similar to ResultSet in JDBC, result set! To use it, provide the following method to move the record pointer of the query result:
    move (offset): Specify the number of rows to move up or down, an integer means to move down; a negative number means to move up!
    moveToFirst (): The pointer moves to the first row, and returns true if successful, which also indicates that there is data
    moveToLast (): The pointer moves to the same end, and returns true if successful;
    moveToNext (): The pointer moves to the next line, and returns true if successful, indicating that there are still elements!
    moveToPrevious (): move to the previous record
    getCount () to get the total number of data
    isFirst (): Whether it is the first record
    isLast (): Whether it is the last item
    moveToPosition (int): move to the specified line
Using code example:
1. Insert data:

public void save (Person p)
{
    SQLiteDatabase db = dbOpenHelper.getWritableDatabase ();
    db.execSQL ("INSERT INTO person (name, phone) values (?,?)",
                new String [] {p.getName (), p.getPhone ()});
}
2. Delete data:

public void delete (Integer id)
{
    SQLiteDatabase db = dbOpenHelper.getWritableDatabase ();
    db.execSQL ("DELETE FROM person WHERE personid =?",
                new String [] {id});
}
3. Modify the data:

public void update (Person p)
{
    SQLiteDatabase db = dbOpenHelper.getWritableDatabase ();
    db.execSQL ("UPDATE person SET name =?, phone =? WHERE personid =?",
        new String [] {p.getName (), p.getPhone (), p.getId ()});
}
4. Query data:

public Person find (Integer id)
{
    SQLiteDatabase db = dbOpenHelper.getReadableDatabase ();
    Cursor cursor = db.rawQuery ("SELECT * FROM person WHERE personid =?",
            new String [] {id.toString ()});
    // Returns true if data exists
    if (cursor.moveToFirst ())
    {
        int personid = cursor.getInt (cursor.getColumnIndex ("personid"));
        String name = cursor.getString (cursor.getColumnIndex ("name"));
        String phone = cursor.getString (cursor.getColumnIndex ("phone"));
        return new Person (personid, name, phone);
    }
    cursor.close ();
    return null;
}
5. Data paging:

public List <Person> getScrollData (int offset, int maxResult)
{
    List <Person> person = new ArrayList <Person> ();
    SQLiteDatabase db = dbOpenHelper.getReadableDatabase ();
    Cursor cursor = db.rawQuery ("SELECT * FROM person ORDER BY personid ASC LIMIT =?,?",
        new String [] {String.valueOf (offset), String.valueOf (maxResult)});
    while (cursor.moveToNext ())
    {
        int personid = cursor.getInt (cursor.getColumnIndex ("personid"));
        String name = cursor.getString (cursor.getColumnIndex ("name"));
        String phone = cursor.getString (cursor.getColumnIndex ("phone"));
        person.add (new Person (personid, name, phone));
    }
    cursor.close ();
    return person;
}
6. Query records:

public long getCount ()
{
    SQLiteDatabase db = dbOpenHelper.getReadableDatabase ();
    Cursor cursor = db.rawQuery ("SELECT COUNT (*) FROM person", null);
    cursor.moveToFirst ();
    long result = cursor.getLong (0);
    cursor.close ();
    return result;
}
PS: In addition to the method of obtaining the number of items above, you can also use the cursor.getCount () method to obtain the number of data, but the SQL statement must be changed! Such as SELECT * FROM person;

This section introduces the basic usage of Android's built-in SQLite, which is still relatively simple. The next section will look at some more advanced things, SQLite transactions, how to update data in the application update database, and how to store large binary files in the database!

SQLite database (on)